Normalization Exercise

Normalization Exercise A

• Functional Dependencies

  • UPC -> Name, Buying_Price, Selling_Price, Size, Manufacturer_Name
  • Manufacturer_Name -> MStreet, MCity, MState, MZip, MPhone
  • Warehouse_Name -> WLocation, WCity, WState, WZip, WSpace
  • UPC, Warehouse_Name -> Quantity

• Relational Model

  • PRODUCT(UPC, Name, Buying_Price, Selling_Price, Size, Manufacturer_Name, MStreet, MCity, MState, MZip, MPhone, Warehouse_Name, WLocation, WCity, WState, WZip, WSpace, Quantity)

--> 일단 First Normal Form이고, partial functional dependency가 존재 (Name이 UPC, Warehouse_Name 둘 다 필요 없고, UPC에 의해서만 정해져서)
--> UPC로 새로운 relation인 Product2를 만들고 종속적인 애들을 다 데리고옴
--> Warehouse_Name에 종속적인 애들을 다 데리고옴
--> 데리고온 애들을 지움

• 2NF Normalization
  • PRODUCT(UPC, Name, Buying_Price, Selling_Price, Size, Manufacturer_Name, MStreet, MCity, MState, MZip, MPhone)
  • WAREHOUSE(Warehouse_Name, WLocation, WCity, WState, WZip, WSpace)
  • PRODUCT_QUANTITY(UPC, Warehouse_Name, Quantity)

--> 현재는 second normal form인데 non-primary-key가 또 다른 non-primary-key를 결정하는 transitive functional dependency가 존재
--> transitive functional dependency를 야기하는 non-primary-key를 primary-key로 하는 relation을 만들고 종속적인 애들을 다 데리고옴
--> 데리고온 애들을 지우고 foreign key의 역할을 하는 것을 남김

• 3NF Normalization
  • MANUFACTURER(Manufacturer_Name, MStreet, MCity, MState, MZip, MPhone)
  • PRODUCT(UPC, Name, Buying_Price, Selling_Price, Size, Manufacturer_Name)
    • Foreign Key(Manufacturer_Name) references MANUFACTURER(Manufacturer_Name)
  • WAREHOUSE(Warehouse_Name, WLocation, WCity, WState, WZip, WSpace)
  • PRODUCT_QUANTITY(UPC, Warehouse_Name, Quantity)

---

Normalization Exercise B

• Functional Dependencies
  • CID -> CName, CAddress, CPhone
  • Film_id -> Title, Rate, Type
  • Type -> Price
  • Film_id, Tape_Num -> Condition
  • CID, Film_id, Tape_Num -> Due_Date
• Relational Model
  • BB(CID, CName, CAddress, CPhone, Film_id, Tape_Num, Title, Rate, Condition, Type, Price, Due_Date)

--> first normal form인데, partial functional dependency 존재

• 2NF Normalization
  • CC(CID, CName, CAddress, CPhone)
  • DD(Film_id, Title, Rate, Type, Price)
  • EE(Film_id, Tape_Num, Condition)
  • BB(CID, Film_id, Tape_Num, Due_Date)

--> second normal form인데, transitive functional dependency 존재(Type -> Price)

• 3NF Normalization
  • CC(CID, CName, CAddress, CPhone)
  • DD(Film_id, Title, Rate, Type)
  • FF(Type, Price)
    • Foreign Key(Type) references DD(Type)
  • EE(Film_id, Tape_Num, Condition)
  • BB(CID, Film_id, Tape_Num, Due_Date)

---

Normalization Exercise C

• Functional Dependencies
  • A,B -> C
  • A -> D, E
  • B -> F
  • F -> G, H
  • D -> I, J
• Relational Model
  • R(A, B, C, D, E, F, G, H, I, J)

질문: 2NF, 3NF일 때 R의 primary key는 무엇일까?
--> first normal form인데, partial functional dependency가 존재

• 2NF Normalization
  • R1(A, D, E, I, J)
  • R2(B, F, G, H)
  • R(A, B, C)

--> second normal form인데, transitive functional dependency가 존재

• 3NF Normalization
  • R1(A, D, E)
  • R11(D, I, J)
    • Foreign Key R11(D) references R1(D)
  • R2(B, F)
  • R22(F, G, H)
    • Foreign Key R22(F) references R2(F)
  • R(A, B, C)

---

• 사진 출처
  scaler topics