CIS 2002 Semester 1 2019 Assignment 1: Big Data and Database Solutions

This assignment, part of CIS 2002, delves into the realm of Big Data and traditional databases, focusing on their comparison and contrasting their features. The solution begins by explaining the relational model and its limitations, particularly in handling modern data complexities. It then explores alternative database models like NoSQL and NewSQL, highlighting their advantages and disadvantages. The assignment includes several SQL queries demonstrating practical database operations and data retrieval. The document provides a comprehensive overview of database models, covering their applications and suitability for various data types. Furthermore, the assignment involves writing an opinion article for a technology magazine, discussing the evolution and challenges of database technologies in the context of Big Data. The document provides solutions to SQL queries which covers different SQL functions and their implementation.

Big Data and traditional databases.
Relational model is a model proposed by E.F Codd to model data using conceptual model which is then
transformed to a logical model which finally becomes a physical model. The relational model is used to
represent how databases store data. It specifies that data in a database is stored in form of relations. A
relation has attributes which define the relation. For example a student relation with attributes;
studentID, first name and last name. A relation with attributes has data stored in form of tuples. For a
relational database, relations can be related to each other. The relational model was designed when
data was neat and organized but relational model has a lot of limitations dealing with today’s type of
data. One of the limitations of relational databases is the rigidity coming from data organization into
tables. This is because, not all type of data can fit naturally into a table because of normalization
performed on the relations. Normalization and organization of data into tabular structure means that
the data stored in a table must be the same thus leading to tables having columns that are optional or
allow null values. Optional columns in relational databases lead to wastage of space. Another limitation
of the relational databases is that changes in the schema are extensive. Changing a record to add a new
field means that the all records should get that change.
There are other alternatives to relational databases. These alternatives were implemented to take on
the best features of relational databases while improving on the limitations. Some of the alternatives
are;
 NoSQL- NoSQL is initials for a non-relational SQL which is an approach for designing databases
that includes many data models including columnar and graph formats and key-value
documents. This type of database was introduced as an alternative to the relational model.
Some of the advantages of NoSQL are;
o NoSQL databases scale out horizontally and work with both unstructured and structured
data.
o NoSQL does not have schemas thus making it easy to make changes.
o NoSQL are high available.
Some of the disadvantages of NoSQL model are;
o NoSQL have little support for joins.
o NoSQL databases do not ensure ACID properties.
o Data in NoSQL databases is not normalized thus require mass updates.
o NoSQL have limited indexing.
 NewSQL
NewSQL is a combination of relational model and NoSQL models. They are mostly designed for
web-scale applications but still need schemas and joins upfront. Advantages of NewSQL include;
o NewSQL databases minimize complexity by offering stronger consistency and full
support for transactions.
o NewSQL takes on the best of relational databases and NoSQL databases.
 Disadvantages of NewSQL are;
o NewSQL databases are not as general purpose as relational databases.
o NewSQL memory architectures are not suitable for databases with few terabytes of
data.

All types of database models are applicable with the type of data being stored in the database thus
there is no perfect database model but the type of data being stored on the database.
SQL
1.
Query
select * from
(select count(customer#) as "Total Customers" from customers) a,
(select count(customer#) as "Direct Customers" from customers
where referred IS NUll) b,
(select count(customer#) as "Referred Customers" from customers
where referred IS NOT NUll) c;
Output
2. Query
select customer#,lastname, firstname,city from customers where
state='FL' and referred IS NULL;
Output

3. Query
select firstname || ' , ' || lastname as
"Customers",region,referred from
customers where region IN ('W','SW','NW');
Output
4. Query

select c.customer#,firstname,lastname, order#,orderDate from
customers c
inner join orders o on o.customer#=c.customer#
where o.shipdate IS NULl
order by o.orderDate;
5. Query
select isbn,title from books where category like '%COM%' and
discount is null;
Output

6. Query
select isbn,title,retail from books where REMAINDER(To_Char(pubDate,'yyyy'),2)!=0
order by title;
Output
7. Query
select b.isbn,quantity,paidEach as "Paid Each",
(quantity*PaidEach) as "Total Order Price" from books b
inner join orderitems oi on oi.isbn=b.isbn
where quantity>1
order by (quantity*PaidEach) desc;
Output

8. Query
select p.name,b.title,b.retail-b.discount as "DISCOUNTED PRICE"
from books b
inner join publisher p on p.pubID=b.pubID
where b.discount is not null
order by p.name asc, b.retail-b.discount desc;
Ouput

9. Query
select b.ISBN,title,retail,pubDate from books b
where category like '%FAMILY%' OR category like '%COMPUTER%' and
pubDate<'30-jun-2004';
Output
10. Query
select a.customer#,a.lastname,a.city,a.zip as "Customer
Zip",b.zip as "Referrer ZIP" from customers a,customers b
where a.customer#=b.referred and a.lastname=b.lastname;

Section C
Question 1

Question 2

Question 3
Question 4