Summary Database Systems Introduction to the database systems

Summary: Database Systems • Introduction to the database systems (1) - What is a database - The main characters of a database - The basic database design method - The entity-relationship data model for application modeling • Concepts and Architecture - Database schema, Schema evolution, Database state - Working process with a database system - Database system architecture - Data independence concept Sept. 2004 Yangjun Chen 91. 3902 1

Summary: Database Systems • Data modeling using ER-model (2) - Entity-relationship model - Entity types - strong entities - weak entities - Attributes - attribute classification - Relationships among entities - Constraints - cardinality constraints - participation constraints - ER-to-Relation-mapping Sept. 2004 Yangjun Chen 91. 3902 2

Summary: Database Systems • Hashing technique (3) - external hashing - static hashing & dynamic hashing - hash function - mathematical function that maps a key to a bucket address - collisions collision resolution scheme - open addressing - chaining - multiple hashing - linear hashing Sept. 2004 Yangjun Chen 91. 3902 3

Summary: Database Systems • Multi-level index (4) - tree - root, internal, leaf, subtree - parent, child, sibling - balanced, unbalanced - b+-tree - splits on overflow; merge on underflow - in practice it is usually 3 or 4 levels deep - search, insert, delete algorithms Sept. 2004 Yangjun Chen 91. 3902 4

Summary: Database Systems • Data modeling using Relational model Relational algebra (5) - Relational Data Model - relational schema, relations - database schema, database state - integrity constraints and updating - Relational algebra - select, project, join, cartesian product - set operations: union, intersection, difference - division Sept. 2004 Yangjun Chen 91. 3902 5

Summary: Database Systems • SQL (6) - DDL - creating schemas - modifying schemas - DML - select-from-where clause - group by, having, order by - update - view Sept. 2004 Yangjun Chen 91. 3902 6

Summary: Database Systems • ER-to-Relation mapping (7) General process 1. Create a relation for each strong entity type 2. Create a relation for each weak entity type 3. For each binary 1: 1 relationship choose an entity and include the other’s PK in it as an FK 4. For each binary 1: n relationship, choose the n-side entity and include an FK wrt the other entity. 5. For each binary M: N relationship, create a relation for the relationship 6. For each multi-valued attribute create a new relation 7. For each n-ary relationship, create a relation for the relationship Sept. 2004 Yangjun Chen 91. 3902 7

Summary: Database Systems • ER-to-Relation mapping (7) 4 choices (for handling is-a relationship): 1. Create separate relations for the supertype and each of the subtypes. 2. Create relations for the subtypes only - each contains attributes from the supertype. 3. (disjoint subtypes) Create only one relation - includes all of the attributes for the supertype and all for the subtypes, and one discriminator attribute. 4. (overlapping subtypes) Create only one relation includes all of the attributes for the supertype and all for the subtypes, and one logical discriminator attribute per subtype. Sept. 2004 Yangjun Chen 91. 3902 8

Summary: Database Systems • Normalization (8) - Function dependency We say a functional dependency exists between the attributes X and Y: X Y, if, whenever a tuple exists with the value x for X, it will always have the same value y for Y. - Inference rules for function dependencies - 1 NF - First Normal Form The domain of an attribute must only contain atomic values. Sept. 2004 Yangjun Chen 91. 3902 9

Summary: Database Systems • Normalization (8) - 2 NF - Second Normal Form A relation schema is in 2 NF if (1) it is in 1 NF and (2) every non-key attribute must be fully functionally dependent on the primary key. - 3 NF - Second Normal Form A relation schema is in 3 NF if (1) it is in 2 NF and (2) each non-key attribute must not be fully functionally dependent on another non-key attribute (there must be no transitive dependency of a non-key attribute on the PK) Sept. 2004 Yangjun Chen 91. 3902 10

Summary: Database Systems • Normalization (8) - Boyce Codd Normal Form, BCNF Consider a different definition of 3 NF, which is equivalent to the previous one. A relation schema R is in 3 NF if, whenever a function dependency X A holds in R, either (a) X is a superkey of R, or (b) A is a prime attribute of R. Removing (b), we have the definition for BCNF. Sept. 2004 Yangjun Chen 91. 3902 11

Summary: Database Systems • Lossless Join (9) - Decomposition of a relation schema Emp_PROJ SSN PNUM hours ENAME PLOCATION F = {SSN ENAME, PNUM {PNAME, PLOCATION}, {SSN, PNUM} hours} R 1 R 2 SSN ENAME SSN PNUM PNAME PLOCATION R 3 Sept. 2004 hours Yangjun Chen 91. 3902 Lossless join 12

Summary: Database Systems • Lossless Join (9) - Matrix for a decomposition A 1 SSN A 2 ENAME A 3 PNUM A 4 PNAME A 5 PLOCATION A 6 hours R 1 b 12 b 13 b 14 b 15 b 16 R 2 b 21 b 22 b 23 b 24 b 25 b 26 R 3 b 31 b 32 b 33 b 34 b 35 b 36 - Matrix transformation to check the lossless join property Sept. 2004 Yangjun Chen 91. 3902 13

Summary: Database Systems • Lossless Join (9) R 1 a 2 b 13 b 14 b 15 b 16 R 2 b 21 b 22 a 3 a 4 a 5 b 26 R 3 a 1 b 32 a 3 b 34 b 35 a 6 SSN ENAME SSN ENAME R 1 a 2 b 13 b 14 b 15 b 16 R 2 b 21 b 22 a 3 a 4 a 5 b 26 R 3 a 1 a 2 a 3 b 34 b 35 a 6 Sept. 2004 Yangjun Chen 91. 3902 14

Summary: Database Systems • Lossless Join (9) PNUM {PNAME, PLOCATION} PNUM PNAME PLOCATION R 1 a 2 b 13 b 14 b 15 b 16 R 2 b 21 b 22 a 3 a 4 a 5 b 26 R 3 a 1 a 2 a 3 a 4 a 5 a 6 Sept. 2004 Yangjun Chen 91. 3902 15

Summary: Database Systems • Deductive Databases (10) - A deductive database can be defined as an advanced database augmented with an inference system. - well-formed formulas - clause and Horn-clause - Bottom-up and top-down evaluation of non-recursive queries. - Naive and semi-naive evaluation of recursive queries. Sept. 2004 Yangjun Chen 91. 3902 16

Summary: Database Systems • Hierarchical Databases (11) - Hierarchical schema - PCR relationship - PCR occurrence and hierarchical occurrence - Virtual PCR - Data definition and data manipulation in hierarchical schema Sept. 2004 Yangjun Chen 91. 3902 17