In SQL create 8 tables based on the logical schema below. Note: you need to defi

Question

In SQL create 8 tables based on the logical schema below. Note: you need to define primary key(s), foreign key(s), constraints, and correct data type to get credit. Please show the syntax. Thanks!

TOURNAMENTS TourneylD PK TOURNEY MATCHES PK FK TourneylD Lanes OddLaneTeamID F MATCH GAMES CPK WinningTeamID FK MatchID EvenLane TeamID FK TEAMS TeamID TeamName CaptainlD PK FK BOWLERS BOWLER SCORES MatchlD GameNumber CPK BowlerID BowlerlD PK CPK CPK BowlerFirstName BowlerMiddlelnit BowlerState BowlerZipCode TeamID FK

Explanation / Answer

Create table command is used to create a table.

Here PK----> Primary Key,   CPK---> Composite Primary Key and FK-----> Foreign Key

Below describes the SQL query to create the table shown in the logical diagram

CREATE TABLE TOURNAMENTS(
   TourneyID INTEGER NOT NULL,
   TourneyDate DATE,
   TourneyLocation VARCHAR(100),
   CONSTRAINT PK_TOURNAMENTS PRIMARY KEY (TourneyID)

);

CREATE TABLE TOURNEY_MATCHES(
   MatchID INTEGER NOT NULL,
   TourneyID INTEGER,
   LANES VARCHAR(20),
   OddLaneTeamID INTEGER,
   EvenLaneTeamID INTEGER,
   CONSTRAINT PK_TOURNEY_MATCHES PRIMARY KEY (MatchID),
   CONSTRAINT FK_TOURNEY_MATCHES FOREIGN KEY (TourneyID)
    REFERENCES TOURNAMENTS(TourneyID),
   CONSTRAINT FK_TOURNEY_MATCHES_ODD FOREIGN KEY (OddLaneTeamID)
    REFERENCES TEAMS(TeamID),
   CONSTRAINT FK_TOURNEY_MATCHES_EVEN FOREIGN KEY (EvenLaneTeamID)
    REFERENCES TEAMS(TeamID),
);

CREATE TABLE TEAMS(
  
   TeamID INTEGER NOT NULL,
   TeamName VARCHAR(20),
   CaptainID INTEGER,
   CONSTRAINT PK_TEAMS PRIMARY KEY (TeamID),
   CONSTRAINT FK_TEAMS FOREIGN KEY (CaptainID)
    REFERENCES BOWLERS(BowlerID)  
);

CREATE TABLE BOWLERS(

   BowlerID INTEGER NOT NULL,
   BowlerLastName VARCHAR(20),
   BowlerFirstName VARCHAR(20) NOT NULL,
   BowlerMiddleInit VARCHAR(20),
   BolwerStreetAddress VARCHAR(100),
   BowlerCity VARCHAR(20),
   BowlerState VARCHAR(20),
   BowlerZipCode INTEGER NOT NULL,
   BowlerPhoneNumber INTEGER,
   TeamID INTEGER,
   CONSTRAINT PK_BOWLERS PRIMARY KEY (BowlerID),
   CONSTRAINT FK_BOWLERS FOREIGN KEY (TeamID)
    REFERENCES TEAMS(TeamID)  
  
);
CREATE TABLE BOWLER_SCORES(
  
   MatchID INTEGER,
   GameNumber INTEGER,
   BowlerID INTEGER,
   RawScore DECIMAL(10,2),
   HandicapScore DECIMAL(10,2),
   WonGame VARCHAR(100),
   CONSTRAINT PK_BOWLER_SCORES PRIMARY KEY (MatchID,GameNumber,BowlerID)
);

CREATE TABLE MATCH_GAMES(
   MatchID INTEGER,
   GameNumber INTEGER,
   WinningTeamID INTEGER,
   CONSTRAINT PK_MATCH_GAMES PRIMARY KEY (MatchID,GameNumber),
   CONSTRAINT FK_MATCH_GAMES FOREIGN KEY (WinningTeamID)
    REFERENCES TEAMS(TeamID)  
  
);

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.