东华大学数据库整理.docx

数据库整理概念：什么是事务？事务的性质有哪些？二段封锁协议的内容是什么？答案：事务是构成单一逻辑工作单元的操作集合。事务的性质有：（1）原子性（2）一致性（3）隔离性（4）持久性两段锁协议的内容：在对任何数据进行读、写操作之前，事务首先要获得对该数据的封锁，在释放一个封锁之后，事务不再获得任何其他封锁。试述实现数据库安全性控制的常用方法和数据库恢复的基本原则。答案：实现数据库安全性控制的常用方法如下：1、帐号安全性-用户名/口令2、系统权限安全性-用角色管理系统级命令3、对象安全性-数据库对象的访问权限4、审计机制-核查对数据库的操作（对话、语句、特权、对象）5、视图机制-敏感数据的保密6、环境参数控制-控制用户对系统和数据库资源的使用。数据库恢复的基本原则是冗余，即数据库重复存储。具体方法为：平时做好转储和建立日志，一旦发生数据库故障，数据库遭到破坏，则将转储数据装入，通过日志文件进行重做（REDO），如数据库未被破坏，但数据不可靠，则通过日志文件进行撤消（UNDO）请述将ER模型转换成关系模型的基本原则。答案：E-R图转换为关系模型的原则：1.一个实体集转换为一个关系模式。2.一个m:n联系转换为一个关系模式。3.一个1:n联系可以与n端对应的关系模式合并（1的关键字放入N的关系中）。4.一个1:1联系可以与任意一端对应的关系模式合并。5.三个或三个以上实体间的多元联系转换为一个关系模式6.同一实体集间的联系（自联系），可按上述1:1、1:n和m:n三种情况分别处理。试述下列概念1.数据库；答案：数据库是长期存储在计算机内的、有组织的、共享的数据集合。它可以供用户共享，具有尽可能小的冗余度和较高的数据独立性，使得数据存储最优，数据最容易操作，并且具有完善的自我保护能力和数据恢复能力。2.视图；答案：视图是从一个或几个基本表（或视图）中导出的虚表。数据库中只保存视图的定义。视图是RDBMS提供给用户以多种角度观察数据库中数据的重要机制。3.函数依赖；答案：函数依赖（Functional Dependency）是关系模式中属性之间的一种逻辑依赖关系。具体定义如下，设关系模式R(U)，U是属性全集，X和Y是U的子集。如果对于R(U)的任意一个可能的关系r，对于X的每一个具体值，Y都有唯一的值与之对应，则称X决定函数Y，或Y函数依赖于X，记作XY。称X为决定因素，Y为依赖因素。4.数据字典；答案：指对系统中全部数据的描述，包括数据项、数据结构、数据流、数据存储和管理五个部分。数据库系统的生命周期。答案：基于数据库系统的生存期，数据库设计分为：规划、需求分析、概念设计、逻辑设计、物理设计、实现与运行维护等七个阶段。规划：进行必要性和可行性分析，确定数据库系统在组织中和信息系统中的地位，以及各个数据库之间的关系；需求分析：收集数据库所需要的信息内容和处理需求，并加以规范化和分析；概念设计：将用户的信息需求统一到一个整体逻辑结构；逻辑设计：包括数据库结构设计和应用程序设计。；物理设计：包括物理数据库结构的选择和程序设计中程序模块说明的精确化。实现：产生一个具体的数据库和应用程序运行和维护：收集和记录系统运行状态的数据，用以评价数据库的性能，用于对数据库系统修正甚至扩充。1简述事务的ACID特征，以及这些特征分别由数据库的什么子系统来完成。1）原子性事务的原子性是指一个事务的所有操作要么全做，要么全不做，保证原子性是数据库系统本身的职责，由DBMS的事务管理子系统来实现。2）一致性事务的一致性是指每个事务的执行保证数据库从一致的正确的状态变成一致的正确的状态，确保单个事务的一致性是编写事务的应用程序员的职责，系统运行时，由DBMS的完整性子系统执行测试任务。3）隔离性在多个事务并发执行时，系统应保证与这些事务先后单独执行时的结果一样，此时称事务达到了隔离性的要求。隔离性是由DBMS的并发控制子系统实现的。4）持久性一个事务一旦完成全部操作后，它对数据库的所有更新应永久地反映在数据库中，不会丢失，即使以后系统发生故障，也是如此。由DBMS的恢复管理子系统实现。2. 关系规范化过程实质上是对关系不断分解的过程。分解关系的基本规则是什么？1）无损分解2）保持函数依赖3. 简述数据库管理系统的二级映射，并说明它们分别保证了什么？1）外模式/逻辑模式映像存在于外模式和逻辑模式之间，用于定义外模式和逻辑模式之间的对应性。这个映像一般放在外模式中描述的。用于保证当数据库的逻辑模式发生变化时，外模式可以不变。2）逻辑模式/内模式映像存在于逻辑模式和内模式之间，用于定义逻辑模式和内模式之间的对应性，这个映像一般放在内模式中描述的。用于保证当数据库的内模式发生变化时，模式可以不变。4. 简述一个设计不好的数据库会带来哪些问题？1）数据冗余。2）操作异常。包括修改异常、插入异常、删除异常。5. 简述关系模型有哪三类完整性规则?1）实体完整性规则：主键不重复、主键不为空2）参照完整性规则：不允许引用不存在的实体3）用户定义的完整性规则：如Between and 约束；NOT NULL 约束；UNIQUE 约束；值域约束三层模式体系结构：外模式：用户与数据库系统的接口，是用户用到那部分数据的描述。外模式由若干个外部记录类型组成。（视图）逻辑模式：数据库中全部数据的整体逻辑结构的描述。它由若干个逻辑记录类型组成，还包含记录间联系、数据完整性和安全性等要求。内模式：数据库在物理存储方面的描述，定义所有内部记录类型、索引和文件组织方式以及数据控制方面的细节。数据库的并发操作通常会带来三个问题：丢失更新问题、读脏数据问题、不可重复读问题。数据独立性有：物理数据独立性和逻辑数据独立性。DBMS主要功能：（1）数据库定义 （2）数据库操纵 （3）数据库保护 （4）数据库维护 （5）数据字典关系模型的三层体系结构：1） 关系模式：模式名、属性名、域名以及模式的主键2） 子模式：用户用到的那部分数据的描述3） 存储模式：关系存储作为文件看待，一个元组就是一条记录。启发式优化原则：1） 尽可能早得执行选择操作2） 尽可能早得执行投影操作3） 避免直接做笛卡尔积关系模式的范式：第一范式：如果关系模式R的每个关系r的属性值都是不可分的原子值，那么称R是1NF的模式。第二范式：如果关系模式是1NF，而且每个非主属性完全函数依赖于候选键，那么称R是2NF的模式。第三范式：无传递依赖。SQL：（1）检索学号为S3的学生所学课程的课程名与任课教师名。（2）检索至少选修LIU老师所授一门课的男学生的姓名。（3）检索WANG同学不学的课程的课程号。（4）检索至少选修C2和C4课的学生的学号。（5）检索学习全部课程的学生名。（6）检索每个学生已选修课程的门数及平均成绩。（7）删除学号为95002的学生选修的课程号为C2的记录。（8）建立数学系学生的视图C_STUDENT，并要求进行修改和插入数据时，仍需保证该视图只有数学系的学生。视图的属性名为：S#，SNAME，AGE，DEPT。答案：（1）Select CNAME,TeacherFrom SC,CWhere SC.C#=C.C# and S#=S3（2）Select SNAMEFrom SWhere S# in (Select S#From SCWhere C# in (Select C#From CWhere teacher=LIU?)（3）Select C#From SCWhere S# not in (Select S#From SWhere SNAME=WANG（4）Select S#From SC X,SC YWhere X.S#=Y.S# and X.C#=C2?and Y.C#=C4（5）Select SNAMEFrom SWhere not exists (Select *From CWhere not exists (select *From SCWhere C#=C.C# and S#=S.S#)（6）Select S#,count(*),avg(GRADE)From SCGroup by S#（7）DeleteFrom SCWhere S#=?5002?and C#=C2（8）Create View C_STUDENT(S#，SNAME，AGE，DEPT) ASSelect S#，SNAME，AGE，DEPTFrom SWhere DEPT=数学系With check option2、查询每一门课的间接先修课（即先修课的先修课）。SELECT FIRST.Cno，SECOND.CpnoFROM Course FIRST，Course SECONDWHERE FIRST.Cpno = SECOND.Cno；1、查询其他系中比信息系某一学生年龄小的学生姓名和年龄SELECT Sname，SageFROM StudentWHERE Sage ANY (SELECT SageFROM StudentWHERE Sdept= IS )AND Sdept IS ;2、查询没有选修1号课程的学生姓名。SELECT SnameFROM StudentWHERE NOT EXISTS(SELECT *FROM SCWHERE Sno = Student.SnoAND Cno=1)；3、查询选修了全部课程的学生姓名。SELECT SnameFROM StudentWHERE NOT EXISTS（SELECT *FROM CourseWHERE NOT EXISTS(SELECT *FROM SCWHERE Sno= Student.SnoAND Cno= Course.Cno)；4、查询至少选修了学生95002选修的全部课程的学生号码。SELECT DISTINCT SnoFROM SC SCXWHERE NOT EXISTS(SELECT *FROM SC SCYWHERE SCY.Sno = 95002 ANDNOT EXISTS(SELECT *FROM SC SCZWHERE SCZ.Sno=SCX.Sno ANDSCZ.Cno=SCY.Cno)；5、建立1号课程的选课视图，并要求通过该视图进行的更新操作只涉及1号课程，同时对该视图的任何操作只能在工作时间进行。CREATE VIEW IS_SCASSELECT Sno，Cno，GradeFROMSCWHERECno= 1AND TO_CHAR(SYSDATE,HH24) BETWEEN 9 AND 17AND TO_CHAR(SYSDATE,D) BETWEEN 2 AND 6WITH CHECK OPTION;关系代数：（1）参与过20万元及以上项目的教师姓名和所属部门；（2）至少参与过两个项目的教师姓名和年龄。1）tname , depart(sfee=20(TIP)2）tname , age(T(s1=526(II)范式：假设某商业集团的商品销售数据库有一关系模式R如下：R (商店编号，商品编号，商品名称，库存数量，部门编号，部门名称、部门负责人)现规定：(1)每个商店的每种商品只在一个部门销售；(2)每个商店的每个部门只有一个部门负责人；(3)每个商店的每种商品只有一个库存数量。试回答下列问题：1）根据上述规定，写出关系模式R的基本函数依赖；说明：关于现实意义有多种假设，其中之一：（1）商品编号在所有商店是一致的；（2）各个商店对部门有自己的编号；（3）不同商店对同一商品可能分配到不同部门。根据以上假设得到的函数依赖有：（1）（商店编号，商品编号）-（部门编号，库存量）（2）（商店编号，部门编号）-部门负责人（3）商品编号-商品名称（4）部门编号-部门名称根据其它假设可能有不同的函数依赖。2）找出关系模式R的候选健；关系模式R的候选键为（商店编号，商品编号）。3）试问关系模式R最高已经达到第几范式？为什么？在R，存在非主属性如商品名称对候选键的部分函数依赖，所以不是2范式(2NF)。但是每个属性都是原子项，不可再分。综上所述，R是1范式(1NF)。4）如果R不属于3NF，请将R分解成3NF模式集。在1）中的函数依赖，已经是极小化后并合并相同左部。R无损连接并保函数依赖的分解为：R1（商店编号，商品编号，部门编号，库存量）,F1= （商店编号，商品编号）-（部门编号，库存量）;R2（商店编号，部门编号，部门负责人），F2=（商店编号，部门编号）-部门负责人;R3（商品编号，商品名称），F3=商品编号-商品名称;R4（部门编号，部门名称），F4=部门编号-部门名称。SQL语句：一、约束和排序语法格式：SELECT *|DISTINCT column|expression alias,.FROM tableWHERE condition(s)ORDER BY column, expr, alias ASC|DESC;1、使用WHERE 子句SELECT employee_id, last_name, job_id, department_idFROM employeesWHERE department_id = 90 ;2、字符串和日期SELECT last_name, job_id, department_idFROM employeesWHERE last_name = Whalen;3、使用比较条件SELECT last_name, salaryFROM employeesWHERE salary =10000AND job_id LIKE %MAN%;9、使用OR 操作SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary = 10000OR job_id LIKE %MAN%;10、使用NOT 操作SELECT last_name, job_idFROM employeesWHERE job_id NOT IN (IT_PROG, ST_CLERK, SA_REP);11、ORDER BY 子句SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY hire_date ;12、降序排序SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY hire_date DESC ;13、用列别名排序SELECT employee_id, last_name, salary*12 annsalFROM employeesORDER BY annsal;14、多列排序SELECT last_name, department_id, salaryFROM employeesORDER BY department_id, salary DESC;二、从多表中显示数据SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column1 = table2.column2;1、 等值连接SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_idFROM employees, departmentsWHERE employees.department_id = departments.department_id;2、 使用表别名SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e , departments dWHERE e.department_id = d.department_id;3、 非等值连接SELECT e.last_name, e.salary, j.grade_level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;4、 外连接SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column(+) = table2.column;SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column = table2.column(+);SELECT e.last_name, e.department_id, d.department_nameFROM employees e, departments dWHERE e.department_id(+) = d.department_id ; 5、自连接SELECT worker.last_name | works for | manager.last_nameFROM employees worker, employees managerWHERE worker.manager_id = manager.employee_id ;三、组函数1、 组函数l AVG l COUNT l MAXl MIN l STDDEV l SUMl VARIANCE2、 分组查询语法SELECTcolumn, group_function(column), .FROMtableWHEREconditionGROUP BYcolumnORDER BYcolumn;四、子查询1、子查询的语法SELECTselect_listFROMtableWHEREexpr operator (SELECTselect_list FROMtable);2、使用子查询SELECT last_nameFROM employeesWHERE salary (SELECT salary FROM employees WHERE last_name = Abel);3、单行子查询SELECT last_name, job_id, salaryFROM employeesWHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141)AND salary (SELECT salary FROM employees WHERE employee_id = 143);4、多行子查询SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary ANY (SELECT salary FROM employees WHERE job_id = IT_PROG)AND job_id IT_PROG;SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary ALL (SELECT salary FROM employees WHERE job_id = IT_PROG)AND job_id IT_PROG;五、操纵数据插入数据INSERT INTOtable (column , column.)VALUES(value , value.);1、 插入新的记录行INSERT INTO departments(department_id, department_name, manager_id, location_id)VALUES (70, Public Relations, 100, 1700);2、 插入具有空值的新行INSERT INTOdepartments (department_id, department_name )VALUES(30, Purchasing);INSERT INTOdepartmentsVALUES(100, Finance, NULL, NULL);3、 插入特定日期INSERT INTO employeesVALUES (114, Den, Raphealy, DRAPHEAL, 515.127.4561, TO_DATE(2月 3, 1999, MON DD, YYYY), AC_ACCOUNT, 11000, NULL, 100, 30);4、 由另一张表拷贝新记录INSERT INTO sales_reps(id, name, salary, commission_pct) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE %REP%;数据更新UPDATEtableSETcolumn = value , column = value, .WHERE condition;1、 修改数据行UPDATE employeesSET department_id = 70WHERE employee_id = 113;UPDATE copy_empSET department_id = 110;2、 利用子查询进行修改UPDATE employeesSET job_id = (SELECT job_id FROM employees WHERE employee_id = 205), salary = (SELECT salary FROM employees WHERE employee_id = 205) WHERE employee_id = 114;删除数据DELETE FROM tableWHERE condition;1、 删除记录DELETE FROM departments WHERE department_name = Finance;DELETE FROM copy_emp;2、删除记录DELETE FROM employeesWHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE %Public%);事务1、事务提交DELETE FROM employeesWHERE employee_id = 99999;INSERT INTO departments VALUES (290, Corporate Tax, NULL, 1700);COMMIT;2、 事务回滚DELETE FROM copy_emp;ROLLBACK;六、表的创建1、表的创建CREATE TABLE schema.table (column datatype DEFAULT expr, .);CREATE TABLE table (column, column.)AS subquery;1、 创建表CREATE TABLE dept(deptno NUMBER(2), dname VARCHAR2(14),loc VARCHAR2(13);2、 使用子查询创建表CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80; 2、修改表的定义ALTER TABLE tableADD (column datatype DEFAULT expr , column datatype.);ALTER TABLE tableMODIFY (column datatype DEFAULT expr , column datatype.);ALTER TABLE tableDROP (column);1、 添加一个列ALTER TABLE dept80ADD (job_id VARCHAR2(9);2、 修改列ALTER TABLEdept80MODIFY(last_name VARCHAR2(30);3、 删除列ALTER TABLE dept80DROP COLUMN job_id; 3、删除表DROP TABLE dept80;创建表时定义约束CREATE TABLE schema.table (column datatype DEFAULT exprcolumn_constraint,.table_constraint,.);列级别约束column CONSTRAINT constraint_name constraint_type,表级别约束column,. CONSTRAINT constraint_name constraint_type (column, .),七、内置约束1、创建表时加入约束CREATE TABLE employees100( employee_id NUMBER(6), first_name VARCHAR2(20), job_id VARCHAR2(10) NOT NULL, CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID);2、空值CREATE TABLE employees200( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL);3、唯一CREATE TABLE employees300( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL, CONSTRAINT emp_email_uk UNIQUE(email);4、主键CREATE TABLE departments100( department_id NUMBER(4), department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL, manager_id NUMBER(6), location_id NUMBER(4), CONSTRAINT dept_id_pk PRIMARY KEY(department_id);5、外键CREATE TABLE employees400( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL, department_id NUMBER(4), CONSTRAINT emp_dept_fk_1 FOREIGN KEY (department_id) REFERENCES departments100(department_id), CONSTRAINT emp_email_uk_1 UNIQUE(email);八、创建视图1 创建视图CREATE OR REPLACE FORCE|NOFORCE VIEW view (alias, alias.) AS subqueryWITH CHECK OPTION CONSTRAINT constraintWITH READ ONLY CONSTRAINT constraint;1、创建简单视图CREATE VIEW empvu80 AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 80;2、创建简单视图CREATE VIEW salvu50 AS SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY FROM employees WHERE department_id = 50;3、查询视图SELECT *FROMsalvu50;4、创建复杂视图CREATE VIEWdept_sum_vu (name, minsal, maxsal, avgsal)AS SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary) FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name;2 修改视图CREATE OR REPLACE FORCE|NOFORCE VIEW view (alias, alias.) AS subqueryWITH CHECK OPTION CONSTRAINT constraintWITH READ ONLY CONSTRAINT constraint;1、修改视图（or replace）CREATE OR REPL