In this task, you will create the schema for the School database and load data into
the
database. To do this, you use SQL Server Management Studio or SQL Server Management
Studio Express to execute a Transact-SQL script. The resulting School database is used as the
relational data source in the rest of the tasks in this quickstart.
To create the School database and schema
1. On the File menu, point to New, and then click Database Engine Query.
2. In the Connect to Database Engine dialog box, type either localhost or the name of
the local SQL Server instance, and then click Connect.
3. Paste the following Transact-SQL script in the query window and then click Execute.
4. SET ANSI_NULLS ON
5. GO
6. SET QUOTED_IDENTIFIER ON
7. GO
8.
9. USE [master];
10. GO
11.
12. IF EXISTS (SELECT * FROM [Link] WHERE name = 'School')
13. DROP DATABASE School;
14. GO
15.
16. -- Create the School database.
17. CREATE DATABASE School;
18. GO
19.
20. -- Specify a simple recovery model
21. -- to keep the log growth to a minimum.
22. ALTER DATABASE School
23. SET RECOVERY SIMPLE;
24. GO
25.
26. USE School;
27. GO
28.
29. -- Create the Department table.
30. IF NOT EXISTS (SELECT * FROM [Link]
31. WHERE object_id = OBJECT_ID(N'[dbo].[Department]')
32. AND type in (N'U'))
33. BEGIN
34. CREATE TABLE [dbo].[Department](
35. [DepartmentID] [int] NOT NULL,
36. [Name] [nvarchar](50) NOT NULL,
37. [Budget] [money] NOT NULL,
38. [StartDate] [datetime] NOT NULL,
39. [Administrator] [int] NULL,
40. CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
41. (
42. [DepartmentID] ASC
43. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
44. ) ON [PRIMARY]
45. END
46. GO
47.
48. -- Create the Person table.
49. IF NOT EXISTS (SELECT * FROM [Link]
50. WHERE object_id = OBJECT_ID(N'[dbo].[Person]')
51. AND type in (N'U'))
52. BEGIN
53. CREATE TABLE [dbo].[Person](
54. [PersonID] [int] IDENTITY(1,1) NOT NULL,
55. [LastName] [nvarchar](50) NOT NULL,
56. [FirstName] [nvarchar](50) NOT NULL,
57. [HireDate] [datetime] NULL,
58. [EnrollmentDate] [datetime] NULL,
59. CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED
60. (
61. [PersonID] ASC
62. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
63. ) ON [PRIMARY]
64. END
65. GO
66.
67. -- Create the OnsiteCourse table.
68. IF NOT EXISTS (SELECT * FROM [Link]
69. WHERE object_id = OBJECT_ID(N'[dbo].[OnsiteCourse]')
70. AND type in (N'U'))
71. BEGIN
72. CREATE TABLE [dbo].[OnsiteCourse](
73. [CourseID] [int] NOT NULL,
74. [Location] [nvarchar](50) NOT NULL,
75. [Days] [nvarchar](50) NOT NULL,
76. [Time] [smalldatetime] NOT NULL,
77. CONSTRAINT [PK_OnsiteCourse] PRIMARY KEY CLUSTERED
78. (
79. [CourseID] ASC
80. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
81. ) ON [PRIMARY]
82. END
83. GO
84.
85. -- Create the OnlineCourse table.
86. IF NOT EXISTS (SELECT * FROM [Link]
87. WHERE object_id = OBJECT_ID(N'[dbo].[OnlineCourse]')
88. AND type in (N'U'))
89. BEGIN
90. CREATE TABLE [dbo].[OnlineCourse](
91. [CourseID] [int] NOT NULL,
92. [URL] [nvarchar](100) NOT NULL,
93. CONSTRAINT [PK_OnlineCourse] PRIMARY KEY CLUSTERED
94. (
95. [CourseID] ASC
96. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
97. ) ON [PRIMARY]
98. END
99. GO
100.
101. --Create the StudentGrade table.
102. IF NOT EXISTS (SELECT * FROM [Link]
103. WHERE object_id = OBJECT_ID(N'[dbo].[StudentGrade]')
104. AND type in (N'U'))
105. BEGIN
106. CREATE TABLE [dbo].[StudentGrade](
107. [EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
108. [CourseID] [int] NOT NULL,
109. [StudentID] [int] NOT NULL,
110. [Grade] [decimal](3, 2) NULL,
111. CONSTRAINT [PK_StudentGrade] PRIMARY KEY CLUSTERED
112. (
113. [EnrollmentID] ASC
114. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
115. ) ON [PRIMARY]
116. END
117. GO
118.
119. -- Create the CourseInstructor table.
120. IF NOT EXISTS (SELECT * FROM [Link]
121. WHERE object_id = OBJECT_ID(N'[dbo].
[CourseInstructor]')
122. AND type in (N'U'))
123. BEGIN
124. CREATE TABLE [dbo].[CourseInstructor](
125. [CourseID] [int] NOT NULL,
126. [PersonID] [int] NOT NULL,
127. CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED
128. (
129. [CourseID] ASC,
130. [PersonID] ASC
131. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
132. ) ON [PRIMARY]
133. END
134. GO
135.
136. -- Create the Course table.
137. IF NOT EXISTS (SELECT * FROM [Link]
138. WHERE object_id = OBJECT_ID(N'[dbo].[Course]')
139. AND type in (N'U'))
140. BEGIN
141. CREATE TABLE [dbo].[Course](
142. [CourseID] [int] NOT NULL,
143. [Title] [nvarchar](100) NOT NULL,
144. [Credits] [int] NOT NULL,
145. [DepartmentID] [int] NOT NULL,
146. CONSTRAINT [PK_School.Course] PRIMARY KEY CLUSTERED
147. (
148. [CourseID] ASC
149. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
150. ) ON [PRIMARY]
151. END
152. GO
153.
154. -- Create the OfficeAssignment table.
155. IF NOT EXISTS (SELECT * FROM [Link]
156. WHERE object_id = OBJECT_ID(N'[dbo].
[OfficeAssignment]')
157. AND type in (N'U'))
158. BEGIN
159. CREATE TABLE [dbo].[OfficeAssignment](
160. [InstructorID] [int] NOT NULL,
161. [Location] [nvarchar](50) NOT NULL,
162. [Timestamp] [timestamp] NOT NULL,
163. CONSTRAINT [PK_OfficeAssignment] PRIMARY KEY CLUSTERED
164. (
165. [InstructorID] ASC
166. )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
167. ) ON [PRIMARY]
168. END
169. GO
170.
171. -- Define the relationship between OnsiteCourse and Course.
172. IF NOT EXISTS (SELECT * FROM sys.foreign_keys
173. WHERE object_id = OBJECT_ID(N'[dbo].
[FK_OnsiteCourse_Course]')
174. AND parent_object_id = OBJECT_ID(N'[dbo].[OnsiteCourse]'))
175. ALTER TABLE [dbo].[OnsiteCourse] WITH CHECK ADD
176. CONSTRAINT [FK_OnsiteCourse_Course] FOREIGN KEY([CourseID])
177. REFERENCES [dbo].[Course] ([CourseID])
178. GO
179. ALTER TABLE [dbo].[OnsiteCourse] CHECK
180. CONSTRAINT [FK_OnsiteCourse_Course]
181. GO
182.
183. -- Define the relationship between OnlineCourse and Course.
184. IF NOT EXISTS (SELECT * FROM sys.foreign_keys
185. WHERE object_id = OBJECT_ID(N'[dbo].
[FK_OnlineCourse_Course]')
186. AND parent_object_id = OBJECT_ID(N'[dbo].[OnlineCourse]'))
187. ALTER TABLE [dbo].[OnlineCourse] WITH CHECK ADD
188. CONSTRAINT [FK_OnlineCourse_Course] FOREIGN KEY([CourseID])
189. REFERENCES [dbo].[Course] ([CourseID])
190. GO
191. ALTER TABLE [dbo].[OnlineCourse] CHECK
192. CONSTRAINT [FK_OnlineCourse_Course]
193. GO
194.
195. -- Define the relationship between StudentGrade and Course.
196. IF NOT EXISTS (SELECT * FROM sys.foreign_keys
197. WHERE object_id = OBJECT_ID(N'[dbo].
[FK_StudentGrade_Course]')
198. AND parent_object_id = OBJECT_ID(N'[dbo].[StudentGrade]'))
199. ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADD
200. CONSTRAINT [FK_StudentGrade_Course] FOREIGN KEY([CourseID])
201. REFERENCES [dbo].[Course] ([CourseID])
202. GO
203. ALTER TABLE [dbo].[StudentGrade] CHECK
204. CONSTRAINT [FK_StudentGrade_Course]
205. GO
206.
207. --Define the relationship between StudentGrade and Student.
208. IF NOT EXISTS (SELECT * FROM sys.foreign_keys
209. WHERE object_id = OBJECT_ID(N'[dbo].
[FK_StudentGrade_Student]')
210. AND parent_object_id = OBJECT_ID(N'[dbo].[StudentGrade]'))
211. ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADD
212. CONSTRAINT [FK_StudentGrade_Student] FOREIGN
KEY([StudentID])
213. REFERENCES [dbo].[Person] ([PersonID])
214. GO
215. ALTER TABLE [dbo].[StudentGrade] CHECK
216. CONSTRAINT [FK_StudentGrade_Student]
217. GO
218.
219. -- Define the relationship between CourseInstructor and Course.
220. IF NOT EXISTS (SELECT * FROM sys.foreign_keys
221. WHERE object_id = OBJECT_ID(N'[dbo].
[FK_CourseInstructor_Course]')
222. AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]'))
223. ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD
224. CONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY([CourseID])
225. REFERENCES [dbo].[Course] ([CourseID])
226. GO
227. ALTER TABLE [dbo].[CourseInstructor] CHECK
228. CONSTRAINT [FK_CourseInstructor_Course]
229. GO
230.
231. -- Define the relationship between CourseInstructor and Person.
232. IF NOT EXISTS (SELECT * FROM sys.foreign_keys
233. WHERE object_id = OBJECT_ID(N'[dbo].
[FK_CourseInstructor_Person]')
234. AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]'))
235. ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD
236. CONSTRAINT [FK_CourseInstructor_Person] FOREIGN KEY([PersonID])
237. REFERENCES [dbo].[Person] ([PersonID])
238. GO
239. ALTER TABLE [dbo].[CourseInstructor] CHECK
240. CONSTRAINT [FK_CourseInstructor_Person]
241. GO
242.
243. -- Define the relationship between Course and Department.
244. IF NOT EXISTS (SELECT * FROM sys.foreign_keys
245. WHERE object_id = OBJECT_ID(N'[dbo].[FK_Course_Department]')
246. AND parent_object_id = OBJECT_ID(N'[dbo].[Course]'))
247. ALTER TABLE [dbo].[Course] WITH CHECK ADD
248. CONSTRAINT [FK_Course_Department] FOREIGN
KEY([DepartmentID])
249. REFERENCES [dbo].[Department] ([DepartmentID])
250. GO
251. ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]
252. GO
253.
254. --Define the relationship between OfficeAssignment and Person.
255. IF NOT EXISTS (SELECT * FROM sys.foreign_keys
256. WHERE object_id = OBJECT_ID(N'[dbo].
[FK_OfficeAssignment_Person]')
257. AND parent_object_id = OBJECT_ID(N'[dbo].[OfficeAssignment]'))
258. ALTER TABLE [dbo].[OfficeAssignment] WITH CHECK ADD
259. CONSTRAINT [FK_OfficeAssignment_Person] FOREIGN
KEY([InstructorID])
260. REFERENCES [dbo].[Person] ([PersonID])
261. GO
262. ALTER TABLE [dbo].[OfficeAssignment] CHECK
263. CONSTRAINT [FK_OfficeAssignment_Person]
264. GO
265.
266. -- Create InsertOfficeAssignment stored procedure.
267. IF NOT EXISTS (SELECT * FROM [Link]
268. WHERE object_id = OBJECT_ID(N'[dbo].
[InsertOfficeAssignment]')
269. AND type in (N'P', N'PC'))
270. BEGIN
271. EXEC dbo.sp_executesql @statement = N'
272. CREATE PROCEDURE [dbo].[InsertOfficeAssignment]
273. @InstructorID int,
274. @Location nvarchar(50)
275. AS
276. INSERT INTO [Link] (InstructorID,
Location)
277. VALUES (@InstructorID, @Location);
278. IF @@ROWCOUNT > 0
279. BEGIN
280. SELECT [Timestamp] FROM OfficeAssignment
281. WHERE InstructorID=@InstructorID;
282. END
283. '
284. END
285. GO
286.
287. --Create the UpdateOfficeAssignment stored procedure.
288. IF NOT EXISTS (SELECT * FROM [Link]
289. WHERE object_id = OBJECT_ID(N'[dbo].
[UpdateOfficeAssignment]')
290. AND type in (N'P', N'PC'))
291. BEGIN
292. EXEC dbo.sp_executesql @statement = N'
293. CREATE PROCEDURE [dbo].[UpdateOfficeAssignment]
294. @InstructorID int,
295. @Location nvarchar(50),
296. @OrigTimestamp timestamp
297. AS
298. UPDATE OfficeAssignment SET Location=@Location
299. WHERE InstructorID=@InstructorID AND
[Timestamp]=@OrigTimestamp;
300. IF @@ROWCOUNT > 0
301. BEGIN
302. SELECT [Timestamp] FROM OfficeAssignment
303. WHERE InstructorID=@InstructorID;
304. END
305. '
306. END
307. GO
308.
309. -- Create the DeleteOfficeAssignment stored procedure.
310. IF NOT EXISTS (SELECT * FROM [Link]
311. WHERE object_id = OBJECT_ID(N'[dbo].
[DeleteOfficeAssignment]')
312. AND type in (N'P', N'PC'))
313. BEGIN
314. EXEC dbo.sp_executesql @statement = N'
315. CREATE PROCEDURE [dbo].[DeleteOfficeAssignment]
316. @InstructorID int
317. AS
318. DELETE FROM OfficeAssignment
319. WHERE InstructorID=@InstructorID;
320. '
321. END
322. GO
323.
324. -- Create the DeletePerson stored procedure.
325. IF NOT EXISTS (SELECT * FROM [Link]
326. WHERE object_id = OBJECT_ID(N'[dbo].[DeletePerson]')
327. AND type in (N'P', N'PC'))
328. BEGIN
329. EXEC dbo.sp_executesql @statement = N'
330. CREATE PROCEDURE [dbo].[DeletePerson]
331. @PersonID int
332. AS
333. DELETE FROM Person WHERE PersonID = @PersonID;
334. '
335. END
336. GO
337.
338. -- Create the UpdatePerson stored procedure.
339. IF NOT EXISTS (SELECT * FROM [Link]
340. WHERE object_id = OBJECT_ID(N'[dbo].[UpdatePerson]')
341. AND type in (N'P', N'PC'))
342. BEGIN
343. EXEC dbo.sp_executesql @statement = N'
344. CREATE PROCEDURE [dbo].[UpdatePerson]
345. @PersonID int,
346. @LastName nvarchar(50),
347. @FirstName nvarchar(50),
348. @HireDate datetime,
349. @EnrollmentDate datetime
350. AS
351. UPDATE Person SET LastName=@LastName,
352. FirstName=@FirstName,
353. HireDate=@HireDate,
354. EnrollmentDate=@EnrollmentDate
355. WHERE PersonID=@PersonID;
356. '
357. END
358. GO
359.
360. -- Create the InsertPerson stored procedure.
361. IF NOT EXISTS (SELECT * FROM [Link]
362. WHERE object_id = OBJECT_ID(N'[dbo].[InsertPerson]')
363. AND type in (N'P', N'PC'))
364. BEGIN
365. EXEC dbo.sp_executesql @statement = N'
366. CREATE PROCEDURE [dbo].[InsertPerson]
367. @LastName nvarchar(50),
368. @FirstName nvarchar(50),
369. @HireDate datetime,
370. @EnrollmentDate datetime
371. AS
372. INSERT INTO [Link] (LastName,
373. FirstName,
374. HireDate,
375. EnrollmentDate)
376. VALUES (@LastName,
377. @FirstName,
378. @HireDate,
379. @EnrollmentDate);
380. SELECT SCOPE_IDENTITY() as NewPersonID;
381. '
382. END
383. GO
384.
385. -- Create GetStudentGrades stored procedure.
386. IF NOT EXISTS (SELECT * FROM [Link]
387. WHERE object_id = OBJECT_ID(N'[dbo].
[GetStudentGrades]')
388. AND type in (N'P', N'PC'))
389. BEGIN
390. EXEC dbo.sp_executesql @statement = N'
391. CREATE PROCEDURE [dbo].[GetStudentGrades]
392. @StudentID int
393. AS
394. SELECT EnrollmentID, Grade, CourseID, StudentID FROM
[Link]
395. WHERE StudentID = @StudentID
396. '
397. END
398. GO
399.
400. -- Create GetDepartmentName stored procedure.
401. IF NOT EXISTS (SELECT * FROM [Link]
402. WHERE object_id = OBJECT_ID(N'[dbo].
[GetDepartmentName]')
403. AND type in (N'P', N'PC'))
404. BEGIN
405. EXEC dbo.sp_executesql @statement = N'
406. CREATE PROCEDURE [dbo].[GetDepartmentName]
407. @ID int,
408. @Name nvarchar(50) OUTPUT
409. AS
410. SELECT @Name = Name FROM Department
411. WHERE DepartmentID = @ID
412. '
413. END
414. GO
415.
416. -- Insert data into the Person table.
417. USE School
418. GO
419. SET IDENTITY_INSERT [Link] ON
420. GO
421. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
422. VALUES (1, 'Abercrombie', 'Kim', '1995-03-11', null);
423. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
424. VALUES (2, 'Barzdukas', 'Gytis', null, '2005-09-01');
425. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
426. VALUES (3, 'Justice', 'Peggy', null, '2001-09-01');
427. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
428. VALUES (4, 'Fakhouri', 'Fadi', '2002-08-06', null);
429. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
430. VALUES (5, 'Harui', 'Roger', '1998-07-01', null);
431. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
432. VALUES (6, 'Li', 'Yan', null, '2002-09-01');
433. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
434. VALUES (7, 'Norman', 'Laura', null, '2003-09-01');
435. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
436. VALUES (8, 'Olivotto', 'Nino', null, '2005-09-01');
437. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
438. VALUES (9, 'Tang', 'Wayne', null, '2005-09-01');
439. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
440. VALUES (10, 'Alonso', 'Meredith', null, '2002-09-01');
441. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
442. VALUES (11, 'Lopez', 'Sophia', null, '2004-09-01');
443. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
444. VALUES (12, 'Browning', 'Meredith', null, '2000-09-01');
445. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
446. VALUES (13, 'Anand', 'Arturo', null, '2003-09-01');
447. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
448. VALUES (14, 'Walker', 'Alexandra', null, '2000-09-01');
449. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
450. VALUES (15, 'Powell', 'Carson', null, '2004-09-01');
451. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
452. VALUES (16, 'Jai', 'Damien', null, '2001-09-01');
453. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
454. VALUES (17, 'Carlson', 'Robyn', null, '2005-09-01');
455. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
456. VALUES (18, 'Zheng', 'Roger', '2004-02-12', null);
457. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
458. VALUES (19, 'Bryant', 'Carson', null, '2001-09-01');
459. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
460. VALUES (20, 'Suarez', 'Robyn', null, '2004-09-01');
461. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
462. VALUES (21, 'Holt', 'Roger', null, '2004-09-01');
463. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
464. VALUES (22, 'Alexander', 'Carson', null, '2005-09-01');
465. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
466. VALUES (23, 'Morgan', 'Isaiah', null, '2001-09-01');
467. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
468. VALUES (24, 'Martin', 'Randall', null, '2005-09-01');
469. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
470. VALUES (25, 'Kapoor', 'Candace', '2001-01-15', null);
471. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
472. VALUES (26, 'Rogers', 'Cody', null, '2002-09-01');
473. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
474. VALUES (27, 'Serrano', 'Stacy', '1999-06-01', null);
475. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
476. VALUES (28, 'White', 'Anthony', null, '2001-09-01');
477. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
478. VALUES (29, 'Griffin', 'Rachel', null, '2004-09-01');
479. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
480. VALUES (30, 'Shan', 'Alicia', null, '2003-09-01');
481. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
482. VALUES (31, 'Stewart', 'Jasmine', '1997-10-12', null);
483. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
484. VALUES (32, 'Xu', 'Kristen', '2001-7-23', null);
485. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
486. VALUES (33, 'Gao', 'Erica', null, '2003-01-30');
487. INSERT INTO [Link] (PersonID, LastName, FirstName, HireDate,
EnrollmentDate)
488. VALUES (34, 'Van Houten', 'Roger', '2000-12-07', null);
489. GO
490. SET IDENTITY_INSERT [Link] OFF
491. GO
492.
493. -- Insert data into the Department table.
494. INSERT INTO [Link] (DepartmentID, [Name], Budget,
StartDate, Administrator)
495. VALUES (1, 'Engineering', 350000.00, '2007-09-01', 2);
496. INSERT INTO [Link] (DepartmentID, [Name], Budget,
StartDate, Administrator)
497. VALUES (2, 'English', 120000.00, '2007-09-01', 6);
498. INSERT INTO [Link] (DepartmentID, [Name], Budget,
StartDate, Administrator)
499. VALUES (4, 'Economics', 200000.00, '2007-09-01', 4);
500. INSERT INTO [Link] (DepartmentID, [Name], Budget,
StartDate, Administrator)
501. VALUES (7, 'Mathematics', 250000.00, '2007-09-01', 3);
502. GO
503.
504.
505. -- Insert data into the Course table.
506. INSERT INTO [Link] (CourseID, Title, Credits, DepartmentID)
507. VALUES (1050, 'Chemistry', 4, 1);
508. INSERT INTO [Link] (CourseID, Title, Credits, DepartmentID)
509. VALUES (1061, 'Physics', 4, 1);
510. INSERT INTO [Link] (CourseID, Title, Credits, DepartmentID)
511. VALUES (1045, 'Calculus', 4, 7);
512. INSERT INTO [Link] (CourseID, Title, Credits, DepartmentID)
513. VALUES (2030, 'Poetry', 2, 2);
514. INSERT INTO [Link] (CourseID, Title, Credits, DepartmentID)
515. VALUES (2021, 'Composition', 3, 2);
516. INSERT INTO [Link] (CourseID, Title, Credits, DepartmentID)
517. VALUES (2042, 'Literature', 4, 2);
518. INSERT INTO [Link] (CourseID, Title, Credits, DepartmentID)
519. VALUES (4022, 'Microeconomics', 3, 4);
520. INSERT INTO [Link] (CourseID, Title, Credits, DepartmentID)
521. VALUES (4041, 'Macroeconomics', 3, 4);
522. INSERT INTO [Link] (CourseID, Title, Credits, DepartmentID)
523. VALUES (4061, 'Quantitative', 2, 4);
524. INSERT INTO [Link] (CourseID, Title, Credits, DepartmentID)
525. VALUES (3141, 'Trigonometry', 4, 7);
526. GO
527.
528. -- Insert data into the OnlineCourse table.
529. INSERT INTO [Link] (CourseID, URL)
530. VALUES (2030, '[Link]
531. INSERT INTO [Link] (CourseID, URL)
532. VALUES (2021, '[Link]
533. INSERT INTO [Link] (CourseID, URL)
534. VALUES (4041, '[Link]
535. INSERT INTO [Link] (CourseID, URL)
536. VALUES (3141, '[Link]
537.
538. --Insert data into OnsiteCourse table.
539. INSERT INTO [Link] (CourseID, Location, Days, [Time])
540. VALUES (1050, '123 Smith', 'MTWH', '11:30');
541. INSERT INTO [Link] (CourseID, Location, Days, [Time])
542. VALUES (1061, '234 Smith', 'TWHF', '13:15');
543. INSERT INTO [Link] (CourseID, Location, Days, [Time])
544. VALUES (1045, '121 Smith','MWHF', '15:30');
545. INSERT INTO [Link] (CourseID, Location, Days, [Time])
546. VALUES (4061, '22 Williams', 'TH', '11:15');
547. INSERT INTO [Link] (CourseID, Location, Days, [Time])
548. VALUES (2042, '225 Adams', 'MTWH', '11:00');
549. INSERT INTO [Link] (CourseID, Location, Days, [Time])
550. VALUES (4022, '23 Williams', 'MWF', '9:00');
551.
552. -- Insert data into the CourseInstructor table.
553. INSERT INTO [Link](CourseID, PersonID)
554. VALUES (1050, 1);
555. INSERT INTO [Link](CourseID, PersonID)
556. VALUES (1061, 31);
557. INSERT INTO [Link](CourseID, PersonID)
558. VALUES (1045, 5);
559. INSERT INTO [Link](CourseID, PersonID)
560. VALUES (2030, 4);
561. INSERT INTO [Link](CourseID, PersonID)
562. VALUES (2021, 27);
563. INSERT INTO [Link](CourseID, PersonID)
564. VALUES (2042, 25);
565. INSERT INTO [Link](CourseID, PersonID)
566. VALUES (4022, 18);
567. INSERT INTO [Link](CourseID, PersonID)
568. VALUES (4041, 32);
569. INSERT INTO [Link](CourseID, PersonID)
570. VALUES (4061, 34);
571. GO
572.
573. --Insert data into the OfficeAssignment table.
574. INSERT INTO [Link](InstructorID, Location)
575. VALUES (1, '17 Smith');
576. INSERT INTO [Link](InstructorID, Location)
577. VALUES (4, '29 Adams');
578. INSERT INTO [Link](InstructorID, Location)
579. VALUES (5, '37 Williams');
580. INSERT INTO [Link](InstructorID, Location)
581. VALUES (18, '143 Smith');
582. INSERT INTO [Link](InstructorID, Location)
583. VALUES (25, '57 Adams');
584. INSERT INTO [Link](InstructorID, Location)
585. VALUES (27, '271 Williams');
586. INSERT INTO [Link](InstructorID, Location)
587. VALUES (31, '131 Smith');
588. INSERT INTO [Link](InstructorID, Location)
589. VALUES (32, '203 Williams');
590. INSERT INTO [Link](InstructorID, Location)
591. VALUES (34, '213 Smith');
592.
593. -- Insert data into the StudentGrade table.
594. INSERT INTO [Link] (CourseID, StudentID, Grade)
595. VALUES (2021, 2, 4);
596. INSERT INTO [Link] (CourseID, StudentID, Grade)
597. VALUES (2030, 2, 3.5);
598. INSERT INTO [Link] (CourseID, StudentID, Grade)
599. VALUES (2021, 3, 3);
600. INSERT INTO [Link] (CourseID, StudentID, Grade)
601. VALUES (2030, 3, 4);
602. INSERT INTO [Link] (CourseID, StudentID, Grade)
603. VALUES (2021, 6, 2.5);
604. INSERT INTO [Link] (CourseID, StudentID, Grade)
605. VALUES (2042, 6, 3.5);
606. INSERT INTO [Link] (CourseID, StudentID, Grade)
607. VALUES (2021, 7, 3.5);
608. INSERT INTO [Link] (CourseID, StudentID, Grade)
609. VALUES (2042, 7, 4);
610. INSERT INTO [Link] (CourseID, StudentID, Grade)
611. VALUES (2021, 8, 3);
612. INSERT INTO [Link] (CourseID, StudentID, Grade)
613. VALUES (2042, 8, 3);
614. INSERT INTO [Link] (CourseID, StudentID, Grade)
615. VALUES (4041, 9, 3.5);
616. INSERT INTO [Link] (CourseID, StudentID, Grade)
617. VALUES (4041, 10, null);
618. INSERT INTO [Link] (CourseID, StudentID, Grade)
619. VALUES (4041, 11, 2.5);
620. INSERT INTO [Link] (CourseID, StudentID, Grade)
621. VALUES (4041, 12, null);
622. INSERT INTO [Link] (CourseID, StudentID, Grade)
623. VALUES (4061, 12, null);
624. INSERT INTO [Link] (CourseID, StudentID, Grade)
625. VALUES (4022, 14, 3);
626. INSERT INTO [Link] (CourseID, StudentID, Grade)
627. VALUES (4022, 13, 4);
628. INSERT INTO [Link] (CourseID, StudentID, Grade)
629. VALUES (4061, 13, 4);
630. INSERT INTO [Link] (CourseID, StudentID, Grade)
631. VALUES (4041, 14, 3);
632. INSERT INTO [Link] (CourseID, StudentID, Grade)
633. VALUES (4022, 15, 2.5);
634. INSERT INTO [Link] (CourseID, StudentID, Grade)
635. VALUES (4022, 16, 2);
636. INSERT INTO [Link] (CourseID, StudentID, Grade)
637. VALUES (4022, 17, null);
638. INSERT INTO [Link] (CourseID, StudentID, Grade)
639. VALUES (4022, 19, 3.5);
640. INSERT INTO [Link] (CourseID, StudentID, Grade)
641. VALUES (4061, 20, 4);
642. INSERT INTO [Link] (CourseID, StudentID, Grade)
643. VALUES (4061, 21, 2);
644. INSERT INTO [Link] (CourseID, StudentID, Grade)
645. VALUES (4022, 22, 3);
646. INSERT INTO [Link] (CourseID, StudentID, Grade)
647. VALUES (4041, 22, 3.5);
648. INSERT INTO [Link] (CourseID, StudentID, Grade)
649. VALUES (4061, 22, 2.5);
650. INSERT INTO [Link] (CourseID, StudentID, Grade)
651. VALUES (4022, 23, 3);
652. INSERT INTO [Link] (CourseID, StudentID, Grade)
653. VALUES (1045, 23, 1.5);
654. INSERT INTO [Link] (CourseID, StudentID, Grade)
655. VALUES (1061, 24, 4);
656. INSERT INTO [Link] (CourseID, StudentID, Grade)
657. VALUES (1061, 25, 3);
658. INSERT INTO [Link] (CourseID, StudentID, Grade)
659. VALUES (1050, 26, 3.5);
660. INSERT INTO [Link] (CourseID, StudentID, Grade)
661. VALUES (1061, 26, 3);
662. INSERT INTO [Link] (CourseID, StudentID, Grade)
663. VALUES (1061, 27, 3);
664. INSERT INTO [Link] (CourseID, StudentID, Grade)
665. VALUES (1045, 28, 2.5);
666. INSERT INTO [Link] (CourseID, StudentID, Grade)
667. VALUES (1050, 28, 3.5);
668. INSERT INTO [Link] (CourseID, StudentID, Grade)
669. VALUES (1061, 29, 4);
670. INSERT INTO [Link] (CourseID, StudentID, Grade)
671. VALUES (1050, 30, 3.5);
672. INSERT INTO [Link] (CourseID, StudentID, Grade)
673. VALUES (1061, 30, 4);
674. GO
675.
To review tables in the School database
1. On the File menu, click Connect Object Explorer.
2. In the Connect to Database Engine dialog box, type either localhost or the name of
the local SQL Server instance, and then click Connect.
3. In the Object Explorer window, expand the node for the newly connected instance,
then expand Databases, expand School, expand Tables, and review the list of table
objects in the database.
The following shows a diagram of the School database schema.
Next Steps
To create the Course Manager application by using Visual
Studio
1. On the File menu, click New Project.
2. Choose either Visual Basic or Visual C# in the Project Types pane.
3. Select Windows Forms Application in the Templates pane.
4. Enter CourseManager for the project name, and then click OK.
To create the Course Viewer form
1. In the CourseManager project, select the default form (Form1).
2. In the File Properties pane, change the File Name to [Link] or
[Link].
3. In Solution Explorer, double-click [Link] or [Link] to open
the form.
4. In the Properties pane, change the Name property to CourseViewer, and change the
Text property to Course Viewer.
5. In the Toolbox, expand Common Controls, drag the ComboBox control to the form,
and change the name of the control to departmentList.
6. In the Toolbox, drag the Button control to the form, change the name of the control
to closeForm, and change the Text value to Close.
7. In the Toolbox, expand Data, drag the DataGridView control to the form, and
change the name of the control to courseGridView.
8. Double-click the closeForm button control.
This opens the code page for the form and creates the closeForm_Click event handler
method.
9. In the closeForm_Click event handler method, type the following code that closes the
form:
C#
VB
//Close the form
[Link]();
Generating the School .edmx File
Visual Studio 2010
Other Versions
In this task, you will learn to use the Entity Data Model Wizard to generate an .edmx file,
which contains a conceptual model, a storage model, and mapping information. This file
defines a set of 1:1 entity-to-table mappings for the School conceptual model and database.
The School conceptual model is used throughout the Entity Framework getting started
documentation.
To add the [Link] Entity Data Model item template
1. Select the CourseManager project in Solution Explorer, right-click, point to Add,
and then click New Item.
2. Select [Link] Entity Data Model in the Templates pane.
3. Type [Link] for the model name and click Add.
The opening page of the Entity Data Model Wizard is displayed.
To generate the .edmx file
1. Select Generate from database in the Choose Model Contents dialog box. Then
click Next.
2. Click the New Connection button.
3. In the Choose Data Source dialog box, select your data source and then click
Continue.
4. In the Connection Properties dialog box, enter your server name, select the
authentication method, type School for the database name, and then click OK.
The Choose Your Data Connections dialog box is updated with your database
connection settings.
5. Ensure that Save entity connection settings in [Link] as: is checked and the
value is set to SchoolEntities. Then click Next.
The Choose Your Database Objects dialog box is displayed.
6. Select all tables and stored procedures and ensure that the value of Model namespace
is SchoolModel.
Note:
The stored procedures in the School model are required by many of the topics in the Entity
Data Model Tools Scenarios. These walkthroughs start with the CourseManager application
and School model created in this quickstart.
7. Ensure that Pluralize or singularize generated object names and Include foreign
key columns in the model are selected.
The wizard performs the following actions:
o Adds references to the [Link], [Link], and
[Link] assemblies.
o Generates the [Link] file that defines the conceptual model, the storage
model, and the mapping between them.
o Creates an object-layer code file that contains the classes that were generated
based on the conceptual model. You can view the object-layer code by expanding
the .edmx file node in Solution Explorer.
Note:
In Visual Basic projects, you must press the Show All Files button in the Solution Explorer
before you can view the source code.
o Creates an [Link] file.
To view the .edmx file in the [Link] Entity Data Model
Designer
1. In the Solution Explorer, double-click the [Link] file.
This displays the School model in the [Link] Entity Data Model Designer
window, as shown in the following diagram.
1. From the View menu, select Other Windows, and then click Model Browser.
This displays the Entity Model Browser window.
2. Expand the SchoolModel and [Link] nodes to view conceptual and storage
content, respectively.
3. From the View menu, select Other Windows, click Mapping Details, and then click an entity
or association in the Entity Designer.
This displays the Entity Mapping Details window with information about the conceptual-
storage mapping for the selected object.
Next Steps
You have successfully generated the conceptual, storage, and mapping content for the School
model in the CourseManager project. Next, you will create queries against CLR objects that
represent entities and associations in the School model and bind the results of those queries to
controls:
Querying Entities and Associations
Visual Studio 2010
Other Versions
In this task, you will create strongly-typed queries against the CLR objects that represent
entities and associations in the School model, and bind display controls to the object
collections returned from these queries.
To query the departments in the School database
1. At the beginning of the code file for the CourseViewer form, add the following using
(C#) or Imports (Visual Basic) statements to reference the model created from the
School database and the entity namespace.
C#
VB
Imports [Link]
Imports [Link]
2. At the top of the partial class definition for the CourseViewer form, add the
following code that creates an ObjectContext instance.
C#
VB
' Create an ObjectContext instance based on SchoolEntity.
Private schoolContext As SchoolEntities
3. In the CourseViewer form designer, double-click the CourseViewer form.
This opens the code page for the form and creates the courseViewer _Load event
handler method.
4. In the courseViewer _Load event handler method, copy and paste the following code
that defines the DataGridView, executes a query that returns a collection of
departments (ordered by Name), and binds the collection of Department objects to
the departmentList control.
C#
VB
' Initialize the ObjectContext.
schoolContext = New SchoolEntities()
' Define a query that returns all Department objects
' and related Course objects, ordered by name.
Dim departmentQuery As ObjectQuery(Of Department) = _
From d In [Link]("Courses") _
Order By [Link] _
Select d
Try
' Bind the ComboBox control to the query.
' To prevent the query from being executed multiple times during
binding,
' it is recommended to bind controls to the result of the Execute
method.
[Link] = "Name"
[Link] = CType(departmentQuery,
ObjectQuery).Execute([Link])
Catch ex As Exception
[Link]([Link])
End Try
To display courses for the selected department
1. In the CourseViewer form designer, double-click the departmentList control.
This creates the departmentList_SelectedIndexChanged event handler method.
2. Paste the following code that loads the courses that are related to the selected
department.
C#
VB
Try
' Get the object for the selected department.
Dim department As Department = _
CType([Link], Department)
' Bind the grid view to the collection of Course objects
' that are related to the selected Department object.
[Link] = [Link]
' Hide the columns that are bound to the navigation properties on
Course.
[Link]("Department").Visible = False
[Link]("StudentGrades").Visible = False
[Link]("OnlineCourse").Visible = False
[Link]("OnsiteCourse").Visible = False
[Link]("People").Visible = False
[Link]("DepartmentId").Visible = False
[Link] = False
[Link]([Link]
ells)
Catch ex As Exception
[Link]([Link])
End Try
Next Steps
You have successfully created queries that return Department and Course objects and bound
those objects to controls. Next, you will save changes made to Course objects in the data grid
back to the database: Inserting and Updating Data.
Inserting and Updating Data
Visual Studio 2010
Other Versions
This is the final task of the Entity Framework Quickstart. In this task, you will save changes
made to Course objects bound to the DataGridView control to the database. You will also
run the completed Course Manager application.
To save changes made to objects
1. In the Toolbox, expand Common Controls, drag the Button control to the
CourseViewer form designer, change the name of the control to saveChanges, and
change the Text value to Update.
2. In the CourseViewer form designer, double-click the saveChanges control.
This creates the saveChanges_Click event handler method.
3. Paste the following code that saves object changes to the database.
C#
VB
Try
' Save object changes to the database,
' display a message, and refresh the form.
[Link]()
[Link]("Changes saved to the database.")
[Link]()
Catch ex As Exception
[Link]([Link])
End Try
To close connections by disposing the long-running object
context
In the closeForm_Click event handler method, type the following code. This code
disposes of the object context before the form is closed.
C#
VB
' Dispose the object context.
[Link]()
To build and run the Class Scheduling application
1. From the Debug menu, select Start Debugging or Start Without Debugging.
This builds and starts the application.
2. When the form loads, select a department from the ComboBox control.
This displays the courses that belong to that department.
3. In the DataGridView, update course information or add a new course and then click
Update.
This saves changes to the database and displays a message box to indicate that
changes have been saved.
Next Steps
You have successfully created and run the Course Manager application. You have also
completed this Entity Framework quickstart.