Questions
1. Consider the table:
STUDENT_CLUB (StudentID, Hobby, Club, Clubfee)
· Assume that Student ID is unique for each student.
· A student can have many hobbies.
· A student can register only one club.
· There is a fixed ClubFee for each club
Follow the procedure, to access the data
a. List all functional dependencies.
b. List any multivalued dependencies.
c. What is the primary key for the STUDENT_CLUB table? Hint: Primary key must be unique for each row in the table.
d. List the normalized tables in Boyce-Codd normal form. (2 tables: TABLE_A and TABLE_B)
e. List all foreign keys.
f. List Primary keys of both tables after normalization to BCNF.
2. Consider the table:
STUDENT_ADVISOR ( StudentID, GPA, Advisor, CoursesTaken ).
· Assume that Student ID is unique for each student.
· GPA is grade point average which is also unique for each student.
· A student can have only one advisor.
· CoursesTaken are the courses completed by the student so far.
Follow the procedure, to access the data
a. List all functional dependencies.
b. List any multivalued dependencies.
c. What is the primary key for the STUDENT_ADVISOR table?
d. List the normalized tables in 4th normal form. (2 tables: TABLE_A and TABLE_B)
e. List all foreign keys.
f. List Primary keys of both tables after normalization to BCNF.
g. Assume the names of Normalized tables in d, TABLE_A and TABLE_B, which were already created in a Database.
Write SQL statements to transfer data in STUDENT_ADVISOR table to TABLE_A and TABLE_B.
STUDENT_ADVISOR to TABLE_A / STUDENT_ADVISOR to TABLE_B3. Write SQL statements to obtain desired result using the provided database.
· Each blank may refer to more than one word. If not applicable fill N/A.
A. Show the FirstName and Lastname of all customers who have had an order with an item named ‘Formal Gown’.
Order by ascending order of CustomerID.
SELECT ______, LastName
FROM ______
WHERE CustomerID ______
(SELECT ______
FROM ______
WHERE ______
SELECT ______
FROM ______
______Item ______
ORDER BY ______
B. Show the CustomerID and Email of all customers who have had ordered more than 2 Blouse. Use Join.
SELECT DISTINCT ______, ______
FROM ______, ______
WHERE ______= ______
AND ______= ______
AND ______= ______
AND ______> ______
4. Let’s assume the following referential integrity constraint holds.
INVOICE_ITEM.InvoiceNumber must exist in INVOICE.InvoiceNumber
· Write an SQL statement that will return any values of foreign key that violate the constraint.
· Each blank may refer more than one word. If not applicable fill N/A.
SELECT InvoiceNumber
FROM ______
WHERE ______
(SELECT ______
FROM ______
WHERE ______= ______