Streamlining Data: A Guide to the Normalization Process

A database in any organization operating with multiple activities and products plays a critical role in the systematizing of data and simplifies the challenges of keeping track of goods as they are added and removed from the store. However, the design involves a crucial analysis of all the possible entities which hold data to ensure it is saved and organized correctly and establish the relationships to help in arranging variables related to different tables through. A database management system helps in accessing, managing, adding, and deleting data and provides a well-built user interface. Its constraints are established to ensure that there are no conflicts between multiple entities in the database and restrict issues such as deletion of some crucial information for normal organizational operations. This paper presents a design of a system for Military Madness Company which will be used for various products sold by the firm as well as records of clients including dealers and private collectors.

Entity Relationship Diagram

An entity relationship and a logical diagram helps in the initial modelling of database system before it is designed and developed. According to Al-Btoush, it is the first step to the creation of a storage (2015). ERD is a vital aspect, especially for analysts, users, designers, software engineers, and project managers.

The assumptions about data and the design process are the following:

• A dealer can own more than one pitch, but more than one dealer cannot own one pitch.

• More than one owner can have a site for holding venues such as castles, forts, disused airfields, and gun ranges.

• Military Madness company transactions cannot be done by more than one dealer.

• A dealer can decide to choose only all on-site services.

• The size of each pitch holding the event can only be determined by the firm, and making payments depends on their evaluation.

• All militaria products are labelled with specific IDs or unique numbers.

List of Entities, Their Attributes, and Design Constraints

Customers

Dealer_ID (Integer, Foreign Key)

Pc_ID (Integer, Foreign Key)

Customer_category (String)

Customer_name (String)

Customer_email (String)

Dealers

Dealer_ID (Integer, Primary Key)

D_name (String)

D_locality (String)

Address (Varchar)

Email (String)

Num_of_pitches (integer)

Flat_fee (Double)

Private collectors

Pc_ID (Integer, Primary Key)

Pc_fname (String)

Pc_lname (String)

Address (String)

Email (String)

Ticket (String)

Site ownership

Owner_ID (Integer, Primary Key [Not Null])

Owner_fname (String)

Owner_lname (String)

Militaria table (products)

Product_ID (Integer, Primary Key Unique, not null)

Product_Name (String)

Number_of_prod_remaining (Integer)

Product_specifications (String)

Product_status (Boolean)

Sites/venue of display

Site_ID (Integer, Primary Key, not null)

Owner_ID (Integer, Foreign Key)

Site_purpose (String)

Site_size (Varchar)

Pitches

Pitch_ID (Integer, Primary Key, not null)

Pitch_name (String)

Pitch_size (String)

Pitch_description (String)

Selling_status (Boolean)

On-site services

Service_ID (Integer, Primary Key, not null)

Pc_ID (Integer, Foreign Key)

Service_description (String)

Numb_of_dealers (Integer)

Figure 1:

Entity relationship diagram.

Logical Model Design

The initial design of an ERD presents all entities with attributes in a complex format. However, the logical model provides a well-structured, normalized database structure which will be transformed to a storage system. Concerning the relational design, normalization is done to remove anomalies and the iteration of data entry (Koloniari, 2017). Moreover, it is essential as it minimizes duplicates and makes the database easy and safe to handle by restructuring the tables into less redundant and small ones (Namrta, 2017). The logical model classifies the relationships between different tables in either 1 to 1, 1 to many, or many to many. However, the latter variant has been simplified into a 1 to many relationships. The arrows on the logical diagram indicate the relations with the start and end points.

Figure 2: Logical design diagram.

Normalization Process

1NF (1st normal form):

Each field in a table should have single values

Each field contains values of the same data type

Set each table to have a unique identifier

The customers_table contains duplicate information hence table dropped.

Dealers_table – has a primary key hence 3NF

Pc_collector_table – primary key pc_ID uniquely identifies each value in the table

