(B) Using the attached database table, answer following questions:1. Discuss whether the tables you designed in SLP 2 (in the previous module) have satisfied 1NF, 2NF, and 3NF. Explain why or why not.
ISALE - E-commerce system will be the focus of the database design. The system requires a database to help in tracking their online sales and inventory as well as customers. The project will be executed to enable digitization of the firm’s online transactions. The database principal objective is to ensure all the sales can be tracked and delivered to the respective customers through their detailed addresses. The objectives can be categorically outlined as below:
Ensure smooth management of the inventory
Ensure concurrent ordering by respective customers
Ensure proficient administration of customer data as well as the sales transactions.
To update the administrations on the current inventory as well as monthly sales.
The scope of the project is limited to e-commerce systems. The bounds ensure the prime intents are attained on the implementation of the project. Consequently, for the digitization of the project, the scope will integrate attributes of the customer information, inventory data and addresses and the relationship between the entities.
The data specification will define the significant entities and associated attributes. The e-commerce database has diverse entities inclusive of customer, inventory, transactions, orders, cashiers, and address. The entities depend on each other and are regarded as of more value related to each other. The relationships will be implemented as one to one, many to many or one to many. The entity relationship design supports database integrity, and reduces data redundancy. This also eases the process of a multi-query application on the records. The entities and attributes are outlined below for implementation.
Customer
Customer ID (Primary key)
Customer Name
Zip Code (Foreign Key)
Email (unique id)
Phone (unique id)
Inventory
Product ID (Primary key)
Product Name
Product Description
Unit Price
CAPoP (cost as percentage of price)
Quantity
Current Inventory
Monthly purchases.
Transactions
Trans ID (primary key)
Order ID (Foreign Key)
Date
Total Cost
Clearing Cashier ID (Foreign key)
Status
Order
Order ID (primary key)
Product ID (Foreign key)
Customer ID (foreign key)
Quantity/Units sold
Date
Address
Zip Code (Primary key)
City
State
Country
Cashiers
Cashier ID (Primary key)
Cashier Name
Cashier Email
Cashier Phone
Zip Code
Figure 1: E-Commerce Database ERD
