CS 115 Database SystemsFall 2022, Exams of Database Programming

Download CS 115 Database SystemsFall 2022 and more Exams Database Programming in PDF only on Docsity!

Functional Dependencies

and Normalization

CS 115 Database Systems

Fall 2022

Redundancy makes DBs “brittle”

id item price customer email 1 bottled water $1 Katara [email protected] 2 ceiling fan $10 Aang [email protected] 3 bottled water $1 Aang [email protected] 4 sandbags $20 Aang [email protected] 5 matches $5 Aang [email protected] Purchases

Depends on Real-World Semantics

id item price customer email 1 bottled water $1 Katara [email protected] 2 ceiling fan $10 Aang [email protected] 3 bottled water $1 Aang [email protected] 4 sandbags $20 Aang [email protected] 5 matches $5 Aang [email protected] Purchases Is the email address redundant? It depends! ...do we want to know the customer’s current email address, or what the email address was at the time of purchase?

Functional Dependencies

● Relations (Tables)

• (^) R (A, B, C) ● (^) relation R with attributes A, B, C ●

Functional Dependencies (FDs)

• (^) {A, B} → C ● (^) {A, B} determines C ● (^) any rows with the same values for A and B must also have the same values for C
• (^) e.g., library_card_no → {patron_name, patron_email, etc.}

Keys and Superkeys

● Key

• (^) Attributes of a relation that functionally determine the rest
• (^) (We will understand “key” to mean a minimal key.) ● Superkey
• (^) Set of attributes that contain a key R (A, B, C, D) with A → B and {A, C} → D

{A, C} is a key

superkeys include {A, B, C} ; {A, C, D} ; {A, B, C, D} ; {A, C}

Closure

●

Functional Dependencies:

• (^) A → C
• (^) B → D
• (^) A, D → E
• (^) E, F → G ●

Closure:

• (^) {A, B}+
• (^) The closure of a superkey is all attributes in the relation.

{A, B}

A

B

C

D

E

Example

● Functional Dependencies

• (^) purchase_id → {item, customer}
• (^) item → price
• (^) customer → email ●

Is this split (schema) in BCNF? (Why or why not?)

• (^) Purchases (purchase_id, item, customer, email)
• (^) Products (item, price)

Practice

● Functional Dependencies

• (^) film_title → page_count
• (^) {film_title, scene_no} → shoot_date
• (^) actor_name → {email, pay_rate} ● Is this split (schema) in BCNF? (Why or why not?)
• (^) Actors (actor_name, email, pay_rate)
• (^) Scenes (film_title, scene_no, shoot_date)
• (^) ActorsInScenes (film_title, scene_no, actor_name)
• (^) Scripts (film_title, page_count)

Example

●

Attributes:

R (A, B, C, D, E, F, G)

●

Functional Dependencies:

A, B → C

B → D

A, D → E, F

Practice

●

Attributes:

R (user_name, email, meetup_name, description,

user_name, event_name, time, location, RSVP_limit)

●

Functional Dependencies:

user_name → email

meetup_name → description, user_name

meetup_name, time, location → event_name, RSVP_limit