Database normalization question


#1

A database used by a college’s application stores the relationship between students and the courses they are enrolled in. We have information for each STUDENT (such as name, date of birth, date of enrollment, student-id) and COURSE (course code, instructor, etc.). In real life, a student takes several courses simultaneously while a subject is studied by several students. We need to capture this many-to-many relationship in our database. From the above information, what is the minimum number of tables required to structure this database in accordance with the rules of 2NF normalization?


#2

For a database to be in 2NF it should be 1. In 1NF 2. Every non-key attribute must fully functionally depend on primary key attribute. For a table to be in 1NF it should contain atomic values only.
Considering, student(id, name, dob, do_enroll) with id as the primary key.
Here, many students can be enrolled on a single date. Hence to achieve atomic values and reduce redundancy we will have to split the table as follows- stud_info(id,name,dob) and stud_admit(do_enroll, id)
Course table is already in 1NF with course code as the primary key.

Now the primary key is a single attribute key and not composite. Hence any table which is in 1NF and has single attribute as primary key is in 2NF.

Hence the tables formed in 2NF are-

  1. stud_info(id,name,dob,code)
  2. stud_admit(do_enroll,id)
  3. course(code,instructor)

#3

for 2NF normalization :- No non-prime attribute should depend on proper subset of candidate key.
therefore, the below three tables will suffice to show the many to many relation.

  1. Student (Name, StudentID, DOB, DOE)
  2. Course (courseCode, Instructor)
  3. Enrollment( StudentID, courseCode)