Data Modeling What are you keeping track of? - PowerPoint Presentation

Data Modeling

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.

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.

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

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.

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

Book Dealer EntitiesThe book dealer data model has three entitiesAuthorBookPublisherNote that the name of each entity is a noun singular

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

Implementing you data model in AccessThe Access DBMS uses the Relational Data ModelIn a Relational Data Model each entity is represented as a tableeach attribute is represented as a field in a tableevery table has a primary key defined to ensure entity integrity

Instances of an EntitiesOnce 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.

Matching RowsNow 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.

Relating Fields & RelationshipsThe 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.

Publisher-Book relationshipThe relationships between publisher and book areA publisher publishes a bookA book is published by a publisher Relationships are usually verbsRelationships are symmetric, you should be able to define them in both directions.

The order of the relationshipThe order of the relationship between two entities can be one of the followingone-to-one 1-1one-to-many 1-nmany-to-many n-n

Diagram the relationshipsStart by adding the relationship to your ER-DiagramRelationships are represent by diamonds

Our RelationshipsIn our example we have two relationshipsWriteauthors write booksbooks are written by authorsPublishpublishers publish books books are published by publishers

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

Determine the order (1)Look at the relationship from each directionFor exampleA (1) book can be published by one (1) publisherA (1) publisher can publish many (n) booksPut the values on your ER-Diagram

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

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

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

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)

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

Representing a 1-n RelationshipTo represent a 1-n relationship in a relational data model youTake 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.

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

Representing a 1-1 RelationshipBut 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 fromYou only go in one direction In our example we do not have a 1-1 relationship.

Author – Book RelationshipDetermine the order of the relationship between Author and Book Look at the relationship from each directionAn (1) author can write many (n) booksA (1) book can be written by (n) autors Put the values on your ER-Diagram

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

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

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

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)

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

Representing a n-n RelationshipTo 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 youTake 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.

Representing a n-n RelationshipIn 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.

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

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.

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

A possible concernTo 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.

Assigned Primary KeysTo 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.

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

A picky technical pointWhen 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.”

ID attributes everywhereNote that our ER-Diagram now has three different ID attributes and they each have different meanings.Author.ID identifies an instance of the Author entityBook.ID identifies an instance of the Book entityWrite.ID identifies and instance of the write associative entity.

Attribute names must be uniqueAttribute 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 nameID is the primary key for the write associative entityAID 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.