Experimental questions

Basic Operation of Experiment 3 Database

(1) Create a database textbook:

CREATE database textbook

(2) Display and modify the maximum number of connections

show variables like "max_connections";
set global max connections = 8;

(3) Delete the database textbook

drop database textbook

Experiment 4 Definition and Modification of Data Based on SQL

(1) In the database Antiepidemic, use the SQL statement to create the data table aehero(aehero_id, aehero_name, aehero_gender) and set aehero_ ID is the primary key.

create table aehero
{
	aehero_id CHAR(20) not null unique primary key,
	aehero_name VARCHAR(30),
	aehero_gender CHAR(4),
	aehero_team VARCHAR(40)
}

(2) Set default values

eeage smallint default 18,

(3) In database Antiepidemic, use SQL statements to create data table contribution(aehero_id, aemeasure_id, aehero_deeds), set primary and foreign keys.

create table contribution
{
	aehero_id CHAR(20),
	aemeasure_id CHAR(20),
	aehero_deeds VARCHAR(40),
	primary key(aehero_id, aemeasure_id),
	constraint fk_aehero foreign key(aehero_id) references aehero(aehero_id),
	constraint fk_aemeasure foreign key(aemeasure_id) references aemeasure(aemeasure_id)
}

(4) Use the SQL statement to modify the name of the data table aehero to hero1 and add the field aehero_team (medical team of anti-epidemic heroes)

alter table aehero rename to hero1;
alter table hero1 add column aehero_team VARCHAE(30);

(5) Using SQL statements to modify aehero_in data table hero1 The name field is of type text.

alter table hero1 modify column aehero_name TEXT;

(6) Use SQL statements to set aemeasure_in aemeasure Change name to aemeasurename

alter table aemeasure change aemeasure_name aemeasurename VARCHAR(30);

(7) Delete field aehero_in data table hero1 using SQL statement Gender.

alter table hero1 drop column aehero_gender;

(8) Delete the table hero1 (delete the table where the foreign key is located first)

drop table contribution;drop table hero1;

(9) Use SQL statements to insert data into the aehero table:

insert into aehero values('1', 'Li Lanjuan', 'female', 'Chinese Academy of Engineering');

(10) Use a SQL statement to set aemeasure_in the aemeasure table Change measure name with id=1 to "fencing".

update aemeasure set aemeasure_detailr = 'Fengcheng' where aemeasure_id = '1';

(11) Use SQL statements to delete all data in the contribution table

SET SQL_SAFE_UPDATES = 0;delete from contribution;

Experiment 5 Form Query and Join Query

(1) Query the mid of the medicine table with a SQL statement starting with'2018'and empty mdescription, and the result shows all its attributes.

select * from medicine where mid like "2018%" and mdescription is null;

(2) Query the value of the myear attribute in the medicine table using a SQL statement and return the value of the myear attribute without duplication.

select distinct myear from medicine;

(3) It is 3300 yuan to query the price of E-gelatin with SQL statement in the past few years, and the result shows the number of years.

select year(now()) - myear as year from medicine where mname = 'Collagen Astragali' and mprice = 3300;

(4) Query medicinal materials'names (mname) and unit prices (mprice) with SQL statements for the Anguo and Yulin markets in the medicine table between 2018 and 2019, and arrange them in ascending order according to the time of medicinal materials' storage.

select mname, mprice from medicine 
where mlocation in ('Security', 'Yulin') 
and (myear between 2018 and 2019) 
and mprice > 58
order by myear asc;

(5) AVG() function was used to calculate the average unit price (named avg_price) of Chinese medicine s in each year. Only those groups with average unit price above 800 were selected. The result shows the year of warehousing and the corresponding average unit price.

select myear, avg(mprice) as avg_price from medicine 
group by myear having avg_price > 800

