objective

Use SQL language to create and delete the database; Create, delete and update basic tables

requirement

1. In MySql Workbench, CREATE and DROP commands in SQL language are used to CREATE and delete databases.
2. In MySql Workbench, CREATE, ALTER and DROP commands in SQL language are used to CREATE, update and delete basic tables, and to limit various integrity constraints in basic tables.
3. After completing the above work, check whether the database, basic table, various integrity constraints and indexes required by the experiment are successfully created in MySql Workbench.

Experimental content

Define database

Define a borrowing database named "Library_01"

create database library_01;

Define database base tables

In the defined borrowing database "Library_01", define three basic tables (Book, library Card and borrowing record Borrow) as required, and determine the following integrity constraints:

• Determine the primary code of each basic table. The primary code attribute cannot be empty;
• If there is a foreign code, define the foreign code of each basic table;
• It is required to check whether the value of attribute Type in the card table is ('t ',' g ',' U ',' f ') (T: teacher, G: graduate student, u: undergraduate, F: personnel of other units);

1. Book
   

2. Card
   

3. Borrow
   

-- Switch to Library_01 Database must be enabled at the beginning of the file, otherwise the server does not know which database to use, or another database is applied
use Library_01;

-- establish Book surface
create table Book
(
    bno           char(8) not null comment'Book number' primary key,
    category  varchar(10) not null comment'Book category',
    title     varchar(40) not null comment'title',
    publisher varchar(30)          comment'press',
    pub_year        year  not null comment'Year of publication',
    author       char(20)          comment'author',
    price    decimal(5,2) not null comment'Price'
);

-- establish Card surface
create table Card
(
	cno           char(7) not null comment'Library Card No' primary key,
	pname         char(8) not null comment'full name',
	department varchar(5)          comment'the unit to which one belongs',
	ctype         char(1) not null comment'Library card category' check(ctype in('T', 'G', 'U', 'F'))
);

-- establish Borrow surface
create table Borrow
(
	cno           char(7) not null comment'Loan No',
	bno           char(8) not null comment'Book number',
	borrow_date      date not null comment'Lending date',
	return_date      date          comment'Return date ',
    constraint C1 primary key(cno, bno),
    constraint C2 foreign key(cno) references Card(cno),
    constraint C3 foreign key(bno) references Book(bno)
);

data input

Input 10 pieces of data into the three basic tables of Book, Card and Borrow

• Book
  
• Card
  
• Borrow
  

-- Book Enter 10 pieces of data in the basic table
insert into Book (bno, category, title, publisher, pub_year, author, price) values
('b0001',   'eng.',       'Database systems', 'Springer', 2015,      'John', 28),
('b0002',   'eng.',      'Operation systems',   'Taylor', 2017,   'Michael', 35),
('b0003',   'sci.',   'Advanced mathematics',      'MIT', 2021, 'Catherine', 15),
('b0004',   'sci.',   'Discrete mathematics',      'MIT', 2019,     'Peter', 22),
('b0005',   'eng.',             'C language', 'Springer', 2016,   'Shannon', 40),
('b0006', 'liter.',           'Harry potter', 'Elseiver', 2018,      'Lisa', 19),
('b0007', 'liter.',              'Star wars', 'Elseiver', 2017,       'Jim', 25),
('b0008',   'his.', 'Modern chinese history',   'Taylor', 2016,     'Frank', 32),
('b0009',   'his.',   'The second world war',   'Taylor', 2020,      'Mark', 32),
('b0010',   'eng.',          'JAVA language',      'MIT', 2021,     'Sally', 38);

-- Card Enter 10 pieces of data in the basic table
insert into Card (cno, pname, department, ctype) values
('T001',  'Li Yuan', 'auto', 'T'),
('T002',  'Wang Li', 'auto', 'T'),
('T003', 'Zhang Qi',   'cs', 'T'),
('T004', 'Zhao Gao', 'math', 'T'),
('G001',    'Wu Mi', 'auto', 'G'),
('G002',  'Qian qi',   'cs', 'G'),
('U001',   'Li Fen', 'auto', 'U'),
('U002',   'Bai La',  'art', 'U'),
('F001', 'Zhang Fa',  'pku', 'F'),
('F002',  'Li Aigu',  'stu', 'F');

