Managing Data Resources - NDSU

Managing Data Resources - NDSU

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. Recap Entity - 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 EntityRelationship (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 ID Last_Name First_Name Middle_Name

DOB DOD Publisher Name Address Phone Title Date Edition 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 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. 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 Instances of an Entities Once youve 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 dont enter data into your table yet. 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. 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. 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. 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 Diagram the relationships Start by adding the relationship to your ERDiagram Relationships are represent by diamonds 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

ER-Diagram (3) Author ID Last_Name First_Name Middle_Name DOB DOD write Book publish

Publisher Name Address Phone Title Date Edition 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 ER-Diagram (4) Author ID Last_Name First_Name Middle_Name DOB DOD write Book

1 is published by publish Title Date Edition n publishes 1 1 Publisher Name

Address Phone Determine the order (2) On each side of the relationship take the bigger value. ER-Diagram (5) Author ID Last_Name First_Name Middle_Name DOB DOD

write Title Date Edition Book n 1 is published by publish 1 1

Publisher Name Address Phone n publishes 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 ID Last_Name First_Name Middle_Name DOB DOD write Book n

publish 1 Publisher Name Address Phone Title Date Edition 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. ER-Diagram (7) Author ID Last_Name First_Name Middle_Name DOB DOD

write Book n publish 1 Publisher Name Address Phone Title Date Edition Name (foreign key)

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 doesnt 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. 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 ER-Diagram (8) 1 Author ID Last_Name First_Name Middle_Name DOB DOD

writes n write n written by 1 Book n publish 1 Publisher Name Address

Phone Title Date Edition Name (foreign key) Determine the order (4) On each side of the relationship take the bigger value. ER-Diagram (9) 1 Author ID

Last_Name First_Name Middle_Name DOB DOD writes n n n write n written by 1

Book n publish 1 Publisher Name Address Phone Title Date Edition Name (foreign key) 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 ID Last_Name First_Name Middle_Name DOB DOD

n write n Book n publish 1 Publisher Name Address Phone

Title Date Edition Name (foreign key) 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.

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. ER-Diagram (11) Author ID Last_Name First_Name Middle_Name DOB

DOD n write n Book n ID (foreign key from Author) Title (foreign key from Book) publish

1 Publisher Name Address Phone Title Date Edition Name (foreign key) 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 ID Last_Name First_Name Middle_Name DOB DOD

n write n Book n ID AID (foreign key from Author) Title (foreign key from Book) publish

1 Publisher Name Address Phone Title Date Edition Name (foreign key) 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. 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. ER-Diagram (13)

Author ID Last_Name First_Name Middle_Name DOB DOD n write n Book

n ID AID (foreign key from Author) BID (foreign key from Book) publish 1 Publisher Name Address Phone ID Title

Date Edition Name (foreign key) 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. 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. 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.

Recently Viewed Presentations

  • Native Sports Team Names - Mr. Soulis

    Native Sports Team Names - Mr. Soulis

    The crisis cost well over $200 million and was the first violent conflict between Natives and the government in the late 20th century . In 1997, the Department of Indian Affairs quietly purchased the disputed land for $5.2 million and...
  • Student Affairs Theories: A Primer

    Student Affairs Theories: A Primer

    Torres's Model of Hispanic Identity Development (2003) Ethnic identity is the maintenance of a culture. The model recognizes the influences of where one grew up, generational status, and self-perception of societal status on identity development of Latino college students.
  • Synonyms and Antonyms - Study Island

    Synonyms and Antonyms - Study Island

    SYNONYMS AND ANTONYMS WHAT ARE SYNONYMS? Synonyms are words that have similar meanings. You can use a synonym to replace a word in a sentence, and the sentence will still have the same meaning. Think fast! Synonyms have similar meanings....
  • INTRODUCTION TO REGIONAL ANATOMY Kaan Ycel M.D., Ph.D.

    INTRODUCTION TO REGIONAL ANATOMY Kaan Ycel M.D., Ph.D.

    topographical anatomy. organization of the human body as major parts or segments. Head. Neck. Trunk. ... + posterior basal regions of the skull. posterior aspect of the body . provides the musculoskeletal axis of support for the trunk. BACK. spinal...
  • Supporting South Africa and Rest of Africa NEDBANK

    Supporting South Africa and Rest of Africa NEDBANK

    A big thank you to Nedbank's partner Bidvest, for donating over a cube of manure towards our sustainable food garden. We would also like to thank Darren Lotter for providing financial aid and his green expertise in assembling our veggie...
  • Unit 1 Review - shshistory.com

    Unit 1 Review - shshistory.com

    Unit 1 Review By Danny Grewal Definitions Imperialism - is the domination of one group over another Open door policy - is a policy that was created so that western countries can trade with china.
  • Use of Weight of Evidence and Mode of Action Approaches in ...

    Use of Weight of Evidence and Mode of Action Approaches in ...

    This definition is for cancer MOA only. New definition focusses on distinction between geno toxic and mutagenic. Focus on mutation as a permanent change in DNA, one that is heritable. Germ cell mutagenicity Guidelines mostly lists test that are relevant....
  • Ancient Rome - Winston-Salem/Forsyth County Schools

    Ancient Rome - Winston-Salem/Forsyth County Schools

    The Apennines are another mountain range that run all along Italy. 2. Greece vs. Rome . Both Italy and Greece have mountain ranges. ... Complete the Ancient Rome Map. 6. How did Rome begin? There are two legends: Romulus and...