Kid Toy

Table of Contents
Executive Summary.........................................................................................................................1
Introduction......................................................................................................................................1
Entity Relationship Diagram...........................................................................................................1
Relational Schema...........................................................................................................................3
Normalization..................................................................................................................................3
Conclusion.......................................................................................................................................5
References........................................................................................................................................6
Executive Summary
For Kids Creative, an online retailer, I was tasked with creating a database. Kids Creative sells
their items on the internet now, but they have no control over their personal data. This report will
contain a details description on the creation of database and the tables and attributes it need. It
will also contain relational schema to show the relationship among the tables, and lastly the
designed tables will be normalized to check for data redundancy.
Introduction
I was entrusted with developing a database for Kids Creative, an online retail company. Kids
Creative is currently selling their products on the internet, but they have no control over their
personal information. The business wishes to maintain track of its inventory, manufacturers,
distributors, customers, and sales in a database. A list of clients classified by suburbs, as well as
a list of inventories arranged by price or category, are included on the company's wish list. VIP
customers and large orders receive a special discount from the company.
Consequently, based on the company's criteria and need, I created a database that includes all
aspects of inventory management, manufacturer information, wholesaler information, customer
information, and sales information. For a corporation to remain competitive in the market, data
and information must be stored in a database. It facilitates in the collecting of year-end income
and expenses for the company. Drawing an entity relationship diagram was the first step in
creating this company's database. The normalizing technique was created after the logical
structure was designed. The technique was used in the database to improve performance while
reducing data redundancy.
Entity Relationship Diagram

ER Modeling is a type of conceptual modeling that shows how relationships are connected. It is
one of the oldest types of modeling. Relationships relate to the tables or entities in which data is
altered to acquire information. The ER model is used to build an ER diagram, which depicts a
conceptual database as seen by a user, and ERDs depict database theme components such as
 Organizations (Entity set or Corresponds to a table.)

 Characteristics (Characteristics of entities.)
 Connections (Association between entities.)
The logical structures of the database are depicted in the ER diagram. As a result, an ER diagram
is a data modeling technique that uses a graphical representation to express data. Creating an
ERD before establishing a database management system is considered best practice for
implementing a systematic database. The Entity Relationships Diagram is a visual representation
of the words used in entity relationship modeling. In ER Diagrams, rectangle are used to
represent entities, ovals to express attributes, and diamond forms to demonstrate connections. To
represent the elements of a data store, ERD diagrams are usually used in combination with a data
flow diagram. They are in use while constructing a relational database since they let us to see
how data is linked in a broad sense. The ER diagram of the given case study is given below:
All characteristics, entities, primary keys, and foreign keys are shown in the above ER diagram.
Relational Schema
The relational schema of a database system expresses the database's structure in a formal
language that the database management system understands (DBMS). A relational schema is a
set of linked multiple model and their associated constituents. A architecture is made up of all of
the tables, columns, indices, subdomains, role - based, saved components, and other pieces that a
user creates to fulfill the data needs of a company or group of services. The goal of the relational
model is to give a formal method for declaring information and questions: users define explicitly
what data the database stores and what knowledge they want from it, and database management
system takes care of creating data structures to store the data. It specifies the database's overall
description. The three forms of relational schema are as follows:
 External Schema: Exterior schema, also referred to as subschemas at the top level, are
related to different data views.
 Conceptual Schema: The conceptual schema defines all of the data's entities and
attributes.
 Internal Schema: In a database, the internal schema refers to the records, data, fields, and
indexes that are stored. There can only be one conceptual schema and one internal
schema in a database.
Relational Schema for the case study
The primary key in the toy information table is Toy Id, which is used to uniquely identify each
row. This characteristic will indicate the type of toy it includes. Toy ID is a unique identifier
shared by inventory and wholesaler information. Each table has its own primary key. Name and
ID are the distinguishing features of wholesaler information. The cutomer ID property is shared
by both the sales and customer data, and both have their own main keys.
Normalization
Normalization is a database design technique that starts with a look at feature connections. The
goal of normalization is to find the right set of relationships to fulfill an organization's data
requirements. As a result, normalization is described as the act of evaluating a relation schema
based on its foreign and main keys in order to achieve the desired attributes of reduced
duplication and addition, removal, and update anomalies. It's being used to wipe up a relational
table by eliminating data duplication and abnormalities from the database. Normalization reduces
duplication and complexity by evaluating new data types used in the table. Breaking down a
large database table into related intervals and connecting them with connections is a good idea.
But at the other hand, normalization is a crucial step. Normalization is frequently used to
describe two methods:
 Eliminating superfluous (ineffective) information.
 Assuring that data dependencies are properly stored.
The following are the most prevalent types of normalization:
 The First Normal Form;

 The Second Normal Form;
 The Third Normal
Each of the three normal forms requires the normalizing steps completed in the preceding normal
form. For example, before using the second normal form to normalize a database, it must first be
normalized using the first normal form.
 First Normal Form

In the first normal form, the data is stored in a table, and most or all tables are given a
primary key after they are formed. Reorganize all repeated categories into distinct tables.
Make that the key of the new table is identical to the key of the old table. The table
containing the repeating group must have a concatenated key to enable the data to be
rearranged in queries.
 Second Normal Form
It is in second normal form if a relation is in first normal form and each non-key
characteristic is completely functionally dependent on the primary key. When the
prerequisites for the first normal form are met, tables are said to be in the second normal
form. If the primary key is a composite of qualities, non-key features must rely on the
whole key.
 Third Normal Form
When the tables meet the parameters for second normal form, they are said to be in third
normal form. The value in the key of another column has no effect on the non-key
attributes in a row. If most of the features (e.g. database columns) are operationally
dependent on the primary key, a database relationship (e.g. database table) has been said
to satisfy third normal form criteria. This is a second normal form relationship, according
to Codd, where all non-prime characteristics are completely reliant on the primary keys
and therefore have no unidirectional dependence on another key.
Normalization for the case study
Except for a few tables, all of the tables have previously been normalized. 1NF contains
information about customers and wholesalers. One or more manufacturers and customers might
be included in the table book information and sales details. As a result, the data can be
normalized to a maximum of 3NF. The rest of the table is going to stay the same.
Conclusion
Kids Creative now sells their products online, but they have no control over their personal
information. The business wishes to maintain track of its inventory, manufacturers, distributors,
customers, and sales in a database. For a children's creative agency, I successfully designed a
database. To start, I developed an entity relationship diagram that matched the objectives of the
evaluation. In order to eliminate data redundancy as much as feasible, the normalizing procedure
was applied. The company will be able to maintain track of inventory, supplier data, customer
information, and sales statistics with the use of this database. They'll be able to make sense of
their inventory and arrange it based on pricing and other factors.

Kid Toy

Uploaded by

Copyright:

Available Formats

Kid Toy

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Kid Toy

Uploaded by

Copyright:

Available Formats

Table of Contents

Entity Relationship Diagram

 Organizations (Entity set or Corresponds to a table.)

Relational Schema for the case study

The following are the most prevalent types of normalization:

 The First Normal Form;

 First Normal Form

You might also like