Understanding Database Design and Implementation

1. Midterm Review I

2. Database Studies • Design of databases. • What kinds of information go into the database? • How is the information structured? • How do data items connect? • Database programming. • How does one express queries on the database? • How does one use other capabilities of a DBMS, such as transactions or constraints, in an application? • How is database programming combined with conventional programming? • Database system implementation. • How does one build a DBMS, including such matters as query processing, transaction processing and organizing storage for efficient access?

3. Ideas E/R design Relational schema Relational DBMS A mechanical process Abstract design Concrete design Database Modeling

4. name addr name manf Bars Beers Sells Bars sell some beers. license Drinkers like some beers. Frequents Likes Note: license = beer, full, none Drinkers frequent some bars. Drinkers name addr “Bars-Beer-Drinkers” (BBD) Example Why we need it?

5. Ternary Relationships name addr name manf Bars Beers license Preferences Why we would need it? Drinkers name addr

6. Weak Entity Sets • Occasionally, entities of an entity set need “help” to identify them uniquely. • Entity set E is weak if in order to identify entities of E uniquely, we need to follow one or more many-one relationships from E and include the key of the related entities from the connected entity sets. • Example. Crews might have a number and some description, • E.g. “Crew 1, Special Effects.” However, there can be another crew with the same data “Crew 1, Special Effects” but which belongs to another studio. • Clearly, we need to add the key for studios, in order to have a key for crews.

7. address name role number No. of emp. Studios Crews Unit-of In E/R diagrams • Double diamond for supporting many-one relationship. • Double rectangle for the weak entity set.

8. Another Example – Football Players • name is almost a key for football players, but there might be two with the same name. • numberis certainly not a key, since players on two teams could have the same number. • But number, together with the team name related to the player by Plays-on should be unique.

9. name number name Plays- on Players Teams In E/R Diagrams • Double diamond for supporting many-one relationship. • Double rectangle for the weak entity set.

10. Weak Entity-Set Rules • A weak entity set has one or more many-one relationships to other (supporting) entity sets. • Not every many-one relationship from a weak entity set need be supporting. • The key for a weak entity set is its own underlined attributes and the keys for the supporting entity sets.

11. Supporting relationships role number name address Operat. Cities Crews Studios Unit-of Op No. of emp. • The supporting relationships must have the referential integrity enforced. • I.e. rounded arrow in the “one side”. • If there are more than one supporting relationships, then all of them will “bring” a key component for the key of the weak entity set. • Not all the many-one relationships connecting a weak entity set (many side) to other entity sets are supporting relationships. E.g.

12. When Do We Need Weak Entity Sets? • The usual reason is that there is no global authority capable of creating unique ID’s. • Example: it is unlikely that there could be an agreement to assign unique player numbers across all football teams in the world.

13. length title year filmType to Stars Movies Voices isa isa weapon Cartoons Murder- Mysteries Subclasses • Often, a class contains certain objects that have special properties not associated with all members of the class. • If so, we find it useful to organize the class into subclasses, each subclass having its own special attributes and/or relationships. • We relate the parent with child entity sets by a special (1-1) relationship called isa.

14. The Relational Data Model Database Model (E/R) Relational Schema Physical storage Complex file organization and index structures. Diagrams (E/R) Tables: row names: attributes rows: tuples

15. Terminology Attribute names Title Year Length FilmType Star Wars 1997 124 color Mighty Ducks 1991 104 color Wayne’s World 1992 95 color . . .. . .. . . . . . components of tuples tuples

16. More Terminology Every attribute has an atomic type. Relation Schema: relation name + attribute names + attribute types Relation instance: a set of tuples. Only one copy of any tuple! Database Schema: a set of relation schemas. Database instance: a relation instance for every relation in the schema.

17. From E/R Diagrams to Relations • Entity sets become relations with the same set of attributes. • Relationships become relations whose attributes are only: • The keys of the connected entity sets. • Attributes of the relationship itself.

18. year title Movies filmType length Entity Sets to Relations Relation schema: Movies(title, year, length, filmtype ) A relation instance:

