Data Modeling

1. Data Modeling

2. What are you keeping track of? • You begin to develop a database by deciding what you are going to keep track of. Each “thing” that you are want to keep track becomes an entity in your database. • Example – A book dealer might want to keep track of books, authors, and publishers.

3. What do you want to know about each entity. • Decide what to know about each entity. • Each piece of information becomes an attribute of the entity. • Example – for an author the book dealer might want to keep track of the name (first, middle, and last), date of birth, and date of death.

4. Recap • Entity - person, place, thing or event on which we maintain information. • Attribute - A single piece of information describing a particular entity.

5. ER – Diagram (1) • It is often useful to use a diagram to visually represent a data model. • A common diagramming tool is the Entity-Relationship (ER) Diagram. • In an ER Diagram an entity is represented as a rectangle. • The attributes associated with the entity can be listed by the rectangle.

6. ER-Diagram (2) Title Date Edition Author Book ID Last_Name First_Name Middle_Name DOB DOD Publisher Name Address Phone

7. Book Dealer Entities • The book dealer data model has three entities • Author • Book • Publisher • Note that the name of each entity is • a noun • singular

8. Attributes • Each entity has one or more attributes associated with it. • If an attributed is underlined it is part of the primary key for that entity. • Note that each entity has a primary key defined. Since a primary key cannot be null (blank) each entity exhibits entity integrity.

9. Implementing you data model in Access • The Access DBMS uses the Relational Data Model • In a Relational Data Model • each entity is represented as a table • each attribute is represented as a field in a table • every table has a primary key defined to ensure entity integrity

10. Instances of an Entities • Once you’ve created your tables you enter data into the rows. • Each table represents an entity and each filled in row is an instance of that entity. • Each entity has a primary key and primary key value must be unique so each row represent a unique occurrence (instance) of an entity. • Note – don’t enter data into your table yet.

11. Matching Rows • Now we have a data model with three entities (tables). • But the entities are independent of each other. • How do we know what row(s) in one entity match up with row(s) in the other entities. • We need to add some relating fields.

12. Relating Fields & Relationships • The relationship(s) between the entities must be defined to determine the relating field(s). • In our model we have • a relationship between the author and book entity entities, and • a relationship between the book and the publisher entities.

13. Publisher-Book relationship • The relationships between publisher and book are • A publisher publishes a book • A book is published by a publisher • Relationships are usually verbs • Relationships are symmetric, you should be able to define them in both directions.

14. The order of the relationship • The order of the relationship between two entities can be one of the following • one-to-one 1-1 • one-to-many 1-n • many-to-many n-n

15. Diagram the relationships • Start by adding the relationship to your ER-Diagram • Relationships are represent by diamonds

16. Our Relationships • In our example we have two relationships • Write • authors write books • books are written by authors • Publish • publishers publish books • books are published by publishers

17. ER-Diagram (3) Title Date Edition Author write Book ID Last_Name First_Name Middle_Name DOB DOD publish Publisher Name Address Phone

18. Determine the order (1) • Look at the relationship from each direction • For example • A (1) book can be published by one (1) publisher • A (1) publisher can publish many (n) books • Put the values on your ER-Diagram

19. ER-Diagram (4) Title Date Edition Author write Book ID Last_Name First_Name Middle_Name DOB DOD 1 n publish is published by publishes 1 1 Publisher Name Address Phone

20. Determine the order (2) • On each side of the relationship take the bigger value.

21. ER-Diagram (5) Title Date Edition Author write Book n ID Last_Name First_Name Middle_Name DOB DOD 1 n publish is published by publishes 1 1 1 Publisher Name Address Phone

22. Determine the order (3) • You have a one-to-many (1-n) relationship between publisher and book. • Now that you know the order of the relationship you need to represent the relationship in your relational data model (your Access tables)

23. ER-Diagram (6) Title Date Edition Author write Book n ID Last_Name First_Name Middle_Name DOB DOD publish 1 Publisher Name Address Phone

24. Representing a 1-n Relationship • To represent a 1-n relationship in a relational data model you • Take the primary key from the one side of the relationship and make it the foreign key in the many side of the relationship. • In our example the primary key of Publisher (Name) becomes a foreign key in Book.

