2020/12/4 Practice Exam Quiz: Database Systems (INFO20003_2020_SM2)
Practice Exam Quiz
Due Dec 1 at 20:00 Points 132 Questions 39 Available Oct 1 at 8:00 – Dec 1 at 20:00 2 months
Time Limit None Allowed Attempts Unlimited Instructions
INFO20003 Practice Exam
This is a practice exam which is representative of the types of questions / answer formats you might encounter in the final exam.
This practice exam has been set to have unlimited time, however in the real exam you would have 180 minutes + 15 mins for uploads. We recommend taking this quiz under timed conditions once you have revised the subject material, as it will give you a good indication of your preparedness for the exam. There is another ‘sample exam’ in the modules section which you might consider using for earlier study / practice, and then make use of this quiz when you are further into your study.
Answers will be immediately given for the questions which are automatically marked (eg multichoice), and answers to the other questions are available in the modules section of the LMS.
Note that there are several questions which require an answer to be attached as a scan/screenshot. You may scan all of the working for these questions together into one image/document if this is easier than scanning each question individually. Please upload this combined document at the top of the exam (in question 1).
This quiz was locked Dec 1 at 20:00. Attempt History
Attempt
KEPT Attempt 3
LATEST Attempt 3
Attempt 2
Attempt 1
Time
18,544 minutes
18,544 minutes
2 minutes
1,421 minutes
Score
0 out of 132 * 0 out of 132 * 0 out of 132 * 0 out of 132 *
* Some questions not yet graded
Submitted Dec 1 at 20:00
Unanswered
Question 1
Not yet graded / 0 pts
There are several questions which require an answer to be attached as a scan/screenshot. Instead of scanning/uploading a document for each question, you may scan all of the working for these questions together into one image/document and attach it below if you prefer.
The following questions should be included: – Modelling
– Relational Algebra [ 2 x questions ]
– Data Warehousing Modelling
Unanswered
Question 2
0 / 2 pts
Which of the following is TRUE?
Use of a search index can only reduce cost of an SQL query when there is a ¡®where¡¯ clause as part of this query.
https://canvas.lms.unimelb.edu.au/courses/8200/quizzes/91343
1/13
2020/12/4 Practice Exam Quiz: Database Systems (INFO20003_2020_SM2)
Correct Answer
Unanswered
Transactions and ACID compliance are only beneficial when there are multiple concurrent users of a database SELECT is the SQL equivalent of Projection (Pi) in Relational Algebra
A data warehouse star schema is always in third normal form
Which of the following is a correct description of a distributed database?
There are always many database management systems (DBMS) storing the data, which are connected together Three are separate and unconnected instances of the database in many locations
There are several logical databases, which are physically split across many locations but all connected There is only one logical database, which is physically split across many locations but all connected
Which of the following is FALSE?
Right-deep query plans are not evaluated because there is always an equivalent left-deep plan
Pipelining is the direct ‘streaming’ in memory of the output of one operation (eg a join) as the input of another operation (eg another join) without writing the output to disk
Pipelining is the technical term for the use of a left-deep query plan instead of a right-deep query plan Left and right deep plans both allow for efficient use of pipelining
Which of the following is the best description of a transaction (in the context of a relational database management system)?
A grouping of SQL statements which must be entirely completed or aborted
An SQL statement, such as an UPDATE of a row, which must be durable (changes to the row are never lost)
Prevents concurrent access to the database by restricting usage to a single individual at a time, thus preserving data integrity
Question 3
0 / 2 pts
Correct Answer
Unanswered
Question 4
0 / 2 pts
Correct Answer
Unanswered
Correct Answer
Question 5
0 / 2 pts
Any change made to the database state
https://canvas.lms.unimelb.edu.au/courses/8200/quizzes/91343
2/13
2020/12/4 Practice Exam Quiz: Database Systems (INFO20003_2020_SM2)
Question 6
0 / 2 pts
A database has a table ‘customer’ with primary key ‘id’.
For each of the usage scenarios of this table, select the BEST configuration for file organisation + index:
Data frequently inserted into the table. The following query is run frequently : SELECT * FROM customer
Correct Answer
Correct Answer
Correct Answer
Heap file organisation with no indexes
Sorted file organisation with a clustered index on id
Heap file organisation with an unclustered index on ID
Data rarely inserted into table. The following type of query is run frequently: SELECT * FROM customer WHERE id > 200
Data is frequently inserted into the table. The following query is run regularly: SELECT * FROM customer WHERE id = 200;
Unanswered
You Answered
You Answered
You Answered
Unanswered
Question 7
0 / 2 pts
Correct Answer
Unanswered
The inconsistent retrieval problem is primarily the result of the absence of which ACID property?
Atomicity Durability Consistency Isolation
Question 8
Not yet graded / 15 pts
A record label company wants to upgrade its albums database and it hires you to do the job. The relevant information is as follows:
They want to keep information about singers, their names (which is unique), active since, whether it’s a band or not.
For each album, the singer (band), the year it was recorded, its unique title, its cover photo, and its price must be stored.
https://canvas.lms.unimelb.edu.au/courses/8200/quizzes/91343
3/13
2020/12/4 Practice Exam Quiz: Database Systems (INFO20003_2020_SM2)
Question 9
Not yet graded / 8 pts
Write the CREATE TABLE statements resulting from the following conceptual model:
Be sure to specify primary and foreign keys. You MUST specify whether the fields are NOT NULL, and whether a UNIQUE constraint is needed. Where the data type is not obvious, feel free to choose an appropriate data type.
Your Answer:
Unanswered
Genres and their hierarchy are also stored. Some genres have emerged from others. For example, a genre can be the parent of 0 or many genres, and another genre can be the child of 0 or 1 genres. Each genre is identified by a unique name.
A given album may belong to more than one genre, which could be at any level in the hierarchy.
Songs of each album are to be stored. For each song, the company stores its title and duration, and the album/albums it belongs to, i.e., all songs must be in some album, but can be in many albums.
Finally, the record company keeps information about the managers. For each manager, the company keeps the name, address, the total amount of dollars owed. Managers can manage multiple artists and genres. We still store the managers even if they are no longer managing genres/artists.
Draw a conceptual model using Chen’s notation for the record label company. Be sure to indicate the various attributes of each entity and relationship set; also specify the key and participation constraints for each relationship set. Be sure to write down any assumptions you make.
Remember you may upload the scan of this question below, or combine and scan all handwritten questions together (see the top of the exam, question 1)
Australia has carried out several censuses. The following diagram shows the tables corresponding to preferred means of transportation, residency location, and origins and destinations of the frequent trips.
https://canvas.lms.unimelb.edu.au/courses/8200/quizzes/91343
4/13
2020/12/4 Practice Exam Quiz: Database Systems (INFO20003_2020_SM2)
Question 10
Not yet graded / 2 pts
How many different people, across all censuses, have said they prefer public transport? Your Answer:
Unanswered
Unanswered
Unanswered
Unanswered
Question 11
Not yet graded / 3 pts
List the name of the suburbs and the number of people who lives in each, according to the 2016 census, ordered from the most to the least populated.
Your Answer:
Question 12
Not yet graded / 4 pts
How many different people, according to the 2016 census, frequently travels from Brunswick to a suburb other than Epping and Port Melbourne?
Your Answer:
Write a single SQL statement to correctly answer each of the following questions. DO NOT USE VIEWS or VARIABLES to answer questions. Query nesting is allowed.
Question 13
Not yet graded / 6 pts
Of people who prefer public transport, from which suburb do the largest number of different people make frequent trips, according to the 2016 census?
https://canvas.lms.unimelb.edu.au/courses/8200/quizzes/91343
5/13
2020/12/4 Practice Exam Quiz: Database Systems (INFO20003_2020_SM2)
Based on the same diagram above (i.e., Australia censuses), write a relational algebra expression for each of the following questions:
Remember you may upload the scan of these questions below, or combine and scan all handwritten questions together (see top of the exam, question 1)
Question 14
Not yet graded / 2 pts
List the DOB of people whose preferred means of transportation is public transport, according to the 2016 census.
Unanswered
Unanswered
Unanswered
Question 15
Not yet graded / 3 pts
List the DOB of people who live in ‘Brunswick’ and prefer public transport, according to the 2016 census.
Question 16
Not yet graded / 6 pts
Consider two relations called Customer and DocumentHeader. Imagine that the relation Customer has 250 pages and DocumentHeader has 1000 pages. Consider the following SQL statement:
There are 502 buffer pages available in memory. Both relations are stored as simple heap files. Neither relation has any indexes built on it.
Evaluate block-oriented NLJ, SMJ and HJ using the number of disk I/O’s as the cost. Provide the formulae you use to calculate your cost estimates.
Which join would be the optimizer¡¯s choice? Consider Customer as the outer relation in all alternatives. Assume that sorting can be performed in two passes for both relations. All selections are performed on-the- fly after the join.
Your Answer:
SELECT *
FROM Customer LEFT JOIN DocumentHeader
ON Customer.CID = DocumentHeader.CID
WHERE Customer.CName = ‘ACME’;
Unanswered
Your Answer:
Question 17
Not yet graded / 2 pts
Would the cost of Sort-Merge Join have changed if we would have had an unclustered B+ tree index created on the attribute in the WHERE condition? If yes, explain how (no need to recalculate). If no, explain why.
Your Answer:
https://canvas.lms.unimelb.edu.au/courses/8200/quizzes/91343
6/13
2020/12/4 Practice Exam Quiz: Database Systems (INFO20003_2020_SM2)
https://canvas.lms.unimelb.edu.au/courses/8200/quizzes/91343
7/13
Consider a relation called Loans that stores information about loans given by a bank. Imagine that the relation Loans consists of 800 pages and each page stores 50 tuples. Imagine that the catalog manager provides statistics about the distribution of some attributes of this relation as the following:
Type: {‘A’: 10%, ‘B’: 30%, ‘C’: 45%, ‘D’: 15%}
ApprovalYear: {2018: 10%, 2019: 60%, 2020: 30%}
Suppose that the following SQL query is executed frequently using the given relation:
SELECT Type
FROM Loans
WHERE Type IN (‘B’, ‘C’)
AND ApprovalYear > 2019;
Question 18
Not yet graded / 2 pts
Compute the estimated result size (in number of tuples) for the query, and the reduction factor of each predicate.
Your Answer:
Unanswered
Unanswered
Unanswered
Question 19
Not yet graded / 2 pts
Compute the estimated cost of plan alternatives and state which plan is the best assuming that a clustered B+ tree index on (ApprovalYear) is (the only index) available. Suppose there are 100 index pages.
Your Answer:
Question 20
Not yet graded / 2 pts
What would happen if our query changed and became:
Will this change impact the result size? If yes, calculate the new result size. If no, explain why.
Your Answer:
SELECT *
FROM Loans
WHERE 2018 < ApprovalYear
AND ApprovalYear < 2020
AND Type IN ('B', 'C');
Consider three relations called Account, AccountDetail, and TransactionType. Imagine that the relation Account has 250 pages, AccountDetail has 5000 pages, and TransactionType has 50 pages. Each page stores 100 tuples. The AccountType attribute has values in the set {'Savings', 'CreditCard'}. There is a clustered B+ tree index on AccountType, which is 50 pages in size. Consider the following query:
SELECT *
FROM Account AS A, AccountDetail AS AD, TransactionType AS TT
2020/12/4
https://canvas.lms.unimelb.edu.au/courses/8200/quizzes/91343 8/13
Practice Exam Quiz: Database Systems (INFO20003_2020_SM2)
Question 21
Not yet graded / 4 pts
Compute the cost of the plan shown below. NLJ is a Page-oriented Nested Loops Join. Assume that Account_ID is the candidate key of Account, TranType_ID is the candidate key of TransactionType, and 100 tuples of a resulting join between Account and AccountDetail can fit on one page.
Your Answer:
Unanswered
Question 22
Not yet graded / 8 pts
Compute the cost of the plan shown below. Assume that SMJ can be done in 2 passes. Assume that Account_ID is the candidate key of Account, TranType_ID is the candidate key of TransactionType, and 100 tuples of a resulting join between Account and AccountDetail can fit on one page.
Your Answer:
Unanswered
Unanswered
WHERE A.Account_ID = AD.Account_ID AND AD.TranType_ID = TT.TranType_ID
AND A.AccountType = 'Savings';
Question 23
Not yet graded / 3 pts
Would the cost of the plan presented in the previous question change if the selection on AccountType happened between HJ and SMJ, or after SMJ? Explain (intuitively) which out of the three possibilities would be the cheapest: a) the original plan presented in the previous question, b) the plan with selection happening between HJ and SMJ, or c) the plan with selection happening after SMJ as the last operation in the plan. You do not need to calculate the cost of all three alternatives, an explanation in 2-3 sentences is sufficient.
2020/12/4 Practice Exam Quiz: Database Systems (INFO20003_2020_SM2)
Your Answer:
You work in the IT department at CBA. The relation LoanSchedules below contains the amortization schedules of the loans approved by the bank:
DueDate
Status
Principal
Interest
Balance
LoanNum
ApprovalDate
Score
Client
CID
31/01/2019
paid
100
12
1007
4885
01/01/2019
B1
James B
7846564
28/02/2019
partial, past due
101
11
950
4885
01/01/2019
B1
James B
7846564
31/01/2019
paid
50
40
4561
4886
02/01/2019
A2
Ben R
8758155
28/02/2019
paid
52
38
4509
4886
02/01/2019
A2
Ben R
8758155
31/12/2018
paid
1550
450
10020
4710
01/12/2018
B2
James B
7846564
31/01/2019
partial, past due
1565
435
10000
4710
01/12/2018
B2
James B
7846564
28/02/2019
past due
1580
420
10000
4710
01/12/2018
B2
James B
7846564
The set {LoanNum, DueDate} is a candidate key for this relation. The following functional dependencies hold for this relation:
LoanNum, DueDate --> Status, Principal, Interest, Balance LoanNum –> CID, ApprovalDate, Score
CID –> Client
Question 24
Not yet graded / 5 pts
Could any anomalies arise in the table above? If yes, discuss which anomalies and problems may appear in this table. Provide examples.
Your Answer:
Unanswered
Unanswered
Question 25
Not yet graded / 10 pts
Normalize the table above to 3rd Normal Form (3NF). For each step, explicitly identify which normal form is violated and briefly explain why. Write the normalised tables in textual format, as in:
TableName (PrimaryKey, Column, ForeignKey(FK)) AnotherTable (PrimaryKey, Column, AnotherColumn)
Your Answer:
Unanswered
Question 26
Not yet graded / 2 pts
Normalisation prevents the anomalies as discussed above, however, some databases are deliberately non- normalised.
https://canvas.lms.unimelb.edu.au/courses/8200/quizzes/91343
9/13
2020/12/4 Practice Exam Quiz: Database Systems (INFO20003_2020_SM2)
Unanswered
Question 27
0 / 2 pts
A
B
C
a1
b1
c5
a1
b2
c6
a2
b1
c7
a3
b2
c6
You Answered Correct Answer
You Answered Correct Answer
Unanswered
You are given this relation:
A possible candidate key is: [ Select ] , and a functional dependency is: [ Select ]
Answer 1:
A, B
Answer 2:
B, A –> C
A conceptual model of the production database used by Yarra Trams is shown below:
Yarra Trams operates 600 trams across 24 routes. New trams are added to the fleet on a yearly basis, whereas routes hardly change. Each tram makes 20 trips daily and each trip comprises 35 stops on average. The “Trip Journal” table stores timestamps of each tram arriving at each stop.
Fill in the blanks below:
The fastest-growing table in this model will be the [ Select ] table, while the slowest-growing will be the [ Select ] table.
If a row in the “Trip Journal” table has an average size of 80 bytes, and it is currently 600 megabytes, we might estimate that the “Trip Journal” table will increase [ Select ] in size this day.
Answer 1:
Trip Journal
(You left this blank)
(You left this blank)
You Answered Correct Answer
(You left this blank)
Answer 2:
Explain in 2-3 sentences why/when we might choose to not normalise a database. Your Answer:
Question 28
0 / 3 pts
https://canvas.lms.unimelb.edu.au/courses/8200/quizzes/91343
10/13
2020/12/4 Practice Exam Quiz: Database Systems (INFO20003_2020_SM2)
You Answered Correct Answer
You Answered Correct Answer
Unanswered
Tram Route
Answer 3:
~ 5%
Question 29
0 / 3 pts
For the following case study, choose the best option for each of the choices related to the company’s backup:
Acme inc. is a sales company with many locations around the world, each with its own production database. Acme is currently trying to optimise its business process. To do this, it wants to be able to save snapshots of parts of its production database over time, so that these can be analysed by data analysts. The data analysts are unlikely to have access to the same version of the DBMS used by Acme, and also different Acme locations often have different DBMS versions! The production databases are used very heavily during the day, but are essentially unused overnight.
Logical/Physical
Correct Answer
Correct Answer
Correct Answer
Logical
Offline
Full
Online/Offline
Full/Incremental
Other Incorrect Match Options: Online
Incremental Physical
You Answered
You Answered
You Answered
Unanswered
(You left this blank)
(You left this blank)
Question 30
Not yet graded / 6 pts
Alice and Bob are two Unimelb students who are both interested in buying a new Nintendo Switch (to deal with isolation boredom). They¡¯ve both wound up on the JB Hifi website, where the page lists that there is only 1 left in stock. Both Alice and Bob are trying to reserve the switch so they can go pick it up in store.
The database table might look something like this: StockReservations(StockID, StoreID(FK), ItemTypeID(FK), ReservationName) The timeline of their interactions with the site is as follows:
https://canvas.lms.unimelb.edu.au/courses/8200/quizzes/91343
11/13
2020/12/4 Practice Exam Quiz: Database Systems (INFO20003_2020_SM2)
Time t
Bob
Alice
t =0
Clicks the JB Hifi link from google [reads available unreserved stock]
Unanswered
t=1
Clicks ¡®Reserve¡¯ [writes new ReservationName]
Answer the following questions:
1. What is the name for this sort of common problem?
2. Who ends up with their name on the switch reservation after this interaction?
3. Give two possible ways to implement concurrency control to prevent this issue, and for each solution,
indicate which of Bob and Alice will wind up with the Nintendo Switch! Your Answer:
Clicks the JB Hifi link from google [reads available unreserved stock]
t=2
Clicks ¡®Reserve¡¯ [writes new ReservationName]
t=3
You work for AirQuality Australia and you have to create a data warehouse to keep track of the pollutants present in the air. The main air pollutants and their units of measurement are: (a) particulate matter PM10 and PM2.5 ( g/m3), (b) ground-level ozone O3 (ppm), (c) nitrogen dioxide NO2 (ppb), (d) carbon monoxide CO (ppm), and (e) sulphur dioxide SO2 (ppb). Sources of these pollutants are motor vehicle exhaust, industry, wildfires, etc. Sensors of different types have been set up across the country to measure the levels of these pollutants. Each sensor location is registered as a latitude and longitude pair, which in turn, is mapped to its corresponding suburb. There are even specialized satellites that report the levels of contamination. In the case of satellites, latitude and longitude are left empty. Public health institutions are interested in knowing the levels of contamination aggregated by sensor location, type of sensor, and pollutant. Due to the massive wildfires in Australia that occurred recently, authorities want to have daily and weekly reports at their disposal.
Question 31
Not yet graded / 5 pts
Use Kimball¡¯s four-step dimensional design process for designing a dimensional model to support the design of this data warehouse. Explain what you achieved in each step of Kimball¡¯s model. In particular make sure to discuss the name of the business process, identify and explain dimensions, declare the grain, and identify and explain facts.
Your Answer:
Unanswered
Question 32
Not yet graded / 5 pts
Draw a star schema to support the design of this data warehouse, showing the attributes in each table. Use PK to denote primary key, PFK to denote primary foreign key, and FK to denote foreign key. You do not need to specify data types nor whether the fields are NULL/NOT NULL.
https://canvas.lms.unimelb.edu.au/courses/8200/quizzes/91343
12/13
2020/12/4 Practice Exam Quiz: Database Systems (INFO20003_2020_SM2)
Question 33
Not yet graded / 2 pts
Why is “Data Consistency” VERY POOR in a distributed database with “Integrated Partitions”? DO NOT USE the explanation in the table provided in the lecture slides.
Your Answer:
Unanswered
Question 34
Not yet graded / 2 pts
You are an up-and-coming Instagram influencer. You¡¯ve just made a new post, and quickly call your friend (who lives overseas) to tell them to go and like it. However, when your friend goes to visit your
profile there¡¯s a problem: your old posts are showing fine, but the new one isn¡¯t showing up! After a few minutes of them refreshing the page, your post finally shows for them. You happen to know that Instagram uses a NoSQL database for their posts. Rationalise these observations with relation to CAP theorem and BASE.
Your Answer:
Unanswered
Question 35
Not yet graded / 2 pts
Give an example of how NoSQL databases help solve the Object-Relational impedance mismatch problem. Your Answer:
Unanswered
Remember to upload your answers to modelling + relational algebra + data warehousing at the top of the exam (question 1) if you’ve chosen to scan all questions together.
END OF EXAM
Remember you may upload the scan of these questions below, or combine and scan all handwritten questions together (see top of the exam, question 1)
https://canvas.lms.unimelb.edu.au/courses/8200/quizzes/91343
13/13