Database Concepts Project 1: SQL, Relational Modeling and ERD

This project delves into fundamental database concepts, encompassing SQL, relational modeling, normalization, and entity-relationship diagrams (ERDs). The project begins with an introduction to SQL, detailing its use in querying and manipulating data within a database system, including examples of subqueries and data retrieval. It then explores relational modeling, covering functional dependencies, primary keys, and the application of inference rules. The project further examines normalization, explaining different normal forms (1NF, 2NF, and 3NF) and demonstrating the decomposition of relations to achieve these forms. ER modeling is addressed through the creation of an ER diagram for a construction company scenario, along with a detailed data dictionary. Finally, the project covers relational schema mapping, illustrating how data is organized within a database, and providing examples related to a supermarket database. The project aims to provide a comprehensive understanding of database design and implementation, with practical examples and explanations of key concepts.

Database Concepts
Project 1
1

Contents
INTRODUCTION...........................................................................................................................3
1. SQL..............................................................................................................................................3
2.Relational Modelling....................................................................................................................6
3. Normalisation..............................................................................................................................8
4. Entity Relationship Modelling...................................................................................................10
5. Relational Schema Mapping......................................................................................................13
CONCLUSION..............................................................................................................................15
REFERENCES..............................................................................................................................16
2

INTRODUCTION
A database can be organized in the collection of data or information, usually accessed and
stored the information electronically from computer system. It is more complex that they often
developed by using the modelling and design methods. In this report, it will use SQLite tool that
interact with the applications and end users to easily capture or analyse data effectively. It
encompasses the basic facilities which provided to the database administrator, who are having
power to maintain entire data in proper manner.
1. SQL
SQL stands for structure query language that support to store, manipulate and retrieving data
in the database system (Elmasri and Navathe, 2017). It is considered the standard language in
context of relational database management and also implement the specific actions such as
update, insert, modify, delete data.
1.1 By using in subquery to find title, give name and family name of all academic who work in
the departments located in Queensland.
SELECT Academic.deptnum, Academic.famname, Academic.givename,
Academic.title, department.state
FROM Academic INNER JOIN
department ON Academic.deptnum = department.deptnum
WHERE (department.state LIKE 'queensland');
1.2 Show the list of all title of field of interest to doctors that work in Queensland. Find output of
field name without any duplications.
SELECT Field.fieldnum, Field.title, interest.descrip, department.deptname,
department.state
FROM department INNER JOIN
Field ON department.fieldnum = Field.fieldnum INNER JOIN
interest ON Field.fieldnum = interest.fieldnum
WHERE (department.state LIKE 'Queensland');
1.3 Display the list of academic, who are collaborating with the another academic on more than
on paper.
SELECT Academic.acnum, count(*) AS Paper
FROM Paper INNER JOIN
Academic ON Paper.acnum = Academic.acnum
group by Academic.acnum
order by Academic.acnum;
3

1.4 List the number of each academic to meet specific conditions.
select Academic.acnum
from Academic
left join Author on Author.acnum = Academic.acnum
left join Papaer on Papaer.panum = Author.panum and Papaer.title like
'%database%'
left join interest on interest.acnum = Academic.acnum
left join field f on Field.fieldnum = interest.fieldnum and field.title like
'%database%'
group by Academic.acnum
having count (distinct Papaer.panum) > 12
or
sum (field.title is not null) > 0;
1.5 List the academic number of any academic that do not contain title or initials.
SELECT acnum
from Academic
where title= 'nnot' and initials= 'nnot';
1.6 Find the academics who are interested in the title of natural language processing.
SELECT Academic.acnum, Field.title
FROM Academic INNER JOIN
Field ON Academic.acnum = Field.acnum
WHERE (Field.title = 'NLP');
1.7 List the academic who have not interested other than natural language processing.
SELECT Academic.acnum, field.title
from Academic, Field
WHERE NOT EXISTS(
SELECT DISTINCT Academic.acnum
FROM Academic inner JOIN Field ON (Academic.acnum = Field.title))
4

1.8 Describe the purpose of following query.
The purpose of this query to represent the solution of Postcode of every department those
having less than 4999 and greater than 4000. It can be used the specific key words that help to
print the particular data which requires. It is combination of sub queries to use the select key
words. In this query, it can use different database tables such as paper, department, academic etc.
it helps to analyse the operations that applicable in sub queries to identify the results where
postcode exits in between 4000 to 49999.
1.9 Create view that display title, surname of every academic and shows papers.
Crete view v_Academic_paper
As SELECT Academic.acnum, count(*) AS Paper
FROM Paper INNER JOIN
Academic ON Paper.acnum = Academic.acnum
group by Academic.acnum
order by Academic.acnum;
where exists (select Academic.givenname, Academic.title,)
from academic left outer join paper on (Academic.acnum= paper.acnum));
5

2.Relational Modelling
A relational data modeling is a process that may involve to use the tables which are
connecting the group of elements (Hilles and Naser, 2017). It is based on the innovative idea that
create setup which may include the identifier or primary key. This process helps to identifies the
specific relation data that can link within results or outcomes.
As per relation: R (A, B, C, D, E, F, G)
It contains functional dependencies:
F -> D
G -> B
C -> D
F -> C, E
B -> F
A -> F, G
2.1 by using interference rule to identify minimal basis.
F -> D 1-argument
C -> D 1-argument
F -> C, E 2 -argument
B -> F 1-argument
A -> F, G 2 -argument
It applicable the interference rule to identifies minimal basis in subsets: G -> B
Because it contains only set of data in the sets.
2.2 identify the primary in the relation.
The primary in the relation is F because it exists in multiple data set. It also contains the
various relational in proper manner.
F -> D, F -> C, E, B -> F, A -> F, G, F -> D
In this way, it can be determined the primary in the relations that present in the data sets.
6

