Introduction to Database Systems

1. Introduction to Database Systems CS363/607* Lecture #2

2. Last class • What is a database? • What is a DBMS? • Database history • DBMS components • Terminology Data model, schema, data, DDL, DML, DBA Relational database system

3. Entity-Relationship Model • The E/R model allows us to sketch the design of a database informally. • Designs are pictures called entity-relationship diagrams. • Fairly mechanical process to convert E/R diagrams to real implementations like relational databases exist.

4. Database modeling process

5. Entity Sets • Entity = “thing”, an abstract object of some sort. • Similar to an object in object-oriented languages. • Entity set = a collection of similar entities. • Similar to a class in object-oriented languages. • Examples: students, movies, stars

6. Attributes • Attributes = properties of the entities in an entity set. • Generally, all entities in a set have the same properties. • Attributes are simple values, e.g. integers or character strings. • Examples: studentNo, accountNo

7. Relationships • Relationships are connections among two or more entity sets. • Example: Entity: Star, movie Relation: Star-in A movie entity m is related to a star entity s by the relationship star-in if s appears in movie m

8. E/R Diagrams In an entity-relationship diagram: • Each entity set is represented by a rectangle. • Each attribute of an entity set is represented by an oval, • Relationships are represented by diamonds • Edges are used to connect an entity to its attributes and also connect a relation ship to its entity sets.

9. Example • Entity set Beers has two attributes, name and manufacturer. • Each Beer entity has values for these two attributes, e.g. (Budweiser, Anheuser-Busch) name manufacturer Beers

10. 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 Example

11. Entity Set and Relationship Set • The current “value” of an entity set is the set of entities that belong to it. • Example: the set of all bars • The “value” of a relationship is a set of lists of currently related entities, one from each of the related entity sets.

12. Example Bar Beer Joe’s Bar Budweiser Joe’s Bar Miller Sue’s Bar Budweiser Sue’s Bar Pete’s Ale Sue’s Bar Bud Lite

13. Example name phone ID Students height Each student has four attributes: ID, name, phone, height

14. Example Students Courses Sally C131 Sally C363 Joe M130 … … Taking Students Courses

15. Example

16. Example: An instance The members of the relationship set are the rows of the table. For Instance, (Basic Instinct, Sharon Stone) Is a tuple in the relationship set for the current instance of relationship Star-in

17. Binary Relationship • A binary relationship can connect any member of one of its entity sets to any members of the other entity set. It is the simplest relationship which only connects two entity sets. • Example Student Class

18. Many-Many Relationships • In a many-many relationship, an entity of either set can be connected to many entities of the other set. • Example: • Think of a relationship between two entity sets, such as Sells between Bars and Beers. A bar sells many beers; a beer is sold by many bars.

19. Many-One Relationships • Some binary relationships are many -one from one entity set to another. • Each entity of the first set is connected to at most one entity of the second set. • But an entity of the second set can be connected to zero, one, or many entities of the first set.

20. Example • Favorite, from Drinkers to Beers is many-one. • A drinker has at most one favorite beer. • But a beer can be the favorite of any number of drinkers, including zero.

21. One-One Relationships • In a one-one relationship, each entity of either entity set is related to at most one entity of the other set. • Example: Relationship Best-seller between entity sets Manufacturers and Beers. • A beer cannot be made by more than one manufacturer, and no manufacturer can have more than one best-seller (assume no ties).

22. Example Runs Studios Presidents

23. Representing “Multiplicity” • Show a many-one relationship by an arrow entering the “one” side. • Show a one-one relationship by arrows entering both entity sets. • In some situations, we can also assert “exactly one,” i.e., each entity of one set must be related to exactly one entity of the other set. To do so, we use a rounded arrow.

24. many-many many-one one-one In Picture • Representation of Many-One • E/R: arrow pointing to “one.” • Rounded arrow = “exactly one.”

25. Example Likes Drinkers Beers Favorite

26. Example • Consider Best-seller between Manufacturers and Beers. • Some beers are not the best-seller of any manufacturer, so a rounded arrow to Manufacturers would be inappropriate. • But a manufacturer has to have a best-seller (we assume they are beer manufacturers).

27. In the E/R Diagram Best- seller Manufacturers Beers

28. Roles • Sometimes an entity set appears more than once in a relationship. • Label the edges between the relationship and the entity set with names called roles.

29. Relationship Set Husband Wife Bob Ann Joe Sue … … Married husband wife Drinkers Example

30. Attributes on Relationships • Sometimes it is useful to attach an attribute to a relationship. • Think of this attribute as a property of tuples in the relationship set.

31. price Sells Bars Beers price Prices Sells Bars Beers

32. Multiway Relationships • Sometimes, we need a relationship that connects more than two entity sets. • Suppose that drinkers will only drink certain beers at certain bars. • Our three binary relationships Likes, Sells, and Frequents do not allow us to make this distinction. • But a 3-way relationship would.

33. name addr name manf Bars Beers license Preferences Drinkers name addr Example

34. Bar Drinker Beer Joe’s Bar Ann Miller Sue’s Bar Ann Budweiser Sue’s Bar Ann Pete’s Ale Joe’s Bar Bob Budweiser Joe’s Bar Bob Miller Joe’s Bar Cal Miller Sue’s Bar Cal Bud Lite A Typical Relationship Set

35. Example Contract Stars Movies Studios (Studio, Star, Movie)

36. Example

38. BBP The-Bar The-Beer The-Price Bars Beers Price Converting Multiway to 2-Way • Many-one relationships from the connecting E.S. to the others.

39. A four-way relationship

40. Example

41. Subclasses • An entity set contains certain entities that have special properties not associated with all members of the set. • Subclass = special case = fewer entities = more properties. • Example: Ales are a kind of beer. • Not every beer is an ale, but some are. • Let us suppose that in addition to all the properties (attributes and relationships) of beers, ales also have the attribute color.

42. Subclasses in E/R Diagrams • Assume subclasses form a tree. • I.e., no multiple inheritance. • Isa triangles indicate the subclass relationship. • Point to the superclass.

43. Example Beers name manufacturer isa Ales color

44. Example

45. E/R Vs. Object-Oriented Subclasses • In the object-oriented world, objects are in one class only. • Subclasses inherit properties from superclasses. • In contrast, E/R entities have components in all subclasses to which they belong. • Matters when we convert to relations.

46. Pete’s Ale Example Beers name manf isa Ales color

47. Exercise 1 • Let us design a database for a bank, including information about customers and their accounts. Information about a customer includes their name, address, phone, and SSN. Accounts have numbers, types and balances. We also need to record the customers who own an account. Draw the E/R diagram for this database. Be sure to include arrows where appropriate, to indicating the multiplicity of a relationship.

48. Solution

49. Summary • E/R model • Elements of E/R model • Entity sets • Attributes • Relationships • Entity-Relationship diagram • Binary and multiway relationships • Roles • Converting multiway to binary • subclass

50. Design principle • Faithfulness • Avoiding redundancy • Simplicity counts • Choosing the right relationships • Picking up the right kind of elements