Presented by�Lucy Martin //
Sandy Luo //
Colton Carner
DISCLAIMER
This workshop is not officially endorsed by the university or the teaching staff and may not fully reflect the assessable subject content. Any tutors that participate in this workshop have verbally chosen to do so voluntarily and are not expected to represent the teaching team.
CISSA Revision Workshops
Structure of Today’s Workshop
Structure of Today’s Workshop
We’ve made a mini-exam for you to work through in groups :)
Entity-Relational
Modelling
Sandy
ER Modelling: Exam tips
ER Modelling: Q1
ER Modelling: Q2
0...m
1...m
1...m
1...1
0...m
0...1
0...1
1...m
ER Modelling: Q3
Main difference:
Should booking be an identifying relationship?
Constraint between Accomodation and Person:
ER Modelling: Q4
CREATE TABLE School (
SchoolName VARCHAR(50),
SchoolCity VARCHAR(50),
StudentCount SMALLINT,
PRIMARY KEY (SchoolName)
);
CREATE TABLE PerfReview (
PRDate DATE,
QualityRating TINYINT,
SkillsRating TINYINT,
Comments VARCHAR(500),
TeacherID INT,
PRIMARY KEY (PRDate, TeacherID),
FOREIGN KEY (TeacherID) REFERENCES Teacher(TeacherID)
);
CREATE TABLE Teacher (
TeacherID INT,
TeacherFirstName VARCHAR(50),
TeacherLastName VARCHAR(50),
SchoolName VARCHAR(50),
PRIMARY KEY (TeacherID),
FOREIGN KEY (SchoolName) REFERENCES School(SchoolName)
);
CREATE TABLE TeacherLanguages (
TeacherID INT,
LanguageName VARCHAR(20),
PRIMARY KEY (TeacherID, LanguageName),
FOREIGN KEY (TeacherID)
REFERENCES Teacher(TeacherID)
);
ER Modelling: Q5
Do we know for sure?
Super Key:
A set of fields where no two distinct tuples can have same values in all key fields.
Candidate Key:
Minimal subset of super keys
SQL + Relational Algebra
Colton: 30 mins
SQL: Exam structure
The way we test SQL is pretty consistent each semester: A case study that you need to write SQL queries for.
SQL: Ready-set-go!
Q1: Return the names of tournaments, played at courses in the suburb of Heatherton, where Bella Sandbury ranked in the top ten (use the rank already provided in the schema).
SELECT DISTINCT TournamentName
FROM
Player
NATURAL JOIN PlayerCompetes
NATURAL JOIN Tournament
NATURAL JOIN Course
WHERE
CourseSuburb = ‘Heatherton’
AND PlayerFirstName = ‘Bella’
AND PlayerLastName = ‘Sandbury’
AND Rank <= 10;
Q2: List every hole in every course, along with the lowest number of strokes ever recorded on that hole, the player who achieved this, and in which competition.
SELECT CourseName, HoleNumber, minStrokes, playerID, competitionName
FROM
Hole
NATURAL LEFT JOIN PlayerPlaysHole
NATURAL LEFT JOIN Tournament
NATURAL LEFT JOIN (
SELECT CourseID, HoleNumber, min(NumberOfStrokes) AS minStrokes
FROM Hole NATURAL JOIN PlayerPlaysHole
GROUP BY CourseID, HoleNumber
) AS holeMinStrokes
WHERE ISNULL(holeMinStrokes.minStrokes) OR holeMinStrokes.minStrokes = PlayerPlaysHole.NumberOfStrokes
Could also do a non-natural left join above instead (but requires a lot in the ON clause)
Could also do this using RANK() window function
SQL: More practice?
More practice on LMS!
Modules > Active learning (scroll down) > SpotifySQL + UberEatsSQL
Modules > Practice on your own (scroll down) > ‘SQL: Golf Study’
Relational Algebra: Exam structure
Relational Algebra: Ready-Set-Go!
List the topics of forums where the general user with user id “1” has posted at least once before the date “01-01-2022”.
Query Optimisation + Processing
Lucy
Exam tips
Single Relation Plan A: Question 9
product: 1000 pages 100 tuples/page 100,000 tuples
product.totalprice: [0,10000] ∴ 10000 distinct values
product.quantity: [1,100] ∴ 100 distinct values
totalprice < 5000 ∴ RF = 0.5
quantity = 20 ∴ RF = 0.01
∴ ΠRF = 0.5 ✕ 0.01 = 0.005
Result_size = NTuples(R) ✕ ΠRF ∴ 100,000 ✕ 0.005 = 500 Tuples
Single Relation Plan B: Question 10
product: 1000 pages 100 tuples/page 100,000 tuples
product.totalprice: [0,10000] ∴ 10000 distinct values
product.quantity: [1,100] ∴ 100 distinct values
unclustered B+ tree index<quantity> 100 pages
Note: can only use the RF from the index’s search key <quantity> ∴ 0.01
(100 + 100,000) ✕ 0.01 = 1001 IOs
However, heap scan = 1000 IOs
Multi-Relation Plan A: Question 11
patient: 20 pages sort passes: 1
diagnosis: 10,000 pages sort passes: 2
buffer: 12 pages
20 + ((20/(12-2)) ✕ 10000) = 20020
20 + ((20/10) ✕ 10000) = 20020
20 + (2 ✕ 10000) = 20020 IOs
Multi-Relation Plan A: Question 12
patient: 20 pages sort passes: 1
diagnosis: 10,000 pages sort passes: 2
buffer: 12 pages
2 ✕ 20 ✕ 1�+ 2 ✕ 10,000 ✕ 2�+ 20 + 10,000
= 50,060 IOs
Query Optimisation Introduction
employee: 10 pages 100 tuples/page 1000 tuples
order: 6000 pages 100 tuples/page 600,000 tuples
item: 100 pages 100 tuples/page 10,000 tuples
employee.position 20 distinct values�order.totalcost [0,10000] ∴ 10000 distinct values
clustered B+ tree index<order.totalcost> 50 pages
clustered B+ tree index<item.itemId> 10 pages
employee ⋈ order 100 tuples/page�order ⋈ item 100 tuples/page��sort passes: 2 passes NLJ: page-oriented
Query Optimisation Plan: Question 13, part 1 [A]
employee: 10 pages 100 tuples/page 1000 tuples
order: 6000 pages 100 tuples/page 600,000 tuples
item: 100 pages 100 tuples/page 10,000 tuples
employee.position 20 distinct values�order.totalcost 10000 distinct values
clustered B+ tree index<order.totalcost> 50 pages
clustered B+ tree index<item.itemId> 10 pages
employee ⋈ order 100 tuples/page�order ⋈ item 100 tuples/page�sort passes: 2 passes NLJ: page-oriented
totalcost > 5000 ∴ RF = 0.5
result_size = NTuples(R) ✕ ΠRF
∴ 600,000 ✕ 0.5 = 300,000 Tuples
∴ 300,000 ÷ 100 = 3,000 Pages
Query Optimisation Plan: Question 13, part 2 [B]
employee: 10 pages 100 tuples/page 1000 tuples
order: 6000 pages 100 tuples/page 600,000 tuples
item: 100 pages 100 tuples/page 10,000 tuples
employee.position 20 distinct values�order.totalcost 10000 distinct values
clustered B+ tree index<order.totalcost> 50 pages
clustered B+ tree index<item.itemId> 10 pages
employee ⋈ order 100 tuples/page�order ⋈ item 100 tuples/page�sort passes: 2 passes NLJ: page-oriented
E.EmpID = O.EmpID ∴ RF = 1/1000 = 0.001
result_size = ΠNTuples ✕ ΠRF
∴ 300,000 ✕ 1000 ✕ 0.001 = 300,000 Tuples
∴ 300,000 ÷ 100 = 3,000 Pages employee⋈order: 3,000 pages
Query Optimisation Plan: Question 13, part 3 [C]
employee: 10 pages 100 tuples/page 1000 tuples
order: 6000 pages 100 tuples/page 600,000 tuples
item: 100 pages 100 tuples/page 10,000 tuples
employee.position 20 distinct values�order.totalcost 10000 distinct values
clustered B+ tree index<order.totalcost> 50 pages
clustered B+ tree index<item.itemId> 10 pages
employee ⋈ order 100 tuples/page�order ⋈ item 100 tuples/page�sort passes: 2 passes NLJ: page-oriented
cost = (50 + 6000) ✕ 0.5 = 3,025 IOs
Query Optimisation Plan: Question 13, part 4 [D]
employee: 10 pages 100 tuples/page 1000 tuples
order: 6000 pages 100 tuples/page 600,000 tuples
item: 100 pages 100 tuples/page 10,000 tuples
employee.position 20 distinct values�order.totalcost 10000 distinct values
clustered B+ tree index<order.totalcost> 50 pages
clustered B+ tree index<item.itemId> 10 pages
employee ⋈ order 100 tuples/page�order ⋈ item 100 tuples/page�sort passes: 2 passes NLJ: page-oriented
2 ✕ 3000 ✕ 2 + �2 ✕ 10 ✕ 2 +
3000 + 10
= 12,050 IOs
Query Optimisation Plan: Question 13, part 5 [E]
employee: 10 pages 100 tuples/page 1000 tuples
order: 6000 pages 100 tuples/page 600,000 tuples
item: 100 pages 100 tuples/page 10,000 tuples
employee.position 20 distinct values�order.totalcost 10000 distinct values
clustered B+ tree index<order.totalcost> 50 pages
clustered B+ tree index<item.itemId> 10 pages
employee ⋈ order 100 tuples/page�order ⋈ item 100 tuples/page�sort passes: 2 passes NLJ: page-oriented
employee⋈order: 3,000 pages
2 1 ✕ 3000 +
2 ✕ 100 +
3000 + 100
= 6,300 IOs
Normalization
Sandy
Normalisation:
QA+B: 5min, QC: 5min
Normalisation: Q1A
Normalisation: Q1B
1. Insertion Anomaly
2. Deletion Anomaly
3. Update Anomaly
Examples:
1. Can’t add a writer unless there is a consultation
2. Changing the mobile of a writer needs to be repeated
3. Delete a consultation for HBO -> details of the writer and order could disappear completely
Normalisation: Q1C
Normalisation: Q1C
2NF: Violated, have a partial dependency CusNo and also Order. We need to resolve both.
Normalisation: Q1C
3NF: Any transitive dependencies? Key: {CusNo, Order}
Normalisation: Q1C
3NF: Violated since we have a transitive dependency Writer hence need to resolve. Customer and Order are finished already.
Data Warehousing
Lucy
Transactions: Ready-set-go!
‘Database Concepts’ (Transactions + Distributed + Backups + NoSQL)
Colton: ~40 mins?
DB Concepts: Exam structure
Final part of the subject, and the most varied in assessment structures.
Transactions: Ready-set-go!
Distributed + NoSQL: Ready-set-go!
Backups: Ready-set-go!
Feedback form + sign ups
Feedback Form!
OLD STUFF BELOW HERE
Key concepts
Question 1: Data types
Tips
Tips
7. Steps to go through a question about ER modelling in the exam
Tips
8. List your assumptions if you made any, but only use assumptions when there are ambiguities instead of simplifying the case study question
9. Do not draw a messy final model
10. Check assignment 1 feedback
Bridgman Art Library (adapted from Practice data modelling task)
Bridgman Art Library manages copyright of artworks. Customer organisations that wish to use a copyrighted image can pay a fee to Bridgman Art Library, who will then provide a high-quality image for reproduction.
Customers must register a proposal for the use of the artwork, containing information about the intended use, intended market and intended duration of use. The customer also nominates the price they are willing to pay for their proposed use. The date the proposal was made is also stored.
Bridgman Art Library needs to be able to identify who has owned the artwork at any point in time since the artwork’s creation. Each artwork may have been owned by many owners over time but can only have one owner at any particular moment. About each owner we need to know the address, and country, and name.
A maker (creator of artwork) can be an individual, a company, or an atelier. The database needs to be able to store the maker’s first and last name, nationality, birth year and death year, and the atelier or company name (if known). Bridgman records a single maker for each artwork.
For each artwork, we store the artwork number by each maker, the type of artwork, the title, the artwork’s dimensions – height, width, and whether it is available for copyright reproduction.
Customers can be individuals or companies. We need to know contact information for each customer – first and last name, company name (if relevant), postal address, city, country, email address, and telephone numbers. At any given time, multiple customers may have proposal permission for a particular artwork.
Entities
Bridgman Art Library manages copyright of artworks. Customer organisations that wish to use a copyrighted image can pay a fee to Bridgman Art Library, who will then provide a high-quality image for reproduction.
Customers must register a proposal for the use of the artwork, containing information about the intended use, intended market and intended duration of use. The customer also nominates the price they are willing to pay for their proposed use. The date the proposal was made is also stored.
Bridgman Art Library needs to be able to identify who has owned the artwork at any point in time since the artwork’s creation. Each artwork may have been owned by many owners over time but can only have one owner at any particular moment. About each owner we need to know the address, and country, and name.
A maker (creator of artwork) can be an individual, a company, or an atelier. The database needs to be able to store the maker’s first and last name, nationality, birth year and death year, and the atelier or company name (if known). Bridgman records a single maker for each artwork.
For each artwork, we store the artwork number by each maker, the type of artwork, the title, the artwork’s dimensions – height, width, and whether it is available for copyright reproduction.
Customers can be individuals or companies. We need to know contact information for each customer – first and last name, company name (if relevant), postal address, city, country, email address, and telephone numbers. At any given time, multiple customers may have proposal permission for a particular artwork.
Entities
Bridgman Art Library manages copyright of artworks. Customer organisations that wish to use a copyrighted image can pay a fee to Bridgman Art Library, who will then provide a high-quality image for reproduction.
Customers must register a proposal for the use of the artwork, containing information about the intended use, intended market and intended duration of use. The customer also nominates the price they are willing to pay for their proposed use. The date the proposal was made is also stored.
Bridgman Art Library needs to be able to identify who has owned the artwork at any point in time since the artwork’s creation. Each artwork may have been owned by many owners over time but can only have one owner at any particular moment. About each owner we need to know the address, and country, and name.
A maker (creator of artwork) can be an individual, a company, or an atelier. The database needs to be able to store the maker’s first and last name, nationality, birth year and death year, and the atelier or company name (if known). Bridgman records a single maker for each artwork.
For each artwork, we store the artwork number by each maker, the type of artwork, the title, the artwork’s dimensions – height, width, and whether it is available for copyright reproduction.
Customers can be individuals or companies. We need to know contact information for each customer – first and last name, company name (if relevant), postal address, city, country, email address, and telephone numbers. At any given time, multiple customers may have proposal permission for a particular artwork.
Weak Entities
Bridgman Art Library manages copyright of artworks. Customer organisations that wish to use a copyrighted image can pay a fee to Bridgman Art Library, who will then provide a high-quality image for reproduction.
Customers must register a proposal for the use of the artwork, containing information about the intended use, intended market and intended duration of use. The customer also nominates the price they are willing to pay for their proposed use. The date the proposal was made is also stored.
Bridgman Art Library needs to be able to identify who has owned the artwork at any point in time since the artwork’s creation. Each artwork may have been owned by many owners over time but can only have one owner at any particular moment. About each owner we need to know the address, and country, and name.
A maker (creator of artwork) can be an individual, a company, or an atelier. The database needs to be able to store the maker’s first and last name, nationality, birth year and death year, and the atelier or company name (if known). Bridgman records a single maker for each artwork.
For each artwork, we store the artwork number by each maker, the type of artwork, the title, the artwork’s dimensions – height, width, and whether it is available for copyright reproduction.
Customers can be individuals or companies. We need to know contact information for each customer – first and last name, company name (if relevant), postal address, city, country, email address, and telephone numbers. At any given time, multiple customers may have proposal permission for a particular artwork.
Entities
Customer
Proposal
Artwork
Owner
Maker
Relationships & Business rules
Bridgman Art Library manages copyright of artworks. Customer organisations that wish to use a copyrighted image can pay a fee to Bridgman Art Library, who will then provide a high-quality image for reproduction.
Customers must register a proposal for the use of the artwork, containing information about the intended use, intended market and intended duration of use. The customer also nominates the price they are willing to pay for their proposed use. The date the proposal was made is also stored.
Bridgman Art Library needs to be able to identify who has owned the artwork at any point in time since the artwork’s creation. Each artwork may have been owned by many owners over time but can only have one owner at any particular moment. About each owner we need to know the address, and country, and name.
A maker (creator of artwork) can be an individual, a company, or an atelier. The database needs to be able to store the maker’s first and last name, nationality, birth year and death year, and the atelier or company name (if known). Bridgman records a single maker for each artwork.
For each artwork, we store the artwork number by each maker, the type of artwork, the title, the artwork’s dimensions – height, width, and whether it is available for copyright reproduction.
Customers can be individuals or companies. We need to know contact information for each customer – first and last name, company name (if relevant), postal address, city, country, email address, and telephone numbers. At any given time, multiple customers may have proposal permission for a particular artwork.
Relationships & Constraints
Customer
Proposal
Artwork
Owner
Maker
makes
for
owns
by
Attributes
Bridgman Art Library manages copyright of artworks. Customer organisations that wish to use a copyrighted image can pay a fee to Bridgman Art Library, who will then provide a high-quality image for reproduction.
Customers must register a proposal for the use of the artwork, containing information about the intended use, intended market and intended duration of use. The customer also nominates the price they are willing to pay for their proposed use. The date the proposal was made is also stored.
Bridgman Art Library needs to be able to identify who has owned the artwork at any point in time since the artwork’s creation. Each artwork may have been owned by many owners over time but can only have one owner at any particular moment. About each owner we need to know the address, and country, and name.
A maker (creator of artwork) can be an individual, a company, or an atelier. The database needs to be able to store the maker’s first and last name, nationality, birth year and death year, and the atelier or company name (if known). Bridgman records a single maker for each artwork.
For each artwork, we store the artwork number by each maker, the type of artwork, the title, the artwork’s dimensions – height, width, and whether it is available for copyright reproduction.
Customers can be individuals or companies. We need to know contact information for each customer – first and last name, company name (if relevant), postal address, city, country, email address, and telephone numbers. At any given time, multiple customers may have proposal permission for a particular artwork.
Attributes
Customer
Proposal
Artwork
Owner
Maker
makes
for
owns
by
Address
Name
Country
ID
Start date
End date
First name
Last name
Address
City
Country
Phone no.
ID
Intended use
Intended market
Use duration
Price
Date
ID
First name
Last name
type
nationality
Atelier / company name
Birth date
Birth year
ID
number
type
title
reproducible?
dimension
height
width
Relational Algebra
Summary
The following tables are part of a database for a role-playing game:
player (playerid, playername, experience)
playeritem ( FK playerid , FK itemid , quantity)
item (itemid, itemname, value, weight, colour
List the names and values of items bought by players with experience level “Adventurer” who are not named “colton”.
The following tables are part of a database for a role-playing game:
player (playerid, playername, experience)
playeritem ( FK playerid , FK itemid , quantity)
item (itemid, itemname, value, weight, colour
List the names and values of items bought by players with experience level “Adventurer” who are not named “colton”.
πitemname, value(σexperience = 'Adventurer' ∧ name != 'colton' (player ⋈ playeritem ⋈ item))
The following tables are part of a database for a role-playing game:
player (playerid, playername, experience)
playeritem ( FK playerid , FK itemid , quantity)
item (itemid, itemname, value, weight, colour
Find names which are shared by at least one player and at least one item.
The following tables are part of a database for a role-playing game:
player (playerid, playername, experience)
playeritem ( FK playerid , FK itemid , quantity)
item (itemid, itemname, value, weight, colour
Find names which are shared by at least one player and at least one item.
πplayername(player) ∩ πitemname (item)
SQL
SQL Questions
Keywords
SELECT colA, …
FROM TableA …
WHERE … AND … OR
GROUP BY
HAVING
ORDER BY … ASC / DESC
LIMIT …
Types of Joins
Subqueries & Set operations
Sub-query operators
�Set operations (which are supported on MySQL)
Unary Joins, Aliases
From Tutorial 5:
Find the employees whose salary is less than half that of their managers.
This requires a unary join. Use the ‘AS’ keyword as an alias for the table
SELECT Emp.EmployeeName
FROM Employee AS Emp
INNER JOIN Employee AS Boss
ON Emp.BossID = Boss.EmployeeID
WHERE Emp.EmployeeSalary < (Boss.EmployeeSalary / 2);
The golf case study
Every year, golf clubs and sporting organisations around Victoria run a series of tournaments for junior (under-18) golfers. Golf Victoria, the peak body for the sport in Victoria, has decided to implement a central database to record participants and scores in all these junior tournaments, to help them award prizes to deserving players at the end of the season, and also to track participation levels in competitive junior golf for strategic planning purposes
If you’re not familiar with golf, here’s what you need to know:
In these questions, we assume that tournaments only have one round, and that no tournaments carry on over the New Year. Write a single SQL statement for each question – subqueries are allowed from question 21 onwards.
Write a query to return the names of tournaments, played at courses in the suburb of Heatherton, where Bella Sandbury ranked in the top ten.
Write a query to return the names of tournaments, played at courses in the suburb of Heatherton, where Bella Sandbury ranked in the top ten.
Hint: Use SELECT DISTINCT to ensure your answer doesn’t contain duplicate rows.
SELECT DISTINCT TournamentName
FROM Player NATURAL JOIN PlayerCompetes NATURAL JOIN Tournament
NATURAL JOIN Course
WHERE CourseSuburb = ‘Heatherton’
AND PlayerFirstName = ‘Bella’
AND PlayerLastName = ‘Sandbury’
AND Rank <= 10;
SQL Question 2
List the first and last names of players who have played in exactly the same set of tournaments as at least one other player.
Write a query to List the first and last names of players who have played in exactly the same set of tournaments as at least one other player.
SELECT DISTINCT P1.FirstName, P1.LastName
FROM Player AS P1, Player AS P2
WHERE P1.PlayerID <> P2.PlayerID
AND 1 NOT IN (SELECT COUNT(*)
FROM (SELECT TournamentID
FROM PlayerCompetes
WHERE PlayerID = P1.PlayerID
UNION ALL
SELECT TournamentID FROM PlayerCompetes
WHERE PlayerID = P2.PlayerID) AS a
GROUP BY TournamentID);
Query Optimisation
Remember this?
Tips
You should be able to answer all those kind of questions!!! They may not be directly examined but they are very important when you analyse a given pipeline!!!
Tips
3. Be careful for usability of indexes!
4. Reduction factor decides cost and result size:
Consider the relations:
A (Aid, ...) – 2500 tuples, 10 tuples per page
B (Bid, FK Aid , ...) – 300 tuples, 50 tuples per page
C (Cid, FK Bid , ...) – 2000 tuples, 20 tuples per page
A clustered B+ tree index exists on the Aid column in relation A, with 100 index pages.
Assume that two passes are required to sort. For all join results, 10 tuples can be stored per page.
Calculate the cost of the following plan.
Number of resulting pages for B JOIN C
Cost of NLJ
Cost of accessing A
Cost of sorting B JOIN C
Cost of SMJ
Total cost
Query Optimisation solutions
| ntuples | tup/pg | pages | Index | #vals | pages |
Student | 20,000 | 20 | 1,000 | UC hash degreename | 40 | 50 |
SS | 50,000 | 50 | 1,000 | C B+ stuId | | 50 |
Subject | 1,000 | 10 | 100 | UC B+ level | 10 | 50 |
Stu X SS | ??? | 100 | ??? | |
SS X Sub | ??? | 100 | ??? | |
Index: 50 pages
Sorting 2 passes
C = clustered; UC = unclustered
Extract info from text
Steps
Steps
Steps
For step 2, what do we subtract in the formula?
CostSMJ = NPages(Sub selection) + NPages(SS)
+ 2* NPages(Sub selection)* num_passes(Sub selection)
+ 2* NPages(SS)* num_passes(SS)
Steps
Don’t forget to add up all the costs to get the total!
What’s the difference?
| Index | #vals | pages |
Student | UC hash degreename | 40 | 50 |
SS | C B+ stuId | | 50 |
Subject | UC B+ level | 10 | 50 |
Normalisation
Why do we normalise relations?
How to normalise:
How to normalise:
To achieve 1NF
To achieve 2NF
To achieve 3NF
Normalise to 3NF
Consider the following relation:
MealsOrdered (OrderID, CustomerID, CustomerName, DishID, DishName, DishPrice)
It has the following functional dependencies:
OrderID → CustomerID, CustomerName
CustomerID → CustomerName
DishID → DishName, DishPrice
1NF
MealsOrdered (OrderID, CustomerID, CustomerName, DishID, DishName, DishPrice)
OrderID → CustomerID, CustomerName
CustomerID → CustomerName
DishID → DishName, DishPrice
As the relation is already in 1NF, because there are no repeating groups, we can directly check if it is in 2NF or not.
2NF
MealsOrdered (OrderID, CustomerID, CustomerName, DishID, DishName, DishPrice)
OrderID → CustomerID, CustomerName
CustomerID → CustomerName
DishID → DishName, DishPrice
The functional dependency OrderID → CustomerID, CustomerName shows that CustomerID and CustomerName is determined by part of the key. Similarly for DishID → DishName, DishPrice. Hence, the relation is not in 2NF. It can be decomposed as:
OrderDish (FK OrderID, FK DishID)
Order (OrderID, CustomerID, CustomerName)
Dish (DishID, DishName, DishPrice)
3NF
OrderDish (FK OrderID, FK DishID)
Order (OrderID, CustomerID, CustomerName)
Dish (DishID, DishName, DishPrice)
OrderID → CustomerID, CustomerName
CustomerID → CustomerName
DishID → DishName, DishPrice
We can observe that the FD CustomerID → CustomerName violates 3NF, as it is a transitive dependency. We further decompose this relation to:
OrderDish (FK OrderID, FK DishID)
Order (OrderID, FK CustomerID)
Dish (DishID, DishName, DishPrice)
Customer (CustomerID, CustomerName)
Normalise this table into 3NF
Normalise this StaffBranch table into 3NF
The dependencies are:
A candidate key is (StaffNo, BranchNo)
Normalisation Solution
Normalisation Solution:
Normalise this StaffBranch table into 3NF
The dependencies are:
A candidate key is (StaffNo, BranchNo)
Remember: We must check if ALL previous normal forms are satisfied before trying to put it into 3NF
Which NF is not satisfied?
What do we do to fix this?
Achieving 1NF
Original Table is StaffBranch(staffNo PK, name, position, salary, branchNo PK, branchAddress, telNos)
Need to make telNo part of the PK to be able to store multiple telNos for each branch!
So we get:
StaffBranch(staffNo PK, name, position, salary, branchNo ?, branchAddress)
?(branchNo ?, telNo ?)
After making the table, think about what might be a good name for this table?
Which attribute should we make the PK and/or which one the FK?
Achieving 1NF
Original Table is StaffBranch(staffNo PK, name, position, salary, branchNo PK, branchAddress, telNos)
Need to make telNo part of the PK to be able to store multiple telNos for each branch!
So we get:
StaffBranch(staffNo PK, name, position, salary, branchNo PK, branchAddress)
?(branchNo PFK, telNo PK)
After making the table, think about what might be a good name for this table?
StaffBranch(staffNo PK, name, position, salary, branchNo PK, branchAddress)
BranchTelephone(branchNo PFK, telNo PK)
Now, it is in 1NF
Is this in 3NF yet?
StaffBranch(staffNo PK, name, position, salary, branchNo PK, branchAddress)
BranchTelephone(branchNo PFK, telNo PK)
The dependencies are:
A candidate key is (StaffNo, BranchNo)
Achieving 2NF
StaffBranch(staffNo PK, name, position, salary, branchNo PK, branchAddress)
BranchTelephone(branchNo PFK, telNo PK)
There are attributes that depend only on a PART of the PK (partial dependencies)
How do we remove these partial dependencies to put it into 2NF?
staffNo → name, position
position → salary
branchNo → branchAddress, telNos
Achieving 2NF
StaffBranch(staffNo PK, name, position, salary, branchNo PK, branchAddress)
BranchTelephone(branchNo PFK, telNo PK)
There are attributes that depend only on a PART of the PK (partial dependencies)
How do we remove these partial dependencies to put it into 2NF?
staffNo → name, position
position → salary
branchNo → branchAddress, telNos
Need to decompose staff details and branch details into their own relations!
StaffBranch(staffNo PFK, branchNo PFK)
?(staffNo PK, name, position, salary)
? (branchNo PK, branchAddress)
BranchTelephone(branchNo PFK, telNo PK)
Remember: We still need to keep the original table StaffBranch so that we still have information which links staff and branch (we still want to know which staff work at which branch)
What should we name relations?
Are we in 3NF yet?
StaffBranch(staffNo PFK, branchNo PFK)
Staff(staffNo PK, name, position, salary)
Branch (branchNo PK, branchAddress)
BranchTelephone(branchNo PFK, telNo PK)
Now all the relations are in 2NF
Are all the relations in 3NF? Why or why not?
The dependencies are:
A candidate key is (StaffNo, BranchNo)
Are we in 3NF yet?
StaffBranch(staffNo PFK, branchNo PFK)
Staff(staffNo PK, name, position, salary)
Branch (branchNo PK, branchAddress)
BranchTelephone(branchNo PFK, telNo PK)
Now all the relations are in 2NF
Are all the relations in 3NF? Why or why not?
The dependencies are:
A candidate key is (StaffNo, BranchNo)
Nope! We’ve got a transitive dependency in the Staff table.
A non-key attribute determines another non-key attribute (position → salary)
Need to separate this out into its own relation
Staff(staffNo PK, name, position ?)
? (position ?, salary)
What should we name the table? Where do we put the FK and PKs?
Achieving 3NF
StaffBranch(staffNo PFK, branchNo PFK)
Staff(staffNo PK, name, position FK)
Position(position PK, salary)
Branch (branchNo PK, branchAddress)
BranchTelephone(branchNo PFK, telNo PK)
Are we done yet?
Are all the relations in 3NF?
The dependencies are:
A candidate key is (StaffNo, BranchNo)
We’ve now achieved 3NF!
StaffBranch(staffNo PFK, branchNo PFK)
Staff(staffNo PK, name, position FK)
Position(position PK, salary)
Branch (branchNo PK, branchAddress)
BranchTelephone(branchNo PFK, telNo PK)
Transactions
DB Transactions + ACID
(for interest only)
Transactions
Transactions
Transactions
Alice’s Transaction
Bob’s Transaction
Time
Alice’s Transaction
Bob’s Transaction
OR
Correct
(no issue with Inconsistent Retrieval problem)
Incorrect
(Inconsistent Retrieval)
Alice’s Transaction
Bob’s Transaction
Transactions
Question:
A DBMS which strictly implements ACID compliant transactions can never encounter the ‘Inconsistent retrieval problem’.
Which ACID property is primarily responsible for preventing this problem? Explain how it prevents the inconsistent retrieval problem.
Transactions solutions
Transactions
A:
C:
I:
D:
Transactions
Solution:
Isolation
Isolation guarantees that concurrent execution of transactions leaves the database in the same state as if the transactions were executed sequentially (one after another)
A: Atomic
C: Consistent
I: Isolated
D: Durable
Distributed DBs
Distributed DBs PRO/CON
Distributed DBs types
Question:
As business grew over time for hiTech Computer Repairs Inc, the company has opened multiple new stores in Australia, and also recently spread to Europe. The management at hiTech Headquarters is interested in upgrading the database associated with the `PC repair services` booking system from centralised to a distributed structure. HiTech repair centers in Australia are unlikely to regularly need access to repair records for European customers, and vice versa.
Which of the following type of distributed database would be best suited for this case: a) Vertically partitioned b) Horizontally Partitioned c) Fully Replicated DB
Distributed Solutions
Answer:
Horizontally Partitioned.
No SQL
NoSQL conceptually
NoSQL Pros/cons
Question:
FaceTube™ is a new online worldwide social video sharing platform that you and your friend have started.
Your friend asks if you think a No-SQL based database for storing the metadata + comments for video uploads is a good idea.
Discuss some pros/cons for using a No-SQL database for this scenario. Your answer should make reference to the properties of the data being stored and BASE.
NoSQL solutions
Answer:
Yes, NoSQL is likely a good choice here
Pros:
Cons:
Questions & Answers
Feedback form + sign ups
Feedback form