云南大學軟件學院數(shù)據(jù)庫系統(tǒng)設計期中復習.docx

數(shù)據(jù)庫系統(tǒng)設計期中復習整理第一章1. 數(shù)據(jù)獨立性：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. 數(shù)據(jù)庫管理系統(tǒng)架構3. ACID(1) 原子性（Atomicity）：一個事務中的所有操作要么全部成功，要么全部失敗。原子性由恢復機制實現(xiàn)。(2) 一致性（Consistency）：事務完成后，所有數(shù)據(jù)處于應有的狀態(tài)，所有內部結構正確，能夠準確反映事務所作的工作?；诟綦x性實現(xiàn)。(3) 隔離性（Isolation）：一個事務不會干擾另一個事務的進程，事務交叉調度執(zhí)行的結果與串行調度執(zhí)行的結果是一致的。隔離性由并發(fā)控制機制實現(xiàn)。(4) 持久性（Durability）：事務提交后，對數(shù)據(jù)庫的影響是持久的，即不會因為系統(tǒng)故障影響事務的持久性。持久性由恢復機制實現(xiàn)。4. CAP(1) Consistency 一致性強一致性強一致性（即時一致性） 假如A先寫入了一個值到存儲系統(tǒng)，存儲系統(tǒng)保證后續(xù)A,B,C的讀取操作都將返回最新值弱一致性假如A先寫入了一個值到存儲系統(tǒng)，存儲系統(tǒng)不能保證后續(xù)A,B,C的讀取操作能讀取到最新值。此種情況下有一個“不一致性窗口”的概念，它特指從A寫入值，到后續(xù)操作A,B,C讀取到最新值這一段時間。最終一致性最終一致性是弱一致性的一種特例。假如A首先write了一個值到存儲系統(tǒng)，存儲系統(tǒng)保證如果在A,B,C后續(xù)讀取之前沒有其它寫操作更新同樣的值的話，最終所有的讀取操作都會讀取到A寫入的最新值。此種情況下，如果沒有失敗發(fā)生的話，“不一致性窗口”的大小依賴于以下的幾個因素：交互延遲，系統(tǒng)的負載，以及復制技術中replica的個數(shù)（這個可以理解為master/salve模式中，salve的個數(shù)），最終一致性方面最出名的系統(tǒng)可以說是DNS系統(tǒng)，當更新一個域名的IP以后，根據(jù)配置策略以及緩存控制策略的不同，最終所有的客戶都會看到最新的值。(2) Availability 可用性（指的是快速獲取數(shù)據(jù)）(3) Tolerance to network Partitions 分區(qū)容忍性（分布式）5. BASE(1) Basically Available -基本可用(2) Soft-state -軟狀態(tài)/柔性事務(3) Eventual Consistency -最終一致性BASE模型反ACID模型，完全不同ACID模型，犧牲高一致性，獲得可用性或可靠性： Basically Available基本可用。支持分區(qū)失敗(e.g. sharding碎片劃分數(shù)據(jù)庫) Soft state軟狀態(tài) 狀態(tài)可以有一段時間不同步，異步。 Eventually consistent最終一致，最終數(shù)據(jù)是一致的就可以了，而不是時時一致。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/Server architecture.Client side presented two problems preventing true scalability:l Fat client, requiring considerable resources on clients computer to run effectively.l Significant client side administration overhead.Three-tire Client/Serverl Thin client, requiring less expensive hardware.l Application maintenance centralized. l Easier to modify or replace one tier without affecting others. l Separating business logic from database functions makes it easier to implement load balancing. l Maps quite naturally to Web environment.第二章1Stages 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. 數(shù)據(jù)庫的種類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 orientedincorporating both stored procedures (programs) and inheritancethereby eliminating the competitive advantage of object-oriented systems.4. 關系數(shù)據(jù)庫的優(yōu)點l Reliance on logical, rather than physical, links between related recordsl Use of a fourth-generation language (4GL)l 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 determ