25. ER-Diagram (7) Title Date Edition Name (foreign key) Author write Book n ID Last_Name First_Name Middle_Name DOB DOD publish 1 Publisher Name Address Phone

26. Representing a 1-1 Relationship • But what if the relationship had been 1-1? • You follow the same principle, take the primary key from one side of the relationship and make it the foreign key in the other side of the relationship. • It doesn’t matter which side you take the primary key from • You only go in one direction • In our example we do not have a 1-1 relationship.

27. Author – Book Relationship • Determine the order of the relationship between Author and Book • Look at the relationship from each direction • An (1) author can write many (n) books • A (1) book can be written by (n) autors • Put the values on your ER-Diagram

28. ER-Diagram (8) writes 1 n Title Date Edition Name (foreign key) Author write Book 1 n written by n ID Last_Name First_Name Middle_Name DOB DOD publish 1 Publisher Name Address Phone

29. Determine the order (4) • On each side of the relationship take the bigger value.

30. ER-Diagram (9) writes 1 n n n Title Date Edition Name (foreign key) Author write Book 1 n written by n ID Last_Name First_Name Middle_Name DOB DOD publish 1 Publisher Name Address Phone

31. Determine the order (5) • You have a many-to-many (n-n) relationship between author and book. • Now that you know the order of the relationship you need to represent the relationship in your relational data model (your Access tables)

32. ER-Diagram (10) n n Title Date Edition Name (foreign key) Author write Book n ID Last_Name First_Name Middle_Name DOB DOD publish 1 Publisher Name Address Phone

33. Representing a n-n Relationship • To represent a n-n relationship in a relational data model you need to create a table between the two entities to represent the relationship. To do this you • Take the primary key from one entity and make it a foreign key in the new table. • Then take the primary key from the other entity and make it a second foreign key in the new table.

34. Representing a n-n Relationship • In our example we create a new table named Write. • The primary key (ID) from Author becomes a foreign key in Write. • The primary key (Title) from Book becomes a foreign key in Write.

35. ER-Diagram (11) n n Title Date Edition Name (foreign key) Author write Book n ID (foreign key from Author) Title (foreign key from Book) ID Last_Name First_Name Middle_Name DOB DOD publish 1 Publisher Name Address Phone

36. Comments of the Associative Entity(the n-n relationship) • Notice that the relationship write now looks like an entity, and is shown with a dash outline. It is still technically a relationship. • The relationship entity has two foreign keys but no primary key. We would like every table to have a primary key. • One solution would be to add an “assigned primary key” to the new table.

37. ER-Diagram (12) n n Title Date Edition Name (foreign key) Author write Book n ID AID (foreign key from Author) Title (foreign key from Book) ID Last_Name First_Name Middle_Name DOB DOD publish 1 Publisher Name Address Phone

38. A possible concern • To create an instance of an author writing a book we would enter an ID and Title value on a line in the write table. • So if one book had three authors then we would add three rows to the write table, each row would have a different Author ID but they would have the same Title. • But book titles can be very long and it would be a waste of space to repeat a long title several times in the database.

39. Assigned Primary Keys • To address this concern we will add an “assigned primary key” to the Book entity and then let the Title become a non-key attribute. • The Book ID is then the primary key that becomes the foreign key in the write relationship.

40. ER-Diagram (13) n n ID Title Date Edition Name (foreign key) Author write Book n ID AID (foreign key from Author) BID (foreign key from Book) ID Last_Name First_Name Middle_Name DOB DOD publish 1 Publisher Name Address Phone

41. A picky technical point • When we first create the write table it only had foreign key attributes and was technically just a relationship. • Once we added a non-foreign key attribute (in this case, the assigned primary key) it became an “associative entity.”

42. ID attributes everywhere • Note that our ER-Diagram now has three different ID attributes and they each have different meanings. • Author.ID identifies an instance of the Author entity • Book.ID identifies an instance of the Book entity • Write.ID identifies and instance of the write associative entity.

43. Attribute names must be unique • Attribute names must be unique within a table but different tables can use the same attribute names for different things. • So in the write table we have three ID attributes but each has a different name • ID is the primary key for the write associative entity • AID is the foreign key the write associative entity that matches the ID attribute in the Author entity. • BID is the foreign key the write associative entity that matches the ID attribute in the Book entity.