IT工程師數位筆記本

USE master
GO 
IF EXISTS(SELECT * FROM sys.sysdatabases WHERE name='SchoolDB')
DROP DATABASE SchoolDB;
GO 
CREATE DATABASE SchoolDB
GO 
USE SchoolDB;

GO 
--創建Standard表
IF EXISTS (SELECT * FROM sysobjects WHERE name='Standard')
DROP TABLE [Standard];
GO 
CREATE TABLE  [Standard]
(
StandardID INT PRIMARY KEY ,
StandardName NVARCHAR(50),
[Description] NVARCHAR(250)
);
 
GO 

--創建Student表
IF EXISTS (SELECT * FROM sysobjects WHERE name='Student')
DROP TABLE Student;
GO 
CREATE TABLE  Student
(
StudentID INT PRIMARY KEY,
StudentName NVARCHAR(100) NOT NULL,
StandardID INT  REFERENCES [Standard](StandardID),
[RowVersion] NVARCHAR(50)

);

GO 

--創建StudentAddress表
IF EXISTS (SELECT * FROM sysobjects WHERE name='StudentAddress')
DROP TABLE StudentAddress;
GO 
CREATE TABLE  StudentAddress
(
StudentID INT PRIMARY KEY,
Address1 NVARCHAR(100) ,
Address2 NVARCHAR(100), 
City NVARCHAR(100),
[State] NVARCHAR(100),
CONSTRAINT StudentID_FK FOREIGN KEY(StudentID) REFERENCES dbo.Student(StudentID)
);

GO 

--創建Teacher表
IF EXISTS (SELECT * FROM sysobjects WHERE name='Teacher')
DROP TABLE Teacher;
GO 
CREATE TABLE  Teacher
(
TeacherID INT PRIMARY KEY ,
TeacherName NVARCHAR(50),
StandardID INT  REFERENCES [Standard](StandardID),
TeacherType NVARCHAR (100)

);
GO 


--創建Course表
IF EXISTS (SELECT * FROM sysobjects WHERE name='Course')
DROP TABLE Course;
GO 
CREATE TABLE  Course
(
CourseID INT PRIMARY KEY ,
CourseName NVARCHAR(50),
Location NVARCHAR(500),
TeacherID INT REFERENCES dbo.Teacher(TeacherID)

);

GO 


--創建StudentCourse表
IF EXISTS (SELECT * FROM sysobjects WHERE name='StudentCourse')
DROP TABLE StudentCourse;
GO 
CREATE TABLE  StudentCourse
(
StudentID INT  REFERENCES Student(StudentID),
CourseID INT  REFERENCES dbo.Course(CourseID),
CONSTRAINT StudentID_CourseID PRIMARY KEY(StudentID,CourseID),

);

GO