Militaria_table – added product_id to determine each product separately

Site_venue_table – added site_ID as primary unique key

Pitch_table – containing primary key, p_ID

On_site_service_table – containing service_ID as a unique identity

Site_owner_table – owner_ID for as the unique identifier.

2NF (2nd normal form):

Normalize all attributes which are not keys.

Dealers_table – added d_locality to determine their location

Pc_collector_table – already in 3NF

Militaria_table – has d_ID and pc_ID as foreign keys to differentiate products between the suppliers and buyers

Site_venue_table – added owner_ID (foreign key) to specifically determine the owner of the venue. Also includes pc_ID from the pc_collector_table to determine the available private collectors

Pitch_table – added foreign key (dealer_ID) to connect with specific dealers owning the pitch

On_site_service_table – contains foreign key (dealer_ID) to differentiate dealers in need and not in need of on-site services

Site_owner_table – already in 3NF

3NF (3rd normal form)

This normalization level ensures that all fields in the table are associated by one key and that there are no transitive functional dependencies. All tables are in 3NF except for the Militaria_table. Addition of variable product_desc normalizes the Militaria_table table into 3NF.

Question 3

Microsoft SQL server is used in implementing SQL tables. Seven of them are created for the database according to the mapping of the normalized logical model, attributes, and constraints per each field. The following CREATE TABLE statements were executed.

Dealers_Table

CREATE TABLE [dbo].[dealers_table](

            [dealer_id] [int] NOT NULL UNIQUE,

            [dealer_name] [nchar](50) NULL,

            [dealer_email] [nchar](50) NULL,

            [dealer_locality] [nchar](50) NULL,

            [email] [nchar](50) NULL,

            [address] [nchar](50) NULL,

            [flat_fee] [float] NULL,

 CONSTRAINT [PK_dealers_table] PRIMARY KEY

(

            [dealer_id] ASC

))

GO

Militaria_Table

CREATE TABLE [dbo].[militaria_table](

            [prod_id] [int] NOT NULL,

            [dealer_id] [int] NULL,

            [pc_id] [int] NULL,

            [prod_name] [nchar](100) NULL,

            [prod_desc] [nchar](100) NULL,

            [num_of_prod] [int] NULL,

            [prod_status] [int]NULL,

 CONSTRAINT [PK_militaria_table] PRIMARY KEY CLUSTERED

(

            [prod_id] ASC

))

GO

Site_Venue_Table

CREATE TABLE [dbo].[site_venue_table](

            [site_id] [int] NOT NULL,

            [owner_id] [int] NULL,

            [pc_id] [int] NULL,

            [site_purpose] [nchar](10) NULL,

            [site_size] [nchar](10) NULL,

 CONSTRAINT [PK_site_venue_table] PRIMARY KEY CLUSTERED

(

            [site_id] ASC

))

GO

Pc_Collector_Table

CREATE TABLE [dbo].[pc_collector_table](

            [pc_id] [int] NOT NULL,

            [pc_fname] [nchar](100) NULL,

            [pc_lname] [nchar](100) NULL,

            [pc_email] [nchar](100) NULL,

            [pc_address] [nchar](100) NULL,

            [ticket] [nchar](20) NULL,

 CONSTRAINT [PK_pc_collector_table] PRIMARY KEY

(

            [pc_id] ASC

))

GO

Pitches_Table

CREATE TABLE [dbo].[pitches_table](

            [p_id] [int] NOT NULL,

            [dealer_id] [int] NULL,

            [p_name] [nchar](100) NULL,

            [p_size] [nchar](100) NULL,

            [p_desc] [nchar](100) NULL,

            [price] [int] NULL,

 CONSTRAINT [PK_pitches_table] PRIMARY KEY

(

            [p_id] ASC

))

GO

On_Site_Service_Table

