優化MySchool 數據庫設計 第二章上機


上機練習1

USE master
GO
IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'MySchool')
DROP DATABASE MySchool
GO

CREATE DATABASE MySchool
ON
(
NAME = ' MySchool_data', --主數據文件的邏輯名
FILENAME = 'D:\project\MySchool_data.mdf' , --主數據文件的物理名
SIZE = 10 MB, --主數據文件初始大小
FILEGROWTH = 20 %
)
LOG ON
(
NAME = 'MySchool_log',
FILENAME = 'D:\project\MySchool_log.ldf' ,
SIZE = 3MB,
MAXSIZE = 20MB,
FILEGROWTH = 1MB
)
GO

上機練習2
USE MySchool
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name='Subject' )
DROP TABLE Subject
GO
CREATE TABLE Subject --創建課程表
(
SubjectNo int IDENTITY(1,1) NOT NULL,
SubjectName nchar(50) NOT NULL,
ClassHour int NOT NULL,
GradeId int NOT NULL
)
GO

上機練習3
USE MySchool
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name=' Result' )
DROP TABLE Result
GO
CREATE TABLE Result --創建成績表
(
StudentNo int NOT NULL,
SubjectNo int NOT NULL,
StudentResult int NOT NULL,
ExamDate datetime NOT NULL
)
GO

上機練習4
USE MySchool
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='Student')
DROP TABLE Student
CREATE TABLE [dbo].[Student](
[StudentNo] [int] NOT NULL,
[LoginPwd] [nvarchar](50) NOT NULL,
[StudentName] [nvarchar](50) NOT NULL,
[Sex] bit NOT NULL,
[GradeId] [int] NOT NULL,
[Phone] [nvarchar](50) NULL,
[Address] [nvarchar](255) NULL,
[BornDate] [datetime] NOT NULL,
[Email] [nvarchar](50) NULL,
[IdentityCard] [varchar](18) NOT NULL
)

IF EXISTS(SELECT * FROM sysobjects WHERE name='Grade')
DROP TABLE Grade
CREATE TABLE [dbo].[Grade](
[GradeId] [int] IDENTITY(1,1) NOT NULL,
[GradeName] [nvarchar](50) NOT NULL
)

上機練習5
USE MySchool
GO
ALTER TABLE Subject --主鍵約束(課程編號)
ADD CONSTRAINT PK_Subject PRIMARY KEY (SubjectNo)

ALTER TABLE Subject --非空約束(課程名稱)
ADD CONSTRAINT CK_SubjectName CHECK (SubjectName is not null)

ALTER TABLE Subject WITH NOCHECK --檢查約束(學時必須大於等於0)
ADD CONSTRAINT CK_ClassHour CHECK (ClassHour>=0)

ALTER TABLE Subject --外鍵約束(主表Grade和從表Subject建立引用關系)
ADD CONSTRAINT FK_GradeId
FOREIGN KEY (GradeId) REFERENCES Grade (GradeId)
GO

上機練習6
USE MySchool
GO
ALTER TABLE Result --主鍵約束(學號、科目號、日期)
ADD CONSTRAINT PK_Result PRIMARY KEY
(StudentNo, SubjectNo, ExamDate)

ALTER TABLE Result --默認約束(日期為系統當前日期)
ADD CONSTRAINT CK_ExamDate DEFAULT (getdate()) FOR ExamDate

ALTER TABLE Result --檢查約束(分數不能大於100,小於0)
ADD CONSTRAINT CK_StudentResult CHECK
(StudentResult BETWEEN 0 AND 100)

ALTER TABLE Result --外鍵約束(主表Student和從表Result建立關系)
ADD CONSTRAINT FK_StudentNo
FOREIGN KEY (StudentNo) REFERENCES Student (StudentNo)

ALTER TABLE Result --外鍵約束(主表Subject和從表Result建立關系)
ADD CONSTRAINT FK_SubjectNo
FOREIGN KEY (SubjectNo) REFERENCES Subject (SubjectNo)
GO

上機練習7
USE MySchool
GO
ALTER TABLE Result -- 刪除檢查約束
DROP CONSTRAINT ck_studentResult

ALTER TABLE Result -- 修改成績表中成績列的數據類型
ALTER COLUMN StudentResult DECIMAL (5, 2)

ALTER TABLE Result -- 添加檢查約束
ADD Constraint ck_studentResult CHECK
(studentresult BETWEEN 0 AND 100)
GO

0 個評論

要回覆文章請先登錄註冊