Database Concepts with SQL

Database Concepts with SQL

Table of Contents
INTRODUCTION...........................................................................................................................3
PART A...........................................................................................................................................4
Part B.............................................................................................................................................12
PART C..........................................................................................................................................16
REFERENCES..............................................................................................................................19

INTRODUCTION
Database management system is a process which help for performing different functions
for securing data in proper manner. This report will implement relational database by using
SQlite software. This assignment will divide into three different parts such as design relational
database design, execute query in Sqlite software and discuss about the software. Database is
referred as a system which resembles data in tabular format. It may be described in various
different ways to establish relationship among different entities. DBMS (Database management
system) is basically used to store information in back-end and this can be done using User
interface which directly allow naive users to store data within a one click. SQL (Structured
Query Language) is a standard Application programming interface which is used to create delete
and update relational database system (Kim, Johnson and Pandis2016). This study will provide
several concepts about DBMS which can be implemented using queries from different APIs such
as oracle, Sqlite, Plsql (Procedure language structured query language) etc. The report will be
able to explain the approaches of maintaining the large database records of user. Furthermore,
this study will also able to build understanding about pros and cons of using Traditional
relational database system and no- sql database system. Present study will also cover traditional
RDBMS such as Oracle and sql server and it will also provide no-sql database such as Mongo
satabaes.

PART A
Design relational database
1). For each of these relations, write down all non-trivial functional dependencies. If there are no
functional dependencies among attributes, you must state so.
No- trivial Functional dependencies- non- trivial functional dependencies is represented the same value
and attribute in particular table and columns (Raouf, Badr and Tolba, 2017).
According to the entity relationship diagram and database system, there are important non-trivial
functional dependencies are represented in the relational database system.
For Example- Written by (Email, ISBN, Title,)
Book (ISBN, Title, Edition, Year, List Price, Publisher Nam)
 This database table will contain the title of book.
 They have same valued store in database tables.
 This relationship shows non- trivial functional dependencies but they don’t have subset of
another tables.
Non-trivial functional dependencies:
1. Author(id, email address, contact number, name)
They have some functional dependencies :
Functional dependencies 1 = Email address-> author_name, author_address
Functional dependencies 2= Email address->contact_no.
2. Book(title, subtitle, edition, ISBN, price, year, publisher)
They have some functional dependencies :
Functional dependencies 1= ISBN-> title
functional dependencies 2= ISBN-> contact number
3. Shopping cart(cardId, time stamp)
They have some functional dependencies :
functional dependencies 1= customer Email-> cartId
functional dependencies 2= customer Email->time stamp
4. Publisher(publisername, id email, contact number, URL, ABN)
They have some following functional dependencies:
functional dependencies 1= publisherfull name-> ABN, email address, ISBN)

5.Warehouse(code, Address)
They have some following functional dependencies:
functional dependencies 1= code->warehouse address
6. Customer(Name, Email address, shopping cart id)
They have following functional dependencies:
Functional dependencies: Email → customernamer, cartid, address
All the entities have attributes that show in the right side and it is not part of left hand such as
non trivial functional dependencies.
2). Write down the highest normal form each of these relations are in. For each of these relations,
state the reasons why it doesn’t meet the next normal form requirements. This is not required if
the relation is in 3NF
3NF (normal form)- 3rd Normal form is useful for normalizing the database table and it also
helps for reducing the duplication in database.
1. Publisher(publishername, email address, ABN, URL): This relationship can be show the
3rd normal form because they don't have primary key. It shows the transitive and partial
dependencies.

These are database tables which help for designing a 3rd normalization form. This normal form
will useful for reduce the data and create tables or columns. Book_isbn is representing functional
dependencies and data redundancy.
1NF (normal forms)- 1st normalization form is useful in database system. It will contain single
value in the tables.
For example-
2. Author(Email address, authorid, contact number, firstname, fullname): This relation is
representing the 1st normalization form and it contains multi values.

