WIXLIB

Conceptual Designand The EntityRelationship ModelSteps in Database Design Requirements Analysis– user needs; what must database do? Conceptual Design– high level descr (often done w/ER model) Logical Design– translate ER into DBMS data model Schema Refinement– consistency, normalization Physical Design - indexes, disk layout Security Design - who accesses what, and howCS 186 Spring 2006Lectures 19 & 20R &G - Chapter 2A relationship, I think, is like ashark, you know? It has toconstantly move forward or itdies. And I think what we got onour hands is a dead shark.Woody Allen (from Annie Hall, 1979)Conceptual DesignDatabases Model the Real World “Data Model” allows us to translate realworld things into structures computerscan store Many models: Relational, E-R, O-O,Network, Hierarchical, etc. Relational– Rows & Columns– Keys & Foreign Keys to link c101Reggae203Topology112History105gradeCBABER Model innameagegpalot What are the entities and relationships inthe enterprise? What information about these entities andrelationships should we store in thedatabase? What are the integrity constraints orbusiness rules that hold? A database schema’ in the ER Model canbe represented pictorially (ER diagrams). Can then map an ER diagram into arelational schema.ER Model Basics (Contd.)Employeesssn Entity: Real-world object, distinguishable fromother objects. An entity is described using a setof attributes. Entity Set: A collection of similar entities. E.g.,all employees.– All entities in an entity set have the same setof attributes. (Until we consider hierarchies,anyway!)– Each entity set has a key (underlined).– Each attribute has a domain.sincenameEmployeesdnamedidlotWorks InbudgetDepartments Relationship: Association among two or more entities.E.g., Attishoo works in Pharmacy department.– relationships can have their own attributes. Relationship Set: Collection of similar relationships.– An n-ary relationship set R relates n entity sets E1 . En ;each relationship in R involves entities e1 E1, ., en En

nameER Model Basics (Cont.)ssnlotsupervisorbudgetsubordinateReports ToWorks InDepartments Same entity set can participate in differentrelationship sets, or in different “roles” inthe same set.sincenamednamedidlotEmployeesbudgetAn employee canwork in manydepartments; adept can havemany employees.DepartmentsManagesWorks InsinceIn contrast, each depthas at most onemanager, accordingto the key constraintMany-to- 1-to Manyon Manages.Many1-to-1Weak EntitiesParticipation Constraints Does every employee work in a department? If so, this is a participation constraint– the participation of Employees in Works In is said to betotal (vs. partial)– What if every department has an employee working in it? Basically means “at least one”ssndidEmployeesEmployeessincednamelotKey ConstraintsnamednamedidsincessnbudgetA weak entity can be identified uniquely only byconsidering the primary key of another(owner) entity.– Owner entity set and weak entity set mustparticipate in a one-to-many relationship set (oneowner, many weak entities).– Weak entity set must have total participation inthis identifying relationship set.DepartmentsManagesnamessnlotcostpnameageWorks InMeans: “exactly one”sinceBinary vs. Ternary RelationshipsIf each policy isowned by just melotageDependentsEmployees Think through allthe constraints inthe 2nd diagram!ageDependentsCoversBad designKey constraint onPolicies wouldmean policy cannamessnonly cover 1dependent!DependentsPolicyBinary vs. Ternary Relationships (Contd.)namessnEmployeesWeak entities have only a “partial key” (dashed underline)Purchaser Previous example illustrated a case when two binaryrelationships were better than one ternary. An example in the other direction: a ternaryrelation Contracts relates entity sets Parts,Departments and Suppliers, and has descriptiveattribute quantity.– No combination of binary relationships is anquantityadequate substitute.BeneficiaryPartsBetter ents

