实验6实验报告

FILEGROUP UGroup1 (NAME=G1File1, FILENAME='C:\Teaching\G1File1Data.ndf', SIZE=2MB, MAXSIZE=50MB,
1
FILEGROWTH=1MB), (NAME=G1File2, FILENAME='C:\Teaching\G1File2Data.ndf', SIZE=2MB, MAXSIZE=50MB, FILEGROWTH=1MB),
NOT NULL
)
GO
IDENTITY(1,1) PRIMARY
--课程表 Courses
USE TeachingDB
GO
CREATE TABLE Courses
(courseIDint
IDENTITY
courseName nvarchar(50) NOT NULL,
credit tinyint NOT NULL
GO
--创建表 Studying 与 Students 之间的关系 ALTER TABLE Studying
ADD CONSTRAINT Studying_StdID_fk FOREIGN KEY(stdID) REFERENCES Students(stdID)
GO
--创建表 Studying 与 Courses 之间的关系 ALTER TABLE Studying
--向表 Classes 中导入数据 USE TeachingDB GO INSERT INTO Classes VALUES('I071','07 信息管理技术 1 班', 'INDE') GO INSERT INTO Classes VALUES('I081','08 信息管理技术 1 班', 'INDE') GO INSERT INTO Classes VALUES('S081','08 软件技术 1 班', 'INDE') GO INSERT INTO Classes VALUES('A081','08 应用电子 1 班', 'ELDE') GO
--向表 Courses 中导入数据 USE TeachingDB GO INSERT INTO Courses VALUES('数据库原理与应用',3) GO INSERT INTO Courses VALUES('可视化编程设计',4) GO INSERT INTO Courses VALUES('管理信息系统开发',2) GO INSERT INTO Courses VALUES('Java 语言程序设计',3) GO INSERT INTO Courses VALUES('面向对象分析与设计',3) GO
--表 Departments，用于存放图书的类别信息。
USE TeachingDB GO CREATE TABLE Departments (dptCode nchar(4) dptNamenvarchar(50) NOT NULL ) GO
--表 Classes，用于存放班级信息。源自USE TeachingDB
--向表 Students 中导入数据 USE TeachingDB GO INSERT INTO Students VALUES('林琳', '1989-10-15', '女', 'I071', 'INDE') GO INSERT INTO Students VALUES('王志峰', '1990-1-9', '男', 'I071', 'INDE') GO INSERT INTO Students VALUES('李利', '1990-12-27', '女', 'I081', 'INDE') GO INSERT INTO Students VALUES('魏薇', '1990-11-3', '女', 'I081', 'INDE') GO INSERT INTO Students VALUES('韩鹏', '1991-3-5', '男', 'I081', 'INDE') GO INSERT INTO Students VALUES('焦小强', '1991-5-20', '男', 'S081', 'INDE') GO INSERT INTO Students VALUES('王小悦', '1990-9-29', '女', 'S081', 'INDE') GO INSERT INTO Students VALUES('张志民', '1990-11-11', '男', 'A081', 'ELDE') GO INSERT INTO Students VALUES('李想', '1991-3-19', '男', 'A081', 'ELDE') GO INSERT INTO Students VALUES('刘艳', '1990-12-12', '女',NULL, 'INDE') GO
)
GO
PRIMARY KEY,
--定义表 Studying
USE TeachingDB
GO
CREATE TABLE Studying
(stdID
int
NOT NULL,
courseID int
NOT NULL,
tchID
int
NOT NULL,
semester nchar(12) NOT NULL,
--向表 Teachers 中导入数据 USE TeachingDB GO INSERT INTO Teachers VALUES('刘翔', '讲师', 'INDE ') GO INSERT INTO Teachers VALUES('王晓燕', '副教授', 'INDE')
5
GO INSERT INTO Teachers VALUES('高威', '副教授', 'ELDE') GO INSERT INTO Teachers VALUES('李海朋', '讲师', 'MADE') GO
GO
--向表 Departments 中导入数据 USE TeachingDB GO INSERT INTO Departments VALUES('INDE','信息工程系') GO
4
INSERT INTO Departments VALUES('ELDE','电气工程系') GO INSERT INTO Departments VALUES('MADE','机械工程系') GO
ADD CONSTRAINT Students_dptCode_fk FOREIGN KEY(dptCode) REFERENCES Departments(dptCode)
GO
--创建表 Teachers 与 Departments 之间的关系 ALTER TABLE Teachers
ADD CONSTRAINT Teachers_dptCode_fk FOREIGN KEY(dptCode) REFERENCES Departments(dptCode)
dptCode nchar(4)
)
GO
--表 Teachers，用于存放教师信息。
USE TeachingDB
GO
CREATE TABLE Teachers
(tchID
int
KEY,
tchName NameChar NOT NULL,
proTitle nvarchar(20),
dptCode nchar(4)
GO
--创建表 Students 与 Classes 之间的关系 ALTER TABLE Students
ADD CONSTRAINT Sutdents_classCode_fk FOREIGN KEY(classCode) REFERENCES Classes(classCode)
GO
--创建表 Students 与 Departments 之间的关系 ALTER TABLE Students
--创建数据库 TeachingDB USE master GO IF EXISTS(SELECT * FROM sys.databases WHERE name='TeachingDB')
DROP DATABASE TeachingDB GO CREATE DATABASE TeachingDB ON PRIMARY (NAME=PFile1, FILENAME='C:\Teaching\PFile1Data.mdf', SIZE=4MB, MAXSIZE=50MB, FILEGROWTH=1MB),