Transcript ER Model and ER to Relational Schema

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
 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
student
courseoffering
instructor
course
Database Management Systems
4
Fall 2006
Course
 “courses including number, title, credits, syllabus,
and prerequisites”
 Attribute?
 Relationship?
courseno
credits
prerequisite
require
s
course
maincourse
syllabus
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
courseoffering
is_offere
d
year
Database Management Systems
6
room
secno
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
student
instructor
program
Database Management Systems
name
dept
8
title
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
enrolls
courseoffering
grade
Database Management Systems
9
Fall 2006
Anymore??
 Instructor teaches course…..
courseoffering
Database Management Systems
teache
s
10
instructor
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
Submitted for –
www.mycollegebag.in
Database Management Systems
22
Fall 2006