Write a query to find each customer who has an account at every branch located in “Brooklyn”

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). Make any reasonable assumptions about the data types. Be sure to declare primary and foreign keys; both types of constraints should be given appropriate names. Correctly and appropriately implement at least one instance for each of the following: check constraint, not null constraint, on delete cascade clause, on update cascade clause, and a default value statement. Lastly, construct the following SQL queries for this relational database. ________________________________________________________________________

You are free to define the DDL for this banking database as you wish, provided that the above requirements are satisfied. However, there is one prohibition: You must not include a foreign key constraint on the account_number attribute of the depositor relation that references the account relation. To be perfectly clear, this means you must not include the following foreign key constraint in your DDL:

CREATE TABLE depositor

( … CONSTRAINT depositor_fkey FOREIGN KEY (account_number)

REFERENCES account (account_number) ON DELETE

CASCADE ON UPDATE CASCADE … );

The reason for this specific foreign key prohibition concerns question (3) of this assignment. The spirit behind question (3) is to write a trigger that performs the same action that is accomplished by the ON DELETE CASCADE clause of the above foreign key constraint. So, if you were to include this foreign key constraint with the ON DELETE CASCADE clause in your DDL, then the trigger you must define in question (3) would be useless since the action would be performed not by the trigger - but, rather by the ON DELETE CASCADE clause instead. If you were to include this foreign key constraint without the ON DELETE CASCADE clause in your DDL, then the DBMS would disallow you from deleting any tuples from the account relation. If you include the specified foreign key constraint in your DDL, you will receive a grade of zero for question (3). ________________________________________________________________________

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 )

Note that in the following problem, there is only one bank, and the individual branches listed in the data are all owned by the one bank. Also, you must not add any additional attributes or tables to the bank database schema. Data for this bank database can be found on Brightspace. You may, of course, add more data if it aids you in testing queries.

Answer rating: 100% (QA)

sql Create the branch table CREATE TABLE branch branchname VARCHAR50 PRIMARY KEY branchcity VARCHAR5... View the full answer