Question

Question: Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course. 

1- Draw an ER diagram of ONLINE COURSE REGISRATION database, that captures all the given below requirements. Specify key attribute(s) of each entity set. For each relationship set, specify structural constraints and participation constraints. 

a. For each USER, the portal maintains user ID, Name, E-mail. Each user has a unique ID. Name is a Composite attributes with Frame, Midname, Lname. 

b. Each COURSE is described by a Course Code, Description, Category, and Term. Both Code and Term have unique values for each course. 

c. Each LECTUR has a lecturer Title ID, Duration, and Date. The value of ID is unique for each lecturer.

 2- Draw a Schema Diagram of ONLINE COURSE database with 3 tables and their probable columns.

Answer 1

To draw the ERD for the given ONLINE COURSE REGISTRATION let’s follow the below steps:

Step 1: List the entities and their corresponding attributes with key columns:

**Primary keys are underlined and highlighted:

• USER(User_ID, Name(Fname, Midname, Lname) , Email)
• COURSE(Course_Code, Term, Description, Category)
• LECTUR (Title_ID, Duration, Date)

Step 2: List the relationship with cardinality for each entity’s participation and constraint:

• USER can enrol for exactly one COURSE, similarly the COURSE can have atleast one to many USER. It is Many to one (M:1) relation constraint with total participation where USER can exist only when he/she has made enrolment for a COURSE.

• COURSE will have many LECTURE whereas a LETURE will be associated with one COURSE only. It is One to Many (1:M) relation constraint with total participation where LECTUR can exist only if it is associated with a COURSE.

• LECTURE will be attended by many USERS whereas a USER can also attend many LECTUR. It is a Many to Many (M:M) relationship constraint.

Step 3: Draw the ERD for given system using data from Step 1 and Step 2:

Fname Midname User_ID Course_Code Lname (Name) Term Email USER USER fan enrolls for COURSE Description (1,N) enrolls for (11) (1,N) Category attended by for (1,M) (1,M) LECTURE Title_ID Duration Date

Step 4: ERD symbols used in diagram:

Below are the symbols used in the Diagram above:

Entity:       

Entity

Attribute:

Attribute

Relationship:

Relationship

Unique attribute:

AttributeUnique

Composite Attribute:

(Attribute Composite)