COMP9120
Week 13: Review
Semester 2, 2021
Acknowledgement of Country
I would like to acknowledge the Traditional Owners of Australia and recognise their
continuing connection to land, water and culture. I am currently on the land of the
Burramattagal people and pay my respects to their Elders, past, present and
emerging.
I further acknowledge the Traditional Owners of the country on which you are on
and pay respects to their Elders, past, present and future.
Copyright warning

COMMONWEALTH OF AUSTRALIA

Copyright Regulations 1969

WARNING

This material has been reproduced and communicated to
you by or on behalf of the University of Sydney
pursuant to Part VB of the Copyright Act 1968 (the
Act).

The material in this communication may be subject
to copyright under the Act. Any further copying or
communication of this material by you may be the
subject of copyright protection under
the Act.

Do not remove this notice.


▪ Date: 26th of November 2021
▪ Time: 9:00 AM Sydney time
▪ Duration: 175 Minutes
▪ Reading time: 10 Minutes
▪ Writing time: 150 Minutes
▪ Upload time: 15 minutes
▪ Everyone starts the exam at the same time
▪ No late submission
▪ New Canvas site
▪ Final Exam for: COMP9120
▪ Access no later than 7 days before the exam
▪ Exam adjustment is done by the exam office
▪ Notification no later than 3 days before the exam
Final Exam
Keep track of your
time during exam
Final Exam
› Exam covers 50% of the final mark
› You must obtain at least 40% in the exam, as
well as an overall mark of at least 50%, to pass
the unit
5
Question Type
▪ No MCQs
▪ Essay-type questions (10 Questions→50 marks)
▪ ERD
▪ Relational Model
▪ Relational Algebra
▪ SQL
▪ Integrity Constraints
▪ Transactions
▪ Normalizations
▪ Storage
▪ Indexing
▪ Query Processing
Practice exam is
available in Canvas
Course Outline
6
Week Topic
Fo
u
n
d
atio
n
s
Week 1 Introduction
Week 2 Conceptual Database Design
Week 3 Relational Data Model / Logical Database Design
Week 4 Relational Algebra and SQL
Week 5 Complex SQL
Week 6 Database Integrity
A
p
p
licatio
n
s
Week 7 Database Application Development and Security
Week 8 Transaction Management
Week 9 Schema Refinement and Normalisation
In
tern
als
Week 10 Storage and Indexing
Week 11 Query Evaluation and Optimisation
Week 12 Revision
Week 13 Final exam Structure
Which is the correct model?
7
“Each chef prepares at most one dish. Every dish must be prepared by at least one chef.”
Chef Dishprepares
Chef Dishprepares
Chef Dishprepares
Chef Dishprepares
a.
b.
c.
d.
Drawing ER Diagram
Consider a CUSTOMER_ORDER database in which employees take orders for products from
customers. The data requirements are summarized as follows:
- The company has employees, each identified by a unique employee number, name and
salary.
- Each customer of the company is identified by a unique customer number, name and
registration date.
- Each product sold by the company is identified by a unique product number, a product
name, unit price, and quantity in stock.
- Each order placed by a customer is taken by an employee and is given a unique order
number. Each order contains specified quantities of each products. Each order has a
date of receipt as well as an expected ship date. The actual ship date is also recorded.
Design an Entity-Relationship diagram for the customer order database.
8
Drawing ER Diagram
9
The company has employees, each
identified by a unique employee
number, name and salary.
Drawing ER Diagram
10
The company has employees, each
identified by a unique employee
number, name and salary.
Each customer of the company is identified
by a unique customer number, name and
registration date.
Drawing ER Diagram
11
The company has employees, each
identified by a unique employee
number, name and salary.
Each customer of the company is identified
by a unique customer number, name and
registration date.
Each product sold by the company is identified
by a unique product number, a product name,
unit price, and quantity in stock.
Drawing ER Diagram
Each order placed by a customer is taken by an employee and is given a unique order
number. Each order contains specified quantities of each products. Each order has a
date of receipt as well as an expected ship date. The actual ship date is also recorded.
The company has employees, each
identified by a unique employee
number, name and salary.
Each customer of the company is identified
by a unique customer number, name and
registration date.
Each product sold by the company is identified
by a unique product number, a product name,
unit price, and quantity in stock.
Mapping Relationship Types with Key Constraints
› Looking on each relationship side:
- 1 player plays in at most 1 team
- 1 team can have 0 to Many players
13
Player TeamPlaysIn
pid
name
tid
tname
Team
tnametid
Player
tidpid name
Mapping Relationship Types with Key Constraints
› Looking on each relationship side:
- 1 library contains 0 to many books
- 1 book can be found in 0 to many libraries
14
Library Bookcontains
lid
name
bid
title
Book
titlebid
Library
lid name
Contains
bidlid
Creating Schema Diagram
Create the schema diagram equivalent to the following ER diagram
Creating Schema Diagram
Create the schema diagram equivalent to the following ER diagram
Employee(employee_number, employeeName, salary)
PK = employee_number
Customer(customer_number, customerName, registrationDate)
PK = customer_number
Product(product_number, productName, price, quantity_in_stock)
PK = product_number
Order(customer_number, order_number, date_receipt,
expected_ship_date, actual_ship_date, employee_number)
PK = (customer_number, order_number)
FK = customer_number --> Customer
employee_number --> Employee
Contains(customer_number, order_number, product_number, quantity)
PK = (customer_number, order_number, product_number)
FK = customer_number --> Order
order_number --> Order
product_number --> Product
SQL
› Modify the table to set the name attribute as Not Null
ALTER TABLE Student ALTER COLUMN name SET NOT NULL;
› Write a SQL statement to change the country of Simon from GBR to USA
UPDATE Student SET country = ‘USA’ WHERE name = ‘Simon’
› Write a SQL statement to delete all records from Student table
DELETE FROM Student;
› Write a SQL statement to delete Student table
DROP TABLE Student;
17
Student
sid name gender country
1001 Ian M AUS
1002 Ha Tschi F ROK
1003 Grant M AUS
1004 Simon M GBR
1005 Jesse F CHN
1006 Franzisca F GER
CREATE TABLE Student
(
sid INTEGER PRIMARY KEY,
name VARCHAR(20),
gender CHAR CHECK(gender IN (‘F’,’M’)),
country CHAR(3)
);
Relational Algebra
- List the names of all students enrolled in ‘DBMS’
- πname (σ title=‘DBMS’ ((Student ⋈ Enrolled) ⋈ UnitOfStudy))
- List the id of students who are not enrolled in any unit of study
P[name] (S[title=‘DBMS’] ((Student J Enrolled) J UnitOfStudy))
Student(sid, name, gender, country)
Enrolled(sid, uos_code, semester)
UnitOfStudy(uos_code, title, points)
P[sid] (Student) - P[sid] (Enrolled)
Relational Algebra
Suppliers(sid, sname, address)
Product(pid, pname, colour)
Catalog(sid, pid, price)
› Find the names of all suppliers who supply a product that is red or green.
(( (=′′ ⋁=′′() ⋈ )) ⋈ )
Employees(employeeID, name, age, salary)
Supervises(bossID, employeeID)
› Find the names and salaries of all bosses who have an employee earning more than 1000
,(=( >1000 ⋈ × )
19
SQL
› Find the id of all students who are enrolled in both ‘COMP5138’ and ‘COMP5318’.
SELECT sid FROM Enrolled WHERE uos_code=‘COMP5138’
INTERSECT
SELECT sid FROM Enrolled WHERE uos_code=‘COMP5318’
› Find the names of students who are enrolled in both ‘COMP5138’ and ‘COMP5318’
SELECT sid FROM Enrolled WHERE uos_code=‘COMP5138’
INTERSECT
SELECT sid FROM Enrolled WHERE uos_code=‘COMP5318’
SELECT DISTINCT name
FROM Student
WHERE sid IN (
)
Student(sid, name, gender, country)
Enrolled(sid, uos_code, semester)
UnitOfStudy(uos_code, title, points)
SQL
› Find the name of the students who are enrolled in the unit of study that has the highest credit points
SELECT uos_code
FROM UnitOfStudy
WHERE points = ( )
SELECT name
FROM Student
WHERE sid IN (
)
Student(sid, name, gender, country)
Enrolled(sid, uos_code, semester)
UnitOfStudy(uos_code, title, points)
SELECT max(points) FROM UnitOfStudy
SELECT name
FROM Student NATURAL JOIN Enrolled NATURAL JOIN UnitOfStudy
WHERE points = ( )SELECT max(points) FROM UnitOfStudy
Approach 1:
Approach 2:
SELECT sid
FROM Enrolled
WHERE uos_code IN (
)
SQL
› Find the number of students in each country, except Australia (‘AUS’). Only include countries that
have equal or more students than Australia. Show the output in sorted order of country name.
22
SELECT country, COUNT(sid)
FROM Student
WHERE country <> 'AUS'
GROUP BY Country
HAVING COUNT(sid) >= (SELECT COUNT(sid) FROM Student WHERE country = 'AUS')
ORDER BY country
Student(sid, name, gender, country)
Enrolled(sid, uos_code, semester)
UnitOfStudy(uos_code, title, points)
Query Processing
› True/False
- Parser takes a query-evaluation plan, executes that plan, and returns the answers.
- SQL systems remove duplicates even if the keyword DISTINCT is not specified in a
query.
- Pipelined evaluation is cheaper than materialization
- External merge-sort works even if the entire table does not fit in the main memory
- Natural join is a special case of Equi-join
23
False
False
True
True
True
Assume that there are 1,000,000 records in a relation R and each record is 200 bytes long.
Each page is 4K bytes, of which 250 bytes are reserved for header and array of record
pointers. Assume pages are 90% full on average.
› How many records can we store in each page?
› How many pages required to store R?
Solution:
Empty space in each page is (4*1024 – 250) = 3846 bytes
Number of records per page = floor(3846 / 200) = 19 records
On average, each page contains floor(19 * 90%) = 17 records
Number of pages required to store the table = ceil(1,000,000/17) = 58,824 pages
24
Storage
Join
Consider two tables employee(eid, ename, did) and department(did, dname). There are
10000 tuples in the employee table and 1000 tuples in the department table. It requires
500 pages and 100 pages to store the records of employee and department table
respectively.
› Calculate the cost of nested loop join and block-nested loop join of these two tables.
25
The estimated cost of nested loops join
employee as outer table: 500+ 10000 * 100 = 1,000,500 disk I/Os
department as outer table: 100 + 1000  500 = 500,100 disk I/Os
The estimated cost of block nested loops join
employee as outer table: 500 + 500 * 100 = 50,500 disk I/Os
department as outer table: 100 + 100  500 = 50,100 disk I/Os
… and that’s it.
• Iwan Budiman
• Dhruvil Shah
• Tommy Zhou
• Mahdie Rezaein
• Chen Chen
• Abbey Lin and
• Sheik Mohammad Mostakim Fattah
Thanks to the teaching team of this semester!
Review
Your Unit of Study Survey (USS) feedback is confidential.
It’s a way to share what you enjoyed and found most useful in your learning, and to provide constructive feedback.
It’s also a way to ‘pay it forward’ for the students coming behind you, so that their learning experience in this class
is as good, or even better, than your own.
When you complete your USS survey (https://student-surveys.sydney.edu.au), please:
Be specific.
Which class tasks, assessments or other activities helped you to learn? Why were they helpful? Which one(s)
didn’t help you to learn? Why didn’t they work for you?
Be constructive.
What practical changes can you suggest to class tasks, assessments or other activities, to help the next class learn
better?
Be relevant.
Imagine you are the teacher. What sort of feedback would you find most useful to help make your teaching more
effective?
Unit of Study Survey
Best of luck!






















































































































































































































































































































































































学霸联盟