Install MySQL in your computer. Give the CREATE TABLE statements to create the C

ID: 3857559 • Letter: I

Question

Install MySQL in your computer. Give the CREATE TABLE statements to create the CLYSTMS Database. The CREATE TABLE statements for the tables Coach and LevelHistory are provided below for you. We need all of the tables. You can use MySQL from oracle or XAMPP. So far I already have this:

DROP TABLE IF EXISTS Coach;

CREATE TABLE Coach(
   CoachId    INT UNSIGNED AUTO_INCREMENT,
   LName      VARCHAR(30) NOT NULL,
   FName      VARCHAR(30) NOT NULL,
   Phone      VARCHAR(12) NOT NULL,
   EMail      VARCHAR(60) NOT NULL,
   CONSTRAINT Coach_pk PRIMARY KEY(CoachId)
);

CREATE TABLE LevelHistory(
   LH_Id      INT UNSIGNED AUTO_INCREMENT,
   SwimmerId INT UNSIGNED NOT NULL,
   LevelId    INT UNSIGNED NOT NULL,
   StartDate DATE NOT NULL,
   Comment    VARCHAR(250),
   CONSTRAINT levelhistory_pk PRIMARY KEY(LH_Id),
   CONSTRAINT levelhistory_ck_1 UNIQUE(SwimmerId, LevelId),
   CONSTRAINT levelhistory_swimmer_fk FOREIGN KEY(SwimmerId)
      REFERENCES Swimmer(SwimmerId),
   CONSTRAINT levelhistory_level_fk FOREIGN KEY(LevelId)
      REFERENCES Level(LevelId)
);

1 Swimmer (Swimmerld. FName. LName. Phone. Email. JoinTime. CurrentLevelld. Main CT ld Main CT Since Candidate Keys [1]Swimmerld [1] Ma CT Id references CareTaker(CT ld). [21 Current d references Foreign Keys Level(Levelld) Nullable Attributes [1] The relationship "Main Caretaker" is implemented as two attributes Notes Main CT Id and Main CT Since. It is also acceptable to use a separate relation to implement the relationship. [2] CurrentLevelld is a derived column that can be obtained from the table LevelHistory 2 Caretaker(CT ld. FName, LName, Phone, Emai CT Candidate Keys Foreign Keys Nullable Attributes Notes 3 OtherCaretakerCOC ld. Swimmerld. CT ld. Since) Candidate Keys 1l OC ld. 121 iswimmerld. CT ldh Foreign Keys Nullable Attributes Notes [1] A surrogate key, OC ld, is created as the primary key. This is optional. Level Leveld, Level, Description [1] Levelld, [2] Level Candidate Keys Foreign Keys Nullable Attributes Possibly Description, depending on assumptions made Notes 5 Leve Hist dd, Swimmerld. Levelld. StartDate. Comment Candidate Keys TETLH ld, [2] Swimmerid Leveld [1] Swimmerld references Swimmer (Swimmerld), 2 Level ld references Foreign Keys Level(Levelld) Nullable Attributes Comment Notes 1) Asurrogate key. LH ld is created as the primary key. This is optional 6 Coach Coachld FName, LName, Phone, Email) Candidate Keys (1) Coachld Foreign Keys Nullable Attributes Notes 7 Meet (MeetID, Title. Date, StartTime. EndTIme, Venueld, Coachld)

Explanation / Answer

Queries to create all tables is given below :

CareTaker :

CREATE TABLE CareTaker(
CT_Id INT UNSIGNED AUTO_INCREMENT,
FName VARCHAR(30) NOT NULL,
LName VARCHAR(30) NOT NULL,
Phone VARCHAR(12) NOT NULL,
EMail VARCHAR(60) NOT NULL,
CONSTRAINT CT_Id_pk PRIMARY KEY(CT_Id)
);

OtherCareTaker :

