首页
网站开发
桌面应用
管理软件
微信开发
App开发
嵌入式软件
工具软件
数据采集与分析
其他
首页
>
> 详细
CS 2550代做、SQL程序语言代写
项目预算:
开发周期:
发布时间:
要求地区:
CS 2550 – Principles of Database Systems (Spring 2024)
Dept. of Computer Science, University of Pittsburgh
Assignment #4: Query Processing & Optimization
Release: April 10, 2024 Due: 8:00PM, April 22, 2024
Goal
The goal of this assignment is to better understand the query evaluation process.
Description
The following is a fragment of the university database schema.
Employee (EmployeeID, First, Last, Gender, DOB, Address, Phone, SNO, Salary)
PK(EmployeeID)
FK(SNO) --> Studio(StudioNum)
Studio (StudioNum, StudioName, Manager, Budget)
PK(StudioNum)
FK(Manager) --> Employee(EmployeeID)
Movie (movieID, producedBy, title, Budget, filmedAt)
PK(movieID)
FK(producedBy) --> Studio(StudioNum)
Consider four possible organizations of the tables Employee(E)-Studio(S)-Movie(M):
1. Tables E, S and M are heap files & no access methods exist on any of them
2. Tables E and S are sorted files and M is a heap file & no access methods exist on any of them
3. Tables E, S and M are heap files & access methods (hashing) exist only on M
4. Table E is a sorted file and tables S and M are heap files & access methods exist on E and
M, one of which is hash and the other is index
Further assume the following statistics:
Available Cache Memory (CM) to be 22 pages.
Attributes belonging to the same table all have the same average size.
When using hash join, the smaller record will be the partitioning part.
The distribution of values for the attribute filmedAt in the table Movie is: 50% ’PGH’, 30%
’PHI’, and 20% ’NYC’.
Movie: r = 500, bfr = 5, BP rj = 100.
Studio: r = 250, bfr = 5, BDep = 50.
Employee: r = 1250, bfr = 5, BEmp = 250.
Note that r is the total number of tuples in a table, bfr is the blocking-factor, and B is the
total number of blocks of a table.
Questions [100 points]
Q1 [20 points] Produce the heuristic query evaluation tree of the following SQL statement assuming no access structures available, and describe in details how the evaluation of the query would
be:
SELECT S.StudioName, COUNT(M.movieID), SUM(M.Budget)
FROM EMPLOYEE AS E, Studio AS S, Movie AS M
WHERE M.producedBy = S.StudioNum AND E.SNO = S.StudioNum
GROUP BY S.StudioName
HAVING COUNT(E.EmployeeID) < 15;
State any additional assumptions. (Hint: some operators can be combined into a single algorithm).
Q2 [80 points] Consider the query evaluation tree as discussed in class of the following SQL
statement.
SELECT M.movieID, M.producedBy, E.First, E.Last, E.Gender
FROM Employee AS E, Studio AS S, Movie AS M
WHERE M.producedBy = S.StudioNum AND S.Manager = E.EmployeeID
AND M.filmedAt=‘PGH’;
Based on your pittID, you have two database organizations assigned to you from the above organizations in order to answer this question. For those two organizations, identify sub trees that
represent groups of operations which can be executed by a single algorithm. For each such sub tree,
there are potentially multiple methods that can be combined to implement it as a single algorithm.
Please provide the following:
The description of one such implementation (steps & data structures) out of all possible ones
for each of the above cases.
The I/O cost of your implementation at each level.
Compute the total I/O cost.
You are asked to answer the question for two database organizations, those are based on your
pittID, as follows:
ddh32, tiw81, yiy161, dhb51, yul251, sml153, nia135, dtk28:
You are required to do the first and second organizations only.
mas937, yux85, anp407, shk148, nat134, aba166, mod53, jeb386:
You are required to do the first and third organizations only.
aym50, bsp22, yuw328, shy158, btl26, shp184, jah292, tbt8:
You are required to do the first and fourth organizations only.
What & How to submit
You are required to submit exactly one PDF file under your pitt user name (e.g.,
pitt01.pdf). In addition to providing the answer, you are expected to: include your
name and pitt ID at the top of the PDF file.
After preparing your solution, submit your file (i.e., the prepared PDF) that contains
your solution by going to the class’ Gradescope by either navigating to the Gradescope
Homepage and selecting the course CS 2550 from the Course Dashboard or by clicking
the Gradescope Navigation option under our course Canvas page.
You must submit your assignment before the due date (8:00 PM, Apr. 22, 2024).
There are no late submissions.
Note that you are required to use a graph tool (such as MS-Word, MS Powerpoint, MS
Visio, idraw, draw.io, etc.) to generate your diagrams and the text that explain them.
Handwritten/Hand-drawn diagrams and/or text will not be accepted/graded
and will receive a zero. Hand-written and digitized/scanned images will
receive a zero (including scanned, photos, or electronically drawn using a
smart pen submissions).
Academic Honesty
The work in this assignment is to be done independently. Discussions with other students on
the assignment should be limited to understanding the statement of the problem. Cheating
in any way, including giving your work to someone else will result in an F for the course and
a report to the appropriate University authority.
软件开发、广告设计客服
QQ:99515681
邮箱:99515681@qq.com
工作时间:8:00-23:00
微信:codinghelp
热点项目
更多
cis432代做、代写python/java程...
2024-05-04
eeen3007j代写、c++程序设计代...
2024-05-04
代写data程序、代做c/c++, jav...
2024-05-04
comp2006代做、代写c++程序语言
2024-05-04
comp26020代做、java/c++设计编...
2024-05-04
csci251 advanced programming...
2024-05-03
cs 6290: high-performance co...
2024-05-03
assignment 2: executing and ...
2024-05-03
ecse427/comp310 programmin...
2024-05-03
cs 452 (fall 22): operating...
2024-05-03
comp9414 23t2 assignment 2 ...
2024-05-03
dpst1091 23t1 assignment 2 ...
2024-05-03
program代做、代写python设计编...
2024-05-03
热点标签
finm8007
comp2006
comp26020
comp1721
eeen3007j
cis432
csci251
comp5125m
com398sust
32022
mth6158
comp328
finn41615
2024
mec302
mgmt3004
mgt7158
com160
as.640.440
econ3016
finm7405
econ7021
fin600
infs4205/7205
mktg2510-
f27sb
csse2310/csse7231
rv32i
eecs 113
comp1117b
cs 412
comp 315
econ7300
comp2017
ecs 116
fit5046
com6511
comp30024
acs341
econ1020
isys3014
acc408
comp1047
csc 256
cs 6347
finm7008
comp34212
csmde21
estr2520
comp285/comp220
mds5130/iba6205
finc6010
is3s665
busi2194
125.785
iom209
msin0041
econ339
cmt218
mast10007
comp5349
ecx2953/ecx5953
bios706
comp3310
mth6150
comp30027
comp20005
eec286
busi2211
bff2401
fnce90046
visu2001
mang6554
finc6001
125785
data423-24s1
engi 1331
fint2100
(520|600).666
can202
cs 61b
mast20029
info20003
stat512
econ3208
cmpsc311
engg1340
ecmt1010
fit5216
basc0003
ee3121
acct2002
comp5313
busi2131
ise529
elec372/472
csit940/csit440
cenv6141
comp3027/comp3927
ftec5580
comp1433
msci223
mark203
en3098
eden1000
ece6483
econ4410
mats16302
cs 6476
com6521
comp222
comp3211
comp10002
csc1002
chc6186
cs 161
comp27112
comp282
swen20003
comm1190
elec9764
acfi3308
acct7101
fin6035
comp2048
geog0163
comp2013
coen 146
dts101tc
sehh2042
comp30023
comp4880/8880
cs 455
07
stat0045.
fil-30023
celen085
psyc40005
math40082
are271
comp9311
ee5311
imse2113
comp 2322
acct2102
fnd109
int102
is3s664
is6153
data4000
accfin5034
fit5212
cs536-s24
fit5225
ecos3006
mes202tc
finc5001
stat3061
csc171
cs1b
7ssmm712
bu.450.760
cs170
comp3411
swen90004
cpt206
comp5313/comp4313—large
bl5611
kxo206
comp532
elec207
kxo151
cs 2820
cpt108
math2319
dts204tc
qm222
comp2511
ccs599
infs1001
mat2355
eeee4123
25721
ifn647
pols0010
hpm 573
qbus6860
comp9417
csci 1100
stat0023
cse340
comp2003j
cs 2550
cs360
fin 3080
ierg 4080
cs6238
cit 594
finm7406
hw6
联系我们
- QQ: 9951568
© 2021
www.rj363.com
软件定制开发网!