(6) Use the COUNT() function and MAX() function to count the number of medicinal herbs (named types) and the maximum unit price (named max_price) in each year of the medicine table. The result shows the year of storage, the number of medicinal herbs and the maximum unit price, in ascending order according to the highest unit price, and select two lines starting from the second line (including the second line).

select myear, count(myear) as kinds, max(mprice) as max_price
from medicine
group by myear
order by max_price asc
limit 2 offset 1

(7) Using Cartesian product inquiry: the participation of various units in the preparation of the plan.

select * from department
cross join record
where department.depart_id = record.depart_id;

(8) Use natural join query: the name of the prepared plan and the name of the participating units.

select plan_name, depart_name
from plan natural join record natural join department;

(9) Conditional join query: Name and telephone of the unit participating in the No. 1 planning.

select depart_name, depart_tel
from plan join record join department 
on plan.plan_id = record.plan_id and record.depart_id = department.depart_id
where plan.plan_id = 1

(10) Join queries using attributes: the participation of various units in planning

select * from department join record using(depart_id);

Experiment 6 Nested Query

(1) Query the average age of the winners in the same year using SQL statements.

select ayear, avg(aage) as avg_agefrom awardgroup by ayear;

(2) Nested query of the average age difference between Wang Shihua and the winners using select clause

select aname, aage - (select avg(aage) from award) as differencefrom awardwhere aname = "Wang Shihua";

(3) Use the select clause nested query to find the difference between the average age and all the winners.

select aname, aage - (select avg(aage) from award) as differencefrom award;

(4) Nested query of age difference between Du Fuguo and Lu Baomin in award table with select clause

select aage - (select aage from award where aname = "Lv Baomin") as differencefrom awardwhere aname = "Dufuguo";

(5) Query the award table using SQL for rows numbered beginning with'2016'and empty description s.

select *from awardwhere aid like "2016%" and description is null

(6) It has been several years since Sun Jiadong won the prize by using nested queries with select clauses.

select year(curdate()) - ayear as "Over the past few years"from awardwhere aname = "Sun Jiadong";

(7) Use the with clause to nest queries on the budget numbers and responsibilities of the Ministry of Civil Affairs

with temp(depart_id, depart_name) as(select depart_id, depart_name from depart where depart_name = "Ministry of Civil Affairs")select depart_name,plan_id,depart_responfrom temp join record on temp.depart_id = record.depart_id;

(8) Use the select clause to nest queries for each unit name and the number of plans it is responsible for.

select depart_name, (select count(*) from record where depart.depart_id = record.depart_id)as num from depart;

Write by Yourself

select depart_name, count(*) from (select * from depart cross join recordwhere depart.depart_id = record.depart_id)group by depart_name

(9) Use the from clause to nest queries for the number of plans the Emergency Department is responsible for, and name the from subquery num

select count(*)from (select plan_id from depart, record where depart_name = "Emergency Department" anddepart.depart_id = record.depart_id) as num;

