ITC556 Assessment 4: DBMS Queries and Database Types

Desklib provides past papers and solved assignments for students. This assignment covers DBMS queries and database types.

ITC556
ASSESSMENT 4

1
Table of Contents
Introduction...........................................................................................................................................3
Part- A Assignment................................................................................................................................4
Part-B Assessment.................................................................................................................................7
DATABASES:..................................................................................................................................7
Blockchain Database:....................................................................................................................7
Cloud Database..............................................................................................................................8
Conclusion.............................................................................................................................................9
References...........................................................................................................................................10
Figures
Figure 1: Query 1 screenshot...................................................................................................................4
Figure 2: Query 2 screenshot...................................................................................................................4
Figure 3: Query 3 Screenshot..................................................................................................................4
Figure 4: Query 4 Screenshot..................................................................................................................4
Figure 5: Query 5 Screenshot..................................................................................................................5
Figure 6: Query 6 Screenshot..................................................................................................................5
Figure 7: Query 7 Screenshot..................................................................................................................5
Figure 8: Query 8 Screenshot..................................................................................................................6
Figure 9: Query 9 Screenshot..................................................................................................................6
Figure 10: Query 10 Screenshot..............................................................................................................6

2
Introduction
This assignment is based on DBMS and its queries. Part- A consists of SQLite3 queries and
their implementations are shown by pasting the images of the output. Part-B consists of
detailed information of the database and its types are written and in the subpart 2 topics/types
of database are explained with their features.

3
Part- A Assignment
Q1: select inv_number, sum (line_units) as UNIT_SUM from line group by inv_number;
Figure 1 Q1 screenshot
Q2: select * from product where v_code is Null;
Figure 2: Q2 screenshot
Q3: select * from invoice invsub where invsub.inv_subtotal> 25 AND invsub.inv_subtotal<
75;

4
Figure 3: Q3 Screenshot
Q4: select i_v.inv_date, i_v.cus_code, i_v.inv_number, i_v.inv_subtotal, i_v.inv_tax from
invoice i_v where inv_subtotal=(select min(inv_subtotal)from invoice);
Figure 4: Q4 Screenshot
Q5: Select distinct v_one.v_code, v_one.v_name from vendor v_one, product p_one where
v_one.v_code=p_one.v_code;

5
Figure 5: Q5 Screenshot
Q6: select distinct vr.V_CODE from vendor vr, product p_one where
vr.V_CODE=p_one.V_CODE;
Figure 6: Q6 Screenshot
Q7: select distinct v_one.V_CODE,v_one.V_NAME
from vendor v_one

6
where v_one.V_CODE not in(select p_one.V_CODE from product p_one );
Figure 7: Q7 Screenshot
Q8: select vd.v_code as code, count(p_code) as product_code
From product pt,vendor vd where pt.v_code=vd.v_code group by pt.v_code;
Figure 8: Q8 Screenshot

7
Q9: select count(product.p_code) as p_code,vendor.v_code,vendor.v_name from
product,vendor where product.v_code=vendor.v_code group by product.v_code;
Figure 9: Q9 Screenshot
Q10: select customer.cus_code, invoice.inv_number, product.p_code
from customer inner join invoice on invoice.cus_code=customer.cus_code
inner join line on line.inv_number=invoice.inv_number
inner join product on product.p_code=line.p_code
where customer.cus_code=10011;

8
Figure 10: Q10 Screenshot

9
Part-B Assessment
DATABASES:
A database is a group of data or information stored in one place where all the data is in an
organized format. Through the organized format the data is easily retrieved. The features like
adding, deleting and altering the data can be done easily. The data can be loaded in a
structured or unstructured format and data can be of any field/ type. DBMS queries are used
to navigate in the database and these queries help to filter the information
Relational database: This type of database is used to store data in the tabular format and was
invented in 1970 in IBM. Each column or tuple is somehow related to one another in the
same table. Primary key and foreign key are used to link the tables together.
Network Database: This type of database is mainly used for advanced computers. In these
numerous connections are to be made between data (Date, 2006)
Blockchain Database:
Blockchain is the type of database which acts as a ledger that can be shared, used for keeping
the record of the history of transactions. The data in blockchain is not stored on any single
server or single location; instead, it is an open peer-to-peer network and is operated by
thousands of global computers. (Glaser, 2017)
The most popular technology which is working on blockchain is: Bitcoin is cryptographically
secured because the information is stored in form of blocks which are uniform in size, for
every block, there is hashed information which derives from the previous block.
Features of Blockchain databases:
1) The international banking is incredibly slow taking days to the method. so there's the
requirement to upgrade to their system to save lots of the time and cash monetary business.
2) History or any movement that is going on is created and managed inside a network,
therefore; every single movement is verified and establishes a secured network.
3) Blockchain technology is secured similarly as localized which implies each user has its
own key and no single plus rely upon the administrator.

10
5) To avoid the corrupted there ought to be the trust on a 3rd party to stay the
knowledge that is correct to create any changes the exhausting fork miners, node operators
used. (Smith, 2017)
Cloud Database
This database helps to run on the cloud computing technology which helps the user to store
everything on the cloud. It increases the availability and scalability of the database. It is just
similar to the traditional database or it can be said like, it is just the advanced version of the
traditional database. There are many clouds services available like AWS which termed as an
Amazon Web Server, IBM cloud etc. Cloud allows the client to purchase a virtual machine
for a limited period of the time. They also provide free services for limited time and then they
just have to pay some amount to continue the cloud services .the data here is the most secure
one, there can be no attacks happen on it. The end user uses to configure the database
instance. Database provider both the scalability, vertical scalability and horizontal scalability.
Basically it an advanced technology for storing the data and provided it security at peak.
(Ramanathan, Goel, & Alagumalai, 2011)
CLOUD DATABASE FEATURES
 The user can be a command, it may be presented as a service and command by the
providers.
 It is also assisted by SQL (contain MySQL) or other SQL like NoSQL.
 The databases may be retrieved and built services via a cloud platform.
 No need to buy the resolute hardware, it allows host databases for enterprises persons.
 It is command via the vendor provider (API) and web interface.
 It gives some previous functions like traditional databases with modifying of more
flexible functions of the cloud database.
 It transforms according to the user need, it has scalability.
Conclusion

11
There is 2 part of the assignment which is based on DBMS. In the 1st part, certain queries are
written which are to be performed and the screenshots of the output are written. All the
queries are written with respect to SQLite3. In the 2nd part, the brief introduction of the
database is written with its types and blockchain and cloud database database are explained
with their features.