CS4750 - Database Systems

Spring 2024 — Assignment 2

Due 19-February, 12pm 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 4 partners (max group size = 5). 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]

[40 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. (15 points) Using BCNF, decompose R into the proper tables — for full credits, show your steps
2. (15 points) Using 3NF, decompose R into the proper tables — for full credits, show your steps
3. (10 points) Verify that the decomposed relations from question 1.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. Be sure to discuss lossless join and dependency preserving properties.
[40 points] Consider the following relation R and functional dependencies (FDs)
```
R = (A, B, C, D, E, F)
FDs = { B → AC,  C → D,  F → E }
```
1. (15 points) Using BCNF, decompose R into the proper tables — for full credits, show your steps
2. (10 points) Verify that the decomposed tables from question 2.1 are in BCNF
  - If they are not, indicate all the BCNF violations
  - If they are, explain or discuss to show that they are in BCNF. Be sure to discuss lossless join and redundancy free properties.
3. (15 points) Using 3NF, decompose R into the proper tables — for full credits, show your steps
[20 points] Consider the relation Party(P, N, H, T, L, G), whose attributes may be thought of informally as party_id, party_name, host_id, time, location, and guest_id. Let the set of FDs for Party be
```
R = (P, N, H, T, L, G)
FDs = { P -> N, P -> H, TL -> P, TH -> L, TG -> L }
```
Intuitively, we can interpret the FDs as
```
P -> N         a party has a unique name
P -> H         a party has a unique host
TL -> P        only one party can be held in a given location at a given time
TH -> L        a host can be in only one location at a given time (to host a party)
TG -> L        a guest can be in only one location at a given time (to attend a party) 
```
1. (5 points) List all the minimal superkeys for the Party relation
2. (15 points) Using 3NF, decompose the given Party relation into proper relations — for full credits, show your steps

Late submission

(-25 points) for 24 hours late (submitted after 19-Feb 12pm (noon) EST, by 20-Feb 12pm (noon) EST)
(-50 points) for 48 hours late (submitted after 20-Feb 12pm (noon) EST, by 21-Feb 12pm (noon) 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.

Released under the Creative Commons License CC-BY-NC-SA 4.0 license.

Last updated 2024-02-10 11:42