And estimated departure and arrival times

ICT285 Databases

January Trimester, 2022

Please submit a single word document using the link in Moodle. The due date is on LMS. You do not need to include a separate cover sheet, but you should include your name and student number as part of your document filename. Your name and student number should also be included within the assignment document.

From the Unit Information Guide:

Assignments not received by the final date WILL NOT BE MARKED and will be recorded as a 0.

Under exceptional circumstances an extension can be provided. Extensions for assignment -
submissions can only be granted if requested in advance of the due date for submission, and with a good reason. Applications for extensions should be made to the Unit Coordinator, not to the student's lecturer/tutor who is unable to grant extensions. If an extension is granted, the Unit Coordinator will tell you at the time of granting it whether any penalty in marks will apply to the submitted work.

The assignment addresses the following learning outcomes for the unit:

1. Apply your knowledge of relational database principles and theory to create effective and

	20
Question 2: SQL Select queries

	20
Question 5: Conceptual design

You are working with a database that stores information about suppliers, parts and projects. The

Supply relation records instances of a Supplier supplying a Part for a Project.

PROJECT (JobNo, JobName, StartYear, Country)

SUPPLY (SNo, PNo, JobNo, Quantity)

	•	You can use the symbols σ, Π, etc or the words ‘PROJECT’, ‘RESTRICT’ etc as you prefer.
	•	You do not need to try to make efficient queries – just correct ones.
	•	Where you use a join, always show the join condition.

d. List the name of any Suppliers who have supplied Parts to the Project called “New Academic

Building” or to the Project called “Removal of Asbestos”.

Dubai.

h. List the details of any supplier who has supplied parts to a project that stared in 2020 and (the

Provide SQL queries and the result tables for the following (20 marks):

Please ensure that you include the result table as well as your SQL; you can copy and paste this from either your ssh client or SQL Developer. Each query is worth 2 marks. These tables exist in arion and are owned by the user dtoohey. You may, if you wish, create your own copies of the tables under your own account. If you do so, you should ensure that you copy the sample data in dtoohey’s tables.

List the details of the works of art (including the Artist who created the work, and the c.

acquisition and asking price details) currently held in the gallery (i.e., works of art that have not been sold).

h. Which artist has had the most works of art sold, and how many of the artist’s works have been sold?

i. Sales of which artist’s works have resulted in the highest average profit (i.e., the average of the profits made on each sale of works by an artist), and what is that amount?

b. You have been given the following specifications of a simple database for a netball association that keeps record of players, teams and matches.

PK	Player		PK
PK			PK
FK1	FirstName	TeamID	FK1
	FirstName

	DateOfBirth	Grade		HomeTeamID(FK)
		Grade		AwayTeamID(FK)

The following question is based upon the Patient-Treatment relation which records the details of transactions occurring in a medical surgery. You may assume the data are representative.

PATIENT ID	PATIENT NAME					FEE	PROVIDER NUMBER
			19/7/202 0	A012
437	REARDO N					56	D42433
		12/05/2001		A013	Short
	WILSON	13/08/1997	20/7/202 0					Leahy
895		13/10/1952				35	D42433
		13/10/1952		A014	Long
	MORTAL	27/02/1969	20/7/202 0					Leahy
432					Standard	56

1. What are the specific problems associated with the current design and why do they arise? 2. How would you change the current design and how does your new design address the problems you have identified with the current design.

In order to receive high marks for this question, you will need to demonstrate an understanding of the theories discussed in Topics 1, 2 and 3, how they apply to this problem, and justify the changes you are making to the system. Simply providing the amended design (even if it is correct) will only attract a small percentage of the marks for this question.

•	single origin and destination.

	Airplanes have a unique serial number, a model, a capacity, and a next-scheduled-maintenance date. The maintenance record of an airplane includes a unique maintenance number, a date, a description, the serial number of the plane, and the employee responsible for the repairs. Employees have a unique employee number, a name, a phone, and a job title.

1. Create an entity-relationship diagram showing the data requirements of the system. Your

ERD should be able to be implemented in a relational DBMS. You should use the ERD

the tools you have used. Please note that hand-drawn ERDs are not acceptable.

2. List and explain any assumptions you have made in creating the data model.

2. The University email server strips out any Visio (.vsd) files that are sent; even if they are

included in a zip archive. So, if you want to send a draft of your design to your lecturer by

b. Entities

c. Have you included all required entities in the design?

ii. Are your relationships correctly annotated?

f. Will the overall design support the enterprise requirements?