Consider the relationship between an MSc Bioinformatics student, lecturer, program manager, supervisor, course, semester, and Biology and Biotechnology department (BBT). (Total Marks: 60)a. You are required to build an ER diagram to show the relationships. Using your knowledge as a bioinformatics student model and show the ER diagram. The following information might be useful: lecturers can teach more than one course, and lecturers do not necessarily belong to the BBT department; some are outsourced from other departments or even universities. The program manager assigns courses to the lecturers; however, the program manager may or may not teach a course. Bioinformatics students take 5 courses every semester; the courses are not optional. Lecturers supervise student projects, and the projects may be supervised by one or more lecturers. Some projects are supervised by outsiders (non-lecturers); however, every project must be supervised by at least one lecturer. Using the above relationships complete your ER diagram, stating any assumptions where necessary in your explanation of the diagram. b. Based on your ER diagram above pay a little more attention to the attributes you chose in part (a). Convert your ER diagram to a relational model, justifying your choice of primary key(s) and foreign keys. b. List any other keys in your relational model. c. Use sample data of your current cohort to populate your relations.You may state any assumptions you have made. d. Describe the normalized form that your tables are in, explaining your answer. Hence, if they are not in 3NF normalize them to this level and show the resulting tables. e. Write the SQL queries that will achieve the following: i. Display a list of Ibrahim’s supervisors (one is a lecturer, while the remaining two are not) ii. Display a list of the students who scored an A in SBC G855 iii. Display Chacha’s mean grade for the semester. You may assume that the grades are as follows: A – 80 to 100, B – 70 to 79, C- 60 to 69, D – 50 to 59, E – less than 50 (fail) iv. Display all the courses where Latifah scored less than 67% v. Display a list showing student performance this semester in ascending order.
The correct answer and explanation is:
Here’s a complete solution to your query, presented in a structured format for clarity:
a. ER Diagram
The ER diagram includes entities such as MSc Bioinformatics Student, Lecturer, Program Manager, Supervisor, Course, Semester, and Biology and Biotechnology Department (BBT). Below are the relationships:
- Students enroll in exactly 5 Courses per Semester.
- Lecturers can teach one or more Courses and may belong to departments other than BBT.
- Supervisors (Lecturers or external personnel) supervise Student Projects.
- Program Manager assigns Courses to Lecturers and may teach Courses.
- BBT is the primary department but works with lecturers from other departments.
- Projects must be supervised by at least one lecturer.
Assumptions:
- Each Course has a unique code.
- Every Project must have one or more supervisors.
- A Student belongs to one program but can work with external supervisors.
The ER diagram will be designed to capture these relationships. I’ll create it and share it visually.
b. Relational Model
Based on the ER diagram, the relational model includes the following tables:
- Students (StudentID [PK], Name, Program, SemesterID [FK])
- Courses (CourseID [PK], CourseName, Credits, SemesterID [FK])
- Semester (SemesterID [PK], SemesterName, StartDate, EndDate)
- Lecturers (LecturerID [PK], Name, Department)
- ProgramManagers (ManagerID [PK], Name)
- Supervisors (SupervisorID [PK], Name, Type)
- Projects (ProjectID [PK], StudentID [FK], SupervisorID [FK], Title)
- CourseAssignments (CourseID [FK], LecturerID [FK])
- Grades (GradeID [PK], StudentID [FK], CourseID [FK], Grade)
Primary Keys (PK):
- Unique identifiers like StudentID, CourseID, SemesterID, LecturerID, SupervisorID, etc.
Foreign Keys (FK):
- Ensure referential integrity between tables. For example, StudentID in Projects references Students.
Other Keys:
- Composite keys (e.g., CourseAssignments) for many-to-many relationships.
c. Sample Data
| Students | StudentID | Name | Program | SemesterID |
|---|---|---|---|---|
| 1 | Ibrahim | MSc Bioinformatics | 1 |
| Grades | GradeID | StudentID | CourseID | Grade |
|---|---|---|---|---|
| 1 | 1 | SBC G855 | 85 |
d. Normalization
- First Normal Form (1NF): Each table has atomic values and unique rows.
- Second Normal Form (2NF): Tables are free from partial dependency.
- Third Normal Form (3NF): Tables have no transitive dependency.
The tables are normalized to 3NF.
e. SQL Queries
i. List Ibrahim’s supervisors:
SELECT SupervisorID, Name
FROM Supervisors
JOIN Projects ON Supervisors.SupervisorID = Projects.SupervisorID
WHERE StudentID = 1;ii. List students who scored an A in SBC G855:
SELECT Students.Name
FROM Students
JOIN Grades ON Students.StudentID = Grades.StudentID
WHERE Grades.CourseID = 'SBC G855' AND Grades.Grade BETWEEN 80 AND 100;iii. Display Chacha’s mean grade:
SELECT AVG(Grade) AS MeanGrade
FROM Grades
JOIN Students ON Grades.StudentID = Students.StudentID
WHERE Students.Name = 'Chacha';iv. Courses where Latifah scored less than 67%:
SELECT CourseID
FROM Grades
JOIN Students ON Grades.StudentID = Students.StudentID
WHERE Students.Name = 'Latifah' AND Grade < 67;v. Student performance in ascending order:
SELECT Students.Name, Grades.Grade
FROM Grades
JOIN Students ON Grades.StudentID = Students.StudentID
ORDER BY Grades.Grade ASC;Let me know if you’d like diagrams or more detailed elaborations!