We Have Numbers Of Free Samples

Table of Contents

• Introduction
• Explain the entity relationship diagram for Henry’s bookstore
• Database Table Creation
• Query design
• Report Design
• Conclusion
• Reference list

Introduction

Database design is generally conducted to store all the information and data in a good manner. It is because to store all the information in a sorted order database need to be designed. Apart from that, it can also be constructed that with the help of this kind of database system any organization can fetch the relevant and important information by implementing some specific query. All the table or relational table that is created in the database must need a unique key Id, otherwise, it is not possible to decompose relational schema in between two tables. More specifically it can be stated that it needs to understand the exact requirements about the data.  Apart from that in the process of database design, it has been observed that actually, the database design convert specifically the conceptual model in the form of the relational schema in a particular DBMS.

Explain the entity relationship diagram for Henry’s bookstore

ERD or Entity relationship diagram is a kind of graphical representation of a proposed information system that actually depicts a relationship in between all the relevant entities that are important for the proposed information system. ERD or Entity relationship diagram can also be considered as a data modeling technique that is generally used to depict any kind of business process that is used in the relational database (Hingorani,Gittens & Edwards, 2017). On the basis of the working process of the ERD, it can be stated that ERD is unable to design the unstructured and semi-structured data rather it is very helpful only for the pre-existing information system. Main three components that are important to design an ERD are an entity, attributes, and cardinality relationship. Attributes actually define the character of the entities and on the other hand, cardinality represents the mode of relationship in between two entities.

Generally, three types of cardinality relationship can possible to depicts and that are

• One to one relationship(1: 1)
• One to many relationships (1: M)
• Many to Many relationships (M: M)

On the basis of the below mentioned ED diagram, it is possible to define all the entities and respective attributes that related to the structure. According to this below-mentioned diagram, it can be stated that all the entity that is used in this diagram are a book, author, inventor, publisher, and author. The respective attributes that are used to design the ERD or entity relationship diagram are different. For Author (Author_Id, Author_Last Name, Author_First Name), Book (Book_Code, Title, Publisher_ Code, Type, Price, Paperback), Branch (Branch _Number, Brunch_ Name, Brunch_Location, Number of employee), Publisher (Publisher_Code, Publisher _Name, Publisher_Name), Inventory (Book_Code, Branch_ Number, On_ hand)

According to the graphical structure of the ERD, it can be represented that a proper cardinality relationship is maintained in this ERD representation.

 One to many relationship (1: M) relationship is maintained in between (books to brunch, books to the author, publisher to inventory, wrote to inventory).

Many to Many relationships (M: M) relationships are maintained in between (Books to inventory, a book to a publisher, Publisher to wrote). On the basis of above-mentioned ER diagram, it can be depicted that in which way the entire information system is running. This specific ER diagram explains the characteristic of every entity and at the same time, it also explains the relationship in between 2 or 3 tables.

Figure 1:  Entity relationship Diagram
(Source: Created by the researcher)

Functional Dependency

FD or Functional dependency is a kind of relationship that actually exists only when one attributes is totally dependent on another. On the other hand, it can also be suggested that FD or Functional dependencies are actually maintained between at least two attributes. The formation of FD or functional dependency will be constructed in a special manner and for this, it requires a condition. If A is has a relationship with B and C then the exact functional dependency can be represented in the form of B->C. On the basis of this, it is possible to explain that C is totally dependent on B.

On the basis of this database and relational table, it is possible to explain the FD’s o functional dependencies (Sukhwani et al. 2015). From the relational database, it can be constructed that Brunch and Inventory are functionality dependent on each other Brunch->Inventory. On the other hand, it can also be identified that Book and Inventory are also functionally dependent on each other. On the basis of that, it can also be constructed that Book->Inventory. Hence, the FD or Functional dependencies can be represented in the form of Book-> Brunch->Inventory. It is because all these three tables are dependent on each other.

Multi-valued Dependency with Respect to the ERD:

When there are more than one independent multi-valued attributes available in a table multivalued dependency occurs. In the above discussed ERD, there are six tables present that have their specific attributes in a table. For Example,

Publisher

P_Id	P_Name	City
P001	B.K	Kolkata
P002	Designer	Delhi
P003	Kapoor’s world	Mumbai

 

