Assignment-2

Database Concepts
Table of Contents
Part A..........................................................................................................................................2

Non Trivial Functional Dependencies....................................................................................2

Highest Normal form of Relations.........................................................................................2

Final Relational Database Schema.........................................................................................3

Part B..........................................................................................................................................4

Part C........................................................................................................................................15

Introduction..........................................................................................................................15

Traditional Relational Database Systems.................................................................................15

Advantage & Disadvantage..................................................................................................15

No-SQL Database systems.......................................................................................................16

Advantage & Disadvantage..................................................................................................16

Conclusion................................................................................................................................17

Recommendations....................................................................................................................17

References................................................................................................................................18
Part A

Non Trivial Functional Dependencies

1. Author: No functional dependencies on any other attributes

2. Publisher: No functional dependencies on any other attributes

3. Written by: Depends on the ISBN of the book and the author

4. Book: Depends on Publisher

5. Warehouse: No functional dependencies on any other attributes

6. Stocked at: Depends on Book and Warehouse

7. Shopping Cart: Depends on Book and Stocked At

8. Customer: Depends on the Shopping Cart

Highest Normal form of Relations

All the relations listed in the process are already in the 3NF form, and hence, no

normalisation is required for them. The choice of primary key, foreign key and the relations

between the attributes, however, is incorrect. The following points can be considered for Part

3 and Part 4:

1. Title is redundant in Written By

2. Author ID should be added in author table, as email is a bad primary key

3. Publisher ID should be added in publisher table, as publisher name can be not unique

4. Customer ID should be added as email ID is bad primary key

5. The cart should have reference to customer, and not the vice versa, as a customer can

have many carts but one cart would have only one customer.
Final Relational Database Schema

After consideration of the points that has been mentioned above for the provided database

ERD, we can make the final relation schema as follows:

Author (AuthorID, Email, Name, Address, Telephone1, Telephone2, Telephone3)

Publisher (PublisherID, Name, Address, URL, ABN)

WrittenBy (AuthorID*, ISBN*)

Book (ISBN, Title, Edition, Year, ListPrice, PublisherID*)

Warehouse (Code, Address)

StockedAt (ISBN*, Code*, StockQty)

ShoppingCart (CartID, TimeStamp, ISBN*, BuyPrice, Qty, CustomerID*)

Customer (CustomerID, Email, Name, Address)

Part B

Write SQL queries for the following tasks.

1) Display the first name and last name of the authors who wrote books on the subject
"DataBases".

a. Write your query using a sub query.

Select au.FIRSTNAME,au.LASTNAME
from author au
where au.AUTHORID IN
(select wr.AUTHORID from written_by wr
join book bo on wr.BOOKDESCID = bo.BOOKDESCID
join subject su on bo.SUBJECTID = su.SUBJECTID and su.SUBJECTTYPE =
'DataBases')

b. Write your query using JOINs.

select au.FIRSTNAME,au.LASTNAME
from author au
join written_by wr on au.AUTHORID = wr.AUTHORID
join book bo on wr.BOOKDESCID = bo.BOOKDESCID
join subject su
on bo.SUBJECTID = su.SUBJECTID and su.SUBJECTTYPE = 'DataBases'

2) Who translated the book "American Electrician's Handbook"? Display the first name,
middle names, and last name of the translator. Each authors’ role in the writing of the
book is described in “role” attribute in written_by table.

select au.FIRSTNAME, au.MIDDLENAME, au.LASTNAME

from author au join written_by wr on au.AUTHORID = wr.AUTHORID
join book bo on bo.BOOKDESCID = wr.BOOKDESCID where
bo.TITLE = "AMERICAN ELECTRICIAN'S HANDBOOK" and wr.ROLE =

'Translator'

3) Display the titles of books that haven't been borrowed.

select bo.TITLE
from book bo join book_copy bc on bo.BOOKDESCID = bc.BOOKDESCID
where bc.BOOKID not in (select BOOKID from borrow_copy)

4) A borrower wants to borrow the book titled "PRINCIPLES AND PRACTICE OF

