Recent Question/Assignment
Practice Exam
All Students - Please Note:
? Solutions to Practice Exam(s) will NOT be made available to students under any circumstances.
? This Practice Exam may contain more questions than a real exam. This is intentional, because it eliminates the possibility of any -the practice exam was easier than the real exam- arguments.
? Treat this Practice Exam like a real exam: print it out, put a -do not disturb- sign on your door, turn off your phone, and give yourself a block of 2 hours to work through the exam.
? This Practice Exam is NOT assessment, so students can work together on this. In fact, students are strongly encouraged to use team work to discuss, check, and solve questions and answers.
? Check your answers in the following ways:
1. Form a study group with other students and compare and check your answers and debate which answer is best.
2. Check your answers by comparing and discussing your answers with other students and debate which answer is best.
3. Where appropriate, for example with the queries, type in your answers and check them against the appropriate database.
NOTE: Also make sure you have a very good read of the Exam Hints and Tips on the Moodle unit web page.
INSTRUCTIONS SHEET
1. Write your answers in the space provided for each question within the examination booklet provided.
2. This examination paper has a total of 40 marks in three (3) sections:
SECTION A (15 MARKS) –
Practical Database Use Questions
SECTION B (10 MARKS) –
Practical Database Design Questions
SECTION C (15 MARKS) –
Theory Questions
3. Each section contains a number of questions.
4. Attempt all questions in all sections.
SECTION A 15 MARKS
PRACTICAL DATABASE USE QUESTIONS
Questions in Section A are based on the Kware database. Tables and columns of interest are represented below, followed by sample data. Please note that your actual exam will have the same tables but with different data.
Tables
Product(ID, Code, Category, Description, UnitPrice, QtyInStock)
Customer(ID, Name, Street, Suburb, State, PostCode, Discount)
SalesOrder(ID, CustomerID, Number, FullPrice, Discount, FinalPrice, TotalPaid) foreign key (CustomerID) references Customer(ID)
SalesOrderItem(ID, SalesOrderID, ProductID, UnitPrice, QtyOrdered, FullPrice) foreign key (SalesOrderID) references SalesOrder(ID) foreign key (ProductID) references Product(ID)
Sample Data
Product
ID Code Category Description UnitPrice QtyInStock
1 BLLY00001 Cookware Billy 2L Saucepan $63.00 4
2 BLLY00002 Cookware Billy 3L Saucepan $73.00 0
3 BLLY00003 Cookware Billy 4L Saucepan $93.00 5
4 RAJA00001 Glassware Raja Glass Decanter $120.00 0
5 RAJA00002 Glassware Raja Glass Goblet $75.00 6
6 WANG00001 Glassware Wang Flute Glass $10.00 72
7 WANG00002 Glassware Wang Wine Glass $12.00 48
8 WANG00003 Glassware Wang Glass Pitcher $39.00 3
Customer
ID Name Street Suburb State PostCode Discount
1 Best Kitchens 1 Beef Parkhurst QLD 4702 10
2 Best Kitchens 2 Rum Road Bargara QLD 4670 10
3 Sheila Smith 3 Sugar Andergrove QLD 4740 0
4 Samir Singh 4 River Road Milton QLD 4064 0
5 Lee Chin 5 Harbour Cremorne NSW 2090 0
6 Bruce Jones 6 Bay Way Elwood VIC 3184 0
SalesOrder
ID CustomerID Number FullPrice Discount FinalPrice TotalPaid
1 1 10000001 $1374.00 $137.40 $1236.60 $1236.60
2 2 10000002 $220.00 $22.00 $198.00 $198.00
3 1 10000003 $1260.00 $126.00 $1134.00 $1134.00
4 3 10000004 $48.00 $0.00 $48.00 $48.00
5 4 10000005 $63.00 $0.00 $63.00 $33.00
SalesOrderItem
ID SalesOrderID ProductID UnitPrice QtyOrdered FullPrice
1 1 1 $63.00 6 $378.00
2 1 2 $73.00 6 $438.00
3 1 3 $93.00 6 $558.00
4 2 6 $10.00 10 $100.00
5 2 7 $12.00 10 $120.00
6 3 5 $75.00 12 $900.00
7 3 4 $120.00 3 $360.00
8 4 5 $12.00 4 $48.00
9 5 1 $63.00 1 $63.00
Question 1 15 Marks Structured Query Language
Formulate SQL queries to answer the following information requests. The expected result using the sample data is shown after each request. Result columns should be named as shown. The order of result rows is only significant if stated in the request.
(a) Provide an alphabetical listing of Queensland customers. (1.5 marks)
Name ID
Best Kitchens 1
Best Kitchens 2
Samir Singh 4
Sheila Smith 3
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(b) List customers from Victoria or Queensland with an address (Street value) that does not
contain the word 'Street' or the word 'Road'. (1.5 marks)
Name State Street
Best Kitchens QLD 1 Beef Highway
Bruce Jones VIC 6 Bay Way
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(c) List customers with one or more orders that have not been paid in full.
Formulate three queries to answer this information request: (2 marks)
i. one that uses IN with a subquery ii. one that uses EXISTS with a subquery iii. one that does not use a subquery
Name ID
Samir 4
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(d) List customers who have placed one or more sales orders with a final price that is greater than $1000 or who have ordered more than 2 different products on any order.
Formulate three queries to answer this information request: (2 marks)
iv. one query that uses IN twice and does not use EXISTS. v. one query that uses EXISTS once and IN once.
vi. one query that uses EXISTS twice and does not use IN.
CID Name
1 Best Kitchens
3 Sheila Smith
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(e) List customers who have ordered one or more cookware items. (1 mark)
Name ID
Best 1
Samir 4
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(f) Display the number of different product types that have been ordered by customers and
the total number of items ordered by customers. (1 mark)
ProductTypesOrder TotalItemsOrdered
7 58
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(g) List states with more than one customer. (1 mark)
State
QLD
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(h) For orders with a total price greater than $1000, list the total price and total number of items ordered. (1 mark)
ID TotalPrice ItemsOrdered
1 1236.60 18
3 1134.00 15
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(i) Display all customers who either have -kitch- in their Name or their Name starts with
-S-. (2 marks)
Name Email
Best Kitchens parkhurst@bestkitchens.com.au
Best Kitchens bargara@bestkitchens.com.au
Sheila Smith sheila.smith@freemail.com
Samir Singh samirsingh@umail.comD
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(j) Display all customer names who live in a State where the second letter is -L-.
(2 marks)
Name State
Best Kitchens QLD
Best Kitchens QLD
Sheila Smith QLD
Samir Singh QLD
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
SECTION B 10 MARKS
PRACTICAL DATABASE DESIGN QUESTIONS
Question 2 10 Marks
Your assistant has proposed that the Hware database might include the Dispatch table below. Some sample data has been provided. The Dispatch table has a composite primary key of DispatchID and ProductID.
Dispatch
Dispatch ID Dispatch Date Product ID Product Code Product Category Qty
Dispatched Customer ID
1 05/01/10 1 BLLY00001 Cookware 2 1
1 05/01/10 2 BLLY00002 Cookware 2 1
1 05/01/10 3 BLLY00003 Cookware 2 1
2 11/01/10 1 BLLY00001 Cookware 4 1
2 11/01/10 3 BLLY00003 Cookware 4 1
3 11/01/10 6 WANG00001 Glassware 10 2
3 11/01/10 7 WANG00002 Glassware 10 2
Note: Support your answers to questions below with any assumptions that help to clarify your understanding of data in the Dispatch table.
(a) Describe one update anomaly that may arise from using the Dispatch table. (1 mark)
(b) Describe one insert anomaly that may arise from using the Dispatch table. (1 mark)
(c) Describe one deletion anomaly that may arise from using the Dispatch table. (1 mark) (d) Identify all functional dependencies in table Dispatch. (2.5 marks)
(e) Explain why table Dispatch does not satisfy Boyce-Codd Normal Form (BCNF).
Fully justify your answer. (2 marks)
(f) Transform table Dispatch into a set of tables that do satisfy BCNF. (2.5 marks)
Note: Document the tables in your design using the notation below.
Customer(ID, Name, State, PostCode, Discount)
SalesOrder(ID, CustomerID, Number, FullPrice, Discount, FinalPrice, TotalPaid) foreign key (CustomerID) references Customer(ID)
SECTION C 15 MARKS THEORY QUESTIONS Question 3 3 Marks
Describe the terms Data and information.
Question 4 3 Marks
What is functional determinant? Explain your answer with relevant examples.
Question 5 3 Marks
What is the purpose of SQL?
Question 6 3 Marks
Define and describe cardinality with relevant examples.
Question 7 3 Marks
Explain the benefits of UNION and OUTER JOIN.
? End of exam paper ??
All Students - Please Note:
? Solutions to Practice Exam(s) will NOT be made available to students under any circumstances.
? This Practice Exam may contain more questions than a real exam. This is intentional, because it eliminates the possibility of any -the practice exam was easier than the real exam- arguments.
? Treat this Practice Exam like a real exam: print it out, put a -do not disturb- sign on your door, turn off your phone, and give yourself a block of 2 hours to work through the exam.
? This Practice Exam is NOT assessment, so students can work together on this. In fact, students are strongly encouraged to use team work to discuss, check, and solve questions and answers.
? Check your answers in the following ways:
1. Form a study group with other students and compare and check your answers and debate which answer is best.
2. Check your answers by comparing and discussing your answers with other students and debate which answer is best.
3. Where appropriate, for example with the queries, type in your answers and check them against the appropriate database.
NOTE: Also make sure you have a very good read of the Exam Hints and Tips on the Moodle unit web page.
INSTRUCTIONS SHEET
1. Write your answers in the space provided for each question within the examination booklet provided.
2. This examination paper has a total of 40 marks in three (3) sections:
SECTION A (15 MARKS) –
Practical Database Use Questions
SECTION B (10 MARKS) –
Practical Database Design Questions
SECTION C (15 MARKS) –
Theory Questions
3. Each section contains a number of questions.
4. Attempt all questions in all sections.
SECTION A 15 MARKS
PRACTICAL DATABASE USE QUESTIONS
Questions in Section A are based on the Kware database. Tables and columns of interest are represented below, followed by sample data. Please note that your actual exam will have the same tables but with different data.
Tables
Product(ID, Code, Category, Description, UnitPrice, QtyInStock)
Customer(ID, Name, Street, Suburb, State, PostCode, Discount)
SalesOrder(ID, CustomerID, Number, FullPrice, Discount, FinalPrice, TotalPaid) foreign key (CustomerID) references Customer(ID)
SalesOrderItem(ID, SalesOrderID, ProductID, UnitPrice, QtyOrdered, FullPrice) foreign key (SalesOrderID) references SalesOrder(ID) foreign key (ProductID) references Product(ID)
Sample Data
Product
ID Code Category Description UnitPrice QtyInStock
1 BLLY00001 Cookware Billy 2L Saucepan $63.00 4
2 BLLY00002 Cookware Billy 3L Saucepan $73.00 0
3 BLLY00003 Cookware Billy 4L Saucepan $93.00 5
4 RAJA00001 Glassware Raja Glass Decanter $120.00 0
5 RAJA00002 Glassware Raja Glass Goblet $75.00 6
6 WANG00001 Glassware Wang Flute Glass $10.00 72
7 WANG00002 Glassware Wang Wine Glass $12.00 48
8 WANG00003 Glassware Wang Glass Pitcher $39.00 3
Customer
ID Name Street Suburb State PostCode Discount
1 Best Kitchens 1 Beef Parkhurst QLD 4702 10
2 Best Kitchens 2 Rum Road Bargara QLD 4670 10
3 Sheila Smith 3 Sugar Andergrove QLD 4740 0
4 Samir Singh 4 River Road Milton QLD 4064 0
5 Lee Chin 5 Harbour Cremorne NSW 2090 0
6 Bruce Jones 6 Bay Way Elwood VIC 3184 0
SalesOrder
ID CustomerID Number FullPrice Discount FinalPrice TotalPaid
1 1 10000001 $1374.00 $137.40 $1236.60 $1236.60
2 2 10000002 $220.00 $22.00 $198.00 $198.00
3 1 10000003 $1260.00 $126.00 $1134.00 $1134.00
4 3 10000004 $48.00 $0.00 $48.00 $48.00
5 4 10000005 $63.00 $0.00 $63.00 $33.00
SalesOrderItem
ID SalesOrderID ProductID UnitPrice QtyOrdered FullPrice
1 1 1 $63.00 6 $378.00
2 1 2 $73.00 6 $438.00
3 1 3 $93.00 6 $558.00
4 2 6 $10.00 10 $100.00
5 2 7 $12.00 10 $120.00
6 3 5 $75.00 12 $900.00
7 3 4 $120.00 3 $360.00
8 4 5 $12.00 4 $48.00
9 5 1 $63.00 1 $63.00
Question 1 15 Marks Structured Query Language
Formulate SQL queries to answer the following information requests. The expected result using the sample data is shown after each request. Result columns should be named as shown. The order of result rows is only significant if stated in the request.
(a) Provide an alphabetical listing of Queensland customers. (1.5 marks)
Name ID
Best Kitchens 1
Best Kitchens 2
Samir Singh 4
Sheila Smith 3
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(b) List customers from Victoria or Queensland with an address (Street value) that does not
contain the word 'Street' or the word 'Road'. (1.5 marks)
Name State Street
Best Kitchens QLD 1 Beef Highway
Bruce Jones VIC 6 Bay Way
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(c) List customers with one or more orders that have not been paid in full.
Formulate three queries to answer this information request: (2 marks)
i. one that uses IN with a subquery ii. one that uses EXISTS with a subquery iii. one that does not use a subquery
Name ID
Samir 4
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(d) List customers who have placed one or more sales orders with a final price that is greater than $1000 or who have ordered more than 2 different products on any order.
Formulate three queries to answer this information request: (2 marks)
iv. one query that uses IN twice and does not use EXISTS. v. one query that uses EXISTS once and IN once.
vi. one query that uses EXISTS twice and does not use IN.
CID Name
1 Best Kitchens
3 Sheila Smith
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(e) List customers who have ordered one or more cookware items. (1 mark)
Name ID
Best 1
Samir 4
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(f) Display the number of different product types that have been ordered by customers and
the total number of items ordered by customers. (1 mark)
ProductTypesOrder TotalItemsOrdered
7 58
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(g) List states with more than one customer. (1 mark)
State
QLD
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(h) For orders with a total price greater than $1000, list the total price and total number of items ordered. (1 mark)
ID TotalPrice ItemsOrdered
1 1236.60 18
3 1134.00 15
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(i) Display all customers who either have -kitch- in their Name or their Name starts with
-S-. (2 marks)
Name Email
Best Kitchens parkhurst@bestkitchens.com.au
Best Kitchens bargara@bestkitchens.com.au
Sheila Smith sheila.smith@freemail.com
Samir Singh samirsingh@umail.comD
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
(j) Display all customer names who live in a State where the second letter is -L-.
(2 marks)
Name State
Best Kitchens QLD
Best Kitchens QLD
Sheila Smith QLD
Samir Singh QLD
Answer:
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
……………………………………………………………………………………………………………………………………………………………………………………………………………………………
SECTION B 10 MARKS
PRACTICAL DATABASE DESIGN QUESTIONS
Question 2 10 Marks
Your assistant has proposed that the Hware database might include the Dispatch table below. Some sample data has been provided. The Dispatch table has a composite primary key of DispatchID and ProductID.
Dispatch
Dispatch ID Dispatch Date Product ID Product Code Product Category Qty
Dispatched Customer ID
1 05/01/10 1 BLLY00001 Cookware 2 1
1 05/01/10 2 BLLY00002 Cookware 2 1
1 05/01/10 3 BLLY00003 Cookware 2 1
2 11/01/10 1 BLLY00001 Cookware 4 1
2 11/01/10 3 BLLY00003 Cookware 4 1
3 11/01/10 6 WANG00001 Glassware 10 2
3 11/01/10 7 WANG00002 Glassware 10 2
Note: Support your answers to questions below with any assumptions that help to clarify your understanding of data in the Dispatch table.
(a) Describe one update anomaly that may arise from using the Dispatch table. (1 mark)
(b) Describe one insert anomaly that may arise from using the Dispatch table. (1 mark)
(c) Describe one deletion anomaly that may arise from using the Dispatch table. (1 mark) (d) Identify all functional dependencies in table Dispatch. (2.5 marks)
(e) Explain why table Dispatch does not satisfy Boyce-Codd Normal Form (BCNF).
Fully justify your answer. (2 marks)
(f) Transform table Dispatch into a set of tables that do satisfy BCNF. (2.5 marks)
Note: Document the tables in your design using the notation below.
Customer(ID, Name, State, PostCode, Discount)
SalesOrder(ID, CustomerID, Number, FullPrice, Discount, FinalPrice, TotalPaid) foreign key (CustomerID) references Customer(ID)
SECTION C 15 MARKS THEORY QUESTIONS Question 3 3 Marks
Describe the terms Data and information.
Question 4 3 Marks
What is functional determinant? Explain your answer with relevant examples.
Question 5 3 Marks
What is the purpose of SQL?
Question 6 3 Marks
Define and describe cardinality with relevant examples.
Question 7 3 Marks
Explain the benefits of UNION and OUTER JOIN.
? End of exam paper ??
This above price is for already used answers. Please do not submit them directly as it may lead to plagiarism. Once paid, the deal will be non-refundable and there is no after-sale support for the quality or modification of the contents. Either use them for learning purpose or re-write them in your own language. If you are looking for new unused assignment, please use live chat to discuss and get best possible quote.
Looking for answers ?
Recent Questions
Program: Master of Information SystemsAssessment 1: Portfolio 1 (Session 2 Topic: Input, Processing andOutput) Semester 2: Block 8-2024Course Code: MBIS4003 Course Title: Software Development Total Marks:...AssessmentAssessment item 2—Assignment 2Due date 11:45pm on Sunday, 24Nov.2024Weighting 35%Length Not applicableObjectivesThis assessment item relates to the unit learning outcomes numbers 1, 2 and 3 as...Plan, construct and write a long-form essay on a particular area of media research that interests you. Using an established theoretical framework, explain how your research offers new insights into the...DescriptionIn this assessment, you are required to demonstrate that you understand how an Older Adult can develop mental health problems associated with cognitive decline leading to cognitive impairment...School of Engineering, Design and Built EnvironmentENGR7008 Fire Engineering Design and AssessmentAssignment 4: Peer Review of Fire Engineering ReportASSIGNMENT 4PEER REVIEW OF A FIRE ENGINEERING REPORTSee...Structural Analysis Etask 5The aim of this assessment task is to evaluate the appropriate use of the Force Method and Moment Distribution Method for structural analysis• Please download the eTask4 from...Purpose:The purpose of this task is for the student to demonstrate how to create a holistic plan of care that is person-centred, trauma-informed, and recovery-oriented in focus.Description:In this written...Show All Questions