Bela Pinga Ltd Company - Essay Example

Bela Pinga Ltd Company Introduction Bela Pinga Ltd is an established company having existence for over ten years. Bela Pinga Ltd sales and purchase different types of Wines. They are an SME, therefore, have a limited resource. At presently most of their Information System is paper based. They are planning to have an E-Commerce solution (Information system) for their routine activities so as widening current customer base and simultaneously improving their services to the customer. Bela Pinga Ltd. had employed an information consultant and she identified that the E-commerce solution must integrate Sales Department, in Sheffield; Logistics Department, in Sheffield; and Purchasing Department, in Lisbon, Portugal. Database Management System (DBMS) can achieve this. By doing this Bela Pinga Ltd. business will achieve Speed of response, Accuracy of Information, Cost Effective Transaction, Low Overheads and Maintaining Service Standards. This report will analyze the sales processing (Sales Department) of the Bela Pinga Ltd, which is located in Commercial Street, Sheffield, receives and processes sales orders from customers. In the initial section, the application domain of the potential user(s) and requirement analysis of the sale office will be presented. After that, E-R Model and Relational Data Model (RDM) will be analyzed for sale office system. In last section of the report database overview and screenshots of the database (Microsoft Access Database System) made will be shown. Application Domain & Business Requirement The Information (Database) System will be made in Microsoft Access database system for sales order processing views for “Bela Pinga Ltd”. This will be serves as prototype for the final version that will be based on a combination of Linux, Apache, MySQL and PHP. This Information system will be used by for the sales order processing view. The potential user of the Information system will be customers who place orders and the sales assistants who checks availability of the desired type of wine in Bela Pinga Ltd stores at the Logistics Department in Nottingham Street, Pitsmoor, Sheffield. Sales office system requirements are summarized below: The Sales Department, receives and processes sales orders from our customers Sales assistants checks availability of the desired type of wine in stores If the quantity of wines are available, order is placed for customer Customer make payment for order and receives the wine through “Bela Pinga Ltd” delivery service However, if the quantities of the desired type of wines are not available, then sales assistants notifies it to the Purchasing Department, which is further taken care by the Purchasing department and is out of scope for this assignment (assumption). E-R Model Figure 1: ERM for Sale Order Processing View (Bela Pinga Ltd.) Figure 1 show the E-R Model for the Sale Order Processing View of Bela Pinga Ltd. The four major entity identified are Customer, Order, Wine Details and Invoice. The important attributes and relationship (1:1 and M: N) are shown in E-R Model. Relational Data Model (RDM) Figure 2: RDM for Sale Order Processing View (Bela Pinga Ltd.) Figure 2 show the Relational Data Model (RDM) for the Sale Order Processing View of Bela Pinga Ltd. An additional entity OrderWineDetails (relationship Contains in E-R Model) are added for wine details for different order. The other four entities identified from E-R Model are CustomerDetails (Customer), OrderDetails (Order), WineDetails (Wine Details) and PaymentDetails (Invoice). The important attributes and relationship (1:1 and M: N) are shown in RDM. The Description of al the entities and their attributes are given below: CustomerDetails: Each instance of the CustomerDetails is a person who purchases or purchased wines from the “Bela Pinga Ltd”. Customer details will be stored in this entity (table). CustomerID (PK): Customer identification number (ID) Title: Customer title such as Mr., Mrs., Ms., Dr., etc. Last_Name: The last name of the customer. First_Name: The first name of the customer. Address: The address (street, etc) of the customer. Town: The town (address) of the customer. County: the county (address) of the customer. Post_Code: The postcode (address) of the customer. Email: The email id of the customer. Phone_No: The phone number of the customer. Mobile_No: The mobile phone number of the customer. Date_Added: The date customer added or placed order first. OrderDetails: Each instance of OrderDetails is the order that the “Bela Pinga Ltd” customers makes. Customer order details will be stored in this entity. OrderID (PK): Order identification number (ID) CustomerID (FK): Customer identification number (ID) Status: Status of the order; Paid, delivered, received, etc. Order_Date: Date of the order made by customer. Delivery_Date: Date of the order delivered to customer. OrderWineDetails: Its each instance contains the details of wines (order) made in OrderDetails. This entity is made so that different quantity of wines for each type of wines for a single customer order can be recorded. OrderID (FK): Order identification number (ID) WindCode (FK): Wine Code (identification) number (ID) Quantity_Ordered: Ordered quantity of wine. Quantity_Delivered: Delivered quantity of wine. WineDetails: Each instance of WineDetails is the Wines that the “Bela Pinga Ltd” offers to its customers. Wines details will be stored in this table. WindCode (PK): Wine Code (identification) number (ID) Wine_Producer: the producer of the wine. Region: the region of the wine produced. Wine_Type: type of wine such as port, red, normal white, etc. Vignard_Type: Type of vignard for the wines. Quantity_Available: Available quantity in stock. Re_Order_Quantity: Re-Order quantity for the wine. Price: Price of the wines in per bottles/litres. Date_Updated: Date details of wine last updated. Last_Arrival_Quantity: The quantity of the wines last arrived. Special_Requirements: special requirements for wine such as Port and red wine are stored in special dark storage rooms at a temperature between 10-20° C, but no special requirements are needed to store the white wine Storage_Location: Where the wines are stored; Self/Rack or dark storage rooms. Observation: Any observation for the wines. PaymentDetails: Each instance of PaymentDetails is the invoice that is billed to customer for (wines) orders made by them. This will contain the details of payment made by the customer for their order. PaymentID (PK): Payment identification number (ID). OrderID (FK): Order identification number Payment_Total: Total amount for the order. Payment_Type: The type of payment made, that is, whether by cash, direct deposit, cheque, credit card or whatever. Credit_Card_Type: Credit card type used, that is, Visa, Master card, etc. Credit_Card_Number: Credit card number. Cheque_Number: Cheque number, if payment made through cheque. Bank_Name: Cheque issuing bank name. Payment_Date: date on which payment made. Database Overview & Screenshots The Information (database) system for the sales processing view of the Bela Pinga Ltd. is developed in Microsoft Access database system. Screenshot 1 shows the tables, forms, queries and report made in database. Screen Shot 1: Reports, Forms, Queries and Tables Screen Shot 2: Welcome Page Screenshot 2 shows the welcome screen when database is opened. Screen Shot 3: Navigation (Menu) Page Screenshot 3 shows the navigation menu (forms and reports) for the database. Screen Shot 4: Order Details Form Screenshot 4 shows the Order Details form for the database. Screen Shot 5: Payment Details Form Screenshot 5 shows the Payment Details form for the database. Screen Shot 6: Order Total Payment Check (OrderAmount Query) Screenshot 6 shows the order amount for any specific order for the database. This can be used for checking total payment of the order in making invoice to customer. Screen Shot 7: Wine Details Form Screenshot 7 shows the Wine Details Form for the database. Screen Shot 8: Customer Details Form Screenshot 8 shows the Customer Details Form for the database. Screen Shot 9: Customer Details Report Screenshot 9 shows the Customer Details Report for the database. Screen Shot 10: Order Details (All) Report Screenshot 10 shows the Order Details (All) Report for the database. Screen Shot 11: Pending Order Details Report Screenshot 11 shows the Pending Order Details Report for the database. Screen Shot 12: Wine Details Report Screenshot 12 shows the Wine Details Report for the database. Screen Shot 13: Wine Re-Order Level Report Screenshot 13 shows the Wine Re-Order Level Report for the database so that different wines can be purchased to maintain the re-order level of each type of wines. Screen Shot 14: Sales Notification Report Screenshot 14 shows the Sales Notification Report for the database. This will be sent immediately if any order contains the quantity of wines which is more than the available quantity of the wine. Screen Shot 15: Order Details Report for Last two Weeks Screenshot 15 shows the Order Details Report for Last two Weeks for the database. Bibliography Elmasri & Navathe. Fundamentals of Database Systems. 3rd edition. Pearson Education Powel, G. 2006. Beginning Database Design, Wiley Publishing, Inc. Viescas, J.L. 2004. Microsoft Office Access 2003 Inside Out , Microsoft Press. Editor: Blanton, A. & Worthwell, A. 2004. Microsoft Office Access 2003 Step By Step, Microsoft Press. Read More