Chapter I establishment of database
Tip: you can add the directories of all articles in the series here. You need to add the directories manually
For example: Chapter 1 Introduction to Python machine learning - use of pandas
Tip: after writing an article, the directory can be automatically generated. For how to generate it, please refer to the help document on the right
preface
SQL commands used: CREATE DATABASE, USE, CREATE TABLE, INSERT, etc.
[experiment content]
1,
Examples of the first chapter of the textbook (Students_Mis_2020): 4 data tables)
Department table: Depts student table: Students
Course schedule: courses course schedule: Reports
(1) Create database:Students_Mis_2020 (the stored files are stored in the folder established by yourself)
(2) Call (USE) database: Students_ Mis_ 2020 USE Students_ Mis_ two thousand and twenty
go
(3) Create table structures for four data tables: departments, Students, Courses, and Reports
(4) INSERT table data (INSERT)
create table Depts(
Dno char(5) primary key,
Dname char(20) not null)
insert
into Depts(Dno,Dname) values('D01','automation')
insert
into Depts(Dno,Dname) values('D02','computer')
insert
into Depts(Dno,Dname) values('D03','mathematics')
insert
into Depts(Dno,Dname) values('D04','signal communication')
insert
into Depts(Dno,Dname) values('D05','Electronics')
select*from Depts;
create table Students(
Sno char(5) primary key,
Sname char(20) not null,
Ssex char(2),
Brithday Date,
Dno char(5),
constraint FK_Dno foreign key(Dno) references Depts)
insert
into Students(Sno,Sname,Ssex,Brithday,Dno) values('S01','Wang Jianping','male','1995-10-12','D01')
insert
into Students(Sno,Sname,Ssex,Brithday,Dno) values('S02','Liu Hua','female','1997-08-21','D02')
insert
into Students(Sno,Sname,Ssex,Brithday,Dno) values('S03','Fan Jinjun','female','1998-02-11','D03')
insert
into Students(Sno,Sname,Ssex,Brithday,Dno) values('S04','Li Wei','male','1996-12-22','D04')
insert
into Students(Sno,Sname,Ssex,Brithday,Dno) values('S05','Yellow River','male','1999-10-31','D05')
insert
into Students(Sno,Sname,Ssex,Brithday,Dno) values('S06','Yangtze River','male','1994-04-08','D06')
select*from Students;
create table Courses(
Cno char(6) primary key,
Cname char(20) not null,
Pre_Cno char(6),
Credit int)
insert
into Courses(Cno,Cname,Pre_Cno,Credit) values('C01','English',' ','4')
insert
into Courses(Cno,Cname,Pre_Cno,Credit) values('C02','data structure','C05','2')
insert
into Courses(Cno,Cname,Pre_Cno,Credit) values('C03','database','C02 ','2')
insert
into Courses(Cno,Cname,Pre_Cno,Credit) values('C04','DB_Design','C03','3')
insert
into Courses(Cno,Cname,Pre_Cno,Credit) values('C05','C++',' ','3')
insert
into Courses(Cno,Cname,Pre_Cno,Credit) values('C06','Network principle','C07 ','3')
insert
into Courses(Cno,Cname,Pre_Cno,Credit) values('C07','operating system','C05','3')
select*from Courses;
create 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)
insert
into Reports(Sno,Cno,Grade) values('S01','C01','92')
insert
into Reports(Sno,Cno,Grade) values('S02','C02','84')
insert
into Reports(Sno,Cno,Grade) values('S03','C03','90')
insert
into Reports(Sno,Cno,Grade) values('S04','C04','94')
insert
into Reports(Sno,Cno,Grade) values('S05','C05','82')
insert
into Reports(Sno,Cno,Grade) values('S06','C06','72')
insert
into Reports(Sno,Cno,Grade) values('S07','C07','90')
insert
into Reports(Sno,Cno,Grade) values('S08','C08','75')
select*from Reports;
(5) Data type and integrity constraint verification (the difficulty is the primary foreign key constraint)
Answer the following questions:
(A) Can I create the Students table first and then the Depts table? Why?
A: No, the foreign key constraint is applied to the Dno of Students. The primary key Dno of the Depts table must be referenced. The relationship table must be established before the Students table can be established.
(B) Can I create a Reports table first and then a Courses table? Why?
A: No, the foreign key constraint on Reports' Cno must refer to the primary key Cno of the Courses table. The Courses table must be established before the Reports table can be established.
(C) For INSERT, give several examples of violating constraints (data type, primary key constraint, non empty constraint, foreign key constraint, and CHECK constraint) and explain the reasons.
1. Data type:
insert
Into students (SnO, sname, ssex, brightday, dno) values ('S01 ',' wangjianping ',' male ',' October 12, 1995 ',' D01 ')
Reason: brightday is a Date type, and the inserted string type causes insert failure.
2. Primary key constraint: primary key cannot be empty.
insert
Into students (SnO, sname, ssex, brightday, dno) values (null, 'Wang Jianping', 'male', '1995-10-12', 'D01')
3. Non empty constraint:
Primary keys cannot be duplicate.
4. Foreign key constraints:
C09 does not belong to the Report table, so the insert failed.
insert
into Reports(Sno,Cno,Grade) values('S01','C09','92')
5. CHECK constraint:
The Check constraint judges the validity of data through logical expressions to limit the range of values entered in one or more columns. When updating data in a column, the content to be entered must meet the conditions of the Check constraint, otherwise it will not be entered correctly.
Grade int check(Grade>=0 and Grade<=100)
(6) Detach database, copy database storage file, attach database detach database:
Copy database storage files:
copy
Copy them to a U SB flash drive so that they can be moved to another machine.
Additional databases:
Click the Add button to find the mdf file of the database you want to attach from the file directory, select it and click OK:
Click OK. If an additional database appears in the object explorer, the attachment is successful.
(7) Save the debugged correct command code and database storage file to your own backup disk (convenient for the next experiment)
2. Exercise 1.3 in the textbook (parts supply database (spj\mis\u 2020):4 data tables)
(Note: the requirements are the same as above)
Supplier table: P parts table: P
Project item list: J parts supply list
[experiment process]
use SPJ_Mis__2020
go
create table S(
Sno char(5)primary key,
Sname char(20)not null,
Status int,
City char(20),
)
create table P(
Pno char(5)primary key,
Pname char(20)not null,
Color char(5),
Weight int
)
create table J(
Jno char(5)primary key,
Jname char(20)not null,
City char(20),
)
create table SPJ(
Sno char(5),
Pno char(5),
Jno char(5),
Qty int
constraint S_SPJ foreign key(Sno) references S,
constraint P_SPJ foreign key(Pno) references P,
constraint J_SPJ foreign key(Jno) references J)
insert into S(Sno,Sname,Status,City) values('S1','Liqun','30','Guangzhou')
insert into S(Sno,Sname,Status,City) values('S2','Tongfang','20','Hangzhou')
insert into S(Sno,Sname,Status,City) values('S3','Tianyuan','60','Beijing')
insert into S(Sno,Sname,Status,City) values('S4','Sincerity','10','Shanghai')
insert into S(Sno,Sname,Status,City) values('S5','Huayuan','80','Chongqing')
insert into S(Sno,Sname,Status,City) values('S6','Hongzhi','50','Taiyuan')
select*from S;
insert into P(Pno,Pname,Color,Weight) values('P1','nail','green','34')
insert into P(Pno,Pname,Color,Weight) values('P2','Screw','blue','25')
insert into P(Pno,Pname,Color,Weight) values('P3','Nut','orange','12')
insert into P(Pno,Pname,Color,Weight) values('P4','Bolt','purple','27')
insert into P(Pno,Pname,Color,Weight) values('P5','screw','red','53')
insert into P(Pno,Pname,Color,Weight) values('P6','gear','green','17')
insert into P(Pno,Pname,Color,Weight) values('P7','Conveyor belt','red','28')
select*from P;
insert into J(Jno,Jname,City) values('J1','Onekey ','Jinan')
insert into J(Jno,Jname,City) values('J2','SAIC Motor','Guangzhou')
insert into J(Jno,Jname,City) values('J3','Zipper production','Hangzhou')
insert into J(Jno,Jname,City) values('J4','Radio power plant','Beijing')
insert into J(Jno,Jname,City) values('J5','Machine tool plant','Shanghai')
insert into J(Jno,Jname,City) values('J6','Screw factory','Chongqing')
insert into J(Jno,Jname,City) values('J7','a machinery plant','Tianjin')
select*from J;
insert into SPJ(Sno,Pno,Jno,Qty) values('S1','P1','J2','250')
insert into SPJ(Sno,Pno,Jno,Qty) values('S1','P1','J3','340')
insert into SPJ(Sno,Pno,Jno,Qty) values('S1','P2','J5','120')
insert into SPJ(Sno,Pno,Jno,Qty) values('S1','P2','J6','270')
insert into SPJ(Sno,Pno,Jno,Qty) values('S1','P2','J7','530')
insert into SPJ(Sno,Pno,Jno,Qty) values('S2','P2','J1','170')
insert into SPJ(Sno,Pno,Jno,Qty) values('S2','P3','J2','260')
insert into SPJ(Sno,Pno,Jno,Qty) values('S2','P4','J3','100')
insert into SPJ(Sno,Pno,Jno,Qty) values('S2','P4','J4','120')
insert into SPJ(Sno,Pno,Jno,Qty) values('S2','P5','J5','560')
insert into SPJ(Sno,Pno,Jno,Qty) values('S2','P5','J7','310')
insert into SPJ(Sno,Pno,Jno,Qty) values('S2','P6','J6','200')
insert into SPJ(Sno,Pno,Jno,Qty) values('S3','P1','J1','400')
insert into SPJ(Sno,Pno,Jno,Qty) values('S3','P7','J1','300')
insert into SPJ(Sno,Pno,Jno,Qty) values('S4','P3','J2','401')
insert into SPJ(Sno,Pno,Jno,Qty) values('S4','P4','J3','330')
insert into SPJ(Sno,Pno,Jno,Qty) values('S4','P4','J4','650')
insert into SPJ(Sno,Pno,Jno,Qty) values('S4','P5','J4','150')
insert into SPJ(Sno,Pno,Jno,Qty) values('S5','P7','J5','230')
insert into SPJ(Sno,Pno,Jno,Qty) values('S5','P7','J7','280')
insert into SPJ(Sno,Pno,Jno,Qty) values('S6','P2','J2','350')
insert into SPJ(Sno,Pno,Jno,Qty) values('S6','P2','J3','420')
insert into SPJ(Sno,Pno,Jno,Qty) values('S6','P6','J1','310')
select*from SPJ;