(10) Use nested queries with where clauses: the name of the unit responsible for `Preparedness at the publishing time after the publishing time of Plan No. 2'and its responsibilities in the Preparedness.

select depart_name, depart_responfrom depart,record,planwhere plan.plan_date > (select plan_date from plan where plan_id = 2)and depart.depart_id = record.depart_id andplan.plan_id = record.plan_id;

Experiment 7 python Connection to MySQL

(1) In MySQL, create a function to calculate the sum of integers a+b.

DELIMITER $$drop function if exists sum_ab$$create function sum_ab(a int, b int)returns intbeginreturn (select a + b);end $$DELIMITER ;select sum_ab(1, 2);

(2) Create a function add_in MySQL Num_ Cn (weapon_id,num_cn), which achieves a fixed value increase in the number of IDs of a given kind of weapon each serviceman of the Chinese People's Volunteer Army. For example: add_num_cn(2,1) is to add 1 to the number of automobiles (num_cn) each armed force of the Chinese People's Volunteer Army.

DELIMITER $$drop function if exists add_num_cn$$create function add_num_cn(id int, num int)returns intbeginupdate weapon set num_cn = num + num_cn where weapon_id = id;return (select num_cn from weapon where weapon_id = id);end $$DELIMITER ;select add_num_cn(2, 1);

Experiment 9 Views and Access Control

(1) Create a name master_on the master table View1, which requires only information about artisans over 50 years of age; Insert data master_into the view Id=6, master_ Name ='Joshukai'

create view master_view1 as select * from master where master_age > 50; insert into master_view1 values(6, 'Chow Sukai', null, null, null);

(2) What about adding a constraint with check option to the view creation? Can I insert it successfully? Please explain why

create view master_view2 as select * from master where master_age > 50 with check option;insert into master_view2 values(7, 'Exhibiting', null, null, null);

View creation succeeded, but inserting data failed. If WITH CHECK OPTION is included at the end of the view definition, the database management system automatically checks that changes to the view should meet the WHERE condition in the view definition, in which case the insertion will be rejected.

(3) Create user user1 with password'12345'. Create role master_admin, grant it permission to query the master table and update the field master_name, and allows this privilege to be granted to other roles/users, and the privilege to that role to user user1.

-- Create User create user user1 IDENTIFIED BY '12345';-- Create roles create role master_admin;-- Grant it master Query permissions and update fields for tables master_name Permission to grant this permission to other roles/user grant select, update on masters.master to master_adminwith grant option;-- Grant privileges for this role to users user1grant master_admin to user1;set default role all to user1;

Verify user1's query and update permissions on the master table (update the name of the artisan whose master_id is 1 to'Vice President of Gao Fenglin');

use masters;update master set master_name = 'Vice President Gao Fenglin' where master_id = 1;

(4) Create user user2 with password'12345'. Create role master_reader, grant the role permissions to user2.

-- Create User create user user2 IDENTIFIED BY '12345';-- Create roles create role master_reader;-- Grant privileges for this role to users user2grant master_reader to user2;set default role all to user2;

Grant query privileges on master table to master_using user user1 Reader role

-- User user1 take master Query privileges granted to tables master_reader role grant select on masters.master to master_reader;

(5) Undo master_with user user1 Reader's query permissions on master table

-- User user1 Revoke master_reader Yes master Query permissions for tables revoke select on masters.master from master_reader;

Experiment 10

(1) What happens when the following data is added to the record table and why?

insert into record values(5, 1, "Emergency supplies", 150);

Interpretation: Inserted depart_id is 5 because depart_ The id is a foreign key to the record table, but there is no department with id 5 in the Department table, so an error occurred.

(2) Plan_in the record table Change the value of ID 1 to 5, what happens, and why

update record set plan_id = 5 where plan_id = 1;

(3) Create a trigger on the plan table and prompt for an insertion error if the trigger is directed at Washington, D.C.

DELIMITER $$CREATE TRIGGER a BEFORE INSERT ON plan FOR EACH ROWBEGINIF(new.plan_area = 'Washington') thensignal sqlstate 'HY000' set message_text = 'Insert error!';end if;END $$DELIMITER;

(4) Create a trigger on the record table if depart_in the record table ID is modified, depart_in the corresponding depart table The ID is also modified.

DELIMITER $$CREATE TRIGGER b after update ON record FOR EACH ROWBEGINIF(new.depart_id <> old.depart_id) thenupdate depart set depart_id = new.depart_idwhere depart_id = old.depart_id;end if;END $$DELIMITER ;

(5) Delete triggers on the plan table.

drop trigger a; 

Experiment 11

(1) Show that a transaction is open, insert the following data into the depart table, and roll back the transaction using ROLLBACK.

begin;insert into depart values(5, 'Treasury Department', '68551114');rollback;commit;

(2) Display opening a transaction, inserting the following two rows of data into the depart table, creating a savepoint between the first insertion statement and the second insertion statement, and rolling back the transaction to the savepoint just after the insertion is complete.

begin;insert into depart values(6, 'Tax Bureau', '63417425');savepoint sql1;insert into depart values(7, 'Bureau of Statistics', '68573311');rollback to sql1;commit;

(3) Display open transaction 1, lock depart table, only allow depart table to have read operation

T1:begin;lock table depart read;commit;T2:begin;insert into depart values(9, 'Division', '58789777');commit;

(4) Use the MD5 encryption algorithm on the string "I love database" and return the encryption result.

select md5('I love database');

(5) Using AES algorithm and string "datebase" as key, symmetrically encrypt "abcdef", output encrypted ciphertext, then decrypt the ciphertext to restore "abcdef".

encryption

select hex(aes_encrypt('abcdef', 'datebase'));

Get the ciphertext 83B78DCBD0503D3420DE40AE0EE6EE6D

Decrypt

select aes_decrypt(unhex('83B78DCBD0503D3420DE40AE0EE6EE6D'), 'datebase');

Midterm and Final Examination

Interim Period

(1) Retrieve the test number and the name of the test paper submitted by the examinee with test number 2181110888.

(2) A database contains two tables, one table P, including columns a1, a2, and b1; Another table Q, including attributes b1, b2, and b3. Table P attribute B1 is a foreign key, referencing table Q's b1. Table P primary key a1, table Q primary key b1; Assume that A has nnn tuples and B has mmm tuples. Suppose there are no NULL s in both tables. How many tuples is the result of the natural join of P and Q?
Answer: nnn
(3)

Question (8) of Experiment 6, don't say more

End of Period

Other

E-R diagram

The basic E-R diagram is converted into a relational model to learn how to share and encounter problems. A library database is designed to keep records of each book saved by each borrower, including reader number, name, address, gender, age, and department. For each book: book number, title, author, publisher. Every time a book is borrowed, keep the information for each borrowing: the date of loan, the date of return, and the date of return. Requirements: An E-R diagram is given and converted to a relational pattern.

Be careful:

1. Don't forget to underline under the primary keys for borrowers and books
2. Don't forget to label M and N
3. Since it's an M:N relationship, don't forget to label the borrowings.

Unit Jobs

(1)

(2)

Final Class Topics

First Class

(1)

(2)

(3)

(4)

(5)

(6)

Second Class

(1)

(2) Any relationship pattern consisting of two attributes must belong to BCNF ()
(3) If the candidate key of relationship mode S<A, D>is A, S must belong to BCNF ()

No non-primary attribute, 3NF. The primary attribute must neither be partial nor pass-through dependent on any candidate key, otherwise the candidate key is not A.

(4) Relational modes S<A, D>, A={I, J, K}, D={I_J,J_K}, {IJ,JK} are decompositions of S lossless joins and remain dependent, {IJ,JK} are BCNF ()
(5) Please design a BCNF database for it. Database Description

1. Set up the Beijing Winter Olympic Competition database to store the information of the athletes'results, competition categories and directors, including attributes: athletes number, competition items, results, competition categories, and competition directors.
2. If it is stipulated that each athlete will get only one result for each event he participates in; Each competition item belongs to only one category; There is only one Competition Supervisor per Competition Category; Each Competition Supervisor is responsible for only one Competition Category.

MOOC Announcement Title

(1)
1 np_id

(2)
16

(3)
2 4 × 2 5 + 3 4 = 593 2^4 \times 2^5 + 3^4 = 593 24×25+34=593​

(4)
243

(5)

350

(6)
n p _ i d → n p _ n a m e , n p _ i d → n p _ a w a r d , n p _ i d → n p _ a g e , n p _ i d → n p _ n a t i o n a l i t y np\_id \rightarrow np\_name, np\_id \rightarrow np\_award, np\_id \rightarrow np\_age, np\_id \rightarrow np\_nationality np_id→np_name,np_id→np_award,np_id→np_age,np_id→np_nationality