-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy path09. Table relations Homework P6.sql
157 lines (123 loc) · 6.64 KB
/
09. Table relations Homework P6.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
/*
05 февруари 2019 г.20:57:59
User:
Server: M812\SQLEXPRESS
Database: UniversityDatabase
Application:
*/
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Subjects
(
SubjectID int NOT NULL,
SubjectName nchar(50) NOT NULL
) ON [PRIMARY]
ALTER TABLE dbo.Subjects ADD CONSTRAINT
PK_Subjects PRIMARY KEY CLUSTERED
(
SubjectID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
ALTER TABLE dbo.Subjects SET (LOCK_ESCALATION = TABLE)
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Majors
(
MajorID int NOT NULL,
Name nchar(50) NOT NULL
) ON [PRIMARY]
ALTER TABLE dbo.Majors ADD CONSTRAINT
PK_Majors PRIMARY KEY CLUSTERED
(
MajorID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
ALTER TABLE dbo.Majors SET (LOCK_ESCALATION = TABLE)
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Students
(
StudentID int NOT NULL,
StudentNumber int NOT NULL,
StudentName int NOT NULL,
MajorID int NOT NULL
) ON [PRIMARY]
ALTER TABLE dbo.Students ADD CONSTRAINT
PK_Students PRIMARY KEY CLUSTERED
(
StudentID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
ALTER TABLE dbo.Students ADD CONSTRAINT
FK_Students_Majors FOREIGN KEY
(
MajorID
) REFERENCES dbo.Majors
(
MajorID
) ON UPDATE NO ACTION
ON DELETE NO ACTION
ALTER TABLE dbo.Students SET (LOCK_ESCALATION = TABLE)
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Agenda
(
StudentID int NOT NULL,
SubjectID int NOT NULL
) ON [PRIMARY]
ALTER TABLE dbo.Agenda ADD CONSTRAINT
PK_Agenda PRIMARY KEY CLUSTERED
(
StudentID,
SubjectID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
ALTER TABLE dbo.Agenda ADD CONSTRAINT
FK_Agenda_Students FOREIGN KEY
(
StudentID
) REFERENCES dbo.Students
(
StudentID
) ON UPDATE NO ACTION
ON DELETE NO ACTION
ALTER TABLE dbo.Agenda ADD CONSTRAINT
FK_Agenda_Subjects FOREIGN KEY
(
SubjectID
) REFERENCES dbo.Subjects
(
SubjectID
) ON UPDATE NO ACTION
ON DELETE NO ACTION
ALTER TABLE dbo.Agenda SET (LOCK_ESCALATION = TABLE)
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Payments
(
PaymentID int NOT NULL,
PaymentDate datetime NOT NULL,
PaymentAmount decimal(18, 2) NOT NULL,
StudentID int NOT NULL
) ON [PRIMARY]
ALTER TABLE dbo.Payments ADD CONSTRAINT
PK_Payments PRIMARY KEY CLUSTERED
(
PaymentID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
ALTER TABLE dbo.Payments ADD CONSTRAINT
FK_Payments_Students FOREIGN KEY
(
StudentID
) REFERENCES dbo.Students
(
StudentID
) ON UPDATE NO ACTION
ON DELETE NO ACTION
ALTER TABLE dbo.Payments SET (LOCK_ESCALATION = TABLE)
COMMIT