University Database Systems: Assessment Item 4 SQL and NoSQL Report

This report comprises two main parts: SQL queries and a discussion on NoSQL databases. Part A presents SQL queries, including SELECT, INSERT, and UPDATE statements, along with their outputs. Part B provides an overview of NoSQL databases, explaining different types such as key-value, document, column-based, and graph-based databases. Each type is described with its characteristics and use cases, highlighting the differences between NoSQL and traditional relational database management systems. The report references relevant sources to support the information presented.

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

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

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

Query 3
select concat(cus.cus_fname,' ' , cus.cus_lname) as CustomerName , count(inv.cus_code) as
'NumberOfInvoices' from customer cus inner join invoice inv on cus.cus_code=inv.cus_code group by
cus_fname, cus_lname;
Query 4
select line_units, line_price, round(sum(line_total),2) as TotalUnitPrice 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','Dr Phair','Phair','904','456-0091','FL','Y');

Query 8
INSERT INTO CUSTOMER VALUES('11694768','Singh','Gurkirat','A','615','844-2333','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='11694768';
Part B
NoSQL Database
NoSQL database is names as Not Only SQL database which works on big data and real time
applications which need very high speed data. There are different types of NoSQL databases
that have different storing techniques, different features, different usage and different specialty
etc. it is totally different from traditional Relational Database Management Systems. Relational
Database Management Systems use the structured data only while the NoSQL database uses the
structured, semi-structured, polymorphic data etc. The NoSQL database is of different types and
is categorized in following types-
- Key-Value Form
- Document Form
- Column based
- Graph based
(Tezer O.S. 2014)
(Violino B. 2019)

Key-Value Form
In Key-value form the data is stored in the hash table in the form of key and value. The key will
be unique in the entire hash table that will be used to identify the data and the value may be text,
BLOB, JSON etc.
Most of the shopping cart systems use this technique. The speed of fetching the data is very in
this. The value that is stored into the hash table in the form of BLOB, JSON or string has no
specific schema. Therefore, it is very good to store large volume of data and displays the results
in a very short period of time.
The structure of the hash table is like this-
Key Value
Key 1 Data
Key 2 Data
Document Form
In the Document Form, the data is stored in the form of documents in a structured way. Because
the data is in the form of structured way, the data can be fetched by using SQL queries. The
format of documents may be XML or JSON. It is just like the Key-Value form. The Key is
unique and the value is in the form of documents.
The shopping cart systems, blogging systems etc. use this form of NoSQL because there is huge
data in blogging sites and the users share data in terabytes in one day. It is not easy to manage
the huge data by relational database management systems. The websites twitter etc. uses this
document form NoSQL database. Lotus Notes, MangoDB are the database that supports the
Document form NoSQL database.
Column Based
The base of column based NoSQL is the BigTable of google. The data is stored in the columns
in this and each column is independent of each other. The aggregate functions queries work well
in column bases NoSQL database.
Library systems, Inventory systems etc use this column based NoSQL database. HBase is the
database that supports the column based NoSQL database.
Graph Based

The Graph based NoSQL database stored the data just like entities of RDBMS. The nodes in the
Graph work as entities and edges work as relationship in between the entities. The nodes and
edges are uniquely identified by a unique key.
In the graph based NoSQL database the data traversal is very fast and easy. Social networking
sits like Facebook use this Graph based NoSQL database.
Infinite Graph is the database that supports the column 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
Tezer O.S. (2014) A Comparison Of NoSQL Database Management Systems And Models.
Retrieved from: https://www.digitalocean.com/community/tutorials/a-comparison-of-nosql-
database-management-systems-and-models