DTSC660: Data and Database Management with SQL

Module 6

Assignment 4

Purpose

For this assignment, you have been asked to create a banking database for Eastern Banking Holdings. For this assignment you will rely on the skills obtained from Modules 5 and 6 to successfully create the banking database and query it to retrieve the appropriate responses. Note that many, but not necessarily all, of the tools you learned in Modules 5 and 6 will be applied in this assignment.To complete this assignment, download and import the dataset and then create queries that respond to each prompt. Please make sure that you only use postgres language conventions.




Each question is all or nothing. Graders will not attempt to correct or interpret malformed SQL queries.




You will be responsible for testing your code on the provided data set before submission. The question will be graded based on whether or not it generates the correct output. Extraneous columns will not count against you.

Submission

You will submit a total of




1




sql files. Each file must use the postgres standards taught in the course. Use of other flavors of SQL such as T-SQL will result in an automatic 0 for the assignment. Do not submit files as archives (ZIP) files.

• 
  
  
  

  
  
  
  File 1
  
  
  : You must submit a SQL document called _Assignment4. This document must include ALL queries requested in the instructions below.
  
  
  
  
  
  
  
  
  
  
  
  
  

  
  
  
  




• 
  
  
  

  
  
  
  You will submit the file to the Assignment 4 folder.
  
  
  

  
  
  
  

Question 1:

Consider the bank database schema given below, where the primary keys are underlined. Write the SQL DDL corresponding to this schema (i.e. the CREATE TABLE statements). When you are writing the DDL, make sure you do the following:

1. 
   
   
   

   
   
   
   Create the tables in the order specified in the schema
   
   
   

   
   
   
   




2. 
   
   
   

   
   
   
   Use the exact naming convention for table names and attributes
   
   
   

   
   
   
   




3. 
   
   
   

   
   
   
   Do not add or remove tables or attributes
   
   
   

   
   
   
   




4. 
   
   
   

   
   
   
   For each table, I have provided some assumptions that can be made about the various attributes and relationships between the tables. Your DDL should ensure that these assumptions are true by how you define the table, data types used, and enforcement of keys and triggers.
   
   
   

   
   
   
   




5. 
   
   
   

   
   
   
   For this example, there is only one bank, and the individual branches listed in the data are all owned by the one bank
   
   
   

   
   
   
   




6. 
   
   
   

   
   
   
   Any attributes identified as the varchar data type should have a length of 40. (varchar(40))
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   

   
   
   
   




7. 
   
   
   

   
   
   
   By the time you have completed you will have at least one of each of the following. Failure to define these will result in lost points:
   
   
   

   
   
   
   








1. 
   
   
   

   
   
   
   check constraint
   
   
   

   
   
   
   




2. 
   
   
   

   
   
   
   not null constraint
   
   
   

   
   
   
   




3. 
   
   
   

   
   
   
   on delete cascade clause
   
   
   

   
   
   
   




4. 
   
   
   

   
   
   
   on update cascade clause
   
   
   

   
   
   
   




5. 
   
   
   

   
   
   
   default value statement.
   
   
   

   
   
   
   

Database Schema:

branch (




branch_name


, branch_city, assets )

customer (




cust_ID


, customer_name, customer_street, customer_city )

loan (




loan_number


, branch_name, amount )

borrower (




cust_ID


,




loan_number





)

account (




account_number


, branch_name, balance )

depositor (




cust_ID


,




account_number





)

Important notes for each table:

• 
  
  
  

  
  
  
  Branch: All branches must have assets that are monetary (they have a dollar value). There are four and only four cities with branches: Brooklyn, Bronx, Manhattan, and Yonkers.
  
  
  

  
  
  
  




• 
  
  
  

  
  
  
  Customer: Customers must have a name.
  
  
  

  
  
  
  




• 
  
  
  

  
  
  
  Loan: Loan numbers can contain both letters and numbers. All loans must have a monetary amount. The default amount for loans is zero dollars and zero cents. If a branch closes or changes its name, these activities should be reflected in the loan table.
  
  
  

  
  
  
  




• 
  
  
  

  
  
  
  Borrower: A borrower is a type of customer, so if the cust_ID is deleted or changed, the borrower table should reflect these actions. The same is true of the loan_number.
  
  
  

  
  
  
  




• 
  
  
  

  
  
  
  Account: Accounts should always have a monetary balance. If a branch closes or changes its name, these activities should be reflected in the account table.
  
  
  

  
  
  
  




• 
  
  
  

  
  
  
  Depositor: A depositor is a type of customer, so if the cust_ID is deleted or changed, the depositor table should reflect these actions. The same is true of the account_number.
  
  
  

  
  
  
  

For the next section, you will need to use the data file attached to this assignment to complete your queries. If your queries do not run, go back and fix your Banking DDL. Make sure you use the correct table names and attributes. If your code does not execute with the provided data, you will not receive credit for the remaining parts of the assignment.

Question 2:

Write a query to find all customers who are depositors and return their Customer ID, Branch Name, Account Number, and Balance.

Question 3:

Write a query that returns all customers who are both depositors and borrowers. Include in your query the customer ID, account number, and loan_number.

Question 4:

Write a query that finds the account number of all customers whose deposit account is in the city where they live. Include the customer’s city, branch city, branch name, and account number.

Question 5:

Write a query to generate a unique list of customer IDs that are both depositors and borrowers WITHOUT USING JOINS.

THIS FINAL QUESTION USES THE UNIVERSITY SCHEMA, NOT THE BANKING DDL.

Question 6:

The following question references the University schema instead of the banking schema referenced in previous questions. Please write an appropriate query for the question below:

Write a SQL query using the university schema to find the ID of each student who has never taken a course at the university. Do this using no subqueries and no set operations (use an outer join).

*******************************GRADING RUBRIC ON NEXT PAGE*********************************

This assignment will be graded on the following rubric. Remember that questions are ALL OR NOTHING. Incorrect syntax or extraneous results will result in loss of points for that question. Graders will NOT attempt to correct malformed sql code. :

***Please see part G of Question 1 and make sure you have at least one of each constraint type