· 7 years ago · Sep 30, 2018, 09:38 AM
1use [master]
2
3IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'BowlingTE')
4 CREATE DATABASE [BowlingTE]
5else
6 DROP DATABASE [BowlingTE]
7 CREATE DATABASE [BowlingTE]
8go
9use [BowlingTE]
10
11create table Tournaments
12(
13 TourneyID INT NOT NULL PRIMARY KEY,
14 TourneyDate DATETIME NOT NULL,
15 TournetLocation [NVARCHAR](200) NOT NULL
16);
17
18create table Teams
19(
20 TeamID INT NOT NULL PRIMARY KEY,
21 TeamName [NVARCHAR](200) NOT NULL,
22 CapitanID INT NOT NULL
23);
24
25create table Bowlers
26(
27 BowlerID INT NOT NULL PRIMARY KEY,
28 BowlerFirstName [NVARCHAR](200) NOT NULL,
29 BowlerLastName [NVARCHAR](200) NOT NULL,
30 BowlerStreetAddress [NVARCHAR](200) NOT NULL,
31 BowlerCity [NVARCHAR](200) NOT NULL,
32 BowlerState [NVARCHAR](200) NOT NULL,
33 BowlerZipCode CHAR(200) NOT NULL,
34 BowlerPhoneNumber CHAR(200) NOT NULL,
35 GamesBolwled INT NOT NULL,
36 TotalScore INT NOT NULL,
37 TotalAverage INT NOT NULL,
38 CurrentHandicap INT NOT NULL,
39 TeamID INT NOT NULL,--REFERENCES Teams (TeamID)
40);
41
42--ALTER TABLE Teams ADD CapitanID INT NOT NULL REFERENCES Bowlers (BowlerID)
43
44create table Tourney_Matches
45(
46 MatchID INT NOT NULL PRIMARY KEY,
47 TourneyID INT NOT NULL, --REFERENCES Tournaments(TourneyID),
48 Lanes [NVARCHAR](200) NOT NULL,
49 OddLaneTeamID INT NOT NULL, --REFERENCES Teams(TeamID),
50 EvenLaneTeamID INT NOT NULL, --REFERENCES Teams(TeamID)
51);
52
53create table Match_Games
54(
55 MatchID INT NOT NULL, --REFERENCES Tourney_Matches (MatchID),
56 GameNumber INT NOT NULL,
57 WinningTeamID INT NOT NULL,
58 PRIMARY KEY (MatchID, GameNumber)
59)
60
61create table Bowler_Scores
62(
63 MatchID INT NOT NULL, --REFERENCES Match_Games(MatchID),
64 GameNumber INT NOT NULL, --REFERENCES Match_Games(GameNumber),
65 BowlerID INT NOT NULL, --REFERENCES Bowlers (BowlerID),
66 RawScore INT NOT NULL,
67 HandicapScore INT NOT NULL,
68 WonGame INT NOT NULL,
69 PRIMARY KEY (MatchID,GameNumber,BowlerID)
70)
71
72
73ALTER TABLE Tourney_Matches
74ADD CONSTRAINT FK_Tourney
75FOREIGN KEY (TourneyID)
76REFERENCES Tournaments(TourneyID);
77
78ALTER TABLE Match_Games
79ADD CONSTRAINT CPK_Matches
80FOREIGN KEY (MatchID)
81REFERENCES Tourney_Matches(MatchID);
82
83ALTER TABLE Tourney_Matches
84ADD CONSTRAINT FK_OddLaneTeam
85FOREIGN KEY (OddLaneTeamID)
86REFERENCES Teams(TeamID);
87
88ALTER TABLE Tourney_Matches
89ADD CONSTRAINT FK_EvenLaneTeam
90FOREIGN KEY (EvenLaneTeamID)
91REFERENCES Teams(TeamID);
92
93ALTER TABLE Match_Games
94ADD CONSTRAINT FK_WinningTeamID
95FOREIGN KEY (WinningTeamID)
96REFERENCES Teams(TeamID);
97
98ALTER TABLE Teams
99ADD CONSTRAINT FK_CapitanID
100FOREIGN KEY (CapitanID)
101REFERENCES Bowlers(BowlerID);
102
103ALTER TABLE Bowlers
104ADD CONSTRAINT FK_TeamID
105FOREIGN KEY (TeamID)
106REFERENCES Teams(TeamID);
107
108ALTER TABLE Bowler_Scores
109ADD CONSTRAINT FK_BowlerID
110FOREIGN KEY (BowlerID)
111REFERENCES Bowlers(BowlerID);
112
113ALTER TABLE Bowler_Scores
114ADD CONSTRAINT FK_MatchID
115FOREIGN KEY (MatchID)
116REFERENCES Match_Games(MatchID);
117
118ALTER TABLE Bowler_Scores
119ADD CONSTRAINT FK_GameNumberID
120FOREIGN KEY (GameNumberID)
121REFERENCES Match_Games(GameNumber);
122Go