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 <LastName>_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:
a. Create the tables in the order specified in the schema
b. Use the exact naming convention for table names and attributes
c. Do not add or remove tables or attributes
d. 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.
e. For this example, there is only one bank, and the individual branches listed in the data are
all owned by the one bank
f. Any attributes identified as the varchar data type should have a length of 40.
(varchar(40))
g. 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:
i. check constraint
ii. not null constraint

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