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.
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.