1 / 27

Database Design

Database Design. GET/INT – Computer Science Dept. Contents. Introduction slide 3 Entity-Relationship model slide 8 E/R  relational mapping slide 13 Reverse engineering slide 25. Relational Schema. Codasyl Schema. …. Files. Introduction. Data model-independent Database design.

sadie
Download Presentation

Database 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. Database Design GET/INT – Computer Science Dept

  2. Contents • Introduction slide 3 • Entity-Relationship model slide 8 • E/R  relational mapping slide 13 • Reverse engineering slide 25 Database Design

  3. Relational Schema Codasyl Schema …. Files Introduction Data model-independent Database design Reality Conceptual Schema Mapping Database Design

  4. Universal relation Functional dependencies Introduction Relational-based database design Decomposition Normalized relational schema Database Design

  5. Introduction Centralized database design Global conceptual Schema Local Schema Local Schema Local Schema …. User Program … Database Design

  6. Local schema Local schema Local schema Introduction Decentralized database design (from existing schema) Heterogeneous/homogeneous Integration Global schema Database Design

  7. Introduction Database design MiniWorld DBMS-independent Requirements collection and analysis Database requirements Conceptual design Conceptual schema (high level) Data model mapping DBMS-dependent Conceptual schema (of a specific DBMS) Physical design Internal schema (for the same DBMS) Database Design

  8. E/R model Entity-Relationship Model • defined in 1976 (Chen) • Numerous extensions since 1976 • Advantages: • Used in numerous design methods (MCD Merise, UML Class diagram …) • Simple • Graphical • Ease discussion with users Database Design

  9. E/R Model Concepts T Name Type Entity A A Ass Ass Role Role 1,n 0,n Database Design

  10. E/R Model Concepts (2) (1,1) Ass Database Design

  11. fname lname SSN address name locations Dependents_of E/R Model Company E/R model Dept_number deptname Emp_nb Works_for supervisee 1,1 1,1 Employee Department 0,n 0,1 supervisor 0,n Manages 1,1 0,n supervision 0,n 1,n startdate controls Works_on 1,1 1,1 hours 1,n Dependent Project birthdate relationship name number Description Database Design

  12. E/R Model Company E/R modelGraphism 2 0,n Employee supervisor Works_for Department 1,1 1,n Nssn Supervision Dept_number Address supervisee Dept_name fname Emp_nb 1,1 0,1 lname 1,1 locations manages 0,n 1,n 1,n startdate Dependents_of controls Works_on hours (1,1) Dependent name 1,1 1,n birthdate Project relationship number Description Database Design

  13. Mapping Mapping E/R  relational • Semantic is not completely preserved (we have to add integrity constraints) • Rules can be automated (numerous commercial tools exist, AMCDesignor for example) • Mapping is done in 7 steps • Comparison of E/R concepts and relational concepts: To do Database Design

  14. mapping Step 1 : mapping non weak entity types • Entity type  relation • Atomic Attribute  attribute • Composite Attributes  n attributes • Key(s) Attribute(s)  logical key E K A E(K, A) Database Design

  15. mapping Examples step 1 Database Design

  16. mapping Step 2 : mapping of weak entities • Weak entity type  relation • Atomic Attribute  attribute • Composite Attributes  n attributes • Key(s) Attribute(s)  part of logical key • Key Attributes from identifying entity  part of logical key E K A E2 K2 A2 (1,1) E2(K,K2, A2) Database Design

  17. mapping Example step 2 Database Design

  18. mapping Step 3 :mapping of mono-valued binary relationships • Key associated to E1  attribute of E2 • Attributes of relationship RS attributes of E2 E1 K1 A1 E2 K2 A2 ?,? RS A3 ?,1 E2(K2, A2, K1, A3) Foreign key, but not key of E2 Database Design

  19. mapping Examples step 3 Database Design

  20. mapping Step 4 : mapping multivalued binary relationships (on both directions) • Creation of a new relation RS • E1 key + E2 key  RS key • Attributes of RS  Attributes of RS E1 K1 A1 E2 K2 A2 ?,n RS A3 ?,n RS(K1,K2, A3) Database Design

  21. mapping Step 5 : mapping of n-ary relationships (n > 2) • like step 4 : • Creation of a new relation RS • E1 key + E2 key + … En key  RS key RS Attributes  RS Attributes E1 K1 A1 E2 K2 A2 RS A4 RS(K1,K2, K3, A4) E3 K3 A3 Database Design

  22. A mapping Step 6 : mapping of multivalued attributes • Creation of a new relation R • Multivalued Attribute -> attribute • Key of Associated entity type -> attribute • key of the new relation: the whole schema C R R(A,C) Database Design

  23. mapping Step 7 : mapping of derived attributes • Derived attribute • Associated query Database Design

  24. mapping Example summary Employee(ssn, address, fname, lname, deptnb, ssnsupervisor) Department(dept_nb, dept_name, ssnmgr, startdate, emp_nb) Project(number, description, deptnb) Dependent(name, ssn, birthdate, relationship) Works_on(ssn, projectnumber, hours) Location(location, dept_nb) SELECT deptnb, COUNT(*) FROM Employee GROUP BY deptnb Database Design

  25. Reverse engineering Reverse engineering • Goal: • Map a relational schema to a entity-relationship schema • Why ? • Database design has not been done or is lost • how ? • Apply mapping steps « in reverse order » • Remark  • There is not a unique solution (loss of information on the relational schema compared to E/R schema) Database Design

  26. Relation without a foreign key : entity type Relation with a foreign key outside its key : entity type and monovalued relationship to the entity type corresponding to the foreign key Reverse of Step 1 Step 3 Reverse engineering principles Database Design

  27. Relation with a key exclusively composed by foreign keys : multivalued relationship among entity types corresponding to foreign keys Relation with a key composed by a foreign key and a local key : weak entity type identifyed by entity type corresponding to the foreign key Reverse of Step 4 and 5 Step 2 Reverse engineering (2) Database Design

More Related