Question

4. The exams database: The following database contains multiple choice exam questions tables Questions and On Hons). Each question has a question Tert and each one of the options has and optid, which is an integer representing options A) (optid=1), B) (optid=2), C) (optid=3), etc. as well as the text of the option. The field correct is either Oor 1 and indicates which is the correct option for that particular question. courseThe exams have numbers 1, 2, 3, etc. Each student takes one or several exams for so we can have exam number 2 for course cid="198-336', as well as a grade. The actual questions that a student gets in a given exam is stored in Esram Questions, where gid, opted represent the questions and options in that particular exam. A given question (qid) appears in an exam a question number (anumberland each of the options appear as optNumber in the exam. The field studentAnswer indicates which option is selected by the student and will be used to compute his/her grade studentAnswer is either 0 or 1). . Students(sid name) • Courses(cid.cnmae) • Exam(sid,cid number grade, foreign key (sid) references Students, foreign key (cid) references Courses) • Questions(qid question Text) • Options(qid optid, option Text,correct, foreign key (qid) references Questions) . Exam Questions(sid,cid number gid.optid.cnumber.optNumber, student Answer foreign key (sid,cid, number) references Exam, foreign key (qid optid) references Options) To help you understand how this database is organized here is a simple sample instance: Students: sid name Alice 2 Bob Courses cid name 198:336 Databases 119:380 Zoology Options: qid optid Escam: sidcid number grade 1 198 336 1 2 198-3361 option Text fish correct Questions: qid question Text 1 Animal that has four legs 2 What is data? 3 Which is a reptile? 4 What does DBMS stand for? octopus facts knowledge frog rat bear Db Manipulation Storage Database Mgmt System Det binary Module 3

Answer 1

(a) sid:2 name:Bob cid: 198:336 exam no.:1 cname: Databases

(b) select sid,name,grade

from Students,Exam

where cid=119:80 and number=1

and Students.sid=Exam.sid;

(c) select sid,name,Options.correct*ExamQuestions.studentAnswer as nCorrect

from Students,Options,ExamQuestions

where ExamQuestions.number=2 and ExamQuestions.cid=198:336

and Options.qid=ExamQuestions.qid

and Options.optid=ExamQuestions.optid

and Students.sid=ExamQuestions.sid;

(d) select distinct count(qNumber),sid,name

from Students,ExamQuestions

where Students.sid=ExamQuestions.sid

and cid=198:336 and number=2;