������� �������� ������ � Introduction slide 109 � Entity-Relationship model slide 114 ���� � E/R � relational mapping slide 119 � Reverse engineering slide 131 slide 136 � Generalisation – specialisation ��� ��� ���������������������� �������� ���������������� �������� ������ Introduction Introduction ������� Reality Universal Functional relation dependencies Conceptual Schema Decomposition Mapping Normalized relational Relational Codasyl schema …. Files Schema Schema ��� ��� DBMS Database Design - 1
����������� �������� ������ �� ���������� �������� �������!"��� Introduction Introduction �#������ � $��� % Global conceptual Schema Heterogeneous/homogeneous Local schema Local schema Local schema Local Local Local …. Schema Schema Schema Integration User Program … Global schema ��� ��� �������� ������ (����)����������$�� *���� Introduction E/R model MiniWorld � defined in 1976 (Chen) DBMS-independent Requirements collection and analysis � Numerous extensions since 1976 Database requirements � Advantages: Conceptual design � Used in numerous design methods (MCD Merise, UML Class diagram …) Conceptual schema (high level) � Simple Data model mapping DBMS-dependent � Graphical Conceptual schema (of a specific DBMS) � Ease discussion with users Physical design Internal schema (for the same DBMS) ��& ��' DBMS Database Design - 2
��� ����� ��� �����!�% E/R Model E/R Model Graphism 1 (E/R) Graphism 2 (AMCDesignor) Entity type T Name Type Entity Attribute A Graphism 1 Graphism 2 Composite Weak entity Attribute type (1,1) Ass Multivalued Identifying Attribute relationship Key attributes Derived A attribute Relationship Ass Ass type Role Role Role Cardinality 1,n 0,n ratio ��+ ��, ������) (-������� ������) (-������� E/R Model E/R Model .���$��� � fname lname 0,n Employee supervisor Works_for Department 1,1 SSN address name 1,n Dept_number deptname locations Emp_nb Nssn Supervision Dept_number Address supervisee Dept_name Works_for fname 1,1 Emp_nb 0,1 lname supervisee 1,1 locations 1,1 Employee 0,n Department manages 1,1 0,1 0,n supervisor 1,n startdate 1,n 1,1 0,n 0,n 1,n Manages 0,n supervision Dependents_of startdate controls Works_on controls Dependents_of hours Works_on (1,1) Dependent name 1,n 1,1 1,1 1,1 hours birthdate Project 1,n relationship Dependent Project number Description birthdate relationship name number Description ��� ��� DBMS Database Design - 3
*������ (-�� � ���������� /��� ��0� mapping non weak entity Mapping mapping types � Semantic is not completely preserved (we � Entity type � relation have to add integrity constraints) � Atomic Attribute � attribute � Rules can be automated (numerous commercial tools exist, AMCDesignor for � Composite Attributes � n attributes example) � Key(s) Attribute(s) � logical key � Mapping is done in 7 steps � Comparison of E/R concepts and relational concepts: To do E E(K, A) K A ��� ��� (#������ ���� ��� /��� ��0�������� �"�1��2 �������� mapping mapping � Weak entity type � relation Employee Atomic Attributes Employee ( ssn, address ) � Atomic Attribute � attribute Composite attributes Employee(ssn, address, fname, lname ) � Composite Attributes � n attributes Composite attributes are flatten � loss of semantic � Key(s) Attribute(s) � part of logical key Department Atomic Attributes Department ( no_dept, libelle ) � Key Attributes from identifying entity � part Multivalued attributes step 6 : location of logical key Derived Attributes step 7 : emp_nb E E2 (1,1) E2(K, K2, A2) Project Atomic Attributes Project ( number, description ) K K2 A A2 ��� ��� DBMS Database Design - 4
(#����� ���� � /��� &�0 ������� �"������3����� �����) mapping mapping ���������$��� � Key associated to E1 � attribute of E2 � Attributes of relationship RS � attributes of Entity dependent Atomic Attributes Dependent ( name, birthdate, relationship ) E2 + Dependent(name, ssn , birthdate, relationship) Identifying entity key ssn: E1 E2 ?,? RS ?,1 Foreign key on Employee E2(K2, A2, K1, A3 ) K1 K2 A3 + A1 A2 Part of Dependent key Foreign key, but not key of E2 ��& ��' /��� '�0�������� �����3����� �����) (#������ ���� & mapping mapping ���������$��� !������$ ���� �����% � Creation of a new relation RS Works_for Employe (ssn, address, fname, lname, deptnb ) � E1 key + E2 key � RS key Manages Department (dept_nb, dept_name, ssnmgr, startdate ) � Attributes of RS � Attributes of RS Manages mono-valued in both directions � other possible mapping Employee(ssn, address, fname, lname, deptnb , manageddeptnb, startdate ) Cardinality (0,1), partial relationship � null values E1 E2 ?,n ?,n RS Controls Project(number, description, deptnum ) RS(K1, K2, A3) K1 K2 A3 A1 A2 Supervision Employee(ssn, address, fname, lname, deptnb , ssnsupervisor ) ssnsuperrvisor foreign key of Employé on Employee Works_on Works_on(ssn, projectnumber, hours) Dependents_of See step 2 ��+ ��, DBMS Database Design - 5
/��� +�0�������� �"�����) ���������$��� /��� ,�0�������� �"������3����� mapping mapping !��4��% ���������� � like step 4 : � Creation of a new relation R � Creation of a new relation RS � Multivalued Attribute -> attribute � E1 key + E2 key + … En key � RS key RS � Key of Associated entity type -> attribute Attributes � RS Attributes � key of the new relation: the whole schema E1 E2 RS RS(K1, K2, K3, A4) K1 K2 A R C R(A, C) A4 A1 A2 E3 Locations in Department Location (location, dept_nb) K3 A3 ��� ��� /��� ��0�������� �"�����3�� ���������� (#����� ������) mapping mapping Employee(ssn, address, fname, lname, deptnb , ssnsupervisor ) � Derived attribute Department(dept_nb, dept_name, ssnmgr , startdate) � Associated query Project(number, description, deptnb ) Dependent(name, ssn , birthdate, relationship) Works_on( ssn, projectnumber , hours) Location(location, dept_nb ) Emp_nb in Department(dept_nb, dept_name, ssnmgr, startdate ) Department SELECT dept_nb, COUNT(*) SELECT deptnb, COUNT(*) FROM Employee FROM Employee GROUP BY dept_nb GROUP BY deptnb Query may be associated to a relational view ��� �&� DBMS Database Design - 6
��3���������������� ��3��������������������� ����� Reverse engineering � Goal: Reverse of � Map a relational schema to a entity-relationship schema � Relation without a foreign key : Step 1 � Why ? entity type � Database design has not been done or is lost � Relation with a foreign key outside � how ? its key : entity type and Step 3 � Apply mapping steps « in reverse order » monovalued relationship to the � Remark entity type corresponding to the � There is not a unique solution (loss of information on the foreign key relational schema compared to E/R schema) �&� �&� ��3�����������������!�% *�3�� �������� �#����� � Relation with a key Reverse of � Movies(id, title, yr, score, votes, director ) exclusively composed by Step 4 and foreign keys : multivalued � Directors(id, name) relationship among entity 5 � Actors(id, name) types corresponding to foreign keys � Castings( movieid, actorid , ord) � Relation with a key composed by a foreign key Step 2 and a local key : weak entity type identifyed by entity type corresponding to the foreign key �&& �&' DBMS Database Design - 7
Recommend
More recommend