Question

An ER model contains:
• Student with attributes: StudentID, Name, Age 
• Course with attributes: CourseID, Title 
• A many-to-many relationship Enrols between Student and Course, with attribute Grade In the relational design, a separate table is needed to capture the relationship and its attribute. 
The correct schema design is?

• A. The Enrols relationship can be represented as a table with columns: StudentID, CourseID, Grade only, with no foreign keys
• B. The relational schema will have three tables: Student, Course, Enrols, where Enrols includes StudentID and CourseID as foreign keys referencing Student and Course
• C. The relational schema can merge Enrols into Student table without violating normalization
• D. The relational schema will have two tables only: Student and Course, since Enrols is many-to-many and does not require a separate table

Hint:

Whenever you see a Many-to-Many relationship in an ER diagram, remember: $N$ entities $+ 1$ relationship $= N+1$ tables. For 1:N relationships, you usually only need $N$ tables (by using a Foreign Key in the "Many" side).

Answer 1

The Correct Option is B

Concept:
In Entity-Relationship (ER) modeling, relationships between entities must be mapped to tables in a relational database. The mapping depends on the cardinality of the relationship. A many-to-many (M:N) relationship cannot be handled by simply adding a column to one of the existing entity tables; it requires a bridge table (also called a junction or associative table).

Step 1: Mapping the Entities.
First, we create independent tables for the entities:
• Student: (StudentID [PK], Name, Age)
• Course: (CourseID [PK], Title)

Step 2: Mapping the M:N Relationship.
A single student can take many courses, and a single course can have many students. To represent this:
• We create a third table, Enrols.
• This table must contain the Primary Keys (PK) of both related tables to identify which student is in which course.
• Therefore, StudentID and CourseID are included. These act as Foreign Keys (FK) referencing their parent tables.
• Any attributes belonging specifically to the relationship (like Grade) must also be placed in this table.

Step 3: Defining Constraints.
To ensure data integrity, the StudentID in Enrols must exist in the Student table, and CourseID must exist in the Course table. This is why they must be defined as Foreign Keys. The Primary Key of the Enrols table is usually a composite key consisting of (StudentID, CourseID).