In this assignment, you are to create a database design for a software system that will...

  

Transcribed Image Text:

In this assignment, you are to create a database design for a software system that will be used to manage a marina. A marina is a place where boat owners can keep their boats "parked" in berths. Each size of a berth has a price which is calculated as 200 times the size of the berth (in meters) per month. For example, a berth that is 12m in size will be priced as 2400/month. For catamaran vessels the multiplication factor is 245 times the size of the berth. Boats are measured by their length and are always rounded up to the nearest meter. For example, a boat that is 11.2m will be recorded as a 12m boat. The marina that we will model has services such as: refueling, battery disposal, sewerage pump-out, lift out, waste oil disposal, and laundry. Each type of service has a standard price. When the boat is moored in the berth, the boat can be connected to shore power so that small appliances on the boat can be used without the need for the owner to run a generator. If the boat consumes more than 50kWh of electricity, the owner of the boat will be billed for the excess usage. Boat owners are provided invoices at the end of each month for all the services that they have received throughout the month which is due 10 days after the invoice is generated providing, they are in good credit standing. When the payment for the invoice has been received, the owner will be given a payment receipt. For owners who are not in good credit standing, then they must pay for the services in advance. Advance payments will result in an invoice and a payment receipt immediately. If an owner contacts the marina for a certain length of berth and that berth size is not available, the marina can put the owner on a wait list. Requests on the waitlist can be set to expire after a certain amount of time. A history of all boats who were using each berth must be kept so that it is possible to know which boat was in which berth at a specific point in time. The tables in your database must be in 3NF. You do not need to show the process of normalization or the 1NF or 2NF tables, just the final design. When you are finished with your design, generate the 'create tables' script from the MySQL Workbench too and make sure that you can execute the script on your database. What to Submit The MySQL workbench file containing the model. The model needs to have consistent naming and must include all keys and attributes. The SQL file that can be used to create the tables in your model. Layout of a Typical Marina Just for reference to give you an idea of some things that might be tracked. Western Boundary Waste Oil Tank EFTPOS Fuel Pump Rubbish Wellington Marine Centre Dockway Lowryay Yacht Club Coffee Cart MARINA ENTRANCE seaviewmarina Grading Rubric (20 points) Entities (6 points) Relationships (6 points) Table Creation Script (4 points) Normalized to 3NF (4 points) Entrance 2 MAP SHOWING LAYOUT AT SEAVIEW MARINA BERTH KEY 10m 12m 14m 16m 18m 20m 14m Cat Ramp Access Marina Office 18m Cat 24m Cat 26m Cat Tra Park Rubbish Per Fier Launching Ramp 10 Parking Public T Bus Route to Wellington *Lower Hutt LOCATION OF SEAVIEW MARINA WELLINGTON HARBOUR In this assignment, you are to create a database design for a software system that will be used to manage a marina. A marina is a place where boat owners can keep their boats "parked" in berths. Each size of a berth has a price which is calculated as 200 times the size of the berth (in meters) per month. For example, a berth that is 12m in size will be priced as 2400/month. For catamaran vessels the multiplication factor is 245 times the size of the berth. Boats are measured by their length and are always rounded up to the nearest meter. For example, a boat that is 11.2m will be recorded as a 12m boat. The marina that we will model has services such as: refueling, battery disposal, sewerage pump-out, lift out, waste oil disposal, and laundry. Each type of service has a standard price. When the boat is moored in the berth, the boat can be connected to shore power so that small appliances on the boat can be used without the need for the owner to run a generator. If the boat consumes more than 50kWh of electricity, the owner of the boat will be billed for the excess usage. Boat owners are provided invoices at the end of each month for all the services that they have received throughout the month which is due 10 days after the invoice is generated providing, they are in good credit standing. When the payment for the invoice has been received, the owner will be given a payment receipt. For owners who are not in good credit standing, then they must pay for the services in advance. Advance payments will result in an invoice and a payment receipt immediately. If an owner contacts the marina for a certain length of berth and that berth size is not available, the marina can put the owner on a wait list. Requests on the waitlist can be set to expire after a certain amount of time. A history of all boats who were using each berth must be kept so that it is possible to know which boat was in which berth at a specific point in time. The tables in your database must be in 3NF. You do not need to show the process of normalization or the 1NF or 2NF tables, just the final design. When you are finished with your design, generate the 'create tables' script from the MySQL Workbench too and make sure that you can execute the script on your database. What to Submit The MySQL workbench file containing the model. The model needs to have consistent naming and must include all keys and attributes. The SQL file that can be used to create the tables in your model. Layout of a Typical Marina Just for reference to give you an idea of some things that might be tracked. Western Boundary Waste Oil Tank EFTPOS Fuel Pump Rubbish Wellington Marine Centre Dockway Lowryay Yacht Club Coffee Cart MARINA ENTRANCE seaviewmarina Grading Rubric (20 points) Entities (6 points) Relationships (6 points) Table Creation Script (4 points) Normalized to 3NF (4 points) Entrance 2 MAP SHOWING LAYOUT AT SEAVIEW MARINA BERTH KEY 10m 12m 14m 16m 18m 20m 14m Cat Ramp Access Marina Office 18m Cat 24m Cat 26m Cat Tra Park Rubbish Per Fier Launching Ramp 10 Parking Public T Bus Route to Wellington *Lower Hutt LOCATION OF SEAVIEW MARINA WELLINGTON HARBOUR