Let's break down the information into entities, attributes, and relationships: Entities: 1. Ship - ShipID (Primary Key) - ShipName - ShipType 2. Captain - CaptainID (Primary Key) - CaptainName - CaptainLicense 3. ShipmentTrip - TripID (Primary Key) - DeparturePort - ArrivalPort - RouteDetails 4. ShipmentManifest - ManifestID (Primary Key) - Date - GoodsList 5. Goods - GoodsID (Primary Key) - GoodsName - GoodsType (BM or LM) 6. Port - PortID (Primary Key) - Street - City - State - PostalCode - Country Relationships: - Ship to ShipmentTrip (Many-to-Many): - Ship can participate in multiple ShipmentTrips, and a ShipmentTrip can involve multiple Ships. Create a junction table `Ship_ShipmentTrip` with attributes `ShipID` and `TripID`. - Captain to ShipmentTrip (Many-to-Many): - A Captain can be assigned to multiple ShipmentTrips, and a ShipmentTrip can have multiple Captains. Create a junction table `Captain_ShipmentTrip` with attributes `CaptainID` and `TripID`. - Captain to Ship (Many-to-Many): - A Captain can work on multiple Ships, and a Ship can have multiple Captains over time. Create a junction table `Captain_Ship` with attributes `CaptainID` and `ShipID`. - ShipmentTrip to Goods (Many-to-Many): - A ShipmentTrip carries multiple Goods, and a Good can be part of multiple ShipmentTrips. Create a junction table `ShipmentTrip_Goods` with attributes `TripID` and `GoodsID`. - ShipmentTrip to Route (One-to-One): - Each ShipmentTrip has a specific route. Add a foreign key `RouteID` in the ShipmentTrip entity. - ShipmentTrip to ShipmentManifest (One-to-One): - Each ShipmentTrip has a corresponding ShipmentManifest. Add a foreign key `ManifestID` in the ShipmentTrip entity. - ShipmentManifest to Goods (Many-to-Many): - A ShipmentManifest lists multiple Goods, and a Good can be part of multiple ShipmentManifests. Create a junction table `Manifest_Goods` with attributes `ManifestID` and `GoodsID`. - ShipmentTrip to DeparturePort (One-to-Many): - Each ShipmentTrip departs from one Port. Add a foreign key `DeparturePortID` in the ShipmentTrip entity, referencing the Port entity. - ShipmentTrip to ArrivalPort (One-to-Many): - Each ShipmentTrip arrives at one Port. Add a foreign key `ArrivalPortID` in the ShipmentTrip entity, referencing the Port entity. - Port to ShipmentTrip (One-to-Many): - One Port can be the departure port for multiple ShipmentTrips. Add a foreign key `PortID` in the ShipmentTrip entity, referencing the Port entity. Remember to denote the cardinality and participation constraints (1, M, 0..1, etc.) in your ERD using the Crow's Foot notation. With this information, you should be able to create a comprehensive ERD using Lucidchart or any other diagramming tool of your choice.
Let's break down the information into entities, attributes, and relationships: Entities: 1. Ship - ShipID (Primary Key) - ShipName - ShipType 2. Captain - CaptainID (Primary Key) - CaptainName - CaptainLicense 3. ShipmentTrip - TripID (Primary Key) - DeparturePort - ArrivalPort - RouteDetails 4. ShipmentManifest - ManifestID (Primary Key) - Date - GoodsList 5. Goods - GoodsID (Primary Key) - GoodsName - GoodsType (BM or LM) 6. Port - PortID (Primary Key) - Street - City - State - PostalCode - Country Relationships: - Ship to ShipmentTrip (Many-to-Many): - Ship can participate in multiple ShipmentTrips, and a ShipmentTrip can involve multiple Ships. Create a junction table `Ship_ShipmentTrip` with attributes `ShipID` and `TripID`. - Captain to ShipmentTrip (Many-to-Many): - A Captain can be assigned to multiple ShipmentTrips, and a ShipmentTrip can have multiple Captains. Create a junction table `Captain_ShipmentTrip` with attributes `CaptainID` and `TripID`. - Captain to Ship (Many-to-Many): - A Captain can work on multiple Ships, and a Ship can have multiple Captains over time. Create a junction table `Captain_Ship` with attributes `CaptainID` and `ShipID`. - ShipmentTrip to Goods (Many-to-Many): - A ShipmentTrip carries multiple Goods, and a Good can be part of multiple ShipmentTrips. Create a junction table `ShipmentTrip_Goods` with attributes `TripID` and `GoodsID`. - ShipmentTrip to Route (One-to-One): - Each ShipmentTrip has a specific route. Add a foreign key `RouteID` in the ShipmentTrip entity. - ShipmentTrip to ShipmentManifest (One-to-One): - Each ShipmentTrip has a corresponding ShipmentManifest. Add a foreign key `ManifestID` in the ShipmentTrip entity. - ShipmentManifest to Goods (Many-to-Many): - A ShipmentManifest lists multiple Goods, and a Good can be part of multiple ShipmentManifests. Create a junction table `Manifest_Goods` with attributes `ManifestID` and `GoodsID`. - ShipmentTrip to DeparturePort (One-to-Many): - Each ShipmentTrip departs from one Port. Add a foreign key `DeparturePortID` in the ShipmentTrip entity, referencing the Port entity. - ShipmentTrip to ArrivalPort (One-to-Many): - Each ShipmentTrip arrives at one Port. Add a foreign key `ArrivalPortID` in the ShipmentTrip entity, referencing the Port entity. - Port to ShipmentTrip (One-to-Many): - One Port can be the departure port for multiple ShipmentTrips. Add a foreign key `PortID` in the ShipmentTrip entity, referencing the Port entity. Remember to denote the cardinality and participation constraints (1, M, 0..1, etc.) in your ERD using the Crow's Foot notation. With this information, you should be able to create a comprehensive ERD using Lucidchart or any other diagramming tool of your choice.
Step by step
Solved in 3 steps with 1 images