Binary vs. Ternary Relationships rs– S “can-supply” P, D “needs” P, and D “deals-with” S doesnot imply that D has agreed to buy P from S.– How do we record qty?ISA ( is a’) Hierarchiesin C , or other PLs,attributes are inherited. hourly wages If we declare A ISA B,every A entity is alsoconsidered to be a Bentity.namessn As lotAggregationnamessnlotEmployeesUsed to model arelationshipMonitorsuntilinvolving arelationship set.sincestarted ondnameAllows us to treat apidpbudgetdidbudgetrelationship setSponsorsas an entity setDepartmentsProjectsfor purposes ofparticipation in Aggregation vs. ternary relationship? Monitors is a distinct relationship,(other)relationships. with a descriptive attribute. Also, can say that each sponsorshipis monitored by at most one employee.Review - Our Basic ER ModelEmployeeshours workedISAcontractidHourly EmpsContract EmpsOverlap constraints: Can Simon be an Hourly Emps as well as aContract Emps entity? (Allowed/disallowed)Covering constraints: Does every Employees entity also have to be anHourly Emps or a Contract Emps entity? (Yes/no)Reasons for using ISA:– To add descriptive attributes specific to a subclass. i.e. not appropriate for all entities in the superclass Entities and Entity Set (boxes) Relationships and Relationship sets (diamonds)– binary– n-ary Key constraints (1-1,1-M, M-M, arrows on 1 side) Participation constraints (bold for Total) Weak entities - require strong entity for key Aggregation - an alternative to n-ary relationships Isa hierarchies - abstraction and inheritance– To identify entities that participate in a particular relationship i.e., not all superclass entities participateConceptual Design Using the ER Model ER modeling can get tricky! Design choices:– Should a concept be modeled as an entity or an attribute?– Should a concept be modeled as an entity or a relationship?– Identifying relationships: Binary or ternary? Aggregation? Note constraints of the ER Model:– A lot of data semantics can (and should) be captured.– But some constraints cannot be captured in ER diagrams. We’ll refine things in our logical (relational) designEntity vs. Attribute Should address be an attribute of Employeesor an entity (related to Employees)? Depends upon how we want to use addressinformation, and the semantics of the data: If we have several addresses per employee,address must be an entity (since attributescannot be set-valued). If the structure (city, street, etc.) is important,address must be modeled as an entity (sinceattribute values are atomic).

Entity vs. Attribute (Cont.)ssntofromnameEntity vs. Relationshipdnamelotdidbudget Works In2 does notallow an employee toDepartmentsEmployeesWorks In2work in a departmentfor two or more periods. Similar to the problem ofwanting to record severaladdresses for anemployee: we want tonamednamessndidlotrecord several values ofbudgetthe descriptive attributesWorks In3DepartmentsEmployeesfor each instance of thisrelationship.fromDurationOK as long as amanager gets aseparatediscretionary budget(dbudget) for eachdept.What if manager’sdbudget covers allmanaged depts?(can repeat value, butsuch redundancy oyeesbudgetDepartmentsis managermanaged bysincetoapptnumMgr ApptsdbudgetA Cadastral E-R DiagramThese things get pretty hairy! Many E-R diagrams cover entire walls! A modest example:A Cadastral E-R DiagramLogical DB Design: ER to Relational Entity sets to tables.cadastral: showing or recording property boundaries, subdivision lines, buildings,and related detailsSource: US Dept. Interior Bureau of Land Management,Federal Geographic Data Committee Cadastral 3666 Attishoo48231-31-5368 Smiley22131-24-3650 Smethurst 35EmployeesCREATE TABLE Employees(ssn CHAR(11),name CHAR(20),lot INTEGER,PRIMARY KEY (ssn))

Relationship Sets to TablesCREATE TABLE Works In( In translating a many-to- ssn CHAR(1),many relationship set to a did INTEGER,relation, attributes of the since DATE,PRIMARY KEY (ssn, did),relation must include:FOREIGN KEY (ssn)1) Keys for eachREFERENCES Employees,participating entity set FOREIGN KEY (did)(as foreign keys). This REFERENCES Departments)set of attributes formsssndid sincea superkey for the123-22-366651 1/1/91relation.123-22-366656 3/3/932) All descriptive231-31-5368 51 2/2/92attributes.Translating ER with Key budgetDepartments Since each department has a unique manager, wecould instead combine Manages and Departments.CREATE TABLE Manages(CREATE TABLE Dept Mgr(ssn CHAR(11),did INTEGER,did INTEGER,dname CHAR(20),Vs. budget REAL,since DATE,PRIMARY KEY (did),ssn CHAR(11),FOREIGN KEY (ssn)since DATE,REFERENCES Employees,PRIMARY KEY (did),FOREIGN KEY (did)FOREIGN KEY (ssn)REFERENCES Departments)REFERENCES Employees)Review: Key Constraints Each dept has atmost onemanager,according to thekey constraint nagesDepartmentsTranslation torelational model?1-to-11-to ManyMany-to-1Many-to-ManyReview: Participation Constraints Does every department have a manager?– If so, this is a participation constraint: the participation ofDepartments in Manages is said to be total (vs. partial). Every did value in Departments table must appear in arow of the Manages table (with a non-null ssn etDepartmentsWorks InsinceParticipation Constraints in SQLReview: Weak Entities We can capture participation constraints involving oneentity set in a binary relationship, but little else(without resorting 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) A weak entity can be identified uniquely only byconsidering the primary key of another (owner) entity.– Owner entity set and weak entity set must participate in aone-to-many relationship set (1 owner, many weakentities).– Weak entity set must have total participation in thisidentifying relationship s