Practical Assessment on Database Management Systems

Desklib provides solved assignments and past papers to help students succeed.

ITC556
DATABASE SYSTEM
ASSESSMENT – 4

Table of Contents
Assessment Part A:..........................................................................................................................3
Assessment Part B:........................................................................................................................10
References......................................................................................................................................14
2

Assessment Part A:
1.
select vond.v_code, vond.v_name, count(proc.v_code) as supplynumber
from product proc, vendor vond
where vond.v_code = proc.v_code
group by vond.v_code, vond.v_name;
Figure 1 Q1
2.
select cso.cus_lname, sum(ivo.inv_total) as Total_invoice_value
from customer cso, invoice ivo
where cso.cus_code= ivo.cus_code
group by cso.cus_lname, ivo.cus_code;
3

Figure 2 Q2
3.
select concat(cso.cus_fname,' ', cso.cus_lname) as cust_name , count(ivo.inv_number) as
Num_of_invoices
from customer cso, invoice ivo
where cso.cus_code= ivo.cus_code
group by cso.cus_lname, cso.cus_fname, ivo.cus_code;
4

Figure 3 Q3
4.
select inv_number, sum(line_units*line_price) as SumPrices
from line
group by inv_number;
Figure 4 Q4
5

5.
select * from invoice where inv_subtotal=(select min(inv_subtotal) from invoice);
Figure 5 Q5
6.
select cso.cus_lname, ivo.inv_number
from customer cso, invoice ivo
where cso.cus_code= ivo.cus_code and cso.cus_lname='orlando';
6

Figure 6 Q6
7.
insert into vendor values (76588,'James, Inc.','michelson','705','890-7654','TN','Y');
Figure 7 Q7
8.
insert into customer values (11690761,'THOTA','Laxman Naidu','A','999','432-8099', 0)
7

Figure 8 Q8
9.
alter table vendor
add customer_rating varchar(14);
Figure 9 Q9
10.
8

update vendor
set Customer_rating = 'great'
where v_name = 'James, Inc.';
Figure 10 Q10
9

Assessment Part B:
NoSQL Database
It is basically an approach that is used to design a database and it is a non-relational database
management system. It is beneficial for web pages and big data. In this approach, tables are used
to store data and the schema of the database is designed carefully before its development
(Chandra, 2015). It stands for “not only SQL” that comprises of many database technologies that
are able to store semi-structured, polymorphic, structured, and unstructured data. Features that
NoSQL exhibits are:
 It does not follow relational model
 Contains no complex features
 No requirement of normalization
 It does not provide a table that contains records in flat fixed columns
 It is free of schema
 Uses simple API and is a distributed database
Advantages:
 Capability of big data
 Easy replication
 Used as analytical or primary source of data
 No existence of single point failure
 Fast performance
 Separate layer for caching is not required
 Simple to implement (Lourenco, et. al., 2015)
Disadvantages:
 No rules of standardization
 Less well performance than relational data
 Limited query capabilities
 Open source option
 Does not provide capabilities of database
10

Types of NoSQL Database:
 Key-value pair based:
Data is stored in the form of a key-value pair, where the key is defined as the attribute. It is
designed in a way that it becomes capable of handling heavy load of data. It stores data in a
table similar to the hash table where each and every key is unique and value can be BLOB,
JSON, string, etc. In this, no optimization of query is used and they are not used for extracting
data (Rouse, 2019). This type is used as dictionaries, collection, associative arrays, etc.
Example: Dynamo, Riak, Voldemort, Redis, Oracle NoSQL.
Figure 1: key-value pair
(Source: guru99.com)
 Column-oriented graph:
It works on columns where each column is separate and is treated separately. Single column
values are contiguously stored and are capable of giving high performance when aggregation
queries are put like MIN, MAX, SUM, COUNT, and AVG (Chandra, 2015). query
optimization can be used in this type of database. Example: HBase, Hypertable, Cassandra,
etc.
11

Figure 2: Column-based
(Source: guru99.com)
 Graphs based:
It is used to store entities and their relations and they are stored as nodes and edge
respectively (Rouse, 2019). It uses graphical representation. Its nature is multi-relational and
hence traversing is quick. Data transforming is easy in this database. This type is used for
logistics, social networks, and spatial data. Example: Infinite graph, FlockDB, OrientDB,
Neo4J (Lourenco, et. al., 2015).
Figure 3: Graph-based
(Source: guru99.com)
 Document-oriented:
12