CS4750 - Database Systems

Spring 2026 — Assignment 2

Due 13-February-2026, 1pm EST (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 3 partners (max group size = 4). 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 = (A, B, C, D, E, F)
FDs = { A -> C, A -> D, B -> E, B -> D, A -> A, AB -> A, A -> F } 
```
1. (10 points) Using 3NF, decompose R into the proper tables — for full credits, show your steps
2. (10 points) Using BCNF, decompose R into the proper tables — for full credits, show your steps
[20 points] Given the following relation and functional dependencies:
```
R = (J, K, L, M, N)
FDs = { J -> JKLMN, K -> MN, N -> L, M -> N }
```
1. (10 points) Using 3NF, decompose R into the proper tables — for full credits, show your steps
2. (10 points) Using BCNF, decompose R into the proper tables — for full credits, show your steps
[30 points] Consider the following relation R and functional dependencies (FDs)
```
R = (A, B, C, D, E, F)
FDs = { C -> AB,  B -> B,  B -> D, F -> E } 
```
1. (10 points) Using 3NF, decompose R into the proper tables — for full credits, show your steps
2. (10 points) Using BCNF, decompose R into the proper tables — for full credits, show your steps
3. (10 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
[30 points] Consider the relation Courses(C, T, H, L, S, G), whose attributes may be thought of informally as course_number, teacher_id, hour, location, student_id, and grade. Let the set of FDs for Courses be
```
R = (C, T, H, L, S, G)
FDs = { C -> T, HL -> C, HT -> L, HS -> L, CS -> G }
```
Intuitively, we can interpret the FDs as
```
C -> T         a course has a unique teacher
HL -> C        only one course can meet in a given location at a given hour
HT -> L        a teacher can be in only one location at a given time (to teach a course)
HS -> L        a student can be in only one location at a given time (to attend a course)
CS -> G        a student gets only one grade in a course
```
1. (5 points) List all the minimal superkeys for the Courses relation
2. (10 points) Use 3NF, decompose the given Courses relation into proper relations — for full credits, show your steps
3. (10 points) Verify that the decomposed relations 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
4. (5 points) Are the decomposed relations from question 4.2 in BCNF?
  - If they are not, indicate all the BCNF violations and suggest how to solve the violations
  - If they are, explain or discuss to show that they are in BCNF
  For full credits, you must discuss both Lossless join and Redundancy free properties

Late submission

(-25 points) for 24 hours late (submitted after 13-Feb 1pm EST, by 14-Feb 1pm EST)
(-50 points) for 48 hours late (submitted after 14-Feb 1pm EST, by 15-Feb 1pm EST)

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-02-03 21:38