I have created the DDL below:
I am getting an error about an existing constraint with the borrower_fkey for the borrower relation. At the same time, I feel like I have not satisfied all the requirements. Could someone show me what I did wrong?
The class is being taught using PostgreSQL so the changes have to be applicable to postgreSQL.
Thank you!
CREATE TABLE branch (
branch_name varchar(25) NOT NULL,
branch_city varchar(15),
assets numeric(15,2),
CONSTRAINT branch_pkey PRIMARY KEY (branch_name)
);
CREATE TABLE customer (
ID varchar(10),
customer_name varchar(25) NOT NULL,
customer_street varchar(25) NOT NULL,
customer_city varchar(15) NOT NULL,
CONSTRAINT customer_pkey PRIMARY KEY (ID)
);
CREATE TABLE loan (
loan_number varchar(25) NOT NULL,
branch_name varchar(25) NOT NULL,
amount numeric(25,2) NOT NULL,
CONSTRAINT loan_pkey PRIMARY KEY (loan_number),
CONSTRAINT loan_fkey FOREIGN KEY (branch_name) REFERENCES branch (branch_name) ON DELETE CASCADE
);
CREATE TABLE borrower (
ID varchar(25),
loan_number varchar(25) NOT NULL,
CONSTRAINT borrower_pkey PRIMARY KEY (ID, loan_number),
CONSTRAINT borrower_fkey FOREIGN KEY (ID) REFERENCES customer (ID) ON DELETE CASCADE,
CONSTRAINT borrower_fkey FOREIGN KEY (loan_number) REFERENCES loan (loan_number) ON UPDATE CASCADE
);
CREATE TABLE account (
account_number varchar(25),
branch_name varchar(25) NOT NULL,
balance numeric(25,2),
CONSTRAINT account_pkey PRIMARY KEY (account_number),
CONSTRAINT account_fkey FOREIGN KEY (branch_name) REFERENCES branch (branch_name) ON DELETE CASCADE
);
CREATE TABLE depositor (
ID varchar(25),
account_number varchar(25) NOT NULL,
CONSTRAINT depositor_pkey PRIMARY KEY (ID, account_number),
CONSTRAINT depositor_fkey FOREIGN KEY (ID) REFERENCES customer (ID) ON DELETE CASCADE
);
Extracted text: Query Editor Query History 23 24 CREATE TABLE borrower ( 25 ID varchar (25), 26 loan_number varchar (25) NOT NULL, 27 CONSTRAINT borrower_pkey PRIMARY KEY (ID, loan_number), 28 CONSTRAINT borrower_fkey FOREIGN KEY (ID) REFERENCES customer (ID) ON DELETE CASCADE, 29 CONSTRAINT borrower_fkey FOREIGN KEY (loan_number) REFERENCES loan (loan_number) ON UPDATE CASCADE 30 ) ; 31 32 CREATE TABLE account ( 33 account_number varchar(25), 34 branch_name varchar (25) NOT NULL, 35 balance numeric(25,2), 36 CONSTRAINT account_pkey PRIMARY KEY (account_number), 37 CONSTRAINT account_fkey FOREIGN KEY (branch_name) REFERENCES branch (branch_name) ON DELETE CASCADE 38 ) ; 39 40 CREATE TABLE depositor ( 41 ID varchar(25), 42 account_number varchar(25) NÓ NULL, 43 CONSTRAINT depositor_pkey PRIMARY KEY (ID, account_number), 44 CONSTRAINT depositor_fkey FOREIGN KEY (ID) REFERENCES customer (ID) ON DELETE CASCADE 45 ) ; Data Output Explain Messages Notifications ERROR: constraint "borrower_fkey" for relation "borrower" already exists SQL state: 42710 LO
Extracted text: 2. 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. NOTE THE FOLLOWING: 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 So, if you were to include this foreign key constraint with the ON key constraint. 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 ( ID, customer_name, customer_street, customer_city ) loan (loan mиmber, branch пате, атоиnt) borrower ( ID, loan number ) ассоunt (аccount mumber, branch_name, balance) depositor ( 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. Data for this bank database can be found on Brightspace. You may, of course, add more data if it aids you in testing queries.