1 / 68

Lecture 12: Database Design

Prof. Ray Larson & Prof. Marc Davis UC Berkeley SIMS Tuesday and Thursday 10:30 am - 12:00 pm Fall 2003 http://www.sims.berkeley.edu/academics/courses/is202/f03/. Lecture 12: Database Design. SIMS 202: Information Organization and Retrieval. Lecture Overview. Review

skule
Download Presentation

Lecture 12: 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. Prof. Ray Larson & Prof. Marc Davis UC Berkeley SIMS Tuesday and Thursday 10:30 am - 12:00 pm Fall 2003 http://www.sims.berkeley.edu/academics/courses/is202/f03/ Lecture 12: Database Design SIMS 202: Information Organization and Retrieval

  2. Lecture Overview • Review • Databases and Database Design • Database Life Cycle • ER Diagrams • Database Design • Normalization • Discussion Questions

  3. Lecture Overview • Review • Databases and Database Design • Database Life Cycle • ER Diagrams • Database Design • Normalization • Discussion Questions

  4. Models (1) Application 1 Application 2 Application 3 Application 4 External Model External Model External Model External Model Application 1 Conceptual requirements Application 2 Conceptual requirements Conceptual Model Logical Model Internal Model Application 3 Conceptual requirements Application 4 Conceptual requirements

  5. Database System Life Cycle Physical Creation 2 Conversion 3 Design 1 Growth, Change, & Maintenance 6 Integration 4 Operations 5

  6. Another View of the Life Cycle Integration 4 Operations 5 Design 1 Physical Creation 2 Conversion 3 Growth, Change 6

  7. Database Design Process Application 1 Application 2 Application 3 Application 4 External Model External Model External Model External Model Application 1 Conceptual requirements Application 2 Conceptual requirements Conceptual Model Logical Model Internal Model Application 3 Conceptual requirements Application 4 Conceptual requirements

  8. Entity • An Entity is an object in the real world (or even imaginary worlds) about which we want or need to maintain information • Persons (e.g.: customers in a business, employees, authors) • Things (e.g.: purchase orders, meetings, parts, companies) Employee

  9. Attributes Birthdate First Age Employee Middle Name SSN Last Projects • Attributes are the significant properties or characteristics of an entity that help identify it and provide the information needed to interact with it or use it (This is the Metadata for the entities)

  10. Relationships • Relationships are the associations between entities • They can involve one or more entities and belong to particular relationship types • One to One • One to Many • Many to Many

  11. Relationships Project Attends Student Class Supplies project parts Supplier Part

  12. Types of Relationships Assigned Assigned Assigned Employee Employee Employee Project Truck Project • Concerned only with cardinality of relationship 1 1 n 1 m n Chen ER notation

  13. More Complex Relationships Manager Assigned Employee Project Manages Employee Evaluation Employee Project 1/1/1 1/n/n n/n/1 SSN Date Project 1 4(2-10) Manages 1 Is Managed By n

  14. Weak Entities Part# Invoice # Invoice# Quantity Contains Order Order-line Rep# • Owe existence entirely to another entity

  15. Supertype and Subtype Entities Manages Employee Is one of Sales-rep Clerk Sold Other Invoice

  16. Many to Many Relationships SSN Proj# Proj# Hours Is Assigned Project Assignment Project Assigned SSN Employee

  17. Lecture Overview • Review • Databases and Database Design • Database Life Cycle • ER Diagrams • Database Design • Normalization • Discussion Questions

  18. Database Design Process Application 1 Application 2 Application 3 Application 4 External Model External Model External Model External Model Application 1 Conceptual requirements Application 2 Conceptual requirements Conceptual Model Logical Model Internal Model Application 3 Conceptual requirements Application 4 Conceptual requirements

  19. Database Design Process Application 1 Application 2 Application 3 Application 4 External Model External Model External Model External Model Application 1 Conceptual requirements Application 2 Conceptual requirements Conceptual Model Logical Model Internal Model Application 3 Conceptual requirements Application 4 Conceptual requirements

  20. Requirements Analysis • Conceptual Requirements • Systems Analysis Process • Examine all of the information sources used in existing applications • Identify the characteristics of each data element • Numeric • Text • Date/time • Etc. • Examine the tasks carried out using the information • Examine results or reports created using the information

  21. Database Design Process Application 1 Application 2 Application 3 Application 4 External Model External Model External Model External Model Application 1 Conceptual requirements Application 2 Conceptual requirements Conceptual Model Logical Model Internal Model Application 3 Conceptual requirements Application 4 Conceptual requirements

  22. Conceptual Design • Conceptual Model • Merge the collective needs of all applications • Determine what Entities are being used • Some object about which information is to maintained • What are the Attributes of those entities? • Properties or characteristics of the entity • What attributes uniquely identify the entity • What are the Relationshipsbetween entities • How the entities interact with each other?

  23. Developing a Conceptual Model • Overall view of the database that integrates all the needed information discovered during the requirements analysis • Elements of the Conceptual Model are represented by diagrams, Entity-Relationship or ER Diagrams, that show the meanings and relationships of those elements independent of any particular database systems or implementation details • Can also be represented using other modeling tools (such as UML)

  24. Database Design Process Application 1 Application 2 Application 3 Application 4 External Model External Model External Model External Model Application 1 Conceptual requirements Application 2 Conceptual requirements Conceptual Model Logical Model Internal Model Application 3 Conceptual requirements Application 4 Conceptual requirements

  25. Logical Design • Logical Model • How is each entity and relationship represented in the Data Model of the DBMS • Hierarchic? • Network? • Relational? • Object-Oriented?

  26. Database Design Process Application 1 Application 2 Application 3 Application 4 External Model External Model External Model External Model Application 1 Conceptual requirements Application 2 Conceptual requirements Conceptual Model Logical Model Internal Model Application 3 Conceptual requirements Application 4 Conceptual requirements

  27. Physical Design • Internal Model • Choices of index file structure • Choices of data storage formats • Choices of disk layout

  28. Database Design Process Application 1 Application 2 Application 3 Application 4 External Model External Model External Model External Model Application 1 Conceptual requirements Application 2 Conceptual requirements Conceptual Model Logical Model Internal Model Application 3 Conceptual requirements Application 4 Conceptual requirements

  29. Database Application Design • External Model • User views of the integrated database • Making the old (or updated) applications work with the new database design

  30. Terms and Concepts • Key • An attribute or set of attributes used to identify or locate records in a file • Primary Key • An attribute or set of attributes that uniquely identifies each record in a file • Candidate Key • An attribute or set of attributes that might be used as a primary key

  31. Lecture Overview • Review • Databases and Database Design • Database Life Cycle • ER Diagrams • Database Design • Normalization • Discussion Questions

  32. Normalization • Normalization theory is based on the observation that relations with certain properties are more effective in inserting, updating and deleting data than other sets of relations containing the same data • Normalization is a multi-step process beginning with an “unnormalized” relation

  33. Normal Forms • First Normal Form (1NF) • Second Normal Form (2NF) • Third Normal Form (3NF) • Boyce-Codd Normal Form (BCNF) • Fourth Normal Form (4NF) • Fifth Normal Form (5NF)

  34. Normalization Unnormalized Relations First normal form Functional dependencyof nonkey attributes on the primary key - Atomic values only Second normal form No transitive dependency between nonkey attributes Third normal form Boyce- Codd and Higher Full Functional dependencyof nonkey attributes on the primary key All determinants are candidate keys - Single multivalued dependency

  35. Unnormalized Relations • First step in normalization is to convert the data into a two-dimensional table • In unnormalized relations data can repeat within a column • (The following is a highly contrived example that actually bears only a slight resemblance to the current implementation of the Phone/Photo project database)

  36. Unnormalized Relations

  37. First Normal Form • To move to First Normal Form a relation must contain only atomic values at each row and column • No repeating groups • A column or set of columns is called a Candidate Key when its values can uniquely identify the row in the relation

  38. First Normal Form

  39. 1NF Storage Anomalies • Insertion: A new person has not yet taken a picture -- hence no Picture # -- Since Picture # is part of the key we can’t insert • Insertion: If People is are known and likely to be photographed, but haven’t been yet -- there is be no way to include that person in the database • Update: If a Person changes status (e.g. Mary Jones becomes a Student) we have to change multiple rows in the database • Deletion (type 1): Deleting a Person record may also delete all info about People in the pictures • Deletion (type 2): When there are functional dependencies (like Object and Object_features) changing one item eliminates other information

  40. Second Normal Form • A relation is said to be in Second Normal Form when every nonkey attribute is fully functionally dependent on the primary key • That is, every nonkey attribute needs the full primary key for unique identification

  41. Second Normal Form Person Table

  42. Second Normal Form People Table

  43. Second Normal Form Picture Table

  44. 1NF Storage Anomalies Removed • Insertion: Can now enter new Persons who haven’t yet taken pictures • Insertion: Can now enter People who haven’t been photographed • Deletion (type 1): If Charles Brown withdraws his photos the corresponding tuples from Person and Picture tables can be deleted without losing information on David Rosen • Update: If John White takes a third picture, and has changed status (e.g., graduate), we only need to change the Person table in one place

  45. 2NF Storage Anomalies • Insertion: Cannot enter the fact that a particular object has a particular feature unless it is associated with a particular picture • Deletion: If John White describes some other object that Beth Little has while shopping, we lose the fact that the bookbag is blue • Update: If the features of an object change change we have to update multiple occurrences of object features

  46. Third Normal Form • A relation is said to be in Third Normal Form if there are no transitive functional dependencies between nonkey attributes • When one nonkey attribute can be determined with one or more nonkey attributes there is said to be a transitive functional dependency • The Obect_Feature column in the Picture table is determined by the Object • Object_Feature is transitively functionally dependent on Object so Picture is not 3NF

  47. Third Normal Form Picture Table

  48. Third Normal Form Object Table

  49. 2NF Storage Anomalies Removed • Insertion: We can now enter the fact that an object has a particular feature • Deletion: If John White describes some other object that Beth Little has while shopping, we don’t lose the fact that the bookbag is blue • Update: The features for each object appear only once

  50. Boyce-Codd Normal Form • Most 3NF relations are also BCNF relations • A 3NF relation is NOT in BCNF if: • Candidate keys in the relation are composite keys (they are not single attributes) • There is more than one candidate key in the relation, and • The keys are not disjoint, that is, some attributes in the keys are common

More Related