Database Fundamentals: ER Diagram, BCNF, SQL Queries, Optimization, Security, Web Interface

This assignment focuses on database fundamentals, including the development of an entity-relationship (ER) diagram, the implementation of Boyce-Codd Normal Form (BCNF), the creation of SQL queries, database optimization techniques, database security measures, and the design of a web interface.

Database Fundamentals
1

Contents
INTRODUCTION...........................................................................................................................3
TASK...............................................................................................................................................3
CONCLUSION..............................................................................................................................22
REFERENCES..............................................................................................................................23
2

INTRODUCTION
Database can be defined as a structured collection of data that can be stored and accessed
electronically from a computer. It is one of the most important things which is required to be
focused upon by organizations as it helps them to stored unlimited data structurally and category
wise for effective management and access (Jukic, Vrbsky and Nestorov, 2016). It further helps a
company in capturing important and relevant data so that it can be used in future for many
different purposes such as analysis of data for decision making, storing customer’s information,
analyzing employee’s data for development of training programs and workshops. In order to
develop a database, it is important to develop an entity- relationship diagram so that a logical
structure can be developed and relationship between entities and attributes can be identified. it
helps in identifying overall structure of database and helps in designing a database in a proper
manner. ER diagram further helps in development of relational model of database so that
database can be populated appropriately. this assignment will lay emphasis upon development of
ER diagram for case study of Stepping into History business organization. ER diagram will be
used for development of logical and relational diagram for designing of database. After
development of database different types of queries will be used for extracting required
information from database. After development of database, security features of database will be
enhanced by creating three different users with different privileges. Lastly a web-based interface
to that database will be designed.
TASK
Produce Entity relationship Model
An entity relationship diagram is based on the set of entities that stored in a database. An entity
in this context as object, component of data. It is a collection of similar entities that have defined
its properties (Boisvert, 2019). By defining the entities, attributes and also representing
relationship between them. ER Diagram illustrates with logical structure of database.
3

Figure 1 Entity Relationship Diagram
4

Entities Attributes
Speaker  Speaker_id (PK)
 Speaker_name
 Speaker_PhoneNo
 Speaker_email
Book  Book_id (PK)
 Book_name
 Book_author
 Book_type
 Workshop_id (FK)
 Speaker_id (FK)
Customer  Customer_id (PK)
 Customer_PhoneNo
 Customer_Name
 Customer_Email
 Book_id (FK)
Workshop  Workshop_id
 Workshop_name
 Workshop_type
 Customer_id
Lecture/visit  Date
 Location
 Time
 Speaker_id (FK)
Table: 1
5

From above table, it has identified the different entities, attributes that help for establishing a
relationship between one or more tables. Each and every table contain unique primary key,
which may represent as foreign key into another table. In this way, it plays important role in
database for developing a strong relationship between them.
Primary and Foreign key
 Primary Key: A primary key is also known as primary keywords, applying within
relational database. That’s why, it is representing as unique for each record, identifier.
Within relational database, it must always have one primary key into table for purpose to
link with another table (Guo and Gao, 2019). For Example- speaker id, book id,
customer id and workshop id. These are using different primary keys within database
tables.
 Foreign key: It is one of important key used to link between two or more tables together.
Sometimes, it is also known as referencing key. In foreign key, each columns are based
on the combination whose values match a primary in different tables. For Example- Book
id is behave as foreign key into customer table.
Relationship between speaker table and book table
Speaker_id is primary key that contain unique value in this column. Each and every data
should be considered as unique information. By using Speaker_id, It can be represented as
foreign key into book table. It will be creating a relationship between book and speaker tables.
Another way, it can be identified that one speaker will talk about single book and represent as
information detailed. For establishing a relationship between Book and Speaker. Primary key is
important part for representing a unique attributes in proper manner.
Relationship between customer and book table
In Book table, Book –id is primary key in which may establish a relation with customer
table. It means that represent as foreign key into customer table. This will help for identifying the
relationship through one column attributes into another table. After successfully established the
6

