Tutorial 4 ER Diagram

Document Sample
Tutorial 4 ER Diagram Powered By Docstoc
					Tutorial 4: ER Diagram
 Question.

          id                                             code



               student          takes           course


 Complete the diagram to capture the following constraints:

 Each student must take exactly one course.
 Every course must be taken by at least one student.




                                  2
          id                                               code



               student           takes            course



 Complete the diagram to capture the following constraints:

 Each student must take exactly one course.
 Every course must be taken by at least one student.




                                  3
  Question. Which design is better and why?



    Office as an attribute

 Employee_id      Office_number   Office_location


               Employee




    Office as an entity

Employee_id                                         Office_number   Office_location


              Employee                          Office

                                         4
    Office as an attribute

 Employee_id      Office_number   Office_location

                                                              redundancy in
               Employee                                    office_number and
                                                              office-location



    Office as an entity

Employee_id                                         Office_number   Office_location


              Employee                          Office

                                         5
 Question. What are the differences in the following two designs?


                       Account as an entity


Customer                      Account                      Branch



                      Account as a relationship


                Customer       Account        Branch




                                   6
 In the first diagram, accounts can be created without
  customers/branches. This is not possible in the second design.

 In the first diagram, many customers can share the same account.
  This is also not possible in the second one.


                       Account as an entity


Customer                      Account                      Branch



                      Account as a relationship


                Customer       Account        Branch

                                   7
 Question. What are the primary keys for the three relationships,
  respectively?




                                  8
 owns: {license}
 participated: {license}
 involved: {driver-id, report-number}




                                  9
 Question. Complete the following diagram to satisfy these constraints:
    Every director must have directed some movie, but not every actor
     has played in a movie.
    Each director/actor may have directed/played in multiple movies.
    Each movie is directed by a single director.
    Not every movie has a sequel.

         name            actor_id                name        director_id

                 actor                                  director


                           play        pay          direct          pay



                                    movie                            sequel to

                            title
                                            10
name           actor_id                name       director_id

       actor                                  director


                 play        pay          direct          pay

                                               original
                          movie                            sequel to
                                               sequel
                  title
                                  11