CISC 332*代写、代做Database留学生、代写SQL程序、SQL编程调试

日期：2019-04-04 11:42

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.

TrueFalse

A 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>

<table>

<tr><th>Student</th><th>Course</th><th>Mark</th></tr>

<php

$dbh = new PDO('mysql:host=localhost;dbname=universityDB', "root", "");

$rows = $dbh->query("select fname, course, mark from student, coursesTaken where studentID = ID");

foreach($rows as $row) {

   echo "<tr><td>".$row[0]."</td><td>".$row[1]."</td><td>".$row[2]."</td></tr>";

}

echo "</table>";

$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.

sidfirstlastnumCourses

01Howard PuffNStuff2

02JoanRivers2

05Mark Anthony1

03PeterRabbit1

04Tina Turner1

B.

sidfirstlastnumCourses

05Mark Anthony1

03PeterRabbit1

04Tina Turner1

01Howard PuffNStuff2

02JoanRivers2

C.

sidfirstlastnumCourses

05Mark Anthony1

01Howard PuffNStuff2

03PeterRabbit2

02JoanRivers2

04Tina Turner2

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

CourseSectionProfIDEnroll

332Fall001100

332Winter002100

432Winter00375

422Fall00149

422Winter001100

CourseCNameCredit

332Database Systems0.5

432Advanced Database Systems0.5

422Software Systems1.0

IDFNameLName

01HowardPuffNStuff

02JoanRivers

03PeterRabbit

04TinaTurner

05MarkAnthony

06GuyWhoTakesNoCourse

CourseStudentIDSectionMark

33201Fall92

33202Fall88

33203Winter99

33204Winter69

43201Winter90

43203Winter88

43205Winter67

42202Winter98

42204Winternull



PIDName

001Meijer

002Powley

003Martin

Professor