Database Queries and NoSQL Databases: A Practical Assessment

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

Contents
Assessment A...................................................................................................................................2
Assessment B.................................................................................................................................10
References......................................................................................................................................13

Assessment A
Queries are given as:
Query 1
Select count (PRDCT.V_CODE) as Num_Supplies, V.V_CODE, V.V_NAME
from PRODUCT PRDCT, VENDOR V
where V.V_CODE = PRDCT.V_CODE
group by V.V_CODE, V.V_NAME;
Figure 1: Query 1
Query 2
Select Sum (IV.INV_TOTAL) as InvoiceTotal, C.CUS_LNAME
from CUSTOMER C, INVOICE IV
where C.CUS_CODE= IV.CUS_CODE
group by C.CUS_LNAME, IV.CUS_CODE;

Figure 2: Query 2
Query 3
Select Concat (C.Cus_Fname,' ', C.Cus_Lname) as CustomerName ,
COUNT(IV.INV_NUMBER) as IV_Number
from CUSTOMER C, INVOICE IV
where C.Cus_Code= IV.Cus_Code
group by C.Cus_Lname, C.Cus_Fname, IV.Cus_Code;

Figure 3: Query 3
Query 4
Select INV_Number, SUM (LINE_UNITS*LINE_PRICE) AS FinalAmount
From LINE ln
Group by INV_Number;

Figure 4: Query 4
Query 5
Select * from INVOICE WHERE INV_SUBTOTAL = (SELECT MIN (INV_SUBTOTAL)
FROM INVOICE);

Figure 5: Query 5
Query 6
Select IV.INV_Number, C.Cus_Lname
From INVOICE IV,CUSTOMER C
Where C.CUS_CODE= IV.CUS_CODE AND C.Cus_Lname='Orlando';

Figure 6: Query 6
Query 7
Insert into vendor values (45225,'Fred','Stuart','788','452-4445','TN','Y');
Select * from vendor;

Figure 7: Query 7
Query 8
Insert into Customer Values (11694267,'Ranjith','Reddy','R','887','745-4557',0);
Select * from Customer;
Figure 8: Query 8
Query 9
Alter table vendor
add Customer_rating VARCHAR (14);

Figure 9: Query 9
Query 10
Update vendor
Set Customer_rating = 'GREAT'
Where V_NAME = 'Fred';

Figure 10: Query 10
Assessment B
The four different type of NoSQL databases are:
 Key-value store NoSQL database
 Document store NoSQL database
 Column store NoSQL database
 Graph base store NoSQL database
Now let’s discuss them in detail:
Key-Value store No SQL database
This database is the simpler one in which key and values are included. The data in this database
get stores in value and a unique key is assigned to it so that every data have unique identification.
The data get stored in the hash table and the data is the unique key or the pointers. The database
does not contain the column relation so that the process and access can be made easier. This
database lead to high performances and many business leading companies adopt this database
(Yaghmazadeh et al., (2017)).

This database is used in various purposes like for e-commerce websites the shopping cart feature
is added so the data that is entered for the cart is store here, the session data of the users can be
stored in this particular database and for the user preference also. The examples of this database
are the Redis, Memcache DB and many others (Klettke et al., (2015)).
Document Store No SQL database
The document store nosql database is very similar to the key value database in which the data
store in the value form likewise key value nosql database but here the data that is stored in value
can be in structured and in semi structure format and these formats are BSON or XML or many
other formats. This particular database can be used in various places like it is used for the
platform of e-commerce, for management of the content and in order to perform the analytics as
well as on the platform of blogging (Ahadi et al., (2015, June)).
The examples of this particular database are the CouchDB, MongoDB, Elastic search and many
others.
Column store NoSQL database
The column store nosql database contains the column format in which the data get store in the
columns only so that the access of the data from the database can get much easier. There are no
rows in this database and the columns that are present can be formed unlimited when the schema
is defined or while the run time. The data aggregation is easier in this particular database and all
the other processes like reading, writing or editing the data can take place only in columns. This
database is used in various places like for the system of managing the contents, in the systems
where the counters are managed, in the services that uses expiration and in the log aggregator
systems also (Vela et al., (2018)).

The examples of this database are HBase, Cassandra and many others.
Graph Base NoSQL database
The graph database is created so that the visualization is created of the provided and stored data
in the database. This database helps in displaying the relations of the data with another one in the
most flexible process. There are various nodes that are present in this database and these nodes
stores entities data present in the database. The traditional databases stores description of the
relationships in the table of junctions but this database has virtual relations.
This database is used in many other purposes like in detecting various frauds and crimes. In the
search of the graphs, in the social networks, in the operations of the information technology.
There are examples of this particular database that are OrientDB, ArangoDB and many others.
.