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.

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?
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?
[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?
Consider the following relation and functional dependencies
```
R(A, B, C, D)
FDs = { A -> B, B -> C, B -> D }
```
Computer F+
Consider the following relation and functional dependencies
```
R = (A, B, C, D, E, F)
FDs = { B -> AC, C -> D, F -> E } 
```
Computer F+
[optional] Give a scenario in which you would choose to decompose a table using 3NF (as opposed to BCNF)
[optional] Give a scenario in which you would choose to decompose a table using BCNF (as opposed to 3NF)

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