SIT772 Database and Information Retrieval - Assessment 1
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"
