云南大学软件学院数据库系统设计期中复习

数据库系统设计期中复习整理第一章1.数据独立性：Physical data independence : Physical level can change without having to change the logical level. Logical data independence : Logical level can change without having to change the external（外部的）level.2.数据库管理系统架构3.ACID(1)原子性（Atomicity）：一个事务中的所有操作要么全部成功，要么全部失败。

原子性由恢复机制实现。

(2)一致性（Consistency）：事务完成后，所有数据处于应有的状态，所有内部结构正确，能够准确反映事务所作的工作。

基于隔离性实现。

(3)隔离性（Isolation）：一个事务不会干扰另一个事务的进程，事务交叉调度执行的结果与串行调度执行的结果是一致的。

隔离性由并发控制机制实现。

(4)持久性（Durability）：事务提交后，对数据库的影响是持久的，即不会因为系统故障影响事务的持久性。

持久性由恢复机制实现。

4.CAP(1)Consistency 一致性强一致性强一致性（即时一致性）假如A先写入了一个值到存储系统，存储系统保证后续A,B,C的读取操作都将返回最新值弱一致性假如A先写入了一个值到存储系统，存储系统不能保证后续A,B,C的读取操作能读取到最新值。

此种情况下有一个“不一致性窗口”的概念，它特指从A写入值，到后续操作A,B,C读取到最新值这一段时间。

最终一致性最终一致性是弱一致性的一种特例。

假如A首先write了一个值到存储系统，存储系统保证如果在A,B,C后续读取之前没有其它写操作更新同样的值的话，最终所有的读取操作都会读取到A写入的最新值。

此种情况下，如果没有失败发生的话，“不一致性窗口”的大小依赖于以下的几个因素：交互延迟，系统的负载，以及复制技术中replica的个数（这个可以理解为master/salve模式中，salve的个数），最终一致性方面最出名的系统可以说是DNS系统，当更新一个域名的IP以后，根据配置策略以及缓存控制策略的不同，最终所有的客户都会看到最新的值。

(2)Availability 可用性（指的是快速获取数据）(3)Tolerance to network Partitions 分区容忍性（分布式）5.BASE(1)Basically Available --基本可用(2)Soft-state --软状态/柔性事务(3)Eventual Consistency --最终一致性BASE模型反ACID模型，完全不同ACID模型，牺牲高一致性，获得可用性或可靠性：Basically Available基本可用。

支持分区失败(e.g. sharding碎片划分数据库) Soft state 软状态状态可以有一段时间不同步，异步。

Eventually consistent最终一致，最终数据是一致的就可以了，而不是时时一致。

6.练习(1)What are the five components of a DBMS?HardwareSoftwareDataProceduresInstructions and rules that should be applied to the design and use of the database and DBMS.PeopleIncludes database designers, DBAs, application programmers, and end-users.(2)DBMS的功能Data Storage, Retrieval, and Update.A User-Accessible Catalog.Transaction Support.Concurrency Control Services.Recovery Services.Authorization Services.Support for Data Communication.Integrity Services.Services to Promote Data Independence.Utility Services.(3)Briefly describe the two-tire Client/Server architecture and three-tire Client/Serverarchitecture.Client side presented two problems preventing true scalability:●‘Fat’ client, requiring considerable resources on client’s computer to run effectively.●Significant client side administration overhead.Three-tire Client/Server●‘Thin’ client, requiring less expensive hardware.●Application maintenance centralized.●Easier to modify or replace one tier without affecting others.●Separating business logic from database functions makes it easier to implement loadbalancing.●Maps quite naturally to Web environment.第二章1．Stages of database system development lifecycle1)Database planning2)System definition3)Requirements collection and analysis4)Database design5)DBMS selection (optional)6)Application design7)Prototyping (optional)8)Implementation9)Data conversion and loading10)Testing11)Operational maintenance.第三章1. 行列表2. 范式第一范式：A table is said to be in first normal form (1NF) when each field in that table contains single values only.第二范式：第三范式：3. 数据库的种类Hierarchical: Represents a database as a tree-structured hierarchy, similar to the folder system on a computer.Network: Represents a database as a network of connected tables. The major difference between a network and a relational database is that the relational database has foreign keys to make connections between tables, whereas the network database uses physical pointers to connect tables.Relational: A relational database is the easiest system in which to develop and maintain a database.Object oriented: The first type of database to allow programs (methods), and not just data, to be stored in the database. They were also the first databases to allow data objects to inherit structures from other data objects. Over time, relational products have become more object oriented—incorporating both stored procedures (programs) and inheritance—thereby eliminating the competitive advantage of object-oriented systems.4. 关系数据库的优点●Reliance on logical, rather than physical, links between related records●Use of a fourth-generation language (4GL)●Allowance for a high degree of data independence第四章1. Database with Many-to-Many Relationships: Enrollment Database(1)Step 1: TablesThree main entities:Instructors, Students, and Courses(2)Step 2: Relationships2.1 One instructor teaches multiple courses2.2 A course can be taught by different instructors each time it is offered.2.3 Model the many-to-many with a new associative table: SECTION.2.4 Students actually enroll in sections;one student enrolls in multiple sections2.5 Each section may also have multiple students enrolled.2.6 Model the many-to-many relationship with a new associative table: ENROLL(3)Step 3: FieldsData from problem statement(4)Step 4: KeysDetails on primary and foreign key creation to be covered after relationships4.1 Choose a PK for a parent – id for INSTRUCTOR4.2 Reproduce the PK as a FK in the child –INSTRUCTOR$id for SECTION4.3 Choose a PK for a parent – code for COURSE4.4 Reproduce the PK as a FK in the child – COURSE$code in SECTION4.5 Choose a PK for a parent – call_no for SECTION4.6 Reproduce the PK as a FK in the child – SECTION$call_no in ENROLL4.7 Choose a PK for a parent – id for STUDENT4.8 Reproduce the PK as FK in the child – STUDENT$id in ENROLL4.9 Choose a PK for the remaining associative table – STUDENT$id and SECTION$call_no for ENROLL(5)Step 5: Data TypesLength and type of data第五章1. 更新异常2. 消除更新异常3. 插入异常4. 消除插入异常5. 删除异常6.消除删除异常7.范式1NF: A table in which all fields contain a single value.2NF: A table in which each non-key field is determined by the whole primary key and not part of the primary key by itself.3NF: A table in which none of the non-key fields determine another non-key field.BCNF:Every determinant is a key.4NF: In an all-key table, part of the key can determine multiple values of, at most, one other field.。