Contains information about the product and it consists of attributes which are Price, Bar_Code, Stock_ID and Name. The primary key of this table is the Bar_Code. Order Line and Product entities are associated using a foreign key called Bar_Code in the Order Line table. There is many to one relationship between the two entities. Each Order Line specifies one and only one Product, whereas each Product may specify by one or more than one Order Line. It also illustrates an optional relation that shows Product may or may not be specified in the Order Line depend on the Customer.
Product entity contains information about the product and it consists of attributes which are Price, Bar_Code, Stock_ID and Name. The primary key of this table is the Bar_Code. In contrast, Stock entity holds information about the product level available in the stock. The attributes associated with this entity are In_Stock_Qunatity, Last_Updated and Description along with Stock_ID which is specified as a primary key. In order to validate this relation, Stock_ID has been determined as a foreign key in product entity. This is beneficial since it helps stock employees to allocate each product to the right warehouse.
Each Stock may store one or many Products, while each Product stored in one and only one Stock. It also shows an optional relation since the Stock may or may not store all the products depend on the Shipment if it contains the Product or not. 3. The Purchase Order entity stores records about the quantity, price, Wine_Name, Order_Date, Order_ID, Supplier_ID and Stock_ID. Bela Pinga requests these information when making its financial statement as well as for many other financial practice. Purchase_Order_ID is the primary key in Purchase Order table.
ER diagram shows that Stock can issue many orders but each order should be issued by only one warehouse. This represents a one to many relations between Sock and Purchase Order. Stock_ID is the foreign key in this relation. It helps in specifying which warehouse has issued the order. Stock may not issue any order if there are enough products available in the stores. This explains the optional relation shown between Stock and Purchase Order. 4. Supplier entity represents Alentejo, Douro, and Minho vineyards which are the three main suppliers for Bela binga.
Rather than creating separate entities for each supplier, which may contain duplicated instances of one another, the model contains the single “Supplier” entity. Supplier_ID, Name, Adress, Zip_Code, Email, Tel and Fax are attributes related with Supplier entity. The primary key of this entity is Supplier_ID. It is also placed in Purchase Order as foreign key to facilitate the link between Supplier and Purchase Order. Many Purchase Orders are sent to different Suppliers in different cities but each Purchase Order should be received by only one supplier.
Purchase Order might not be sent to Supplier if there are enough products available in the stores. This explains the optional relation shown between Purchase Order and Supplier. 5. Shipment entity represents an important activity in the purchasing department. It specifies which wine have been shipped and when. Different attributes are associated to this entity such as Shipement_ID, Stock_ID, Sulier_ID and Shipmemt_Date. The primary key of this entity is Shipment_ID. Supplier_ID in Shipment entity works as a foreign key to link between Shipment and supplier.
By relating these two tables, determining which Supplier has sent which Shipment will be a simple task. The diagram illustrates that each Supplier can send many Shipments but each shipment should be sent by only one supplier. 6. In the relationship between the Shipment and Stock, each Stock may receive one or many Shipments, while each Shipment shipped to one and only one Stock. Stock_ID is the forging key located in Shipment entity. It helps in specifying where each Shipment should be stored. Physical Design: Physical database design can not be handled until the required size, volatility and transactional are analyzed.
In the following section, since we are going to make a prototype for one view, a sale view has been chosen to be analyzed and then prototyped. 1) Sizing Analysis: Size analysing involves an assessment to the size of the file (table) according to the numbers of trace expected to occur in each table . Different formulas and equations were used to develop an estimate capacity for Bela Pinga Company based on the type of the attributes in each table: • DATE => 8 bytes • VARCHAR2 (Size) => Size + 1 bytes NUMBER (precision, scale) => precision X 0. 5 + 1 bytes