introduction slide 109 entity relationship model
play

Introduction slide 109 Entity-Relationship - PowerPoint PPT Presentation

Introduction slide 109 Entity-Relationship model slide 114 E/R relational mapping slide 119 Reverse engineering slide 131 slide 136


  1. ������� �������� ������ � 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

  2. ����������� �������� ������ �� ���������� �������� �������!"��� 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

  3. ��� ����� ��� �����!�% 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

  4. *������ (-�� � ���������� /��� ��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

  5. (#����� ���� � /��� &�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

  6. /��� +�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

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

Download Presentation
Download Policy: The content available on the website is offered to you 'AS IS' for your personal information and use only. It cannot be commercialized, licensed, or distributed on other websites without prior consent from the author. To download a presentation, simply click this link. If you encounter any difficulties during the download process, it's possible that the publisher has removed the file from their server.

Recommend


More recommend