Conceptual Design with ER Model

1. Conceptual Design with ER Model Lecture #2

2. Lecture Outline • Logistics • Steps in building a database application • Conceptual design with ER model

3. Steps in Building a DB Application • Step 1: Pick an application • something where you think you will have quite a bit of data • need to manage multiple users who process/query the data • Step 2: Application analysis and conceptual design • discuss what to model for the application • need a modeling language to express what you want • ER model is the most popular such language • output: an ER diagram of the application

4. Example ER Diagram name category name cid ssn Takes Course Student quarter Advises Teaches Professor name field address

5. Steps in Building a DB Application • Step 3: Translate the ER diagram into a relational schema • using a set of rules • Step 4: Refine the relational schema (normalization) • use a set of schema refinement rules to transform the above rel. schema into a good rel. schema • At this point • you have a good relational schema on paper

6. Example Relational Schema Students: Takes: Courses:

7. Steps in Building a DB Application • Step 5: Pick a relational DB management system (RDBMS) • MS SQL server, IBM DB2, Oracle • MySQL, PostreSQL • SQLite, ... • Step 6: Implement your DB in that RDBMS • first, implement the schema • next, add the tuples • you can do all these using a language called SQL

8. Steps in Building a DB Application • Step 7: Query your DB using SQL • now that your DB is in place, you can query it using SQL in the “command-line interface” Find all courses that Mary takes select C.namefrom Students S, Takes T, Courses Cwhere S.name = “Mary” and S.ssn = T.ssn and T.cid = C.cid

9. Steps in Building a DB Application • Subsequent steps • you may not want to access the DB just via the command line interface • ordinary users may not know how to interact with the database directly & ask SQL queries • and the database also cannot do everything you want; SQL is a limited language (not Turing complete, can’t do recursive stuff) • hence you often want to write an application program in C++, Java, Perl, etc to give lay users an easier way to query the DB and take care of things that the database cannot do • many such applications user a Web interface to interact with users

10. Database management system (RDBMS) Web browser interface PhP code DB 2 DB 1 App 2 DB 3

12. Lay users Database management system (RDBMS) PhP code DB 2 DB 1 DB 3 Database developer Database system administrator (DBA)

13. Steps in Building a DB Application • Subsequent steps • When the tables have many tuples, queries may run very slowly • so you may need to build indexes to speed up query execution • in the long run, need to maintain the DB, expand, clean, etc. • This class will discuss the above steps, and will look into the internals of RDBMSs

14. We Will Start with Step 2 • Step 1: Pick an application • something where you think you will have quite a bit of data • may need to manage multiple users who process/query the data • Step 2: Application analysis and conceptual design • discuss what to model for the application • need a modeling language to express what you want • ER model is the most popular such language • output: an ER diagram of the application

15. ER Model • Gives us a language to specify • what information the db must hold • what are the relationships among components of that information • Proposed by Peter Chen in 1976 • What we will cover • basic stuff: entities, relations • constraints • weak entity sets • design principles

16. Basic Concepts name category name price makes Company Product stockprice buys employs Person name ssn address

17. Entities and Attributes • Entities • real-world objects distinguishable from other objects • described using a set of attributes • Attributes • each has an atomic domain: string, integers, reals, etc. • Entity set: a collection of similar entities name price name category Product Company stockprice

18. makes Company Product 1 a b 2 A= c 3 B= d Relations • 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:

19. ER Diagram name category name price makes Company Product stockprice buys employs Person name ssn address

20. More about relationships ...

21. makes Company Product 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

22. makes Company Product 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

23. Important: Difference with the Cow Book (Raghu Book) • You should use the notations in the lectures • We will use Manages Employee Department • Cow book use (see Page 33, 3rd edition) Manages Employee Department

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

25. When creating an ER diagram, you will create various relationships • Then decide on the “cardinality” of those • should they be 1-1, 1-many, many-1, many-many, etc • Then specify these cardinalities on the ER diagram • You may or may not be able to specify the *exact* cardinality requirements • specially for multiway relationships • May need to approximate

26. Invoice VideoStore Rental Movie Person Example Given the following relationship Suppose invoice determines the rest How would we specify this?

27. Invoice VideoStore Rental Movie Person Example There is no good way; here’s a possible solution Q: Why is this incomplete ?

28. Invoice VideoStore Rental Movie Person A: Because of the meaning of such an arrow sign If I know the store, person, invoice, I know the movie too

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

30. This is a bit confusing, because we have no idea what role each Person entity would play • So we should label the edges to indicate the roles Product Purchase Store buyer salesperson Person Person

31. We can condense this further by collapsing the two Persons into one Product Purchase Store buyer salesperson Person

32. date Product Purchase Store Person Attributes on Relationships

33. Attributes of Entities and Relationships always have atomic values!

34. Consider Attribute Phone of Professors name category name cid ssn Takes Course Student quarter Advises Teaches Professor name phone address 34

35. Converting Multiway Relationships to Binary ProductOf date Product Purchase StoreOf Store BuyerOf Person

36. Relationships: Summary • Modeled as a mathematical set • Binary and multiway relationships • Converting a multiway one into many binary ones • Constraints on the degree of the relationship • many-one, one-one, many-many • limitations of arrows • Attributes of relationships • not necessary, but useful

37. Subclasses in ER Diagrams name category price Product isa isa Software Product Educational Product platforms Age Group

38. Subclasses • 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.

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

40. Example Beers name manf isa Ales color

41. Constraints • A constraint = an assertion about the database that must be true at all times • Part of the database schema • Very important in database design • When creating the ER diagram, you need to find as many constraints as possible • And specify them on the ER diagram

42. Modeling Constraints 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. Domain constraints: peoples’ ages are between 0 and 150. General constraints: all others (at most 50 students enroll in a class)

43. Why Constraints are Important? • Give more semantics to the data • help us better understand it • Prevent wrong data entry • Allow us to refer to entities (e.g, using keys) • Enable efficient storage, data lookup, etc.

44. 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

45. More about Keys • Every entity set must have a key • why? • A key can consist of more than one attribute • There can be more than one key for an entity set • one key will be designated as primary key • Requirement for key in an isa hierarchy • not covered in this lecture

46. Single Value Constraint • At most one value play a particular role • An attribute of a entity set has a single value • we can specify if the value must be present or can be missing (represented with say NULL or -1) • example in real-estate domain • price vs. house-style • A many-one relation implies single value const.

47. Referential Integrity Constraint • Single value constraint: at most one value exists in a given role • Ref. int. constraint: exactly one value exists in a given role • An attribute has a non-null, single value • this can be considered a kind of ref. int. constraint • However, we more commonly use such constraints to refer to relationships

48. Referential Integrity Constraints • In some formalisms we may refer to other object but get garbage instead • e.g. a dangling pointer in C/C++ • the Referential Integrity Constraint on relationships explicitly requires a reference to exists

49. Referential Integrity Constraints makes Product Company makes Product Company • This will be even clearer once we get to relational databases

50. Other Kinds of Constraints • Domain constraints • Constraints on degree of a relationship • Other more general constraints • See the book