SIT772 Assessment Task 1 Solved

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


Question 1 (5 marks) 

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