(aka ER) model represented pictorially as ER diagrams – Map ER model to relaConal schema • QuesCons to ask yourself – What are the enCCes and relaConships in the applicaCon? – What informaCon about these enCCes and relaConships should we store in the database? – What are the integrity constraints or business rules that hold? Lipyeow Lim -‐-‐ University of Hawaii at Manoa 3
object disCnguishable from other objects. An enCty is described (in DB) using a set of a)ributes. ! En#ty Set: A collecCon of similar enCCes. E.g., all employees. ! All enCCes in an enCty set have the same set of aXributes. (UnCl we consider ISA hierarchies, anyway!) ! Each enCty set has a key. ! Each aXribute has a domain. Lipyeow Lim -‐-‐ University of Hawaii at Manoa 4 Employees ssn name lot
AssociaCon among two or more enCCes. ! Rela#onship Set: CollecCon of similar relaConships. ! An n-‐ary relaConship set R relates n enCty sets E1 ... En; each relaConship in R involves enCCes e1 E1, ..., en En ! Same enCty set could parCcipate in different relaConship sets, or in different “roles” in same set. Lipyeow Lim -‐-‐ University of Hawaii at Manoa 5 lot dname budget did since name Works_In Departments Employees ssn
Key Constraints ! Consider Works_In: An employee can work in many depts; a dept can have many employees : m-‐to-‐m ! Consider Manages: each dept has at most one manager ! Dept has a key constraint on Manages: each instance of dept appears in at most one instance of manages ! Denoted by an arrow: given a dept enCty we can uniquely idenCfy the manages relaConship in which it appears dname budget did since lot name ssn Manages Employees Departments since Works_In
ParCcipaCon constraints ! Does every dept have a manager? ! If so, this is a par#cipa#on constraint: the parCcipaCon of dept in Manages is said to be total (vs. par#al). Denoted by thick/double line ! Meaning that every Dept enCty must appear in an instance of the Manages relaConship lot name dname budget did since name dname budget did since Manages Departments Employees ssn
of the Employees enCty set take part in the manages relaCons • Total ParCCcipaCon: All members of the Dept enCty set take part in the manages relaConship • Dept has a key constraint on Manages: each member of the dept enCty set takes part in at most one member of the manages relaConship set Lipyeow Lim -‐-‐ University of Hawaii at Manoa 9 Employees Manages (1:m) Departments B A C D 1 2 3
Weak EnCCes ! A weak en#ty can be idenCfied uniquely only by considering the primary key of another (owner) enCty. ! Owner enCty set and weak enCty set must parCcipate in a one-‐to-‐many relaConship set (one owner, many weak enCCes). ! Weak enCty set must have total parCcipaCon in this iden#fying relaConship set. ! Denoted by a box with double or thick lines lot name age pname Dependents Employees ssn Policy cost
Design Choices ! Should a concept be modeled as an enCty or an aXribute? ! Should a concept be modeled as an enCty or a relaConship? ! IdenCfying relaConships: Binary or ternary? AggregaCon? ! How much semanCcs to capture in the form of constraints ?
EnCty vs. AXribute ! Depends upon how we want to use the address informaCon, and the semanCcs of the data: - If we have several addresses per employee, address must be an enCty (since aXributes cannot be set-‐valued). - If the structure (city, street, etc.) is important, e.g., we want to retrieve employees in a given city, address must be modeled as an enCty (since aXribute values are atomic). Employee address Employee has address
to tables: CREATE TABLE Employees (ssn CHAR(11), name CHAR(20), lot INTEGER, PRIMARY KEY (ssn)) Employees ssn name lot 13 Lipyeow Lim -‐-‐ University of Hawaii at Manoa
relaCon must include: – Keys for each parCcipaCng enCty set (as foreign keys). • This set of aXributes forms a superkey for the relaCon. – All descripCve aXributes. CREATE TABLE Works_In( ssn CHAR(11), did INTEGER, since DATE, PRIMARY KEY (ssn, did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments) 14 Lipyeow Lim -‐-‐ University of Hawaii at Manoa lot dname budget did since name Works_In Departments Employees ssn
to a table: – Note that did is the key now! • Since each department has a unique manager, we could instead combine Manages and Departments. CREATE TABLE Manages( ssn CHAR(11), did INTEGER, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments) CREATE TABLE Dept_Mgr( did INTEGER, dname CHAR(20), budget REAL, ssn CHAR(11), since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees) 15 Lipyeow Lim -‐-‐ University of Hawaii at Manoa Emp. Manages (1:m) B A C D Depts 1 2 3 Employees Manages Departments ssn did since
constraints involving one enCty set in a binary relaConship, but liXle else (without resorCng to CHECK constraints). CREATE TABLE Dept_Mgr( did INTEGER, dname CHAR(20), budget REAL, ssn CHAR(11) NOT NULL, since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE NO ACTION) 16 Lipyeow Lim -‐-‐ University of Hawaii at Manoa
idenCfying relaConship set are translated into a single table. – When the owner enCty is deleted, all owned weak enCCes must also be deleted. CREATE TABLE Dep_Policy ( pname CHAR(20), age INTEGER, cost REAL, ssn CHAR(11) NOT NULL, PRIMARY KEY (pname, ssn), FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE CASCADE) 17 Lipyeow Lim -‐-‐ University of Hawaii at Manoa
Can Joe be an Hourly_Emps as well as a Contract_Emps enCty? (Allowed/disallowed) • Covering constraints: Does every Employees enCty also have to be an Hourly_Emps or a Contract_Emps enCty? (Yes/no) Contract_Emps name ssn Employees lot hourly_wages ISA Hourly_Emps contrac9d hours_worked • As in C++, or other PLs, aXributes are inherited. • If we declare A ISA B, every A enCty is also considered to be a B enCty. 18 Lipyeow Lim -‐-‐ University of Hawaii at Manoa
– 3 relaCons: Employees, Hourly_Emps and Contract_Emps. • Hourly_Emps: Every employee is recorded in Employees. For hourly emps, extra info recorded in Hourly_Emps (hourly_wages, hours_worked, ssn); must delete Hourly_Emps tuple if referenced Employees tuple is deleted). • Queries involving all employees easy, those involving just Hourly_Emps require a join to get some aXributes. • AlternaCve: Just Hourly_Emps and Contract_Emps. – Hourly_Emps: ssn, name, lot, hourly_wages, hours_worked. – Each employee must be in one of these two subclasses. 19 Lipyeow Lim -‐-‐ University of Hawaii at Manoa
applicaCon • Model only what is needed in the applicaCon • Minimize redundancy (why?) • Simple is good • If the model is geong too complicated, take a step back and ask – Am i conceptualizing the right enCCes ? – Am i thinking of the right relaConships ? – Should some relaConships become enCCes ? Vice versa ? – Should some aXributes become enCCes ? Vice versa ? Lipyeow Lim -‐-‐ University of Hawaii at Manoa 26
lipyeow
yuyakurodou
debug_mode
masatatsu
signer
bernhardsvt
coderdojojapan
mansuy
ktanishima
totodo713
brettharned
eitanlees
jcasabona
addyosmani
csswizardry
caitiem20
moore
lara
holman
shpigford
bkeepers
skipperchong
