Hi there,
I am a complete newbie and need help with normalization and ER diagram. here is the case study:
a college specializes in providing foreign language courses at various levels to the general public. it consists of several local centers in and around the city. at present, course enquiries and bookings are managed centrally by the college on a semi-automated basis, namely, they are handled by a largely paper-based file system, supported partially by a basic computer system.
the college provides courses for various foreign languages. each course is associated with a particular level (intro, intermediate, or advanced). any of the courses in identified with a course code, but may be run at different centers of the college and taught by different lecturers within the same period. in addition, a lecturer may teach at any of the centers if necessary.
a potential student will initially make an enquiry to one of the course officers at the central enquiry unit of the college. a brief conversation with the student will normally enable the course officer to ascertain the course in which the student is interested, and to identified any appropriate level for the student.
the course officer will then check the cost and dates of the course, together with its availability. in most cases, the student will be offered to choose from a list of available centers and lecturers that run the same course. once a choice has been made by the students and provided the chosen class is not yet fully booked, the course officer will complete the course booking form:
(booking_ref_no, booking_date, student_no, student_name, student_address, student_tel, course_code, course_name, course_level, course_start_date, course_finish_date, course_cost, lecturer_name, center_name)
a copy of this form will be given to the student. each course booking form deals with one course booking only. a student may book several courses, in which case separate course booking forms will need to be completed individually.
when a student confirms the booking, a copy of the course booking form will be passed to the administrative officer at the registry of the college, who will prepare a formal enrollment form. this enrollment form will be sent to the student, together with an invoice for payment and other relevant course information.
the administrative officer will then use the details contained in the enrollment form to update the course summary form:
(course_code, course_name, course_level, course_start_date, course_finish_date, course_cost)
this form provides for each course the most up to date information on all the centers and lecturers running the course, together with a list of students who have registered for the course so far. a copy of this course summary will be provided to all centers and lecturers involved for record keeping. consequently, each lecturer will be able to extract information from this form to compile his own class registration list
registry produces other forms such as lecturer assignment forms, center assignment forms, course assignment forms, staff details forms, students details forms, course student reports, lecturer student reports.
I'll try to work it out... i'll show you my work in 15 mins.
please advise me how to make it properly...
thank you