CREATE TABLE [dbo].[on_site_service_table](

            [service_id] [int] NOT NULL UNIQUE,

            [dealer_id] [int] NULL,

            [service_desc] [nchar](100) NULL,

 CONSTRAINT [PK_on_site_service_table] PRIMARY KEY

(

            [service_id] ASC

))

GO

Siter_Owner_Table

CREATE TABLE [dbo].[site_owner_table](

            [owner_id] [int] NOT NULL UNIQUE,

            [owner_name] [nchar](50) NULL,

            [owner_email] [nchar](10) NULL,

 CONSTRAINT [PK_site_owner_table] PRIMARY KEY

(

            [owner_id] ASC

))

GO

Question 4

This task involved entering data to tables created in the storage. Working code for dealer_table insertion queries:

USE [Militay_database]

GO

INSERT INTO [dbo].[dealers_table]

           ([dealer_id],[dealer_name],[dealer_email],[dealer_locality],[address],[flat_fee])

     VALUES

           (2,'Jackson','[email protected]','foreign','18  Caxton Place',450.00),

                           (3,'Winnie','[email protected]','foreign','38  Park Terrace',450.00),

                           (4,'Cirilo','[email protected]','Domestic','38  Park Terrace',450.00),

                           (5,'Bucky Roberts','[email protected]','foreign','38  Park Terrace',500.00),

                           (6,'Jane','[email protected]','foreign','38  Park Terrace',200.00),

                           (7,'Norah','[email protected]','Domestic','18  Caxton Place',500.00),

                           (8,'Williamson','[email protected]','Domestic','18  Caxton Place',80.00),

                           (9,'Michael','[email protected]','Foreign','18  Caxton Place',250.00),

                           (10,'Albert','[email protected]','Domestic','156  Park Terrace',500.00);

GO

Output Screenshot

Insertion Queries for Militaria_Table

USE [Militay_database]

GO

INSERT INTO [dbo].[militaria_table]

           ([prod_id],[dealer_id],[pc_id],[prod_name],[prod_desc],[num_of_prod],[prod_status])

     VALUES

           (1,1,2,'swords','all swords available',150,1),

                           (2,2,6,'uniforms','Full neck, short sleeved',500,1),

                           (3,1,1,'daggers','daggers available',150,1),

                           (4,3,3,'deactivated guns','contact us for the best',1000,1),

                           (5,4,4,'holsters','find every holsters',800,1),

                           (6,5,5,'inert granades','best granades',5,0),

                           (7,7,7,'bombs','well invented bombs',5000,1),

                           (8,8,9,'riffles guns','AK47, short guns',150,1);

GO

Screenshot of Table Contents

Pc_Collector_Table

Working Code

INSERT INTO [dbo].[pc_collector_table]

           ([pc_id],[pc_fname],[pc_lname],[pc_email],[pc_address],[ticket])

     VALUES

           (1,'EA_username','EA_username_last','[email protected]','150  Park Terrace','XVD258XYS'),

                           (2,'EA_username1','EA_username_last1','[email protected]','148  Park Terrace','X89258XYu'),

                           (3,'EA_username2','EA_username_last2','[email protected]','150  Park Terrace','hghvXhghjYS'),

                           (4,'EA_username3','EA_username_last3','[email protected]','0032  Paris','THE258XYS'),

                           (5,'EA_username4','EA_username_last4','[email protected]','0524  Park Terrace','XVDGHJGGH'),

                           (6,'EA_username5','EA_username_last5','[email protected]','820  KING Han','WVJA858S85'),

                           (7,'EA_username6','EA_username_last6','[email protected]','821  Park Terrace','85922XYS'),

                           (8,'EA_username7','EA_username_last7','[email protected]','150  Kens Down','X7554545aA'),

                           (9,'EA_username8','EA_username_last8','[email protected]','38  Park Terrace','AHOSLYS'),

                           (10,'EA_username9','EA_username_last9','[email protected]','865  Park Terrace','862RHJSOI');

GO

On_Site_Service_Table

Working code

