Section 1Multiple choice questions25 questions1 point per question
Circle the swers to each question. Unless indicated otherwise, one valid answer per question. Only 100 valid answers allow you to obtain the assigned point for each question, all other answers will be scored O.
Q1.1 What is a direct access on disk ?
A B C
We will read on the disk without going through the main memory We will read on the disk at a specific location
The tracks ofthe disk are scanned in a precise order
Q1.2 What is a random access on disk ?
A B C
We read somewhere randomly on the disk
We read either on the disk or ineRAM
We read on disk without takg into account the posion ofread heads
Q1.3 Whyisitbettertoreadacollectionofdatarecordswithsequentialaccessratherthanseveralrandom accesses?
A B C
Because its easier to program
Because we limit to a minimum the movement o f the read heads Because we will not miss any data
Q1.4 What is an index entry ?
A The first block ofthe index
B A record ofthe index, ofthe form key, address
C The value range covered by the keys ofthe same block
D The interval between two keys ofthe index
Ql.5 How many nondense indexes can be created on a data file? A Atmost1
B Exactly 1
C As many as we want
P a g e 2 o f 14
Ql.6 The indexing key of an index on a Book table is the pair title, year. What kind of filtering conditions in queries over the Book records can benefit from this index? several possible true answers
A B C D E F G
title On the road and year1958
title On the road
ye1958
title between Ground zero and Greys Anatomyyear between 2010 and 2016
title different from Greys Anatomy gee Science Fiction
Q1.7 What remains to be done after performing a search in an index file ?
A B C
Close the file
Display the result to the user
Access the data file with the address found in the index
Q1.8 The chaining ofthe leaves in a Btree indX is useful for
A B C
The procedure of expanding leaves
The search proced
Maintaining the overall consistency ofthe index tree
Q1.9 What is the disadvantage of an interval search with a Btree?
B C
It causes random nonsequential reads at the leaves level
It causes random nonsequential reads of blocks in the data file It causes a sequential scan of part of the data file
Ql.10 Ofthe following queries, which onsrequire a blocking operator in their execution plan? sevral possible ue answers
A Select title om Book
B Select distinct title om Book
C Select count titleom Book group by ye
D Select title om Book order by year
Ql.11 Assuming there is an index over the year attribute ofthe Book table, which ofthe following queries should have an execution plan combining the operators IndexScan and DirectAccess when we know the address of a record ? several possible true answers
A Select om Book
B Select om Book where year btween 2001 and 2010
C Select year om Book where title like A
Page 3 of 14
University No.
Ql.12 Which ofthe following queries can be evaluated with an execution plan that includes only the IndexScan operator? several possible true answers
A B C
Select title om Book where ye between 2001 and 2010
Select count om Book where year between 2001 and 2010 Select count titleom Book where year between 2001 and 20 IO
Ql.13 With a memory ofM blocks, what is the size of the fragments in the mergesort external sorting method?
A 1
B M
C M2
Ql.14 Why should the primy key of a table be indexed ? multiple possible ue answers
A Because most SQL queries focus on the value ofthe primary key
B To quickly check the uniqueness consaintwhen inserting
C To quickly check the referential integrity consaint when inserting a foreign key
D To quickly check the referential integrity constraint when removing a primary key
Consider the following Employee and Department tables: Empenum,name,dnum with 2 records
Deptdnum, dname with 1 record
Ql.15 For a join based on an index, how many index navigations should one perform? A 1
B 2 C 3
Q1.16 In the following query, can we apply the index nested loop join, where fand gare arbitra functions?
Select om Emp, Dept where f E.dnumg Dept.1um
A Yes B No
Page 4 of14
Ql.17 What isare the relationships between Crash Recovery CR and the transactional properties ? multiple possible ue answers
A CR guarantees that it is always possible to finalize a transaction
B CR guarantees that the updates ofa committed transaction commit always permanent
C CR guarantees that the updates of an aborted ansaction are always canceled
D CR guantees that, after recovery, each transaction execution is at the point where it W
before the crash
Ql.18 A ansaction T starting at tO and running in total isolation sees at a time t:
The state ofthe database at tO
A database instance copondg to the state at tO and the changes made by T up to the time t A database instance corresponding to e state at tO, the changes made by T up to the time t
A
B
C
and the changes made by any other transactions having committed at time t
Ql.19 Which statements about the content ofthe log are correct? multiple possible true answers A In the log, there is a commit statement for each completed ansaction
B Inthelthereisarollbackstatementforeachabortedtransaction
C The database image after a transaction is completed is in the log
D The database image before aansaction started is in the log
Here are the contents of a log file at the time of a crash, the oldest ansactions first, format of writes being write f olect, ol val, newval :
staTl
write Tl, x, 10, 20
commitTl checkpoint staT2
writeT2, y, 5, 10 staT4
writeT4, x, 20, 40 startT3
writeT3, z, 15, 30 writeT4, u, 100, 101 commitT4
write T2, x, 40, 60
Page 5 of 14
University No.
Ql.20 What is the list of validated ansactions ? A Tl
B T4,
C Tl,T2 D Tl,T4
Q1.21 What is in the list ofactive or canceled transactions ? A 1
B T4
C TI,T3 D T3
Q1.22 What are the values ofx, y, z, u after REDO ?
A x60,y10,z5,u100
B x60, ylO, z5, ulOl
C x40, y5, z15, ulOO
Q1.23 Why is 2PC unatactive at cloud scale ?
A Coordinator failure may cause unavailability
B The average latency for 2PC in a data center is a bottleneck
C The maximum latency for 2PC in a data center is a bottleneck
D1eaveragelatencyacrosstheglobefor2PCisabottleneck
Ql.24 Which ofthe following partitioning schemes works well for equality queries? multiple possible ue answers
ARange
B Hash
C Round Robin
Ql.25 Broadcast join is useful when one relation is
A
B
C
Very small
Lge, but highly skewed Partitioned on the join key
Page 6 of14
Section 2Detailedanswer questions20 questions
Q2. l 6 points Disk RAM accesses Assuming the memory specifications below
Memory type
Main memory dynamic RAM
Order of size GBs TBs
Random read latency
10lOOns
10210ms
Seq. read speed
10 GBs per sec
100 MBs per sec
Hard disk HDD
We have a database of 3GB consisting of 3,000 records.
Q2. l. l How long does it take to perfon a complete reading of this database from hard disk assuming a sequential reading is possible.
Q2. l .2 How long does it take to read this database om hard disk assuming a random access is required for each record?
Q2.l.3 We want to read 100 objects of 10 bytes each. How long does it take to read them if they are on hard disk?
Q2. l .4 We want to read 100 objects of 10 bytes each. How long does it take to read them if they are in RAM?
Page 7 of14
University No.
Q2.2 6 points Cache hit ratio With blocks o f size 4KB, we have a file o f I GB and a cache o f 100 MB, for which the replacement strategy is LRU.
Q2.2. l What is the hit ratio assuming that the probability o f needing and read the blocks is uniform?
Q2.2.2 Same question, assuming that 80 of the readings concern 200 :t, the remaining 20 being evenly distributed over the remaining 800 MB?
Q2.2.3 With the previous hypothesis, up to what cache size can we expect a significant improvement in hit ratio?
Q2.3. 3 points You have 4 buffer pages and your file has a total of 108 pages ofrecords to sort. How many passes and how many runs would it take to sort the file ?
Page 8 of 14
Q2.4 9 points We need to perform a join between the tables Book and Writer, on the common attribut writerld.
Q.2.4.1 What kind ofjoin is this?
Q 4.2 Assuming that there is no index on either table, we perform the join by the nested loop join NLJ oprator, while charging in memory part of a table. Explain how NLJ performs the join in general.
Q.2.4.3 The Book table consists of 1000 blocks, while the writer table consists of 10,000 blocks. It is assumed that the available RAM memory has size M252 blocks.
What would be the execution cost by taking the Writer table as the external table? explain the cost, not necessarily comping the exact number
Q2.4.4. What would be the execution cost by taking the Book table as the external table? explain the cost, not necessarily computing the exact number
Q2.4.5 That is yo conclusion after these two analysis ?
Page 9 of14
University No.
Q2.5 7 points Consider a relation RA, B, C with 1000 tuples. We have an index on attribute A with 50 unique values in the range 1, 50 and an index on B with I00 unique values in the range 1, 100.W e do not have an index on C. Use selectivity estimation to estimate the number of tuples produced by the following queries.
I. SELECTFROfR
2. SELECTFROMRWHEREA40
3. SELECT FROM R WHERE B40
4. SELECTFROMRIEREC40
5. SELECTFROM R WHERE A 25
6. SELECTFROM R WHERE C25
7. SELECT FROM R IERE A25 AND B25
Q2.6 4 points Conflict serializability Consider the following schedule:
Tl:
RA WA RB
T2:
T3: RC
Q.2.6.1 Draw the dependency graph precedence graph for the schedule.
Q2.6.2 Is this schedule conflict serializable? I f yes, what are all the conflict equivalent serial schedules? I f not, explain why?
WB RC WC WA WD
Page 10 of 14
Q.2.7 4 points Deadlocks Consider the following schedule. Draw the waitsfor graph and explain
whether or not there is a deadlock. Assume no locks are released throughout this schedule.
Tl: SA SD SB
T2: T3: T4:
XBX hu qu
XA
Q2.8 2 points What is the difference between a transformation and an action in Apache Spark?
Q2.9 2 points Explain the notion of lazy evaluation in Apache Spark.
Q2.10 4 points Cassandra Why can we say that documents in the Cassandra NoSQL system are self describing ?That are the pros and cons o f this modelisation aspect ?
Page 11 of 14
University No.
XB
q
Q2.1 l 3 points Cassandra What is the key difference between SQL and Cassandras CQL query language ? Give one example illusating this difference.
Q2.12 8 po Cassandra Assuming the tables created by the instructions below
CREATE TYPE person id text, lastname text, firstnne text, bdate int;
CREATE TABLE film id text, title text, yeargenre text, couny text, director ozenperson, actors set ozenperson, primary keyid;
Explain what happens after the execution ofeach the following CQL queries :
Q2.12. l select om person where id A;
Q2.12.2 select om film where year 1995;
Q2.12.3 selectfrom person order by id ;
Q2.12.4 select om person
where lastnameAffleck allow filtering;
Page 12 of14
Q2.12.5 create index on filmye;
select om film where ye1995;
Q2.13 2 points SPOFSPF: What does this acronym mean? Give an example ofa system without SPOF.
Q2.14 2 points Describe a situation where the use ofa NoSQL system seems suitable.
Q2.15 2 points Describe a situation where the use ofa NoSQL system seems unsuitable.
Q2.16 2 points What is the definition of scalability?
Page 13 of 14
University No.
Q2.17 2 points A system consisting ofn servers devotes 80 ofCPUs to an application; doubling the data size, we add n new servers, and we stay at 80 CPU. Is it enough to say at the system is scalable? Explain.
Q2.18 2 points What is the difference between horizontal scaling and horizontal partitiong?
Q2.19 2 points Assume that 50 ofthe records oftable T share the same value for some attribute X. Suppose we run a series of experiments, each time increasing the number of machines over which we hashpartition T, and measuring the time to complete a parallel scan ofT each time. Across experiments, how does depend the completion time o f the parallel scan onnumber o f machines used ?
Q2.20 3 points Explain what is weak consistency in NoSQL systems.
END OF PAPER
Page 14 of 14
Reviews
There are no reviews yet.