Transcript Slide 1
Chapter Extension 5 Database Design © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke Study Questions CE 5-2 Who will volunteer? How are database application systems developed? What are the components of the entity-relationship data model? How is a data model transformed into a database design? What is the user’s role? Who will volunteer? © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke Who Will Volunteer? Fundraising manager needs database to: – Track volunteers CE 5-3 How many years Effectiveness Personal information © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke Database Application Systems Database application consists of: – – – – CE 5-4 Forms Reports Queries Application programs © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke How Are Systems Developed? Figure CE5-1 CE 5-5 © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke Database Application System Development Process Developers interview users Develop requirements for new system – Requirements summarized in data model – – Logical representation of structure of data Contains description of data and relationships Users validate and approve model Design implemented in a database – CE 5-6 Analyze existing reports, forms, and user activities Database filled with user data © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke Database Must include all data necessary for users to perform jobs Contains only that amount of data, and no more Developers rely on users to: – – – CE 5-7 Tell them what to include Check data model Verify correctness and completeness © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke Entity-Relationship Data Model Techniques for creating data model – Entity-relationship data model – Unified Modeling Language UML Database content – CE 5-8 E-R data model Entities stored in database and relationship among those things © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke Entities Something that users want tracked May be: – – – CE 5-9 physical object Logical construct Transaction Always singular © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke Entities (continued) Have: – Attributes – Identifier CE 5-10 Describe characteristics Attribute whose value is associated with one and only one entity instance © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke E-R Diagrams Figure CE5-5 Figure CE5-6 CE 5-11 © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke E-R Diagrams (continued) Rectangles represent entities Relationships shown by lines Crow’s foot – – – CE 5-12 Forks at end of lines Indicate more than one relationship Read “many” Vertical line means at least one entity of that type Small oval means entity is optional © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke Relationships Entities have relationships with each other 1:1 relationship – 1:N relationship – – One-to-many Single entity to many entities N:M – CE 5-13 Single entity to single entity Many-to-many © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke Database Design Process of converting data model – – Transforms entities into tables Expresses relationships – CE 5-14 Defines foreign keys Shows data constraints © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke Normalization Process – – Data integrity problems – – – Different names for the same entity Produces incorrect and inconsistent information Resolve by eliminating duplicated data Normalized tables – – – CE 5-15 Converts table into two or more tables Changes from poorly structured to well-structured Eliminate data duplication Slower to process Every table has single topic © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke Cardinality Number of entities that can be involved in relationship – Maximum cardinality – Minimum cardinality CE 5-16 Maximum number involved Constraints on minimum requirements © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke Relational Database Construction Figure CE5-10 CE 5-17 © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke CE 5-18 © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke Relational Database Design CE 5-19 Designer creates table for every entity Entity identifier becomes primary key of table Attributes of entity become columns Tables normalized to single theme Represent relationships between tables Add foreign key to one or more tables © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke What Is the Users’ Role? CE 5-20 Final judges as to what data should be contained Determine how records are related to each other Need to review data model Must insure that model reflects an accurate view of business © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke Who Will Volunteer? Consultant creates data model – Based on interviews with users Data model reviewed and approved Database tables constructed – Primary and foreign keys selected Microsoft Access database created – – CE 5-21 Based on interviews Relationships indicated Forms and reports constructed © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke Active Review CE 5-22 Who will volunteer? How are database application systems developed? What are the components of the entity-relationship data model? How is a data model transformed into a database design? What is the user’s role? Who will volunteer? © 2008 Pearson Prentice Hall, Experiencing MIS, David Kroenke