共计 9165 个字符，预计需要花费 23 分钟才能阅读完成。
CISC 332/CMPE 332 –Database Management Systems
Quiz #2
April 4, 2018
No questions will be answered during the quiz.
There are several versions of the quiz. Do not rely on your neighbours’ choices – they may not be correct for your version!
Many of the questions on this exam are multiple choice. For each of these questions you can indicate two responses. The primary response will be awarded more points than the secondary response. You may provide only one (1) response per row. If you mark more than one response in a row, that row will receive zero (0) points.
If your primary and secondary responses are the same, and both are correct, you will receive the sum (the maximum number of points). This lets you indicate your confidence in your response, with a less confident response receiving less than the maximum number of points.
If you think that none of the responses provided for the question are correct, or that the question is unclear or ambiguous, you should make a reasonable interpretation and state what you have assumed. You must then write down the answer that you feel is correct.
Example
This is the sample question.
1)possible answer 1
2)possible answer 2
3)possible answer 3
4)possible answer 4
You are quite certain that the answer is 2. However, you have some doubts and think the next best choice is 1. You choose 2 for your primary answer and 1 for your secondary answer. The correct answer is 2. You get 4/6 marks. If choice 1 had been the correct answer, you would have received 2/6 marks. If you had chosen 2 for both your primary and secondary answer, you would get 6/6.
NOTE: Missing parenthesis, capitalization, quotes (or lack of) are not to be considered errors. A query is not considered correct if it works ONLY with the data sample that is provided. It must work in the general case.
Question 1: [10 marks] These True/False questions test your general knowledge of databases covered in the course. Make only ONE choice for each statement. Each question is worth one mark.
True FalseA heap file organization maintains records sorted in order based on the primary key of each record.
The primary factor affecting the speed of database access is the size of the main memory allocated to the database management system.
Given the database schema on page 10, a primary clustered index on ID on the Student table and a secondary clustered index on the LName field of the student table could be created to improve the efficiency of accesses to both fields.
A hash index improves the efficiency of a table scan.
All views are updateable.
One way to prevent an SQL injection attack is by using prepared statements. When using a prepared statement, the access plan is generated and stored for a query and is accessed when the query is run.
A B+ tree index is efficient for range queries.
Views can be defined on one or a combination of tables in order to shield data from being accessed by a particular user or group of users.
The idea of a check constraint is to restrict values entered for a particular attribute.
Either the mysql API or PHP Data Objects (PDOs) can be used to interface with a database management system (DBMS). PDOs are advantageous over the mysql API in that they provide the capability to access any type of database management system (eg. MySQL, DB2, Postgresql or Oracle) with little to no modification to the code.
Question 2: [6 marks] Consider the following SQL script (it contains no intentional errors):
create table abc (
i integer,
j integer,
primary key (i)
);
create table xyz (
p integer,
s integer,
primary key (p),
foreign key (s) references abc(i) on delete cascade
);
insert into abc values (10, 20), (30, 40);
insert into xyz values (14, 10), (25, 10), (29, 30);
delete from abc where i = 10;
After the deletion shown above, the tuples (p, s) in the table xyz are:
A.(29, 30)
B.(14, 10), (25, 10), (29, 30)
C.(25, 10), (29, 30)
D. (14, null), (25, null), (29, 30)
Primary Answer A B _ C D _ (4 marks)
Secondary Answer A______ B _ C D_______ (2 marks)
Question 3: [6 marks] Consider the following permissions granted on a table called “professor”. Assume that all user ids (UserA, UserB, UserC) referenced have valid user accounts. UserA is the creator of the professor table. There are no intentional syntax errors.
UserA issues:
grant select on professor to UserB with grant option;
UserB issues:
grant select on professor to UserC with grant option;UserC issues:
grant select on professor to UserD with grant option;UserA issues:
revoke select on professor from UserB, UserC
Which of the following statements is True after the above commands are executed? (Choose only one).
A)UserD and UserA can both select data from the professor table.
B)UserD can select data from the professor table but UserB and UserC cannot.
C)UserA may not revoke the privileges from UserC since UserC has granted permission for UserD to select from the professor table.
D)UserA is the only one with permission to select from the professor table.
Primary Answer A B _ C D _ (4 marks)
Secondary Answer A______ B _ C D_______ (2 marks)
Question 4 [6 marks] Which of the following SQL queries finds the names of the professor(s) who have taught any course taken by Howard PuffNStuff. This query uses the database found on the last page of the test.
A.
select P.name
from Professor P join CoursesTaken C join Student S join Section SC
where S.FName = “Howard” and S.LName = “PuffNStuff”
B.
select P.name
from Professor P, CoursesTaken C, Student S, Section SC
where P.ID=SC.ProfID
and S.ID = C.StudentID
and S.FName = “Howard”
and S.LName = “PuffNStuff”
and C.Course=SC.Course
and C.Section = SC.Section
C.
select P.name
from Professor P, CoursesTaken C, Student S, Section SC
where P.ID=SC.Prof
and S.ID = C.StudentID
and S.FName = “Howard”
and S.LName = “PuffNStuff”
D.
select P.name
from Professor P join CoursesTaken C join Student S join Section SC
where S.ID = “01”
Primary Answer A B _ C D _ (4 marks)
Secondary Answer A______ B _ C D_______ (2 marks)
Question 5: [6 marks] This query uses the database found on the last page of the test.
Find students who took 332 in the Fall but who did not take 432 in the Winter term.
A.
select studentid
from coursestaken
where course = 332 AND section = “Fall” and
studentid not in (select studentid from coursestaken where course = 432 and section = “Winter”)
B.
select studentid
from coursestaken
where course = 332 AND section = “Fall” and
studentid in (select studentid from coursestaken where course = 432 and section = “Winter”)
C.
select studentid
from coursestaken
where (course = 332 AND section = “Fall”) and not(course = 432 and section = “Winter”)
D.
select studentid
from coursestaken
where course = 332 and section = “Fall” or not(course = 432 and section = “Winter”)
Primary Answer A B _ C D _ (4 marks)
Secondary Answer A______ B _ C D_______ (2 marks)
Question 6: [6 marks] List the courses that have an average enrolment (across all sections) of more than 75 students and a minimum mark of greater than 75. This query uses the database found on the last page of the test.
A.
select Section.course
from Section S, CoursesTaken C
group by S.course, S.section
having avg(enroll) > 75 and min(Mark) > 75
B.
select Section.course
from Section S, CoursesTaken C
group by C.course, S.section
where avg(enroll) > 75 and min(Mark) > 75
C.
select Section.course, avg(enroll) > 75, min(Mark) > 75
from Section S, CoursesTaken C
group by S.course, S.Section
D.
select Section.course
from Section S, CoursesTaken C
group by S.course
having avg(enroll) > 75 and min(Mark) > 75
Primary Answer A B _ C D _ (4 marks)
Secondary Answer A______ B _ C D_______ (2 marks)
Question 7: [6 marks] There are no intentional syntax errors in the following code.
<!DOCTYPE html>
<html>
<body>
| Student | Course | Mark |
|---|---|---|
| “.$row[0].”</td><td>”.$row[1].” | “.$row[2].” |
“;
$new = $dbh->query(“select studentID, avg(mark) from coursesTaken group by StudentID”);
foreach($new as $row) {
if ($row[1] > 90) {
echo “<p>”.$row[0].” is a great student”; }}
$dbh = null;>
</body>
</html>
Choose the best description of what this code does:
A.Creates a web page with a 1st level heading that reads “Student/Course/Mark”. Lists the students by first name, course name and their mark in each course. Then prints, for each student who has an average of over 90%, that they (listing the student name) are a great student.
B.Creates a web page that lists the first name, the course id and the mark for each student in all courses where the student achieved over 90%. For each of these students, a line is printed that outputs the student name and then ” is a great student”.
C.Creates a web page that lists the first name, the course id and the mark for each student in all courses in a tabular format. For each student who has an average of over 90% in all courses, a line is printed that outputs the student’s id and then ” is a great student”.
D.Creates a web page that lists the first name, the course id and the mark for each student in all courses. For each student who has an mark over 90%, a line is printed that outputs the student name and then ” is a great student”.
Primary Answer A B _ C D _ (4 marks)
Secondary Answer A______ B _ C D_______ (2 marks)
Question 8: [6 marks] This question uses the database on the last page of the quiz.
create view temp (sid, first, last, numCourses) as
select id, fname, lname, count(*) as numCourses
from student, coursesTaken
where studentId=id
group by id, fname, lname
order by numCourses, lname;
select * from temp;
Which one of the following most closely resembles the output of the select statement?
A.
sid first last numCourses
01 Howard PuffNStuff 2
02 Joan Rivers 2
05 Mark Anthony 1
03 Peter Rabbit 1
04 Tina Turner 1
B.
sid first last numCourses
05 Mark Anthony 1
03 Peter Rabbit 1
04 Tina Turner 1
01 Howard PuffNStuff 2
02 Joan Rivers 2
C.
sid first last numCourses
05 Mark Anthony 1
01 Howard PuffNStuff 2
03 Peter Rabbit 2
02 Joan Rivers 2
04 Tina Turner 2
D.Querying a view is not permitted in SQL.
Primary Answer A B _ C D _ (4 marks)
Secondary Answer A______ B _ C D_______ (2 marks)
Course Database
The following is a relational schema (with partial data) for a Course database. You may detach this page from the quiz for reference. You may discard it at the end of the quiz.
Course
Course Section ProfID Enroll
332 Fall 001 100
332 Winter 002 100
432 Winter 003 75
422 Fall 001 49
422 Winter 001 100
Course CName Credit
332 Database Systems 0.5
432 Advanced Database Systems 0.5
422 Software Systems 1.0
ID FName LName
01 Howard PuffNStuff
02 Joan Rivers
03 Peter Rabbit
04 Tina Turner
05 Mark Anthony
06 Guy WhoTakesNoCourse
Course StudentID Section Mark
332 01 Fall 92
332 02 Fall 88
332 03 Winter 99
332 04 Winter 69
432 01 Winter 90
432 03 Winter 88
432 05 Winter 67
422 02 Winter 98
422 04 Winter null
PID Name
001 Meijer
002 Powley
003 Martin
Professor
WX：codehelp