relationship between tables. Customer can easily use database to access information of each and
every book, identifying full detailed about particular books in proper manner.
Design a set of relations conforming to Boyce-Codd Normal Form (BCNF)
A relational scheme R is considered as Boyce-code Normal form, if everyone consist of
one dependency x->y one of this following conditions hold true:
X->y is a type of trivial functional dependency, Y is consider as subset of X
X is super key, Schema of R
Usually, Boyce-Code Normal form can be represented as each attribute that must represent a fact
about the different key, but nothing about key (Jukic, Vrbsky and Nestorov, 2016).
7

Book id -> Book author, Book type
Speaker id-> Book id
Speaker id-> Book id, Book type, book author
Implement final database design
Database tables
Books table
Customer table
Literur/visit
8

Speaker table
Workshop table
Database Query
Inner Join Query / two table
SELECT Book.Book_id, Book.book_name, Book.book_author, Book.Book_type
FROM Book INNER JOIN
customer ON Book.Book_id = customer.book_id
Output
Inner Join Query / three table
SELECT Book.Book_id, Book.book_name, Book.book_author,
customer.customer_name, customer.customer_PhoneNo, Workshop.workshop_name,
9

Workshop.workshop_type
FROM Book INNER JOIN
customer ON Book.Book_id = customer.book_id INNER JOIN
Workshop ON Book.workshop_id = Workshop.workshop_id AND
customer.customer_id = Workshop.customer_id
Output
Union
select speaker_id from Speaker
union all
select speaker_id from Speaker
Output
10

Ordering
Select *from customer
order by customer_PhoneNo;
Output
Grouping
Select count(book_id), Book_price
From Book
Group by book_price;
Output
Aggregate Functions (Min, Max, Avg, count, sum)
Count Function
select count (book_id)
from Book;
Output
11

Min Function
select min(book_price) As smallestprice
from Book;
Output
Max Function
select max(book_price) As largestprice
from Book;
Output
Average Function
Select AVG (book_price) as Book_price
From Book;
Output
Sum Function
select sum(book_price) as Book_price
from Book;
Output
12

Renaming column
alter table "customer"
alter column "customer_id" to "customer_no";
Sub queries
select book_id
from
Book.customer
where
customer_name="john";
Table Aliases
select id As book_id, Name As Book_name
from Book
where book_price is not null;
Optimize database
Apply Database Optimization techniques to find set of tables
There are different ways to Optimise database and find set of multiple tables.
 Proper indexing- index is a type of data structure that help to speed up the data retrieval
process. Unique index is kind of process that will create a separate columns with
overlapping each other.
 Retrieve the relevant data or information which require consider whole set of data, when
user want only a certain part of it. Sometimes, it is not required to large source of data
accessing and save themselves (Khan, Ahmad and Ahmed, 2019). A correlated the sub
query basically depends on it’s outer query and parents. This process can be done in step
by step. It means that decrease its overall speed of process. In some situation, it will use
Inner Join command instead of correlated sub query.
13

Implement a range of index
In SQL server, index is an on disk and memory structure that directly associated with
database table, view speed of retrieval of rows from different table (Lino, Rocha and Sizo, 2019
). An index is contained a suitable key which built from one or more column. Different keys are
stored in tree structure that enable SQL server to find rows associated with key values efficiently
and effectively.
14

Suite of queries that will invoke those indexes
Different queries that will involve different indexes within database system. it help to support in
performance improvement of different queries (Mukherjee, 2019). Also helping to maintain a
relationship between one tables to another.
Demonstrate some index suppression techniques
To ensure that data or information access can be as fast as possible. When using SQL
server like relational database system. it can easily utilize indexing to find data as quickly.
By applying the codes, it can utilise large amount of information and generate apprpriate indexes
within database tables.
Secure database
The database server should be secured or protected from other threats by firewall, which denies
access to large amount of traffic by default. It is only allowed through which come from
particular application, web server as per requirement to access sensitive information (Sandha and
et.al., 2019). For protecting the database, it will be created the different users on the basis
requirement. Each and every users have provided as limited authority but only handle the action
by Database administrator. Who have more power to change preference of each and every users
within SQL server database/
Create three new users on database
15

Allocate them different security privileges, roles
dbo user-1
16

Guest user
This type of user is only execute the different acivities.
17

