CODEWITHSUNDEEP
databasedatabase-designrelational-databasedatabase-normalization
user3769364
user3769364

Reputation: 11

many to many normalization

I'm studying Database normalization and I just can't understand one thing. Suppose we have a Many-to-Many relation. We have a table Course and we have a table Student. Multiple students may take multiple courses.

Suppose we have a "transition" table Course_Student that stores only primary keys of a student and a course he has chosen.

My task is to make all tables in 3NF and if some table is not in 3NF then to explain why.

My question is: are these tables already in 3NF and I'm especially concerned about the "transition" table.

Thanks so much!

Course
id title
1  Math
2  Programming

Student
id name
1  John Stevens
2  Jack Ryan

Course_Student
course_id student_id
1         1
1         2
2         1


CREATE TABLE Course(
id int IDENTITY(1,1) PRIMARY KEY,
title varchar(100) NOT NULL
)

CREATE TABLE Student(
id int IDENTITY(1,1) PRIMARY KEY,
name varchar(50) NOT NULL
)

CREATE TABLE Course_Student(
course_id int FOREIGN KEY REFERENCES Course,
student_id int FOREIGN KEY REFERENCES Student
)

Upvotes: 0

Views: 788

Answers (2)

Erwin Smout
Erwin Smout

Reputation: 18408

Assessing normal form is something that is done to a database design that includes, for each table, the applicable functional dependencies.

Absent an explicit statement of the latter, all dependencies can only be assumed to be on (all) the keys, and hence the design can only be assumed to satisfy (at least) BCNF, by definition.

Upvotes: 1

Parimal Raj
Parimal Raj

Reputation: 20595

As per my understanding of Normalization, your tables are already in 3.5 NF (BCNF). The design of your table is great.

Over normalization can be bad sometimes but in your case it seems perfectly sane.

For the Table (Course_Student)

Primary Key should be (course_id, student_id)

with 2 foreign keys, referencing the two table :) !

Upvotes: 1

Related Questions