Database Management Systems

1. Database Management Systems Entity-Relationship Model

2. Database Design • Why do we need it? • Agree on structure of the database before deciding on a particular implementation. • Consider issues such as: • What entities to model • How entities are related • What constraints exist in the domain • How to achieve good designs

3. Database Design Formalisms 1. Object Definition Language (ODL): • Closer in spirit to object-oriented models 2. Entity/Relationship model (E/R): • More relational in nature. • Both can be translated (semi-automatically) to relational schemas • ODL to OO-schema: direct transformation (C++ or Smalltalk based system).

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

5. Entity / Relationship Diagrams Product Entities Attributes Relationships between entities address buys

6. name category name price makes Company Product stockprice buys employs Person name ssn address

7. University Example • A college contains many departments • Each department can offer any number of courses • Many instructors can work in a department • An instructor can work only in one department • For each department there is a Head • An instructor can be head of only one department • Each instructor can take any number of courses • A course can be taken by only one instructor • A student can enroll for any number of courses • Each course can have any number of students

8. Modeling • A database can be modeled as: • a collection of entities, • relationship among entities. • An entityis an object that exists and is distinguishable from other objects. • Example: person, company, event, plant • Entities have attributes • Example: people have names and addresses • An entity set is a set of entities of the same type that share the same properties. • Example: set of all persons, companies, trees, holidays

9. Entity Sets instructor and student instructor_ID instructor_name student-ID student_name

10. ER Case Study Banks Database • Each bank has a unique name. • Each branch has a number, name, address (number, street, city), and set of phones. • Customer includes their name, set of address (P.O. Box, city, zip code, country), set of phones, and social security number. • Accounts have numbers, types (e.g. saving, checking) and balance. Other branches might use the same designation for accounts. So to name an account uniquely, we need to give both the branch number to which this account belongs to and the account number. • Not all bank customers must own accounts and a customer may have at most 5 accounts in the bank. • An account must have only one customer. • A customer may have many accounts in different branches.

11. Relationship Sets • A relationship is an association among several entities Example: 44553 (Ahmed)advisor 22222 (Hassan)student entity relationship set instructor entity • A relationship set is a mathematical relation among n 2 entities, each taken from entity sets {(e1, e2, … en) | e1  E1, e2  E2, …, en  En}where (e1, e2, …, en) is a relationship • Example: (44553,22222)  advisor

12. Relationship Set advisor

13. Relationship Sets (Cont.) • An attribute can also be property of a relationship set. • For instance, the advisor relationship set between entity sets instructor and student may have the attribute date which tracks when the student started being associated with the advisor

14. makes Company Product 1 a b 2 A= c 3 B= d What is a Relation ? • A mathematical definition: • if A, B are sets, then a relation R is a subset of A x B • A={1,2,3}, B={a,b,c,d}, R = {(1,a), (1,c), (3,b)} - makes is a subset of Product x Company:

15. Relationships • Relationships indicate a meaningful connection between two entity types • Relationships may have attributes, but they cannot have key attributes. • Identifying relationships connect a weak entity type to some other entity type • indicates where the weak entity gets a key to complete its own partial key WorksOn DependentOf IdentifyingRelationship Relationship

16. 1 2 3 1 2 3 1 2 3 a b c d a b c d a b c d Multiplicity of E/R Relations • one-one: • many-one • many-many

17. Mapping Cardinalities One to many One to one Note: Some elements in A and B may not be mapped to any elements in the other set

18. Mapping Cardinalities Many to one Many to many Note: Some elements in A and B may not be mapped to any elements in the other set

19. One-to-One Relationship • one-to-one relationship between an instructor and a student • an instructor is associated with at most one student via advisor • and a student is associated with at most one instructor via advisor

20. One-to-Many Relationship • one-to-many relationship between an instructor and a student • an instructor is associated with several (including 0) students via advisor • a student is associated with at most one instructor via advisor,

21. Many-to-Many Relationship • An instructor is associated with several (possibly 0) students via advisor • A student is associated with several (possibly 0) instructors via advisor

22. Alternative Notation for Cardinality Limits • Cardinality limits can also express participation constraints

23. Product Purchase Store Person Multi-way Relationships How do we model a purchase relationship between buyers, products and stores? Can still model as a mathematical set (how ?)

24. Relational Roles • It is sometimes convenient to name an entity’s role in a relationship. • particularly useful in recursive relationships • removes ambiguity in direction of relationship Supervision supervisor supervisee EMPLOYEE

25. Roles in Relationships What if we need an entity set twice in one relationship? Product Purchase Store buyer salesperson Person

26. Data Modeling Case Study • The following is description by a pharmacy owner: • “Ahmed Hassan catches a cold and what he suspects is a flu virus. He makes an appointment with his family doctor who confirm his diagnosis. The doctor prescribes an antibiotic and nasal decongestant tablets. • Ahmed leaves the doctor's office and drives to his local drug store. The pharmacist packages the medication and types the labels for pill bottles. • The label includes information about customer, the doctor who prescribe the drug, the drug (e.g., Penicillin), when to take it, and how often, the content of the pill (250 mg), the number of refills, expiration date, and the date of purchase." • Please develop a data model for the entities and relationships within the context of pharmacy.