INSERT INTO [dbo].[on_site_service_table]

           ([service_id],[dealer_id],[service_desc])

     VALUES

           (1,1,'catering'),

                           (2,2,'toilets'),

                           (3,3,'catering'),

                           (4,4,'insurance'),

                           (5,5,'card payment services'),

                           (6,6,'car park management'),

                           (7,7,'security services'),

                           (8,8,'EA user catering'),

                           (9,9,'EA insurance'),

                           (10,10,'EA user card payment');

GO

Pitches_Table

Working code

USE [Militay_database]

GO

INSERT INTO [dbo].[pitches_table]

           ([p_id],[dealer_id],[p_name],[p_size],[p_desc],[price])

     VALUES

           (1,1,'Santiago','400 M','400 by 400 m - medium',500),

                           (2,2,'Sant EA','200 M','200 by 200 m - small',250),

                           (3,3,'San Jose','1000 M','1000 by 1000 m - large',1000),

                           (4,4,'Zagreb','400 M','400 by 400 m - medium',500),

                           (5,5,'Paris','250 M','250 by 250 m - small',250),

                           (6,6,'Canda','1000 M','1000 by 1000 m - large',1000),

                           (7,7,'Manchester','500 M','500 by 500 m - medium',500),

                           (8,8,'City Stadium','1000 M','1000 by 1000 m - large',1000),

                           (9,9,'Tiger Twerrer','250 M','250 by 250 m - small',250),

                           (10,10,'State Pitch','1000 M','100 by 1000 m - large',1000);

GO

Site_Owner_Table

Working code

USE [Militay_database]

GO

INSERT INTO [dbo].[site_owner_table]

           ([owner_id],[owner_name],[owner_email])

     VALUES

           (1,'EA User','[email protected]'),

                           (2,'Josoehs','[email protected]'),

                           (3,'William','[email protected]'),

                           (4,'Prof','[email protected]'),

                           (5,'josephene','[email protected]'),

                           (6,'kim','[email protected]'),

                           (7,'woods john','[email protected]'),

                           (8,'mercy','[email protected]'),

                           (9,'rankle','[email protected]'),

                           (10,'hencky','[email protected]');

GO

Site_Venue_Table

Working Code

USE [Militay_database]

GO

INSERT INTO [dbo].[site_venue_table]

           ([site_id],[owner_id],[pc_id],[site_purpose],[site_size])

     VALUES

           (1,2,1,'Product testing','400 x 400'),

                           (2,1,1,'Product launching','400 x 400'),

                           (3,3,3,'Product display','400 x 400'),

                           (4,2,2,'Advertisemnt','400 x 400'),

                           (5,4,4,'car parking','400 x 400'),

                           (6,5,5,'Security','250 x 250'),

                           (7,6,7,'Catering site service','400 x 400'),

                           (8,7,6,'Marketing and display','400 x 400'),

                           (9,9,9,'Toiletry service','500 x 500'),

                           (10,10,8,'Product repair and testing','400 x 400');

GO

Question 5

SQL queries to test the usefulness of the database:

i. Query selects all dealers from the dealers_table where the leaser locality is foreign

USE [Militay_database]

GO

SELECT [dealer_id],[dealer_name],[dealer_email],[dealer_locality],[address],[flat_fee]

  FROM [dbo].[dealers_table]

  WHERE [dealer_locality] = 'foreign';

GO

ii. It selects all product information from the militaria_table where the dealer_id is 1.

USE [Militay_database]

GO

SELECT [prod_id],[dealer_id],[pc_id],[prod_name],[prod_desc],[num_of_prod]

  FROM [dbo].[militaria_table]

  WHERE [dealer_id] = 1;

GO

iii. It selects all pitch and dealer information on the name and locality where there is similar dealer_id in the pitches_table and dealers_table

USE [Militay_database]

GO

