Key Entities

Student

Attributes: StudentID $($PK$),$ FirstName, LastName, Email, DateOfBirth, EnrollmentDate

Relationships: Enrolls in Courses, Has Grades

Course

Attributes: CourseID $($PK$),$ CourseName, Credits, Semester

Relationships: Has Students, Has Instructors

Instructor

Attributes: InstructorID $($PK$),$ FirstName, LastName, Email, HireDate

Relationships: Teaches Courses

Department

Attributes: DepartmentID $($PK$),$ DepartmentName, Budget

Relationships: Contains Courses, Has Instructors

Grade

Attributes: GradeID $($PK$),$ GradeValue, DateAssigned

Relationships: Belongs to Student, Related to Course

Inheritance Structure

Person $($Superclass$)$

Attributes: PersonID $($PK$),$ FirstName, LastName, Email

Subclasses: Student, Instructor

Relationships and Cardinalities

A Student can enroll in one or many Courses.

A Course can have zero or many Students enrolled.

An Instructor can teach one or many Courses.

A Department can have zero or m Instructors and Courses.

Extended Attributes and Constraints

Course:

Attributes: Prerequisites $($optional$),$ MaxEnrollment $($mandatory$)$

Grade:

Constraints: GradeValue must be between $0$ and $100.$

Logical Design

Objective: Map the conceptual model to a logical schema.

Normalize the data to avoid redundancy and ensure data integrity:

First Normal Form $(1$NF$)$: Ensure each attribute has atomic values.

Second Normal Form $(2$NF$)$: Ensure no partial dependencies.

Third Normal Form $(3$NF$)$: Ensure no transitive dependencies.