CSC424 Exam 1

CSC 424 Introduction to Database March 27, 2012
Prof Emile C. Chi 1st Exam

NAME______

1) (30 points) Let R (A, B, C, D, E) be a relations with attributes A, B, C, D, E. Let F be the set of functional dependencies: A -> B,C C,D -> E B -> D E -> A
a) Compute the closure F+ of F. Give a reason for each dependency in F+

Closure for F+:
A+= (A,B,C,D,E)
BC+ = (A,B,C,D,E)
CD+ = (A,B,C,D,E)
E+ = (A,B,C,D,E)

b) Draw an E-R diagram for R.
c) Is R in BCNF? Give a reason for your answer.
R is in BCNF because every attribute in R is a candidate key

2) (30 points) Consider the following relational database for a bank: These are the relations in the database.
Branch (branch_name, branch_city, assets)
Customer (customer_id, customer_name, customer_street, customer_city)
Loan (loan_number, branch_name, amount)
Borrower(customer_id, loan_number)
Account (account_number, branch_name, balance)
Depositor (customer_id, account_number)

a) Write the SQL required to create this database. The primary keys of each relation are underlined.

CREATE TABLE `Account` ( `account_number` int, `branch_name` varchar(255), `balance` double, PRIMARY KEY (`account_number`)
);

CREATE TABLE `Borrower` ( `customer_id` int, `loan_number` int, PRIMARY KEY (`customer_id`,`loan_number`)
);

CREATE TABLE `Branch` ( `branch_name` varchar(255), `branch_city` varchar(255), `assets` varchar(255), PRIMARY KEY (`branch_name`)
);

CREATE TABLE `Customer` ( `customer_id` int, `customer_name` varchar(255), `customer_street` varchar(255), `customer_city` varchar(255), PRIMARY KEY (`customer_id`)
);

CREATE TABLE `Depositor` ( `customer_id` int, `account_number` int, PRIMARY KEY (`customer_id`,`account_number`)
);

CREATE TABLE `Loan` ( `loan_number` int, `branch_name` varchar(255), `amount` double, PRIMARY KEY (`loan_number`)
);

b) Write the SQL required to answer these queries to the database:

Find the id’s and