Convenient Delivery pace

Question : Discuss about Convenient Delivery Pace ? Answer : Introduction Convenient Delivery is an organization that is growing at a rapid pace. It has immensely expanded itself with the passage of time and it is now essential for the organization to revise its data processing, data entry, data management and handling operations and services. There are a number of problems that are associated with the existing system and it is now required to apply the technologically advanced options to manage the data of the company. The solutions have been described in the report with the aid of Entity Relationship (ER) diagram along with the SQL queries for the database of the company. Problem analysis Convenient delivery is a small delivery business which is growing rapidly. Initially, it started with fewer suppliers and centered in one city. Entire data processing and storage was managed manually in the initial phases. However, with the increase in customer base and expansion to other cities, management of data has become a tedious task for the company. It has led to various inconsistencies in data such as duplicate customer entries, single order assigned to more than one owner-drivers and so on. As a result of which, company is now considering an implementation of information technology tools and techniques. Currently, there are also problems associated with the processing of the data as it takes a lot of time to manually process a single bit of data leading to human errors and mistakes as well. Redundancy of the data is also an issue that the company is currently facing and the same has led to some of the severe consequences in terms of order processing and maintenance. Due to the presence of these issues, it is becoming difficult for the organization to maintain the level of customer satisfaction and engagement. Also, in this competitive world, there are a huge number of entries that operate in the same field and have a competitive edge over Convenient Delivery due to their advanced data management mechanisms. Due to the manual processing of data, there are also problems associated with the security of the data as it involves enhanced chances of data loss or theft. There are also occurrences of violation of data integrity and confidentiality that has been observed. Solution description In order to remove the data inconsistencies, an automated system has been proposed with an underlying database. It will be built using the principles of relational database management system. The proposed database captures data of all customers, suppliers, parts, orders, etc. It can be prepared in any database such as MySQL, Oracle, etc. Assumptions An order can either be a replacement order or a new order. This will be depicted in database in the form TYPE of the order having either of two values: NEW or REPLACE. A BUSINESS customer has a mandatory business contact name in order to distinguish it from INDIVIDUAL customer. A customer should have an email and a phone number to contact him/her whenever required. Similarly, supplier will have a business contact name, email and contact phone number. The owner drivers are staff of the company hence they an employee ID will be assigned each driver. A supplier will be responsible for supplying the parts and also to repair the parts supplied by him/her. Every order has a status which depicts current status of the order i.e. PROCESSING, CANCELLED, DELIVERED, etc. An order will have either of these values at a particular point of time. Any number of customers can register with the company and each customer has a unique identifier `customerID`. Single user can create many orders. A part will be delivered by one and only one supplier and each part will have a stock and total parts supplied by the corresponding supplier. An order will always be delivered and cannot be return back to the company. Business rules Business rule refers to a statement that applies some or the other constraint or limitation on the particular part of the database. For instance, an element present in a particular field or an attribute associated with a particular class or relationship. Business rules are created and set up on the basis of the data that is associated with the organization and the way the organization uses the same. The information is extracted from the functions of the organization and its statistics on the conduction of the business. (Silberschatz, , Korth, and Sudarshan, 2002) There are two types of business rules: database oriented and application oriented. Database business rules are those rules which can be incorporated in the logical design of the database and application oriented are those business rules which cannot be established in the logical design of the database. The scope of the report covers the database oriented business rules only Following are key business rules of the proposed database system (Rob and Coronel, 2002): A customer can place one to many orders but each order can either a request for new parts or replacement of old parts. An ORDER cannot have one part for replacement and other part for new delivery. A customer can either be a business company or an individual user. A part can belong to more than one order and an order can contain one to many parts. An order can be delivered by only one driver but a single driver can deliver various orders. An order can be attended by only supplier but a single supplier can attend more than one order. SQL queries age. SQL syntax SELECT d.driverID, count(d.deliveryID) from OWNER_DRIVER od, DELIVERY d WHERE od.employeeID = d.driverID GROUP BY d.driverID ORDER BY od.surName, od.firstName, od.age; Query 2 a): A list all new customers who have been added to the business through partsconvenient.com after the 1st of November 2016 ordered by the joined date. SQL syntax SELECT c.customerID, c.surName, c.firstName FROM CUSTOMER WHERE c.registrationDate = `11-01-2016` b) The suburbs where most products are delivered to ordered by product name; ? SQL Syntax Select d suburb, count(d.suburb) from delivery d, order o, order_detail od, part p where d.order = o.orderID and o.order = od.order and od.partID = p.partID group by suburb order by count(d.suburb) desc, p.partName Query 3 a) : Suburbs owner drivers live sorted by their surname and first names to take decisions to improve delivery efficiency SQL syntax SELECT od.surName, od.firstName FROM OWNER_DRIVER od, DELVIERY d WHERE suburb = d.suburb ORDER BY od.surName, od.firstName; Query 4: To determine the breakdown of products delivered to vehicle repair centres and to online buyers. ? SQL syntax SELECT od.orderID, od.partID, od.numberOfParts FROM ORDER o, ORDER_DETAIL od WHERE o.orderID = od.orderID AND o.type = REPLACE List of reports The database will support following reports, but not limited to them, for the company: Report 1: List of orders which have been cancelled in the month of July, 2016. SQL syntax SELECT orderID FROM order WHERE status = `CANCELLED` AND month(deliveryDateTime) = `7` AND year (deliveryDateTime) = `2016`; Report 2: List of customers who are business based and have ordered on 1st January 2016. SQL syntax SELECT c.customerID, o.orderID FROM customer c, order o WHERE c.customerID = o.customer AND o.receiveDateTime = `01-01-2016`; Report 3: List of driver owners having age between 25-45 years. SQL syntax SELECT driverID, firstName, surname FROM owner_driver WHERE age = 25 AND age =45; Justification Convenient Delivery requires to store following key data: Details of all customers. Details of all orders. Details of all deliveries made to which supplier and by which driver. Details of all drivers. Details of all parts offered by the company. The proposed database satisfies all the above-mentioned requirements by creating following entities: Entity Attributes Relevance CUSTOMER customerID, firstName, lastName, email, streetNo, suburb, city, pincode, email, phone, businessContact, type This entity stores all the information regarding the customer i.e. the registered customer. The TYPE field depicts that the customer is either a business contact or an individual. The contact details are stored in email and phone number. ORDER orderID, receivedDateTime, deliveryDateTime, status, discount, orderType, customer This entity stores all the information regarding the orders placed by the customer. Since, all orders which are received by 3:00pm are to be dispatched first, this data is stored in receivedDateTime field. Also, the ID of the customer who has placed the order is also stored in this entity. DELIVERY deliveryID, driverID, orderID, deliveryDate, streetNo, suburb, city, pincode This entity stores all the information about the delivery i.e. which driver is assigned to which order for its delivery. ORDER_DETAIL orderDetailID, orderID, partID, numberOfParts This entity stores all the data regarding the order details of an order. OWNER_DRIVER employeeID, surname, firstname, age, streetNo, suburb, city, pincode, designation This entity provides data of all drivers working in the company. The age of the driver is very important as it pertains to the driver license hence it has been captured in the AGE field. SUPPLIER supplierID, surname, fistname, businessContact, email, phone This entity provides data of all suppliers who provides parts to the company. PARTS partID, partName, stock, total, supplier This entity provides data of all parts being services by the company. Critical evaluation The database that has been proposed is present in its normalized form. Normalization is a term that refers to the procedure of decomposition of the relations associated with the database that comprise of anomalies to come up with smaller and better structured relations as an outcome. Following are the steps that are used in order to covert a relation in its first normal form (1NF). 1NF The groups that are repeating are removed to make sure that multi-valued attributes are not present. It will lead to the presence of a single value across all the intersections of the rows and columns of a particular table (Garcia-Molina, Ullman, and Widom, 2000). 2NF: Partial functional dependencies are removed in this normal form. 3NF: Transitive dependencies are removed in this normal form (Connolly and Begg, 2002). The normalized form allows users to add data without any data inconsistency and data repetition. Redundancy is one of the prime issues that is associated with Convenient Delivery in the present scenario. Normalization will allow the complete removal of all the redundant data from the database along with the easier options for the data storage as it would convert the entire data in to a number of different smaller data sets. Human errors and mistakes are also currently present which would also be avoided through Normalization as it would provide enhanced data integrity and reduced risk for the mistakes. For processing and handling of orders, it is essential for the organization to have faster processing of operations on the data such as searching, sorting and likewise. Normalization would allow the same as it makes the tables narrower and well-structured as well. It would also allow the modification on the data to be done with much ease and lesser effort along with easier maintenance of the overall database. The database meets all the current requirements and is scalable to add any further new requirements. For example: it can be extended to add staff of the company who are responsible to process the order. Recommendation The proposed database is sufficient to meet the current growing demands of the business. However, in near future many extensions can be made to it. Security and privacy is a growing concern for all organization. To resolve this issue, the database should store password of online customers in encrypted form like MD5. With the advancement in technology, there are newer and innovative operations being added to the field of databases as well. Once the initial phase of the proposed solution is implemented successfully, there are a lot many features and functionalities that may be added in the future. First and foremost is the advanced security mechanism in the database itself for the information and data security. It would allow the organization to safeguard all of the information associated with it and protect the confidentiality, integrity and availability of the customer data. Use of multi-level authentication, advanced authorization and likewise would enhance the data security. There are also business intelligence concepts and operations that may be applied to the database such as data analysis and online analytical processing of the data. Application of these concepts of BI to the database would allow the analysts of the organization to have a better view of the customer choices and preferences, For instance, analysis of the order received for a particular product category over a certain period of time would provide an overview of the demand of the product in the market and the category of users opting for the same. Advanced index compression is another feature that may be added to the database in the future which would allow the faster and easier compression of the indexes associated with the database. It would also lead to improvement in the access of the indexes along with the improvement in the compression ratios as well. References Atkinson, M. (1981).Database. Maidenhead, Berkshire, England: Pergamon Infotech. Connolly, T. and Begg, C. (2002).Database systems. Harlow, England: Addison-Wesley. Elmasri, R. and Navathe, S. (1989).Fundamentals of database systems. Redwood City, Calif.: Benjamin/Cummings. Garcia-Molina, H., Ullman, J. and Widom, J. (2000).Database system implementation. Upper Saddle River, N.J.: Prentice Hall. Rob, P. and Coronel, C. (2002).Database systems. Boston, MA: Course Technology. Silberschatz, A., Korth, H. and Sudarshan, S. (2002).Database system concepts. Boston: McGraw-Hill.