Question

A course consists of many subjects, but each subject can only be associated with one course. Each subject is co-ordinated by a lecturer and each lecturer can co-ordinate many subjects. Subjects and courses are identified by subject and course codes, and have a name. Lecturers are identified by an ID and have a name, room number and telephone contact number

.a. Using Enterprise Architect, create an ERD representing the above scenario. You need to include the cardinality of the relationships.

b. Transform the ERD to a set of relational tables.

Answer 1

Entity Relationship Diagram (ERD) :

This ERD consists of three entities

• Course :
  • Course_Code-Primary key
  • Course_Name
• Lecturer :
  • ID - Primary key
  • Name
  • room_number
  • telephone_number
• Subject :
  • ​​​​​​​Subject_Code -Primary key
  • Subject_Name
  • Course_Code - foreign key references course table
  • ID-foreign key references lecturer table

*************************************************

Relational tables :

1.Table Name :Course
Create table Course(
Course_Code varchar(10),
Course_Name varchar(50),
Constraint Course_PK Primary key (Course_Code));

2.Table Name :Lecturer
Create table Lecturer(
ID int,
name varchar(50),
room_number int,
telephone_number varchar(20),
Constraint Lecturer_PK Primary key (ID));

3.Table Name :Subject
Create table Subject(
Subject_Code varchar(10),
Subject_name varchar(50),
Course_Code varchar(10),
ID int,
Constraint Subject_PK Primary key (Subject_Code),
Constraint Subject_Course_FK foreign key (Course_Code) references course(Course_Code),
Constraint Subject_Lecturer_FK foreign key (ID) references Lecturer(ID));