1 / 25

Logical Model

Logical Model. Agenda - Informal Mapping ER-Diagram to Schemas - Functional Dependencies - Definition of ‘Good Design’ - Normalization (1NF, 2NF, 3NF, BCNF). Relational Model: definitions. - All data is stored in ‘ relations ’ - Relation  Table Columns: Attributes

elvis
Download Presentation

Logical Model

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. Logical Model Agenda - Informal Mapping ER-Diagram to Schemas - Functional Dependencies - Definition of ‘Good Design’ - Normalization (1NF, 2NF, 3NF, BCNF)

  2. Relational Model: definitions - All data is stored in ‘relations’ - Relation Table Columns: Attributes Rows: Tuples - Domain of an Attribute, A  allowed set of values of A - Relational Schema NAME( A1, A2, …, An) - Tuple, t, of R(A1, A2, …, An)  ORDERED set of values, < v1, v2, v3, …, vn> vi dom( Ai) - Relation Instance, r( R)  a set of tuples

  3. Relational model: example Student( Name, SID, Age, GPA)

  4. Constraints on Relational Schemas A. Domain constraints t[Ai]  dom( Ai), for all t, Ai B. Key constraints Superkey of R: A set of attributes, SK, of R such that t1[ SK] != t2[SK] whenever t1≠ t2 Key: minimal Superkey of R minimal: removal of any attribute from Key  no longer a Superkey of R

  5. Constraints on Relational Schemas.. A. Domain constraints B. Key constraints, examples: CAR( State, LicensePlateNo, VehicleID, Model, Year, Manufacturer) K1 = { State, LicensePlateNo} K1 is a minimal Superkey  Key K2 = { VehicleID } K2  Key (Why ?) K3 = { VehicleID, Manufacturer} Superkey ? Key ?

  6. Constraints on Relational Schemas.. A. Domain constraints B. Key constraints C. Entity Integrity constraints If PK is the Primary Key, then t[PK] != NULL for any tuple t  r( R) D. Referential constraints - All referential constraints must be defined - X(Ai) references Y(Bj)  dom(Ai) = dom(Bj) - Foreign Key attributes that reference a Primary Key Example:

  7. Informal Mapping of ER-diagram to Schemas 1. For each regular entity, E, One relation E with all the simple attributes of E. Select a primary key for E, and mark it. 2. For each binary relation type, R, between entity types, S and T: For 1:1 relationship between S and T Either add PK(S) as FK(T), or add PK(T) as FK(R) For 1:N relationship between S and T (S: the N-side) Add PK(T) as a foreign key in S. For M:N relationship, R, between S and T Create a new relation, R, with the PK’s of S and T as FK’s of P, plus any attributes of R

  8. Informal Mapping of ER-diagram to Schemas.. 3. For each weak entity type, W, whose identifying entity is E One relation W with all attributes of W and the primary key of E Mark the Primary Key 4. For each multi-valued attribute A, Create a new relation, R, including A, plus PK of the entity/relationship containing A 5. For each n-ary relationship, R, with degree > 2 Create a relation R, with PK of each participating entity as FK, plus all simple attributes of R

  9. Informal Mapping of ER-diagram to Schemas… Examples:

  10. Formal DB Design How can we tell if a DB design is ‘Good’ ? A DB Design is good if: (1) it provides a way to store all information in the system (2) the design is not bad How can we tell if a DB design is ‘Bad’ ?

  11. Bad DB Designs Example: (a) Information is stored redundantly (b) Insertion anomalies (c) Deletion Anomalies (d) Modification Anomalies

  12. Bad DB Designs.. - Avoid too many NULL values in tuples STUDENT( SID, Name, Phone, Email, SocietyName, MembershipNo) OR STUDENT( SID, Name, Phone, Email) MEMBERSHIP( SID, SocietyName, MembershipNo)

  13. Bad DB Designs.. - Spurious Tuples must not be created when ‘join’-ing tables Example: - Who supplied P2 to Proj2 ? -- the answer requires us to ‘join’ the two tables - Who supplied P1 to Proj2 ?

  14. Formal DB Design: Functional Dependencies A set of attributes, X, functionally determines a set of attributes Y if the value of X determines a unique value for Y. NOTATION: X  Y X  Y implies that for any two tuples, t1 and t2, if t1[X] = t2[X], then t1[ Y] = t2[ Y] Examples: {SSN}  {Employee name} {Employee SSN, Project Number}  {Hours per week}

  15. FD’s: Armstrong’s Rules A1. (Reflexive). If Y  X, then X  Y A2. (Augmentation). If X  Y, then XZ  YZ (XZ == X union Z) A3. (Transitive). If X  Y and Y  Z, then X  Z Common methods of proving: Construction, Induction, Contradiction Common methods of disproving: Construction, Counterexamples

  16. More Theorems about FD’s A4. (Decomposition). If X  YZ, the X  Y and X  Z A5. (Union). If X  Y and X  Z, then X  YZ A6. (Pseudotransitive). If X  Y and WY  Z, then WX  Z Definition: Two sets of FDs, F and G are said to be equivalent if every FD in F can be inferred from G, and every FD in G can be inferred from F. FD’s are critical in our definition of Normalized DB designs

  17. First Normal Form: 1NF A schema is in 1NF if it does not contain - any composite attributes, - any multi-valued attributes, - any nested relations Any non-1NF schema can be converted into a set of 1NF schemas STUDENT_COURSES_1NF Composite Multi-valued STUDENT_COURSES Not 1NF 1NF

  18. EMPLOYEE_PROJECTS Nested Projects SSN Lname Fname ProjNo Hours Not 1NF 1123 Smith John P1 10 P2 5 3312 Doe Jane P2 10 P3 5 EMP_PROJECTS EMPLOYEE SSN ProjNo Hours SSN Lname Fname 1123 P1 10 1123 Smith John 1123 P2 5 3312 Doe Jane 3312 P2 10 1NF 3312 P3 5 1NF..

  19. Second Normal Form, 2NF Prime Attribute: An attribute that is a member of the primary key Full functional Dependency: A FD, Y  Z, such that X  Z is false for all X  Y Full FD ? {SSN, PNumber}  {Hours} Full FD ? {SSN, PNumber}  EName

  20. EMP_PROJ1 Hours SSN PNumber EMP_PROJ EName PName PLocation SSN Pnumber Hours EMP_PROJ2 SSN EName EMP_PROJ3 PNumber PName PLocation 2NF.. A schema R is in 2NF if every non-prime attribute A in R is fully functionally dependent on the primary key. Any non-2NF schema can be converted into a set of 2NF schemas Not 2NF 2NF

  21. Third Normal Form, 3NF A Transitive Functional Dependency is an FD, Y  Z that can be derived from two FDs Y  X and X  Z. Examples: SSN  MgrSSN is a transitive dependency [SSN  DNumber, and DNumber  MgrSSN] SSN  LName is NOT a transitive dependency [there is no set of attributes X, s.t. SSN  X and X  LName]

  22. EMP_DEPT1 EName Address Dno SSN EMP_DEPT Dno Address MgrSSN EName DName SSN EMP_DEPT2 DNo DName MgrSSN 3NF.. A schema is in 3NF if - it is in 2NF, and - no non-prime attribute A in R is transitively dependent on the primary key. 3NF Not 3NF

  23. General normal forms Our previous definitions depend on our choice of the PK Problem ? General 1NF: -same as before- A schema is in general 2NF if: - it is in 1NF, and - every non-prime attribute FFD on every key of R. A schema is in general 3NF if: - whenever a FD X  A holds in R, then either X is a superkey of R, or A is a prime attribute of R.

  24. LOTS Area Lot# TaxRate PropertyID District Price FD1 FD2 FD3 FD4 LOTS1 Area Lot# PropertyID District Price FD1 FD2 FD4 LOTS2 LOTS2 District District TaxRate TaxRate LOTS1B LOTS1A Area Lot# Area Price PropertyID District FD1 FD2 General normal forms.. Example: Property Lots DB 1NF Keys? 2NF 3NF

  25. Boyce Codd Normal Form, BCNF Slightly stricter than the general 3NF definition: BCNF, 1NF: -same as before- A schema is in BCNF, 2NF if: - it is in 1NF, and - every non-prime attribute FFD on every key of R. A schema is in BCNF, 3NF if: - whenever a FD X  A holds in R, then X is a superkey of R

More Related