19. E/R Relationships to Relations • Relationships in the E/R model are also represented by relations. • The relation for a given relationship Rhas the following attributes: • For each entity set involved in relationship, we take its key attribute(s) as part of the schema of the relation for R. • If the relationship has attributes, these are also attributes of relation R. • When one entity set is involved several times in a relationship - or - the same attribute name appears in the keys of different ES participating in the relationship R, - or - even when we like to avoid confusion and to be clearer in meaning we should rename the attributes.

20. name name addr manf Drinkers Likes Beers husband 2 1 Favorite Buddies Likes(drinker, beer) Favorite(drinker, beer) wife Married Buddies(name1, name2) Married(husband, wife) Example (with Renaming)

21. Combining Relations • It’s OK to combine the relation for an entity-set E with the relation R for a many-one relationship fromE to another entity set. • Example: • Drinkers(name, addr) and Favorite(drinker, beer) combine to make • Drinker1(name, addr, favBeer)

22. name addr beer Sally 123 Maple Bud Sally 123 Maple Miller Redundancy Risk with Many-Many Relationships • Combining Drinkers with Likes would be a mistake. Why? • It leads to redundancy, as:

23. Handling Weak Entity Sets • Relation for a weak entity set must include attributes for its complete key (including those belonging to other entity sets), as well as its own, nonkey attributes. • A supporting (double-diamond) relationship is redundant and yields no relation.

24. name number name Plays- on Players Teams Must be the same PlaysOn becomes part of Players Example Teams(name) Players(number, teamName, name) PlaysOn(number, teamName, teamName2)

25. name manf manfAddr Beers Entity Sets Versus Attributes I • Example: Bad design This design repeats the manufacturer’s address once for each beer; Loses the address if there are temporarily no beers for a manufacturer.

26. Ex.1,2 location area eventid date np cid Takes place Complexes Events Used for Involved Chief Org MultiSport OneSport Officials Equipment locIndicator Designated for Designated for oid name equipid description Sports Complexes(cid,location,area) MultiSport(cid) DesignatedFor(cid,sportname,locIndicator) OneSport(cid,sportname) Officials(oid, name) Events(eventid,date,np,chief_org_oid) Involved(oid,eventid) Equipment(equipid,description) UsedFor(equipid,eventid) name

27. Ex.3,4 name dob Personnel(pid,name,dob,pob) Players(pid,ba,baorientation,tid) Pitchers(pid,era) Coaches(pid,tid) Manangers(pid,tid) Umpires(pid) Teams(tid,name,city,division) Games(gid,date,winning_pid,losing_pid) Plays(gid,tid1,tid2,runs1,hits1,error1, runs2,hits2,error2) Hits(gid,pid,singles,doubles,triples,hruns) SavePitcher(gid,pid) pid pob Personnel ba borientation Players Coaches Managers Umpires coaches manages era memberOf name tid city Pitchers Teams singles division doubles save hits triples runs1 winning losing plays hruns hits1 errors1 Games runs2 errors2 hits2 gid date

28. Ex.5a number name code phone name dob Enrolled Students Departments semester Registered Offered grade Courses location name desc

29. Ex.5b number name code phone name DOB Enrolled Students Departments Registered Semesters Offered grade SemId Courses location name desc

30. Exercise 3.6.1 R(A,B,C,D) with FD’s ABC, CD, and DA Indicate all the BCNF violations. Do not forget to consider FD’s that are not in the given set, but follow from them. Indicate all the BCNF violations. Do not forget to consider FD’s that are not in the given set, but follow from them.

31. Babies Exercise 2.2.5: At a birth, there is one baby (twins would be represented by two births), one mother, any number of nurses, and any number of doctors. Suppose, therefore, that we have entity sets Babies, Mothers, Nurses, and Doctors. Suppose we also use a relationship Births, which connects these four entity sets. Note that a tuple of the relationship set for Births has the form (baby, mother, nurse, doctor)

32. Babies (Cont’ed) There are certain assumptions that we might wish to incorporate into our design. For each, tell how to add arrows or other elements to the E/R diagram in order to express the assumption. a) For every baby, there is a unique mother. c) For every combination of a baby and a mother there is a unique doctor.