This table shows the first normalization form because all column contains single value and
unique identifier. This table show 1NF forms.
3. Book (ISBN, bookid, title, subtitle, editions, price, year, publishername):This relation
is represented 0NF because many publishers can publish different books and it contains
multi valued attributes in database relationship.
4. Customer(customerId, customername, customer address, cart ID) : These entities
show 3NF relations because they have partial and transitive dependencies in absence of
primary key.
5. Warehouse(code, Address): This is also 3NF relations because they determine the
partial dependencies in the attributes.
6. Shopping cart(Cart_id, time stamp, quality, isbn, price): This relation is 2NF because
they can represent the simple primary key. Partial dependencies occurs by using
Composite primary key.
3). Decompose the relational in 3NF
1. Author(AuthorId, name, email address, contact number)
According to this table, it can hold multiplier attribute with same values such as
Author(id, Email, address, name)
Author(id, email, address)
It can be decompose the author and get the functional dependencies such as
Functional dependencies: id-> email, name, address
Functional dependencies: id-> contact number
This relation is 1NF because there are null multi valued attributes. This relation is not support
the 2NF and 3NF.
2.Publisher(Publisherid, publishername, address, ABN, URL)
This relation contains primary key that support the partial and transitive dependencies,

This relation is 3 normal form.
3. Book(Title, ISBN, title, subtitle, price, Edition, year, publisher name)
This relation contains primary key that support to another entities such as book. Publisher
Publishes many book so that it contain multi valued attribute, it doesn't support 1NF relations.
Book(ISBN, subtitle, editions, year, price)
Book(ISBN, Publishername)
Now, this relation is 3NF.
4.Customer(customerid, email address, name, cartId)
This relation is 3NF because it can determine primary key and also representing the partial and
transitive dependencies.
5.Warehouse(code, address)
This relation is 3NF because there is some specific primary key available.
6. Written By(Email, ISBN, Title)
This relation contains specific primary key and it is partial dependence so that this relation is
3NF(Normal from).
7. Shopping cart(I'd, time stamp, quality, price, ISBN)
This relation representing the 1st normal form. They don't have any multi value. It also shows the
2 NF relation because primary key is correct and simple.
4).Final relation data schema
author(id, name, email, address, contact number)
author(id, address, contact number)
Publisher(publishername, address, ABN, URL, id)
written by(author name, address, title, ISBN)
book price(time stamp, quality)
Customer(id, email, number, address, email)
Inventory level (code, ISBN)
warehouse(Address, code)

SQL queries
Display first name and last name of author who wrote the book of database.
Display the first name, and last name of the translator.

A borrower wants to borrow the book titled "PRINCIPLES AND PRACTICE OF DATABASE
SYSTEMS
Using partial matching of the book title note that the borrower is interested in a "DATABASE"
book.

By searching of other books written by the same author of "PRINCIPLES AND PRACTICE OF
DATABASE SYSTEMS

Part B
Write down Sql queries
1). Display firstname, last-name of author who wrote the book such as “databases”
using sub query and join query
a). SELECT firstname, last name
FROM author
Where author.authorid IN
(SELECT written_by.AUTHORID FROM written_by WHERE written_by.BOOKDESCID IN
(SELECT book.BOOKDESCID FROM book WHERE book.subjectid IN (SELECT
subject.SUBJECTID FROM subject WHERE subject.SUBJECTTYPE = 'DataBases')
b). SELECT firstname,lastname FROM author INNER JOIN written_by ON author.AUTHORID
= written_by.AUTHORID INNER JOIN book ON book.BOOKDESCID =
written_by.BOOKDESCID INNER JOIN subject ON subject.SUBJECTID = book.SUBJECTID
WHERE subject.SUBJECTTYPE = "DataBases
2).Display firstname, last name or middle name of translator who translate the American
electrician handbook
Source Code:
SELECT middlename,firstname,lastname
FROM author
INNER JOIN written_by ON written_by.AUTHORID= author.AUTHORID INNER JOIN
book on book.BOOKDESCID = written_by.BOOKDESCID WHERE Book.TITLE =
"AMERICAN ELECTRICIAN'S HANDBOOK" AND written_by.ROLE = "Translator";
3).show book title that not borrowed
Source Code:
SELECT Book.TITLE FROM book LEFT JOIN borrow_copy ON book.BOOKDESCID =
borrow_copy.BOOKID WHERE borrow_copy.TRANSACTIONID IS NULL;

