Tables:
Complaint:
Contact:
Customers:
Department:
Employees:
Products:
Stores:
Data Population:
Complaint:
Contact:
Customers:
Department:
Products:
Stores:
Queries:
Query 1:
Query 2:
Query 3:
Query 4:
Query 5:
Query 6:
Query 7:
Query 8:
Query 9:
Query 10:
Database Design and Implementation
Stage 1
First task was to create an Entity Relational Model or ERD for Tom Wiley Department Store
ERD is a kind of structural diagram
ERD allows understanding how real life elements are stored in system
Attributes in ERD describes nature and facts about individual elements
ERD has relationship
Relationship has very significant in ER Model
Understanding ERD
Before completing an ERD it was essential to understand what is it
The reason behind its name is relation between entities of a database model is typically represented through it
In order to identify each row in an entity uniquely a special attribute is required
It is called primary key
Primary can be also combination of more than one entity
ER Model also has referential keys
These attributes are references of other table’s attributes
Chen’s Notation
The entities in the ER Model have been represented through rectangles
Each entity in the model has attributes that are visualized as the shape of ellipses
Primary key attributes has underline
The relation between the entities are shown using diamond. The name of the relationship defines what the purpose of it
Entities, Attributes and Relationships
The customers entity stores all the information about customers of organization
Customer data will be store irrespective of the store he/she purchased
The store contains information like name, location and contact information
Each store has a manager. Each manager has to be an employee
Product name, description and unit cost is stored
Product and Stores has many-to-many relationship
Stage 2
First activity of stage 2 is creating relational data structures
Each entity of ERD model has been analyzed to generate the database structure
The database structure also provides information about the dependency in the entity
The second task was to generate database schema
Schema for each database entity has been created
Normalization
The relational data structure has concentrated on showing normalization
Normalization is the process of identifying anomalies in the entity
These anomalies can cause insert, update or deletion issue
Normalization can be done up-to six stages
However, normalization Up-to 3NF is most relevant for most cases
Each level of normalization has its own rules
Normalization Up-to 3NF
NF –
When a relation contains multi-valued or composite attribute, it will violate the first normal form. But employee table does not contain any composite or multi-valued attribute
2 NF –
A relation must be in first 1nf and it must not contain partial dependency. Employee tables does not contain any partial dependency
3 NF –
There is no transitive dependency for non-prime attributes are present in the employee table thus, this table is in third normal form
Data Type and Keys
Data type is essential for physical implementation of database model
The INT represents integer value. The default range is 11
Varchar is variable character it can range from 1 to 255
Date stores the date value such as 04-01-2020. 04 is month that is April
Char is character
Decimal is float value. In order to represent cost decimal 12,2 can be be used. ,2 is more essential that 12 because it represents how many values can be after point.
Stage 3
MySQL is a relational database tool for storing and retrieving data.
MySQL is the most used structured query language tool
In order to make a database ready to use entities and relationships created in ERD must be implemented
The SQL for creating table is called DDL or Data Definition Language
The SQL for entering data or update structure is called DML or Data Modification Language
Creating Database
In order to create table of each entity in ERD separate DDL was created
Each create table query had a primary key and other attributes within it
The foreign key were created after all table was created
This was to make sure that no mismatch happen between attribute names
For composite primary key, separate alter table command was used
Inserting Data
Foe each entity ten rows were inserted
To make sure that database was created perfectly, foreign key check was not turned off
As store table needed employee data for manager
Employee table also needed store data to mention in which store it works
The manager column in store table was set as null
First all the employees and stores were created
Queries
Total ten queries were executed in stage 3
For each query alias has been used to represent table name
Screen shot for each query result has been provided
