Database Implementation and Research Activity
Value: 20%
Due Date: 01-Feb-2019
Return Date: 22-Feb-2019
Submission method options: Alternative submission method
Task
back to top
This Assessment comprises two parts. Please refer to submission instructions section for submission of each part.
Assessment Part A: (10 MARKS)
From Resources under Main Menu in Interact 2 go to Assignment 5 Folder
Copy sqlite3.exe file from the Assignment 5 Folder into a folder in your machine

Copy a5.txt from the Assignment 5 Folder into the folder in your machine where your SQLite file is located

From the SQLite Command Prompt execute the a5.txt script. To do this, at the SQLite Command Line type .read a5.txt [to create the tables you will use to answer this assignment’s questions]

At the SQLite Command Line type .headers on

At the SQLite Command Line type .mode column

Write and run SQL statements to complete the following tasks
1. Show sums of line units for each invoice.
2. Show the details of the products that do not have a value for the attribute v_code.
3. Show the details of the invoices whose subtotal is greater than 25 but less than 75.
4. Show the details of the invoice who has the minimum subtotal.
5. Show the codes and names of the vendors who supplied products.
6. Using EXCEPT show the codes of the vendors who did not supply any products.
7. Using ‘NOT IN’ show the codes and names of the vendors who did not supply any products.
8. List the codes of vendors and the number of products each vendor has supplied, i.e. vendor XXX has supplied xxx products, and vendor YYY has supplied yyy products etc.
9. List the names and codes of vendors and the number of products each vendor has supplied, i.e. vendor XXX has supplied xxx products, and vendor YYY has supplied yyy products etc.
10. List all purchases by customer 10011.
Assessment Part B: 10 MARKS
Write five most important features of the following database technologies (Select any two):
1. Blockchain Databases
2. NoSQL Database
3. Cloud databases
Note: Use proper references in APA style.
Submission Instructions :
You are required to submit the following files (inside a single zipped file):
1. A Word or PDF document with the SQL statements for each query should be copied from your SQLite3 Command Line and pasted into your submission file. If you type .echo on at the SQLite3 Command Line, SQLite will output your SQL statement with the results of the query making it easy to copy and paste both.
2. A Word or PDF document with the screenshots of the results of the SQL statements copied from your SQLite3 Command Line and pasted into your submission file immediately after you execute the SQL query. If you used an output file, please submit only the output file.
3. A well drafted Word or PDF document about the features of the given databases technologies.
NOTES
• This assignment requires you to use SQLite to complete the tasks listed below.
• For guidance on how to install SQLite, watch this short YouTube video: https://www.youtube.com/watch?v=9ynCDVg54H0
• For guidance on how to create tables using SQLite, watch this short YouTube video: https://www.youtube.com/watch?v=kij5lPw0F-o
• Typing the SQL statements or the results is NOT acceptable.
Rationale
back to top
This assessment task will assess the following learning outcome/s:
• be able to use complex SQL commands to query a database.
• be able to research a recent development in the field of database technology.