INFO20003 Week 3 Lab – Solutions Section 2: Drawing a physical model in MySQL Workbench
◆ Task 2.1 Using MySQL Workbench, draw a physical model for the “cinema” case study from the Week 3 tutorial. You should already have developed a conceptual and logical model in class.
There is a screencast video on LMS showing how this model was drawn.
Continued over page
INFO20003 LA-Wk3 1 © The University of Melbourne
Section 3: MySQL data types
◆ Task 3.1 Choose a suitable data type (or data types) for each of the following attributes. Where necessary, specify the length and precision (e.g. VARCHAR(50) instead of VARCHAR).
Many different data types are equally valid for some of these attributes.
Attribute
Data type
Why did you select this data type?
Bank account balance
DECIMAL(10,2)
This would allow for a maximum balance of 99,999,999.99. There is no rounding error with the DECIMAL type, which is important for currency applications.
Your full name
VARCHAR(80)
Alternatively spread over three fields:
FirstName VARCHAR(30)
MiddleNames VARCHAR(40)
LastName VARCHAR(40)
There is no single correct answer here.
In Victoria, you cannot give a newborn child first and middle names that are longer than 38 characters in total, including spaces. The surname is also limited to 38 characters. (source)
People born in other states or countries might have longer names, but the line needs to be drawn somewhere.
Home address
VARCHAR(100)
Typically spread across several fields:
VARCHAR(…) Address1 30 Address2 30 Suburb 20 Postcode 10 State 12 Country 25
Again, there is no single correct way to handle addresses. However, it is usual to split addresses in several fields, to permit filtering according to state or country, and to make it easier to print addresses on envelopes correctly.
(allowing for international postcodes)
Postcode (Australian)
INT
Australian postcodes are four-digit numbers (0000 to 9999). INT UNSIGNED could also be used.
LinkedIn page
VARCHAR(80)
Because the domain name is constant (linkedin.com) it can be smaller than the general “website” attribute below
Website
VARCHAR(128)
You may have selected a different length
When a text message was sent
DATETIME
We want to be able to distinguish messages by time as well as date
INFO20003 LA-Wk3 2 © The University of Melbourne
Attribute
Data type
Why did you select this data type?
When a person started working for a company
DATE
Time of day is not important
Duration of a song
TIME
TIME can be used to store elapsed time or durations.
Unimelb room number (e.g. 109, 228A, G09)
CHAR(4)
Room numbers are a very short string that is always three or four characters.
Unimelb assignment grade (H1, H2A, H2B, H3, P, N)
ENUM(‘H1′,’H2A’, ‘H2B’,’H3′,’P’,’N’)
Assignment grades are limited to this fixed list which is unlikely to change
A comment on a news article
TEXT
It is free-form text that could be quite long
INFO20003 LA-Wk3 3 © The University of Melbourne