In this project, we will discuss a computer retail company inventory database. This project focuses on the merchandise inventory, including the production, warehouse storage, store stock, and customer purchase of the merchandise.
1. ER model
Figure 1 shows the ER mode of this database project. Its entities includes the product (merchandise, includes laptop and desktop subclass), manufacturer, warehouse, store and customer. It also has the manufacturer-make, product-in-store, product-in-warehouse, and customer-order relationships.
This project focuses on the product (or merchandise) entity. Its attributes include item number, model, type, price and quantity. The item number distinguishes every item, and we choose it as the primary key of product entity. Here quantity is a derived attribute, whose value can be calculated from the sum of item quantity at store and warehouse. Product can be divided into two disjoint subclass, laptop and desktop. We assume the computer retail company only sell computer, includes laptop and desktop. Another entity is the manufacturer of the product (computer in this case). It has the attributes of the manufacturer name, ID, address, and telephone. Manufacturer ID is the primary key. The company is consisted of some warehouses and stores. The store attributes include store_no, address, and telephone. The store_no is primary key. Similar to store, warehouse has the ware_no as primary key, and other attributes include address and telephone. The last entity is customer, described by customer name, customer number, and payment. Customer number is the primary key.
Besides these entities, the model also includes 5 relations. First, manufacturer makes product. We assume that one manufacturer can make many products for the company, and one product can only be made by one manufacturer. Second, warehouses provide merchandise to stores. We assume that...