Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
INFO20003 Database Systems
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?
A)
B)
C)
D)
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.
Slide is part of Slideshow
Tram door is part of Tram
Employee Office
Music track Album
Department Employee
address
first name
suburb postcode
manager ID ID
name location last name
Phone number
desk phone
home phone
mobile
phone
INFO20003 Miscellaneous practice questions Page 2 of 12
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