CS4750 - Database Systems

POTD 1: FD, F+, Normalization

Due 6-Feb-2024, 12pm/noon EST

Purpose:

Understand the concepts of normalization
Understand normalization and database design issues
Indicate BCNF and 3NF violations
Determine when a relation needs to be decomposed for database schema refinement

You may make a copy of a worksheet and complete this activity, or simply type your answers in any text editor.

You may work alone or with 3-4 students (max size=5) in this course.

Expand / Collapse all

Given a relation R(title, year, length, genre, studioName) and a set of FDs
```
title year -> length
title year -> genre
title year -> studioName   
```
Are the above FDs equivalent to the following/single FD?
```
title year -> length genre studioName
```
Yes or No?
```
Yes 
```

Given a relation R(title, year, length, genre, studioName) and the following FD:

title year -> length

Is the above FD equivalent to the following FDs?

title -> length
year -> length

Yes or No?

No
Title does not functionally determine length, 
since there can be multiple movies with the same title 
(e.g., King Kong) but of different lengths. 
Similarly, year does not functionally determine length, 
because there are movies of different lengths made in 
any particular year.

[optional] Consider a relation about people in the United States, including their name, Social Security number, street address, city, state, ZIP code, (phone) area code, and phone number (7 digits). What FD's would you expect to hold? What are the possible candidate keys for the relation?

To answer this question, you need some real world facts. For example, can the same (phone) area code be used in two states? Can the same ZIP code be associated with two (phone) area codes? Can two people have the same Social Security number? Can they have the same address or phone number?
```
Some possible FDs:
   Social Security number -> name
   Area code -> state
   Street address, city, state -> zipcode

One possible candidate key:
   (Social Security number, street address, city, state, area code, phone number)

Need street address, city, state to uniquely determine location. 
A person could have multiple addresses. 
The same is true for phones. 
These days, a person could have a land line and a cellular phone
```

Consider the following relation and functional dependencies

R(A, B, C, D)
FDs = { A -> B, B -> C, B -> D }

Computer F+

Write all LHS and remaining
   A ->
   B -> 
   C -> 
   D -> 
Copy FDs as is
   A ->  B
   B ->   CD
   C ->
   D ->
Apply reflexivity
   A -> AB
   B ->  BCD
   C ->   C
   D ->    D
Apply transitivity
   A -> ABCD   
   B ->  BCD
   C ->   C
   D ->    D

Thus, F+ = { A->ABCD, B->BCD, C->C, D->D }

Consider the following relation and functional dependencies

R = (A, B, C, D, E, F)
FDs = { B -> AC, C -> D, F -> E }

Computer F+

Write all LHS and remaining
   A ->
   B -> 
   C -> 
   D -> 
   E ->
   F ->
Copy FDs as is
   A ->  
   B -> A C
   C ->    D
   D ->
   E -> 
   F ->     E
Apply reflexivity
   A -> A
   B -> ABC
   C ->   CD
   D ->    D
   E ->     E
   F ->     EF
Apply transitivity
   A -> A   
   B -> ABCD
   C ->   CD
   D ->    D
   E ->     E
   F ->     EF
   
Thus, F+ = { A->A, B->ABCD, C->CD, D->D, E->E, F->EF }

[optional] Give a scenario in which you would choose to decompose a table using 3NF (as opposed to BCNF)
```
(Any examples related to needing the dependency preserving property)
```
[optional] Give a scenario in which you would choose to decompose a table using BCNF (as opposed to 3NF)
```
(Any examples related to needing the redundancy free property)
```

Expand / Collapse all

Grading rubric

[Total: 10 points]: Done (or provide evidence of your attempt, full or reasonable effort)

(5 points) — Providing evidence of your attempt, minimal effort

(-2.5 points) for 24 hours late (submitted after 6-Feb-2024 12pm EST, by 7-Feb-2024 12pm EST)
(-5 points) for 48 hours late (submitted after 7-Feb-2024 12pm EST, by 8-Feb-2024 12pm EST)

Submission

[optional] Take a selfie (or picture) of your team and submit it with your POTD
Include all team member names and computingIDs in the report.
You may do one of the following:
- take screenshot(s) of your POTD, or
- save your POTD as a .txt or .pdf file — No Word document.
Each team submits only one copy.
Upload your report as a PDF to POTD 1 on Gradescope. Make sure you connect your partner to your group on Gradescope so that everyone receives credit.
Please verify that your POTD is accessible. Making your submission available to instructors and course staff is your responsibility; if we cannot access or open your file(s), we have to assign a zero grade. Be sure to test access to your file(s) before the due date.

CC-BY-NC-SA 4.0 license.
Last updated 2024-02-03 16:23