Data Server Technology - Database Modeling and Implementation For International Parcel Deliveries - Essay Example

?CI2611: Data Server Technology Database Modeling and Implementation For International Parcel Deliveries Introduction: International Parcel Deliveries is a worldwide courier delivery company based in Maidenhead in the South of England. This report presents a detailed overview of database Modeling and database Implementation for the web-based application of the organisation that enables its clients to book for shipment of items, make online payments, and be able to track the progress of an item to its destination. Business Requirement: Upon studying the system requirements, it was analysed that the application/business would require capturing 3 different types of details for each transaction: customer details, order details and invoice details and a look up database for obtaining the cost for the each item on the order based on the destination and the item type. Customer details would include a customer id that uniquely identifies each customer, customer name, address, postal/region code and the type of the customer (business/Private). The order details would include an order id that uniquely identifies the each order, order date, item to be shipped, destination details like name and address, destination Company and destination country, order arrival date and departure/shipped date, charge for the order, payment method and the payment status, and the order status. The invoice details would include an invoice id that uniquely identifies an invoice, invoice amount, invoice created and the due date and the invoice status. a) Conceptual Design: Using these general idea, the conceptual model or the ER (Entity Relationship) model can be created using UML (Unified Modeling Language). From the above requirement analysis, the entities are identified as customer, order invoice and cost. The ER diagram is first defined for each entity along with their attributes as shown in the below figure. Fig 1 Entity diagram for Customer Entity Fig 2 Entity diagram for Order Entity Fig 3 Entity diagram for Invoice Entity Fig 4 Entity diagram for Cost Entity Based on the above entity diagrams for each entity, generalized ER model is created by defining relationship between the entities and is shown in below figure (fig 5). Fig 5 ER model showing the relationship between the entities Relationships defined: Customer/Order: Each Customer can have zero/one/more Orders, but no two customers can have same order id (one order contains information for one customer only). Customer/Invoice: Each Customer can place zero/one Invoice and no two customers can have same Invoice id (One invoice is created for one customer only). Order/Shipment: One order can be sent to one or more shipments (shipment here just means number of items but destination is same) depending upon the number of items but each shipment contains information about one order only. Cost/Shipment: One entry in the cost lookup can be supplied to one/many shipment (order id + item no combination) but no two cost entry should be available for one shipment (one shipment contains only one entry from cost entity). Assumptions: The above model is designed based on the following assumption: All the fields entered by the user in the web application are validated for invalid input errors before storing it in the database. Destination field in the web application is filled by the user from a predefined list (may employ a drop-down list) whose values for pulled up from the destination look up table and not by entering the text for destination manually. b) Relational Model: The first step in creating the relational model is normalization. Normalization is the process of eliminating data redundancy and update anomalies (the errors while inserting, updating or deleting the database due to improper relations definitions) in order to efficiently organise the data in the database. 1st Normal Form (1NF): 1NF requires that each group of related items must be stored in a separate table with a unique column (primary key) which identifies each row in the table and all occurrences of a record type must have the same number of field, which implies that each field should hold only one value (single-valued). In our ER model designed above, the item attribute in the order entity would practically be multi-valued because the business does not restrict its customer to send only one item per destination. Hence item attribute is multi-valued and violates 1NF. To normalise, a new attribute is created in the order table called item number such that a combination of order id and item id acts as the primary key. The changes are updated in the model and the revised model is shown in below figure (fig 6). Fig 6 ER Model for order entity normalized to 1NF. 2nd Normal Form (2NF): 2NF requires that the database meets all the requirements for 1NF and should remove any subset of data that spans across multiple rows of a table and place them in a separate table. In the model above, the subset of item, item_no would span across multiple columns if more than one item is ordered for shipping. Hence the details for items are removed from the order table and a new entity is created for order items called shipment. The relationship from the cost table to the order table is now moved to shipment table. The order table is also related to the shipment table. The revised updated model is shown in below figure (fig7). Fig 7 ER Model normalized to 2NF 3rd Normal Form (3NF): 3NF requires that the database should satisfy 1NF and 2NF and in addition all the non-key attributes should purely depend upon the primary key. By analysing the model all the attributes of each entity are fully dependant on the primary key for the entity except the order cost attribute of the order entity which is derived field rather than a dependant field. The value can be obtained by summing up all the cost for individual items and hence the attribute is removed in order to normalize the model. However the invoice cost, though can be obtained by summing up the cost for all items in each order, it is dependent on the invoice id, as it may include additional costs like fines due to late payments (orders carried over to the next invoice due to non-payment), etc. Hence it is not removed. The revised model is shown in below figure. Fig 8 ER Model normalised to 3NF The properties for each attributes are defined and the updated model is shown below: Fig 9 ER model showing entities, attributes and properties for the attributes Assumptions: In order to avoid unnecessary space consumption in the database, the size of the attributes is defined efficiently. The contact_no is defined as varchar(12) because integer accepts only upto 10 digits but international numbers may have 12 digits. Item type is defined as char(3) which can take values like doc(document), low (light weight parcels) and med (medium weight parcels). The following attributes accepts only one characters. The value for Cust_type can be either as B (business ) or P (Private). The value for Invoice_status and pay_status are stored as Y (Paid) or N (not-paid). The value for order_pay_mode is either B (banking) or C (credit cards). Order status is either at depot (A), On route (R) or Delivered (D). It is the responsibility of web application developers to convert the information pulled up from the form to a appropriate values before storing it in the data base. c) Physical Database Creation: The physical tables for customer, orders, invoice, shipment and cost are created using CREATE TABLE statements and the necessary entity integrity and referential integrity are defined separately using ALTER TABLE statements. All the SQL statements used for defining the model is shown below: Customer table: CREATE TABLE CUSTOMER ( Cust_id integer NOT NULL , cust_name char(18) NULL , cust_contact_no varchar(12) NULL , cust_address varchar(30) NULL , cust_post_code char(2) NULL , cust_type char NULL ) ALTER TABLE CUSTOMER ADD CONSTRAINT pk_cust PRIMARY KEY(Cust_id ) Order Table: CREATE TABLE ORDERS ( order_id integer NOT NULL , order_date datetime NULL , order_dest_address varchar(30) NULL , order_dest_company char(10) NULL , order_arr_date datetime NULL , order_dis_date datetime NULL , order_pay_mode char NULL , order_pay_status char NULL , order_status char NULL , Cust_id integer NOT NULL ) ALTER TABLE ORDERS ADD CONSTRAINT pk_order PRIMARY KEY(order_id) ALTER TABLE ORDERS ADD CONSTRAINT fk_order FOREIGN KEY (cust_id) REFERENCES CUSTOMER(cust_id) Cost Table: CREATE TABLE COST ( destination_country char(8) NOT NULL , cost float NULL , item_type char(3) NOT NULL ) ALTER TABLE COST ADD CONSTRAINT pk_cost PRIMARY KEY(destination_country, item_type) Invoice Table: CREATE TABLE INVOICE ( invoice_id integer NOT NULL , invoice_crt_dt datetime NULL , invoice_due_dt datetime NULL , invoice_amount float NULL , invoice_status char NULL , Cust_id integer NOT NULL ) ALTER TABLE INVOICE ADD CONSTRAINT pk_invoice PRIMARY KEY(invoice_id ) ALTER TABLE INVOICE ADD CONSTRAINT fk_invoice FOREIGN KEY (cust_id) REFERENCES CUSTOMER(cust_id) ON DELETE CASCADE Shipment Table: CREATE TABLE SHIPMENT ( order_id integer NOT NULL , item_no integer NOT NULL , item_name char(18) NULL , destination_country char(8) NOT NULL , item_type char(3) NOT NULL , ) ALTER TABLE SHIPMENT ADD CONSTRAINT pk_shipment PRIMARY KEY(order_id, item_no) ALTER TABLE SHIPMENT ADD CONSTRAINT fk_shipment FOREIGN KEY (destination_country,item_type) REFERENCES COST(destination_country,item_type) ALTER TABLE SHIPMENT ADD CONSTRAINT fk1_shipment FOREIGN KEY (order_id) REFERENCES ORDERS(order_id) d) Populated tables: The tables are populated with appropriate values. The values for each table are obtained by using SELECT * statements and are shown below: Customer Table: Order Table: Invoice Table: Shipment Table: Cost Table: e) Constraints Used: Entity Constraints: For each entity, primary key constraint consisting of one or more columns is defined as not null to ensure that each row in an entity can be uniquely identified. Primary key for the customer table is cust_id which represents customer id, uniquely identifies each entry in the customer table. Entity constraint for order entity is the order id which uniquely identifies each entry in the order table. For Invoice entity, invoice_id is the primary key constraint. For Shipment entity the combination of order id and item no forms the primary key constraint. For cost entity, destination_country and item_type together forms the primary key constraint. This also ensures the table does not have duplicate entries in the primary key column. Moreover having null values would not help one to identify a particular row in a table. Thus the entity constraint helps one to identify individual rows in a table. Referential Constraint: The relationship between entities is defined using referential constraints called foreign keys. Each customer can place one or more orders and is related to the order entity through the foreign key Cust_id which represents the customer id. Each order may have one or more shipment details (same destination – more than one item) depending upon the number of items and is related to the shipment table using the order id. Invoices are created for business customers at the end of each month and the invoice entity is related to the customer entity through cust_id (customer id). Shipment entity pulls up value from the COST entity for each item depending upon the destination country and item type, and is related to the cost entity through a combination of destination country and item type. These relation attributes form the foreign key constraints for each relating entities. This type of constraint ensures consistency among the rows in the two relations. Column constraints: A not null constraint on columns ensures that mandatory information is not missed out in the transaction. Column constraints ensure that the values in each column of the table are relevant and are consistent with the other columns of the same table and the relating tables. The Not Null constraints in the primary key used in the table definition ensures that each row in a column is identifiable. If not null is not defined on primary keys, then the rows would not be unique and this would result in redundancy. The not null constraint on the foreign key columns adds meaning to the relation as it ensure that for each row in the relation, there exist a matching column on both the entity. f) Query Results: i. Produce a report listing the name, address, telephone number and type of client ordered by customer number. SQL Statement: SELECT cust_id, cust_name, cust_address, cust_post_code, cust_contact_no, cust_type from CUSTOMER order by cust_id Output: ii. List the names and details of clients and item type to be picked up on a given date, ordered by region or post code. SQL Statement: SELECT CUSTOMER.cust_name, CUSTOMER.cust_address, CUSTOMER.cust_post_code, CUSTOMER.cust_contact_no, ORDERS.order_date from CUSTOMER, ORDERS where CUSTOMER.cust_id = ORDERS.cust_id and ORDERS.order_date='2012-01-04' order by CUSTOMER.cust_post_code Output: iii. Display the destinations whose the number of transactions is greater than the average. SQL Statements: Average is assumed to be 3. SELECT DESTINATION_COUNTRY FROM SHIPMENT GROUP BY DESTINATION_COUNTRY HAVING COUNT(*) >3 Output: iv. Present a report on invoices that have not been paid by the due date, ordered by invoice number. SQL Statements: SELECT invoice_id, invoice_due_dt, invoice_status FROM INVOICE WHERE invoice_status ='N' order by invoice_due_dt, invoice_id Output: v. List the number of transactions per destination (per country) SQL Statements: SELECT COUNT(*) as no_of_transactions, DESTINATION_COUNTRY FROM SHIPMENT GROUP BY DESTINATION_COUNTRY Output: Few Generic Queries: As cost for the order is removed from the order table (in 3 NF), Cost can be derived for each Customer or each order using the below query: Cost for each order: SQL Statement: select sum(cost), shipment.order_id from COST,shipment,orders where COST.destination_country=SHIPMENT.destination_country and COST.item_type=SHIPMENT.item_type and shipment.order_id=orders.order_id group by shipment.order_id Output: Amount of Transaction for each Customer: SQL Statement: select sum(cost) as total_amount, Customer.Cust_id from COST,shipment,orders,customer where COST.destination_country=SHIPMENT.destination_country and COST.item_type=SHIPMENT.item_type and shipment.order_id=orders.order_id and orders.cust_id=CUSTOMER.cust_id group by Customer.Cust_id Output: Read More