Database Fundamentals: ER Diagram, BCNF, SQL Queries, Optimization, Security, 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