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
Assessment Criteria for Written Report (20%)• Submission of a minimum 1500 to maximum 1800-words written report due from week 9. You will submit your written report one week after you have undertaken the...Assessment 2: (20%) Case Study Presentation Due Date weeks 10This assessment builds on your theoretical knowledge and tests your ability to apply this knowledge to practice.you are required to formulate...Assessment 2Assessment 2Report: 30%Word count: 1400 – 1600 wordsDue date: Sunday of Week 8DescriptionYou are playing the role of a community service worker supporting a young adult son who is 18 years...1500-1800 wordsInstructions From TutorPlease download the eTask3 spreadsheet from Canvas and save the eTask spread as an Excel file. The main content of the force method is taught in Lectures 6,7&8.eTask3.1 is a...ASSIGNMENT INSTRUCTIONSAssessment Practical assessmentAssessment code: 011Academic Year: 2023/2024Trimester: 2Module Title: Critical Perspectives on Cross-Border BusinessModule Code: MOD009194Level: 6Module...Information Systems Analysis & DesignAssignmentDue date: Week 5 Friday (5 Apr 2024) 11:59 pm AESTWeighting: 30%Length: 1,200 to 1,500 words (penalty will apply if the length is outside this limit)Assessment...Show All Questions