Database Design Activities of Bela Pinga Ltd - Case Study Example

Bela Pinga LTD Abstract Database design is the process of transforming a logical data model into an actual physical database. After the logical data model is complete, though, what must be done to implement a physical database? This paper presents the global domain description, statement of user requirements and the integration process carried out to merge the three views (Sales, Purchase and Stock). Then it covers the necessary physical design activities and its analysis before the prototype is designed for the chosen view, Sales view. Domain Description: Bela Pinga, Ltd is a growing company in the wine business industry. The aim of the company is to evolve in E-commerce solution and making full use of the database. However, as a started firm, it has a very limited resources and a small customer base. This carried out many problems and difficulties in developing and improving the company. As the managing director said “these manual statement are very time consuming to operate, untidy in appearance and difficult to amend" (Baptista, 2007). There is no integrity between the three offices (Sales, Purchase and Logistics). As a result time consuming, inefficiency and hardware cost are problems in the company. For example, Customers might order something that is out of stock, because the sales department has no idea about the stock and there is no constant update with current inventory. Besides that, the paper based system effect on the employee speed of response as well as the accuracy of the information which indirectly force company's customers to shift to other competitors with better customer service system (Elmasri, 2000). Also, there is no centralized location to store the information therefore the employees spend long time to find the information they need. For example, customer information is stored manually in papers; thus it is difficult for the sales staff to go through papers to find the details required for advertisement. Also, employees at logistic and sale departments need to go down and check if they have the order issued by the customer which is a quite annoying process and time consuming. The same problem is faced during purchase order process, thus it will be quite useful to have a database which alarms the purchase department to make orders when required rather than waiting for logistic department notification. Moreover, high storage space is required to maintain all the records. Due to lake of data flow, duplicate data spread and sometime is done again and again with different employees over the company. Statement of User Requirements: In sales department, customers need to wait for long time to get their order due to the paper based procedures. In addition, if any customer has a question regarding an order, the employee needs to search in a huge amount of papers in order to find the invoice and serve the customer. These manual procedures take long time as well as costing the company huge amounts of money. Sale department staff needs to have a unique order ID and distinctive customer number connected to the database to track this information easily when needed. Having customer information stored in central location, facilities market and research strategy. Moreover, in order to meet legal requirements, the database should provide information about the date of birth of each customer to prevent unauthorized sale. In terms of the logistic department, customers are annoyed by ordering something that is out of stock. Therefore, the logistic department needs to be constantly updated with the current inventory (Beynon, 1992). When wines are received, the stock needs to update the product list upon the new inventory arrival. As any product is sold, wine storage record needs to be updated automatically and provide the information of the new stock level. An automatic alert when a wine has reached the minimum level would be beneficial. Regarding the purchase department, it needs to contact its three regional offices who request the wine from vineyards owners before shipping the purchasing order to the logistic department. Logistic department needs to be constantly updated with the current inventory. The new system should make it easy for the purchasing department employee to keep track on the number of orders they made, shipments detail regarding each order and information about each vineyard. Availability of such information makes it easy for the purchasing employees to do their job faster and more efficiently. Database is required to addresses the problem of maintaining data integrity between the Sales, Purchase and Logistics Department. It is required to help the employee saving time, do their job in efficient way and provide them with a better way to find the information they need. Moreover, the database should be easy for restructuring, upgrading and maintenance so that it can meet future requirements. Entities According to the diagram, the process starts when the Customer places an Order in the sale department. This Order is received by the Sales person. The Order consists of Order Line which specifies the Products. The Products are stored in the Stock. If the Customer purchases some Products the inventory will automatically be updated to show the new stock level. Relationship and Description There are nine entities in the WineZone database which are customers, orders, Sales Person, Order Line, Product, Stock, Purchase Order, suppliers and shipment. Order line is the associative entity. The underlined attributes are the identifying attributes (primary key) that will be used to uniquely identify each entity. The following section will present the relationship between the entities along with the attributes, primary and foreign key: Description: Customer entity is a table in the database. It contains information about the customers. Many attributes are associated with the Customer entity. Each customer will be given a customer I.D. (primary key)that will never change, and will be used to uniquely identify that customer in the database. The other attributes of the customer entity are name, address, email address, Zip_Code and Tel which introduced to keep trace of the customer. Moreover, the date of birth attribute is important to obtain because of drinking age regulations. The Order entity is another table in the database which used to keep track of the order. Description: Sales Person entity contains information about the Sales Staff and it consists of attributes which are First_Name, Sir_Name, Address and date_of_Birth. The primary key is Staff_ID and it used as a foreign key in the Order table to connect the two entities. This will show each staff with the sales order he has served, thus facilitate the process of employee promotion and incentive in terms of performance. It is clear that, there is many to one relationship between the two entities. Each Order is processed by one and only one Sales Person, while each Sales Person may receive one or more than one Order. An optional relation indicates the Sales Person may or may not receive an Order Description: When a customer makes a purchase the quantity is kept in the Order Line table. Oreder_Line_ID, Order_ID, Quantity and Bar_Code are attributes associated with this entity. Each order line will be given an Order_Line_ID that will never change and will be used to uniquely identify that Order. It is an associative entity to breakdown many to many relationship Order and Product entities. These two tables linked together using a foreign key called Order_ID in the Order Line table. There is one to many relationships between the two entities. Description: 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. 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. Description: 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. Description: 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 relation 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. Description: 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. Description: 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,Supplier_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. Description: 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. View Integration Type conflicts occur when the same concept is represented in different views by different structures. For example, delivery exists as an entity in the stock view, and as an attribute for order entity in the sale view. Domain conflicts occur when the property is represented using different data types in different views. Customer_ID perceived as being an integer in one view and as a varchar in the other view. Constraint conflicts occur when different views impose different constraints on certain aspects of the application domain. For example, one view may identify a concept by a particular property (which will influence the choice of primary key); yet another view may identify the same concept using a different property, or even a group of properties. Logical system Physical Design: Physical database design cannot 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 analyzing 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 The table below presents an illustration of the sizing information for the Bela Binga database: Table Average Max Record Length File Size Customer 100 200 256 51200 Order 50 100 41 4100 Sales Person 5 10 107 1070 Order Line 50 100 39 3900 Product 200 300 52 15600 Total 75870 Table1: Sizing information The capacity of the database is 75870, which demonstrates a practical space enable the database to satisfy the requirements of the users. Updated E-R Diagram: 2) Volatility Analysis: Volatility is simply a measure of change in market since it shows how many records such as customer and orders have been added or deleted from the database. The table below represents the volatility rate for each record stored Bela Pina’s sale database according to Beyond Davies formula: Volatility = flow rate / population Table flow rate Population Volatility Customer 600 1500 40% Order 500 1500 33% Sales Person 7 25 28% Order Line 50 100 50% Product 200 600 33% Table 2: Volatility 3) Transaction Analysis An important role in physical designing process of a database is played by the step of observing and analyzing the transactions performed on database. Issues Customer Invoice Transaction Name Issue Customer Invoice Volume: Average: Peak: Volume: 10 per day 20 per day Figure1: Transaction map for issue customer invoice The transaction map shows that performing this transaction involves reading the customer table to get the order for that customer and reading through the order line to find the order details relating to that order. Event Action Access Trans Refs Peak Refs 1 Get Customer Read 100 200 2 Get Order Read 10 20 4 Get Order line Read 50 100 Order Entry: Transaction Name Order Entry Volume: Average: Peak: Volume: 2 per day 10 per day Figure2: Transaction map for order entry Order entry transaction consists of three steps. First it starts by entering the customer’s details by the sales person then specify the orders quantity and price by reading the order table. Event Action Access Trans Refs Peak Refs 1 Get Sales Person Read 5 10 2 Get Customer Read 2 10 4 Get order Read 100 100 Delivery: Transaction Name Delivery Volume: Average: Peak: Volume: 6 per day 10 per day Figure3: Transaction map for delivery According to the figure, delivery transaction engages reading the customer table to find the address and other detail then moves to read the order table to get the order belongs to the customer and via the order line more detail about the order is found such as the quantity. Event Action Access Trans Refs Peak Refs 1 Get Customer Read 100 200 2 Get Order Read 6 10 4 Get Order line Read 50 100 View order information Transaction Name Delivery Volume: Average: Peak: Volume: 10 per day 20 per day Figure4: Transaction map for View order information The above figure shows the view product information transaction. This transaction helps the sale staff to get ordered product information such as the price and stock name by reading the order line table to find the products consisted in the order. Event Action Access Trans Refs Peak Refs 1 Get Order Read 10 20 2 Get Order line Read 50 100 4 Get Product Read 200 300 Justification – Implications The views to be created are justified as follows: 1. Retrieve all suppliers who supply more than one type of wine 2. Produce status of the supplies of wines 3. Produce a list of all pending payments for the wines 4. Produce a list of all suppliers and their observation. 5. Produce a list of all wines and their availability status References/Bibliography Baptista, M. (2007). Managing Director, 08 March. Beynon-Davies, P. (1992). Relational Database Design. Oxford: Blackwell. Elmasri, R. & Shamkant B. N. (2000). Fundamentals of database systems / Reading, Mass.; Harlow : Wesley. Silberschatz, A., Henry F. Korth (1997). Database System Concepts. New York: McGraw Hill. Read More