Assessment Item 4: Research and SQL Queries on Database Systems

This report is a comprehensive analysis of database systems, focusing on SQL queries and the emergence of NoSQL databases. Part A of the report presents a series of SQL queries designed to extract and manipulate data from a relational database, covering topics such as vendor product counts, invoice values, customer invoice frequency, and data modification through INSERT and UPDATE statements. Part B shifts the focus to NoSQL databases, discussing their rise to handle big data challenges. The report details the four main categories of NoSQL databases: key-value, document-based, column-based, and graph-based, explaining their structures, use cases, and advantages over traditional relational databases. The report references academic sources to support its findings and provide context for database technologies. This assessment item provides a valuable overview of database concepts and technologies for students.

Assessment Item 4
Research and SQL Queries
5/27/2019
Student ID:
Student Name:

Report
Part A
Query 1
select vd.v_code, vd.v_name, count(pd.p_code) as NumberOfProducts from vendor vd inner join
product pd on vd.v_code=pd.v_code group by vd.v_code, vd.v_name;

Query 2
select cstm.cus_lname, sum(invc.inv_total) as 'Total_invoice_value' from customer cstm inner join
invoice invc on cstm.cus_code=invc.cus_code group by cus_lname;

Query 3
select concat(cstm.cus_fname,' ' , cstm.cus_lname) as CustomerName , count(invc.cus_code) as
'NumberOfinvoices' from customer cstm inner join invoice invc on cstm.cus_code=invc.cus_code group
by cus_fname, cus_lname;;

Query 4
select line_units, line_price, round(sum(line_total),2) as UnitPrice from line group by line_units,
line_price;
Query 5
select * from invoice order by inv_subtotal desc limit 1;

Query 6
select cus_code, cus_fname, cus_lname, inv_number, inv_date, inv_subtotal, inv_tax, inv_total from
invoice natural join customer having cus_lname='Orlando';
Query 7
INSERT INTO VENDOR VALUES('25596','Chetan Singh','Chetan','915','100-1000','FL','Y');

Query 8
INSERT INTO CUSTOMER VALUES('11643306','Chokshi','Rakesh','A','912','333-1245','0');
Query 9
ALTER TABLE CUSTOMER ADD Customer_rating VARCHAR(14);

Query 10
SET SQL_SAFE_UPDATES=0;
Update CUSTOMER SET Customer_rating='GREAT' where CUS_CODE='11643306';

Part B
NoSQL Database
The Relational database is not able to manage the big data or large volume of data. To solve this
issue, the NoSQL database is developed. NoSQL means Not Only SQL that supports the
structured, unstructured and semi-structured data. The e-commerce websites, social networking
websites have to manage a large amount of data and the Relational database management system
cannot handle the same amount of data sufficiently. (Violino B. 2019)
There are four categories of the NoSQL database-
- Key-Value NoSQL database
- Document-based NoSQL database
- Column-based NoSQL database
- Graph-based NoSQL database
(Guru99. 2019)
Key-Value No-SQL Database
The key-value NoSQL database is used to manage the big data. The key is unique in key-value
database and the value will be any of the BLOB, JSON, string etc. the key identifies the values in
the database. It is very fast because the data is stored in key-value NoSQL database in the form
of hash table. The Amazon website which is the very busy e-commerce website uses the key-
value NoSQL database.
The keys and values are stored in the form of tables as mentioned below. In one column there is
a key and in another column values are stored with specific keys. All values are identified by the
specified keys.
Key Value
Key Text

Document-based No-SQL Database
The document-based NoSQL database is used to manage the big data. It is just like key value
NoSQL database but the difference is that the data is stored in the form of documents. Each
document is identified by unique key.
The document-based NoSQL stored the data in the documents in the form of XML or JSON
which is very structured data and the users can run SQL queries like relational database. There
are so many blogging websites that are using the document-based NoSQL database like twitter
etc. The website access the data in terabytes in a day and the document-based NoSQL database
manages the entire data sufficiently. The CloudBase database is one of the best examples of the
document-based NoSQL database.
Column-Based No-SQL Database
The column-based NoSQL database is used to manage the big data. In this, the data is stored in
the form of cells which contains lots of columns and each column stored the data separately. The
SQL queries can be easily queried in the column-based NoSQL database. The aggregate function
queries give better results in this.
The Goog;e’s BigTable is one of the best examples of the document-based NoSQL database.
Library Management Systems are mostly built with the column-based NoSQL database.
Graph-Based No-SQL Database
The graph-based NoSQL database is used to manage the big data. In this, the data is stored in the
form of nodes and edges. The nodes represent the entities and edges represent the relationships.
The traversal time is very less in the graph-based NoSQL database. it is very easy to add new
nodes in the graph-based NoSQL database in future.
The OrientDB is one of the best examples of the graph-based NoSQL database. E-commerce
websites are mostly built with the graph-based NoSQL database.

References
Violino B. (2019). How to choose the right NoSQL database, Type. Retrieved
https://www.infoworld.com/article/3260184/how-to-choose-the-right-nosql-database.html
Guru99. (2019). NoSQL Tutorial: Learn NoSQL Features, Types, What is, Advantage. Retrieved
from: https://www.guru99.com/nosql-tutorial.html