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?
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-
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.
- Student (Name, StudentID, DOB, DOE)
- Course (courseCode, Instructor)
- Enrollment( StudentID, courseCode)