CREATE TABLE OtherCaretaker(
OC_Id INT UNSIGNED AUTO_INCREMENT,
SwimmerId INT UNSIGNED NOT NULL,
CT_Id INT UNSIGNED NOT NULL,
Since INT NOT NULL,
CONSTRAINT OC_Id_pk PRIMARY KEY(OC_Id),
CONSTRAINT SwimmerId_CT_Id_pk UNIQUE(SwimmerId,CT_Id)
);

Level :

CREATE TABLE Level(
LevelId INT UNSIGNED AUTO_INCREMENT,
Level INT UNSIGNED NOT NULL,
Description VARCHAR(100) NULL,
CONSTRAINT LevelId_pk PRIMARY KEY(LevelId),
CONSTRAINT Level_pk UNIQUE(Level)
);

Swimmer :

CREATE TABLE Swimmer(
SwimmerId INT UNSIGNED AUTO_INCREMENT,
FName VARCHAR(30) NOT NULL,
LName VARCHAR(30) NOT NULL,
Phone VARCHAR(12) NOT NULL,
Email VARCHAR(60) NOT NULL,
CurrentLevelId INT UNSIGNED NOT NULL,
Main_CT_Id INT UNSIGNED NOT NULL,
Main_CT_Since INT UNSIGNED NOT NULL,
CONSTRAINT SwimmerId_pk PRIMARY KEY(SwimmerId),
CONSTRAINT Swimmer_Caretaker_fk FOREIGN KEY(Main_CT_Id) REFERENCES CareTaker(CT_Id),
CONSTRAINT Swimmer_Level_fk FOREIGN KEY(CurrentLevelId) REFERENCES Level(LevelId)
);

LevelHistory :

CREATE TABLE LevelHistory(
   LH_Id      INT UNSIGNED AUTO_INCREMENT,
   SwimmerId INT UNSIGNED NOT NULL,
   LevelId    INT UNSIGNED NOT NULL,
   StartDate DATE NOT NULL,
   Comment    VARCHAR(250),
   CONSTRAINT levelhistory_pk PRIMARY KEY(LH_Id),
   CONSTRAINT levelhistory_ck_1 UNIQUE(SwimmerId, LevelId),
   CONSTRAINT levelhistory_swimmer_fk FOREIGN KEY(SwimmerId)
      REFERENCES Swimmer(SwimmerId),
   CONSTRAINT levelhistory_level_fk FOREIGN KEY(LevelId)
      REFERENCES Level(LevelId)
);

Coach :

Venue :

CREATE TABLE Venue (
VenueId INT UNSIGNED NOT NULL,
Name VARCHAR(50) NOT NULL,
Address VARCHAR(100) NOT NULL,
City VARCHAR(30) NOT NULL,
State VARCHAR(30) NOT NULL,
ZipCode VARCHAR(16) NOT NULL,
Phone VARCHAR(12) NOT NULL,
CONSTRAINT VenueId_pk PRIMARY KEY(VenueId),
CONSTRAINT Venue1_Uk UNIQUE(Name),
CONSTRAINT Venue2_Uk UNIQUE(Address, City, State, ZipCode)

);

Meet :

CREATE TABLE Meet(
MeetId INT UNSIGNED AUTO_INCREMENT,
Date DATETIME NOT NULL,
StartTime DATETIME NOT NULL,
EndTime DATETIME NOT NULL,
VenueId INT UNSIGNED NOT NULL,
CoachId INT UNSIGNED NOT NULL,
CONSTRAINT MeetId_pk PRIMARY KEY(MeetId),
CONSTRAINT Meet_Coach_fk FOREIGN KEY(CoachId) REFERENCES Coach(CoachId),
CONSTRAINT Meet_Venue_fk FOREIGN KEY(VenueId) REFERENCES Venue(VenueId)
);

Event :

