Problem
Using the scenario outlined below, complete the following:
A. Write an ERD (in UML format) for the scenario that accounts for all of the attributes and relationships between the entities. Pay attention to possible ways to reduce redundancy through primary and foreign keys. You can use whatever drawing tool you want to create the ERD.
B. Write the DDL script to create your database on MySQL name your database shipping and name your script creat_shipping. You may choose appropriate data types and names for your columns.
C. Write the DML script to add data to your data on MySQL. Name your script shipping_data. You must put at least 5 ports, 10 ships, and 20 containers in your database. Make up the data as appropriate.
D. Write a DML script named shipping_queries. The script must contain at least ten (10) queries that you can run against the databases with these properties:
a. NO query should include the star (*) syntax - in other words, no simple query that just selects all columns and all rows from the database.
b. One query must include an aggregate query with grouping.
c. One query must include a sub-query.
d. One query must include calculating the volume of a container or a group of containers.
e. Five queries must include multi-table JOINS.
All SQL keywords should be uppercase to distinguish them from regular column names.
Scenario: A shipping company owns many container ships. The company wants a database to keep track of the ships, their home ports, and containers that are loaded on them. Here are the details about ships, ports, and containers you need to track:
Ships: Each ship has a name (which is unique), a displacement (how much water it displaces - measured in tons), a captain's name, the number of crew it contains, its home port, and the year (ONLY) it was built. None of this data can be empty.
Ports: Each port has a name (which is not unique), a city, and a country. None of this data can be empty.
Containers: Each container has a container ID (which is unique), its dimensions (height, width, length - in feet), weight (in pounds), and the ID of the ship on which it is currently loaded. The ID of the ship on which it is loaded can be empty if it is not currently on a ship.