Reference no: EM132197298
Assessment: Database Design and Implementation Report
Learning Outcomes
This assessment assesses the following Unit Learning Outcomes (ULO) and related Graduate Learning Outcomes (GLO):
Unit Learning Outcome (ULO)
ULO 2: Explain the concept of data modelling and use Entity-Relationship (ER) models to represent data.
ULO 3: Design and implement relational database systems through the use of SQL
Purpose
This task requires students to apply their understanding and ability to use Relational DataBase Management Systems (RDBMS) as well as use SQL in the modelling of the physical world. Students will be provided with a set of business scenarios and are required to design a database and provide related SQL queries.
Instructions
This is an individual assessment task. Students are required to submit a written report that describes the design and implementation of a small database project. Students are also required to submit the associated SQL scripts
- Read these instructions and the following four questions.
- Answer as many questions as possible.
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 Saffron 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:
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:
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:
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.
Attachment:- Database and Information Retrieval.rar