Problem: A university database maintains information about students, courses, instructors, and enrollments.
- Each student can enroll in multiple courses.
- Each course can be taught by multiple instructors.
- The database also stores the grades of students for each course.
Database Schema:
- Students: (StudentID, Name, Email, Department)
- Courses: (CourseID, CourseName, Credits, Department)
- Instructors: (InstructorID, InstructorName, Email, Department)
- Enrollments: Links a student to a course and stores the grade
- Teaches: Links an instructor to a course
Tasks:
- Draw an ER-Diagram that clearly shows entities, attributes, and relationships.
- Critically analyze the database design in terms of consistency, atomicity, and integrity.
Answer
1. Text-Based Relations only (Drawing Base Recommended)
+-------------------+ +-------------------+ +-------------------+
| STUDENTS | | COURSES | | INSTRUCTORS |
+-------------------+ +-------------------+ +-------------------+
| StudentID (PK) | | CourseID (PK) | | InstructorID (PK) |
| Name | | CourseName | | InstructorName |
| Email (Unique) | | Credits | | Email (Unique) |
| Department | | Department | | Department |
+-------------------+ +-------------------+ +-------------------+
| | |
v v v
+-------------------+ +-------------------+
| ENROLLMENTS | | TEACHES |
+-------------------+ +-------------------+
| StudentID (FK) |<------->| CourseID (FK) |
| CourseID (FK) | | InstructorID (FK) |
| Grade | | |
| PK=(StudentID, | | PK=(InstructorID, |
| CourseID) | | CourseID) |
+-------------------+ +-------------------+
2. Consistency
The design ensures consistency through:
- Enrollments: Students can only enroll in valid courses (foreign key constraint).
- Grades: Grades exist only if enrollment exists (linked via StudentID & CourseID).
- Teaches: Instructors can only be assigned to existing courses (foreign key constraint).
3. Atomicity
Enrollment and grading transactions must be handled as all-or-nothing:
- If a student enrolls, both enrollment and initial grade record must succeed, otherwise rollback.
- Updating grades must only occur if enrollment exists; failed updates trigger rollback.
4. Integrity
To maintain data integrity, the following constraints are necessary:
- Primary Keys:
- Students(StudentID)
- Courses(CourseID)
- Instructors(InstructorID)
- Enrollments(StudentID, CourseID)
- Teaches(InstructorID, CourseID)
- Foreign Keys:
- Enrollments.StudentID → Students.StudentID
- Enrollments.CourseID → Courses.CourseID
- Teaches.InstructorID → Instructors.InstructorID
- Teaches.CourseID → Courses.CourseID
- Unique: Student and Instructor emails must be unique.
- Check: Grade must be valid (A, B, C, D, F), Credits must be greater than 0.
Example SQL Constraint:
CHECK (Grade IN ('A','B','C','D','F'))
5. Justification
Consistency ensures valid links between students, courses, and instructors.
Atomicity prevents partial execution of enrollment or grading.
Integrity constraints enforce correctness of data through PK, FK, Unique, and Check rules.
কোন মন্তব্য নেই:
একটি মন্তব্য পোস্ট করুন