Top Special Offer! Check discount
Get 13% off your first order - useTopStart13discount code now!
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.
Hire one of our experts to create a completely original paper even in 3 hours!