Assessment objectives :

Course objectives

Corresponding index point

Description of requirements

Corresponding module

Test score


Basic knowledge and principle method





Database design modeling capability





database SQL Programming and database management ability





Database application programming and complex engineering problems



Question type of test paper :

  Question type of test paper

* Single choice question (20 branch , Each topic 1 branch ,20 topic )
* Judgement question (20 branch , Each topic 1 branch ,20 topic )
* Applied questions (20 branch , Each topic 5 branch ,4 topic )
* Programming problems (30 branch , Each topic 5 branch ,6 topic )
* Design questions (10 branch , Each topic 10 branch ,1 topic )
Score proportion of each chapter

choice question : The proportion of all seven chapters is the same

Judgement question : The proportion of all seven chapters is the same

Applied questions :3,4,5,6 Mention each chapter ( Similar short answer , Application of deviation from short answer )

Programming problems : Chapter III 20 branch 5,6 One question for each chapter 5 branch

Design questions : Chapter IV 10 branch (E_R chart )

Chapter III : The difficulty is similar to the experimental evaluation .

Chapter IV : Number of entities does not exceed 6 Of CDM chart .

Chapter V : Charts in Courseware , Give part , Complete the remainder ; code : Locking protocol ;

Chapter VI : Charts in Courseware , Give part , Complete the remainder ; code :JDBC, stored procedure , trigger , cursor ;

Courseware based , fifth , Chapter 6 forms of code questions include : Self completion ; Give some codes , Complete the remainder ; Write code Notes ;

Chapter 7 only choice and right and wrong , Review conceptual content

Section 1 chapter Introduction to Database System

Section 2 chapter Relational data model

1. Cartesian product


2. and union

4 that 's ok , seek common ground while reserving differences

 3. hand over intersect

2 that 's ok , Seek common ground

 3. difference except


R Remove the same and leave the difference

4. product cross join on

 6 column 9 that 's ok

5. query , Projection

choice sigma Projection π,sigma dept='IS'(Student)

 sigma age<20(Student)




6. connect

  Wrong : I mistook it for a natural connection , Now conditional connection




Equivalent connection


Natural connection


Left outer connection

Section 3 chapter SQL language  

Database creation

creat database CourseDB;

  Database modification

wrong : alter CourseDB rename to CourseManageDB;

Should be :

alter database CourseDB rename to CourseManageDB;

Delete database

drop database CourseMangerDB;

Create table

creat table Student(

StudentID char(13) not null primary key,

StudentName varchar(10) not null,

 ... char(2),,




Create table 2

 creat table Course(

... char(4) not null primary key, not unique, ...check in('...','...'),                  error , Change to check(

... smallint

vc... default '....'



  surface 3





constraint ..._PK primary key(.....)        Didn't remember


  surface 4


... series not null,        Wrong : Change to serial


constraint .._PK primary key (...);        Missed


surface 5


... serial ...

... bigint not..

contraint ... pri.. c..

contraint ... foreign key (C..) reference from table ...;       
Wrong : Change to references Plan(CourseID) on delect cascade;

Modify table


 alter table Student add email varchar(255);

Delete table

drop table Register;        No, alter

  Create index

 creat index B_idx on Student(Birthday);        Missed

Modify index

 alter index b.. rename to b...;

  Delete index

drop index b..;

Data insertion

 insert into table Student values('....',);        error : surplus , Change to :insert into
Student values('....',);  

Data update

insert into Student(email) '...' where Sname='...';

Change to

updata Student

set Email=‘...’

where StudentName='..';

Data deletion

drop Student where SN='';

Change to


from student

where SN='';

Data query

 select ........ from Student;



Query de duplication

 select distinct * from...

Select query

 select * ... where S..gender='..';


 select ........ ....

 select * ...where bir.. between '...' and '...';


 select .... where ... like '';

 select ..where ..and  ...;


Descending query

 ...order by ... desc        Forget about it

  Descending before ascending query

 ..order by ... DESC,order by ...ASC;        More , Change to  ..order by ... DESC,

Aggregate function


 select count(*) as Number of students from student;        Missed


Max min order

 select max(age),min(age) as .. ,.. from ...;

Out of sequence , Change to max(bir.. ) as...min(b...) as ...


 select * f.. s.. group by major;

Change to

 select major as major ,count(StudentID)as Number of students     f.. s.. group by major;

Restricted group query

 select major as ..count(SI) as ... from student  group by major having
count(SI)>2;                leak , wrong

  select major as ..count(SI) as ... from student where SG=' male ' group by major
having count(*)>2;  


select ...from teacher where CI in (select CI from C where CN='...');

  Multi table Association

select ... from t,c where and CN='..'

  Internal connection


Internal connection 2

  External connection

Grant permission

 grant insert,alter,delect,select table register to RoleS;       
Wrong : For data modification update, Table on

Change to

grant insert,update,delect,select on register to RoleS;  

  Permission withdrawal

 revoke delect on register from Roles;

Deny permission

 deny delect on teacher to RoleT;       Forget

Create view

 create view Basic... from  ..;

Change to

creat view B... as select ...from...where ...='';

Access view

 select * from view B.. order by  CN;        More : Change to  select * from B.. order by 

  Delete view

 drop view B...;

View application




  Section 4 chapter Database design

normal form


  The primary key and attribute are tables respectively


Found two primary keys, one of which is indispensable , The attribute that can be pushed is a table , Then the primary keys adopt their own attributes








  Section 5 chapter Database management

Thing definition

Thing submission

begin transaction




end transaction

Thing revocation

begin transaction




end transaction

pg_dump Backup database

cd \program files\PostgreSQL\12\bin

pg_dump -h localhost -U postgres -p 5432 -d coursedb -c -C
-f f:\databackup\coursedb.backup


psql Recover database

psql -h -U postgres -p 5432 -f f:\databackuplcoursedb.backup

 pg_dumpall Backup database

pg_dumpall -h localhost -U postgres -p 5432 -c -C -f
f:\databackup\coursedb.backup        No, -d

ER Figure painting


  Create user

 creat user "userA" with







connection limit -1

password '123456';

Modify user permissions

alter user "userA"

conncection limit 10        No commas

password 'gres123';

delete user

 drop user "userA";

Authorized user


  Create role : want “ Role name ”

  Role authorization


Section 6 chapter Database programming

JDBC Load driver


  Establish connection


establish Statement object

implement SQL sentence


resultset Save result set


Close connection


JDBC example

  Create stored procedure

 create or replace function ...()

returns integer AS $$


        count int;




$$ language plpgsql;

Execute stored procedure

 select countRecords();

 select into res countRecords();

Delete stored procedure

drop function if exists testExec();


 PL,SQL grammar


Create trigger function



  Create trigger




Update trigger

Delete trigger







Embedded SQL sentence





Section 7 chapter NoSQL Database technology

NoSQL theoretical basis :

    CAP theory : uniformity , usability , Partition tolerance .

    BASE Model : Basically available , Soft state , Final consistency .

    Final consistency theory : Causal consistency , Read consistency , Session consistency , Monotonic read consistency , Monotonic write consistency .

NoSQL Database classification :

    Key value pair storage mode :Redis

    Column storage mode :HBase

    Document storage method :MongoDB

    Graphic storage mode :Neo4j