COMP 421 @ Mc an ER schema into the relational model
ER-Relational Translation
• Database design is first done using the entity- relationship model (or other semantic models such as UML)
Copyright By PowCoder代写 加微信 powcoder
• An ER schema must then be translated into relations
• This is a relatively straightforward process that can be automated.
COMP 421 @ McGill
Entity Sets to Relations
Companies(name, address, empl)
PostgreSQL:
CREATE TABLE Companies (name VARCHAR(30), addrVARCHAR(50),
empl INTEGER,
PRIMARY KEY (name))
CREATE TABLE Companies
(name VARCHAR(30) PRIMARY KEY,
addr VARCHAR(50),
empl INTEGER)
CREATE TABLE Companies
(name VARCHAR(30) NOT NULL,
addr VARCHAR(50),
empl INTEGER,
PRIMARY KEY (name))
BiggestEngCompanyEver
BiggestConstCommpanyEver
NoNameCompany Whatever St., …
Eng. Av., H3X…
Constr. St. H4E…
COMP 421 @ McGill
Many-many Relationship Sets
A many-to-many relationship set is ALWAYS translated as an individual table.
Attributes of the table are
– Keys for each participating entity set (as foreign keys) • Thissetofattributesformsthekeyfortherelation
– All descriptive attributes
Companies(cname,addr,empl) Parties(pname, status) Sponsorship(cname,pname,tamount)
cname references Companies pname references Parties
COMP 421 @ Mc Tables
BiggestEngCompanyEver
Eng. Av., H3X…
NoNameCompany
Whatever St., …
opposition
Sponsor- ship
BiggestEngCompanyEver
BiggestEngCompanyEver
NoNameCompany
COMP 421 @ Mc
Many-many Relationship Sets
Total amount
CREATE TABLE Companies
(cname VARCHAR(30),
addr VARCHAR(50),
empl INTEGER,
PRIMARY KEY (cname))
)CREATE TABLE Parties (pname VARCHAR(20),
status VARCHAR(10),
PRIMARY KEY (pname))
Sponsorship(cname,pname,tamount)
CREATE TABLE Sponsorship
(cname VARCHAR(30),
pname VARCHAR(20),
tamount INTEGER,
PRIMARY KEY (cname,pname),
FOREIGN KEY (cname)
REFERENCES Companies, FOREIGN KEY (pname) REFERENCES Parties)
COMP 421 @ Mc Sets with Key Constraints
• Alternative I: map relationship set to table
– Many-onefromentitysetE1toentitysetE2:keyofE1
• i.e., key of entity-set with the key constraint is the key for the new relationship table (mname is now the key)
– One-one:keyofeitherentityset
– Separatetablesforentitysets(MembersandParties)
Members(mname,riding) Parties(pname,status) Membership(mname,pname)
CREATE TABLE Membership
(mname VARCHAR(30),
pname VARCHAR(20),
PRIMARY KEY (mname),
FOREIGN KEY (mname)
REFERENCES Members,
FOREIGN KEY (pname)
belongs Parties
REFERENCES Parties)
COMP 421 @ McGill 7
Example Tables
Quebec solidaire
Quebec solidaire
COMP 421 @ Mc
Member- ship
Relationships Sets with Key Constraints
• Alternative 1I: include relationship set in table of the entity set with the key constraint
– Possible because there is at most one relationship per entity
– Not useful if many entities do not have a relationship (wasted space,
many not filled values)
Members(mname,riding,pname) CREATE TABLE Member (mname VARCHAR(30),
Parties(pname,status)
riding VARCHAR(30),
pname VARCHAR(20),
PRIMARY KEY (mname),
FOREIGN KEY (pname)
REFERENCES Parties)
COMP 421 @ McGill
Example Tables
Quebec solidaire
La Piniere
Quebec solidaire
COMP 421 @ McGill 10
Key and Participation Constraints
• Include relationship set in table of the entity set with the key constraint
Members(mname,since,pname) Parties(pname,status)
CREATE TABLE Member
(mname VARCHAR(30),
since DATE,
pname VARCHAR(20) NOT NULL,
PRIMARY KEY (mname),
FOREIGN KEY (pname)
REFERENCES Parties)
COMP 421 @ McGill
Participation Constraints
• Can usually not be reflected
• Only exception on previous slide
– If there is a key constraint and a participation constraint
COMP 421 @ Mc
In the case the keys of the participating entity sets have the same names we must rename attributes accordingly
• Companies(name, addr, empl)
empl • SpinOff(spinoffcompany, parentcompany)
CREATE TABLE SpinOff
(spinoffcompany VARCHAR(30) PRIMARY KEY,
parentcompany VARCHAR(30),
FOREIGN KEY (spinoffcompany)
REFERENCES Companies(name),
FOREIGN KEY (parentcompany)
REFERENCES Companies(name))
Renaming can also occur for foreign keys, etc.
Otherwise, all other translation rules apply
COMP 421 @ Mc s
BiggestEngCompanyEver
Eng. Av., H3X…
BiggestConstCommpanyEver
Constr. St. H4E…
NoNameCompany
Whatever St., …
spinoffcompany
parentcompany
NoNameCompany
BiggestConstCompanyEver
• Alternative?
COMP 421 @ McGill 14
Translating Weak Entity Sets
• Weak entity set and identifying relationship set are translated into a single table
tname ranking
Shirt number
CREATE TABLE Players
(tname VARCHAR(30),
shirtN INT,
pname VARCHAR(30,)
PRIMARY KEY (tname,shirtN),
FOREIGN KEY (tname)
REFERENCES Teams)
• Teams(tname,ranking)
• Players(tname,shirtN,pname)
COMP 421 @ McGill 15
Absolute Zero
Ten Left Wingers
Absolute Zero
PutYourNameHere
Absolute Zero
Ten Left Wingers
COMP 421 @ McGill 16
Translating ISA Hierarchies
General Approach: distribute information among relations Relation of superclass stores the general attributes and defines key Relations of subclasses have key of superclass and addit. attributes
• Companies(name, addr, empl)
• Sole(name, owner) • Partnership(name)
• Corporation(name, level) CREATE TABLE Companies
(name VARCHAR(30) PRIMARY KEY,
addr VARCHAR(50),
empl INTEGER)
Partnership
CREATE TABLE Corporation
(name VARCHAR(30) PRIMARY KEY,
level VARCHAR(20),
FOREIGN KEY (name)
REFERENCES Companies)
Corporation
– E/R: sub-entity sets do NOT have primary key attribute (that would be redundant)
– Relational: sub-tables have primary key attribute which represents a reference to the parent table
• That’s not redundant: it’s the encoding of the ISA symbol! 17
BiggestEngCompanyEver
Eng. Av., H3X…
BiggestConstCompanyEver
Constr. St. H4E…
NoNameCompany
Whatever St., …
BiggestEngCompanyEver
Corporation
Overlapping/disjoint ? Covering / non-covering ?
NoNameCompany
Bugs Bunny
Translating ISA Hierarchies (contd.)
• Object-oriented approach:
– Sub-classes have all attributes;
– if an entity is in a sub-class it does not appear in the super-class relation;
• Companies(name, addr, empl)
• Corporation(name,addr,empl,level) • Sole(name, addr, empl, owner)
• Partnership(name,addr,empl)
Companies Corporation Sole
BiggestEngCompanyEver
Eng. Av., H3X…
BiggestConstCompanyEver
Constr. St. H4E…
NoNameCompany
Whatever st.
Bugs Bunny
Object-oriented
Pro/Contra:
+ A query asking for all information about Corporations (name, addr, empl, level) only has to san through one table.
– A Query wanting the names of all companies have to read all four tables
– Overlapping sub entity sets => undesired redundancy
• Companies(name, addr, empl)
• Corporation(name,addr,empl,level) • Sole(name, addr, empl, owner)
• Partnership(name,addr,empl)
Translating ISA Hierarchies (contd.)
• Last Alternative: one big relation
– Create only one relation for the root entity set with all attributes found
anywhere in its network of subclasses.
– Put NULL in attributes not relevant to a given entity
Companies(name,addr,empl,owner,level)
BiggestEngCompanyEver
Eng. Av., H3X…
BiggestConstCompanyEver
Constr. St. H4E…
NoNameCompany
Whatever St., …
COMP 421 @ Mc Big relation
+ All information in one big table; never need to join information from several tables
– Lot’sofpossibleNULLvalues
– Ifasub-classhasarelationshipset,withanotherentityset we cannot enforce that only the tuples of that subclass can have relationships in that relationship set.
– Might be hard to detect by looking at at tuple to which subclass(es) it belongs as attributes might be NULL despite the fact that the tuple belongs to a subclass
Pro/Contra:
COMP 421 @ McGill 22
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com