Transcript ER Diagram Example

ER Diagram Example
Exercise
Consider the following relations for a database that keeps track of
student enrollment in courses and the books adopted for each
course:
STUDENT(SSN, Name, Major, Bdate)
COURSE(Course#, Cname, Dept)
ENROLL(SSN, Course#, Quarter, Grade)
BOOK_ADOPTION(Course#, Quarter, Book_ISBN)
TEXT(Book_ISBN, Book_Title, Publisher, Author)
Draw a relational schema diagram specifying the foreign keys
for this schema.
2
Jinze Liu @ University of Kentucky
8/4/2016
Example 1
The company you work for wants to digitize their time cards. You have been asked to
design the database for submitting and approving time cards. Draw the database ER
diagram with the following information:
A timecard should have hours worked and date submitted
Each timecard is associated with exactly one employee
Each timecard should have a unique id
Each timecard has a status: it is either approved, not approved, or pending
Each employee has a unique id
Each employee has a name and address.
Each employee submits a time card every pay period. i.e. In 1 year, they will
submit multiple time cards
Each employee either has direct deposit or physical check as their method of
payment
Each employee is associated with exactly one manager
Each manager has a unique id and a name
Each manager is in charge of multiple employees
Each manager approves time cards for multiple employees
Example 1 Answer
Example 2
Travel Agency
You are asked to design a database for a travel agency
containing the following information about customers
and their trips:
 Each customer has an SSN, name and address.
 Each trip has an ID, date (i.e. the begin to end date of
the trip), total cost, and associated set of reservations.
 Each trip must be owned by exactly one customer.
 A reservation has a Conf#, date, and type.
hospital
Construct an E-R diagram for a hospital with a set of patients and a set of medical
doctors. Associate with each patient a log of the various tests and examinations
conducted
Entity‐Relationship Diagram For a
Primary School
While drawing the ER diagram for primary school, we had some assumption which is as
follows:‐
1. One teacher can teach multiple subjects
2. There is no elective /optional subject for any of the class in primary school.
3. In school, only one guardian name will be mentioned in database and hence it is
one‐to one relationship.
4. One student cannot be associated with more than one class. Where as in one class,
many number of student will be studying.
5. One teacher can teach in several classes and in one class several teachers can teach
different subjects and hence it is “many‐to‐many” relationship.
6. One student can be thought by many teacher and vice‐versa is also acceptable and
hence it is “many‐to‐many” relationship
7. One student need to pay fee every month, so that one student need to pay fee several
Time .
8. In entity student and teacher, age is derived attribute which can be derived by the date
of birth.
ER Diagram Mail order
E-R Diagram for Exam Scheduling
Consider a university database for the
scheduling of classrooms for final exams. This
database could be modeled as the singe entity
set as
** exam, with attributes course-name
,section-number ,room-number and time.
Alternatively, one or more additional entity
sets could be defined, along with relationship
sets to replace some of the attributes of the
exam entity set, as
• Course with attributes name ,, department,
and c-number
• Section with attributes s-number and
enrollment, and dependent as a weak entity
set on course
• Room with attributes r-number , capacity, and
building
Consider the following entity-relationship diagram representing
appointments that include one doctor, one nurse, and one patient:
Translate the E/R diagram to a relational schema using the method described in class
and in the course notes. Underline key attributes for the relations derived from
entity sets only.