27. Attributes • An entity is represented by a set of attributes, that is descriptive properties possessed by all members of an entity set. • Example: instructor = (ID, name, street, city, salary ) course= (course_id, title, credits) • Domain – the set of permitted values for each attribute • Attribute types: • Simple and composite attributes. • Single-valued and multivalued attributes • Example: multivalued attribute: phone_numbers • Derived attributes • Can be computed from other attributes • Example: age, given date_of_birth

28. Sets and Derived Attributes • Multivalued attributes  double lined oval • multivalued = set valued • that there may be more than one value for the attribute. • Derived attributes  dashed line ovals • the attribute is computed from other data Locations NumEmployees MultivaluedAttribute DerivedAttribute

29. Composite Attributes • Composite attributes  tree • composed of other attributes. • used for a set of related attributes, when the set is not a conceptual entity • the composite doesn’t have identity … it doesn’t have a key Address City ZipCode Street State CompositeAttribute

30. Composite Attributes

32. Attributes on Relationships date Product Purchase Store Person

33. Converting Multi-way Relationships to Binary ProductOf date Product Purchase StoreOf Store BuyerOf Person

34. ER Case Study Television Series Database A Television network wishes to create a database to keep track of its TV series. A television series has one or more episode. Television series identified by name and season number, and includes their production company name and Num_of_Episodes ( i.e. total number of episodes in a specific season of a series ). Episode of a specific season of a series is identified by episode number and has a title and a length. No episode can exist without a corresponding television series. Also each episode has only one writer. A writer is identified by name, and also has birth date and a literary agency that represents him or her. An actor appears as a performer in a television series or a guest star on an episode. An actor is identified by name and also has a nationality and birth date. An actor plays a particular character in a television series or episode.

35. Keys in E/R Diagrams • Every entity must have a key name category price Product

36. Attributes and Keys • Key attributes must be unique for each entity • Keys are used to identify particular entities • Partial keys are only partially unique • used for weak entity types Age SSN Date Key Attribute Partial Key Attribute Attribute

37. Keys • A super key of an entity set is a set of one or more attributes whose values uniquely determine each entity. • A candidate key of an entity set is a minimal super key • ID is candidate key of instructor • course_id is candidate key of course • Although several candidate keys may exist, one of the candidate keys is selected to be the primary key.

38. Keys in E/R Diagrams name category Underline: price Product No formal way to specify multiple keys in E/R diagrams Person name ssn address

39. Entity With Composite, Multivalued, and Derived Attributes

40. E-R Diagrams • Rectangles represent entity sets. • Diamonds represent relationship sets. • Attributes listed inside entity rectangle • Underline indicates primary key attributes

41. name category name price makes Company Product stockprice buys employs Person name ssn address

42. Relationship Sets with Attributes

43. Entity Set to Relation name category price Product Product(name, category, price) name category price gizmo gadgets $19.99

44. Classroom Design Exercise Imagine we are creating a database for a dorm, which includes a cooperative kitchen. • We want to record certain information about each resident. What? • Not all residents belong to the kitchen coop. Those that do interact in various ways: • They take turns at various jobs: preparer, cleanup, buyer (for supplies). No one should have two jobs on one day. • They may or may not be vegetarian. Each meal must have at least one vegetarian entreé. • They pay fees to the coop. • For each meal, there is a menu. Each menu item requires certain ingredients, which must be on hand.

45. Constraints in E/R Diagrams Finding constraints is part of the modeling process. Commonly used constraints: Keys: social security number uniquely identifies a person. Single-value constraints: a person can have only one father. Referential integrity constraints: if you work for a company, it must exist in the database. Other constraints: peoples’ ages are between 0 and 150.

46. Other Constraints makes <100 Product Company What does this mean ?

47. EMPLOYEE Entity Entity Types • Entity types boxes Weak entity type  double box DEPENDENT Weak Entity

48. Weak Entity Sets Entity sets are weak when their key comes from other classes to which they are related. affiliation Team University sport number name

49. Weak Entity Sets Sometimes an E.S. E’s key comes not (completely) from its own attributes, but from the keys of one or more E.S.’s to which E is linked by a supporting many-one relationship. • Called a weak E.S. • Represented by putting double rectangle around E and a double diamond around each supporting relationship. • Many-one-ness of supporting relationship (includes 1-1) essential. • With many-many, we wouldn't know which entity provided the key value. • “Exactly one” also essential, or else we might not be able to extract key attributes by following the supporting relationship.

50. Example: Logins (Email Addresses) Login name = user name + host name, e.g., ark@soe.ucsc.edu. • A “login” entity corresponds to a user name on a particular host, but the passwd table doesn’t record the host, just the user name, e.g., ark. • Key for a login = the user name at the host (which is unique for that host only) + the IP address of the host (which is unique globally). name name @ @ Logins Hosts