ITC566 Assessment 4: SQL Queries and NoSQL Databases

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

ITC566
Assessment 4

Contents
Introduction:.............................................................................................................................................3
Part A:......................................................................................................................................................3
SQL Queries:.......................................................................................................................................3
Part B:......................................................................................................................................................8
NoSQL Databases:...............................................................................................................................8
Block Chain Databases:.......................................................................................................................9
Conclusion:............................................................................................................................................10
References:.............................................................................................................................................10
Figures
Figure 1 Line Units Sum..........................................................................................................................3
Figure 2 Null values Query......................................................................................................................3
Figure 3 Greater than less than Query.....................................................................................................4
Figure 4 Min aggregate function Query..................................................................................................4
Figure 5 Getting Particular Columns Query............................................................................................5
Figure 6 Use of Except Query.................................................................................................................5
Figure 7 Use of Not In Query..................................................................................................................6
Figure 8 Use of Join with group by Query..............................................................................................6
Figure 9 Use of join with group by Query...............................................................................................7
Figure 10 Multiple Inner Join Query.......................................................................................................7

Introduction:
In the given scenario, we are provided with two parts, Part-A contains some of the queries
which are to be solved in SQLite. So, to solve these queries we are going to use SQLite
server. For implementing the queries in the server we first have to enter few commands
provided us for creating database, creating header and showing data in the form of columns.
In the second part, Part-B, we are asked to write descriptions on any of the two topics given.

Part A:
SQL Queries:
1: select sum (line_units) as lu_sm ,inv_number from line group by inv_number;
Figure 1 Line Units Sum
2: Select pcd.p_code, pcd.p_descript, pcd.p_indate, pcd.p_qoh, pcd.p_min, pcd.p_price,
pcd.p_discount, pcd.v_code from product pcd where v_code = '';
Figure 2 Null values Query
3: Select ivc.inv_number, ivc.cus_code, ivc.inv_date, ivc.inv_subtotal, ivc.inv_tax,
ivc.inv_total from invoice ivc where inv_subtotal> 25 AND inv_subtotal< 75;

Figure 3 Greater than less than Query
4: Select ivc.inv_number, ivc.cus_code, ivc.inv_date, ivc.inv_subtotal, ivc.inv_tax,
ivc.inv_total from invoice ivc where inv_subtotal is (select min (inv_subtotal) from invoice);
Figure 4 Min aggregate function Query
5: Select distinct vdr.v_code, vdr.v_name from vendor vdr inner join product pcd where
vdr.v_code=pcd.v_code;
Figure 5 Getting Particular Columns Query
6: Select vdr.v_code from vendor vdr except select pcd.v_code from product pcd;
Figure 6 Use of Except Query
7: Select vdr.v_name, vdr.v_code from vendor vdr where vdr.v_code not in (select
pcd.v_code from product pcd);

Figure 7 Use of Not In Query
8: select count (Distinct (pcd.p_code)) as p_code,vdr.v_code from product pcd,vendor vdr
where pcd.v_code=vdr.v_code group by pcd.v_code;
Figure 8 Use of Join with group by Query
9: select count (Distinct (pcd.p_code)) as p_code,vdr.v_code,vdr.v_name from product
pcd,vendor vdr where pcd.v_code=vdr.v_code group by pcd.v_code;
Figure 9 Use of join with group by Query
10: Select ct.cus_code ,ivc.inv_number, pcd.p_code from customer ct inner join
invoice ivc on ivc.cus_code=ct.cus_code inner join line li on
li.inv_number=ivc.inv_number inner join product pcd on pcd.p_code=li.p_code
where ct.cus_code=10011;

Figure 10 Multiple Inner Join Query