SELECT [p_id],[p_name],[p_size],[p_desc],[price],[dealer_name],[dealer_locality]

  FROM [dbo].[pitches_table] p

  INNER JOIN [dbo].[dealers_table] d ON p.[dealer_id] = d.[dealer_id];

GO

iv. Joins three tables

USE [Militay_database]

GO

SELECT [p_id],[p_name],[p_size],[p_desc],[price],[dealer_name],[dealer_locality]

  FROM [dbo].[pitches_table] p

  INNER JOIN [dbo].[dealers_table] d ON p.[dealer_id] = d.[dealer_id]

  INNER JOIN (SELECT [pc_id]

            FROM [dbo].[pc_collector_table]

            GROUP BY [pc_id]

  ) pc

  ON d.[dealer_id] = pc.[pc_id];

GO

v. Selects private collector with id = 5

USE [Militay_database]

GO

SELECT [pc_id]

      ,[pc_fname]

      ,[pc_lname]

      ,[pc_email]

      ,[pc_address]

      ,[ticket]

  FROM [dbo].[pc_collector_table]

  WHERE [pc_id] = 5;

GO

vi. Selects all information from the militaria_table order them by dealer_id

USE [Militay_database]

GO

SELECT [prod_id]

      ,[dealer_id]

      ,[pc_id]

      ,[prod_name]

      ,[prod_desc]

      ,[num_of_prod]

      ,[prod_status]

  FROM [dbo].[militaria_table]

  WHERE [dealer_id] = 1

  ORDER BY [prod_id];

GO

Selects all information from site venue with owner_id = 2

USE [Militay_database]

GO

SELECT [site_id]

      ,[owner_id]

      ,[pc_id]

      ,[site_purpose]

      ,[site_size]

  FROM [dbo].[site_venue_table]

  where [owner_id] = 2;

GO

vii. Selects pitch information from pitches_id where the size of the each is 1000 M and order them by dealer_id

USE [Militay_database]

GO

SELECT [p_id]

      ,[dealer_id]

      ,[p_name]

      ,[p_size]

      ,[p_desc]

      ,[price]

  FROM [dbo].[pitches_table]

  where [p_size] = '1000 M'

  order by [dealer_id]

GO

viii. Selects all information from from site_venue_table left, joins them with owner_id

USE [Militay_database]

GO

SELECT [site_id]

      ,[pc_id]

      ,[site_purpose]

      ,[site_size]

  FROM [dbo].[site_venue_table] s

  left JOIN [dbo].[site_owner_table] so ON s.[owner_id] = so.[owner_id];

GO

ix. Selects maximum price from the pitches_table

USE [Militay_database]

GO

SELECT MAX([price])

  FROM [dbo].[pitches_table];

GO

x. Selects sum of all products in store ranging from 150 to 1000

USE [Militay_database]

GO

SELECT SUM([num_of_prod])

  FROM [dbo].[militaria_table]

  where [num_of_prod] BETWEEN 150 AND 1000;

GO

Selects min price for each pitch

USE [Militay_database]

GO

SELECT MIN([price])

  FROM [dbo].[pitches_table];

GO

xi. Selects average number of products in store

USE [Militay_database]

GO

SELECT AVG([num_of_prod])

  FROM [dbo].[militaria_table]

  where [num_of_prod] BETWEEN 5 AND 1000;

GO

xii. Selects number of dealers from the militaria_table using Count

USE [Militay_database]

GO

SELECT COUNT([dealer_id])

  FROM [dbo].[militaria_table];

GO

References

Al-Btoush, A.A.S., 2015. Extracting entity relationship diagram (ERD) from English sentences. International Journal of Database Theory and Application, 8 (2), pp. 35-244.

Koloniari, G., 2017. Evaluating the use of an interactive software tool for learning BCNF normalization. In Proceedings of the 8th Balkan Conference in Informatics (p. 27). ACM.

Namrta, K., 2017. The NF-calculator–a tool for database normalization. San Diego State University.