Here, column P name and city are independent of each other and dependent on P_Id. In this case, two columns are known as multi-valued dependent on P_Id. The MVD can be represented as:

P_id à P_name
P_id à City

Multi-valued dependency generally exists in 4NF in a table. It prevents normalization to fourth normal form. It is related to functional dependency also in relational database management.

Database Table Creation

Generally, the entire table that is composed of the database is totally dependent on the ER diagram. On the other hand, it can also be stated that all the relational table and entity are interdependent to each other. It is because after depicting the ER diagram it is possible to figure out all the entities in a proper manner. Later, all those entities are utilized to decompose the table for creating a particular database. According to the table structure or from the relational table it is possible to depict the unique key or primary key from any specific columns (Vitart et al. 2017). Every column in this table must contain a unique value so that it becomes possible to identify primary key which can be considered as a unique identifier. Apart from that, it can also be constructed that from this diagram it is possible to understand the working process of all the entities that are related to entity relationship diagram.

TableAuthor

AUTHOR_NUMBER	AUTHOR_LAST NAME	AUTHOR_FIRST NAME
1	Morrison	Toni
2	Solotaroff	Paul
3	Vintage	Vernor
4	Francis	Dick
5	Straub	Peter
6	King	Stephen
7	Pratt	Philip
8	Chase	Truddi
9	Collins	Bradley
10	Heller	Joseph
11	Wills	Gary
12	Hofstadter	Douglas R.
13	Lee	Harper
14	Ambrose	Stephen E.
15	Rowling	J.K.

 

Table Book

BOOK_CODE	TITLE	PUBLISHER_CODE	TYPE	PRICE	PAPERBACK
0180	A Deepness in the Sky	TB	SFI	7.19	TRUE
0189	Magic Terror	FA	HOR	7.99	TRUE
0200	The Stranger	VB	FIC	8	TRUE
0378	Venice	SS	ART	24.5	FALSE
0791	Second Wind	PU	MYS	24.95	FALSE
0808	The Edge	JP	MYS	6.99	TRUE
1351	Dreamcatcher: A Novel	SC	HOR	19.6	FALSE
1382	Treasure Chests	TA	ART	24.46	FALSE
1384	Beloved	PL	FIC	12.95	TRUE
2226	Harry Potter and the Prisoner of Azkaban	ST	SFI	13.96	FALSE

 

Table Publisher

PUBLISHER_CODE	PUBLISHER_NAME	CITY
AH	Arkham House	Sauk City WI
AP	Arcade Publishing	New York
BA	Basic Books	Boulder CO
BP	Berkley Publishing	Boston
BY	Back Bay Books	New York
CT	Course Technology	Boston
FA	Fawcett Books	New York
FS	Farrar Straus & Giroux	New York
HC	HarperCollins Publishers	New York
JP	Jove Publications	New York

 

Table Brunch

BRANCH_NUMBER	BRANCH_NAME	BRANCH_LOCATION	NUM_EMPLOYEES
1	Henry Downtown	16 Riverview	10
2	Henry On The Hill	1289 Bedford	6
3	Henry Brentwood	Brentwood Mall	15
4	Henry Eastshore	Eastshore Mall	9

 

Table Wrote

BOOK_CODE	AUTHOR_NUM	SEQUENCE
0180	3	1
0189	5	1
0200	18	1
0378	11	1
0791	11	1
0808	11	1
1351	4	1
1382	6	2
1382	23	1
1384	25	1

 

Table Inventory

BOOK_CODE	BRANCH_NUM	ON_HAND
0180	1	2
0189	22	22
0200	122	1
0200	2	3
0378	3	2
0791	2	1
0791	3	2
0791	4	3
0808	2	1
1351	2	4

Relational Schema

In order to configure the database relational schema plays a most important role. The word schema refers or can be identified as a blueprint of the database process. With the help of this relational schema it is possible to construct the database and at the same time, this process imposes integrity constraints in the database.

In order to configure the database for Henry’s Bookstore this kind relational schema is quite important because it is processes that not only impose the integrity constraints rather it helps to modify the entire database table and also support a different kind of functionality. Mainly there are two steps that are used to design the relational schema and that are logical schema and physical schema (Lesage & Samson, 2016). Purpose of these two schemes is different but both are related to the database. Usually, the logical schema describes the structure of the database and displays the relationship in between all the tables in a particular database. On the other hand, physical schema is used to construct the table and column and at the same time is used to make a relationship specifically RDBMS or relational database management software.

