1 / 14

Advanced Data Modeling

Advanced Data Modeling. Why Needs A Database. To maintain consistency and integrity in your data by reducing redundancy and eliminating anomalies via Data Modeling and Database Design. Normalization. All attributes must be single-valued . First Normalization .

bruno-solomon
Download Presentation

Advanced Data Modeling

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Advanced Data Modeling

  2. Why Needs A Database • To maintain consistency and integrity in your data by reducing redundancy and eliminating anomalies via Data Modeling and Database Design.

  3. Normalization • All attributes must be single-valued. • First Normalization. • All attributes must be dependent on its entity’s entire UID. • Second Normalization. • No non-UID attribute can be dependent on another non_UID attribute. • Third Normalization.

  4. LEAVE EMPLOYEE EMPLOYEE #* id * name #* type #* start_date #*end_date • #* id • Name • 。leave_type • * leave_date 1st Normalization • All attributes must be single-valued. (1) To resolve, add an entity LEAVE #* employee_id

  5. 1st Normalization Ex. 差假系統: 記錄員工請假資料 EMPLOYEE id name phone address leave_type leave_date Has multi-values Repeat Group

  6. PROD_ITEM ORDER ORDER #* id * status *date #* id * name *description #* id * status 。date * prod_item 1st Normalization(續) (2) To resolve, add an entity PROD_ITEM #* order_id

  7. 1st Normalization(續) • The above process is called 1st normalization. The final result is said to be in 1NF(Normalization Form). • Conclusion: • If an attribute has multi-valued attribute, create an additional entity and relate it to the original entity with M:1 relationship.

  8. COURSE ACCOUNT PRODUCT #* id * name 。fee * duration #* number * balance 。date_opened * bank_location #* name #* warehouse 。Warehouse_ location 2nd Normalization • All attributes must be dependent on its entity’s entire UID. OK Not dependent on its UID

  9. 2nd Normalization(續) • The above process is called 2nd normalization. The final result is said to be in 2NF(Normalization Form). • Conclusion: • If an attribute is not dependent on its entire UID, it is misplaced and must be moved.

  10. TUTOR #* id * name *department * dept_location *dept_phone 3rd Normalization • No non-UID attribute can be dependent on another non_UID attribute. (1) dept_location(系辦位置) and dept_phone(系辦電話)are dependent upon Department(系)

  11. TUTOR DEPARTMENT #* id * name #* name * location * phone 3rd Normalization(續) Way to resolve:

  12. EMPLOYEE #* id * name *work_depart * mgrno *admrdept *location 3rd Normalization(續) (2) mgrno(主管代號) , Admrdept(主管部門)and Location( 部門地點) are dependent upon work_depart(工作部門)

  13. EMPLOYEE DEPARTMENT #* id * name #* id * name * mgrno * admrdept * location 3rd Normalization(續) Way to resolve:

  14. 3rd Normalization(續) • The above process is called 3rd normalization. The final result is said to be in 3NF(Normalization Form). • Conclusion: • If an attribute is dependent upon a non_UID attribute, move both the dependent attribute and the attribute it is dependent upon to a new related entity.

More Related