Database Fundamentals 1 Project: ER Model, Queries, and Security

This assignment focuses on database fundamentals, beginning with the development of an Entity-Relationship (ER) diagram for a business organization named 'Stepping into History.' The ER diagram serves as the foundation for designing a logical and relational database model. The project then progresses to the implementation of the database, followed by the creation of various SQL queries to extract specific information. The assignment also emphasizes database security, involving the creation of users with varying privileges to enhance data protection. Furthermore, the project culminates in the design of a web-based interface, enabling user interaction with the database through operations such as data insertion, updating, deletion, and querying. The solution demonstrates the application of Boyce-Codd Normal Form for database normalization, optimization techniques, and indexing strategies to improve database performance. The project encompasses the entire lifecycle of database design and implementation.

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