# CSC424 Exam 1

Topics: Relational model, SQL, Relational database Pages: 5 (856 words) Published: November 11, 2014
﻿CSC 424Introduction 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.
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 names of all customers who have an account at all of the branches located in “Staten Island”. SELECT Customer.customer_name
AND Customer.customer_id
FROM Customer,Depositor,Account,Branch
WHERE Customer.customer_id = Depositor.customer_id
AND Depositor.account_number = Account.account_number     AND Account.branch_name = Branch.branch_name
AND Branch.branch_city = "Staten Island"

Find the names of all branches that have assets greater than those of at least one branch located in “Manhattan”. SELECT branch_name
FROM Branch
WHERE branch_city != "Manhattan"
AND assets > (SELECT MIN(assets) FROM Branch WHERE branch_city = "Manhattan")

The queries in questions 3 and 4 are to the university database. Write the SQ, run it and hand in both your SQL program and the results it retrieves from the database.

3) (20 points)  Find the last names of all professors who have not taught every CS course. Your query must find all professors who taught some CS course and also all professors who never taught any CS course. A CS course is a course offered by the CS department, not a course which starts with ‘CSC’.

use university;

CREATE VIEW CScourse AS (SELECT cnum
FROM course
WHERE dept='CS');

select * from CScourse;

CREATE VIEW ProfsList AS (SELECT pnum
FROM prof);

select * from ProfsList;

CREATE VIEW CSProfsName AS(SELECT CScourse.cnum, pnum
FROM CScourse, section
WHERE CScourse.cnum = section.cnum);

CREATE VIEW Result AS
SELECT DISTINCT ProfsList.pnum
FROM ProfsList, CSProfsName
WHERE ProfsList.pnum = ANY (SELECT CSProfsName.pnum FROM CSProfsName)

UNION

SELECT DISTINCT ProfsList.pnum
FROM ProfsList, CSProfsName
WHERE ProfsList.pnum NOT IN (SELECT CSProfsName.pnum
FROM CSProfsName);

SELECT Lastname
FROM prof, Result
WHERE Result.pnum = prof.pnum;...