Part B:
Database:
A database can be defined as a collection of data, stored on a system and can be easily
accessed using computer systems from any place. Traditionally, data being stored in a form
of files, or we can say thousands of files containing a large amount of data written on them.
The main problem people faced were access time issues which is resolved by the electronic
databases.
NoSQL Databases:
NoSQL also termed as Not Only SQL is an approach to databases that actually represents an
alternate to traditional RDBMS i.e. Relational Database Management System, here the tables
are created, placing of data in tables and data schema is being designed carefully and properly
before building the database("NoSQL Databases", 2017). NoSQL are different from RDBMS
as they do not depend upon structures instead they use more efficient and flexible models.
NoSQL is mainly being used to store unstructured data, which is growing too fast than any
other structured type of data. Unstructure data includes: user and session data; chats,
messaging and log data, also includes data of time series such as Internet of things and device
data; also large objects are included such as images and videos ("NoSQL Databases
Explained", 2018).
Features of NoSQL:
 Main feature of the NoSQL is the Scalability. They use horizontal scale out
methodology as it makes adding and reducing capacity easy and quick. This mainly
helps in reducing cost and complexity.
 Performance is also a key feature of NoSQL, adding commodity resources,
organizations can increase performance with NoSQL databases. This actually helps in
a fast user experiences

 Availability is also a key feature of NoSQL. If we talk about RDBMS, complexity is
high so to decrease the complexity NoSQL databases are designed also they provide
high availability.
 Also, a new feature can be Global availability of NoSQL databases. As the
automatically repetition of data on multiple servers and cloud sources, there is
minimization of the recession of the availability of data and provide consistent
application experience to the users wherever they are located.
 Last key feature of the NoSQL is Flexible Data Modelling. It basically helps in
implementing flexible data models. (Rouse, Vaughan & Beal, 2018).
Block ChainDatabases:
Block Chain is a kind of database which is actually stores a collection of data or information
in data structure called as Blocks. If we talk about Database, the data is being stored in tables
whereas in Block chain Databases the data stored in Blocks. We can refer a Block Chain as
database but Database is actually not a Block Chain. If we compare database with Block
Chain, database is centralised whereas block chain is decentralised also database requires
administration but block chain doesn’t have any administrator ("BigchainDB,Theblockchain
database.", 2018).
Features of Block Chain Databases:
 The most of important feature of the Block Chain is increasing of the capacity of the
complete network. As the database is centralised, block chain being decentralised, a
lot of computers working together provides a huge power and capacity.
 Another key feature can be the security provided by the Block Chain databases as
there is zero chances of getting the system or the computer of getting switched off.
Hacking chances are also close to negligible if we talk about Block Chain databases.

 Immutability is another key feature of the Block Chain Database. If we talk about
database, it is centralised so can easily get hacked. But Block Chain Databases are
decentralised so it is impossible to hack and they are immutable.
 Another key feature is faster settlement, as if we talk about old banking systems, there
it needs long time to settle. So, Block Chain helps in transferring money and doing
transactions at high speeds.
 Last key feature is decentralised system, as if we talk about database, everything is
centralised so administrator plays an important role where as in block chain databases
every user have equal access (Tabora, 2018).

Conclusion:
In the conclusion part, we have included all the work we have done in the above two parts. In
the First part, we have performed the given ten queries on the server named SQLite. We have
used the commands provided to us for creating the database, header and command for
showing data in form of columns. In the second part, we have provided the descriptions of
NOSQL databases and Block Chain Databases with their features.

References:
 Rouse, M., Vaughan, J., & Beal, B. (2018). What is NoSQL (Not Only SQL
database)? - Definition from WhatIs.com. Retrieved from
https://searchdatamanagement.techtarget.com/definition/NoSQL-Not-Only-SQL.
(Rouse, Vaughan & Beal, 2018).
 NoSQL Databases Explained. (2018). Retrieved from
https://www.mongodb.com/nosql-explained("NoSQL Databases Explained", 2018).
 NoSQL Databases. (2017). Retrieved from http://basho.com/resources/nosql-
databases/("NoSQL Databases", 2017).
 BigchainDB • • Theblockchain database. (2018). Retrieved from
https://www.bigchaindb.com/("BigchainDB • • The blockchain database.", 2018).
 Tabora, V. (2018). Databases and Blockchains, The Difference Is In Their Purpose
And Design. Retrieved from https://hackernoon.com/databases-and-blockchains-the-
difference-is-in-their-purpose-and-design-56ba6335778b(Tabora, 2018).