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
Copyright By PowCoder代写 加微信 powcoder
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.
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)
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
When a text message was sent
VARCHAR(128) DATETIME
You may have selected a different length
We want to be able to distinguish messages by time as well as date
INFO20003 LA-Wk3 2 © The University of Melbourne
When a person started working for a company
Duration of a song
Unimelb room number (e.g. 109, 228A, G09)
Unimelb assignment grade (H1, H2A, H2B, H3, P, N)
A comment on a news article
ENUM(‘H1′,’H2A’, ‘H2B’,’H3′,’P’,’N’)
Why did you select this data type?
Time of day is not important
TIME can be used to store elapsed time or durations.
Room numbers are a very short string that is always three or four characters.
Assignment grades are limited to this fixed list which is unlikely to change
It is free-form text that could be quite long
INFO20003 LA-Wk3 3 © The University of Melbourne
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com