ITC556 Database Systems: SQL Queries and NoSQL Database Types

Desklib provides past papers and solved assignments for students. This assignment covers SQL and NoSQL databases.

ITC556
DATABASE SYSTEM

Table of Contents
Assessment Part A:......................................................................................................................................3
Assessment Part - B.....................................................................................................................................5
References...................................................................................................................................................7
2

Assessment Part A:
1.
SELECT VR.V_CODE, VR.V_NAME, COUNT(PCC.V_CODE) AS SUPPLY
FROM PRODUCT PCC, VENDOR VR
WHERE VR.V_CODE=PCC.V_CODE
GROUP BY VR.V_CODE, VR.V_NAME;
Figure 1 query1
2.
SELECT CCS.CUS_LNAME, SUM(IE.INV_TOTAL) AS TOTAL_INVOICE_VALUE
FROM CUSTOMER CCS, INVOICE IE
WHERE CCS.CUS_CODE= IE.CUS_CODE
GROUP BY CCS.CUS_LNAME, IE.CUS_CODE;
3

Figure 2 query2
3.
SELECT CONCAT(CCS.CUS_FNAME,' ', CCS.CUS_LNAME) AS CST_NAME , COUNT(IE.INV_NUMBER) AS
NUM_OF_INVOICES
FROM CUSTOMER CCS, INVOICE IE
WHERE CCS.CUS_CODE= IE.CUS_CODE
GROUP BY CCS.CUS_LNAME, CCS.CUS_FNAME,IE.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 CCS.CUS_LNAME, IE.INV_NUMBER
FROM CUSTOMER CCS, INVOICE IE
WHERE CCS.CUS_CODE= IE.CUS_CODE AND CCS.CUS_LNAME='Orlando';
6

Figure 6 query6
7.
INSERT INTO vendor VALUES (45678,'MAX','SAMSON','895','188-0984','TN','Y');
Figure 7 query7
8.
7

INSERT INTO customer VALUES (11633000,' Nagekar', ‘Mahesh’,'A','777','675-1993', 0)
Figure 8 query8
9.
ALTER TABLE VENDOR
ADD Customer_rating VARCHAR(14);
8

Figure 9 query9
10.
UPDATE vendor
SET Customer_rating = 'GREAT'
WHERE V_NAME = ' MAX ';
Figure 10 query10
9

10

Assessment Part - B
Four categories of the NoSQL are:
Key-value store NoSQL database, Document store NoSQL database, Column store NoSQL
database and Graph base store NoSQL database.
Key-Value store No SQL database
The key-value database is the easiest as well as the simple one. In this, the data stores in values
that are assigned with different keys and this are why it is known as a key-value database. The
data like hash table & pointers are stored in one place and the pointers are those that point over a
data. In this database, the column is not there so no relation of the columns is present in this
database. This is the quality of this database that makes the database easy to access and used in
many business and leading organizations. This database is used in various places like in cart in
any website, in preferences of the user and many other places. Example of the database is
MemcacheDB and many more (Roy et al., (2016)).
Document Store No SQL database
This database is similar to the key-value database but in this database, the data and information
can be stored in the structure form and in the unstructured form as well. There are different file
formats present in this database that is the XML or the bson. The use of the database is done in e-
commerce website and in managing the contents as well. In blogging platform also. Example of
this database is MongoDB (Böhlen et al., (2018)).
Column store NoSQL database
This database contains the columns and no rows for storing the data and the use of tuple is
neglected so that the access to a large amount of data can be made easier and simpler. The
columns in this database can be unlimited and can be created while the run time or while the
11

designing of the schema. This database has the easy access of data as well as easy data
aggregation all the processing of the data like data reading, data writing and operations are
performed on the columns only. The example is Hbase of this database (Jukic et al., (2016)).
Graph Base NoSQL database
This is the database where the data stored in the form of graph to represent the graph in
visualization form. The display of relations of the data is easier and to understand the
relationships among them is also simpler by using this particular database. Some sort of nodes,
relationships, and other components are added in this particular database. The database stores the
data in virtual relation, unlike all other databases. The example of this database is Arango DB
and there are more other existing databases are present like Oreintdb.
12