1 / 27

Database Design 2: Creating an ER Model

Database Design 2: Creating an ER Model. CS 320. Review: Steps in Creating an Entity-Relationship Model. Identify entities Identify entity attributes and primary keys Specify relationships. ER Model Notation. Entity. Primary key: Field whose value is unique for each record

audrey-tate
Download Presentation

Database Design 2: Creating an ER Model

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 2: Creating an ER Model CS 320

  2. Review: Steps in Creating an Entity-Relationship Model Identify entities Identify entity attributes and primary keys Specify relationships

  3. ER Model Notation Entity Primary key: Field whose value is unique for each record & serves as an identifier Attributes Represent entities as rectangles List attributes within the rectangle

  4. Aside: Tools for Creating ER Models • Microsoft Visio • Other open source tools • Drawing tools • Powerpoint • Illustrator

  5. Data Model Naming Conventions • Entity names should be short, descriptive, compound words • UWEC_STUDENT, CandyProduct • Entity names will ultimately correspond to table names • Why compound words? • Avoids "reserved" words • Ensures every entity has a unique name

  6. Data Model Naming Conventions(continued) • Attribute names should be descriptive compound words that correspond to the entity name • Attribute names will ultimately correspond to field names • Relates the field back to the table • Every attribute name within the database should be unique

  7. Example Entity (Table) and Attribute (Field) Names CANDY_CUSTOMER CANDY_PURCHASE CANDY_CUST_TYPE CANDY_PRODUCT

  8. Primary Key Attributes • Attribute whose value is unique for every entity instance • Every entity MUST have a PK • Designate by: • Placing as first attribute in the entity • Underline • Label using "PK"

  9. Selecting Primary Keys • Must be values that are: • Unique for every possible record • Do not change • Best practice: numeric with no blank spaces or formatting characters • Often you need to create a surrogate key • ID value that serves only to identify the object in the database • Exception: objects with "natural" primary keys • SKU • ISBN • VIN

  10. Atomic and Composite Attributes • Atomic attribute: represents a single data value • 15, "Joline", 12/25/2009 • Composite attribute: can be decomposed into atomic attributes • "James B. Brown" • "5580 Pinewood Road, Eau Claire, WI 54701"

  11. Composite Attributes Student_First_Name Student_MI Student_Last_Name Student_Address_Line_1 Student_Address_Line_2 Student_City Student_State Student_Country Student_Postal_Code • Always decompose into atomic components for: • Sorting • Searching • Formatting

  12. Multi-Valued Attributes Attribute that might have multiple values for the same entity:

  13. Modeling Multi-Valued Attributes • If it has a definite maximum number, leave as a repeating attribute • If the upper limit is variable, make a new entity and create a relationship

  14. Derived Attributes • Values that can be derived from other attributes • Student_Age = 22 (DOB = 11/20/1989, current date is 11/13/2011) • Order_Total = $500 (Item 1 cost = $200, Item 2 cost = $300)

  15. Modeling Derived Attributes • In general, don't store derived attributes • Instead, create attributes to represent the underlying data values from which you can derive the value • Examples: • DOB => Age • Current_Price and Units_Sold of an item (for a sales order)

  16. Data Model Relationships • Specify the number of instances of one entity that can be associated with instances of a related entity • Types: • 1:M • 1:1 • M:M • “M” denotes some value greater than 1 whose upper bound is undetermined • This is called relationship cardinality

  17. Example 1:M Relationship

  18. Example 1:1 Relationship

  19. Example M:M Relationship

  20. Putting it all together:

  21. Summary: The Data Modeling Process Define entities Define attributes & primary keys Define relationships

  22. Test Yourself: Which of the following is the best choice for the primary key of a STUDENT entity? STUDENT_NAME STUDENT_EMAIL STUDENT_PHONE_NUMBER STUDENT_SOC_SECURITY_NUMBER None of the above are good choices

  23. Test Yourself: Which of the following is the best choice for the primary key of a STUDENT entity? STUDENT_NAME STUDENT_EMAIL STUDENT_PHONE_NUMBER STUDENT_SOC_SECURITY_NUMBER None of the above are good choices

  24. Test Yourself: In the video rental store database, VIDEO_ID is an example of a: Primary key Surrogate key Multi-valued attribute Derived attribute Both a and b

  25. Test Yourself: In the video rental store database, VIDEO_ID is an example of a(n): Primary key Surrogate key Multi-valued attribute Derived attribute Both a and b

  26. Your Turn: Al’s Body Shop keeps data on customers that includes name, address, phone number, car make, car model, car year, and VIN. When a customer brings a car in for service, the service manager completes a work order that contains the date of the service, the name of each service performed, a description of each service, and the charge for each service. Draw the ER Model for the following database application:

  27. Solution

More Related