-- Borrow Enter 10 pieces of data in the basic table
insert into Borrow (cno, bno, borrow_date, return_date) values
('T001', 'b0001', '2021/1/10', '2021/3/4'),
('T001', 'b0002', '2021/5/10', '2021/8/1'),
('T001', 'b0005', '2021/1/1', '2021/3/4'),
('T001', 'b0010', '2021/1/1', '2021/3/4'),
('T003', 'b0001', '2021/5/15', '2021/8/4'),
('T003', 'b0002', '2021/3/15', '2021/4/4'),
('T003', 'b0003', '2021/2/15', '2021/3/4'),
('T003', 'b0004', '2020/2/15', '2020/3/4'),
('G002', 'b0005', '2021/5/5', '2021/7/14'),
('G002', 'b0007', '2021/8/10', null),
('G002', 'b0010', '2021/7/10', null),
('U001', 'b0009', '2021/2/2', '2021/3/9'),
('U001', 'b0008', '2021/2/2', '2021/3/9'),
('U002', 'b0003', '2021/3/6', '2021/3/9'),
('U002', 'b0004', '2021/4/2', '2021/5/9'),
('F001', 'b0001', '2021/8/12', '2021/8/15'),
('F001', 'b0002', '2021/2/1', '2021/3/1'),
('F001', 'b0003', '2021/6/12', '2021/7/11'),
('F002', 'b0009', '2021/8/20', null);

-- View completed tables
select * from Book;
select * from Card;
select * from Borrow;

Data modification

• Give a 20% discount on all the values of the price attribute in the Book table
• Change the name of cno = 'F001' in the Card form to 'Li Ai'.

-- take Book In the table price All values of the attribute are given a 20% discount
update Book
set price = price*0.8;

-- take Card In the table cno='F001'Change your name to'Li Ai'
update Card
set pname = 'Li Ai'
where cno = 'T001';

Data deletion

• Delete the borrowing record with cno = 'F002' in the Borrow table
• Delete the books of 'MIT' Publishing House (super outline knowledge points)

-- delete Borrow In the table cno='F002'Book borrowing record
delete from Borrow
where cno = 'F002';

-- delete'MIT'Books from publishing houses
-- I'artificial intelligence'practice
select * from Book where publisher = 'MIT';
delete from Borrow where bno in ('b0003', 'b0004', 'b0010');
delete from Book where bno in ('b0003', 'b0004', 'b0010');
select * from Book;
select * from Borrow;
-- 2, Delete by temporary table'MIT'Books from publishing houses
create temporary table MIT_bno_table as
	select bno from book where publisher = 'MIT';
delete from Borrow where bno in (select * from MIT_bno_table);
delete from Book where bno in (select * from MIT_bno_table);
select * from Book;
select * from Borrow;
drop table MIT_bno_table;

reflection

If no data integrity constraint is defined when creating a table, what impact will it have on the database during data entry or deletion?
Data integrity constraints are data verification rules enforced on tables and fields. In order to prevent non-standard data from entering the database, when users insert, modify, delete and other operations on data, DBMS automatically monitors the data according to certain constraints, mainly the constraints on null values and duplicate values, so that non-conforming data cannot enter the database, To ensure the integrity and accuracy of data storage.
Therefore, if no data integrity constraint is defined when creating a table, non-standard values may be entered or null and duplicate values may appear during data entry or deletion.

Appendix - complete code

-- View existing databases
show databases;

-- Create database
create database Library_01;

-- Switch to Library_01 database
use Library_01;

-- Create table
create table Book 
(
    bno           char(8) not null comment'Book number' primary key,
    category  varchar(10) not null comment'Book category',
    title     varchar(40) not null comment'title',
    publisher varchar(30)          comment'press',
    pub_year        year  not null comment'Year of publication',
    author       char(20)          comment'author',
    price    decimal(5,2) not null comment'Price'
);

create table Card
(
	cno           char(7) not null comment'Library Card No' primary key,
	pname         char(8) not null comment'full name',
	department varchar(5)          comment'the unit to which one belongs',
	ctype         char(1) not null comment'Library card category' check(ctype in('T', 'G', 'U', 'F'))
);

-- drop table Borrow;
create table Borrow
(
	cno           char(7) not null comment'Loan No',
	bno           char(8) not null comment'Book number',
	borrow_date      date not null comment'Lending date',
	return_date      date          comment'Return date ',
    constraint C1 primary key(cno, bno),
    constraint C2 foreign key(cno) references Card(cno),
    constraint C3 foreign key(bno) references Book(bno)
);

