SIT772 Database and Information Retrieval - Assessment 1

Assessment 1 : Information Retrieval Techniques Problem Solving Task

Question 1 (5 marks)

Problem statement:

A shop has a list of electronic products to sell, which includes mobile phone, TV, Movie, etc.. Each product has a product number, a name, a price, a category, and a description. Each product is made and delivered by a single company only. Each company has an organization code, an organization name, an address, and telephone.

People will visit the shop and purchase products. When a person makes an order, his/her name, address, phone number, and the ordered time will be recorded. The person will be given a unique customer id.

A person can buy more than one product in one order.

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, Enrolment, and Course tables in the database below:
Student

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

Enrolment

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

Course

Course_ID Course Location
SIT772 Database Burwood
SIT774 IT security Burwood
SIT775 Software Development Geelong
SIT772 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 2017 or earlier.

Exercise 3:

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

Exercise 4:

Write the SQL query to find students who are enrolled for units offered in ‘Geelong’.

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 following:

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 PROCEEDURE
246 ROVER DOG 12 SAM COOK

JAN 13/2002

MAR 27/2002

01

10

RABIES VACCINATION

EXAMINE and TREAT WOUND

298 SPOT DOG 2 TERRY KIM

APR 02/2002

JAN 21/2002

05

08

HEARTWORM TEST

TETANUS        VACCINATION

341 MORRIS CAT 4 SAM COOK

MAR 10/2002

JAN 23/2001

JAN 13/2002

05

01

01

HEARTWORM TEST

RABIES        VACCINATION

RABIES        VACCINATION

 

519 TWEEDY BIRD 2 TERRY KIM

APR 30/2002

APR 30/2002

20

ANNUAL CHECK UP

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.

Assessment feedback

General feedback to the class will be provided via CloudDeakin. Students will have the opportunity to seek additional feedback during the fortnightly seminar sessions. Feedback provided for this report will be useful for implementing the testing plan in assessment task 3.

Submission details

The assessment file should be a Microsoft Word or PDF file with the filename formatted as: FirstName_Surname_CourseCode_A1 (eg. Susan_Wolf_SIT772_A1.docx).

Extension requests

Requests for extensions should be made to Unit/Campus Chairs well in advance of the assessment due date.

Special consideration

You may be eligible for special consideration if circumstances beyond your control prevent you from undertaking or completing an assessment task at the scheduled time. See the following link for advice on the application process: http://www.deakin.edu.au/students/studying/assessment-and-results/special-consideration Assessment feedback Detailed written feedback will be provided within two weeks of submission.

Referencing

You must correctly use Harvard referencing in this assessment.

 

"We have expert assistance for database design and development. SIT772 Database and Information Retrieval is our top service - 100% Quality Service"

Order Now