280 likes | 424 Views
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.
E N D
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 Database Design
Relational Schema Codasyl Schema …. Files Introduction Data model-independent Database design Reality Conceptual Schema Mapping Database Design
Universal relation Functional dependencies Introduction Relational-based database design Decomposition Normalized relational schema Database Design
Introduction Centralized database design Global conceptual Schema Local Schema Local Schema Local Schema …. User Program … Database Design
Local schema Local schema Local schema Introduction Decentralized database design (from existing schema) Heterogeneous/homogeneous Integration Global schema Database Design
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
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
E/R Model Concepts T Name Type Entity A A Ass Ass Role Role 1,n 0,n Database Design
E/R Model Concepts (2) (1,1) Ass Database Design
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
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
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
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
mapping Examples step 1 Database Design
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
mapping Example step 2 Database Design
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
mapping Examples step 3 Database Design
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
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
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
mapping Step 7 : mapping of derived attributes • Derived attribute • Associated query Database Design
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
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
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
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