DATABASE SYSTEMS", but all of its copies are already borrowed by others. Write
two queries to display other recommended titles using the following methods.
 a) Using partial matching of the book title -- note that the borrower is interested in a
"DATABASE" book.
select TITLE
from book
where book.TITLE LIKE '%DATABASE%'
and book.TITLE <> "PRINCIPLES AND PRACTICE OF DATABASE

SYSTEMS"

b) By searching of other books written by the same author (i.e. the author of
"PRINCIPLES AND PRACTICE OF DATABASE SYSTEMS"

select b.TITLE
from book b join written_by w on b.BOOKDESCID=w.BOOKDESCID join
author a on a.AUTHORID=w.AUTHORID
where a.AUTHORID IN
( select au.AuthorID
from author au join written_by wr on au.AUTHORID=wr.AUTHORID join book
bo on bo.BOOKDESCID=wr.BOOKDESCID
where bo.TITLE='PRINCIPLES AND PRACTICE OF DATABASE SYSTEMS')
AND b.TITLE<>'PRINCIPLES AND PRACTICE OF DATABASE SYSTEMS'

5) Display the list of publishers who have published books on the subject "DataBases".
Your query should display publisher's full name, along with "DataBases" book titles
they published.

select pu.PUBLISHERFULLNAME, bo.TITLE

from publisher pu join published_by pu_by on pu.PUBLISHERID =
pu_by.PUBLISHERID
join book bo on bo.BOOKDESCID = pu_by.BOOKDESCID join subject sub on
sub.SUBJECTID = bo.SUBJECTID
where sub.SUBJECTTYPE = 'DataBases'

6) Display the titles of books that never borrowed.

a. Write your query using OUTER JOINs.

select bo.TITLE
from book bo left outer join book_copy bc on bo.BOOKDESCID=bc.BOOKDESCID
where bc.BOOKID NOT IN( select BOOKID from borrow_copy)
UNION
select bo.TITLE
from book bo
where bo.BOOKDESCID NOT IN( select BOOKDESCID from book_copy)

b. Write the query again without using OUTER JOINs

select bo.TITLE
from book bo join book_copy bc on bo.BOOKDESCID=bc.BOOKDESCID
 where bc.BOOKID NOT IN( select BOOKID from borrow_copy)
UNION
select bo.TITLE
from book bo
where bo.BOOKDESCID NOT IN( select BOOKDESCID from book_copy)

7) Display full names of publishers with whom the author Alfred Aho published his
book(s). Your query must use EXISTS clause.

SELECT DISTINCT pu1.PUBLISHERFULLNAME

FROM publisher pu1 JOIN published_by pub1 on
pu1.PUBLISHERID=pub1.PUBLISHERID join written_by wr1 on
pub1.BOOKDESCID=wr1.BOOKDESCID join author au1 on
wr1.AUTHORID=au1.AUTHORID
WHERE EXISTS
(SELECT *
FROM publisher pu2 JOIN published_by pub2 on
pu2.PUBLISHERID=pub2.PUBLISHERID join written_by wr2 on
pub2.BOOKDESCID=wr2.BOOKDESCID join author au2 on
wr2.AUTHORID=au2.AUTHORID
AND
lower(au2.FIRSTNAME) = 'alfred' AND
lower(au2.LASTNAME) = 'aho' )

8) Display the first name and last name of authors who wrote more than 3 books. Along
with each name, display the number of books as well.

select count(*) AS 'Number of Books', au.FIRSTNAME, au.LASTNAME

from author au join written_by wr on au.AUTHORID=wr.AUTHORID
group by au.AUTHORID
having count(*) > 3

The SQL Query does not return any value as no author has written more than 3 books.

