Assignment 2 S1 20014 Cis2000 Autoparts Warehouse
Assignment 2 S1 20014 Cis2000autoparts Warehouseautoparts Warehouse Is
Assignment 2 S CIS2000 AutoParts Warehouse 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. 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.
Paper For Above instruction
Introduction
AutoParts Warehouse is a regional auto parts supplier aiming to optimize its inventory management and sales tracking through computerized systems. The implementation of a relational database system will streamline operations, facilitate real-time data access, and improve decision-making processes. This paper presents an Entity-Relationship Diagram (ERD), database table designs, normalization to the third normal form (3NF), and recommendations for data coding to enhance efficiency and usability.
ERD Development for AutoParts Warehouse
The ERD is fundamental in visualizing the relationships among key entities involved in AutoParts Warehouse operations. Entities identified include Customer, Vehicle, Sale, Parts, Store, Warehouse, TransferRequest, and Invoice.
- The Customer entity contains customer_id (PK), name, contact information.
- The Vehicle entity includes vehicle_id (PK), make, model, customer_id (FK).
- The Parts entity has parts_number (PK), description, cost, retail_price.
- The Store and Warehouse entities each possess store_number or warehouse_number (PK), location details.
- The Sale entity includes invoice_number (PK), date, customer_id (FK).
- The SaleDetails links Sales with Parts, including parts_number (FK), invoice_number (FK), quantity.
- The TransferRequest entity tracks parts movement, with tracking_number (PK), invoice_number (FK), date, parts_number (FK), origination_location, destination_store (FK).
Relationships include:
- Customers own Vehicles (one-to-many).
- Sales are made to Customers, involving one or many parts (many-to-many via SaleDetails).
- Parts can be fulfilled through direct sale or transfer; transfer requests link to parts and involve stores/warehouse locations.
The ERD depicts these entities and relationships with primary and foreign keys, illustrating one-to-many and many-to-many relationships where appropriate.
Database Table Designs and Keys
Based on the ERD, the tables are designed with candidate keys, primary keys, foreign keys, and secondary keys:
1. Customer Table
```sql
CREATE TABLE Customer (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
contact_info VARCHAR(255)
);
```
Candidate keys: customer_id
Secondary key: name
2. Vehicle Table
```sql
CREATE TABLE Vehicle (
vehicle_id INT PRIMARY KEY,
make VARCHAR(50),
model VARCHAR(50),
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);
```
Candidate keys: vehicle_id
Foreign key: customer_id
3. Parts Table
```sql
CREATE TABLE Parts (
parts_number VARCHAR(20) PRIMARY KEY,
description VARCHAR(255),
cost DECIMAL(10,2),
retail_price DECIMAL(10,2)
);
```
Candidate key: parts_number
4. Store Table
```sql
CREATE TABLE Store (
store_number INT PRIMARY KEY,
location VARCHAR(255)
);
```
Candidate key: store_number
5. Warehouse Table
```sql
CREATE TABLE Warehouse (
warehouse_number INT PRIMARY KEY,
location VARCHAR(255)
);
```
Candidate key: warehouse_number
6. Sale Table
```sql
CREATE TABLE Sale (
invoice_number INT PRIMARY KEY,
date DATE,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);
```
Candidate key: invoice_number
7. SaleDetails Table
```sql
CREATE TABLE SaleDetails (
invoice_number INT,
parts_number VARCHAR(20),
quantity INT,
PRIMARY KEY (invoice_number, parts_number),
FOREIGN KEY (invoice_number) REFERENCES Sale(invoice_number),
FOREIGN KEY (parts_number) REFERENCES Parts(parts_number)
);
```
Candidate keys: (invoice_number, parts_number)
8. TransferRequest Table
```sql
CREATE TABLE TransferRequest (
tracking_number VARCHAR(50) PRIMARY KEY,
invoice_number INT,
date DATE,
parts_number VARCHAR(20),
origination_location VARCHAR(50),
destination_store INT,
FOREIGN KEY (invoice_number) REFERENCES Sale(invoice_number),
FOREIGN KEY (parts_number) REFERENCES Parts(parts_number),
FOREIGN KEY (destination_store) REFERENCES Store(store_number)
);
```
Secondary keys include indices on date columns and foreign keys for faster joins.
Normalization to 3NF
The above table designs are normalized to 3NF:
- 1NF: All tables contain atomic values; no repeating groups or arrays.
- 2NF: All non-primary key attributes are fully functional dependent on the primary keys.
- 3NF: No transitive dependencies; non-key attributes are not dependent on other non-key attributes.
For instance, in the Customer table, contact info depends solely on customer_id, and no other attribute violates 3NF.
Using Codes for Data Simplification
AutoParts Warehouse can implement coding strategies to streamline operations:
- Product Codes: Use standardized parts_number codes encoding category, supplier, and item number (e.g., BRA-1234 for brake parts). This enables quick filtering and search.
- Location Codes: Store locations using predefined store and warehouse codes, simplifying data entry and retrieval.
- Customer and Vehicle Codes: Assign unique customer IDs and vehicle IDs with embedded information about the region or vehicle type, facilitating segmentation.
- Date and Time Codes: Use string representations or standardized formats like YYYYMMDD for dates, enabling easier sorting and processing.
- Status Indicators: Incorporate status codes (e.g., "P" for pending, "C" for completed) in transfer requests and sales.
These coded fields make data input more efficient, minimize errors, and improve output readability for reports and queries, thereby enhancing overall system usability.
Conclusion
Implementing a robust database design through ERDs, normalization, and coding techniques offers AutoParts Warehouse a competitive edge in inventory and sales management. The logical structure ensures data integrity, consistency, and ease of maintenance. Proper use of coding conventions further simplifies operations, reduces manual errors, and accelerates data processing. As the business scales, these foundational frameworks will support scalability and integration with other enterprise systems.
References
- Codd, E. F. (1970). A relational model for large shared data banks. Communications of the ACM, 13(6), 377-387.