CREATE TABLE Event(
EventId INT UNSIGNED AUTO_INCREMENT,
Title VARCHAR(60) NOT NULL,
StartTime DATETIME NOT NULL,
EndTime DATETIME NOT NULL,
MeetId INT UNSIGNED NOT NULL,
LevelId INT UNSIGNED NOT NULL,
CONSTRAINT EventId_pk PRIMARY KEY(EventId),
CONSTRAINT Event_Meet_fk FOREIGN KEY(MeetId) REFERENCES Meet(MeetId),
CONSTRAINT Event_Level_fk FOREIGN KEY(LevelId) REFERENCES Level(LevelId)
);

Participation :

CREATE TABLE Participation(
ParticipationId INT UNSIGNED AUTO_INCREMENT,
SwimmerId INT UNSIGNED NOT NULL,
EventId INT UNSIGNED NOT NULL,
Committed VARCHAR(100),
CommitTime DateTime,
Participated BOOL,
Result VARCHAR(50),
Comment VARCHAR(100),
CommentCoachId INT UNSIGNED,
CONSTRAINT ParticipationId_pk PRIMARY KEY(ParticipationId),
CONSTRAINT Participation_Uk UNIQUE(SwimmerId, EventId),
CONSTRAINT Participation_Swimmer_fk FOREIGN KEY(SwimmerId) REFERENCES Swimmer(SwimmerId),
CONSTRAINT Participation_Event_fk FOREIGN KEY(EventId) REFERENCES Event(EventId),
CONSTRAINT Participation_Coach_fk FOREIGN KEY(CommentCoachId) REFERENCES Coach(CoachId)
);

V_TaskList :

CREATE TABLE V_TaskList(
VTL_Id INT UNSIGNED AUTO_INCREMENT,
MeetId INT UNSIGNED NOT NULL,
Required VARCHAR(50) NOT NULL,
Description VARCHAR(200) NOT NULL,
Penalty VARCHAR(100),
PenaltyAmt INT UNSIGNED,
CONSTRAINT V_TaskList_pk PRIMARY KEY(VTL_Id),
CONSTRAINT V_TaskList_UK UNIQUE(MeetId),
CONSTRAINT V_TaskList_Meet_fk FOREIGN KEY(MeetId) REFERENCES Meet(MeetId)
);

V_Task :

CREATE TABLE V_Task(
VT_Id INT UNSIGNED AUTO_INCREMENT,
VTL_Id INT UNSIGNED NOT NULL,
Name VARCHAR(30) NOT NULL,
Comment VARCHAR(100),
Num_V INT UNSIGNED NOT NULL,
CONSTRAINT V_Task_pk PRIMARY KEY(VT_Id),
CONSTRAINT V_Task_UK UNIQUE(VTL_Id, Name),
CONSTRAINT V_Task_V_TaskList_fk FOREIGN KEY(VTL_Id) REFERENCES V_TaskList(VTL_Id)
);

Commitment :

CREATE TABLE Commitment(
CommitmentId INT UNSIGNED AUTO_INCREMENT,
CT_Id INT UNSIGNED NOT NULL,
VT_Id INT UNSIGNED NOT NULL,
CommitTime DateTime NOT NULL,
Rescinded BOOl,
RescindedTime DateTime,
CarriedOut Varchar(30),
Comment Varchar(100),
CommentCoachId INT UNSIGNED,
CONSTRAINT Commitment_pk PRIMARY KEY(CommitmentId),
CONSTRAINT Commitment_UK UNIQUE(CT_Id, VT_Id),
CONSTRAINT Commitment_CareTaker_fk FOREIGN KEY(CT_Id) REFERENCES Caretaker(CT_Id),
CONSTRAINT Commitment_V_Task_fk FOREIGN KEY(VT_Id) REFERENCES V_Task(VT_Id),
CONSTRAINT Commitment_Coach_fk FOREIGN KEY(CommentCoachId) REFERENCES Coach(CoachId)
);

if you have any doubts you can ask in comment section.

Navigate

Install Haskell Stack For Mae OSX If you on mac Follow the instrtions to we home

Install MySQL or Oracle server free version, read their getting started manuals,

Install MySQL in your computer. Give the CREATE TABLE statements to create the C

Question

Explanation / Answer

Related Questions

Navigate