Click here to Skip to main content
15,867,771 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
A local boatyard hires out cruising yachts on the river Forth. The season is 20 weeks long. Each week is identified by number: Week 1, Week 2, etc. There are several yachts: each has a unique name (for example, Isobel). The lengths of the yachts are recorded in metres. Groups of people make bookings of a boat for a week. The price of the hire depends on the size of the boat and the week. Each group has a “skipper” and the remainder of the group is the “crew”. There are at least two people in each group. Some people may be in several groups (in different weeks) and the skipper for a booking in one week may be a crew-member in another week. The boatyard firm keeps contact information on all the people in the group, including the skipper, including at least their name, age, and their e-mail address. When booking, the group may request one or more standard “extras” from a predefined list. Each extra is identified by a unique string, e.g. “blankets”, “snacks” etc, and has its own price. The one or more of each extra may be ordered.

Please help.

What I have tried:

entity : Season - > Attribute : weekID (PK)
entity : yacht - > Attribute : Name (PK), Length
entity : Price - > Attribute : weekID (FK), Length (FK), Price
entity : Contact Info - > Attribute : name, age, email (PK)
entity : extras- > Attribute : Name (PK), price
entity : Group - > Attribute : groupID

But that is surely not right.
Posted
Updated 21-Nov-20 8:48am
Comments
[no name] 21-Nov-20 10:01am    
Your "entity" and "attribute" and "arrows" are just "word noise", and add nothing to other's understanding. Try parsing your "bigger explanation" and come up with some logical "paragraphs". The world could use more paragraphs.
Member 14999025 21-Nov-20 11:29am    
Thanks for the answer Gerry, what i was trying to explain with the "entity" and "attribute" was the structure of the tables i thought about for the scenario.

The bigger explanation you talk about could be the scenario that was given? If so, i was looking for a set of tables with their respective attributes such that:

Season is 20 weeks long, and each week is identified by a number.
Yachts are identified by their unique name and their length.
Different groups of people may book a boat for a week. Each group has at least two people (1 skipper, and at least 1 crew member), but the skipper could not be a skipper in a different week, whereas the crew could be in a several groups.
Each member needs to provide contact info, which are Name, Age and finally email.
Each group is entitled to some extras, which are identified by a unique name and a price.

I need to extract a set of entities and attributes in a tabular way, which i tried to represent using arrows, unfortunately not that effectively. Any advice on how to work from here?
[no name] 22-Nov-20 4:34am    
The main thing you're missing is a booking / reservation / excursion; that ties a lot of things together. You need to work on that to progress.

1 solution

Maybe this ER diagram will get you started: dbdiagram.io - Database Relationship Diagram[^]
You can hover over the diagram relation links to see if they are one-to-one or one-to-many.
Can not tell exactly how this diagram is supposed to work as I did not create it.

The SQL code:
CREATE TABLE "sailors" (
  "id" SERIAL PRIMARY KEY,
  "full_name" varchar(50),
  "created_at" timestamp
);

CREATE TABLE "reservations" (
  "id" SERIAL PRIMARY KEY,
  "day" datetime,
  "sailors_id" int,
  "boats_id" int
);

CREATE TABLE "boats" (
  "id" int PRIMARY KEY,
  "name" varchar(50)
);

CREATE TABLE "crew" (
  "id" int PRIMARY KEY,
  "name" varchar(50)
);

CREATE TABLE "leads" (
  "crew_id" int PRIMARY KEY,
  "sailors_id" int
);

CREATE TABLE "belongs" (
  "crew_id" int PRIMARY KEY,
  "sailors_id" int
);

ALTER TABLE "sailors" ADD FOREIGN KEY ("id") REFERENCES "reservations" ("sailors_id");

ALTER TABLE "boats" ADD FOREIGN KEY ("id") REFERENCES "reservations" ("boats_id");

ALTER TABLE "leads" ADD FOREIGN KEY ("sailors_id") REFERENCES "sailors" ("id");

ALTER TABLE "belongs" ADD FOREIGN KEY ("sailors_id") REFERENCES "sailors" ("id");

ALTER TABLE "crew" ADD FOREIGN KEY ("id") REFERENCES "leads" ("crew_id");

ALTER TABLE "crew" ADD FOREIGN KEY ("id") REFERENCES "belongs" ("crew_id");
 
Share this answer
 
v3
Comments
Member 14999025 21-Nov-20 18:42pm    
Thanks for the Answer Rick, the only issue is that the link seems faulty, could you send it again please? Thanks again.
RickZeeland 22-Nov-20 10:26am    
See the updated solution, I also changed the reservations table as it did not seem right to use a timestamp as the primary key.
Sanskar Jain 2022 21-Aug-22 0:30am    
Rick could u plz send it again. The link is not working
RickZeeland 21-Aug-22 1:56am    
It is working for me, even without signing in, maybe your anti-virus is blocking it?
RickZeeland 21-Aug-22 2:09am    
Oh now I see the first link does not work, I can not find the ER diagram anymore, I will update the solution.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900