🎓 CS340 Database Systems

Comprehensive Final Examination

Course Code

CS340

Total Points

100

Total Questions

Duration

180 min

⏱️ 00:00:00

Your Progress 0/30 answered

✍️ Written Questions 40 marks

▼

Q11 10 marks

Given the following schema, identify all foreign keys and explain their role in maintaining referential integrity:

Restaurant (restaurantId, restaurantName, restaurantAddress, restaurantCity)
Customer (customerId, customerName, customerAddress, customerCity)
Reservation (customer_id, restaurant_id, reservationDate, noOfPersons)

✓ Model Answer

Foreign Keys:

customer_id in Reservation → references Customer.customerId
restaurant_id in Reservation → references Restaurant.restaurantId

Role in Referential Integrity: Ensures every reservation links to valid customers and restaurants, prevents orphaned records, enforces relationships, and enables cascade operations.

Keywords Found:

Q12 10 marks

Explain First Normal Form (1NF) and describe violations that prevent a relation from being in 1NF.

✓ Model Answer

A relation is in 1NF if all attributes contain only atomic (indivisible) values.

Violations: Multivalued attributes, composite attributes, repeating groups, nested relations.

Keywords Found:

Q13 10 marks

Explain Second Normal Form (2NF) and the type of functional dependency that must be eliminated.

✓ Model Answer

A relation is in 2NF if it's in 1NF and every non-prime attribute is fully functionally dependent on the entire primary key.

Must eliminate: Partial dependencies (non-prime attributes depending on part of a composite key).

Keywords Found:

Q14 10 marks

Explain Third Normal Form (3NF) and what must be eliminated to achieve it.

✓ Model Answer

A relation is in 3NF if it's in 2NF and no non-prime attribute is transitively dependent on the primary key.

Must eliminate: Transitive dependencies (A → B → C where B is non-prime).

Keywords Found:

💻 SQL Queries 20 marks

▼

Q15 10 marks

Write a SQL query to find customers who have placed orders totaling more than $500.
Schema: Customers (CustomerID, Name), Orders (OrderID, CustomerID, TotalAmount)

✓ Model Answer

SELECT C.Name, SUM(O.TotalAmount) AS Total
FROM Customers C
JOIN Orders O ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerID, C.Name
HAVING SUM(O.TotalAmount) > 500;

Keywords Found:

Q16 10 marks

Write SQL queries to:
1. List all unique cities where customers live
2. Insert a new customer
3. Update a customer's address

✓ Model Answer

-- 1. Unique cities
SELECT DISTINCT City FROM Customers;

-- 2. Insert customer
INSERT INTO Customers (CustomerID, Name, City) 
VALUES (101, 'John Doe', 'Riyadh');

-- 3. Update address
UPDATE Customers 
SET Address = '123 Main St' 
WHERE CustomerID = 101;

Keywords Found:

📐 Normalization Problems 15 marks

▼

Q17 15 marks

Normalize the LIBRARY relation to 3NF:

LIBRARY (Branch#, Branch_Addr, ISBN, Title, Author, Pub_ID, Pub_Name, Pub_Address, Num_copies)

FD1: Branch# → Branch_Addr
FD2: ISBN → Title, Author, Pub_ID
FD3: Pub_ID → Pub_Name, Pub_Address
FD4: (ISBN, Branch#) → Num_copies

Show all steps and final relations.

✓ Model Answer

2NF Decomposition (eliminate partial dependencies):

BRANCH (Branch#, Branch_Addr)
BOOK (ISBN, Title, Author, Pub_ID, Pub_Name, Pub_Address)
INVENTORY (ISBN, Branch#, Num_copies)

3NF Decomposition (eliminate transitive dependencies):

BRANCH (Branch#, Branch_Addr)
PUBLISHER (Pub_ID, Pub_Name, Pub_Address)
BOOK (ISBN, Title, Author, Pub_ID) FK: Pub_ID
INVENTORY (ISBN, Branch#, Num_copies) FK: ISBN, Branch#

Keywords Found:

🎉 Your Exam Results

0/100

Multiple Choice

0/25

Written Questions

0/40

SQL Queries

0/20

Percentage