4). Borrow the book title of principle and practice of database
a). SELECT book.TITLE FROM book WHERE TITLE LIKE '%DATABASE%';
Written by author search the book
b). SELECT book.TITLE FROM book INNER JOIN written_by ON book.BOOKDESCID =
written_by.BOOKDESCID WHERE written_by.AUTHORID = (SELECT author.AUTHORID
FROM author INNER JOIN written_by ON written_by.AUTHORID = author.AUTHORID
INNER JOIN book ON book.BOOKDESCID = written_by.BOOKDESCID WHERE
book.TITLE = "PRINCIPLES AND PRACTICE OF DATABASE SYSTEMS");
5). Show publisher who publish the databases book.
SELECT publisher.PUBLISHERFULLNAME , book.TITLE
FROM publisher INNER JOIN published_by ON published_by.PUBLISHERID =
publisher.PUBLISHERID INNER JOIN book ON book.BOOKDESCID =
published_by.BOOKDESCID INNER JOIN subject ON book.SUBJECTID =
subject.SUBJECTID WHERE subject.SUBJECTTYPE = "DataBases";
6).show the title of book and can never borrowed by using outer join.
SELECT Book.TITLE FROM book LEFT JOIN borrow_copy ON book.BOOKDESCID =
borrow_copy.BOOKID WHERE borrow_copy.TRANSACTIONID IS NULL;
without using outer join:
SELECT Book.TITLE FROM book WHERE book.BOOKDESCID NOT IN (SELECT
borrow_copy.BOOKID FROM borrow_copy);
7. show full name of publisher with the author of Alfred AHO
SELECT publishername.publisher
FROM publisher WHERE EXISTS (SELECT published_by.PUBLISHERID FROM
published_by INNER JOIN written_by ON written_by.BOOKDESCID =
published_by.BOOKDESCID INNER JOIN author ON written_by.AUTHORID =
author.AUTHORID WHERE author.lastname = "AHO" AND author.FIRSTNAME =
"ALFRED");

8. Display last name and firstname of author who wrote more 3 book
SELECT
author.FIRSTNAME,author.LASTNAME
AS "number" FROM author INNER JOIN written_by ON written_by.AUTHORID =
author.AUTHORID GROUP BY author.AUTHORID HAVING count> 3;
9.display book title that is the most physical copies.
SELECT book.title
FROM book INNER JOIN borrow_copy ON book.BOOKDESCID = borrow_copy.BOOKID
INNER JOIN borrow ON borrow.TRANSACTIONID = borrow_copy.TRANSACTIONID
GROUP BY borrow_copy.BOOKID ORDER BY 2 desc limit 1;
10. Book burrowed in one transaction are to be returned together
This database represents the comprehensive library management system in which
librarian have to submit the books which are issued by a common transaction. So to provide such
changes in database schema, one more attribute is required to be recorded in book entity which
will work as a foreign key in this table.
Attribute name transaction id need to be included in book entity so that librarian can also
able to keep information about transaction occurred for a specific. In this way when, librarian
will be able to trace the quantity of books issued by student with a common transaction. Along
with the name of burrower of the book, total quantity of books can also be identified by adding a
attribute namely book id. This attribute which is present in burrow table which acts as primary
key in table can be used as foreign key in another table named as burrow.
11. Allowance of hold onto book features for customers
Library management are able to issue only those books which are available at the time
when person request for a specific book. A conditions might be arrise at the time of issuing a
book. First is if the librarian have the book then book will be assigned to the person who have
requested but if the book is not available then librarian might allow the customer to place holds
on to book.
There may be multiple customers of a one book so names of all those customers are
recorded in hold queue to read the book after one have resubmitted it (Groeneveld and
Lichtenberg, 2016). But here problem arises is that queue may contain multiple holders of same

book so there is requirement to provide that book to the one whose name is appeared at the front
of Hold queue. Therefore, it requires lot of changes in database such changes are as follows:
 An entity namely “hold queue” required to be added to database. This contains the names
of those persons which are applying for that book which is not available at the time of
their request for a particular book.
 There can be four attributes in this entity i.e, name of customer, Hold queue id, title of
book required, Book id.
 Book id and Book Title will act as a foreign key in “hold queue ” table which can be
arrived from another table named as book. These two attribute are considered as primary
key in book entity.
 Librarian can allow the book issue to that person whose hold queue id is “1”.
 Update status is another component which increase the transactions after every book
