[Solved] SIT772-Assessment 1-proficiency against the first three Unit Learning Outcomes

This assessment task enables students to demonstrate their proficiency against the first three Unit Learning Outcomes (ULOs) of this unit.

ULO1: Define the difference between data and information and the difference between a traditional multiple-file system and a database.

ULO2: Explain the concept of data modelling and use Entity-Relationship (ER) models to represent data. ULO3: Design and implement relational database systems through the use of SQL

Please read the rubric carefully as it outlines what criteria your assessment will be evaluated on.

Instructions

Read these instructions and the following four questions.

Answer as many questions as possible.

Place your name, ID and answers in your document.

Please submit your word file with your answers and graphs (embedded) where appropriate as a SINGLE document in the Submission Portal.

Question 1)

Problem statement

Saffron Gallery is about to host an exhibition next month. A number of works of art (paintings,

Sculptures, etc.) will be exhibited. Every art work will have a barcode, a title and a description. Each Art Work is created by a single artist. Every artist has a name, an address and a contact number. One artist may create more than one art work for the exhibition.

People will come and visit the exhibition. As each person enters the exhibition, their name, address and phone number will be recorded. Each person will be given a unique barcode id.

During their visit, people will nominate which art work they consider the best and this will be recorded by Uptown Gallery. People will be able to vote for more than one art work.

Exercise 1:

Describe the elements (entities, attributes and relationships) of the table of this database system.

Exercise 2:

Draw the Entity Relationship Diagram (ERD) of the generated tables.

Exercise 3:

Write the SQL code to create the tables

Question 2 (10 marks):

You are given the Student 1, Student 2, and Course tables in the database below:

Student 1

Student_ID	Student_FName	Student_Lname
10001	John	Smith
10002	Dave	Franklin
10003	Febby	Johns
10004	Mary	Gibson
10005	Glory	Anson

Student 2

Student_ID	Course_ID	Year_Joined
10001	SIT772	2016
10002	SIT774	2015
10003	SIT775	2017
10004	SIT712	2016
10005	SIT772	2017

Course

Course_ID	Course	Location
SIT772	Database	Burwood
SIT774	IT security	Burwood
SIT775	Software Development	Geelong
SIT712	Project Management	Burwood

For each of the following exercises please provide a screenshot of SQL query execution.

Exercise 1: Write the SQL code to create the above three tables, and insert the sample data into the tables.

Exercise 2: Write the SQL query to find all students who have enrolled into a course in 2016 or later.

Exercise 3:

Write the SQL query to generate a list of all students who have the letter a in their name (first name/last name).

Exercise 4: Write the SQL query to find students who are enrolled for units offered in Burwood.

Exercise 5:

Write the SQL query to show only students who have enrolled to Database course or a course that has been offered in Geelong.

Question 3 (10 marks):

Sales

CUS_ID	Name	Ord_NO	Ord_Date	Prod_ID	Description	Qty_Ord
C001	Gold	O81	15-Apr	P005	Chisel	6
C001	Gold	O81	15-Apr	P004	Plane	14
C075	Red	O99	16-Apr	P015	Saw	3
C009	Blue	O56	16-Apr	P033	Punch	24
C009	Blue	O56	16-Apr	P004	Plane	9
C001	Gold	O88	17-Apr	P015	Saw	10

Using the above Sales table structure, perform the followings:

Exercise 1:

Write the relational schema for the above table, draw the dependency diagram, and identify all dependencies, including all partial and transitive dependencies. You may assume that an order number references more than one product and that the table does not contain repeating groups.

Exercise 2:

Write the relational schemas after removing all partial dependencies and draw the new dependency diagrams. Identify the normal form for each created table structure.

Exercise 3:

Write the relational schemas after removing all transitive dependencies and draw the new dependency diagrams. Identify the normal form for each created table structure.

Exercise 4:

Draw the Entity Relationship Diagram (ERD) according to the result of Exercise 3.

Question 4 (5 marks):

You are given the following table structure with sample data in it.

PET ID	PET NAME	PET TYPE PET AGE OWNER VISIT DATE PROC_ID PROCEDURE
246	ROVER	DOG	12	SAM COOK	JAN 13/2002 01 RABIES VACCINATION
					MAR 27/2002 10 EXAMINE and TREAT WOUND
					APR 02/2002 05 HEART WORM TEST
298	SPOT	DOG	2	TERRY KIM	JAN 21/2002 08 TETANUS VACCINATION
					MAR 10/2002 05 HEART WORM TEST
341	MORRIS	CAT	4	SAM COOK	JAN 23/2001 01 RABIES VACCINATION
					JAN 13/2002 01 RABIES VACCINATION
519	TWEEDY	BIRD	2	TERRY KIM	APR 30/2002 20 ANNUAL CHECK UP
					APR 30/2002 12 EYE WASH

Exercise 1:

Draw the dependency diagram of the table, and normalize the table to ensure all generated tables are in 3NF. Present all tables generated from the normalization.

Exercise 2:

Write SQL code to create the generated tables.