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

  • CDISC Controlled Terminology Initiative: An Overview 1 June

    CDISC Controlled Terminology Initiative: An Overview 1 June

    Browser caDSR CDE Browser Production Representations Stage I: Standard Definition/Team Initiation Stage III: Education & Support Stage IV: Updates & Maintenance Stage II (a-e): Standards Development/Review/V 1.0 Release Initial terminology, code list and definition development (Excel) Analyze, compare and harmonize...
  • Risk Neutral Equilibria of Noncooperative Games

    Risk Neutral Equilibria of Noncooperative Games

    The set of correlated equilibria is a convex polytope, like sets of probabilities that arise elsewhere in decision theory (incomplete preferences, incomplete markets….) Extreme points of the polytope have rational coordinates if the game payoffs do. Existence proof only depends...
  • Presentation main title can go over three or four lines if ...

    Presentation main title can go over three or four lines if ...

    Cambridge IGCSE is the world's most popular qualification for 14 to 16 year olds. It celebrated it's 25th anniversary on 2010. The curriculum offers a variety of routes for learners with a wide range of abilities, including those whose first...
  • Childrens Hospice International 13th Annual ChiPACC Conference With

    Childrens Hospice International 13th Annual ChiPACC Conference With

    This is a step beyond traditional hospice rules, and the right thing to do for these most vulnerable children and their families" The Honorable Mike Leavitt, Secretary. U.S. Dept. of Health & Human Services* July, 2005 (*Minister of Health) ChiPACC....
  • A Grade 8 Parents Information Session on Pathway

    A Grade 8 Parents Information Session on Pathway

    "A student who successfully completes all these math courses will be fully prepared to enrol in Advanced Functions (MHF 4U0) in his/her 4. th. year at SLSS." "MFM 1H0 is a two week transition course in math. This transition does...
  • Our greatest weakness lies in giving up. The

    Our greatest weakness lies in giving up. The

    "The passion for stretching yourself and sticking to it , even (or especially) when it's not going well is the hallmark of the growth mindset.This is the mindset that allows people to thrive during some of the most challenging times...
  • Human Resources Administration Medical Assistance Program (MAP) Office

    Human Resources Administration Medical Assistance Program (MAP) Office

    MAGI/NON-MAGI. NY State of Health (MAGI Rules) Pregnant women (unless presumptive eligibility) Parents/caretakers/relatives (any age) Children under 19 years old. Children 19-20 full time students. Children 19 or 20 living with parents. Adults 19-64 .
  • Clinical Trial Costs in Emerging Geographies: China R.

    Clinical Trial Costs in Emerging Geographies: China R.

    Eli Lilly / Nicholas Piramal. $100M. risk sharing partnership. AstraZeneca sets up process R&D lab in Bangalore. Roche plans to expand R&D center in Shanghai. GSK plans to establish a fully integrated R&D center . GSK / Ranbaxy . $100...