Query Design

It is most important because it is generally used to sort the database and also to filter the database. If the particular server is in the database then it requires support view because with the help of view it is possible to filter the records on a particular server in order to speed up specifically the display time. In order to create a query design, it is important to maintain all the important steps so that the output of the query can generate properly (Batini, Ceri & Navathe, 2014).  Generally, the query can be constructed in this format so that after inputting a specific code it is possible to get a different output. On the other hand, it can also be suggested that with the help of select and update query it is possible to get appropriate output. In case of henry’s database, it can be suggested that to get an appropriate result it requires an effective query design. For that some of the tables are required and that are BOOK, BRUNCH, INVENTORY.

Book

BOOK_CODE	TITLE	PUBLISHER_CODE	TYPE	PRICE	PAPERBACK
0180	A Deepness in the Sky	TB	SFI	7.19	TRUE
0189	Magic Terror	FA	HOR	7.99	TRUE
0200	The Stranger	VB	FIC	8	TRUE
0378	Venice	SS	ART	24.5	FALSE
0791	Second Wind	PU	MYS	24.95	FALSE
0808	The Edge	JP	MYS	6.99	TRUE
1351	Dreamcatcher: A Novel	SC	HOR	19.6	FALSE
1382	Treasure Chests	TA	ART	24.46	FALSE
1384	Beloved	PL	FIC	12.95	TRUE
2226	Harry Potter and the Prisoner of Azkaban	ST	SFI	13.96	FALSE

 

Brunch

BRANCH_NUMBER	BRANCH_NAME	BRANCH_LOCATION	NUM_EMPLOYEES
1	Henry Downtown	16 Riverview	10
2	Henry On The Hill	1289 Bedford	6
3	Henry Brentwood	Brentwood Mall	15
4	Henry Eastshore	Eastshore Mall	9

 

Inventory

BOOK_CODE	BRANCH_NUMBER	ON_HAND
0180	1	2
0189	22	22
0200	122	1
0200	2	3
0378	3	2
0791	2	1
0791	3	2
0791	4	3
0808	2	1
1351	2	4

The syntax that is implemented to extract the appropriate output for designing the query is

Syntax 

SELECT book.TITLE, book.BOOK_CODE, Inventory.ON_HAND, Brunch.BRANCH_NUMBER
FROM (book INNER JOIN Inventory ON book.BOOK_CODE = Inventory.BOOK_CODE) INNER JOIN Brunch ON Inventory.BRANCH_NUM = Brunch.BRANCH_NUMBER;

Output  

TITLE	BOOK_CODE	ON_HAND	BRUNCH_NUMBER
A Deepness in the Sky	180	2	1
The Stranger	200	3	2
Second Wind	791	1	2
The Edge	808	1	2
Dreamcatcher: A Novel	1351	4	2
Venice	378	2	3
Second Wind	791	2	3
Second Wind	791	3	4

 

From the above-mentioned output table, it is possible to understand in which way the particular query is formatted in order to get a particular result. From the syntax of the query, it can be suggested that the above-mentioned output is possible to figure out the authenticity of the query

Report Design

Reporting is extremely important and necessary for any organization and actually is a process by which it is possible to express the output of the application. In most of the cases, it can be seen that the stakeholders of a particular project will define some of the requirements and all these requirements are implemented in the form of operational functionality. In most of the cases, it has been observed that maintenance of all the information, definition all this are implemented in the form of a report. It is true that the report very often actually requires a huge range of data, therefore, it can be suggested that normalized database and form of normalized table is extremely difficult in the report form (Chen, 2015).

In case of Henry’s database model, it can be stated that here report designing will play an important and crucial role.  With the help of this kind of report design it possible to identify all the important applications that are related to the database. In this part, this kind of report design is conducted in an effective manner.

Figure 2:  Report Design
(Source: Created by the researcher)

Conclusion

From the above discussion part, it can be deduced that it is essential to create a particular database to store all the relevant information in a proper manner. For that database creation is essential and to support the database appropriate relational table and SQL query design is also a most important element. In this part, it can also be evaluated that without proper query design it is not possible to collect any particular data from the database.

[Download not found]

Download