数据库复习完整重难点.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)CallNumberAuthor=Thomas(Title)SELECT MemNoFROM Member MWHERE NOT EXISTS (SELECT CallNumberFROM TitleWHERE Author=Thomas)EXCEPT(SELECT CallNumberFROM Likes LWHERE M.MemNo=L.MemNo);Character StringsFixed length n CHAR(n) or CHARACTER(n) 定长串 不足充空 spaceVarying length of maximum nVARCHAR(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 NUMERIC(6,2); -postgreSQLALTER TABLE table-name ALTER 修改字段数据类型ALTER TABLE LIBRARIAN ALTER Salary NUMBER(6,2); ALTER TABLE LIBRARIAN ALTER COLUMN Salary TYPE NUMBER(6,2); -postgreSQL改变字段的缺省值 ALTER TABLE DEPENDENT ALTER COLUMN kinship DROP DEFAULT;ALTER TABLE BOOK ALTER COLUMN BorrowerMemNo SET DEFAULT NULL; 增加一个不能写成表约束的非空约束ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;ALTER TABLE table-name ADD 增加字段ALTER TABLE products ADD COLUMN description text ; 增加约束 ALTER TABLE products ADD CHECK (name ); ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;解决两表相互引用问题!ALTER TABLE table-name DROP删除字段ALTER TABLE products DROP COLUMN description RESTRICT; -默认受限删除ALTER TABLE products DROP COLUMN description CASCADE; -CASCADE 授权删除任何依赖该字段的东西删除约束 ALTER TABLE products DROP CONSTRAINT constraint_name; ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;SELECT staffNo,fName,lName,salary/12 AS monthlySalaryFROM Staff; %任意个字符 _ 一个字符SELECT SSN, Name FROM LIBRARIAN WHERE Name LIKE Nick%; |连接串 SELECT Fname | MI | Lname AS Name, PhoneNumber FROM MEMBER WHERE PhoneNumber LIKE 412-6_3%; Example: Display the name and call number of all the book titles that contain 10% as part of the title name.SELECT Name, CallNumber FROM TITLE WHERE Name LIKE %10&% ESCAPE &; DELETE FROM librarianWHERE section = (SELECT sectno FROM section WHERE name = checkout);UPDATE librarianSET salary = salary*1.05WHERE section = (SELECT sectno FROM sction WHERE name = Art);An entity is an object in the real world that is uniquely identifiable and has independent existence. 实体 能被惟一标识 本身能独立存在 entity type Entities having the same properties are grouped together to form an entity type. 实体型：具有相同属性的实体Relationship 联系A relationship is a meaningful association between two or more entities. 实体型间有意义的关联cardinality ratio and participation. 基数约束 参与性约束one-to-one (1:1): 左实体集中的每一个实体，在右实体集中最多有一个实体与之相连，反之亦然。one-to-many (1:N):左实体集中的每一个实体，在右实体集中可能有多个实体与之相连；右实体集中的每一个实体在左实体集中最多只有一个实体与之相连。many-to-many (M:N):左实体集中的每一个实体，在右实体集中可能有多个实体与之相连，反之亦然。Participation The participation constraint specifies whether the existence of an entity is dependent on the existence of another entity to which it must be related.实体型的实体是否都参与了联系双线全参与Mapping AlgorithmS1：每一强实体用一个新表表示，确定主码和备选码S2：处理参与1：1标识联系的弱实体WS3：处理参与1：N标识联系的弱实体W，T中增加宿主强实体的主码为外码，T的主码为该外码W的鉴别器（部分码）。KU是完全函数依赖，则K是候选码。主属性：包含在任何候选码中的属性。关系表的每一分量是不可分的数据项范式1NF 不允许表中出现嵌套或复合的属性满足2NF的关系模式不存在非主属性对码的部分函数依赖。MEMBER_BOOK(MemNo, Book_Id, DueDate, Mname, City, CallNo, Title)函数依赖MemNoMname,MemNoCity,Book_IdCallNo, Book_IdTitle等都是非主属性对码的部分函数依赖,所以不满足2NF.方法：大关系分解为小关系(相关信息放在一个表中）。Member(MemNo,Mnam,City)Book(Book_Id,CallNo,Title)Borrow(MemNo,Book_Id,DueDate)满足2NF。满足3NF的关系模式在2NF的基础上消除了非主属性对码的传递函数依赖。Example