WIXLIB

Introduction to Data Management*** The“Flipped” Edition ***Lecture #5(ERà Relational Mapping)Instructor: Mike Careymjcarey@ics.uci.eduSQLDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke1

Today’s Notices Keep watching the wiki page: And, of course, follow the Piazza page: piazza.com/uci/fall2021/cs122aeecs116/homeHW#1 is in flight! (Keep Q’s coming on Piazza.) http://www.ics.uci.edu/ cs122a/SWOOSH.comAnd, of course, avoid discussing solutions publicly here!Remember that quizzes run from Wed 3PM – Fri 3PM Don’t miss the free points! (You will if you fall behind!)Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke2

Review: Weak Entities 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 weak entities).Weak entity set must have total participation in thisidentifying relationship set.namessncostlotEmployees1PolicyDatabase Management Systems 3ed, R. Ramakrishnan and J. GehrkednameageNDependents3

Translating Weak Entity Sets Weak entity set and identifying relationshipset are translated into a single table. When the owner entity is deleted, all of its ownedweak entities must also be deleted.CREATE TABLE Dependents2 (dname VARCHAR(20),age INTEGER,cost REAL,ssn CHAR(11) NOT NULL,PRIMARY KEY (dname, ssn),FOREIGN KEY (ssn) REFERENCES EmployeesON DELETE CASCADE)Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke4

Review: IsA HierarchiesAs in C , or other PLs,hourly wagesattributes are inherited. If we declare A IsA B, thenevery A entity is also consideredto be a B entity.namessnEmployees lothours workedIsAcoveringHourly EmpscontractidContract EmpsOverlap constraints: Can employee Joe be an Hourly Empsas well as a Contract Emps entity? (disjoint if not)Covering constraints: Must every Employees entity be eitheran Hourly Emps or a Contract Emps entity? (covering if so)Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke5

From IsA Hierarchies to Relations Most general and “clean” approach (recommended):3 relations: Employees, Hourly Emps, & Contract Emps. Hourly Emps: Every employee recorded in Employees.For hourly emps, extra info recorded in Hourly Emps(hourly wages, hours worked, ssn); delete Hourly Empstuple if its referenced Employees tuple is deleted. Queries about all employees easy; those involving justHourly Emps require a join to access the extra attributes. Another alternative: Hourly Emps & Contract Emps. Ex: Hourly Emps(ssn, name, lot, hourly wages, hours worked) If each employee must be in one of the two subclasses.(Q: Can we always do this, then? A: Not w/o redundancy!) Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke6

IsA Hierarchy Translation Options I. “Delta table” approach (recommended): Emps(ssn, name, lot) (All Emps partly reside here) Hourly Emps(ssn*, wages, hrs worked) Things to consider: Expected queries? Contract Emps(ssn*, contractid) PK/unique constraints?II. “Union of tables” approach: Relationships/FKs? Overlap constraints? Space/time tradeoffs? Emps(ssn, name, lot) Hourly Emps(ssn, name, lot, wages, hrs worked) Contract Emps(ssn, name, lot, contractid) III. “Mashup table” approach: Emps(kind, ssn, name, lot, wages, hrs worked, contractid)(*ssn here is both a local PK as well as an FK referencing Emps)Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke7

IsA Considerations (cont’d.) Query convenience Ex: List the names of all Emps in lot 12A PK enforcement Ex: Make sure that ssn is unique for all Emps Relationship (FK) targets Ex: Lawyers table REFERENCES Contract Emps Handling of overlap constraints Ex: Sally is under a contract for her hourly work Space and query performance tradeoffs Ex: List all the info about hourly employee 123 Ex: What if most employees are “just plain employees”?Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke8

M. Carey, Winter 2020: CS 190 (CS122D Beta)9Logical Design for SQL (Ex. 2)Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke9

M. Carey, Winter 2020: CS 190 (CS122D Beta)10Logical Design for SQL (Ex. 2) Again, an IsA hierarchy in an E-R schema can be“tabularized” for SQL in one of at least three ways: Delta tables Root table defines the PK and the top-level fields “Sub-tables” have the same PK (also an FK to its parent) and the newly-appearingfieldsJoin queries are needed to materialize all instances of a non-root entity set Mashup table The ”one table to rule them all” – has all fields from everywhere in the entity hierarchy Must also tag rows with information about their particular entity type(s)Union tables Every table has the same PK plus all fields (newly-appearing and inherited) for its entity typeEach entity in the hierarchy therefore lives (fully and only) in a table unique to itsentity type/subtypeUnion-all queries are needed to materialize all instances of a non-leaf entity type(Comes up short w.r.t. PK enforcement)Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke10

