Key Entities
1. Student
o Attributes: StudentID (PK), FirstName, LastName, Email, DateOfBirth, EnrollmentDate
o Relationships: Enrolls in Courses, Has Grades
2. Course
o Attributes: CourseID (PK), CourseName, Credits, Semester
o Relationships: Has Students, Has Instructors
3. Instructor
o Attributes: InstructorID (PK), FirstName, LastName, Email, HireDate
o Relationships: Teaches Courses
4. Department
o Attributes: DepartmentID (PK), DepartmentName, Budget
o Relationships: Contains Courses, Has Instructors
5. Grade
o Attributes: GradeID (PK), GradeValue, DateAssigned
o Relationships: Belongs to Student, Related to Course
Inheritance Structure
• Person (Superclass)
o Attributes: PersonID (PK), FirstName, LastName, Email
o 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:
o Attributes: Prerequisites (optional), MaxEnrollment (mandatory)
• Grade:
o Constraints: GradeValue must be between 0 and 100.
3. Logical Design
Objective: Map the conceptual model to a logical schema.
Normalize the data to avoid redundancy and ensure data integrity:
o First Normal Form (1NF): Ensure each attribute has atomic values.
o Second Normal Form (2NF): Ensure no partial dependencies.
o Third Normal Form (3NF): Ensure no transitive dependencies.
