Question

You are asked to design a relational database for a simple course registration software application for your school. The relational database must have the following information about the student, the course, and the registration, respectively

1. StudentID, FirstName, LastName, DataOfJoining, and Major
2. CourseNumber, CourseName,InstructorName, StartDate, EndDate, NumberOfCredits
3. ReferenceID, StudentID,CourseID, DateOfRegistration

Apply the following constrains while designing the database

1. Each student in the database must be uniquely identifiable
2. Each course listed in the database must be have unique CourseNumber
3. Each course registration must have unique ReferenceID.

A student can take multiple courses, but each course must be registered separately, and the following two rules must be applied

1. A student can do multiple course registrations
2. There can be many courses and a course can appear multiple times in the registration process.

Using pen and paper draw an Entity Relations Diagram (ERD) for the above application, then take a picture of it and submit. You may follow the below Steps for completing this assignment

Step 1: Identify the entities and their attributes involved in the given business.

Step 2: Write the entity names and their attributes along with the data type as tables.

Step 3: Identify the primary key fields and mark them as PK.

Step 4: Read and understand the business rules. Then, create natural relations between

entities that can capture the business rules. Identify the foreign key fields and mark them as FK.

Answer 1

• Step1: Following are the 3 entities identified as per the given data: Student, Course, and Registration.
• Step2: Following are the tables along with their respective attributes:

• Student(

  StudentID,

  FirstName,

  LastName,

  DataOfJoining,

  Major)

• Course ( CourseNumber, CourseName,InstructorName, StartDate, EndDate, NumberOfCredits )

• Registration ( ReferenceID, StudentID,CourseID, DateOfRegistration )

• Step3: For Student table, PK: StudentID, for Course table, PK: CourseNumber , for Registration table, PK: ReferenceID

• Step4: Since each student enrolls in a course and each course gets registered so, we can have the 2 relations Enrolls and Registered between these 3 entities.

• Enrolls is between tables Student and course with the following attributes:

• Enroll( StudentID, CourseNumber ) with FK: StudentID refrences StudentID of Student table, and FK: CourseNumber references CourseNumber of Course table

• Registered is between tables Course and Registration with the following atttributes:

• Registered( CourseNumber, ReferenceID ) with FK: CourseNumber references CourseNumber of Course table,and FK: ReferenceID reference ReferenceID of Registration table.

Following is the ERD representing the 3 entities and the 2 many-to-many relationships between them:

Note that both the 2 relations are many-to-many, thus indicated as M:N

Feel free to ask any doubts you may have. Happy to help.