-- Book Enter 10 pieces of data in the basic table
insert into Book (bno, category, title, publisher, pub_year, author, price) values
('b0001',   'eng.',       'Database systems', 'Springer', 2015,      'John', 28),
('b0002',   'eng.',      'Operation systems',   'Taylor', 2017,   'Michael', 35),
('b0003',   'sci.',   'Advanced mathematics',      'MIT', 2021, 'Catherine', 15),
('b0004',   'sci.',   'Discrete mathematics',      'MIT', 2019,     'Peter', 22),
('b0005',   'eng.',             'C language', 'Springer', 2016,   'Shannon', 40),
('b0006', 'liter.',           'Harry potter', 'Elseiver', 2018,      'Lisa', 19),
('b0007', 'liter.',              'Star wars', 'Elseiver', 2017,       'Jim', 25),
('b0008',   'his.', 'Modern chinese history',   'Taylor', 2016,     'Frank', 32),
('b0009',   'his.',   'The second world war',   'Taylor', 2020,      'Mark', 32),
('b0010',  'eng.',          'JAVA language',      'MIT', 2021,     'Sally', 38);

-- Card Enter 10 pieces of data in the basic table
insert into Card (cno, pname, department, ctype) values
('T001',  'Li Yuan', 'auto', 'T'),
('T002',  'Wang Li', 'auto', 'T'),
('T003', 'Zhang Qi',   'cs', 'T'),
('T004', 'Zhao Gao', 'math', 'T'),
('G001',    'Wu Mi', 'auto', 'G'),
('G002',  'Qian qi',   'cs', 'G'),
('U001',   'Li Fen', 'auto', 'U'),
('U002',   'Bai La',  'art', 'U'),
('F001', 'Zhang Fa',  'pku', 'F'),
('F002',  'Li Aigu',  'stu', 'F');

-- Borrow Enter 10 pieces of data in the basic table
insert into Borrow (cno, bno, borrow_date, return_date) values
('T001', 'b0001', '2021/1/10', '2021/3/4'),
('T001', 'b0002', '2021/5/10', '2021/8/1'),
('T001', 'b0005', '2021/1/1', '2021/3/4'),
('T001', 'b0010', '2021/1/1', '2021/3/4'),
('T003', 'b0001', '2021/5/15', '2021/8/4'),
('T003', 'b0002', '2021/3/15', '2021/4/4'),
('T003', 'b0003', '2021/2/15', '2021/3/4'),
('T003', 'b0004', '2020/2/15', '2020/3/4'),
('G002', 'b0005', '2021/5/5', '2021/7/14'),
('G002', 'b0007', '2021/8/10', null),
('G002', 'b0010', '2021/7/10', null),
('U001', 'b0009', '2021/2/2', '2021/3/9'),
('U001', 'b0008', '2021/2/2', '2021/3/9'),
('U002', 'b0003', '2021/3/6', '2021/3/9'),
('U002', 'b0004', '2021/4/2', '2021/5/9'),
('F001', 'b0001', '2021/8/12', '2021/8/15'),
('F001', 'b0002', '2021/2/1', '2021/3/1'),
('F001', 'b0003', '2021/6/12', '2021/7/11'),
('F002', 'b0009', '2021/8/20', null);

select * from Book;
select * from Card;
select * from Borrow;

-- take Book In the table price All values of the attribute are given a 20% discount
update Book
set price = price*0.8;

-- take Card In the table cno='F001'Change your name to'Li Ai'
update Card
set pname = 'Li Ai'
where cno = 'T001';

-- delete Borrow In the table cno='F002'Book borrowing record
delete from Borrow
where cno = 'F002';

-- delete'MIT'Books from publishing houses
select * from Book where publisher = 'MIT';
delete from Borrow where bno in ('b0003', 'b0004', 'b0010');
delete from Book where bno in ('b0003', 'b0004', 'b0010');
select * from Book;
select * from Borrow;

-- Delete by temporary table'MIT'Books from publishing houses
create temporary table MIT_bno_table as
	select bno from book where publisher = 'MIT';
delete from Borrow where bno in (select * from MIT_bno_table);
delete from Book where bno in (select * from MIT_bno_table);
select * from Book;
select * from Borrow;
drop table MIT_bno_table;