High-Level Database Models

Slide 1

Slide 1 text

ICS 321 Data Storage & Retrieval High Level Database Models Prof. Lipyeow Lim InformaCon & Computer Science Department University of Hawaii at Manoa 1 Lipyeow Lim -‐-‐ University of Hawaii at Manoa

Slide 2

Slide 2 text

Database Design & Deployment Lipyeow Lim -‐-‐ University of Hawaii at Manoa 2 Requirements Analysis Conceptual Database Design Logical Database Design Physical Database Design (DDL/DML) Business Processes SQL OperaCons & program code TesCng ProducCon iterate

Slide 3

Slide 3 text

Overview Database Design •  Conceptual Design – Use enCty-‐relaConship (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

Slide 4

Slide 4 text

ER Model Basics: EnCCes !  En#ty: Real-‐world 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

Slide 5

Slide 5 text

ER Model Basics: RelaConships !  Rela#onship: 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 diﬀerent relaConship sets, or in diﬀerent “roles” in same set. Lipyeow Lim -‐-‐ University of Hawaii at Manoa 5 lot dname budget did since name Works_In Departments Employees ssn

Slide 6

Slide 6 text

Lipyeow Lim -‐-‐ University of Hawaii at Manoa 6 Cardinality RaCos of RelaConships !  Consider binary relaConships, i.e., between two enCty sets !  Alternate notaCon: 1:1, 1:M, M:1, M:N Many-to-Many 1-to-1 1-to Many Many-to-1

Slide 7

Slide 7 text

Lipyeow Lim -‐-‐ University of Hawaii at Manoa 7 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

Slide 8

Slide 8 text

Lipyeow Lim -‐-‐ University of Hawaii at Manoa 8 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

Slide 9

Slide 9 text

Set TheoreCc FormulaCon •  ParCal ParCCcipaCon: Not all members 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

Slide 10

Slide 10 text

Lipyeow Lim -‐-‐ University of Hawaii at Manoa 10 Weak EnCCes !  A weak en#ty can be idenCﬁed 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

Slide 11

Slide 11 text

Lipyeow Lim -‐-‐ University of Hawaii at Manoa 11 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 ?

Slide 12

Slide 12 text

Lipyeow Lim -‐-‐ University of Hawaii at Manoa 12 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

Slide 13

Slide 13 text

Logical DB Design: ER to RelaConal •  EnCty sets 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

Slide 14

Slide 14 text

RelaConship Sets to Tables •  AXributes of the 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

Slide 15

Slide 15 text

TranslaCng ER Diagrams with Key Constraints •  Map relaConship 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

Slide 16

Slide 16 text

ParCcipaCon Constraints in SQL •  We can capture parCcipaCon 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

Slide 17

Slide 17 text

TranslaCng Weak EnCty Sets •  Weak enCty set and 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

Slide 18

Slide 18 text

ISA Hierarchies •  Overlap constraints: 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

Slide 19

Slide 19 text

TranslaCng ISA Hierarchies to RelaCons •  General approach: –  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

Slide 20

Slide 20 text

Uniﬁed Modeling Language •  Standardized general-‐purpose modeling language for sonware design •  Based on object-‐oriented model •  Class diagrams Lipyeow Lim -‐-‐ University of Hawaii at Manoa 20

Slide 21

Slide 21 text

UML Classes Lipyeow Lim -‐-‐ University of Hawaii at Manoa 21 Movies Ctle year length genre ER Entity Set UML Class Class name Methods secCon typically not used in data modeling

Slide 22

Slide 22 text

AssociaCons Lipyeow Lim -‐-‐ University of Hawaii at Manoa 22 Cardinality constraints : one instance of Stars can be connected to at least 0 instance of movies and at most iniﬁnite instances of movies

Slide 23

Slide 23 text

ReferenCal Integrity Lipyeow Lim -‐-‐ University of Hawaii at Manoa 23 AggregaCon: Must be 0..1 (includes 1..1) ComposiCon : Must be 1..1 Every president runs exactly one studio AggregaCon never named

Slide 24

Slide 24 text

AssociaCon Classes Lipyeow Lim -‐-‐ University of Hawaii at Manoa 24

Slide 25

Slide 25 text

Sub-‐Class Hierarchies Lipyeow Lim -‐-‐ University of Hawaii at Manoa 25

Slide 26

Slide 26 text

Modeling Tips •  Faithful to the semanCcs of the 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