· 7 years ago · Oct 01, 2018, 08:12 PM
1use [master]
2
3IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'NewspaperAdsHandler_2')
4 CREATE DATABASE [NewspaperAdsHandler_2]
5else
6 DROP DATABASE [NewspaperAdsHandler_2]
7 CREATE DATABASE [NewspaperAdsHandler_2]
8go
9use [NewspaperAdsHandler_2]
10
11create table AdCustomer
12(
13 AdCustomerID INT NOT NULL PRIMARY KEY,
14 Name_ varchar(40) NOT NULL,
15 Surname varchar(40) NOT NULL,
16 PhoneNumber varchar(40),
17 Email varchar(40) not null,
18 CreditCardNumber varchar(40) not null ,
19
20);
21
22create table AdRedactor
23(
24 AdRedactorID INT NOT NULL PRIMARY KEY,
25 Name_ varchar(40) NOT NULL,
26 Surname varchar(40) NOT NULL,
27 PhoneNumber varchar(40),
28 Email varchar(40) not null,
29 CreditCardNumber varchar(40) ,
30);
31
32create table OrderList
33(
34 OrderID INT NOT NULL PRIMARY KEY,
35 OrderAdsCount int ,
36 AdRedactorID int REFERENCES AdRedactor(AdRedactorID ),
37 AdCustomerID int REFERENCES AdCustomer(AdCustomerID ),
38 OrderTime DateTime,
39 OrderPrice int
40);
41
42create table AdsList
43(
44 AdID INT NOT NULL PRIMARY KEY,
45 AdName varchar(40) Not null,
46 AdPublicationDate DateTime ,
47 AdPrice int,
48 OrderID int REFERENCES OrderList(OrderID )
49);
50
51create table AdsTextList
52(
53 AdsTextID INT NOT NULL PRIMARY KEY,
54 AdID int REFERENCES AdsList(AdID ),
55 AdText Text,
56);
57
58create table AdsTextTarif
59(
60 AdsTextTarifID INT NOT NULL PRIMARY KEY,
61 AdTextID int REFERENCES AdsTextList(AdsTextID),
62 EstimatedTextPrice Int,
63);
64
65create table AdsImageList
66(
67 AdImageID INT NOT NULL PRIMARY KEY,
68 AdID int REFERENCES AdsList(AdID ),
69 Image_ Image,
70);
71
72create table AdsImageTarif
73(
74
75
76 AdsImageTarifID INT NOT NULL PRIMARY KEY,
77 AdID int REFERENCES AdsList(AdID ),
78 EstimatedAdImagesPrice Int,
79);
80create table AdHashtagList
81(
82 AdHashtagID INT NOT NULL PRIMARY KEY,
83 AdID int REFERENCES AdsList(AdID ),
84 HashtagName varchar(50),
85);
86
87create table AdsHashtagTarif
88(
89 AdHashtagTarifID INT NOT NULL PRIMARY KEY,
90 AdID int REFERENCES AdsList(AdID ),
91 EstimatedAdHashtagsPricePrice Int,
92);
93 /*
94create table Teams
95(
96 TeamID INT NOT NULL PRIMARY KEY,
97 TeamName [NVARCHAR](50) NOT NULL,
98);
99
100create table Bowlers
101(
102 BowlerID INT NOT NULL PRIMARY KEY,
103 BowlerFirstName [NVARCHAR](50) NOT NULL,
104 BowlerLastName [NVARCHAR](50) NOT NULL,
105 BowlerStreetAddress [NVARCHAR](50) NOT NULL,
106 BowlerCity [NVARCHAR](50) NOT NULL,
107 BowlerState [NVARCHAR](50) NOT NULL,
108 BowlerZipCode CHAR(10) NOT NULL,
109 BowlerPhoneNumber CHAR(13) NOT NULL,
110 GamesBolwled INT NOT NULL,
111 TotalScore INT NOT NULL,
112 TotalAverage INT NOT NULL,
113 CurrentHandicap INT NOT NULL,
114 TeamID INT REFERENCES Teams (TeamID)
115);
116
117ALTER TABLE Teams ADD CapitanID INT NOT NULL REFERENCES Bowlers (BowlerID)
118
119create table Tourney_Matches
120(
121 MatchID INT NOT NULL PRIMARY KEY,
122 TourneyID INT NOT NULL REFERENCES Tournaments(TourneyID),
123 Lanes INT NOT NULL,
124 OddLaneTeamID INT NOT NULL REFERENCES Teams(TeamID),
125 EvenLaneTeamID INT NOT NULL REFERENCES Teams(TeamID)
126);
127
128create table Match_Games
129(
130 MatchID INT NOT NULL UNIQUE REFERENCES Tourney_Matches (MatchID),
131 GameNumber INT NOT NULL UNIQUE,
132 WinningTeamID INT NOT NULL,
133 PRIMARY KEY (MatchID, GameNumber)
134)
135
136create table Bowler_Scores
137(
138 MatchID INT NOT NULL REFERENCES Match_Games(MatchID),
139 GameNumber INT NOT NULL REFERENCES Match_Games(GameNumber),
140 BowlerID INT NOT NULL REFERENCES Bowlers (BowlerID),
141 RawScore INT NOT NULL,
142 HandicapScore INT NOT NULL,
143 WonGame INT NOT NULL
144 PRIMARY KEY (MatchID,GameNumber,BowlerID)
145)*/
146Go