help with normalization

[slackertser]

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

[slackertser]

here is my work:

UNF:
(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, lecturer_no, center_name, centre_phone, class_size)

NF1:
booking: (booking_ref_no, booking_date, student_no, student_name, student_address, student_tel)
course: (course_code, course_name, course_level, course_start_date, course_finish_date, course_cost, lecturer_name, lecturer_no, centre_name, centre_phone, class_size)

NF2:
BOOKING: (booking_ref_no(pk), booking_date(pk), student_no(fk))
STUDENT (student_no(pk), student_name, student_address, student_tel)
COURSE: (course_code(pk), course_name, course_level, course_cost, course_start_date. course_finish_date, student_no(fk), lecturer_no(fk))
LECTURER: (lecturer_no(pk), lecturer_name, centre_name, centre_phone, class_size)

3NF: BOOKING: (booking_ref_no(pk), booking_date(pk), student_no(fk))
STUDENT (student_no(pk), student_name, student_address, student_tel)
COURSE: (course_code(pk), course_name, course_level, course_cost, course_start_date. course_finish_date, student_no(fk), lecturer_no(fk))
LECTURER: (lecturer_no(pk), lecturer_name)
CENTRE: (centre_name, centre_phone, class_size, lecturer_no(fk))

i look forward to your reply... thank you for your help

[timothybard]

It looks like you are off to a good start. However, I would suggest that, in addition to the Booking entity, you have an Enrolled entity to track that a person not only booked a course, but was accepted into the course.

Secondly, you have student_no as a foreign key in the course entity. This would only allow one student to be in a course; you need a many-to-many relationship between student and course.

Thirdly, unless I misread the description, you need to be able to track a course that is taught in several different locations and by several different lecturers. You need to change the relationships between course, lecturer and centre to support this.

As I said, what you have is very good, but it just needs to be touched up.

[slackertser]

thank you for your reply.. i appreciate it..

i have been examining a different designs but i am not sure if it is correct. here it is:

student (student_no(pk), student_name, student_add, student_tel)

booking(booking_ref_no(pk), student_no(fk), booking_date)
enrollment(booking_ref_no(pk)(fk), course_code(pk)(fk), course_start_date, course_finish_date)

course(course_code(pk), course_name, course_level, course_cost)

department(lecturer_no(pk)(fk), centre_no(pk)(fk), course_code(pk)(fk), class_size)

lecturer(lecturer_no(pk), lecturer_name)

centre(centre(no(pk), centre_name, centre_tel)

once again thank you for your help...

[timothybard]

I would recommend getting rid of the (pk)(fk) attributes in the department entity. I would recommend putting a department(fk) attribute in the lecturer entity and course entity instead. Also, there needs to be a many-to-many relationship between course and lecturer.

[slackertser]

I am confused now... i am a complete newbie... i am doing a self study course.. could you please explain to me what i should do so i can understand...

thank you

[coursehorror]

hey slakerster, it's been four years and the same normalizations problem has been assigned to us as a coursework. Could you please post your final solution here.. I'll thank you many... you must have gone through the same situation as I am now.. please help....!!!!

[coursehorror]

UNF:
(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, lecturer_no, center_name, centre_phone, class_size)

Here's what I got after 3NF, could some one verrify whether is the right way?

BOOKING: ( booking number (pk), booking_date, student_number (fk))
STUDENT: (student_number (pk), student_fname, student_lname, student_house_no, street_name, town_name, post_code, student_tel_no)
COURSE: (Course_code (pk), course_name, course_level, start_date, finish_date, Course_Cost)
LECTURER: (lecturer_code (pk), lecturer_fname, lecturer_lname lecturer_telephone
STUD_CRSE_LECT: (student_number (pk)(fk), Course_Code (pk)(fk), lecturer_code (fk), centre_name (fk))
CENTRE: (centre_name (pk), centre_telephone, centre_size)