Chapter 2
Useful Material
Exercise Ch-02
Q5: The entity type STUDENT has the following
attributes:
• Student Name, Address, Phone, Age, Activity, and
No of Years. Activity represents some campus-
based student activity, and No of Years represents
the number of years the student has engaged in
this activity. A given student may engage in more
than one activity. Draw an ERD for this situation.
What attribute or attributes did you designate as
the identifier for the STUDENT entity? Why?
• 8. Figure 2-25 shows a grade report that is mailed
to students at the end of each semester.
• Prepare an ERD reflecting the data contained in the
grade report.
• Assume that each course is taught by one
instructor.
• Also, draw this data model using the tool (Ms-Visio)
• Explain what you chose for the identifier of each
entity type on your ERD.
Fig 2.25
ERD
Relational Schema
• Register(@sid, @cid, semester, year, grade)
Course(cid, title, @instructor_name)
Q9
• 9. Add minimum and maximum cardinality
notation to each
of the following figures, as appropriate:
a. Figure 2-5
b. Figure 2-10a
c. Figure 2-11b
d. Figure 2-12 (all parts)
e. Figure 2-13c
f. Figure 2-14
Fig 2-5
Fig 2-10 a
Fig 2-11 b
Fig 2-12
Fig 2-13 c
Fig 2-14
Find the Problem
Solution
Q 11
• 11. Figure 2-26 represents a situation of students who attend and work in
schools and who also belong to certain clubs that are located in different
schools.
• Study this diagram carefully to try to discern what business rules are
represented.
– a. You will notice that cardinalities are not included on the Works For relationship.
State a business rule for this relationship and then represent this rule with the
cardinalities that match your rule.
– b. State a business rule that would make the Located In relationship redundant
(i.e., where the school in which a club is located can be surmised or derived in
some way from other relationships).
– c. Suppose a student could work for only a school that student attends but might
not work. Would the Works For relationship still be necessary, or could you
represent whether a student works for the school she attends in some other way
(if so, how)?
Q 15b
• A university has a large number of courses in its
catalog.
• Attributes of COURSE include Course_num
(identifier), Course_Name, and Credit_Hrs.
• Each course may have one or more different courses
as prerequisites, or may have no prerequisites.
• Similarly, a particular course may be a prerequisite for
any number of courses, or may not be a prerequisite
for any other course.
ERD For Q 15b
Credit_hrs
New Style ERD For Q 15b
Example
• A university course may have one or more scheduled
sections, or it may not have a scheduled section.
• Attributes of COURSE include Course_ID (identifier),
Course_Name, and Credit_Hrs.
• Attributes of SECTION include Section_Number and
Semester_ID. Semester_ID is composed of two parts:
Semester and Year. Section_Number is an integer that
distinguishes one section from another for the same
course but it does not uniquely identify a section.
ERD For Example
Section was modeled as a weak entity. It could have
been modeled as a multi-valued attribute of course,
however, this model allows a section of a course to
have a relationship with another entity (think
instructor or student)…the multi-valued attribute
case would not allow this relationship.
New Style ERD For
Example
Section was modeled as a weak entity. It could have
been modeled as a multi-valued attribute of course,
however, this model allows a section of a course to
have a relationship with another entity (think
instructor or student)…the multi-valued attribute
case would not allow this relationship.
Example
• A laboratory has several chemists who work on one or more projects.
Chemists may also use certain kinds of equipment on each project.
Attributes of CHEMIST include Employee_ID (identifier), Name, and
Phone_no.
• Attributes of PROJECT include Project_ID (identifier) and Start_Date.
• Attributes of EQUIPMENT include Serial_no. and Cost.
• The organization wants to record Assign_Date – that is, the date when
a give equipment item was assigned to a particular chemist working on
a specified project.
• A chemist must be assigned to at least one project and one equipment
item.
• A given piece of equipment need not be assigned, and a given project
need not be assigned either a chemist nor a piece of equipment.
ERD For Example
This relationship was created to show what
projects a chemist works on. In the case
where no equipment is used for a project, All three entities participate in
there would be no way of showing an an assignment. However,
assignment using the Assigned relationship. EQUIPMENT and
PROJECT do not need to
participate in any
assignments. All entities
can have multiple
assignments.
New Style ERD For Example
This relationship was created to show what
projects a chemist works on. In the case
All three entities participate in
an assignment. However,
where no equipment is used for a project, EQUIPMENT and
there would be no way of showing an PROJECT do not need to
assignment using the Assigned relationship. participate in any
assignments. All entities
can have multiple
assignments.
Example
• Projects Inc., is an engineering firm with approximately 500 employees. A database is required to
keep track of all employees, their skills, assigned projects, and departments in which they work.
• Every employee has a unique number assigned by the firm, a name, and date of birth. If an
employee is married to another employee of the firm, the data of the marriage and who is
married to whom must be stored; however, no record of marriage is required if an employee’s
spouse is not also an employee. Each employee has a job title. Each employee does only one type
of job at a time, and we only need to retain information about an employee’s current job.
• There are 11 different departments in the firm, each with a unique name. An employee can report
to only one department. Each department has a phone number.
• To procure various types of equipment, each department deals with many vendors. A vendor
typically supplies equipment to many departments. We need to store the name and address of each
vendor and the date of the last meeting between a department and a vendor.
• Many employees can work on a project. An employee can work on many projects, but can only be
assigned to at most one project in a given city. For each city, we are interested in its state and
population.
• An employee can have many skills, but they can use only a given set of skills on a particular
project. Employees use each skill that they posses in at least one project. Each skill is assigned a
number, and we will record a short description of each skill.
• Projects are distinguished by project numbers and we must store the estimated cost of each project.
ERD For Example
New Style ERD For
Example
Example
• Each semester, each student must be assigned an advisor who
counsels students about degree requirements and helps the
students register for classes.
• Each student must register for classes with the help of an
advisor, but if the student’s assigned advisor is not available, the
student may register with any advisor.
• We must keep track of students, the assigned advisor for each,
and the name of the advisor with whom the student registerd for
the current term.
ERD For Example
New Style ERD For Example