9) Display the title of the book which has most physical copies. If there are more than
one book with the largest number of copies, show them all. Your query should show
the number of copies along with the title.
SELECT bo.TITLE, count(boc.BOOKID) AS 'Number of Copies'
FROM book bo join book_copy boc on bo.BOOKDESCID=boc.BOOKDESCID
group by bo.BOOKDESCID
having count(boc.BOOKID) = (SELECT count(boc1.BOOKID) AS 'Number of
Copies'
FROM book bo1 join book_copy boc1 on bo1.BOOKDESCID=boc1.BOOKDESCID
group by bo1.BOOKDESCID
order by count(boc1.BOOKID) desc
Limit 1 )
The output of SQL queries is given below in the form of screen shots:
1) BOOKS BORROWED IN A SINGLE TRANSACTION RETURNED
SEPARATELY
Part C

Introduction

The analysis is based on making the decisions to rebuilding the public transport with the
using of power for the website with the mobile apps. The assigning of the tasks is related to
the producing of the impact with the analysis related to the backend database server options
which can directly be able to cater and deal with the projected growth. This has to be done in
the time of 5 years or so (Zafar et al., 2016). The website includes the usage of how the
smartphone apps tend to cater the different users and then one million timetabling queries that
are processed in a day. The expectations are for the steady growth with 10% of the increase
annually. The choice of the server is about how the back-end computer programs are able to
maintain the finding, saving and changing the data which could easily be accessed through
the back-end languages. There are companies which work on improving the dedicated server
database with the hardware and software setup.

Traditional Relational Database Systems

The database system is about handling the relational database where there are relational
database systems that tend to make use of SQL mainly for the querying and the maintenance
of database. The model is able to organize the data in the form of tables and then there are
unique key identification of each row. The processing involves selecting or modify depending
upon how the new unique values are set for the primary key that is generated. The example is
Oracle and SQL Server (Seda et al., 2018). In relational database works on the data with
storage and then working on accessing the relations with storing and handling the data which
needs to be recorded to be a tuple. The formats are for the data and then accessing the
information through proper relations and variations. They are for the base relations and there
are implementations that are for the computation processes in the form of views. The derived
relations are depending upon grabbing information from the different perspectives.

Advantage & Disadvantage

The advantage is about the MS SQL Server 2008 with focus on Microsoft Corporation which
is set with scalability of server system that makes it easy for building, managing and handling
the query of the data warehouse (Fan et al., 2015). The advantage is about the search with the
data tables that are for handling the information and the needs. This is about working on the
ability to scale the database and then handle the ability to access, with updating and sharing
information for different user stations. The analysis and the reporting is about the query
languages that relies on the formula and then working on the database systems with the
interface that is for the third-party tools.

The disadvantage is about the relational database with focusing on the object-oriented
programming language like Java. The technical standards are set for the different
disadvantages where here are problems related to additional layers for the database of new
functions and then using the web services for handling the data for couple layers (Seo et al,
2017). The problems are also about the limitations in language and then recreating the logic
for finding solutions which leads to mismatching between the applications or the system of
database. There are retrofits or the debuts that have been found to be set with system debuts
and the money which is invested in the work.

No-SQL Database systems

The system of database is important for providing a better mechanism and help in handling
the data storage and then retrieving the data procedures as well. There are triggers which are
set and the focus is on the supporting of the SQL query languages with the same that is for
handling the scaling to the cluster approach. The structures of the data work on handling
compromise of consistency with the barriers that are found to be in greater adoption of the
NoSQL stores that tends to include the lower level of the languages for query. The instances
are generally related to the major lack in the interfaces with investments that are relevant for
the relational database. The concepts are for eventual consistency where the changes are
found to be propagated with the nodes that are accurate with the proper reads. (Ma et al.,
2015) The example of this is MongoDB.

Advantage & Disadvantage

Disadvantage: The less support and the problems related to the business intelligence and
analytics are set with longer problems related to the advancement of years. NoSQL database
alternatives are related to the problems of production stages with problems that includes
contrasts that tends to be open source. The database issues are related to the directing and
meeting the demands with problems related to insert-read-update-delete cycle with few
features of analysis and the query ad-hoc. The administration includes that there is an end
goal with offering a solution which does not require any administration (Liu et al., 2015).

