1 / 56

Database Design

Database Design. HIMA 4160 Fall 2009. House Keeping. Assignment 4 due tonight Assignment 5 due next Monday. . Agenda. Database Concepts Entity Relational Diagram (ERD). Database == Data + Base. Transaction. Storage. Database: Definition. A collection of data that: is organized

leopold
Download Presentation

Database Design

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. Database Design HIMA 4160 Fall 2009

  2. House Keeping • Assignment 4 due tonight • Assignment 5 due next Monday.

  3. Agenda • Database Concepts • Entity Relational Diagram (ERD)

  4. Database == Data + Base Transaction Storage

  5. Database: Definition • A collection of data that: • is organized • usually computer-based • represents repetitive information implicitly • primarily for transaction • supports retrieval

  6. Is paper medical record a database? • Yes • No • No opinion

  7. Is Excel a database system? • Yes • No • No Opinion

  8. The shortcomings of a large table

  9. A more relational database

  10. Database Management System (DBMS) • A computer program for the purposes of managing databases. • Is the basis for many applications (e.g., Electronic Health Records, Personal Health Records). • A DBMS can host many databases • A database can be implemented in many different DBMS systems.

  11. Data Modeling • Before you implement a database, you need to design the database • Data modeling is a systematic way to help you design the database. • Map/simplify the real world to database schema/structure.

  12. Data Modeling

  13. Why Data Modeling? • Power and flexibility of database depend on data model • Database is the realization of data model • Evaluation of commercial products • Communicating with vendors and IT staff • Building your own databases

  14. Database Analysis • Determine User Requirements • Develop Data Models • “A conceptual data model is one that represents data from the viewpoint of the user, independent of any technology that will be used to implement the model.” • A database has two parts • Schema • Data

  15. Stages of Data Modeling • Conceptual Model • E-R diagram • Logical Model • Relational Model • Physical Model • Database management

  16. Database Modeling Conceptual Logical Physical Real World E-R Diagram Relational Model DBMS

  17. Introduction • Entity Relationship Modelling (ERM) • A technique used to analyze & model the data in organizations • Using diagram to represent entities and relationship of components in a system. • Supported by modern DBMS system. • De facto tool for database design

  18. The Importance of Data Modeling • Characteristics captured during data modeling • crucial in design of databases, programs, other items • facts and rules essential in assuring data integrity • Data are the most complex aspects of the modern organization • Data are determined by the business rules. • Need to avoid scope creeps • Data tend to be more stable than the business processes that use the data

  19. Definitions • Entity • an aggregation of a number of data elements • each data element is an attribute of the entity • Relationship • an association between two or more entities that is of particular interest

  20. Background • Introduced by Peter Chen in ’75 • “The Entity-Relationship Model – Toward a Unified View of Data”, ACM Transactions on Database Systems, Vol. 1, No. 1, March 1976, Pages 9 - 36 • now widely used in commercial database.

  21. Why use ER Diagrams ? • provides a global quick reference to an organization’s data structures. • can be used individually to design an Information System’s (IS) data structure. • offers a basis of consequential database design and development.

  22. ERD Development Process • Identify the entities • Determine the attributes for each entity • Select the identifier for each entity • Establish the relationships between the entities • Draw an entity model • Test the relationships and the keys

  23. Entities • Person, place, object, event, or concept … • in the user environment • about which the organization wishes to maintain data • Entity type • collection of entities that share common properties or characteristics • CAPITAL LETTERS • Entity instance • single occurrence of an entity type ENTITY

  24. A Simple Example • STUDENTs attend COURSEs that consist of many SUBJECTs. • A single SUBJECT (i.e. English) can be studied in many different COURSEs. • Each STUDENT may only attend one COURSE.

  25. Identify the entities Any physical object, event, or abstract concept that we can record facts about. Rule of thumbs: • Look for nouns • Not all nouns are entities • Some of them are attributes

  26. Identify Entities A football team has coaches, players, trainers and other assistants. Coach’s roles include head coach, defense and offence coordinators, quarterback coaches, etc. Football players can play different positions like quarterback, running backs, receivers etc. Rule of thumbs: • Look for nouns • Not all nouns are entities • Some of them are attributes

  27. Identify Entities (my answers) • Coach • Player • Trainer • Assistants • Roles • Positions

  28. Attributes • Property or characteristic of an entity type that is of interest to the organization • initial capital letter followed by lowercase letters • underscore instead of space (first_name) • Simple or Composite? • composite has component parts • will users need to refer to those individual components? • Single-valued or Multivalued? • Stored or Derived?  Name Number PLAYER

  29. Determine the Attributes • Every Entity has attributes. • Attributes are characteristics that allow us to classify/describe an entity • e.g., entity STUDENT has the attributes: • student number • name • date of birth • course number

  30. Identifier [Attribute] • “An identifier is an attribute (or combination of attributes) that uniquely identifies individual instances of an entity type.” • atomic or composite • Criteria • will not change its value • never null • unique SS# Name Number PLAYER

  31. Key Attributes • Certain attributes identify particular facts within an entity, these are known as KEY attributes. • The different types of KEY attribute are: • Primary Key • Composite Primary Key • Foreign Key

  32. Key Definitions • Primary Key: • One attribute whose value can uniquely identify a complete record (one row of data) within an entity. • Composite Primary Key • A primary key that consists of two or more attribute within an entity. • Foreign Key • A copy of a primary key that exists in another entity for the purpose of forming a relationship between the entities involved.

  33. COURSE ER Diagram Notations • Every entity diagram consists of the following components: Entity (labelled box) Relationship line

  34. Degrees of a Relationship One-to-one (1:1) 1 1 • WOMAN • MAN One-to-many (1:n) 1 M • ORDER • CUSTOMER Many-to-many (n:m) M M • SUBJECT • COURSE NOTE: Every many to many relationship consists of two one to many relationships working in opposite directions

  35. Notation of Cordiality (Degree of relationship) One-to-one (1:1) • WOMAN • MAN One-to-many (1:n) • ORDER • CUSTOMER Many-to-many (n:m) • Subject • Course

  36. CAR Notation for Optional Participation 1 M PERSON A person can own no or several cars. A car doesn’t have to be owned by a person, but if it is, it is owned by only one person. 1 optional relationship

  37. A Sample Four Entities ER Diagram SUBJECTS STUDENTS COURSES PROFESSORS A Student Record Entity Diagram

  38. Exercise • One patient can see several doctors. One doctors can see many patients. A patient may not see a doctor at all

  39. Exercise • A nurse is assigned to manage an exam room.

  40. Exercise • A patient must have a insurance and a insurance company can have many patients as their customer.

  41. Exercise (complex one) • Draw an E-R diagram to represent the data schema of a physician office. The entities included are physicians, patients, insurance companies, lab, medications etc.

  42. ER Diagram Summary • Identify the entities • Determine the attributes for each entity • Select the primary key for each entity • Establish the relationships between the entities • Draw an entity model

  43. Logic Design and Physical Design

  44. Conceptual Design BED PATIENTS

  45. Conceptual Design DOCTORS PATIENTS

  46. DOCTORS PATIENTS

  47. Conceptual Design DOCTORS PATIENTS

  48. Conceptual Design • DOCTORS • Doctor_ID • Doctor_lname • Doctor_fname • Doctor_mi • Doctor_specialty • Doctor_address • Doctor_phone • Doctor_email • PATIENTS • Patient_ID • Patient_lname • Patient_fname • Patient_mi • Patient_address • Patient_phone • Patient_email

  49. Logical Model • Entities  tables • Relationship  keys (primary and foreign) • Many to many  two one to many relationship with an associate entity in the middle • Normalization

More Related