1 / 88

IT 20303

IT 20303. Data Modeling for Logical Design Section 04. Relational Database Theory. Database Development Life Cycle Requirements Analysis/Design Produce Build/Test Production Maintenance. Relational Database Theory. What is a Data Model? A way to represent reality

said
Download Presentation

IT 20303

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. IT 20303 • Data Modeling for Logical Design • Section 04

  2. Relational Database Theory • Database Development Life Cycle • Requirements • Analysis/Design • Produce • Build/Test • Production Maintenance

  3. Relational Database Theory • What is a Data Model? • A way to represent reality • A schematic of data items and relationships • A “blueprint” for the database

  4. Relational Database Theory • Why do people use data models? • To better understand the world we are modeling • To “picture” the conceptual view of the database • To better communicate between people • To arrive at a common understanding of meaning of terms

  5. Relational Database Theory • When do we make a data model? • For a specific application, begin in the definition phase • For multiple applications, in a long-range planning activity • An information resource management activity • A data administration activity • When in doubt - model

  6. Relational Database Theory • Building a database is a learning process • At the beginning, we only know what things are important • We identify the major things and call them Entities • We accumulate Attributes and associate them with entities • We determine how to identify each occurrence of an entity – PK

  7. Relational Database Theory • Cont’d • We identify relationships between entities – FK • We accumulate rules for the entities and relationships – Constraints • We put it all together in a model and check it out with users • This is called top-down data modeling (This is good to do)

  8. Relational Database Theory • The Entity-Relationship Approach • Represents reality using well-defined graphics and rules • Basic building blocks are “things” (entities) and relationships Member M Adopts 1 Animal

  9. Relational Database Theory • Advantages • Theoretical foundation (Set Theory) • Good for communication • Build E-R Model, then translate to any type of RDBMS • Disadvantages • Different (yet another new thing to learn) • Must translate to the relational model

  10. Relational Database Theory • Entity-Relationship Model: Basic Concepts • Entity • Thing, Object, Concept of interest to the enterprise • Each occurrence can be uniquely identified

  11. Relational Database Theory • Entity-Relationship Model: Basic Concepts • Attribute • Property of an entity • Column

  12. Relational Database Theory • Entity-Relationship Model: Basic Concepts • Relationship • Association between two (or more) entities

  13. Relational Database Theory • Entity-Relationship Model: Basic Concepts • Entity Identifier • Attribute(s) whose value uniquely identifies an entity • Primary Key

  14. Relational Database Theory • What is an Entity? • Physical entity types • Person • Building • Machine • Book • Usually Singular

  15. Relational Database Theory • What is an Entity? • Conceptual entity types • Contract • Account • Order • Course

  16. Relational Database Theory • What is an Entity? • Event entity types • Transaction • Shipment • Reservation • Phone Call • Seminar Offering

  17. Relational Database Theory • Entity-Relationship Model: Diagrams • Example: • Soft Rectangle represents entities • Noun • Singular • Connecting Line represents relationships • Verb Member Adopts Animal

  18. Relational Database Theory • Relationships have Characteristics • A relationship has Cardinality (Degree) One-to-One One-to-Many Many-to-Many

  19. Relational Database Theory • Each entity’s participation is Mandatory or Optional • Cardinality & Optionality are based on business rules Mandatory Optional

  20. Relational Database Theory • One:One Relationship • One Member adopts one animal • One Animal is adopted by one member Member Adopts Animal

  21. Relational Database Theory • One:Many relationship • One member adopts one animal • One animal is adopted by many members Member Adopts Animal

  22. Relational Database Theory • Many:Many relationship • One member adopts many animals • One animal is adopted by many members Member Adopts Animal

  23. Relational Database Theory • Optionality: Participation in a Relationship Zoo Employee Cares for Animal

  24. Relational Database Theory • Mandatory • Every instance of the entity MUST participate in the relationship • Example: • Every animal is cared for by at least one employee

  25. Relational Database Theory • Optional • An instance of the entity CAN participate in the relationship • Example: • Some employees do not take care of animals

  26. End 09-14-05

  27. Relational Database Theory • Data Modeling Example: Hospital • Sample Hospital Data • Ward Name: Liston • Ward Type: Orthopedic • # of Beds: 6 • Senior Nurse: J. Bryan

  28. Relational Database Theory • Data Modeling Example: Hospital • Cont’d • Date of Birth: dd/mm/yy

  29. Relational Database Theory • Requirements determines boundaries of data modeling • Data model does contain data about • Ward • Patients • Data model does not contain data about • Color of walls in ward • Color of patients hair • Springiness of the beds in the ward

  30. Relational Database Theory • Entity-Relationship Modeling: Step-by-Step • Identify Entities PATIENT WARD

  31. Relational Database Theory • Entity-Relationship Modeling: Step-by-Step 2. Associate Attributes with entities PATIENT Patient number Name Date of birth WARD Ward name Type No beds Sr nurse

  32. Relational Database Theory • Entity-Relationship Modeling: Step-by-Step 3. Select or create a Unique Identifier for each entity WARD Ward name Type No beds Sr nurse PATIENT Patient number Name Date of birth

  33. Relational Database Theory • Entity-Relationship Modeling: Step-by-Step 4. Identify Relationship between entity PATIENT Patient number Name Date of birth WARD Ward name Type No beds Sr nurse Is assigned to Has assigned to it

  34. Relational Database Theory • Entity-Relationship Modeling: Step-by-Step 5. Determine Optionality Patient Ward

  35. Relational Database Theory • Entity-Relationship Modeling: Step-by-Step 6. Show Cardinality Patient Ward

  36. Relational Database Theory • Entity-Relationship Modeling: Step-by-Step 7. History – time affects cardinality Patient Ward

  37. Relational Database Theory • Entity-Relationship Modeling: Step-by-Step 8. Resolve Many:Many relationship STAY Patient number Ward name Date PATIENT Patient number Name Date of birth WARD Ward name Type No beds Sr nurse

  38. Relational Database Theory • Meaning of an Entity • What is a patient? • In-patient • Out-patient • Psychiatric patient • Baby born in hospital • Baby born outside hospital • Current patient • Former patient

  39. Relational Database Theory • Meaning of an Entity • Cont’d • Clarify exact meaning of an entity by: • Identifying several attributes • List examples • Documenting the meaning

  40. Relational Database Theory • Document Precise definition of each entity (Should always do)

  41. Relational Database Theory • Drawing an E-R Diagram • Identify the entities • Write (make up) a few attributes for each entity • Designate the unique identifier (PK) • Identify the relationship (FK goes on the many side) • Show cardinality and optionality for each relationship

  42. Relational Database Theory • E-R Modeling Exercise: • A Seminar Company • A seminar company offers more than 100 different courses • Each course has a unique course number and a title • The company schedules thousands of seminars annually • Each seminar is the presentation of one specific course • A seminar is either public or on-site

  43. Relational Database Theory • E-R Model: • Entities • Course • Course # (PK) • Course Title • Duration • Author

  44. Relational Database Theory • E-R Model: • Entities • Seminar • Seminar # (PK) • Date • Place • Type • Public • On-site • Course # (FK)

  45. Relational Database Theory COURSE • E-R Model: 1 Optional Mandatory M SEMINAR

  46. Relational Database Theory • E-R Modeling Exercise: • A Personnel Database • A company has four divisions • Each employee works for a department • Each department reports to one of the divisions

  47. Relational Database Theory • E-R Model: • Entities • Division • Division # • Division Name

  48. Relational Database Theory • E-R Model: • Entities • Employee • Employee # • Employee Name • Dept # (FK)

More Related