Entity-Relationship Diagrams and the Relational ModelReviewSlide 3Data ModelsWhy Study the Relational Model?Steps in Database DesignConceptual DesignER Model BasicsER Model Basics (Contd.)ER Model Basics (Cont.)Key Constraints…to be clear…Participation ConstraintsWeak EntitiesBinary vs. Ternary RelationshipsBinary vs. Ternary Relationships (Contd.)Slide 17Summary so farRelational Database: DefinitionsEx: Instance of Students RelationSQL - A language for Relational DBsSQL OverviewCreating Relations in SQLTable Creation (continued)Adding and Deleting TuplesKeysPrimary KeysPrimary and Candidate Keys in SQLForeign Keys, Referential IntegrityForeign Keys in SQLEnforcing Referential IntegrityIntegrity Constraints (ICs)Where do ICs Come From?Relational Query LanguagesThe SQL Query LanguageQuerying Multiple RelationsSemantics of a QueryCross-product of Students and Enrolled InstancesRelational Model: SummaryGOSUB XML;Databases for ProgrammersYUCK!!Object-Relational MappingsDetails, detailsRuby on RailsRails and ERSome Rails “Models”Further ReadingEntity-Relationship Diagrams and the Relational ModelCS 186, Fall 2007, Lecture 2R & G, Chaps. 2&3A 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)Review•Why use a DBMS? OS provides RAM and diskReview•Why use a DBMS? OS provides RAM and disk–Concurrency–Recovery–Abstraction, Data Independence–Query Languages–Efficiency (for most tasks)–Security–Data IntegrityData Models•DBMS models real world•Data Model is link between user’s view of the world and bits stored in computer•Many models exist•We think in terms of..–Relational Model (clean and common)–Entity-Relationship model (design)–XML Model (exchange)1010111101Student (sid: string, name: string, login: string, age: integer, gpa:real)Why Study the Relational Model? •Most widely used model.•“Legacy systems” in older models –e.g., IBM’s IMS•Object-oriented concepts merged in–“Object-Relational” – two variants•Object model known to the DBMS•Object-Relational Mapping (ORM) outside the DBMS–A la Rails•XML features in most relational systems–Can export XML interfaces–Can provide XML storage/retrievalSteps in Database Design•Requirements Analysis– user needs; what must database do?•Conceptual Design– high level description (often done w/ER model)– Rails encourages you to work here•Logical Design– translate ER into DBMS data model– Rails requires you to work here too•Schema Refinement – consistency, normalization•Physical Design - indexes, disk layout•Security Design - who accesses what, and howConceptual Design•What are the entities and relationships in the enterprise?•What information about these entities and relationships should we store in the database?•What integrity constraints or business rules 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 object, distinguishable from other objects. An entity is described using a 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.…to be clear…•Recall that each relationship has exactly one element of each Entity Set–“1-M” is a constraint on the Relationship Set, not each relationship•Think of 1-M-M ternary relationshipParticipation 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 would mean policy can only cover 1 dependent!Binary vs. Ternary Relationships (Contd.)•Previous example illustrated a case when two binary relationships were better than one ternary relationship.•An example in the other direction: a ternary relation Contracts relates entity sets Parts, Departments and Suppliers, and has descriptive attribute qty. No combination of binary relationships is an adequate substitute. (With no new entity sets!)Binary vs. Ternary Relationships (Contd.)–S “can-supply” P, D “needs” P, and D “deals-with” S does not imply that D has agreed to buy P from S.–How do we record
View Full Document
Unlocking...