1 / 23

ECIS466: Database Management LOGICAL DESIGN

ECIS466: Database Management LOGICAL DESIGN. Traditional Systems Development Lifecycle. Planning. Analysis. Logical Design. Physical Design. Implementation. Steps in Logical Database Design. Conceptual Data Model. Represent entities as relations. Represent relationships as relations.

tamra
Download Presentation

ECIS466: Database Management LOGICAL DESIGN

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. ECIS466: Database Management LOGICAL DESIGN

  2. Traditional Systems Development Lifecycle Planning Analysis Logical Design Physical Design Implementation

  3. Steps in Logical Database Design Conceptual Data Model Represent entities as relations Represent relationships as relations Normalize Relations Merge relations Logical Data Model (relational)

  4. Normalization • “Process of converting complex data structures into simple, stable data structures • Most critical concept in logical database design • Accomplished in stages through a sequence of normal forms • Normal forms are the state of a relation based on its functional dependencies

  5. Functional Dependency • Basis for normalization • Definition (for a given relation/table) • if for every valid instance of attribute A • the value of A uniquely determines the value of another attribute B • B is functionally dependent on A B A

  6. Normal Forms (5+1) • First Normal Form (1NF) • contains no repeating groups • based on property of atomic values

  7. Second Normal Form (2NF) • A relation is in 2NF if • it is in 1NFAND • any one of the following conditions is true • primary key consists of one attribute • no non-key attributes exist • every non-key attribute is functionally dependent on the full set of primary keys (composite key)

  8. The remaining normal forms

  9. Third Normal Form (3NF) • A relation is said to be 3NF if • it is in 2NFAND • there are no transitive dependencies • A transitive dependency occurs when there is a functional dependence relationship between non-key fields

  10. Example of 3NF • Consider the relation SALES(Cust#, Name, Salesperson, Region) • It is in second normal form (why?) • It has transitive dependencies (where?)

  11. Boyce-Codd Normal Form (BCNF) • We have only considered cases where there is one possible key • Some cases, there is a choice as to what to use as the primary key • These choices are called candidate keys • BCNF is based on functional dependencies that take into account all candidate keys

  12. Formally stated... • A relation is said to be in Boyce-Codd Normal Form (BCNF) if, andonly if, every determinant is a candidate key. • Every relation in BCNF is also in 3NF. • Every relation in 3NF is not necessarily in BCNF.

  13. Example.... • St-Maj-Adv (SSN#, Major, Advisor) • Functional dependencies • SSN#, Major -------------> Advisor • Advisor -----------> Major • Two candidate keys • SSN#, Major • SSN#, Advisor • No single attribute is enough

  14. So, what do we do? • Arbitrarily choose (SSN#, Major) as the key • St-Maj_Adv (SSN#, Major, Advisor) • Clearly this is in 3NF, but.... • What happens if a student changes major modification anomaly • What happens if we want to add Turing as an advisor for CS (not possible without a student enrolled in CS) - addition anomaly • What if student 456 drops from school? - -- deletion anomaly

  15. So, we break it down... ST-ADV (SSN#, Advisor) ADV-MAJ (Advisor, Major) BCNF problems typically arises when candidate keys overlap

  16. Another Example of BCNF Client_Interview (Client#, Interview-date, Interview-Time, Staff#, Room#) Two possible candidate keys: 1. (Client#, Interview-date) 2. (Staff#, Interview-Date, Interview-Time)

  17. Functional Dependencies Client#, Interview Date Staff#, Interview-Time, Room# Staff#, Interview Date, Interview-Time Client# Room# Staff#, Interview Date

  18. Break it down... • Interview (Client#, Interview-Date, Interview-Time, Staff#) • Staff_Room (Staff#, Interview-Date, Room#)

  19. Other Normal Forms • Fourth Normal Form (4NF) • Fifth Normal Form (5NF) • Domain-Key Normal Form (DKNF) Usually, 3NF is sufficient to protect against most anomalies

  20. Process of Normalization Unnormalized form (UNF)

  21. Process of Normalization Unnormalized form (UNF) remove repeating groups First Normal Form (1NF)

  22. Process of Normalization Unnormalized form (UNF) remove repeating groups First Normal Form (1NF) remove partial dependencies Second Normal Form

  23. Process of Normalization Unnormalized form (UNF) remove repeating groups First Normal Form (1NF) remove partial dependencies Second Normal Form remove transitive dependencies Third Normal Form

More Related