Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2008
    Posts
    1

    help with normalization and ER diagram

    Hi there,

    i am a complete newbie and need help with normalization and ER diagram. I tried to work out (please see below) but i am not sure if it is right please advise me how to make it properly. thank you in advance

    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

    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 appreciate your consideration and help.
    thank you

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    you are probably best bouncing this off your fellow students and or leturers at this stage

    best thing to do is to go through each stage of the requirement and think if you have removed every bit of duplication, and have identified entities which only contain information which is truly relevant to that entity. Id also suggest you make sure that every part of the requiremnt is covered. ie can you store everything that is mentioned, can you access everythign that is mentioned

    ultimately WE don't do YOUR homework, WE may assist (if WE choose to), but you cannot rely on that. ultimately its your homework designed to encourage you to learn what to do
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •