1 / 86

Concepts of Database Management Seventh Edition

Concepts of Database Management Seventh Edition. Chapter 6 Database Design 2: Design Method. Objectives. Discuss the general process and goals of database design Define user views and explain their function Define Database Design Language (DBDL) and use it to document database designs

jamiec
Download Presentation

Concepts of Database Management Seventh Edition

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. Concepts of Database ManagementSeventh Edition Chapter 6 Database Design 2: Design Method

  2. Objectives • Discuss the general process and goals of database design • Define user views and explain their function • Define Database Design Language (DBDL) and use it to document database designs • Create an entity-relationship (E-R) diagram to visually represent a database design • Present a method for database design at the information level and view examples illustrating this method

  3. Objectives (continued) • Explain the physical-level design process • Discuss top-down and bottom-up approaches to database design and examine the advantages and disadvantages of both methods • Use a survey form to obtain information from users prior to beginning the database design process • Review existing documents to obtain information prior to beginning the database design

  4. Objectives (continued) • Discuss special issues related to implementing one-to-one relationships and many-to-many relationships involving more than two entities • Discuss entity subtypes and their relationships to nulls • Learn how to avoid potential problems when merging third normal form relations • Examine the entity-relationship model for representing and designing databases

  5. Introduction • Two-step process for database design • Information-level or conceptual level design: completed independently of any particular DBMS • Physical-level design: information-level design adapted for the specific DBMS that will be used • Must consider characteristics of the particular DBMS

  6. User Views • User view:set of requirements necessary to support operations of a particular database user • Cumulative design:supports all user views encountered during design process

  7. Information-Level Design Method • For each user view: • Represent the user view as a collection of tables. Use the EAR method to do this. • Entity – identify the entities (e.g. Students, Courses) • Attributes – identify the attributes for each entity • Relationship – create relationship among related tables • Normalize these tables • Identify all keys in these tables • Merge the result of Steps 1 through 3 into the cumulative design Mnemonics : RaNK Me

  8. Represent the User View As a Collection of Tables • Step 1: Determine the entities involved and create a separate table for each type of entity • Step 2: Determine the primary key for each table • Step 3: Determine the properties for each entity • Step 4: Determine relationships between the entities • One-to-many • Many-to-many • One-to-one

  9. Represent the User View As a Collection of Tables (continued) • One-to-many relationship: include primary key of the “one” table as a foreign key in the “many” table • Many-to-many relationship: create a new table whose primary key is the combination of the primary keys of the original tables • One-to-one relationship: simplest implementation is to treat it as a one-to-many relationship

  10. Normalize the Tables • Normalize each table • Target is third normal form • Careful planning in early phases of the process usually rules out need to consider fourth normal form

  11. Identify All Keys • For each table, identify: • Primary key • Alternate keys • Secondary keys • Foreign keys • Alternate key: column(s) that could have been chosen as a primary key but was not • Secondary keys:columns of interest strictly for retrieval purposes

  12. Identify All Keys (continued) • Foreign key: column(s) in one table that is required to match value of the primary key for some row in another table or is required to be null • Used to create relationships between tables • Used to enforce certain types of integrity constraints

  13. Types of Primary Keys • Natural key:consists of a column that uniquely identifies an entity • Also called a logical key or an intelligent key • Artificial key: column created for an entity to serve solely as the primary key and that is visible to users • Surrogate key: system-generated; usually hidden from users • Also called a synthetic key

  14. Database Design Language (DBDL) • Table name followed by columns in parentheses • Primary key column(s) underlined • AK identifies alternate keys • SK identifies secondary keys • FK identifies foreign keys • Foreign keys followed by an arrow pointing to the table identified by the foreign key

  15. Database Design Language (DBDL) (continued) FIGURE 6-1: DBDL for the Employee table

  16. Entity-Relationship (E-R) Diagrams • Visually represents database structure or the conceptual model of the Database

  17. Building Blocks of ERD

  18. ERD Popular Notation • Chen Notation • Crow’s Foot Notation

  19. Chen Notation - Symbol Rectangle represents an Entity Diamond represents a Relationship 1 M Lines with labels represents Cardinality

  20. Entity (Chen Notation) • is a real-world object distinguishable or unique from other objects. • An entity can be a concrete or physical object like employee, student, faculty, customer etc. Or it could also be conceptual or abstract like transaction, order, course, subjects etc. • It can be thought of as a noun like student, employee etc. • It is normally represented by a rectangle shape.

  21. Database Background Person (ex. Teacher, Student, Physician) • Remember in Chapter 1 Entity could be a : Place (ex. School, Hotel, Store ) Object (ex. Mouse, Books, Bulding ) Event (ex. Enroll, Withdraw, Order ) Idea or Concept (ex. Courses, Account, Delivery )

  22. Entity - Example • For example in our Premiere Database the different Entities are the following: Customer Sales Rep Order Parts

  23. Relationship • is a way of relating one entity to another. Entities can therefore participate in a relationship. • it is commonly thought as a verb connecting the entities or nouns. • It is normally represented by a diamond shape.

  24. Relationship - Example • For example in our Premiere Database again we have this relationships among entities: represents Customer Sales Rep has Could be read as : A Sales Rep Represents a Customer. And a Customer has an Order. Order

  25. Cardinality • Cardinality: number of items that must be included in a relationship • An entity in a relationship with minimum cardinality of zero plays an optional role in the relationship • An entity with a minimum cardinality of one plays a mandatory role in the relationship

  26. Cardinality - Symbols 1 M One-is-to-many Relationship M N Many-to-many Relationship

  27. Cardinality Symbols - Example represents Sales Rep 1 M Customer Could be read as : A Sales Rep could represent 1 or Many Customers.

  28. Cardinality Symbols – Example (Cont’d) has Order M N Parts Could be read as : An Order could have many Parts (e.g. Products Ordered) and a Part could have many Orders.

  29. Degree of Relationship • There are three Degree of Relationships in ERD notation, namely: • Unary • Binary • Ternary

  30. Degree of Relationship (Cont’d) Unary Binary Ternary

  31. Degree of Relationship (Cont’d) Manages Unary Employee makes Customer Orders Binary Vendor Warehouse Ternary supplies Part

  32. Attribute • Refers to the characteristic or basic fact or field of an Entity or Relationship. • For example a Student entity could have the following attributes ID Number, Last Name, First Name, Address, Birth Date etc. • A relationship could also have an attribute for example an Entity name Student enrolls (relationship) to a Course/Program. Now, when you enroll you enroll on a certain date so you will have an attribute of Enrollment Date under Enroll relationship. • It is normally represented by an oval.

  33. Attribute - Example Lastname Firstname RepNum Street City Sales Rep State Take note that a Primary Key is underlined. Rate Zip Commission

  34. Attribute – More Example CustomerName CustomerNum Lastname Firstname RepNum Street Street represents 1 M City City Sales Rep Customer State State Rate CreditLimit Zip Zip Commission Balance

  35. Crow’s Foot notation - Symbol Entity name Attribute 1 Attribute 2 Attribute 3 Attribute 4

  36. Crow’s Foot notation - Example Entity Student StudentID Firstname Lastname Gender Program Attributes

  37. Crow’s Foot notation - Keys Student PK – Primary Key StudentID (PK) Firstname (SK) Lastname (SK) Gender ProgramID (FK) SK – Secondary Key FK – Foreign Key

  38. Crow’s Foot Cardinality - Symbols One and only one included in the relationship Zero or many could be included in the relationship. This is optional mode. One or many could be included in the relationship. This is mandatory mode.

  39. Crow’s Foot notation – with Cardinality Customer Rep Customernum(PK) Customername Street City State Zip Balance CreditLimit Repnum (FK) Repnum (PK) Firstname Lastname CommissionRate

  40. ERD for Department/Employee example Customer Department EmployeeNum(PK) Lastname (SK) Firstname (SK) Street City State Zip WageRate SocSecNum (AK) DepartmentNum (FK) DepartmentNum(PK) Name Location

  41. Graded Seatwork • Make an ERD using both Chen and Crow’s Foot notation for Question No. 6 letter c onPage 224 of your Textbook – • An employee can work on many projects and a project can be worked on many employees

  42. Merge the Result into the Design • Combine tables that have the same primary key to form a new table • New table: • Primary key is same as the primary key in the tables combined • Contains all the columns from the tables combined • If duplicate columns, remove all but one copy of the column • Make sure new design is in third normal form

  43. Merge the Result into the Design (continued) FIGURE 6-3: Information-level design method

  44. Database Design Examples • Develop an information-level design • Company stores information about sales reps, customers, parts, and orders • User view requirements • Constraints FIGURE 6-4: Cumulative design after first user view

  45. Database Design Examples (continued) FIGURE 6-6: Cumulative design after third user view

  46. Database Design Examples (continued) FIGURE 6-8: Final information-level design

  47. Database Design Examples (continued) • Henry Books database: information about branches, publishers, authors, and books • User view requirements FIGURE 6-9: DBDL for Book database after first user view

  48. Database Design Examples (continued) FIGURE 6-10: DBDL for Book database after second user view

  49. Database Design Examples (continued) FIGURE 6-13: Cumulative design after fifth user view

  50. Physical-Level Design • Undertaken after information-level design completion • Most DBMSs support primary, candidate, secondary, and foreign keys • To enforce restrictions, DB programmers must include logic in their programs

More Related