Oracle SQL:

The logical model:

has TEAM P * TeamID U * TeamName UF* CarnDate * TeamNoMembers F CharName F * EntryID CARNIVAL P CarnDate CarnName * CarnDirector * CarnLocation He 1 made up of 1 1 has members 1 1 1 1 EVENT PF* CarnDate PF * EventTypeCode lead by 1 1 1 1 has 1 . ! I 1 supported by ENTRY P * EntryID EntryStart Time EntryFinishTime F CharName F * CompNo F + CarnDate OF * EventTypeCode F TeamID CHARITY CharName CharContact CharPhone supported by HO- classifies EVENTTYPE P * EventTypeCode * Event Type Desc EMERCONTACT P * EmerConPhone * EmerConFName * EmerConLName contact for HI- V O 1 1 registers for F COMPETITOR CompNo K CompFName CompLName CompGender * CompDOB * CompEmail * CompUniOpt * CompPhone * CompECRelation F * EmerConPhone TEAM P-Teamid NUMBER (3) U TeamName VARCHAR2 (30) UF CarnDate DATE TeamNoMembers NUMBER (2) F CharName VARCHAR2 (30) F. EntryID NUMBER (5) TEAM_PK (TeamID) TEAM_UN (CarnDate, TeamName) TEAM CARNIVAL_FK (CarnDate) TEAM CHARITY_FK (CharName) TEAM_ENTRY_FK (EntryID) TEAM_IDX (EntryID) CARNIVAL P" CarnDate DATE U * CarnName VARCHAR2 (50) CarnDirector VARCHAR2 (50) CarnLocation VARCHAR2 (50) > CARNIVAL_PK (CarnDate) CARNIVAL_UN (CarnName) ENTRY P.EntryID NUMBER (5) EntryStart Time DATE EntryFinishTime DATE Char Name VARCHAR2 (30) F CompNo NUMBER (4) F. CarnDate DATE F. Event TypeCode CHAR (3) F TeamID NUMBER (3) ENTRY_PK (EntryID) ENTRY_CHARITY_FK (CharName) ENTRY_COMPETITOR_FK (CompNo) ENTRY_EVENT_FK (CarnDate, EventTypeCode) ENTRY TEAM FK (TeamID) EVENT PF. CarnDate DATE PF Event TypeCode CHAR (3) > EVENT_PK (CarnDate, Event TypeCode) EVENT_CARNIVAL_FK (CarnDate) EVENT_EVENTTYPE_FK (Event TypeCode) CHARITY P* Char Name VARCHAR2 (30) CharContact VARCHAR2 (50) * CharPhone CHAR (10) CHARITY_PK (CharName) EVENTTYPE P. Event TypeCode CHAR (3) Event Type Desc VARCHAR2 (50) EVENTTYPE_PK (Event TypeCode) EMERCONTACT P "EmerConPhone CHAR (10) * EmerConFName VARCHAR2 (30) EmerCont Name VARCHAR2 (30) EMERCONTACT_PK (EmerConPhone) COMPETITOR CompNo NUMBER (4) CompFName VARCHAR2 (30) CompLName VARCHAR2 (30) * CompGender CHAR (1) CompDOB DATE CompEmail VARCHAR2 (50) * CompUniOpt CHAR (1) CompPhone CHAR (10) CompECRelation CHAR (1) F EmerConPhone CHAR (10) > COMPETITOR_PK (CompNo) COMPETITOR_EMERCONTACT_FK (EmerConPhone) (b) List all registered runners who registered to support a charity as an INDIVIDUAL for the '42.2 km Marathon' event. The listing must include the carnival date, the first name and last name of the runner as a single column called runner, the charity name, the charity contact person, and the full description of the supported event in which the runner is running order the listing in ascending order of carnival date, within a carnival order by the charity name and then by the runners' full name within a supported charity. has TEAM P * TeamID U * TeamName UF* CarnDate * TeamNoMembers F CharName F * EntryID CARNIVAL P CarnDate CarnName * CarnDirector * CarnLocation He 1 made up of 1 1 has members 1 1 1 1 EVENT PF* CarnDate PF * EventTypeCode lead by 1 1 1 1 has 1 . ! I 1 supported by ENTRY P * EntryID EntryStart Time EntryFinishTime F CharName F * CompNo F + CarnDate OF * EventTypeCode F TeamID CHARITY CharName CharContact CharPhone supported by HO- classifies EVENTTYPE P * EventTypeCode * Event Type Desc EMERCONTACT P * EmerConPhone * EmerConFName * EmerConLName contact for HI- V O 1 1 registers for F COMPETITOR CompNo K CompFName CompLName CompGender * CompDOB * CompEmail * CompUniOpt * CompPhone * CompECRelation F * EmerConPhone TEAM P-Teamid NUMBER (3) U TeamName VARCHAR2 (30) UF CarnDate DATE TeamNoMembers NUMBER (2) F CharName VARCHAR2 (30) F. EntryID NUMBER (5) TEAM_PK (TeamID) TEAM_UN (CarnDate, TeamName) TEAM CARNIVAL_FK (CarnDate) TEAM CHARITY_FK (CharName) TEAM_ENTRY_FK (EntryID) TEAM_IDX (EntryID) CARNIVAL P" CarnDate DATE U * CarnName VARCHAR2 (50) CarnDirector VARCHAR2 (50) CarnLocation VARCHAR2 (50) > CARNIVAL_PK (CarnDate) CARNIVAL_UN (CarnName) ENTRY P.EntryID NUMBER (5) EntryStart Time DATE EntryFinishTime DATE Char Name VARCHAR2 (30) F CompNo NUMBER (4) F. CarnDate DATE F. Event TypeCode CHAR (3) F TeamID NUMBER (3) ENTRY_PK (EntryID) ENTRY_CHARITY_FK (CharName) ENTRY_COMPETITOR_FK (CompNo) ENTRY_EVENT_FK (CarnDate, EventTypeCode) ENTRY TEAM FK (TeamID) EVENT PF. CarnDate DATE PF Event TypeCode CHAR (3) > EVENT_PK (CarnDate, Event TypeCode) EVENT_CARNIVAL_FK (CarnDate) EVENT_EVENTTYPE_FK (Event TypeCode) CHARITY P* Char Name VARCHAR2 (30) CharContact VARCHAR2 (50) * CharPhone CHAR (10) CHARITY_PK (CharName) EVENTTYPE P. Event TypeCode CHAR (3) Event Type Desc VARCHAR2 (50) EVENTTYPE_PK (Event TypeCode) EMERCONTACT P "EmerConPhone CHAR (10) * EmerConFName VARCHAR2 (30) EmerCont Name VARCHAR2 (30) EMERCONTACT_PK (EmerConPhone) COMPETITOR CompNo NUMBER (4) CompFName VARCHAR2 (30) CompLName VARCHAR2 (30) * CompGender CHAR (1) CompDOB DATE CompEmail VARCHAR2 (50) * CompUniOpt CHAR (1) CompPhone CHAR (10) CompECRelation CHAR (1) F EmerConPhone CHAR (10) > COMPETITOR_PK (CompNo) COMPETITOR_EMERCONTACT_FK (EmerConPhone) (b) List all registered runners who registered to support a charity as an INDIVIDUAL for the '42.2 km Marathon' event. The listing must include the carnival date, the first name and last name of the runner as a single column called runner, the charity name, the charity contact person, and the full description of the supported event in which the runner is running order the listing in ascending order of carnival date, within a carnival order by the charity name and then by the runners' full name within a supported charity