1
Introduction to
Database Systems
Mapping ER Models to
Relational Schemas
Werner Nutt
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Guidelines and tips
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
We cannot store date in an ER schema ... ER schemas to relational schemas ... Every atomic attribute of the entity type becomes a relation attribute.
Typology: Study notes
2021/2022
1 / 39
This page cannot be seen from the preview
Don't miss anything!
Related documents
Partial preview of the text
Download Introduction to Database Systems Mapping ER Models to ... and more Study notes Design in PDF only on Docsity!
Introduction toDatabase Systems Mapping ER Models toRelational SchemasWerner Nutt
Conceptual and Logical Design
Conceptual Model: Relational Model:
PERSON BUYS namePRODUCTprice^
name^
ssn
Translation: Principles
-^ Mapsā ER schemas to relational schemasā ER instances to relational instancesā¢^ Ideally, the mapping shouldā be one-to-one in both directionsā not lose any informationā¢^ Difficulties:ā what to do with ER-instances that have identical
attribute values, but consist of different entities?ā in which way do we want to preserve information?
Mapping Entity Types to Relationsā¢ For every
entity type
create a
relation
-^ Every
atomic attribute
of the entity type becomes a
relation attribute
-^ Composite attributes
: include
all the atomic attributes
-^ Derived attributes
are not included(but remember their
derivation rules
-^ Relation instances are subsets of the cross productof the domains of the attributesā¢^ Attributes of the
entity key
make up the
primary key
of the relation
given^ familynameSTUDENTstudno
no. ofstudents^
equip courseno COURSEsubject
Mapping Many:many Relationship Types
to Relations
Create a relation with the following set of attributes:
N^ (degree of relationship) U^ primary_key(E
)^ U^ i
{a,ā¦,a^1
}M
i=
primary keys of eachentity type participatingin the relationship
attributes of therelationship type (if any)
labmarkENROLLED^ exammark given^ familynameSTUDENTstudno
no. ofstudents^
equip courseno COURSEsubject
Mapping Many:many Relationship Types
to Relations (cntd.)
ENROL(studno, courseno, labmark, exammark)Foreign Key ENROL(studno) references STUDENT(studno)Foreign Key ENROL(courseno) references COURSE(courseno)
labmarkENROLLEDexammark familygiven (^) nameSTUDENTstudno
no. ofstudents^
equip courseno COURSEsubject
Mapping Many:one Relationship Types
to Relations: Example
slot TUTOR
given^ familyname STUDENT studno
name STAFF
roomno
m^
1
The relationSTUDENT(studno, givenname, familyname)is extended toSTUDENT(studno, givenname, familyname, tutor, roomno, slot)and the constraintForeign Key STUDENT(tutor,roomno) references STAFF(name,roomno)
Mapping Many:one Relationship Types
to Relations (cntd.)
STAFFname
roomno kahn^
IT
bush^
goble^
zobel^
watson
IT
woods^
IT
capon^
A
lindsey
barringer
STUDENTstudno
given
family
tutor
roomno
slot
s^
fred^
jones^
bush^
2.^
12B
s^
mary^
brown^
kahn^
IT^
12B
s^
sue^
smith^
goble^
2.^
10A
s^
fred^
bloggs^
goble^
2.^
11A
s^
peter^
jones^
zobel^
2.^
13B
s^
jill^
peters^
kahn^
IT^
12A
Mapping Many:one Relationship Types
to Relations (cntd.)
TUTOR(studno, staffname, rommno, slot)and Foreign key TUTOR(studno) references STUDENT(studno)Foreign key TUTOR(staffname, roomno) references
Compare with themapping of many:manyrelationship types!STAFF(name, roomno) slot TUTOR
given^ familyname STUDENT studno
name STAFF
roomno
m^
1
STUDENTstudno given
family s^
fred^
jones s^
mary^
brown s^
sue^
smith s^
fred^
bloggs s^
peter^
jones s^
jill^
peters
STAFFname^
roomno kahn^
IT
bush^
goble^
zobel^
watson
IT
woods^
IT
capon^
A
lindsey
barringer
TUTORstudno
tutor^
roomno
slot
s^
bush^
2.^
12B
s^
kahn^
IT^
12B
s^
goble^
2.^
10A
s^
goble^
2.^
11A
s^
zobel^
2.^
13B
s^
kahn^
IT^
12A
Mapping
Many:one
Relationship Types
to Relations (cntd.)
Optional Participation of the
Determined Entity
STUDENTstudno
given
family
hons
s^
fred^
jones^
ca
s^
mary^
brown^
cis
s^
sue^
smith^
cs
s^
fred^
bloggs
ca
s^
peter^
jones^
cs
s^
jill^
peters^
ca
SCHOOLhons^
faculty ac^
accountancy is^
information systems cs^
computer science ce^
computer science mi^
medicine ma^
mathematics
āhonsā cannot be NULL becauseit is mandatory for a student tobe registered for a school^ Ć^ ānot nullā constraintNo student is registered for āmiā,so āmiā doesnāt occuras a foreign key value
(but thatās no problem)
Optional Participation of theDeterminant Entity (āmany endā)
slot TUTOR
given^ familyname STUDENT studno
name STAFF
roomno
m^
1
A student entity instance need not
participate in a relationship instance of TUTOR
A staff entity instance
must participate in a relationshipinstance of
TUTOR
Optional Participation of theDeterminant Entity (cntd.)
STAFFname
roomno kahn^
IT
bush^
goble^
zobel^
watson
IT
woods^
IT
capon^
A
lindsey
barringer
STUDENTstudno
given
family
tutor
roomno
slot
s^
fred^
jones^
bush^
2.^
12B
s^
mary^
brown^
kahn^
IT^
12B
s^
sue^
smith^
goble^
2.^
10A
s^
fred^
bloggs^
goble^
2.^
11A
s^
peter^
jones^
zobel^
2.^
13B
s^
jill^
peters^
kahn^
IT^
12A
Optional Participation of theDeterminant Entity (cntd.)
STAFFname
roomno kahn^
IT
bush^
goble^
zobel^
watson
IT
woods^
IT
capon^
A
lindsey
barringer
STUDENTstudno
given
family
tutor
roomno
slot
s^
fred^
jones^
bush^
2.^
12B
s^
mary^
brown^
kahn^
IT^
12B
s^
sue^
smith^
goble^
2.^
10A
s^
fred^
bloggs^
goble^
2.^
11A
s^
peter^
jones^
NULL^
NULL^
NULL
s^
jill^
peters^
kahn^
IT^
12A