1 / 19

CS 370 Database Systems

CS 370 Database Systems. Lecture 6 Introduction to Database Design. Name. Students. Picnic. Teacher. Name. date. destination. weather. A case Study. A primary school student writes a composition about a picnic: Today is Sep 9, the weather is fine.

quincy-hooper
Télécharger la présentation

CS 370 Database Systems

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. CS 370 Database Systems Lecture 6 Introduction to Database Design CS370 Spring 2007

  2. Name Students Picnic Teacher Name date destination weather A case Study A primary school student writes a composition about a picnic: Today is Sep 9, the weather is fine. My classmates, Ali, Maria and I go to a picnic in park. Our teacher is Mr. Khan My Initial Design: CS370 Spring 2007

  3. Questions ? • Why “Ali”, “Maria”, “Mr. Khan” are not in the ER diagram ? • What do these names tell us ? • What are the keys of Student, Picnic & Teacher ? • What are the cardinalities of the relationships ? CS370 Spring 2007

  4. destination Name StudentNo Name weather date goes Teacher Picnic leading Student My solution • Every student has an ID number, it is better to keep it in the database and use it as a key • I bet that there won’t be teachers with the same name; otherwise, I’ll add employee number and use it as a key • goes is N:M, why ? A picnic has more than one student participating;also, a student can go to more than 1 picnic. However, this N:M relationship allows a student to go to more than one picnic on the same date • leading is N:1 , why? Depends on your assumptions • I assume a teacher can only lead 1 picnic on a certain date, so given the teacher name and the date, I can identify a picnic • Picnic is made a weak entity. I could have added a PicnicNo, but it would be very awkward. CS370 Spring 2007

  5. E-R Design Decisions • The use of an attribute or entity set to represent an object. • Should an address be an attribute or entity? • Whether a real-world concept is best expressed by an entity set or a relation set. • Should marriage be an entity or relationship? • Should picnic be an entity or relationship? • The use of a ternary relationship versus a pair of binary relationships. • See the borrow-loan-branch example • The use of a strong or weak entity set. • See the employee-dependent example CS370 Spring 2007

  6. Number Name Name Location Name Sex Birthdate Number Relationship E-R Diagram for Company Database Fname Minit Lname Locations WORKS_FOR Address Name Sex Salary Ssn DEPARTMENT Number Of Employees Startdate EMPLOYEE Bdate MANAGES CONTROLS supervisor supervisee Hours SUPERVISION WORKS_ON PROJECT DEPENDENTS_OF DEPENDENT Can you translate it back into English? CS370 Spring 2007

  7. Limitations of ER model • Consider representing Part-time and Full-time employees in the company database: • Either you have two entity types will lots of similarity • Or you have a single entity type with redundancy for most of the entities within it • ER model is extended to support other features such as generalization (but it won’t be covered in this course!) CS370 Spring 2007

  8. Reduction of an E-R Schema to tables • Primary keys allow entity sets and relationship sets to be expressed uniformly as tables which represent the contents of the database. • A database which conforms to an E-R diagram can be represented by a collection of tables. Always! • Converting an E-R diagram to a table format is the basis for deriving a relational database design from an E-R diagram. CS370 Spring 2007

  9. customer customer-name customer-id customer-street customer-city Jones 321-12-3123 Main Harrison Smith 019-28-3746 North Rye Hayes 677-89-9011 Main Harrison Composite key payment loan-no payment-no payment-date payment-amount L-17 5 10 May 1999 50 L-23 11 17 May 1999 75 L-15 22 23 May 1999 300 Representing Entity Sets as Tables • A strong/regular entity set reduces to a table with the same attributes. • A weak entity set becomes a table that includes a column for the primary key of the identifying strong entity set CS370 Spring 2007

  10. depositor customer loan date cust-name cust-no share% loan-no Representing Relationship Sets as Tables • A many-to-many relationship set is represented as table with columns for the primary keys of the two participating entity sets, and any descriptive attributes of the relationship set. depositor CS370 Spring 2007

  11. borrow customer loan date cust-name cust-no loan-no customer indicates who borrowed the loan cust-no cust-name loan A12345 Peter Wong loan-no cust-no B56789 Mary Cheung L-001 A12345 date L-002 B56789 Sep 2000 Aug 2001 • For 1:N and 1:1 relationships, you can create a table for each relationship • But it is more concise to merge the relationship-table with the entity-table on the “N” side CS370 Spring 2007

  12. Questions to Think About • In a 1:N relationship, can we include the key from the “N” side in the table representing the entity in the “1” side? I.e., include Loan_no into the Customer table. Why and Why not? • How can we express existence constraints on table? CS370 Spring 2007

  13. borrow customer loan date cust-name cust-no loan-no loan customer loan-no cust-no cust-name loan-no L-001 cust-no A12345 Peter Wong L-001 L-002 A12345 date Which one is better? B56789 Mary Cheung L-002 B56789 Sep 2000 Aug 2001 Questions to Think About (Cont.) • In a 1:1 relationship, we can include the key from either entity into the table representing the other entity. Suppose the Loan-Customer relationship is 1:1, would you include the Customer_no into Loan or Loan_no into Customer? CS370 Spring 2007

  14. borrow customer loan date cust-name cust-no loan-no loan loan-no Not allowed; must be enforced by DBMS L-001 cust-no L-002 A12345 date Sep 2000 Aug 2001 Questions to Think About (Cont.) • How can we express existence constraints on table? CS370 Spring 2007

  15. Already indicates the 1:N relationship between loan-no and payment-no payment loan-no payment-no payment-date payment-amount L-17 5 10 May 1999 50 L-17 6 17 May 1999 75 L-17 7 23 May 1999 300 Weak Entities • Since a weak entity has to include the primary key of the identifying entity, the 1:N relationship is already captured. E.g., The payment table already contains information about the Loan (I.e., loan_no) CS370 Spring 2007

  16. Summary of Symbols Used in E-R Notation CS370 Spring 2007

  17. Summary of Symbols Used in E-R Notation CS370 Spring 2007

  18. E-R Diagram for a Banking Enterprise CS370 Spring 2007

  19. E-R Diagram for a Publisher Enterprise E-R Diagram for Exercise 2.12 CS370 Spring 2007

More Related