Database Management Systems This one question 4 part. Please give all necessary
ID: 3755175 • Letter: D
Question
Database Management Systems
This one question 4 part. Please give all necessary things. Thanks
What to submit:
Submit SQL commands used to answer Part 1, Part 2, and Part 3. Make sure that your SQL script runs without any errors. Submit screen shots of running commands for Part 2. Submit answers to Part 4 along with explanation of the system response to your command (so in case of error, explain why the error happened, in case the insert works, explain why it worked).
Part 1 (3 Points): Creating the database:
Create the following tables. The underlined bold column names are the primary keys. Make sure to specify the primary and foreign key constraints in your create table statements.
1. Branch(branch_id:integer, branch_name:varchar(50), branch_city:varchar(50), assets:numeric(11,2)
2. Loan(loan_number:integer, branch_id:integer, amount:numeric(8,2))
foreign key branch_id references Branch(branch_id)
3. Customer(customer_id:integer, customer_name:varchar(30), customer_street:varchar(30), customer_city:varchar(50))
4. Borrower(customer_id:integer, loan_number:integer)
foreign key (customer_id) references Customer(customer_id)
foreign key (loan_number) references Loan(loan_number)
5. Depositor(customer_id:integer, account_number:integer)
foreign key (customer_id) references Customer(customer_id)
foreign key (account_number) references Account(account_number)
6. Account(account_number:integer, branch_id:integer, balance:numeric(8,2))
foreign key branch_id references Branch(branch_id)
Part 2 (3 Points): Inserting data:
Insert the following data in the tables using insert into statements:
1.Branch:
1, DowntownChi, Chicago, 40000000.00
2, UptownMPL, Minneapolis, 32000000.00
3, DowntownSP, St. Paul, 21000000.00
2.Loan:
1001, 1, 2008.08
1002, 1, 3201.06
1003, 2, 4508.03
1004, 3, 21008.00
3.Customer:
1, Gretzky, 14 S 6th St., St. Paul
4, Carr, 16699 39th Ave N, Gary
2, Kilmer, 205 Dupont Ave. N, Minneapolis
3, Smith, 3598 Jones Rd., Hopkins
5, Smith, 9873 5Th. St., Chicago
4.Borrower:
4, 1001
5, 1002
2, 1003
1, 1004
5.Depositor:
1, 232
2, 235
4, 294
3, 295
5, 249
6.Account:
232, 3, 456.23
235, 2, 4500.19
294, 1, 6003.63
295, 3, 7500.00
249, 1, 670.85
Part 3 (9 Points): SQL Queries
Write SQL statements to answer each of the following questions.
1.List the Customer Code, Name for all customers, sorted by Name.
2.List the loan number and loan amount for all loans belonging to branch 1.
3.List the loan number, loan amount from all branches with the branch name “UptownMPL” (use branch name, if you have a 1000 branches, you are not going to know the branch number for each branch name).
4.List all account numbers for customers with the name of “Smith”.
5.List all the loan numbers for the branches in the city of Duluth.
6.For each branch list the branch name, customer name, and customer city for all customers that have an account at that branch. (note: accounts, not loans)
Part 4 (5 Points): Checking entity and referential integrity:
Write an SQL statement to do each of the following tasks in the given order (try 1, 2, 3, 4, than 5). Explain whether and why the statement is correctly executed or not. If you cut and paste, you may have to correct the single quotes (those are not errors)
1. Insert the following entry in CUSTOMER
4, ‘Ford’, ‘19 14th Ave N’, ‘Mankato’
2. Insert the following entry in LOAN
1005, 2, 30000.00
3. Insert the following entry in Borrower
3, 1005
4. Insert the following entry to the LOAN table
1006, 4, 45000.00
5. Insert the following entry in Branch table
4, ‘DowntownMK’, ‘Mankato’, 2100000.00
Explanation / Answer
answer for part 1:
create table Branch (branch_id int NOT NULL PRIMARY KEY, branch_name VARCHAR2(50), branch_city VARCHAR2(50), assets NUMBER(11,2));
CREATE TABLE Loan (loan_number int NOT NULL PRIMARY KEY, branch_id int, amount number (8,2), FOREIGN KEY (branch_id) REFERENCES Branch(branch_id));
Create table Customer(customer_id int NOT NULL PRIMARY KEY, customer_name varchar2(30), customer_street varchar2(30), customer_city varchar2(50));
Create table Borrower (customer_id int NOT NULL PRIMARY KEY, loan_number int,foreign key (customer_id) references Customer(customer_id), foreign key (loan_number) references Loan(loan_number));
Create table Depositor (customer_id int NOT NULL PRIMARY KEY, account_number int ,foreign key (customer_id) references Customer(customer_id), foreign key (account_number) references Account(account_number));
create table Account(account_number int NOT NULL PRIMARY KEY, branch_id int, balance number(8,2),foreign key (branch_id) references Branch(branch_id));
answer for part2:
1.Branch:
insert into branch values (1, 'DowntownChi', 'Chicago', 40000000.00);
insert into branch values (2, 'UptownMPL', 'Minneapolis', 32000000.00);
insert into branch values (3, 'DowntownSP', 'St. Paul', 21000000.00);
2.Loan:
insert into loan values (1001, 1, 2008.08);
insert into loan values (1002, 1, 3201.06);
insert into loan values (1003, 2, 4508.03);
insert into loan values (1004, 3, 21008.00);
3.Customer:
insert into customer values (1, 'Gretzky', '14 S 6th St.', 'St. Paul');
insert into customer values (4, 'Carr', '16699 39th Ave N', 'Gary');
insert into customer values (2, 'Kilmer', '205 Dupont Ave. N', 'Minneapolis');
insert into customer values (3, 'Smith', '3598 Jones Rd.', 'Hopkins');
insert into customer values (5, 'Smith', '9873 5Th. St.', 'Chicago');
4.Borrower:
insert into borrower values(4, 1001);
insert into borrower values(5, 1002);
insert into borrower values(2, 1003);
insert into borrower values(1, 1004);
5.Depositor:
insert into depositor values (1, 232);
insert into depositor values (2, 235);
insert into depositor values (4, 294);
insert into depositor values (3, 295);
insert into depositor values (5, 249);
6.Account:
insert into account values (232, 3, 456.23);
insert into account values (235, 2, 4500.19);
insert into account values (294, 1, 6003.63);
insert into account values (295, 3, 7500.00);
insert into account values (249, 1, 670.85);
answer for the part 3:
List the Customer Code, Name for all customers, sorted by Name.
select customer_id,customer_name from customer order by customer_name;
2.List the loan number and loan amount for all loans belonging to branch 1.
select loan_number,amount from loan where branch_id ='1';
3.List the loan number, loan amount from all branches with the
branch name “UptownMPL” (use branch name, if you have a 1000 branches,
you are not going to know the branch number for each branch name).
select l.loan_number,l.amount from loan l ,branch b where b.branch_name='UptownMPL';
4.List all account numbers for customers with the name of “Smith”.
select a.account_number from account a,customer c where c.customer_name='Smith';
5.List all the loan numbers for the branches in the city of Duluth.
this query will return no rows because there are no city like Duluth.
6.For each branch list the branch name, customer name, and customer city for all
customers that have an account at that branch. (note: accounts, not loans)
select b.branch_name,c.customer_name,c.customer_city from customer c,branch b where b.branch_id in (select branch_id from branch);
answer for part4:
1.Insert the following entry in CUSTOMER
insert into customer values (4, 'Ford', '19 14th Ave N', 'Mankato');
this query will not work because previously we are having 1 row which is having
the value as 4 in customer_id which is unique.
2. Insert the following entry in LOAN
insert into loan values (1005, 2, 30000.00);
3. Insert the following entry in Borrower
insert into borrower values (3, 1005);
4. Insert the following entry to the LOAN table
insert into loan values (1006, 4, 45000.00);
5. Insert the following entry in Branch table
insert into branch values ('4', 'DowntownMK', 'Mankato', 2100000.00);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.