FDD Diagram

Entity/Relationship Analysis

A new school, which prepares students for standard international
"A-level" examinations, wants you to design a database for it so that it
can record students' progress.

Entity Types

In this school there are Courses; Lecturers; Students; and Classrooms.

Relationships

Students enrol in Courses (up to four of them at a time). A Course can
exist with no Students, but every Student must be signed up for at least
one Course. Students will sit an exam in a given course on a certain
date, and receive a mark in that exam. Students may sit more than one
exam on a given date, but only in different Courses.

Lecturers are assigned Courses to teach. A Lecturer may be assigned to
no course, or to one, or to more than one. A Course will be taught be
only one Lecturer.

A Course is presented in one or more classrooms, on prescribed days and
times.

Students are each assigned a lecturer as a Personal Tutor. A lecturer
may have no tutees, or up to twelve. A few students are, in addition,
also assigned Subject Tutors. A Subject Tutor is a lecturer who tutors a
student in a given subject. A Student may, exceptionally, have more than
one subject Tutor for a given subject. And Students may have Subject
Tutors for more than one Subject.

Attributes

Unique Attributes: Courses are identified by Coursecodes; Lecturers by
Employee Numbers; Students by Registration Codes, and Classrooms by Room
numbers.

Non-Unique Attributes: Students have Names (Given, middle, and family);
birthdates; home addresses; and telephone numbers. Courses have a Name,
a date-initiated, and a Lead Lecturer (who may not be the lecturer
presenting the course in any given year); Lecturers have Names (Given,
middle, and family); birthdates; home addresses; and telephone numbers.
Classrooms have seating-capacities and equipment-available. Each
student's achievement in a single course is assessed by a single annual
exam.



EXAMPLE:

Pak Chong II, whose Student Registration Code is P0038, is enrolled in
Pure Maths, Level I (Coursecode PM0 l), Physics, Level I (Coursecode
PH0l), and Computing, Level II (Coursecode CM02). His Personal Tutor is
Dr Ngogi, whose Employee Number is E77623. Dr Ngogi is also his Subject
Tutor for Pure Mathematics, and for Applied Mathematics. Pak Chong II
has a second Subject Tutor for Applied Mathematics, Dr Kurosawa. Level I
Physics is taught by Mr Gundar, whose employee code is E54009, and is
presented in Main Hall Adjunct, whose Room Number is MH96 on Tuesdays
and Thursdays from loam to 1 1am, and in the Physics lab, Room 3, whose
Room Number is LB03, on Fridays from 2-5pm. The Main Hall Adjunct can
seat 75 people, and has an OHP projector, a network connection point,
and a whiteboard. This year Level Computing will be examined on 2nd of
June. Last year Pak Chong II sat his examination in Level I Computing on
23 May 2006, and received a mark of 74%.

YOUR ASSIGNMENT

A. Draw an Entity relationship diagram showing the relationships among
Students,

Courses, Lecturers and Rooms, as described above. Where appropriate
indicate any existence and cardinality constraints. Show the attributes
of each entity type.

Relationships between entity types should not be resolved into entity
types themselves

but should be shown as relationships, using the following conventions:

B. Draw up Functional Dependency Diagrams for all the attributes, using
the following conventions.

"Each lecturer may present several courses (but a course is presented by
only one lecturer)."

To show that for a given Course code there is only one Employee Number:

Course code ( Employee Number

"Students will sit an exam in a given course on a certain date, and
receive a mark in that exam."

To show that a given student may have taken a given exam on a certain
date, receiving

a grade for it:

StudentRegistrationNumber + Course code + Date ( Mark