Data Server Technology - Database Modeling and Implementation For Essay

Data Server Technology - Database Modeling and Implementation For International Parcel Deliveries - Essay Example The paper tells that 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 dat e and the invoice status. 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. 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