M. Carey, Winter 2020: CS 190 (CS122D Beta)11Logical Design for SQL (Ex. 2) Delta tablesCREATE TABLE Video ( id INTEGER PRIMARY KEY, title VARCHAR, description VARCHAR, . )CREATE TABLE Full Video ( id INTEGER PRIMARY KEY, FOREIGN KEY (id) REFERENCES (Video) )CREATE TABLE Trailer ( id INTEGER PRIMARY KEY, trailer for INTEGER,FOREIGN KEY (id) REFERENCES (Video), FOREIGN KEY (trailer for) REFERENCES (Full Video) )CREATE TABLE TV Show ( id INTEGER PRIMARY KEY, season INTEGER, episode INTEGER,FOREIGN KEY (id) REFERENCES (Full Video) )CREATE TABLE Movie ( id INTEGER PRIMARY KEY, rating VARCHAR, FOREIGN KEY (id) REFERENCES (Full Video) ) Mashup tableCREATE TYPE VIDEO KIND AS ENUM (‘full video’, ‘trailer’, ‘tv show’, ‘movie’); -- Q: Why the absence of ‘video’ here?CREATE TABLE Video ( id INTEGER PRIMARY KEY, title VARCHAR, description VARCHAR, . ,trailer for INTEGER, season INTEGER, episode INTEGER, rating VARCHAR, kind VIDEO KIND,FOREIGN KEY (trailer for) REFERENCES (Video) ) Union tables(covering)CREATE TABLE Video ( id INTEGER PRIMARY KEY, title VARCHAR, description VARCHAR, . )CREATE TABLE . -- one table per entity typeCREATE TABLE Movie ( id INTEGER PRIMARY KEY, title VARCHAR, description VARCHAR, . , rating VARCHAR)Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke11

Mapping Advanced ER Features Multi-valued (vs. single-valued) attributesssnnamephoneEmployees Composite (vs. atomic) attributesnamessnaddressEmployees phones(ssn, phone) ssn is an FK in this table (ssn, phone) is its PKsnumstreetcityEmployeeszipEmployees(ssn, name, address snum, address street, address city, address zip)Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke12

Mapping Advanced ER Features (cont.) Mandatory (vs. optional) attributesnamessnagegender Employees Employees(ssn, name, age, gender) ssn is the PK name VARCHAR(20) NOT NULL Note: PRIMARY KEY à NOT NULLDerived (vs. stored) attributesnamessnbdateageEmployeesDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke13

SQL Views (and Security) A view is just a relation, but we store its definitionrather than storing the (materialized) set of tuples.CREATE VIEW YoungStudents (name, login)AS SELECT S.name, S.loginFROM Students SWHERE S.age 19; Views can be used to present needed informationwhile hiding details of underlying table(s). Given YoungStudents (but not Students), we can see(young) students S with only their names and logins.Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke14

SQL Views (Cont’d.) Other view uses in our ER translation contextmight include: Derived attributes, e.g., age (vs. birthdate) Simplifying/eliminating join paths (for SQL) Beautifying the “Mashup table” approach (to IsA)CREATE VIEW EmployeeView (ssn, name, bdate, age)AS SELECT E.ssn, E.name, E.bdate,date part('year', age(E.bdate))::intFROM Employees E;Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke15

Review: Putting the Basics tempriceForDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke?qty16

Review: Putting It Together (Cont’d.)CREATE TABLE Order (CREATE TABLE Customer (oid INTEGER,cid INTEGER,custid INTEGER,cname VARCHAR(50),shipto VARCHAR(200),login VARCHAR(20)total DECIMAL(8,2),NOT NULL,PRIMARY KEY (oid),PRIMARY KEY (cid),FOREIGN KEY (custid) REFERENCES Customer))UNIQUE (login))CREATE TABLE LineItem (oid INTEGER,CREATE TABLE Product (lno INTEGER,sku INTEGER,price DECIMAL(8,2),pname VARCHAR(100),qty INTEGER,color VARCHAR(20),sku INTEGER,listprice DECIMAL(8,2),PRIMARY KEY (oid, lno),PRIMARY KEY (sku))FOREIGN KEY (oid) REFERENCES OrderON DELETE CASCADE),FOREIGN KEY (sku) REFERENCES Product))Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke17

Review: Putting It Together (Cont’d.)CustomerProductcidcnamelogin1Smith, Jamesjsmith@aol.com2White, Susansuzie@gmail.com3Smith, Jamesjs@hotmail.comskupnamecolorlistprice123Frozen DVDnull24.95456Graco Twin Strollergreen199.99789Moen Kitchen dlnopriceqtyitem13J. Smith, 1 Main St., USA199.9511169.95145621Mrs. Smith, 3 State St.,USA300.001215.00212321300.001789Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke18

Wait! Clarifying IsA Mappings.namessnlotEmployeeshourly wageshours workedIsAcoveringHourly EmpscontractidContract EmpsDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke19

Delta Table ull4Chen11Note 1: Joe can’t really exist here (covering)Note 2: Chen couldn’t exist if we’d said disjointHourly EmpsContract Empsssnwageshrs workedssncontract id2154031014250104102Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke20

Union Table ApproachEmployeesssnnamelot1Joe12Note 1: Joe can’t really exist here (covering)Hourly EmpsContract Empsssnnamelotwageshrs workedssnnamelotcontract 2Note 3: No CREATE TABLE way to prevent ssn duplicationDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke21

Mashup Table ApproachEmployeesssnis hourlyis contractnamelotwageshrs workedcontract 1125010102Note 1: Joe can’t really exist here (covering)Note 4: Might want to create VIEWs on top.Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke22

Relational Model and E-R SchemaTranslation: Summary Relational model: Tabular data representation.Simple and intuitive, very widely used (RDBMSs)Integrity constraints – specified by DBA based onapplication semantics. (DBMS prevents violations) Most important ICs: Primary and foreign keys (PKs and FKs)In addition, we also have domain (column type) constraintsHigh-level query languages (including SQL!)Rules to translate E-R to relational model Can be done by a human or automatically (via a tool) Entities, relationships, attributes; cardinality, participation,. IsA handling; composite, multi-valued, and derived attributesDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke23

Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 3 Review: Weak Entities A 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 on