数据库复习完整重难点.doc

Database Management System (DBMS) a security system (authorized users) 安全性 an integrity system (consistency of stored data) 完整性 a concurrency control system (shared access) 并发控制 a recovery control system (when H/S failure) 恢复 a user-accessible catalog (descriptions of data ) 数据字典 数据模型三要素 Data structure 数据结构 Data manipulation 数据操纵 Constraints of data integrity 数据的完整性约束 Entity Integrity 实体完整性 (Candidate) keys must be unique. 码值唯一 In a relation, no attribute of a primary key can be null. 基本表主码的诸属性均不能为空 Referential Integrity 参照完整性 外码取参照码值或取空值 PRIMARY KEY (Book_Id), FOREIGN KEY (BorrowerMemNo) REFERENCES MEMBER(MemNo), INSERT INTO MEMBER (MemNo, Memname, PhoneNum) VALUES (101, Susan Jones, 412-376-8888); Division selects the attributes specified on an attribute_list from a table r, while discarding the rest. Six basic operators 书名不会有两个 List the member numbers of all library member who likes all books written by ‘Thomas’. Title(CallNumber, ISBN, Name, Author) Like(MemNo, CallNumber) (Like)÷πCallNumberσAuthor=‘Thomas’(Title)) SELECT MemNo FROM Member M WHERE NOT EXISTS ( (SELECT CallNumber FROM Title WHERE Author=Thomas) EXCEPT (SELECT CallNumber FROM Likes L WHERE M.MemNo=L.MemNo)); Character Strings Fixed length n CHAR(n) or CHARACTER(n) 定长串 不足充空 space Varying length of maximum n VARCHAR(n) or CHAR VARYING (n) 变长串 不足截断 VARCHAR2(n) ORACLE用 to_date(2013/03/08 , ‘yyyy/mm/dd ) PRIMARY KEY (LIBSSN, Name), The unique key word specifies each row must have unique values -- specify that a set of columns may serve as an alternate key. 备选码 唯一码 CREATE TABLE Title ( CallNumber VARCHAR(40), Name VARCHAR(200), ISBN VARCHAR(40), Year INT, Publisher VARCHAR(80), PRIMARY KEY (CallNumber), UNIQUE (ISBN) //备选码备选码 ); DROP TABLE MEMBER CASCADE; DROP TABLE MEMBER RESTRICT; ALTER TABLE table-name ALTER 修改字段数据类型 ALTER TABLE LIBRARIAN ALTER Salary NUMERIC(6,2); ALTER TABLE LIBRARIAN ALTER COLUMN Salary TYPE N