INFO20003 Database Systems

Miscellaneous practice questions

Data modelling

1.    Select appropriate MySQL data types for the following pieces of information:

a.    A person’s last name

b.   The number of items purchased in a particular transaction

c.    The average number of items purchased

d.   A phone number

e.    When a tap-and-go purchase took place

f.    Whether an order has been completed or not

g.    The unit price of an item at a convenience store

h.   The unit price of a vehicle at a car dealership

i.     A person’s gender, stored according to Australian Government guidelines – M (Male), F (Female) or X (Indeterminate/Intersex/Unspecified)

j.     A person’s Aboriginal and Torres Strait Islander status – Aboriginal, Torres Strait Islander, both, or neither

2.   Assuming that common sense applies, which one of the following ER model fragments is most believable?

3.   A  student  was  asked  to  model  a  typical  employee-department  scenario  by  drawing  a conceptual  ER  model  in  Chen’s  notation.  Their  model,  shown  below,  has  a  number  of mistakes. Identify the mistakes and redraw the model correctly.

4.    Imagine you are developing a database to store information about marriages.

a.    Using Chen’s notation, draw a “person” entity with appropriate attributes and a “married to” relationship that records who is currently married to whom. What type of relationship is this?

b.    Based on your answer to part a, draw a model that stores not only current marriages, but also all previous marriages.

c.    Based  on your  answer to  part  b,  draw  a  model that  stores  all  current  and  previous marriages, including the date of marriage and, for previous marriages, date of dissolution.

d.    Can your answer to part c handle the situation where the same two people marry, divorce and later marry again? What do you need to change to handle this?

e.    In certain societies (both historical and modern), a person can be married to many people at the same time. Draw another model to handle this scenario, based on your answer to part d.

f.     Resolve all five Chen’s notation models into physical models using Crow’s foot notation. Take particular care that your physical models for parts d and e can handle the scenarios explained in those parts.

Relational algebra

5.   The following tables are part of a database for a role-playing game:

player (playerid, playername, experience)

FK              FK

playeritem (playerid, itemid, quantity)

item (itemid, itemname, value, weight, colour)

Write relational algebra expressions to answer the following questions:

a.    List all the possible experience levels of players.

b.   Show all information about gold-coloured items with a value of 20 coins.

c.    List the names of players who have bought a “Vine Knife” .

d.    List the names and values of items bought by players with experience level “Adventurer” who are not named “coulter” .

e.    Find names which are shared by at least one player and at least one item.

SQL

6.    Consider the following tables:

FK

Employee (EmployeeID, Name, Phone, DepartmentID)

Department (DepartmentID, DepartmentName, DepartmentFloor)

This SQL query returns all employees who work on the fifth floor:

SELECT Name

FROM Employee NATURAL JOIN Department

WHERE DepartmentFloor = 5;

Rewrite the query using the following SQL techniques:

a.    INNER JOIN

b.    Cross product

c.    Outer join

d.    IN

e.    EXISTS

f.    ANY

7.   Which two of these fragments cannot appear as part of a SELECT query?

a. SELECT COUNT(*)

b. WHERE COUNT(*) > 1

c. GROUP BY COUNT(*)

d. HAVING COUNT(*) > 1

e. ORDER BY COUNT(*)

8.    Consider the following table definitions:

FK

Employee (EmployeeID, EmployeeName, Phone, BuildingID, RoomNumber) Building (BuildingID, BuildingName, NumFloors)

Not  every  employee  is  located  in  a  room; the  BuildingID  and  RoomNumber  columns on Employee may be NULL.

Explain what is wrong with the following SQL queries, and suggest a correction. (Some of the queries contain invalid syntax, and others are technically valid but conceptually incorrect.)

a. SELECT *

FROM Building

OUTER JOIN Employee ON Building.BuildingID = Employee.BuildingID;

b. SELECT EmployeeName, BuildingName FROM Building INNER JOIN Employee;

c. SELECT EmployeeName, BuildingID FROM Building

INNER JOIN Employee ON Building.BuildingID = Employee.BuildingID;

