INFO20003 Exam – Semester 1
Suggested Solutions and Marking Scheme
and July 2020
Copyright By PowCoder代写 加微信 powcoder
0.1 ER Modelling I (Exam Q8 – 15 marks) 0.1.1 VicRoads
VicRoads wants to upgrade its database and it hired you to be in charge of the design of the roadwor- thiness certificates section. The relevant information is as follows:
• Every vehicle has a registration number, and each vehicle is of a specific category.
• VicRoads consider several categories, and each category is identified by code (e.g., LV for light
vehicles), maximum capacity, and average weight.
• Several certified mechanics can issue roadworthiness certificates. You need to store the name, driver’s license, address, phone number, and cost per hour of each mechanic.
• Each mechanic is an expert on one or more vehicle category(ies), and his or her expertise may overlap with that of other mechanics. This information about mechanics must also be recorded.
• All mechanics belong to a union. You must store the union membership number of each mechanic. You can assume that each mechanic is uniquely identified by her/his driver’s license as every mechanic needs one to test the vehicle on the road.
• VicRoads has several tests that are required whenever a vehicle is to be sold, re-registered, or to clear some defect notices. Each test has a code (e.g., W&T) and a name (e.g., wheels and tyres), and a minimum score.
• VicRoads requires to store the issued certificates (one per vehicle). Each certificate comprises a set of tests by a given mechanic. The information needed for each certificate is the date, the number of hours the mechanic spent doing the test, and the score the vehicle received on each test.
Draw a conceptual model using Chen’s notation for the roadworthiness tests section. Be sure to indicate the various attributes of each entity and relationship set; also specify the key and participation constraints for each relationship set. Be sure to write down any assumptions you make.
Figure 1: VicRoads Conceptual Model
0.1.2 Record Label
A record label company wants to upgrade its albums database and it hires you to do the job. The relevant information is as follows:
• They want to keep information about singers, their names (which is unique), active since, whether it’s a band or not.
• For each album, the singer (band), the year it was recorded, its unique title, its cover photo, and its price must be stored.
• Genres and their hierarchy are also stored. Some genres have emerged from others. For example, a genre can be the parent of 0 or many genres, and another genre can be the child of 0 or 1 genres. Each genre is identified by a unique name.
• A given album may belong to more than one genre, which could be at any level in the hierarchy.
• Songs of each album are to be stored. For each song, the company stores its title and duration, and the album/albums it belongs to, i.e., all songs must be in some album, but can be in many albums.
• Finally, the record company keeps the information about the managers. For each manager, the company keeps the name, address, the total amount of dollars owed. Managers can manage multiple artists and genres. We still store the managers even if they are no longer managing genres/artists.
Draw a conceptual model using Chen’s notation for the record label company. Be sure to indicate the various attributes of each entity and relationship set; also specify the key and participation constraints for each relationship set. Be sure to write down any assumptions you make.
Assumptions:
Figure 2: Record Company Conceptual Model
• Album can be authored by more than one band/singer, e.g., compilation albums.
Marking Scheme
Total marks: 15
• Entities: 5 marks
1 mark for each table
Deduct 1 mark for each unnecessary entity
• Attributes: 5 marks
Max of 1 mark for each entity’s attributes (summed)
– 1 mark for each entity that has ALL attributes – 0.5 for each entity that has some attributes
• Correct Key Constraints: 2 marks
• Correct Participation Constraints: 2 marks
• Legibility: 1 mark
MARKER NOTE: Ensure that before taking marks off, you check if they have any reasonable as- sumptions which allows them to change the constraints. E.g. do they make the assumption that Album has only one band/singer?
0.1.3 Toy Library (Supp. Exam)
You are hired to design the database model of a new toy library to be open here in Melbourne. Toy libraries are non-profit organisations that give toys on loan to their members for a fixed period of time. They rely on partnerships and volunteers to work. Memberships are on a yearly basis and they can be renewed. The database application must store information as follows:
• Every member has a unique ID, first name, last name and the date he/she joined the library. A member should volunteer once a year to help arrange toys. A flag that indicates whether the member has already volunteered in his/her membership year must be stored.
• The names, sex, and dates of birth of each member’s children are also stored in the system.
• Toys are classified into categories, have a unique ID, name and a short description.
• A toy may be comprised of parts that are also stored. For example, a toy can be a ”Polar Igloo Set” and one of its parts is a ”Chair”.
• Each member can have up to 5 toys on loan, and at least two of them must be from the ”Puzzles” category. The system must keep a record of the current loans of each member as well as the loan history. The loan date, due date and the number of renewals of a toy must be stored. Also, a flag indicating whether a toy has been returned is needed.
• An important part of a toy library is sponsorship. The system must store information about the sponsor’s name and address. More importantly, the system must store which toys were donated by the sponsor and when.
Draw a conceptual model using Chen’s notation for the toy library. Be sure to indicate the various attributes of each entity and relationship set; also specify the key and participation constraints for each relationship set. Be sure to write down any assumptions you make.
Figure 3: Toy Library Conceptual Model
0.2 ER Modelling II (Exam Q9 – 8 marks) 0.2.1 Option 1
Write the CREATE TABLE statements resulting from the following conceptual model:
Figure 4: DDL Option 1
Be sure to specify primary and foreign keys. You NEED to specify whether the fields are NULL/NOT NULL. Where the data type is not obvious, feel free to choose an appropriate data type.
CREATE TABLE A (
aid INT NOT NULL,
PRIMARY KEY (aid)
CREATE TABLE B (
bid INT NOT NULL,
PRIMARY KEY (bid)
CREATE TABLE C (
cid INT NOT NULL,
aid INT NOT NULL UNIQUE,
bid INT NOT NULL,
PRIMARY KEY (cid),
FOREIGN KEY (aid) REFERENCES A (aid),
FOREIGN KEY (bid) REFERENCES B (bid)
0.2.2 Option 2
Write the CREATE TABLE statements resulting from the following conceptual model:
Figure 5: DDL Option 2
Be sure to specify primary and foreign keys. You NEED to specify whether the fields are NULL/NOT NULL. Where the data type is not obvious, feel free to choose an appropriate data type.
CREATE TABLE B (
bid INT NOT NULL,
PRIMARY KEY (bid)
CREATE TABLE C (
cid INT NOT NULL,
PRIMARY KEY (cid)
// need extra table rather than 2 optional FKs in A, since that
// would allow one to be null. Also need aid in this relationship so that we
// only allow for ternary relationships (a solution with a FK A to R and R with
// bid + cid only allows for a binary relationship between B+C in R
CREATE TABLE R (
aid INT NOT NULL,
bid INT NOT NULL,
cid INT NOT NULL,
// a needs to be unique
PRIMARY KEY (aid),
FOREIGN KEY (aid) REFERENCES A (aid),
FOREIGN KEY (bid) REFERENCES B (bid),
FOREIGN KEY (cid) REFERENCES C (cid)
CREATE TABLE A (
aid INT NOT NULL,
PRIMARY KEY (aid)
Note: other options for cases with more key constraints like option #4 won’t work here. . .
0.2.3 Option 3
Write the CREATE TABLE statements resulting from the following conceptual model:
Figure 6: DDL Option 3
Be sure to specify primary and foreign keys. You NEED to specify whether the fields are NULL/NOT NULL. Where the data type is not obvious, feel free to choose an appropriate data type.
CREATE TABLE B (
bid INT NOT NULL,
PRIMARY KEY (bid)
CREATE TABLE C (
cid INT NOT NULL,
PRIMARY KEY (cid)
CREATE TABLE A (
aid INT NOT NULL,
bid INT NOT NULL,
cid INT NOT NULL,
PRIMARY KEY (aid),
FOREIGN KEY (bid) REFERENCES B (bid),
FOREIGN KEY (cid) REFERENCES C (cid)
0.2.4 Option 4
Write the CREATE TABLE statements resulting from the following conceptual model:
Figure 7: DDL Option 4
Be sure to specify primary and foreign keys. You NEED to specify whether the fields are NULL/NOT NULL. Where the data type is not obvious, feel free to choose an appropriate data type.
CREATE TABLE A (
aid INT NOT NULL,
PRIMARY KEY (aid)
CREATE TABLE B (
bid INT NOT NULL,
PRIMARY KEY (bid)
CREATE TABLE C (
cid INT NOT NULL,
PRIMARY KEY (cid)
CREATE TABLE R (
aid INT NOT NULL UNIQUE,
bid INT NOT NULL UNIQUE,
cid INT NOT NULL UNIQUE,
PRIMARY KEY (aid, bid, cid),
FOREIGN KEY (aid) REFERENCES A (aid),
FOREIGN KEY (bid) REFERENCES B (bid),
FOREIGN KEY (cid) REFERENCES C (cid)
Alternative Solution I
CREATE TABLE A (
aid INT NOT NULL,
rid INT UNIQUE,
PRIMARY KEY (aid),
FOREIGN KEY (rid) REFERENCES B (rid),
FOREIGN KEY (rid) REFERENCES C (rid)
CREATE TABLE B (
bid INT NOT NULL,
rid INT UNIQUE,
PRIMARY KEY (bid),
— FOREIGN KEY (rid) REFERENCES A (rid),
— FOREIGN KEY (rid) REFERENCES C (rid)
CREATE TABLE C (
cid INT NOT NULL,
rid INT UNIQUE,
PRIMARY KEY (cid),
— FOREIGN KEY (rid) REFERENCES A (rid),
— FOREIGN KEY (rid) REFERENCES B (rid)
Alternative Solution II [requires constraining ids]
CREATE TABLE A (
aid INT NOT NULL,
rid INT UNIQUE,
PRIMARY KEY (aid),
— This requires that bid and cid are
— constrained…
FOREIGN KEY (rid) REFERENCES B (bid),
FOREIGN KEY (rid) REFERENCES C (cid)
CREATE TABLE B (
bid INT NOT NULL,
PRIMARY KEY (bid),
CREATE TABLE C (
cid INT NOT NULL,
PRIMARY KEY (cid),
Marking Scheme
Total marks: 8
• Correct implementation of Key Constraints (FK positioning + UNIQUE) : 3 marks
– No demonstration of dealing with key constraints (even if many are ’incidentally’ correct): -2 marks
– Didn’t capture ternary relationship constraint that one set of (a,b,c) participates only once: -1 mark
– Would have received 0 marks for this and next criteria (ie 0/6), but did have some FK notation present: 1 mark
• Correct implementation of Participation Constraint (NULL/NOT NULL): 3 marks
– No demonstration of dealing with participation constraints (even if many are ’incidentally’
correct): -2 marks
– Allowed for other binary relations in model (ie no participation constraint that ALL of a,b,c participate in a relationship): -1 mark
• Working table implementation (can store attributes): 1 marks • Uses correct syntax + PKs for tables: 1 mark
Australia has carried out several censuses. The following diagram shows the tables corresponding to preferred means of transportation, residency location, and origins and destinations of the frequent trips.
Figure 8: Australia’s Censuses Model
Write a single SQL statement to correctly answer each of the following questions. DO NOT USE VIEWS or VARIABLES to answer questions. Query nesting is allowed.
0.4 SQL I (Exam Q10 – 2 marks) 0.4.1 Option 1
How many different people, across all censuses, have said they live in Brunswick?
SELECT COUNT(DISTINCT R.PID)
FROM residency AS R
NATURAL JOIN suburb AS S
WHERE S.SName = ’Brunswick’;
0.4.2 Option 2
How many different people, across all censuses, have said they prefer public transport?
SELECT COUNT(DISTINCT PT.PID)
FROM preferredtransportation AS PT
NATURAL JOIN transportmeans AS TM
WHERE TM.TName = ’Public Transport’;
0.4.3 Option 3
How many different people, across all censuses, have said they frequently travel to Brunswick?
SELECT COUNT(DISTINCT FT.PID)
FROM frequenttrip AS FT
INNER JOIN suburb AS S ON FT.to = S.SID
WHERE S.SName = ’Brunswick’;
Marking Scheme
Total marks: 2
• Correct Answer: 2 marks
– Missing COUNT: -0.5
– Incorrect JOIN: -1
– Missing/Incorrect condition: -0.5 – Missing DISTINCT: -0.5
– Other error(s): -0.5 (each)
0.5 SQL II (Exam Q11 – 3 marks) 0.5.1 Option 1
List the name of the suburbs and the number of people who lives in each, according to the 2016 census, ordered from the most to the least populated.
SELECT S.SName
COUNT(R.PID)
suburb AS S
NATURAL JOIN residency AS R
NATURAL JOIN censusrecord AS CR
CR.CRYear = 2016
COUNT(R.PID) DESC;
MARKER NOTE: OK to group by S.SID instead, and thus have multiple rows with suburbs of the same names. Grouping just by name as done above is OK too.
0.5.2 Option 2
List the name of the transport means and the number of people who use each, according to the 2016 census, ordered from the most to the least preferred.
SELECT TM.TName
COUNT(PT.PID)
transportmeans AS TM
NATURAL JOIN preferredtransportation AS PT
NATURAL JOIN censusrecord AS CR
CR.CRYear = 2016
COUNT(PT.PID) DESC;
Marking Scheme
Total marks: 3
• Correct Answer: 3 marks
– Missing COUNT: -0.5
– Incorrect JOIN: -1.5
– Missing/Incorrect condition: -0.5
– Missing GROUP BY: -1
– Missing/Incorrect ORDER BY: -0.5 – Other error(s): -0.5 (each)
0.6 SQL III (Exam Q12 – 4 marks) 0.6.1 Option 1
It was found that the suburbs that are the worst affected by COVID-19 are Brunswick, Epping, and Port Melbourne. Calculate how many different people, according to the 2016 census, frequently travels from or to those suburbs.
SELECT COUNT(DISTINCT FT.PID)
FROM frequenttrip AS FT
NATURAL JOIN censusrecord AS CR
INNER JOIN suburb AS S_FROM ON FT.from = S_FROM.SID
INNER JOIN suburb AS S_TO ON FT.to = S_TO.SID
S_FROM.SName IN (’Brunswick’, ’Epping’, ’Port Melbourne’)
S_TO.SName IN (’Brunswick’, ’Epping’, ’Port Melbourne’)
CR.CRYear = 2016;
0.6.2 Option 2
How many different people, according to the 2016 census, frequently travels from Brunswick to a suburb other than Epping and Port Melbourne?
SELECT COUNT(DISTINCT FT.PID)
FROM frequenttrip AS FT
NATURAL JOIN censusrecord AS CR
INNER JOIN suburb AS S_FROM ON FT.from = S_FROM.SID
INNER JOIN suburb AS S_TO ON FT.to = S_TO.SID
WHERE CR.CRYear = 2016
S_FROM.SName = ’Brunswick’
S_TO.SName NOT IN (’Epping’, ’Port Melbourne’);
0.6.3 Option 3
How many different people who live in Brunswick frequently travel to the CBD, according to the 2016 census?
SELECT COUNT(DISTINCT R.PID)
FROM frequenttrip AS FT
NATURAL JOIN residency AS R
NATURAL JOIN censusrecord AS CR
INNER JOIN suburb AS S_LIVE ON R.SID = S_LIVE.SID
INNER JOIN suburb AS S_TO ON FT.to = S_TO.SID
WHERE CR.CRYear = 2016
AND S_LIVE.SName = ’Brunswick’
AND S_TO.SName = ’CBD’;
Marking Scheme
Total marks: 4
• Correct Answer: 4 marks
– Missing COUNT: -0.5
– Incorrect JOIN: -2
– Missing/Incorrect condition: -1.5 (-0.5 for each) – Other error(s): -0.5 (each)
0.7 SQL IV (Exam Q13 – 6 marks) 0.7.1 Option 1
Of people who prefer public transport, from which suburb do the largest number of different people make frequent trips, according to the 2016 census?
SELECT S_FROM.SName
COUNT(FT.PID)
frequenttrip AS FT
INNER JOIN suburb AS S_FROM ON FT.from = S_FROM.SID
NATURAL JOIN preferredtransportation
NATURAL JOIN censusrecord AS CR
NATURAL JOIN transportmeans AS TM
CR.CRYear = 2016
TM.TName = ’Public Transport’
S_FROM.SName
COUNT(FT.PID) DESC
LIMIT 1;
0.7.2 Option 2
Of people who prefer public transport, in which suburb do the largest number of different people live, according to the 2016 census?
SELECT S.SName
COUNT(R.PID)
residency AS R
NATURAL JOIN suburb AS S
NATURAL JOIN preferredtransportation
NATURAL JOIN censusrecord AS CR
NATURAL JOIN transportmeans AS TM
CR.CRYear = 2016
TM.TName = ’Public Transport’
COUNT(R.PID) DESC
LIMIT 1;
Marking Scheme
Total marks: 6
• Correct Answer: 6 marks
– Missing COUNT: -0.5
– Incorrect JOIN: -3
– Missing/Incorrect condition: -1 (-0.5 for each) – Missing GROUP BY: -1
– Missing/Incorrect ORDER BY: -0.5
– Missing LIMIT: -1
– Other error(s): -0.5 (each)
0.8 SQL III (Supp. Exam) 0.8.1 Option 1
How many people have reported in the 2016 census to live in more residencies compared with what they reported in the 2011 census?
IMPORTANT: Consider also people who did not report in the 2011 census but they did in 2016.
HINT: You can use the IFNULL() function.
SELECT COUNT(*)
SELECT COUNT(R.SID) no_2016, R.PID
FROM residency AS R NATURAL JOIN censusrecord AS CR
WHERE CR.CRYear = 2016
) AS R_2016 LEFT JOIN
SELECT COUNT(R.SID) no_2011, R.PID
FROM residency AS R NATURAL JOIN censusrecord AS CR
WHERE CR.CRYear = 2011
) AS R_2011 ON R_2016.PID = R_2011.PID
R_2016.no_2016 – IFNULL(R_2011.no_2011, 0) > 0;
0.8.2 Option 2
How many people have reported a greater number of preferred transportation means in the 2016 census compared with what they reported in the 2011 census?
IMPORTANT: Consider also people who did not report in the 2011 census but they did in 2016.
HINT: You can use the IFNULL() function.
SELECT COUNT(*)
SELECT COUNT(PT.TID) no_2016, PT.PID
FROM preferredtransportation AS PT NATURAL JOIN censusrecord AS CR
WHERE CR.CRYear = 2016
) AS PT_2016 LEFT JOIN
SELECT COUNT(PT.TID) no_2011, PT.PID
FROM preferredtransportation AS PT NATURAL JOIN censusrecord AS CR
WHERE CR.CRYear = 2011
) AS PT_2011 ON PT_2016.PID = PT_2011.PID
PT_2016.no_2016 – IFNULL(PT_2011.no_2011, 0) > 0;
0.8.3 Marking Scheme
Total marks: 4
• Correct Answer: 4 marks
– Missing COUNT: -0.5
– Incorrect JOIN: -2
– Missing/Incorrect condition: -1
– Do not consider when there is no records in 2011: -0.5 – Other error(s): -0.5 (each)
0.9 SQL IV (Supp. Exam) 0.9.1 Option 1
What is the means of transportation preferred the most by people who frequently travel to Brunswick, according to the 2016 census?
IMPORTANT: There may be more than one transportation means. If that is the case, all must be shown.
SELECT TM.TName
FROM preferredtransportation AS PT
NATURAL JOIN transportmeans TM
NATURAL JOIN censusrecord AS CR
NATURAL JOIN frequenttrip AS FT
INNER JOIN suburb AS S ON FT.to = S.SID
WHERE CR.CRYear = 2016
AND S.SName = ’Brunswick’
HAVING COUNT(PT.PID) =
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com