 Download Download Presentation Logical Model

# Logical Model

Télécharger la présentation ## Logical Model

- - - - - - - - - - - - - - - - - - - - - - - - - - - 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. 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’ ?

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

11. 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)

12. 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 ?

13. 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}

14. 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

15. 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

16. 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

17. 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..

18. 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

19. 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

20. 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]

21. 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

22. 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.

23. 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

24. 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