issuing activity occurred.
12. Loan extensions before due date
a.) Loan extensions are applied to library management for burrowing the books for a specific
time duration. So after adding one more entity of hold queue in this relational database, this
schema will allow librarian for loan extension. Loan extension can be referred as the process of
reserving the particular book for a customer if initially it is not available in library (Mühlbauer
and Neumann et.al.,2015).
“Hold queue” consists of lists of those people who have reserved the book which is
currently not available but applied for that already when it will be available in library
management system. This entity also contains such field like name of the customer and book
details. These attributes will also help in reserving a particular book for the one who have applies
for loan of the book before anyone does.
Hence, it will be possible in this given schema to extend a book loan to customer for
future reservations of specific book.
b.) in case if the customers are allowed to extend loan only two times then there is a need to
provide conditions after adding customer name and book details in hold queue entity which
requested by a customer “loan extension of customer”. Two cases will be arrived from this
scenerio.Check if the name of the customer is already mentioned in the list or not. If the name is

mentioned only one time then book will be issued else the loan extension re3quest will be
cancelled.
PART C
Traditional Relational Database System
A Relational Database System (RDBMS) is a database management system which is
based on relational concept which uses table to represent relationship among different entities
and their attributes. This system of management of data in form of table is invented by Edgar F.
Codd at IBM's San Jose Research Laboratory (Johnson and et.al., 2014). It can be considered as
most popular concept which organise and manage the data in the simplest manner. Queries are
fired to store, retrieve and update the data in RDBMS. It is a commonly used method for storing
information such as Financial, manufacturing, logistical and personal records.
This table contains one or more columns which can also be called as attributes. Here each
raw in a table can be represented as records or tuples which contains unique instance of data. To
establish a relationship between different tables primary key is assigned to a field which is a
combination of unique and not NULL values in a column. The primary key of one table can
work as a foreign key for another table and this is mainly used to build relationship between
database of different tables (Johnson, and et.al.,2014). There are multiple Application
programming interfaces are used to insert, update and manipulate data into tables. These APIs
are as follows:
Oracle: This is a database which is commonly used to provide functionality to online processing
transactions, data warehousing and mixed database workloads. It allows users to access the
objects of data directly (Jo and et.al.,2016). It is a Microsoft product which works effectively on
different OS platforms like UNIX, Linux,Mac etc.
SQL server: it is database management system for performing variety of transaction process and
analytics applications in the information technology environment. It is necessary part of database
management system for establishing connection between server and database.

Advantage of SQL server
 This server is useful for preventing two users for updating and record the information at
same time.
 It is support the set oriented SQL access and navigational access.
 SQL queries applicable to retrieve large records form database system. It is very efficient
and quick process for managing the entire operations and functions.
 SQL server is synonymous relational database and also having storage capabilities to
extend the relational database (Johnson, and et.al.,2014).
 Easy installation process
 security
 Improve the performance
 Powerful data restoration process or recovery mechanisms.
Disadvantage of SQL server
 Difficult for interfacing process because interface process is complex as compare to line
of code.
 Price
 usability
 Hardware
Advantage of MongoDB
 MongoDB is based on document database while collection hold the different document in
proper manner.
 MongoDB is less schema
 This is not complex join in MongoDB
Disadvantage of MongoDB
 Joins and data are not supported.
 It is not providing the rational solutions.
 It is based on memory hog.

CONCLUSION
As per discuss, this report is successfully designed an effective relational database
system. It also determines the advantages and disadvantages of SQL server and MongoDB.

REFERENCES
Books and journals:
Johnson, and et.al.,2014, February. From research to practice: experiences engineering a
production metadata database for a scale out file system. In FAST (pp. 191-198).
Jo, I., and et.al.,2016. YourSQL: a high-performance database system leveraging in-storage
computing. Proceedings of the VLDB Endowment, 9(12), pp.924-935.
Groeneveld, E. and Lichtenberg, H., 2016. TheSNPpit—a high performance database system for
managing large scale SNP data. PloS one, 11(10), p.e0164043.
Raouf, A.E.A., Badr, N.L. and Tolba, M.F., 2017. Distributed database system (DSS) design over
a cloud environment. In Multimedia Forensics and Security (pp. 97-116). Springer, Cham.
Kim, K., Wang, T., Johnson, R. and Pandis, I., 2016, June. Ermia: Fast memory-optimized
database system for heterogeneous workloads. In Proceedings of the 2016 International
Conference on Management of Data (pp. 1675-1687). ACM.
Mühlbauer and Neumann et.al.,2015, May. High-performance main-memory database systems
and modern virtualization: Friends or foes?. In Proceedings of the Fourth Workshop on
Data analytics in the Cloud (p. 4). ACM.