Analyze some Data

Tasks 1. Using the UML notation produce a conceptual data model for the International Parcel Delivery database system. This diagram should capture the data, associations and multiplicity for each association. Each association should be named. State any assumptions (if necessary) you make when creating the diagram (AC 2.1) 2. Convert the conceptual model produced in task 1 to a relational model, and show clearly each of the steps you have taken to arrive at a set of fully normalized tables (AC 2.1) 3. Implement the relational model described in task 2 using SQL (AC 2.2) 4. Populate the tables with appropriate data (AC 2.2) 5. Write a short report explaining the benefits of queries in database design (AC 3.1) 6. Write SQL queries for 5 transactions listed in the system overview. Evaluate the useful of the queries that you wrote to the business. (AC 3.2, 3.3) 7. Critically test and review your database. (AC 4.1) 8. Create documentation for your database. a. Create technical documentation that supports the implementation of your database (AC 4.1) 9. Critically evaluate your database focusing on data verification and validation and explain how you have ensured that your finished product meets your user’s needs. (AC 4.4, 4.5) What to hand in: Create a report to be handed in to the module tutor. The report will contain: a. The initial class (conceptual) diagram, and the assumptions you have made. b. The revised (relational) diagram showing primary and foreign keys, and the steps (1NF to 3NF) taken to produce the relational model. c. The relevant SQL CREATE TABLE statements. d. A screenshot of each of the populated tables e. A full discussion of the constraints used, covering the three types: entity, referential and column. Explain why you used them and how they ensure that the integrity and consistency of your data is maintained. f. A screenshot of the output of each query.