UNIVERSITY OF WOLVERHAMPTON SCHOOL OF MATHEMATICS AND COMPUTER SCIENCE 7CI006 Data Management PG INYR UM1 2020/21 Assessment 1 Hand in: Tuesday 19thJanuary 2021 This is worth 45% of the portfolio CASE STUDY: PUGWASH SHIPPING LTD 1. Introduction Pugwash Shipping is a firm of container ship owners who wish to computerise part of their operations using a relational data base management system. The firm owns a number of ships. Each ship undertakes a number of voyages. A voyage is recorded as a list of ports visited in the course of the voyage e.g., London, Cape Town, Mumbai, Sidney is a voyage from England to Australia. There are a limited number of ports at which the company’s ships may call and a port may have many voyages passing through. At each port a number of consignments of goods may be loaded. The system is required to record the port and voyage at which a consignment is loaded. Each ship has a number of officers working on it, one of which is the captain who is in charge of the ship. Each officer works on only one ship. Systems analysis reveals that the following items are important SHIP: Ship no. (4 chars unique) Ship name (20 chars) OFFICER: Officer no. (6 chars unique) Officer name (20 chars) Officer address (40 chars) Officer Date-of-Birth CONSIGNMENT: Consignment no. (6 chars, unique within each set of records for a given ship on a given voyage at a given port) Supplier Address (40 chars) Delivery Address (40 chars) Volume (numeric, a whole number between 10 and 7,500) Weight (numeric, a whole number between 100 and 100,000) VOYAGE: Voyage no. (6 chars, unique within each set of records for a ship) Duration (number of days) PORT: Port no. (3 chars unique) Port name (20 chars) Agent name (20 chars) A further requirement is that the arrival and departure dates of a given ship on a given voyage at a particular port are stored. The departure date must be the same or later than the arrival date. 2. Manual Records The following manual records are kept: 2.1 All consignments loaded are recorded on a Port record card: PUGWASH SHIPPING LTD.PORT RECORDShip No:Port No:Ship Name:Port Name:Captain:Agent NameVoyage No:CONSIGNMENT DETAILSConsignment CodeDate DeliveredSupplier AddressDelivery AddressVolumeWeight 2.2 Details of all the ports visited on a ship’s voyage are recorded on the Voyage Record: PUGWASH SHIPPING LTD.VOYAGE RECORDShip No:Name of Captain:Ship Name:Voyage No:Duration:VOYAGE INFORMATIONPort NoPort NameAgent NameDate of ArrivalDate of Departure 2.3 Sample Data: PUGWASH SHIPPING LTD.PORT RECORDShip No:1000Port No:120Ship Name:Nautical CloudPort Name:SouthamptonCaptain:Captain BirdseyeAgent NameMr P. EyeVoyage No:000120CONSIGNMENT DETAILSConsignment CodeDate DeliveredSupplier AddressDelivery AddressVolumeWeight00000106/10/2020Widgets Inc.Parts-U-Like100220SouthamptonSidneyParkAustralia00000218/11/2020Nuts & Bolts LtdWorld Parts600080000Science ParkMumbaiWolverhamptonIndia PUGWASH SHIPPING LTD.VOYAGE RECORDShip No:1000Name of Captain:Captain BirdseyeShip Name:Nautical CloudVoyage No:000120Duration: 66 daysVOYAGE INFORMATIONPort NoPort NameAgent NameDate of ArrivalDate of Departure120SouthamptonMr P. Eye28/10/202002/11/2020130MumbaiMr G. Patel16/11/202020/11/2020135SidneyMr P. Hogan04/12/202008/12/2020130MumbaiMr G. Patel22/12/202002/01/2021 PUGWASH SHIPPING LTD.PORT RECORDShip No:1800Port No:112Ship Name:Morning CloudPort Name:ManchesterCaptain:Captain HeathAgent NameMs B TurpinVoyage No:000120CONSIGNMENT DETAILSConsignment CodeDate DeliveredSupplier AddressDelivery AddressVolumeWeight00000108/10/2020MU On-LineMU Supporters50006300Trafford ParkClub, Milano, Italy00000207/10/2020Mad HattersFashion Inc.440012000StockportParis, France00000308/11/2020Mad HattersSeville Fashions3304560StockportMalaga, Spain PUGWASH SHIPPING LTD.VOYAGE RECORDShip No:1800Name of Captain:Captain HeathShip Name:Morning CloudVoyage No:000120Duration: 38 daysVOYAGE INFORMATIONPort NoPort NameAgent NameDate of ArrivalDate of Departure112ManchesterMs B. Turpin01/10/202003/10/2020221CalaisMr F. Lechat06/10/202008/10/2020310MalagaMr P. Hogan04/11/202008/11/2020 3. Requirements You are required to: Using the Oracle Developer Data Modeler tool, draw an Entity Relationship Model for the Pugwash Shipping Ltd case study above. Explain any assumptions you have made. Hand-in: The ER diagram, plus assumptions if necessary. (40%) Use the Data Modeler tool to map your Entity Relationship model to a set of relations. Hand in: the Relational Diagram produced from the Engineering process and a copy of the final generated DDL script (no need to hand in any intermediate scripts you may have generated). Highlight any constraints you have added to the tables. Include the Relational Diagram in the main body of your Part 1 document, but put the DDL script in an appendix. Comment on any changes you made to the Relational diagram before mapping the final DDL script. Discuss why you had to make these changes (1 page maximum). (10%) Create and store appropriate test data in the database. Each table should have at least 6 rows, but some will have more, for example 1 Ship will have many Voyages Basically, the tables at the many end of a relationship should therefore contain more data than the relation at the one end. The test data should reflect the scenario. Include some commentary on how you devised your data and if you encountered any issues. (10%) The final design should be tested to ensure it will meet the requirements for the shipping company. You need to devise some queries that would test the design. Think about what sort of queries would be generated by the users of the above scenario. Produce 5 queries to test the above design. Overall the 5 queries must demonstrate the following concepts: A join;An aggregate function, such as COUNT, AVG, etc.;A selection (that is not all rows and columns are returned);Sorting of the data;A subquery Credit will be given to queries that are not trivial, for example, “SELECT * FROM Tablename” is unlikely to gain more than one mark. Credit will be given for appropriate headings, formatting and use of substitution variables where appropriate in the SQL queries. (30%) In no more than 250 words, discuss: how rules surrounding data governance can affect a shipping company; This should briefly discuss what data governance is and what particular issues would be applicable in a shipping company such as above. (10%) The above percentages are to give an indication of the weightings of the marks. Listings should demonstrate that the queries do indeed work; you will therefore need to hand in listings of the data in all your tables. See Canvas for a sample report layout. The above percentages are to give an indication of the weightings of the marks. Summary of requirementsPartNo of marksRequirements3.140Entity Relationship diagram, using the Oracle Data Modeller Tool3.210Relational Diagram and DDL script. Highlight any additional constraints you have added. Note, primary and foreign keys are expected by default, so these are not viewed as additional constraints. The DDL script can be put into an appendix (see the sample report on Canvas).3.310Test data, list the final set of data using SELECT * FROM Tablename for each table. You do not need to include the INSERT statements, or any errors encountered whilst populating the database. You must use the Oracle database for this assessment. 0 marks will be given if you use any other DBMS software.3.4305 SQL test queries using the Oracle database. Include a sentence to describe the query, the SQL query and results.3.510Short report on data governance in the above environment. Ensure you reference your work using the University’s Harvard notation. Note: Use File>Print Diagram>To Image File to create a copy of your ER or Relational Model diagram. Then insert this into your document. Ensure all your entities and relationships can be viewed on the diagram.For the queries and test data, ensure the data is properly formatted so it readable. For example, use the COURIER NEW font, so the data is correctly spaced.We do NOT need a copy of your Data Modeler files, i.e., the DMD or XML files.Your documentation should clearly identify any assumptions you have made.The above percentages are to give an indication of the weightings of the marks. Ensure you keep a copy of your work. PLEASE NOTE: This assignment is intended as an individual piece of coursework. On no account should you work on the assignment in groups to produce a group answer. Hand in date: Tuesday19th January 2021 by 2pm (PG Teaching week 11) Submit the assessment electronically on Canvas. Penalties for late submission of coursework Standard University rules apply. Note, 2pm is a strict deadline. The Electronic Management of Assessments (EMA) will automatically apply a penalty if you are late. If you have mitigating circumstances, such as illness, you can apply for mitigation, but you must do this before the due date. Retrospective claims will not be accepted. Mary Garvey 29th October 2020 Grade Criteria The following is given as a general guideline only. Marks may vary away from this rigid framework based on the professional judgement of the module team and the overall performance of each student in attempting to accurately reflect the scenario. 70%+ The answer must correctly reflect the scenario taking into account any valid assumptions that the student may make on the assignment. A correct entity-relationship diagram produced with appropriate constraints. ER diagram is engineered to a relational model. DDL script produced matches the Relational diagram. Any modifications made are fully explained and documented. A full set of SQL queries, which fully work. Excellent set of test data. Incisive report on data governance and how it relates to a shipping company that is well referenced 60-9% The answer must almost correctly reflect the scenario taking into account any valid assumptions that the student may make on the assignment. Entity-relationship diagram produced with most of the appropriate constraints. ER diagram is engineered to a relational model. DDL script produced matches the Relational diagram. Any modifications made are fully explained and documented. At least 4 SQL queries fully work. Very good set of test data. Good report on data governance and how it relates to a shipping company 50-9%: The answer must correctly reflect the scenario taking into account any valid assumptions that the student may make on the assignment. Entity-Relationship diagram produced with some of the appropriate constraints. ER diagram is engineered to a relational model. DDL script produced matches the Relational diagram. Any modifications made are not fully explained, or documented. At least 3 SQL queries fully work. Test data basic. Adequate report on data governance and how it relates to a shipping company 40-9%: The answer meets the minimum requirements to reflect the scenario. A barely acceptable entity-relationship diagram produced. ER diagram is engineered to a relational model. DDL script produced matches the Relational diagram. Any modifications made are not explained, or documented. At least two SQL queries fully work. Test data weak. Basic report, few references. 30-9%: The answer does not reasonably reflect the scenario although some good points are included. Entity-relationship diagram produced, but has major flaws. Mapping missing or not fully complete. Only one SQL query attempted. Little or no test data. Poor report, few or no references. 0-29%: The answer does not reasonably reflect the scenario and has no redeeming features.
- Assignment status: Already Solved By Our Experts
- (USA, AUS, UK & CA PhD. Writers)
- CLICK HERE TO GET A PROFESSIONAL WRITER TO WORK ON THIS PAPER AND OTHER SIMILAR PAPERS, GET A NON PLAGIARIZED PAPER FROM OUR EXPERTS