PART $1$ Banking Database Design

Banking DDL:

Consider the bank database schema given below. 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 $($this is important to avoid errors in

pgAdmin$).$

b$.$ Use the exact names given for table names and attributes. DO NOT add or remove

tables or attributes.

c$.$ DO NOT use ALTER TABLE or UPDATE statements to add constraints. Constraints

should be included in the CREATE TABLE statements.

d$.$ DO NOT include the INSERT statements provided in your submission!

e$.$ DO NOT include a CREATE DATABASE statement in your submission!

f$.$ For each table, assumptions have been provided regarding the attributes and

relationships between them. The purpose of your DDL is to meet the outlined

requirements by utilizing constraints and keys for data validation. By defining the tables,

specifying appropriate data types, and enforcing constraints and keys $($both primary and

foreign$),$ you should ensure that these assumptions hold true.

g$.$ In this database, only the data types varchar$(40)$ and money will be used. Please

ensure that attributes are defined with only these data types for consistency.

h$.$ There are $16$ constraints required for this database $($see rubric$).$ Each Table Assumption

below translates to a constraint. Do not add constraints as this may result in loss of

points.

i$.$ By the time you have completed you will have at least one of each of the following:

i$.$ CHECK constraint

ii$.$ NOT NULL constraint

iii. ON UPDATE CASCADE ON DELETE CASCADE

iv$.$ DEFAULT value constraint

Note: For the purposes of this assignment, item iii will be counted as ONE complete constraint.

When using this constraint, use it in its entirety or you will not receive credit.

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 $)$

$**$ Bold indicates Foreign Key, Underlined indicates Primary Key

Database Assumptions:

$$ A customer can have multiple accounts and$/$or multiple loans. Likewise, an account or

loan can be tied to more than one customer $($think spouses or business partners$).$

$$ For this example, it is assumed that there is only one bank, and all the individual

branches listed in the data are owned by this bank

Table Assumptions:

$$ Branch:

$$ All branches must have assets.

$$ Asset amount cannot be negative

$$ There are four and only four cities with branches: Brooklyn, Bronx, Manhattan,

and Yonkers.

$$ Customer:

$$ Customers must have a name.

$$ Customers must have a street address $($You may choose to include an additional

CONSTRAINT for the city; however, please note that only the street address is a

requirement.$)$

$$ Loan:

$$ All loans must have an amount.

$$ The default amount for loans is zero dollars and zero cents.

$$ A loan amount cannot be negative

$$ 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:

$$ All accounts must have an amount.

$$ The default balance amount is zero dollars and zero cents.

$$ 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.

Note: Please remember that for this assignment, you should only use the data types

varchar$(40)$ and money for consistency purposes. While these data types might not be the

absolute best choice in all cases, the goal here is to demonstrate your understanding of

constraints, data validation, and keys. Data type declaration was already assessed in a prior

assignment, and it is not the primary focus of this assignment. Therefore, choose the most

appropriate data type between varchar$(40)$ and money for each attribute to maintain

consistency throughout the database design.