1 / 22

COMP231 Tutorial 1

COMP231 Tutorial 1. ER Model and ER to Relational Schema. E-R Diagram. Rectangles – entity sets Ellipses – attributes Diamonds – relationship sets Double ellipses – multivalued attributes Dashed ellipses – derived attributes Double lines – total participation

marikor
Télécharger la présentation

COMP231 Tutorial 1

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. COMP231 Tutorial 1 ER Model and ER to Relational Schema

  2. 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

  3. 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.

  4. Entities course-offering instructor student course

  5. Course • “courses including number, title, credits, syllabus, and prerequisites” • Attribute? • Relationship? courseno credits prerequisite requires course maincourse syllabus title

  6. Course Offering • “course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom” semester time courseno course- offering is_offered room course secno year

  7. 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.

  8. 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

  9. Enrollment • “Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled must be appropriately modeled.” course-offering enrolls student grade

  10. Anymore?? • Instructor teaches course….. course-offering teaches instructor

  11. E-R Diagram for a University

  12. Exercise 1.2 Covert E-R Diagram into Tables

  13. Entities (Not Weak)

  14. Entities (Not Weak) • course (courseno, title, syllabus, credits) • student (sid, name, program) • instructor (iid, name, dept, title)

  15. Weak Entities

  16. Weak Entities • course-offering (courseno, secno, year, semester, time, room)

  17. Relationships (Not defining weak entities)

  18. Relationships (Not defining weak entities) • enrolls (sid, courseno, secno, semester, year, grade) • teaches (courseno, secno, semester, year, iid) • requires (maincourse, prerequisite)

  19. Relationships with Weak Entities

  20. 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)

  21. 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)

  22. Submitted for –www.mycollegebag.in

More Related