d. SELECT EmployeeName, BuildingID, COUNT(EmployeeID) FROM Building NATURAL JOIN Employee

GROUP BY BuildingID;

e. (We were asked to find how many employees are in each occupied single-storey building.) SELECT BuildingName, COUNT(EmployeeID)

FROM Building NATURAL JOIN Employee

GROUP BY BuildingID

HAVING NumFloors = 1;

f. (We were asked to find the number of employees in every occupied building, placing taller buildings before shorter buildings in the result set.)

SELECT BuildingName, COUNT(*)

FROM Building NATURAL JOIN Employee

ORDER BY NumFloors DESC

GROUP BY BuildingName;

g. (We were asked to find all buildings which are not occupied by any employees.) SELECT BuildingName

FROM Building

WHERE BuildingID NOT IN (SELECT BuildingID

FROM Employee);

Indexes and query cost

9.    Consider the following table:

Employee (EmployeeID, Name, Age, DepartmentID)

Suppose the following three queries are executed frequently on this table:

i. SELECT Name FROM Employee

WHERE Age > 30 AND DepartmentID = 5;

ii. SELECT EmployeeID, Name FROM Employee

WHERE Age = 65;

iii. SELECT Name, Age FROM Employee

WHERE DepartmentID = 7;

Out of the above queries, which ones (if any) could potentially make use of:

a.    A clustered B-tree index on Age

b.   A hash index on Age

c.    A hash index on DepartmentID

d.   A clustered B-tree index on (DepartmentID, EmployeeID)

e.   A hash index on (EmployeeID, DepartmentID)

f.    An unclustered B-tree index on (DepartmentID, Age)

10. The Employee table from question 9 has 50 data pages, with 20 tuples per page. Employees are between 25 and 65 years old, and there are 20 different departments. All indexes have 10 index pages.

a.    Calculate the reduction factors for each of the three queries.

b.    Compute the estimated cost of the best plan for each query, assuming that an unclustered B-tree index on (DepartmentID, Age) is the only index available. Discuss and calculate alternative plans.

c.    Compute the estimated cost of the best plan for each query, assuming that a clustered B- tree  index  on  (Age,  DepartmentID)  is the  only  index  available.  Discuss  and  calculate alternative plans.

Query optimisation

11. Consider the relations:

A (Aid, …) – 2500 tuples, 10 tuples per page

FK

B (Bid, Aid, …) – 300 tuples, 50 tuples per page

FK

C (Cid, 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, and determine the final result size.

SMJ

⋈

Page NLJ

⋈

B C

A(index

scan)

12. Consider the following SQL statement:

SELECT *

FROM Dept, Emp, Finance

WHERE Dept.did = Finance.did AND Dept.did = Emp.did;

The sorting of a relation can be done in 2 passes. A page holds 10 tuples. There are 1000 employees, with a total of 100 departments. Each department has a corresponding financial

record. There is a clustered B+ tree index on Emp.did which contains 50 pages. Calculate the cost of the following plan, and determine the final result size.

SMJ

⋈

SMJ

⋈

Emp (index

scan)

Dept Finance

Normalisation

13. Consider the following relation:

MealsOrdered (OrderID, CustomerID, CustomerName, DishID, DishName, DishPrice) It has the following functional dependencies:

OrderID t CustomerID, CustomerName

CustomerID t CustomerName

DishID t DishName, DishPrice

a.    Label these functional dependencies as partial, transitive or neither.

b.    Normalise this relation to second normal form. Write down the functional dependencies that remain.

c.    Normalise this relation to third normal form.

14. Selected rows from the OrderItem table of an online retailer are shown below.

(OrderID, ItemID) is the primary key. Several functional dependencies exist on this table:

CustomerID t CustomerPostcode

OrderID t CustomerID

OrderID, ItemID t ItemQuantity, CanDispatchFrom

Normalise the table to third normal form. Write the normalised tables in a textual format, as in:

FK

TableName (PrimaryKey, Column, ForeignKey)

AnotherTable (PrimaryKey, Column, AnotherColumn)

15. The following table stores information about players in a sports league. Five of the rows are shown below – the actual table has many more rows.