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\u2019s 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\u2019s mean grade for the semester. You may assume that the grades are as follows: A \u2013 80 to 100, B \u2013 70 to 79, C- 60 to 69, D \u2013 50 to 59, E \u2013 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.<\/p>\n\n\n\n
The correct answer and explanation is:<\/mark><\/strong><\/p>\n\n\n\n Here\u2019s a complete solution to your query, presented in a structured format for clarity:<\/p>\n\n\n\n The ER diagram includes entities such as MSc Bioinformatics Student<\/strong>, Lecturer<\/strong>, Program Manager<\/strong>, Supervisor<\/strong>, Course<\/strong>, Semester<\/strong>, and Biology and Biotechnology Department (BBT)<\/strong>. Below are the relationships:<\/p>\n\n\n\n The ER diagram will be designed to capture these relationships. I\u2019ll create it and share it visually.<\/p>\n\n\n\n Based on the ER diagram, the relational model includes the following tables:<\/p>\n\n\n\n The tables are normalized to 3NF.<\/p>\n\n\n\n Let me know if you’d like diagrams or more detailed elaborations!<\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[25],"tags":[],"class_list":["post-186395","post","type-post","status-publish","format-standard","hentry","category-exams-certification"],"_links":{"self":[{"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/posts\/186395","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/comments?post=186395"}],"version-history":[{"count":0,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/posts\/186395\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/media?parent=186395"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/categories?post=186395"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/tags?post=186395"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n\n\n\na. ER Diagram<\/strong><\/h3>\n\n\n\n
\n
Assumptions:<\/h3>\n\n\n\n
\n
\n\n\n\nb. Relational Model<\/strong><\/h3>\n\n\n\n
\n
Primary Keys (PK)<\/strong>:<\/h4>\n\n\n\n
\n
Foreign Keys (FK)<\/strong>:<\/h4>\n\n\n\n
\n
Other Keys<\/strong>:<\/h4>\n\n\n\n
\n
\n\n\n\nc. Sample Data<\/strong><\/h3>\n\n\n\n
Students<\/strong><\/th> StudentID<\/strong><\/th> Name<\/strong><\/th> Program<\/strong><\/th> SemesterID<\/strong><\/th><\/tr><\/thead> 1<\/td> Ibrahim<\/td> MSc Bioinformatics<\/td> 1<\/td> <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Grades<\/strong><\/th> GradeID<\/strong><\/th> StudentID<\/strong><\/th> CourseID<\/strong><\/th> Grade<\/strong><\/th><\/tr><\/thead> 1<\/td> 1<\/td> SBC G855<\/td> 85<\/td> <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\nd. Normalization<\/strong><\/h3>\n\n\n\n
\n
\n\n\n\ne. SQL Queries<\/strong><\/h3>\n\n\n\n
i. List Ibrahim\u2019s supervisors:<\/h4>\n\n\n\n
SELECT SupervisorID, Name \nFROM Supervisors\nJOIN Projects ON Supervisors.SupervisorID = Projects.SupervisorID\nWHERE StudentID = 1;<\/code><\/pre>\n\n\n\nii. List students who scored an A in SBC G855:<\/h4>\n\n\n\n
SELECT Students.Name \nFROM Students\nJOIN Grades ON Students.StudentID = Grades.StudentID\nWHERE Grades.CourseID = 'SBC G855' AND Grades.Grade BETWEEN 80 AND 100;<\/code><\/pre>\n\n\n\niii. Display Chacha\u2019s mean grade:<\/h4>\n\n\n\n
SELECT AVG(Grade) AS MeanGrade \nFROM Grades\nJOIN Students ON Grades.StudentID = Students.StudentID\nWHERE Students.Name = 'Chacha';<\/code><\/pre>\n\n\n\niv. Courses where Latifah scored less than 67%:<\/h4>\n\n\n\n
SELECT CourseID \nFROM Grades\nJOIN Students ON Grades.StudentID = Students.StudentID\nWHERE Students.Name = 'Latifah' AND Grade < 67;<\/code><\/pre>\n\n\n\nv. Student performance in ascending order:<\/h4>\n\n\n\n
SELECT Students.Name, Grades.Grade \nFROM Grades\nJOIN Students ON Grades.StudentID = Students.StudentID\nORDER BY Grades.Grade ASC;<\/code><\/pre>\n\n\n\n