Database Systems Assessment Item 4: SQL Queries and NoSQL Overview

Desklib provides solved assignments and past papers to help students succeed.

DATABASE SYSTEM
Assessment item 4

Table of Contents
Assessment Part A:..........................................................................................................................3
Assessment Part B:........................................................................................................................10
References......................................................................................................................................12
2

Assessment Part A:
1.
select VENDOR.V_CODE, VENDOR.V_NAME, count(PRODUCT.V_CODE) as
NUMBER_SUPPLIED
from PRODUCT, VENDOR
where VENDOR.V_CODE=PRODUCT.V_CODE
group by VENDOR.V_CODE, VENDOR.V_NAME;
Figure 1 query1
2.
SELECT CUSTOMER.CUS_LNAME, SUM(INVOICE.INV_TOTAL) AS Total_invoice_value
FROM CUSTOMER, INVOICE
WHERE CUSTOMER.CUS_CODE=INVOICE.CUS_CODE
group by CUSTOMER.CUS_LNAME,INVOICE.CUS_CODE;
3

Figure 2 query2
3.
SELECT concat(CUSTOMER.CUS_FNAME,' ',CUSTOMER.CUS_LNAME) as Cust_Name ,
COUNT(INVOICE.INV_NUMBER) AS Num_of_Invoices
FROM CUSTOMER, INVOICE
WHERE CUSTOMER.CUS_CODE=INVOICE.CUS_CODE
group by CUSTOMER.CUS_LNAME,CUSTOMER.CUS_FNAME,INVOICE.CUS_CODE;
4

Figure 3 query3
4.
SELECT INV_NUMBER, SUM(LINE_UNITS*LINE_PRICE) AS TOTAL_UNIT_PRICE
FROM LINE
GROUP BY INV_NUMBER;
Figure 4 query4
5.
5

SELECT * FROM INVOICE WHERE INV_SUBTOTAL=(SELECT MIN(INV_SUBTOTAL)
FROM INVOICE);
Figure 5 query5
6.
SELECT CUSTOMER.CUS_LNAME, INVOICE.INV_NUMBER
FROM CUSTOMER, INVOICE
WHERE CUSTOMER.CUS_CODE=INVOICE.CUS_CODE AND
CUSTOMER.CUS_LNAME='Orlando';
6

Figure 6 query6
7.
INSERT INTO vendor VALUES (78965,'Jeff, Inc.','Jhonson','995','103-6984','TN','Y');
Figure 7 query7
8.
INSERT INTO customer VALUES (11685175,'Katta','Jonah Wesley','A','633','555-1073', 0)
7

Figure 8 query8
9.
ALTER TABLE VENDOR
ADD Customer_rating VARCHAR(14);
Figure 9 query9
10.
UPDATE vendor
SET Customer_rating = 'GREAT'
8

WHERE V_NAME = 'Jeff, Inc.';
Figure 10 query10
9

Assessment Part B:
There are mainly four types of NoSQL datatypes which are shown below:
Key Value Store NoSQL Database:
In this type of NoSQL database data store in the key value format. The key can be auto-
generated and the value associate with it can be of String, BLOB, JSON type etc. In this type of
database there is a hash table having unique key and particular data item pointer. There is a
bucket also which the logical group of keys is. It increase the performance of the database by the
cache mechanisms. In order to read the data bucket and key should be known.
Document Store NoSQL Database:
In this type of NoSQL database, the data that is gathered as the group of the key value pairs is
completely compacted in the form of a document and that store almost same key-values, the one
and only dissimilarity is that the stored value gives some of the structure and also encoded data.
Column Store NoSQL Database:
In this type of the NoSQL database, the entire data is stored in the form of the cells that are
grouped together in the form of the columns other than the data present in the form of the rows.
A infinite number of the columns are present here and are actually being created during the run-
time.
Graph Base NoSQL Database:
Here, in this last part of the NoSQL Database, the actual format of the SQL is not found and
neither the data is arranged in the form of the tables or the columns, the data is represented in the
form of the graphs. Graphs are rather used along with the nodes, edges and the properties that
provide the free of index adjacency.
Key Value Store
NoSQL Database
Document Store
NoSQL Database
Column Store
NoSQL Database
Graph Base NoSQL
Database
It consists of the Hash
table that contains the
key-values.
It mainly helps in
storing the documents
that are created using
tagged elements.
Data is contained here
only from a single
column.
It uses graphs to
represent the data.
Here, data cannot be Data can be queried Here, data is stored Transformation of the
10

queried. based on the present
data.
only in the columns
not in the rows.
data can easily be
done.
Eg: Riak and Amazon
Dynamo.
Eg: CouchBase and
MongoDB.
Eg: Google’s Big
Table.
Eg: Labelled,
Directed and
attributed multi-graph.
11

References
Liao, Y. T., Zhou, J., Lu, C. H., Chen, S. C., Hsu, C. H., Chen, W., ... & Chung, Y. C. (2016).
Data adapter for querying and transformation between SQL and NoSQL database. Future
Generation Computer Systems, 65, 111-121.
Hogan, R. (2018). A practical guide to database design. Chapman and Hall/CRC.
Tang, E., & Fan, Y. (2016, November). Performance comparison between five nosql databases.
In 2016 7th International Conference on Cloud Computing and Big Data (CCBD) (pp. 105-109).
IEEE.
12