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.
Ans 2: Table Designs
Database tables designs are as given below for the list of entities identified to implement Autoparts warehouse.
|Is_Warehouse||Char (Y/N Allowed)|
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.
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.
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
Read about Newspaper Web Designing Assignment
Below users will be required to maintain the system:
- 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
- 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.