ER Diagram and Normalization Factor Assignment

26-04-16 Rochio Varela 2 comments

Case Study

AutoParts Warehouse is a small auto parts supplier with locations in several Midwestern metro areas. The business is based on the idea of discount prices for auto parts that are always in stock or can be delivered within hours. The owners have decided to computerize their operations to allow them to better track their business and manage their inventories for just in time deliveries for sales. In ER Diagram and Normalization Factor Assignment, Data to be gathered for each customer sale includes invoice number, date, parts number, cost, retail price. Customer data is collected that includes basic contact information and vehicles owned, which includes vehicle make and model. For parts that have to be delivered to the store to fulfill a customer purchase, a transfer request is created. Data associated with these transfers include tracking number, invoice number, date, parts number, origination location (store or warehouse) number, destination store number.

Ans 1: ER Diagram for Autoparts Warehouse

Below diagram is the entity relationship diagram for Autoparts Warehouse solution.

ER Diagram and Normalization Factor Assignment Programming Assignments Help

ER Diagram

Ans 2: Table Designs

Database tables designs are as given below for the list of entities identified to implement Autoparts warehouse.

Customer Table

Column_Name Keys DataType
Customer_Id PK Numeric
First Name Varchar
Last Name Varchar
Address Varchar
Contact No Numeric
City_Id FK Numeric
Vehicle_ID FK Numeric
Employee_Id FK Numeric

 

Employee Table

Column Name Keys DataType
Employee_Id PK Numric
FirstName Varchar
LastName Varchar
Address Varchar
ContactNo Numeric
City_Id FK Numeric

  

Orders Table

Column Name Keys DataType
Tracking_Id PK Numeric
Invoice_num Numeric
Date Date
Part_Id FK Numeric
From_Location FK (Store_ID) Numeric
To_Location FK (Store_ID) Numeric
Discount_Id FK Numeric
Discount_Percentage Numeric
Customer_ID FK Numeric

 

City_Master Table

 

Column Name Keys Datatype
City_Id PK Numeric
City_name Varchar
State Varchar
Pincode Numeric

Store_Master Table

ColumnName Keys Datatype
Store_Id PK Numeric
City_id FK Numeric
Is_Warehouse Char (Y/N Allowed)
Address Varchar
Contact No Numeric

Discount_Master Table

ColumnName Keys DataType
Discount_Id PK Numeric
City_Id From FK Numeric
City_Id To FK Numeric
Discount_Percentage Numeric

 

Stock_Master Table

ColumnName Keys Datatype
Stock_Id PK Numeric
Part_Id FK Numeric
Cost Numeric
Retail_Price Numeric
Quantity Numeric
Store_Id FK Numeric

 

Parts_Master Table

ColumnName Keys Datatype
Part_Id PK Numeric
Description Varchar
Vehicle_Id FK Numeric

  

Vehicle_Master Table

ColumnName Keys Datatype
Vehicle_Id PK Numeric
Make Varchar
Model Varchar
Year_of_Manufacturing Numeric
Store_Id FK Numeric
Read about: Features of Database Management System Assignment

Ans3: 3NF Tables

I have designed the Autoparts warehouse database and normalized the table’s upto 3NF.

What is 3NF?

Third normal form (3NF) is the third step in normalizing a database and it builds on the first and second normal forms i.e. 1NF and 2NF.

In 3NF state, all column reference in referenced data that are not dependent on the primary key should be removed. In other words, only foreign key columns should be used to reference another table, and no other columns from the parent table should exist in the referenced table

In this case, I have followed this rule to normalize this database. E.g.

Orders Table

Tracking ID PK
Invoice_Num
Date
Part_Id
From_Location FK (Store_ID)
To_Location FK(Store_ID)
Discount_Id FK
Discount_Percentage
Customer_Id FK

Parts_Master

Part_ID PK
Description
Vehicle_ID FK

 

Customer

Customer_Id PK
FirstName
LastName
Address
ContactNo
City_Id FK
Vehicle_Id FK
Employee_Id FK

In above example, we can see that there are no duplicate values present in parent tables and we are getting relevant data from the reference tables.

Ans4: Suggestion

To automate the processes of Autoparts Warehouse, a new automated system shall be in place as per the database design mentioned in above sections.

A web based solution will suffice the purpose as this will be used across multiple locations.

There will be two type of users, one is the employees of organization and another one may be the store owner (we have not considered this type of users in DB design as it was not required in case study).

Major modules of the applications will be:

  • Employee Lifecycle Management
  • Customer Lifecycle Management
  • Parts Inventory
  • Vehicle Details
  • Order Details
  • City Masters
  • Reports
Read about Newspaper Web Designing Assignment

Below users will be required to maintain the system:

  1. Database Users: To manage data entry and company administration with Responsive UIs, easy to data backup / restore, Easy tool for data entry, data security / consistency, Information Management, High performance, Availability and reporting
  1. Warehouse Managers: Will be responsible to maintain the stocks, orders, updation of all masters to keep data up to date, Discountine management etc.

The said system will be able to raise alarms once stock level goes below the defined limits so that in time order processing can be done.

Reports can also be generated from the said system which will help management to keep track on sales/employee performance. Reports can be generated on daily, monthly or quarterly or half-yearly or yearly basis. This will certainly help to improve the business as well as the performance of employees.

We ensure system will fulfill all the expectation of Autoparts Warehouse management.

Programming Assignments Help is the best assignment help provider in the United Kingdom. Our online assignment writing help UK is especially dedicated for the students studying in all UK colleges and universities. Submit assignment to get the best quality assignment help.



Discussion

  • ER Diagram and Normalization Factor Assignment ...

    […] ER Diagram and Normalization Factor Assignment: Autoparts Warehouse have decided to computerize their operations, Normalization Factor,database design  […]

    26-04-16
  • Relational Database System Assignment

    […] Entity Relationship diagram has been created according to the case study. It shows six entities: person, member, specialist, […]

    26-04-16
  • Comments are closed.