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
Entity-Relationship model
E/R relational mapping
Reverse engineering
Generalisation – specialisation
Conceptual Schema Relational Schema Codasyl Schema Files …. Mapping Introduction
relation Functional dependencies Decomposition Normalized relational schema Introduction
Schema Local Schema Local Schema …. Local Schema Program User … Introduction
Integration Global schema Local schema Local schema Local schema Heterogeneous/homogeneous Introduction
&
Introduction MiniWorld Requirements collection and analysis Database requirements Conceptual design Conceptual schema (high level) Data model mapping Conceptual schema (of a specific DBMS) Physical design Internal schema (for the same DBMS) DBMS-independent DBMS-dependent
'
defined in 1976 (Chen) Numerous extensions since 1976 Advantages:
Used in numerous design methods (MCD Merise,
Simple Graphical Ease discussion with users
E/R model
+
E/R Model
Graphism 2 (AMCDesignor) Graphism 1 (E/R) Cardinality ratio Role Relationship type Key attributes Multivalued Attribute Composite Attribute Attribute Entity type
Name Type Entity T A A Ass Ass Role Role 0,n 1,n ,
E/R Model
Graphism 2 Graphism 1 Derived attribute Identifying relationship Weak entity type
Ass (1,1)
E/R Model
Employee name address SSN fname lname Dependent Project Department hours Dept_number deptname number Description name locations Dependents_of Manages Works_for Works_on controls startdate
0,n 1,n 0,1 0,n 1,1 1,1 1,1 1,n 1,1 0,n
Emp_nb supervision
supervisor 0,n supervisee 1,1
birthdate relationship
E/R Model
1,n 1,n 1,1 supervisee 0,n supervisor (1,1) 0,n 1,1 1,n 1,1 0,1 1,n 1,1
Employee Nssn Address fname lname Department Dept_number Dept_name Emp_nb Project number Description Dependent name birthdate relationship Works_for manages startdate controls Dependents_of Supervision Works_on hours locations
Semantic is not completely preserved (we
Rules can be automated (numerous
Mapping is done in 7 steps Comparison of E/R concepts and relational
Mapping
Entity type relation Atomic Attribute attribute Composite Attributes n attributes Key(s) Attribute(s) logical key
mapping
E K A E(K, A)
mapping
Project(number, description) Atomic Attributes Project step 7 : emp_nb Derived Attributes step 6 : location Multivalued attributes Department(no_dept, libelle) Atomic Attributes Department Composite attributes are flatten loss of semantic Employee(ssn, address, fname, lname) Composite attributes Employee(ssn, address) Atomic Attributes Employee
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
mapping
E K A E2 K2 A2
(1,1)
E2(K, K2, A2)
&
mapping
ssn: Foreign key on Employee + Part of Dependent key Dependent(name, ssn, birthdate, relationship) + Identifying entity key Dependent(name, birthdate, relationship) Atomic Attributes Entity dependent
'
Key associated to E1 attribute of E2 Attributes of relationship RS attributes of
mapping
E1 K1 A1 E2 K2 A2
?,1
E2(K2, A2, K1, A3)
?,?
RS A3 Foreign key, but not key of E2
+
mapping
See step 2 Dependents_of Employee(ssn, address, fname, lname, deptnb, ssnsupervisor) ssnsuperrvisor foreign key of Employé on Employee Supervision Manages mono-valued in both directions other possible mapping Employee(ssn, address, fname, lname, deptnb, manageddeptnb, startdate) Cardinality (0,1), partial relationship null values Project(number, description, deptnum) Controls Department(dept_nb, dept_name, ssnmgr, startdate) Manages Employe(ssn, address, fname, lname, deptnb) Works_for
,
Creation of a new relation RS E1 key + E2 key RS key Attributes of RS Attributes of RS
mapping
E1 K1 A1 E2 K2 A2
?,n
RS(K1, K2, A3)
?,n
RS A3 Works_on(ssn, projectnumber, hours) Works_on
like step 4 :
Creation of a new relation RS E1 key + E2 key + … En key RS key RS
mapping
E1 K1 A1 E2 K2 A2 RS(K1, K2, K3, A4) RS A4 E3 K3 A3
Multivalued Attribute -> attribute Key of Associated entity type -> attribute key of the new relation: the whole schema
mapping
R A C R(A, C) Location (location, dept_nb) Locations in Department
Derived attribute Associated query
mapping
Department(dept_nb, dept_name, ssnmgr, startdate) SELECT dept_nb, COUNT(*) FROM Employee GROUP BY dept_nb Query may be associated to a relational view Emp_nb in Department
&
Employee(ssn, address, fname, lname, deptnb, ssnsupervisor) Department(dept_nb, dept_name, ssnmgr, startdate) 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
mapping
&
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)
Reverse engineering
&
Relation without a foreign key :
Relation with a foreign key outside
&&
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
&'
Movies(id, title, yr, score, votes, director) Directors(id, name) Actors(id, name) Castings(movieid, actorid, ord)
&+
0,n 1,1 0,n 0,n
Movies score id title yr score Actors idactor nameactor Directors iddirector namedirector Castings
IsDirectedBy
&,
Introduce in the E/R model, some concepts
Allow to define an entity set related to
&
Sub entity set inherits from its super entity set A priori no multiple inheritence Specialisation may be:
total (all instances are specialised in at least one
a partition (an instance may be specialised in
&
Entity Set A A1 A2 Entity Set B B1 Entity Set C C1 Entity Set D D1 D2 Super entity set A1 A2 B1 A1 A2 C1 A1 A2 D1 D2
A = B ∪ C ∪ D B ∩ C = ∅ C ∩ D = ∅
&
PERSON fname lname address STUDENT nostudent cycle EMPLOYEE salary T/O P = E ∪ S E ∩ S ≠ ∅ PRIVATE bonus PUBLIC grade T/P S = PR ∪ PU PR ∩ PU = ∅
'
generalisation approach
leave: base table with a schema defined as union
Non leave: view defined as SQL union of its
'
Non instances insertions on non leaves Low access cost to leaves Non leaves are accessed with union-projection
A single instance can be specialised in several
No real « object identity" (so no shared values) Difficult to do schema evolution
'
CREATE TABLE STUDENT(fname, lname, address, nostudent, cycle) CREATE TABLE PRIVATE(fname, lname, address, salary, bonus) CREATE TABLE PUBLIC(fname, lname, address, salary, grade) CREATE VIEW EMPLOYEE AS
SELECT fname, lname, address, salary FROM PRIVE UNION SELECT fname, lname, address, salary FROM PUBLIC
CREATE VIEW PERSON AS
SELECT fname, lname, address FROM STUDENT UNION SELECT fname, lname, address FROM EMPLOYEE
'&
Specialisation approach
Each node: table with schema defined with
Each table: addition of a oid (or surrogate) inheritence: add oid of mother’s table as foreign
''
instance insertions at any level within the
High cost access to instances « far » from
Multiple inheritence is difficult Oids are supported (sharing of structure is
Multi-instanciation is possible Schema Evolution more easy
'+
CREATE TABLE ∆PERSON(idfP, fname, lname, address) CREATE TABLE ∆STUDENT(idfS, nostudent, cycle, idfP) CREATE TABLE ∆EMPLOYEE(idfE, salary, idfP) CREATE TABLE ∆PUBLIC(idfPU, grade, idfS) CREATE TABLE ∆PRIVATE(idfPR, bonus, idfS) CREATE VIEW PERSON AS SELECT fname, lname, address FROM ∆PERSON CREATE VIEW STUDENT AS SELECT P.fname, P.lname, P.address, S.nostudent, S.cycle FROM ∆ PERSON P, ∆ STUDENT S WHERE P.idfP = S.idfS ...