CS4750 - Database Systems

Fall 2026 — Assignment 2

Due 23-September-2026 25-September-2026, 10am (before class)

Purpose:

Understand the concepts of normalization and database design issues
Be able to evaluate and indicate 3NF and BCNF violations
Recognize when the tables need fine-tuning
Normalize tables
Demonstrate that the tables are in a certain normal form

For this assignment, you may work individually or with at most 2 partners (max group size = 3). We strongly encourage you to work with partners as different designers typically have different perspectives. Besides, you can learn from each other.

Answer the following questions / complete the following tasks. All homeworks are due before class on the due date. Please remember that the UVA Honor Code is in effect.

[Total: 100 points]

[20 points] Given the following relation and functional dependencies:
```
R = (J, K, L, M, N, O)
FDs = { J -> KM, K -> M,  M -> L, O -> N, K -> L } 
```
1. (10 points) Using BCNF, decompose R into the proper tables — for full credits, show your steps
2. (10 points) Using 3NF, decompose R into the proper tables — for full credits, show your steps
[20 points] Given the following relation and functional dependencies:
```
R = (A, B, C, D, E, G)
FDs = { A -> CD, B -> AC, C -> D, G -> E } 
```
1. (10 points) Using BCNF, decompose R into the proper tables — for full credits, show your steps
2. (10 points) Using 3NF, decompose R into the proper tables — for full credits, show your steps

[32 points] Consider the following table. The table displays sample information about UVA students (where columns A, B, C, D, and E represent record_id, computingID, student_name, credit_hours, and the amount that they paid the school for tuition and fees for a given semester.

A	B	C	D	E
1	ht1y	Humpty	15	1600
2	dt2y	Dumpty	12	1300
3	md3y	Mickey	18	1900
4	mn4e	Minnie	15	1600
5	ht1y	Humpty	16	1700
6	md3y	Mickey	13	1400

(4 points) Write a minimal set of functional dependencies (FDs) that logically imply all others. Include any assumptions you may have made. (hint: you should start by finding all functional dependencies that hold on the table)
(10 points) Consider the FDs from question 3.1, decompose the table into the proper tables using BCNF. For full credits, show your steps
(8 points) Verify that the decomposed relations from question 3.2 are in BCNF
- If they are not, indicate all the BCNF violations
- If they are, explain or discuss to show that the decomposed relations are in BCNF
For full credits, you must discuss both lossless join and redundancy free properties
(10 points) Consider the FDs from question 3.1, decompose the table into the proper tables using 3NF. For full credits, show your steps

[28 points] Consider the following relation R and functional dependencies (FDs)
```
R = (M, N, O, P, Q, R)
FDs = { M → NO, Q → MNR, O → OP, N → OP } 
```
1. (10 points) Using BCNF, decompose R into the proper tables — for full credits, show your steps
2. (10 points) Using 3NF, decompose R into the proper tables — for full credits, show your steps
3. (8 points) Verify that the decomposed relations from question 4.2 are in 3NF
  - If they are not, indicate all the 3NF violations
  - If they are, explain or discuss to show that the decomposed relations are in 3NF.
  For full credits, you must discuss both lossless join and dependency preserving properties

Late submission

(-25 points) for 24 hours late (submitted after 23-Sep 10am, by 24-Sep 10am)
(-50 points) for 48 hours late (submitted after 24-Sep 10am, by 25-Sep 10am)

(-25 points) for 24 hours late (submitted after 25-Sep 10am, by 26-Sep 10am)
(-50 points) for 48 hours late (submitted after 26-Sep 10am, by 27-Sep 10am)

To grade and record your assignment efficiently, the following details apply to all homework assignments, unless otherwise specified.

(-10 points) — For submitting a report in a Word document, handwriting, or hand drawing

Submission

Include all team member names and computingIDs in the report.

Embed all diagrams in a single report. Save your report as a PDF. No Word document. No handwriting.

Each team submits only one copy.

Upload your report as a PDF to the Assignment 2 on Gradescope. Make sure you connect your partner to your group on Gradescope so that everyone receives credit.

Making your submission available to the course staff is your responsibility; if we cannot access or open your file, we have to assign a zero grade. Be sure to test access to your file before the due date.

CC-BY-NC-SA 4.0 license.
Last updated 2026-09-13 20:42