Database Concepts with SQL - Relational Database Report Analysis

This report delves into the core concepts of database management systems (DBMS), focusing on relational database design and SQL queries. It begins with an introduction to databases and DBMS, emphasizing the use of SQL for creating, deleting, and updating relational database systems. The report is divided into three parts: relational database design, SQL query execution using SQLite, and a discussion of the software. Part A covers non-trivial functional dependencies, the highest normal form of relations, and the decomposition of relations into 3NF. Part B provides SQL queries to retrieve and manipulate data, including subqueries, joins, and outer joins. Part C discusses database design concepts and covers practical SQL queries. The report also addresses topics such as the implementation of library management systems. The report aims to provide a comprehensive understanding of database concepts and practical SQL query techniques.

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;