Establishment of database

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



[experiment content]

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

(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)
into Depts(Dno,Dname) values('D01','automation')
into Depts(Dno,Dname) values('D02','computer')
into Depts(Dno,Dname) values('D03','mathematics')
into Depts(Dno,Dname) values('D04','signal communication')
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)
into Students(Sno,Sname,Ssex,Brithday,Dno) values('S01','Wang Jianping','male','1995-10-12','D01')
into Students(Sno,Sname,Ssex,Brithday,Dno) values('S02','Liu Hua','female','1997-08-21','D02')
into Students(Sno,Sname,Ssex,Brithday,Dno) values('S03','Fan Jinjun','female','1998-02-11','D03')
into Students(Sno,Sname,Ssex,Brithday,Dno) values('S04','Li Wei','male','1996-12-22','D04')
into Students(Sno,Sname,Ssex,Brithday,Dno) values('S05','Yellow River','male','1999-10-31','D05')
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)
into Courses(Cno,Cname,Pre_Cno,Credit) values('C01','English',' ','4')
into Courses(Cno,Cname,Pre_Cno,Credit) values('C02','data structure','C05','2')
into Courses(Cno,Cname,Pre_Cno,Credit) values('C03','database','C02 ','2')
into Courses(Cno,Cname,Pre_Cno,Credit) values('C04','DB_Design','C03','3')
into Courses(Cno,Cname,Pre_Cno,Credit) values('C05','C++',' ','3')
into Courses(Cno,Cname,Pre_Cno,Credit) values('C06','Network principle','C07 ','3')
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)
into Reports(Sno,Cno,Grade) values('S01','C01','92')
into Reports(Sno,Cno,Grade) values('S02','C02','84')
into Reports(Sno,Cno,Grade) values('S03','C03','90')
into Reports(Sno,Cno,Grade) values('S04','C04','94')
into Reports(Sno,Cno,Grade) values('S05','C05','82')
into Reports(Sno,Cno,Grade) values('S06','C06','72')
into Reports(Sno,Cno,Grade) values('S07','C07','90')
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:

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.

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.
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 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

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;

Tags: SQL

Posted by austinderrick2 on Thu, 02 Jun 2022 09:39:08 +0530