©2020-2024 ioDraw All rights reserved
Assessment objectives ：
Course objectives
Corresponding index point
Description of requirements
Corresponding module
Test score
CO1
Basic knowledge and principle method
CM1-CM7
40
CO2
GR3.4
Database design modeling capability
CM4
15
CO3
GR4.2
database SQL Programming and database management ability
CM3,CM5
15
CO4
GR5.3
Database application programming and complex engineering problems
CM3,CM6
30
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)
πSname,depc(Student)
πdepc（Student）
6. connect
Wrong ： I mistook it for a natural connection , Now conditional connection
Equivalent connection
Natural connection
except
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),
...data,
...varchar(30),
...char(11)
);
Create table 2
creat table Course(
... char(4) not null primary key,
...vc(20) not unique,
...vc ...check in('...','...'), error , Change to check(...in
('..','..','..'))
... 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
delete
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 '%@163.com';
select ..where ..and ...;
...where ...in('...');
Descending query
...order by ... desc Forget about it
Descending before ascending query
..order by ... DESC,order by ...ASC; More , Change to ..order by ... DESC,
...ASC;
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 ...
grouping
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;
Subquery
select ...from teacher where CI in (select CI from C where CN='...');
Multi table Association
select ... from t,c where t.ci=c.ci 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
CN;
Delete view
drop view B...;
View application
train
Section 4 chapter Database design
normal form
1NF
The primary key and attribute are tables respectively
2NF
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
3NF
BCNF
4NF
Section 5 chapter Database management
Thing definition
Thing submission
begin transaction
SQL
...
commit
end transaction
Thing revocation
begin transaction
SQL
...
rollback
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 127.0.0.1 -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
login
nosuperuser
nocreatedb
nocreaterole
inherit
noreplication
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 $$
declare
count int;
begin
select...;
end;
$$ 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
cursor
Embedded SQL sentence
C
JAVA
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
Technology