How to Use a Relational DBMS for Database Creation and Management

Project Tasks:

In this project, you will get started on how to use a relational DBMS. You can either use the ORACLE RDBMS, or the MySQL system. You will use the interactive SQLPLUS facility, and the SQL programming facility, by creating tables, populating them with data, and querying and updating the tables. You should do the following:

Create the following tables for a Bank database whose schema is specified in Page 3 of this document: Bank, Account, Customer, Loan, Bank_Branch,Account_Customer ,Loan_Customer.

1. Write your CREATE TABLE statements in a text file and execute the commands from the file through SQLPLUS. You should capture the execution in a spool file that will be turned in. Specify appropriate key and referential integrity constraints. The data types for each attribute are given after the schema diagram.

2. Write one or more database programs to load the records that will be synthesizing yourself. There will be credit for generation of appropriate synthetic data. The data files in CSV format need to be submitted as part of the project submission. Once your data sheet is ready, use any programming or scripting language to load this data into tables that you have created in step1.

3. Write down the queries for the English queries that are listed later. Execute each query and display its results. Capture your commands in spool files for turning in.

4. Execute 3 more Insert commands that attempt to insert 3 more records, such that the records violate the integrity constraints. Make each of the 3 records violate a different typeof integrity constraint. Capture your commands in spool files for turning in.

5. Execute a SQL command to Delete a record that violates a referential integrity constraint. Capture your command in a spool file for turning in.

6. Repeat 5 but Insert three new records that do not violate any integrity constraints. Capture your commands in spool files for turning in.

You should turn in one or more Spool files as part of the assignment, data files, including creating the tables and the query results. You should also turn in the source code for the programming part of the assignment for loading the data.

Document your output when needed by writing down an explanation for each step (by editing the spool file); for example, explain the integrity constraints violated in item 5.

Important Notes:

• This project is to be done INDIVIDUALLY.
• Copying from other students is not permitted and will result in a grade of zero for the entire project.

Submit your assignment in Canvas any time before midnight of the due date. You should follow the instructions on how to turn in your project (One Zipped folder containing all files together).

For item 3)

Apply the following queries and display the result of each query

• Print the name and address of branch that serves the largest loan
• Retrieve the name and address of customers who have largest account net balance (account balance – loan).
• Print name and address of top 10 branches in having accounts as well as serving loans
• Retrieve the name and address of branches having net account deficit and sort them in the largest to smallest deficit
• List out the bank having minimum number of branches but at the same time having largest account surplus.
• List the bank who supports largest number of branches supporting home loans.