Syst user-3
Sys user has a power for giving the privilges for other users. In this way, other user can easily
access data and perform different task in proper manner.
View over existing base tables
If a particular user want to check the existing tables database by using SQL server studio 2012
(Scherzinger, 2019). This platform or tool have been stored data, information in the form of
different tables. Each and every tables has established a relationship through foreign key.
18

Issues appropriate privileges so that at least on use can those views
Authorization include two different process that can utilise issues regarding privileges.
That’s why, it is important to use and views all necessary tasks.
 Permitting only certain users to process, access and alter data or information
(Scherzinger, 2019)
 Implementing varies limitations on user access, actions. Generally, particular limitation
placed on user so that they can apply to objects such as schemas, tables, resources and
time.
A privilege is a right way to execute particular type of SQL statement, access another user’s
object. There are different actions performed within database.
 Connect to the database
 Create multiple tables
 Selecting a row from another user’s table
 Execute another user’s and stored procedure.
At some point, Database Admin can provide grant privilege to users so that people can
accomplish tasks which required for particular job. It should grant a privilege only to user, who
become absolutely needs a privilege to perform task.
Significantly, the roles allows for user in easier so that they can handle the privilege
management, normally grant privilege to role and not to specific users. Sometimes, it can be
divided into two different ways:
 System privilege
 Scheme based object privilege
19

Demonstrate action each user cannot perform
There are some common actions of user cannot perform such as manipulating the database,
optimization of data within different tables (Scherzinger, 2019). It is only access, perform certain
specified task within limited manner.
Building a web interface
 Insert new rows of data: A web interface to database will be developed in which new row of
data will be added into database in existing database.
 Update existing rows of data: This web interact will be focusing upon updating existing data
within database.
20

 Delete existing rows of data: delete operation will be performed in which required data from
database tabled will be deleted.
 Query existing rows of data: a web database interface will be created in which a using an
SQL query data within the database will be updated.
21

CONCLUSION
In the above assignment, an ER diagram has been developed successfully. Developed ER
diagram helped in understanding relationship between all the attributes and entities used within
development of database. On the basis of developed ER diagram, Boyce-Codd Normal Form was
also developed for setting normalized relationship among all the entities and attributes. further
using BCNF form, relational model and SQL tables were developed including foreign and
primary keys. using relational design, final relationship model was developed using which final
database was developed. Different kinds of SQL queries were also developed using different
techniques. After development of database, it was optimized using appropriate optimization
techniques and applying different range of indexes. Optimized database was successfully secured
by creating three different users with separate privileges. in order to further enhance database
security, different kinds of suitable views of existing tables and lastly web-based interface to that
database was designed and developed.
22

REFERENCES
Book and Journals
Boisvert, C., 2019, January. Teaching relational database fundamentals: a lack-of-progress
report. In Proceedings of the 3rd Conference on Computing Education Practice (pp. 1-4).
Guo, T. and Gao, H., 2019. Table2answer: Read the database and answer without SQL. arXiv
preprint arXiv:1902.04260.
Jukic, N., Vrbsky, S. and Nestorov, S., 2016. Database systems: Introduction to databases and
data warehouses. Prospect press.
Khan, W., Ahmad, W. and Ahmed, E., 2019, March. SQL Database with physical database
tuning technique and NoSQL graph database comparisons. In 2019 IEEE 3rd Information
Technology, Networking, Electronic and Automation Control Conference (ITNEC) (pp.
110-116). IEEE.
Lino, A., Rocha and Sizo, A., 2019. Application of clustering-based decision tree approach in
SQL query error database. Future Generation Computer Systems. 93. pp.392-406.
Mukherjee, S., 2019. Popular SQL server database encryption choices. arXiv preprint
arXiv:1901.03179.
Sandha, S.S. and et.al., 2019. In-database distributed machine learning: demonstration using
Teradata SQL engine. Proceedings of the VLDB Endowment. 12(12). pp.1854-1857.
Scherzinger, S., 2019. Build your own SQL-on-Hadoop Query Engine: A Report on a Term
Project in a Master-level Database Course. ACM SIGMOD Record. 48(2). pp.33-38.
23