FD / Normalization 1

[2122002]

Please check if below Normalisation is correct, also advice where to improve and reasons for an improvement.


FDs and thier meaning to be Normalized:

exam_date: the date on which the exam session was held
exam_name: Name of exam to be taken
StudNo: Identify'n Nos of student
exam_time: Time of the exams
exam_location: Location of the exam
Invig_attendant: Invigetator staff in charge of the exam
Co_suppervisor: Supervisor attending the
Supper2: Suopervisor attending the Exam

Below is my temporary solution:

exam_name -> exam_location
exam_date, exam_location -> Invigilator
Invig_attendance -> exam_location
StudNo -> exam_location, exam_date
exam_date, exam_location -> Co_suppervisor

Below is my normalisation:

Student(StudNo, exam_name)
ExamLocation(Exam_location, exam_name, exam_date, Supperv2)
Staff(exam_name, Invg_attendance, Co_supervisor)

 

[SantaMufasa]

Dan,

On this particular posting, rather than my critiquing/fixing your normalisation, I believe you will be better served by your presenting to us your rationale for the normalisation that you have chosen.

I will get you started by making a general comment, asking some questions (and then asserting some answers) regarding your normalisation:

General comment: The names of your tables and columns should reflect exactly the type of data that you store in a table. For example, your "Student" table has two columns: "StudNo" and "exam_name". I would expect to see a "StudNo" in a "Student" table, but I would also expect to see a Student's "LastName" and "FirstName", as well; I would not expect to seen an "exam_name" in a "Student" table since such a designation disobeys Second Normal Form.

I would, instead, expect to see a "Student_Exam" table that cross references the many-to-many relationship that exists between Students and Exams. I would also expect to see as attributes in the "Student_Exam" table Foreign Keys to "Student", "Exam", and "Location", along with a "Exam_Date", "Exam_Score", et cetera.

I'll say no more at this point...you post us your rationale for your normalisations now.
-

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[2122002]

How about this:

Student(StudNo, StudNames)
Examination(exam_name, exam_date)
Supervisors(Invg_attendance, Co_supervisor,Supperv2)

Which means, I have added name of students to my table

The question raised now is this:
it is assumed that many studuent writes course CSO9: Oracle Database,

how do I know which staff supervise this particular course?? Shold I inlcude "exam_Code", thus have below normalisation:

Student(StudNo, StudNames, exam_code)
Examination(exam_code, exam_name, exam_date)
Supervisors(exam_code, Invg_attendance, Co_supervisor,Supperv2)

Therefore, My Primary Keys are StudNo, exam_code (STUDENT)
and Foreign Keys: exam_code (EXamination and Supevisors) REFERENCES student.

Please check.....




Dan

 

[2122002]

Musafa, I used "4NF".

http://en.wikipedia.org/wiki/Database_normalization#Further_Reading

Dan

 

[SantaMufasa]

Dan,

I have a better solution for you than our attempts at a piecemeal tutorial via Tek-Tips of your database design/normalisation training: If you are serious about learning proper database design and proper normalisation, I highly recommend a great book that you can obtain from Amazon.com, "Case*Method: Entity Relationship Modelling", by Richard Barker. Barker explains things very nicely and progressively builds an easy-to-understand airline-reservation system as a case study throughout the book. Barker was formerly the President of Oracle UK Ltd. and this book is the Bible for database design in the Oracle World.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[2122002]

Great, Will check on it now.

Many thanks.


Dan