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); Query Editor Query History2324CREATE TABLE borrower (25ID varchar (25),26loan_number varchar (25) NOT NULL,27CONSTRAINT borrower_pkey PRIMARY KEY (ID, loan_number),28CONSTRAINT borrower_fkey FOREIGN KEY (ID) REFERENCES customer (ID) ON DELETE CASCADE,29CONSTRAINT borrower_fkey FOREIGN KEY (loan_number) REFERENCES loan (loan_number) ON UPDATE CASCADE30) ;3132CREATE TABLE account (33account_numbervarchar(25),34branch_name varchar (25) NOT NULL,35balance numeric(25,2),36CONSTRAINT account_pkey PRIMARY KEY (account_number),37CONSTRAINT account_fkey FOREIGN KEY (branch_name) REFERENCES branch (branch_name) ON DELETE CASCADE38) ;3940CREATE TABLE depositor (41IDvarchar(25),42account_numbervarchar(25) NÓNULL,43CONSTRAINT depositor_pkey PRIMARY KEY (ID, account_number),44CONSTRAINT depositor_fkey FOREIGN KEY (ID) REFERENCES customer (ID) ON DELETE CASCADE45) ;Data Output ExplainMessagesNotificationsERROR:constraint "borrower_fkey" for relation "borrower" already existsSQL state: 42710LO 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 andforeign keys; both types of constraints should be given appropriate names. Correctly andappropriately 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 defaultvalue 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 asyou wish, provided that the above requirements are satisfied. However, there is oneprohibition: You must not include a foreign key constraint on the account numberattribute of the depositor relation that references the account relation. To be perfectlyclear, this means you must not include the following foreign key constraint in your DDL:CREATE TABLE depositorCONSTRAINT depositor_fkey FOREIGN KEY (account_number)REFERENCES account (account number)ON DELETE CASCADEON UPDATE CASCADE);The reason for this specific foreign key prohibition concerns question (3) of thisassignment. The spirit behind question (3) is to write a trigger that performs the sameaction that is accomplished by the ON DELETE CASCADE clause of the above foreignSo, if you were to include this foreign key constraint with the ONkey 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 bythe ON DELETECASCADE clause instead. If you were to include this foreign keyconstraint without the ON DELETE CASCADE clause in your DDL, then the DBMSwould disallow you from deleting any tuples from the account relation. If you include thespecified foreign key constraint in your DDL, you will receive a grade of zero forquestion (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 brancheslisted 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 moredata if it aids you in testing queries.

Below is the answer with explanation:

Answered: I am getting an error about an existing…

Computer Networking: A Top-Down Approach (7th Edition)

7th Edition

ISBN:9780133594140

Author:James Kurose, Keith Ross

Publisher:James Kurose, Keith Ross

Chapter1: Computer Networks And The Internet

Section: Chapter Questions

Problem R1RQ: What is the difference between a host and an end system? List several different types of end...

See similar textbooks

Related questions

Question

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

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

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.

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here