2.3 As per the use of key that determine if relation exits in BCNF and also discuss about FD and
keys.
Functional dependency is basically the criteria that can establish relationship between
two different attributes which are typically use primary with non-attribute within table. As per
relation, R attributes F functional dependent on the attributes of D. if it consists of valid instance
in F that uniquely determine D value of C.
In the relation, F is a primary key that are established the connection with other sets and it
can be modified in BCNF with the help of primary key.
2.4 Evaluate that relation R is not contain 3NF and then decompose into the relation of third
normal form.
F -> D G -> B C -> D F -> C, E B -> F A -> F, G
Let us assume that
 FC-> D
 A -> F, G
It can be identified that these are developed the collection of data element which are
automatically combined together to hold the data set in proper manner.
In this way, it can be analyzed that FC is a candidate key and there is no partial dependency
exists and doesn’t identify the any type of non- prime attribute (Gorskis, 2018).
On the other hand, by decompose R relation into third normal forms. For Example: -
 G -> B
When it constructs third normal form because G -> B is functional dependent so that G is a type
of super key and B is a prime attribute. In this way, it will generate third normal forms.
7

3. Normalisation
A normalization is a type of process that can take data from the problem and also reducing
the set of relation. It must ensure the data integrity and remove data redundancy in database
system (Neugebauer, 2016). Information should be stored once and avoid the data which are
already exits in the database. In case, if the redundancy present in the database which increases
the issues in the operational executions. It requires for eliminating the duplication and maintain
the lack of errors.
It can be considered different normal forms that indicate the redundancy in data. There are
various types of normal forms:
First Normal Form
It is a type of relation in the database system which create forms if only contain each
attribute which consists of atomic values. It must eliminate the repeating group in the
individual’s tables. It also creating the separate table for every data set of related information
(Gorskis, Aleksejeva and Poļaka, 2017).
Second Normal Form
It is mainly containing the first normal form and also follows the requirements: it does
not contain the prime attributes that functionally depended on the accurate subset of any type of
candidate key in relation. In this way, it shows the dependency on the candidate key.
Third Normal Form
It doesn’t consist of non-prime attribute which are absolutely transitively dependent on
the particular (Connolly and Begg, 2015). In this form, it eliminates the anomalies information
and also useful for making data more in effective manner.
3.1 identify the primary in sales relations.
In R relation, it can be determined the primary in the sales relations.
 Store address
 Grade
As per scenario, it can be determined that emp id, serial no is primary that establish a
relationship between other subset in sales relations.
8

3.2 Decompose the sales relation into third normal forms and represent the final schema with
primary or foreign key.
 Store_Address -> Store_Phone
 Emp_id -> Store _Address
 Emp_id -> Grade
 Grade -> Commission
These are combination of data set when they are connected to each other for developing
relational schema.
By converting into third normal which requires to identify the primary key that shows as foreign
key into another data set (Brookes, 2018).
Store_address is a primary key that establish a relation between store and employee
database tables. When it constructs third normal form because emp_id -> Store _Address is
functional dependent so that store_phone is a type of super key and store_address is a prime
attribute. In this way, it will generate third normal forms.
On the other hand, Emp_id -> Grade is also established the third normal form by using
prime attributes and primary key.
3.3 Evaluate the relations in the decomposition and also test the relation to show its third normal
form.
Store_Phone-> Store_Address
It consists of functional dependency:
Store_Address Store_Phone Emp_id Grade Commission
Table:1
Store _Address Store_Phone Grade Commission
Table:2
Store _Address Store_Phone Grade
Table:3
The relation is decomposing into the smaller forms which only consists to show the Store
Address, store_phoneno, Grade.
9

4. Entity Relationship Modelling
Entity relationship diagram is based on the structural representation that can use in the
design of database. It consists of various type of connectors and symbol that represent as a
visualise forms. The modelling technique is generating an abstract model which representing the
system data for designing relational Database. It become easy for designer to understand each
and every requirement of system in proper manner. This model can be designed before develop
database because it can pre analyzed the essential entities that must fulfill need and requirement
of system effectively.
Figure 1 ER Diagram
According to scenario of construction company for creating the entity relationship diagram
which fulfill all requirement where it can be performed in the business operations. It is based on
the company that will purchase products and hire the efficient candidates for managing the entire
functionality of business in proper manner.
The diagram consisting of multiple entities and attributes which represents as a brief about the
activities of system effectively.
10

Data dictionary
Entities Attributes
Equipment  Eq_id (PK)
 Name
 Type
 Fuel
 Capacity
 length
Employee  emp_id (PK)
 emp_name
 emp_phoneno
 emp_email
 emp_age
 cand_id (FK)
Registration  reg_id (PK)
 reg_date
 reg_time
Item_purchase  purchase_id
 purchase_date
 purchase_price
 purchase-quantity
 purchase_time
11

New_candidates  Can_id (PK)
 Can_name
 Can_email
 Can_age
 Can_phoneno
 Reg_id (FK)
Company  company_id (PK)
 company_name
 purchase_id (FK)
 project_code (FK)
 Eq_id (FK)
Table :1
12