The Entity-Relationship ModelAdministrivaReview – Last TimeReview – The Big PictureToday and Thursday: The ER ModelReview: Levels of AbstractionDatabases Model the Real WorldA Problem with the Relational ModelOne Solution: The E-R ModelSteps in Database DesignConceptual DesignER Model BasicsER Model Basics (Contd.)ER Model Basics (Cont.)Key ConstraintsParticipation ConstraintsWeak EntitiesBinary vs. Ternary RelationshipsBinary vs. Ternary Relationships (Contd.)Slide 20Summary so farISA (`is a’) HierarchiesAggregationConceptual Design Using the ER ModelEntity vs. AttributeEntity vs. Attribute (Cont.)Entity vs. RelationshipNow you try itThese things get pretty hairy!A Cadastral E-R DiagramSummary of Conceptual DesignSummary of ER (Cont.)Slide 33The Entity-Relationship ModelLecture 11R&G - Chapter 2A relationship, I think, is like a shark, you know? It has to constantly move forward or it dies. And I think what we got on our hands is a dead shark.Woody Allen (from Annie Hall, 1979)Administriva•Homework 2 Due Next Sunday•Midterm Tuesday, October 14–Please see me ASAP if you have a conflictReview – Last Time•Query Optimization–Some resources, see slides•Finished discussing SQL–Insert–Delete–Update–Null Values – Outer Joins–Views–Order By–Access Control–Integrity ConstraintsReview – The Big Picture•Data Modelling–Relational–E-R•Storing Data–File Indexes–Buffer Pool Management•Query Languages–SQL–Relational Algebra–Relational Calculus•Query Optimization–External Sorting–Join Algorithms–Query Plans, Cost EstimationToday and Thursday: The ER Model•Discussed briefly in Lecture 2 •A different data model from Relational•Most commonly used for database design•Today: Details of the ER Model•Thursday: Translating ER Schemas to RelationalReview: Levels of Abstraction•Views describe how users see the data. •Conceptual schema defines logical structure•Physical schema describes the files and indexes used.•E-R Model most often appears at the View level, with the Relation Model at the Conceptual level•Some systems exist that use ER model as Conceptual ModelPhysical SchemaConceptual SchemaView 1 View 2 View 3DBUsersDatabases Model the Real World•“Data Model” translates real world things into structures computers can store•Many models: –Relational, E-R, O-O, Network, Hierarchical, etc.•Relational–Rows & Columns–Keys & Foreign Keys to link Relationssid name login age gpa53666 Jones jones@cs 18 3.453688 Smith smith@eecs 18 3.253650 Smith smith@math 19 3.8sid cid grade53666 Carnatic101 C53666 Reggae203 B53650 Topology112 A53666 History105 BEnrolledStudentsA Problem with the Relational ModelWith complicated schemas, it may be hard for a person to understand the structure from the data definition.CREATE TABLE Students(sid CHAR(20), name CHAR(20), login CHAR(10), age INTEGER, gpa FLOAT) CREATE TABLE Enrolled(sid CHAR(20), cid CHAR(20), grade CHAR(2)) sid name login age gpa53666 Jones jones@cs 18 3.453688 Smith smith@eecs 18 3.253650 Smith smith@math 19 3.8StudentsEnrolledOne Solution: The E-R Model•Instead of relations, it has:Entities and Relationships•These are described with diagrams, both structure, notation more obvious to humanslotnameStudentsssnEnrolled_insincednamebudgetdidCoursesSteps 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 howConceptual Design•Define enterprise entities and relationships •What information about entities and relationships should be in database?•What are the integrity constraints or business rules that hold? •A database `schema’ in the ER Model can be represented pictorially (ER diagrams).•Can map an ER diagram into a relational schema.ER Model Basics•Entity: Real-world thing, distinguishable from other objects. Entity described by set of attributes. •Entity Set: A collection of similar entities. E.g., all employees. –All entities in an entity set have the same set of attributes. (Until we consider hierarchies, anyway!)–Each entity set has a key (underlined).–Each attribute has a domain.EmployeesssnnamelotER Model Basics (Contd.)•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 EnlotnameEmployeesssnWorks_InsincednamebudgetdidDepartmentsER Model Basics (Cont.)•Same entity set can participate in different relationship sets, or in different “roles” in the same set.subor-dinate super-visorReports_TosinceWorks_IndnamebudgetdidDepartmentslotnameEmployeesssnKey Constraints An employee can work in many departments; a dept can have many employees. 1-to-11-to ManyMany-to-ManysinceManagesdnamebudgetdidDepartmentssinceWorks_InlotnamessnEmployeesIn contrast, each dept has at most one manager, according to the key constraint on Manages.Participation 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 be total (vs. partial)–What if every department has an employee working in it?•Basically means “at least one”lotnamednamebudgetdidsincenamednamebudgetdidsinceManagessinceDepartmentsEmployeesssnWorks_InMeans: “exactly one”Weak EntitiesA weak entity can be identified uniquely only by considering the primary key of another (owner) entity.–Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner, many weak entities).–Weak entity set must have total participation in this identifying relationship set. lotnameagepnameDependentsEmployeesssnPolicycostWeak entities have only a “partial key” (dashed underline)Binary vs. Ternary RelationshipsIf each policy is owned by just 1 employee:Bad designBeneficiaryagepnameDependentspolicyidcostPoliciesPurchasernameEmployeesssnlotBetter design• Think through all the constraints in the 2nd diagram!PoliciespolicyidcostagepnameDependentsCoversnameEmployeesssnlot Key constraint on Policies
View Full Document
Unlocking...