博客

计算机网课代修 – Query Processing代写 – assignment代写 – COMP207
计算机网课代修

计算机网课代修 – Query Processing代写 – assignment代写 – COMP207

COMP207 Assignment 2

Query Processing

 

 

计算机网课代修 This is the second of two assignments for COMP207. It is worth 10% of the total marks for this module. It consists of four questions, which ···

 

About This Assignment  计算机网课代修

This is the second of two assignments for COMP207. It is worth 10% of the total marks for this module. It consists of four questions, which you can find at the end of this document.

Submit your solutions to these questions in PDF format by the given submission deadline. Your solutions must be submitted on Vital (see the detailed submission instructions below).

Accuracy and relevance are more important in your answers, so don’t write large volumes in your submission, but do ensure that what you write covers what is asked for and keeps to the problem statement.

Submission Details  计算机网课代修

Please submit one PDF file with your solutions. Name your file as follows:

<your student ID>-Assignment-2.pdf

If your student ID is 12345678, then your file should be named: 12345678-Assignment-2.pdf.

Please submit only this file (no archives).

To act as your ‘signature’ for the assignment, at the top of your PDF document put your Student ID number.

Your solutions must be submitted on Vital (see Vital for submission instructions).

The submission deadline for this assignment is Tuesday, 03 December 2019, 17:00. Earlier submission is possible, but any submission after the deadline attracts the standard lateness penalties. Plagiarism and collusion guidelines will apply throughout the assignment submission. For details on late submissions, how to claim extenuating circumstances, etc., please see the undergraduate student handbook, which can be found at http://intranet.csc.liv.ac.uk/student/ug-handbook.pdf , or in Section 6 of the Code of Practice on Assessment.1

Question 1 (10 marks)  计算机网课代修

The following tables form part of a hotel booking database held in a relational DBMS (primary keys are underlined):

Hotel (hotelNo, hotelName, city)

Room (roomNo, hotelNo, type, price)

Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)

Guest (guestNo, guestName, guestAddress)

  • Hotelcontains hotel details and hotelNo is the primary
  • Room contains room details for each hotel and (roomNo, hotelNo) forms the primary
  • Booking contains details of the bookings and (hotelNo, guestNo, dateFrom) forms theprimary key.
  • Guestcontains guest details and guestNo is the primary

Give the relational algebra expressions to return the results for the following two queries:

  • Listthe names and cities of those hotels who charge more than £85 for a  (5 marks)
  • List the names and addresses of guests who have made a booking to stay Christmas Day (5 marks)

 

 Question 2 (10 marks)  计算机网课代修

Consider the following database schema and example instance for a property management system:

Hints:

  • Attributeswith a grey background form the primary key of a relation (e.g, pId for relation

property).

  • Theattribute pId of relation repairs is a foreign key to relation property.

Give the relational algebra expressions to return the results for the following two queries:

  • Get the pId, owner and location details of all properties that are larger than 900 square feet(sqrFeet). (5 marks)
  • Get the names of repair companies (company) that did a repair on a property in Hyde (5 marks)

Question 3 (20 marks)  计算机网课代修

  • Considerthe following relation:

studentCourses(StudentID, CourseNo, Quarter, Year, Units, Grade)

The relation contains the grades for the courses completed by students. Assume that in studentCourses there are 200,000 different students, each identified by their StudentID. On average, a student took 40 different courses.

If the file blocks hold 2000 bytes and each studentCourses tuple requires 50 bytes, how many blocks will then be needed to store the relation studentCourses? (5 marks)

  • Adatabase includes two relations Student (S) and Program (P).

  • Translatethe following relational algebra into SQL:πstudId,lName(Ϭ course=’BSc’(STUDENT)) (5 marks)
  • Giventhese relations, write the SQL statement that produced the equivalent queries below:

Course (courseNo, courseDept, courseLeader)计算机网课代修

Student (studNo, name, type, tutorId, courseNo)

Two sample equivalent corresponding queries have been produced:

πstudno,name(Ϭ(type=’undergrad’)^(courseDept=’CompSci’)(Student⋈s.courseNo=c.courseNo Course)) and

πstudno,name(Ϭ type=’undergrad’(Student))⋈s.courseNo=c.courseNo(Ϭ courseDept=’Comp Sci’(Course))

(5 marks)

Question 4 (60 marks)  计算机网课代修

Consider a database with relations R(A, B,C), S(D, E), and T (F, G).

  • Give the initial query plan (constructed as in Lecture 13) for the SQL querySELECT B, E, G

FROM R, S, T

WHERE A = 10 AND C = D AND E = F AND A > G;

Then use the heuristics from Lecture 16 to transform the initial query plan into an optimised (logical) query plan. Perform the transformation step-wise, pushing a single operator over a single operator in each step, and indicate the heuristics you apply. (20 marks)

  • Supposethat
    • |R|= 1000, |πA(R)| = 1000, |πB(R)| = 100, |πC(R)| = 500;
    • |S|= 5000, |πD(S)| = 300, |πE (S)| = 10;
    • |T| = 4000, |πF (T )| = 4000, |πG(T )| =

Estimate the number of tuples returned by the following queries. Explain your calculations.

  1. σA=10(R) (6marks)
  2. σA=10OR B=b(R) (6 marks)

  • R⋈C=DS (6 marks) 计算机网课代修

  • Supposethat in addition to the assumptions on R, S, and T from part (ii), we also have the following:
    • Eachdisk block can hold up to 10
    • Allrelations are stored in consecutive blocks on
    • Noindexes are

What is the best physical query plan (in terms of the number of disk access operations) you can find for σB=b AND E=100(R ⋈C=D S)? Describe your plan and show the calculation of the number of disk access operations.

 

更多代写：留学生CS代写 留学考试代考   留学网课代修 美国essay代写 美国英国代考 作业代写

合作平台：天才代写 幽灵代写  写手招聘  paper代写

发表回复