1 / 16

More Data Modeling

More Data Modeling. CS 146. Relationship Lower Bounds. Lower bound: minimum number of related instances in a relationship Value is typically 0 or 1. Store Store_ID Store_Address. “A store rents 0 to many videos”. Rents. Video Video_ID Video_Title Video_Format.

lotte
Download Presentation

More Data Modeling

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. More Data Modeling CS 146

  2. Relationship Lower Bounds • Lower bound: minimum number of related instances in a relationship • Value is typically 0 or 1 Store Store_ID Store_Address “A store rents 0 to many videos” Rents Video Video_ID Video_Title Video_Format “A video can be rented by 1 and only 1 store”

  3. More Lower Bound Examples Store Store_ID Store_Address Spouse Spouse_ID Spouse_Name Rents Has Video Video_ID Video_Title Video_Format Customer Customer_ID Customer_Name Customer_Address Rents

  4. How Do You Select Upper and Lower Bounds? • Business rules of the organization • Database design decisions • Example: Auto service center • Guidelines: • Make the system as flexible as possible • Avoid M:M relationships when possible • Increase system complexity Car Car_ID Car_Make Car_Model Car_Year Customer Customer_ID Customer_Name Customer_Address owns ? ?

  5. Composite Relationship • Occurs for a relationship that has attributes • Always has M:M cardinality Student StudentID StudentFirstName StudentLastName StudentAddress StudentDOB StudentClass CourseSection CourseID CourseDay CourseTime CourseLocation EnrollsIn CourseGrade Attribute of the relationship

  6. Composite Relationships:Another Example Employee EmployeeID EmployeeName Project ProjectID ProjectName WorksOn Hours Attribute of the relationship

  7. Decomposing a Composite Relationship • Can always be decomposed into 2 1:M relationships! Student StudentID StudentFirstName StudentLastName StudentAddress StudentDOB StudentClass Course CourseID CourseName CourseDay CourseTime CourseLocation EnrollsIn CourseGrade Course CourseID CourseName CourseDay CourseTime CourseLocation Student StudentID StudentFirstName StudentLastName StudentAddress StudentDOB StudentClass Enrollment EnrollmentID CourseGrade Has Has Note the direction of the 1:Ms!

  8. Degree of a Relationship • Specifies the number of entities that participate in a relationship • Up until now, all relationships have been binary (2 entities) Video Video_ID Video_Title Video_Format Customer Customer_ID Customer_Name Customer_Address rents

  9. Unary Relationships • Entity has a relationship with itself

  10. Ternary Relationships • Relationship exists among 3 entities

  11. Generalization/Specialization Relationships • Do not show cardinalities Supertype Subtypes

  12. How common are these? • Unary • Rare • Ternary, Generalization/Specialization • More common than you would think • Be aware that they can exist and how you represent them!

  13. Homonyms • Homonym: different attributes have the same name • Avoid! • Attributes should take their name from their entity

  14. Synonyms • Synonym: same attribute has different names in different tables • FK field should have same name as PK field, unless another name makes it A LOT more descriptive • How should you change these entities?

  15. Strategies for Developing ER Models • How do you decide if an item is an entity or an attribute? • An item is an entity if… • An item is an attribute if…

  16. Strategies for Associating an Attribute With the Correct Entity • To which entity does an attribute belong? • Example: Is AdvisorName an attribute of a UniversityStudent instance? • Answer: Only if each advisor only advises only one student. Otherwise, you are repeating the name multiple times. • The ONLY thing that you can repeat in tables is a foreign key value! • Strategy: • If a non-attribute appears in multiple entities, it is not associated with the correct entity

More Related