Quiz: Exam: Database Systems (INFO20003-2022-SM2)

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Quiz: Exam: Database Systems (INFO20003-2022-SM2)

Question 1

Q0: Uploading instructions +Single Upload

There is only one question in the exam which requires an upload (ER modelling), the other questions have textboxes which allow photo/document uploads, and you may upload handwritten documents to those questions if you wish so, or simply type which is preferred. If you are having trouble uploading to these textboxes, you can merge all of the images for each of those questions into one merged document (eg PDF, Word.docx etc), and upload to this question (Q0)

If you do uplad contet here:

You MUST make a note for the marker in the text box for each question of the quizthat you are uploading here (eg write 'please see uploaded document in Q0'in the canvas quiz textbox), otherwise your work WILL be overlooked (it will appear as though you did not attempt the question when we are marking).

You must not upload working for any questions aside from questions which are 'essay style' (ie have a full textbox to enter data). Do NOT upload working for multiple choicequestions or numerical answer questions, they will NOT be marked.'

Section 1: Relational Database Modelling+Implementation (25 marks)

Question 2

Data Types

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

Auto-incremented primary key of table Vehicle Model" (model_id; the company makes thousands of vehicle models over time)

Number of seats in a vehicle model

Does a car include the 'heated seats' upgrade?

The customer's 4 digit long Australian postcode

Liters of petrol a vehicle consumes per 100KMs driven (including fractions of a liter)

Color choice for a vehicle (1 of 20 different choices: 'lime green', 'electric yellow', ...)

Video walkthrough of the car

Date of birth of a customer

Time and date that a product waspurchased to print on a receipt

A customer's income in AUD including cents

Question 3

ER Modelling A

The Commonwealth games committee loved the database you designed for them earlier this semester!  They've asked if you would be interested in designing an extension to the schema for some additional business requirements.

The committee want to be able to store some information about non-sport related events for the games like opening and closing ceremonies, cultural events, music performances, etc.  For these non-sport related events, we need to store a URL to a video recording, the number of spectators, and also which venue they were held in.

Storing the coaches for each of the competitors is also a necessity.  A competitor might have multiple different coaches (even for the same event!).  The coach is associated with a competitor and a particular event they are in.  E.g. for the water polo event in the 2000 commonwealth games', competitor X was coached by coaches Y and Z. The same coach can coach multiple competitors, even if it is not a team sport (e.g. if Australia has two swimmers in the mens 100m freestyle, those two swimmers might have the same coach).  Every coach needs to have their full name stored, as well as their primary contact number, their unique 'International Coaching League ID' number, and the 'main/primary' sport type that they usually coach must also be recorded.

Extend the physical model for the Commonwealth Games model to account for these requirements.

Instructions

The solution for A1 is provided below. Feel free to draw your solution on top of this image (there is some extra space around the edge of the image added so you can sketch on it).

If starting from scratch (eg on a piece of paper), then you only need to indicate which tablesyou're associating any new tables with (don't need to recreate the existing tables or includetheir attributes).

You don't need to try to mimic the MySQL workbench symbols: Instead write 'NN', 'FK', 'PK','PFK, for Not Null, Foreign Key, Primary Key, Primary Foreign Key attributes, respectively.(If an attribute is nullable (N), there is no need to write a nullable symbol). You may useworkbench instead if you prefer.

Question 4

DDL

You have been working to set up a database for a new website for leasing devices to business customers. The business owns many different models of devices (e.g. iPad, surface, thinkpad, etc), and many devices of each type of model (e.g. might have 100 iPads available). Therefore, devices are referred to with a combination of the model and the "number' of the device, e.g. "This is iPad #1" or "This is thinkpad #10"

A customer could potentially rent a device, return it, and then later rent the same device again with a new date_leased. Features for each device are just a set of strings (up to 1000 chars long for each feature), like "high density display" or "upgraded ram", etc. The schemayou have been provided with is described below.

Write SQL statements to create the tables for the data model shown below. Be sure tospecify primary and foreign keys. You do not need to specify whether the fields areNULL/NOT NULL. Where the data type is not obvious, feel free to choose an appropriatedata type.

ddl.png