Docstoc

tutorial1

Document Sample
tutorial1 Powered By Docstoc
					     COMP231 Tutorial 1

ER Model and ER to Relational Schema

         Wenwei Xue (Wayne)
          wwxue@cse.ust.hk
                              E-R Diagram

           Rectangles – entity sets

           Ellipses – attributes

           Diamonds – relationship sets

           Double ellipses – multivalued attributes

           Dashed ellipses – derived attributes

           Double lines – total participation

           Double rectangles – weak entity sets

           Double diamonds – identifying relationship sets



Database Management Systems            2                      Fall 2006
            Exercise 1.1 Construct E-R Diagram
     A university registrar’s office maintains data about the following entities:

           courses, including number, title, credits, syllabus, and prerequisites;
           course offerings, including course number, year, semester, section
            number, instructor(s), timings, and classroom;
           students, including student-id, name, and program;
           instructors, including identification number, name, department, and title.

     Further, the enrollment of students in courses and grades awarded to
        students in each course they are enrolled must be appropriately
        modeled.
     Construct an E-R diagram for the registrar’s office.




Database Management Systems                    3                                      Fall 2006
                              Entities


                               course-    instructor
                  student      offering




                                course




Database Management Systems         4                  Fall 2006
                                         Course
           “courses including number, title, credits, syllabus,
               and prerequisites”
                  Attribute?
                  Relationship?

                                          courseno
                                                                        credits
                                        prerequisite
                              require                          course
                                        maincourse                      syllabus
                                 s

                                                       title



Database Management Systems                      5                                 Fall 2006
                              Course Offering

       “course offerings, including course number, year,
           semester, section number, instructor(s), timings, and
           classroom”

                                                 semester
                                                             time
      courseno

                                                 course-
        course                 is_offere                           room
                                                 offering
                               d

                                                             secno
                                               year


Database Management Systems                6                         Fall 2006
                               Weak Entity

          A weak entity can only be identified uniquely by
              combining the primary key of another (owner) entity
              and the partial key of itself.

          Owner entity set and weak entity set must participate
              in one-to-many relationship (one owner entity, many
              weak entities).

          Weak entity set must have total participation in this
              identifying relationship set.




Database Management Systems               7                         Fall 2006
                                  Student, Instructor
         “students, including student-id, name, and program”

         “instructors, including identification number, name,
            department, and title”


                  sid              name        iid            name


                        student                       instructor


                         program               dept                title



Database Management Systems                8                               Fall 2006
                               Enrollment

         “Further, the enrollment of students in courses and
            grades awarded to students in each course they are
            enrolled must be appropriately modeled.”



                     student                     course-
                                 enrolls
                                                 offering



                                  grade




Database Management Systems            9                         Fall 2006
                              Anymore??

         Instructor teaches course…..




                   course-      teache    instructor
                   offering        s




Database Management Systems        10                  Fall 2006
                       E-R Diagram for a University




Database Management Systems          11               Fall 2006
         Exercise 1.2 Covert E-R Diagram into Tables




Database Management Systems   12                       Fall 2006
                              Entities (Not Weak)




Database Management Systems            13           Fall 2006
                              Entities (Not Weak)
         course (courseno, title, syllabus, credits)

         student (sid, name, program)

         instructor (iid, name, dept, title)




Database Management Systems            14               Fall 2006
                              Weak Entities




Database Management Systems         15        Fall 2006
                              Weak Entities
         course-offering (courseno, secno, year, semester,
            time, room)




Database Management Systems         16                        Fall 2006
     Relationships (Not defining weak entities)




Database Management Systems   17              Fall 2006
     Relationships (Not defining weak entities)
         enrolls (sid, courseno, secno, semester, year, grade)

         teaches (courseno, secno, semester, year, iid)

         requires (maincourse, prerequisite)




Database Management Systems         18                            Fall 2006
                 Relationships with Weak Entities




Database Management Systems      19                 Fall 2006
                  Relationships with Weak Entities
         There is no extra table for the relationship between a
            weak entity and its strong entity.

         The relationship is already present in the schema for
            the weak entity.


            course-offering (courseno, secno, year, semester,
            time, room)




Database Management Systems            20                          Fall 2006
              Relational Schemas for a University
         course (courseno, title, syllabus, credits)

         student (sid, name, program)

         instructor (iid, name, dept, title)

         course-offering (courseno, secno, year, semester,
            time, room)

         enrolls (sid, courseno, secno, semester, year, grade)

         teaches (courseno, secno, semester, year, iid)

         requires (maincourse, prerequisite)


Database Management Systems            21                         Fall 2006

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:13
posted:10/10/2010
language:English
pages:21