Advantage: The factors which adapt to the enterprise related to the handling of scalability
with the elastic with RDBMS that is not easy to scale out the clusters of commodity where
there are transparent expansions which are for the new nodes. This is for the lower costs
commodity hardware where there is upward scalability that is set with replacing and handling
the database which are better fit. The big data applications are including the rates of
transactions where there is growth from recognition and there is a need to store the volume of
data with RDBMS to match the needs of growth. It is difficult to use one to manage the data
volumes, with handling no NoSQL database. The database administration is about the focus
on the services with designing, installing and maintenance of the system (Makris et al., 2016).
The database needs to handle the less hand-on management with distribution of data with
simplification of data models. The practices are related to the taking care of performance with
database availability. The economy with the installation for the expensive storage systems
and servers. It includes the installation of the cheap with hardware clusters that are for
handling increase of data volume. The processes are for storing more data at a less price.

Conclusion

NoSQL database works on handling the response with creating the scalability and providing
the superior performance. The larger volume is structured with unstructured data with agile
sprint with frequent code pushes. The efficiency is about scaling out architecture rather than
expansive and handling monolithic architecture. The companies choose MongoDB is about
the development of the modern applications with the offering of the lower cost ways to
develop and manage the implementation and working on software implementation. The
ability is about the requirements to map and then handle the operations with the key value,
graph and the wider column with documentation of models (Namdeo et al., 2018). The
consistency models offer the system. The relational models are determined with the
specifications of the order with the tuples that are for imposing no order on the attributes. The
applications are set for the access data with the specifying of queries and then using the
operations like the select of the identifying of tuples and then projecting to identify the
attributes. The relations are easily modified through using the insert, delete and the update of
the operators.
Recommendations

There is a need to focus on improving the system where there are increased requirements of
the PTV app on iPhone. For this, there is a need to properly work on handling the database
server options and then cater to the volume of data that one is dealing with currently along
with the improvement of the projected growth in the coming time. This could be through the
assessment which is done by the users in the indirect manner through the external
applications rather than the application programming which is for the storing within the
database itself or through the lowering down of the data level manipulation.

References

Fan, C., Bai, C., Zou, J., Zhang, X. and Rao, L., 2015. A dynamic password authentication

system based on nosql and rdbms combination. In LISS 2013 (pp. 811-819). Springer, Berlin,

Heidelberg.

Seo, J.Y., Lee, D.W. and Lee, H.M., 2017. Performance Comparison of CRUD Operations in

IoT based Big Data Computing. International Journal on Advanced Science, Engineering

and Information Technology, 7(5), pp.1765-1770.

Liu, Z.H. and Gawlick, D., 2015. Management of Flexible Schema Data in RDBMSs-

Opportunities and Limitations for NoSQL-. In CIDR.

Makris, A., Tserpes, K., Andronikou, V. and Anagnostopoulos, D., 2016. A classification of

NoSQL data stores based on key design characteristics. Procedia Computer Science, 97,

pp.94-103.

Namdeo, B., Nagar, N. and Shrivastava, V., 2018. Survey on RDBMS and NoSQL
Databases. International Journal of Innovative Knowledge Concepts, 6(6), pp.261-264.

Ma, K. and Yang, B., 2015, August. Access-aware in-memory data cache middleware for

relational databases. In High Performance Computing and Communications (HPCC), 2015

IEEE 7th International Symposium on Cyberspace Safety and Security (CSS), 2015 IEEE
12th International Conferen on Embedded Software and Systems (ICESS), 2015 IEEE 17th

International Conference on (pp. 1506-1511). IEEE.

Zafar, R., Yafi, E., Zuhairi, M.F. and Dao, H., 2016, May. Big Data: The NoSQL and

RDBMS review. In Information and Communication Technology (ICICTM), International

Conference on (pp. 120-126). IEEE.

Seda, P., Hosek, J., Masek, P. and Pokorny, J., 2018, April. Performance testing of NoSQL

and RDBMS for storing big data in e-applications. In 2018 3rd International Conference on

Intelligent Green Building and Smart Grid (IGBSG) (pp. 1-4). IEEE.