Shoeempire is an online shoe store. The main reason for choosing an online store for the database project is because online stores are becoming popular today. A secure and well-designed database is required for the project because the operation and success of the company will be dependent on the database. If a faulty database is made, will not be served well as transactions in an online store relies mainly on the data provided by the database.
Four entities are vital to the database. These are the Customer entity which holds the data about the customer. This is filled out upon registering s a customer of the store. Only vital information needed by the store to process orders is collected from the client. The shoe table holds information about each shoe. The Order Table on the other hand contains basic information about the an order. Order status table is dependent on the Order Table whose main function is to keep track of the status of a certain order. For example, if payments have been made or if it has been shipped. The attributes of each table are chosen because they are needed to complete each transaction. Possible attributes which are not needed are not included because it will only eat up storage space.
Below are the tables/entities and their corresponding attributes. The Customer Table is already compliant to the rules of the 3rd Normal Form as there are no multivalued attributes as well all attributes are dependent on the primary key which is the customer number.
After applying the rules of normalization up to the 3rd normal form the results are as follows:
The shoe table is normalized as follows since the price of each shoe may depend on the size, color and category so they become a multivalued attribute where the shoe Style Number is used for the general description of the shoe and Shoe Number as the unique identification of each shoe.
The order table is normalized as follows because of the possibility of having multiple types of orders in one transaction.
Below is the resulting ER diagram of the database showing the relationship between the normalized tables.
Limitations set for the database includes inability to create an order if the a client has not registered. He needs to register first before transacting business. Payment options must only be those indicated and order Quantity of orders must not be negative and must be a whole number. All fields must be filled out when placing an order. RMA will not be given if the order number hasn’t been validated. The sale associate will be responsible for determining if an order has been paid and the warehouse associate in determining availability and shipping.
The size of the database will rely on the number of orders made and the products. Within a year, it is expected that around five hundred thousand (500,000) records have been created to increase at least 5% monthly.
