姓名：专业：班级：学号：科目：数据库系统原理实验日期：实验题目：实验1 SQL SERVER 的安装及使用，数据库的建立MAXSIZE=50,FILEGROWTH=5)LOG ON(NAME='Students_Log',FILENAME='E:\ SQL_DATEBASE \Students_Mis_log.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)GO（2）调用（USE）数据库：Students_Mis_2018use Students_Mis_2018GO（3）分别建立4个数据表的表结构(CREATE TABLE):系（Depts），学生（Students）,课程（Courses）,选课（Reports）CREATE TABLE Depts(Dno CHAR(5) PRIMARY KEY,Dname CHAR(20) NOT NULL)GOCREATE TABLE Students(Sno CHAR(5) PRIMARY KEY,Sname CHAR(20) NOT NULL,Ssex CHAR(2),Sage INT,Dno CHAR(5),CONSTRAINT FK_Dno FOREIGN KEY(Dno) REFERENCES Depts )GOCREATE TABLE Courses(Cno CHAR(6) PRIMARY KEY,Cname CHAR(20),Pre_Cno CHAR(6),Credits INT)GOCREATE TABLE Reports(Sno CHAR(5),Cno CHAR(6),Grade INT CHECK(Grade>=0 AND Grade<=100),PRIMARY KEY(Sno,Cno),CONSTRAINT Student_Report FOREIGN KEY(Sno) REFERENCES Students, CONSTRAINT Report_Course FOREIGN KEY(Cno) REFERENCES Courses)GO（4）分别对4个表插入表数据（INSERT）INSERTINTO deptsVALUES ('D01','自动化')INSERTINTO deptsVALUES ('D02','计算机')INSERTINTO deptsVALUES ('D03','数学')INSERTINTO deptsVALUES ('D04','通信')INSERTINTO deptsVALUES ('D05','电子')INSERTINTO deptsVALUES ('D06','化学')SELECT *FROM DeptsINSERTINTO StudentsVALUES ('S01','王建平','男',21,'D01') INSERTINTO StudentsVALUES ('S02','刘华','女',19,'D01') INSERTINTO StudentsVALUES ('S03','范林军','女',18,'D02') INSERTINTO StudentsVALUES ('S04','李伟','男',19,'D03') INSERTINTO StudentsVALUES ('S05','黄河','男',18,'D03') INSERTINTO StudentsVALUES ('S06','长江','男',20,'D03') SELECT *FROM StudentsINSERTINTO CoursesVALUES ('C01','英语','',4) INSERTINTO CoursesVALUES ('C02','数据结构','C05',2) INSERTINTO CoursesVALUES ('C03','数据库','C02',2) INSERTINTO CoursesVALUES ('C04','DB-设计','C03',3) INSERTINTO CoursesVALUES ('C05','C++','',3)INSERTINTO CoursesVALUES ('C06','网络原理','C07',3) INSERTINTO CoursesVALUES ('C07','操作系统','C05',3) SELECT *FROM CoursesINSERTINTO ReportsVALUES ('S01','C01','92')INSERTINTO ReportsVALUES ('S01','C03','84')INTO ReportsVALUES ('S02','C01','90')INSERTINTO ReportsVALUES ('S02','C02','94')INSERTINTO ReportsVALUES ('S02','C03','82')INSERTINTO ReportsVALUES ('S03','C01','72')INSERTINTO ReportsVALUES ('S03','C02','89')INSERTINTO ReportsVALUES ('S04','C03','75')SELECT *FROM Reports（5）数据类型及完整性约束验证:（A）若先建立学生（Students）表，后建立系（Depts）表，行吗？为什么？答：不行，对Students的Dno作外键约束，必须引用到Depts表的主键Dno。
必须先建立系（Depts）表,后才能建立学生（Students）表。
（B）若先建立选课（Reports）表，后建立课程（Courses）表，行吗？为什么？答：不行，Reports的Cno作外键约束外键，必须引用到Courses表的主键Cno，应该先建课程表，然后再才能对表Report的Cno作外键约束。
（C）对INSERT，自己举几个违反约束条件（数据类型、主键约束、非空约束、外键约束、CHECK 约束）的例子，并说明理由。
①数据类型FILEGROWTH=5)LOG ON(NAME='student_Log',FILENAME='E:\ SQL_DATABASE \SPJ_Mis_log.ldf', SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)GO（2）调用数据库use SPJ_Mis_2018GO（3）分别建立4个数据表的表结构CREATE TABLE S(Sno CHAR(5) PRIMARY KEY,Sname CHAR(20) NOT NULL ,Status INT,SCity CHAR(20))GOCREATE TABLE P(Pno CHAR(6) PRIMARY KEY,Pname CHAR(20) ,Color CHAR(9),Weight FLOAT)GOCREATE TABLE J(Jno CHAR(5) PRIMARY KEY,Jname CHAR(20) ,JCity CHAR(20))GOCREATE TABLE SPJ(Sno CHAR(5),Pno CHAR(6),Jno CHAR(5),QTY INT CHECK(QTY>=0),PRIMARY KEY(Sno,Pno,Jno),CONSTRAINT S_SPJ FOREIGN KEY(Sno) REFERENCES S, CONSTRAINT P_SPJ FOREIGN KEY(Pno) REFERENCES P, CONSTRAINT J_SPJ FOREIGN KEY(Jno) REFERENCES J )GO（4）插入数据INTO SVALUES('S1','利群',30,'广州'), ('S2','同方',20,'杭州'), ('S3','天远',60,'北京'), ('S4','精诚',10,'上海'), ('S5','华缘',80,'重庆'), ('S6','弘治',50,'太原') SELECT *FROM SINSERTINTO PVALUES('P1','钉子','绿',34),('P2','螺丝','蓝',25),('P3','螺母','橙',12),('P4','螺栓','紫',27),('P5','螺钉','红',53),('P6','齿轮','绿',17),('P7','传送带','红',28) SELECT *FROM PINSERTINTO JVALUES('J1','一建','济南'),('J2','三汽','广州'),('J3','拉链厂','杭州'), ('J4','无线电厂','北京'), ('J5','机床厂','上海'), ('J6','螺钉厂','重庆'), ('J7','机械厂','天津')SELECT *INSERTINTO SPJVALUES('S1','P1','j3',340), ('S1','P1','j2',250), ('S1','P2','j5',120), ('S1','P2','j6',270), ('S1','P2','j7',530) INSERTINTO SPJVALUES('S2','P2','j1',170), ('S2','P3','j2',280), ('S2','P4','j3',100), ('S2','P4','j4',120), ('S2','P5','j7',310), ('S2','P5','j5',560), ('S2','P6','j6',200) INSERTINTO SPJVALUES('S3','P7','j1',300), ('S3','P1','j1',400), ('S4','P3','j2',410), ('S4','P4','j3',330), ('S4','P4','j4',650), ('S4','P5','j4',150) INSERTINTO SPJVALUES('S5','P7','j5',230), ('S5','P7','j7',280), ('S6','P2','j2',350), ('S6','P2','j3',420), ('S6','P6','j1',310) SELECT *FROM SPJ【实验结果及分析】【教师评语和成绩】成绩：指导教师：日期：感谢下载!欢迎您的下载，资料仅供参考。