Database Management Systems (Database Design and Tuning)

Database Management Systems

Part 6: Database Design and Tuning

Chapter 19. Schema refinement and normal forms

problems caused by redundancy: redundant storage, update anomalies, insertion anomalies, deletion anomalies

functional dependency (FD): a kind of IC that generalizes the concept of a key. FD X->Y if t1.X = t2.X then t1.Y = t2.Y

  --> decomposition of a relation schema: normal forms; properties: lossless-join, dependency-preservation

FD does not require that set X be minimal; a primary key constraint is a special case of an FD, key is X and the set of all attributes is Y

FD f is implied by a given set F of FDs if f holds on every relation instance that satisfies all dependencies in F --> the set of all FDs implied by a given set F of FDs is called the closure of F, denoted as F+

how to infer/compute the closure F+: Armstrong's Axioms: sound and complete

reflexivity: if X is a superset of Y, then X->Y

augmentation: if X->Y, then XZ->YZ for any Z

transitivity: if X->Y and Y->Z, then X->Z

additional rules: union: if X->Y and X->Z, then X->YZ; decomposition: if X->YZ, then X->Y and X->Z

attribute closure X+: set of attributes A such that X->A can be inferred <-- check whether a given dependency X->Y is in the closure F+; find all candidate keys

normal forms: if a relation schema is in one of the normal forms, we know that certain kinds of problems cannot arise.

1NF, 2NF, 3NF, Boyce-Codd normal form (BCNF) <-- increasingly restrictive requirements

1NF: every field contains only atomic values, no lists or sets (implicit in the definition of the relational model)

BCNF: X is a subset of attributes, A is an attribute, for every FD X->A in F, one of the following is true: A \in X (a trivial FD) or X is a superkey

(in a BCNF relation, the only nontrivial dependencies are those in which a key determines some attributes)

BCNF ensures that no redundancy can be detected using FD information alone

3NF: for every FD X->A in F, one of the following is true: A \in X, or X is a superkey, or A is part of some key (any key if there are several) for R

every BCNF relation is also in 3NF

(finding all keys of a relation schema is known to be an NP-complete problem, and so is the problem of determining whether a relation schema is in 3NF)

X->A violates 3NF: 2 cases: (1) X is a proper subset of some key K: partial dependency, (2) X is not a proper subset of any key: transitive dependency

motivation for 3NF: by making an exception for certain dependencies involving key attributes, we can ensure that every relation schema can be decomposed into a collection of 3NF relations using only decompositions that have certain desirable properties (such a guarantee does not exist for BCNF relations, but some redundancy is possible with 3NF)

2NF: partial dependencies are not allowed

lossless-join decomposition: for every instance r of R that satisfies dependencies in F, \pi_X(r) \naturaljoin \pi_Y(r) = r, i.e. we can recover the original relation from the decomposed relations

theorem: lossless iff. F+ contains either FD R1 \intersect R2 -> R1 or the FD R1 \intersect R2 -> R2, i.e. the attributes common to R1 and R2 must contain a key for either R1 or R2

==> if an FD X->Y holds over a relation R and X \intersect Y is empty, the decomposition of R into R - Y and XY is lossless

dependency-preserving decomposition: allows to enforce all FDs by examining a single relation instance on each insertion or modification of a tuple (deletions cannot cause violation of FDs) -- (F_X \union F_Y)+ = F+

projection of F on X: set of FDs in the closure F+ that involve only attributes in X; a dependency U->V in F+ is in F_X only if all attributes in U and V are in X

if a relation schema is not in BCNF, it is possible to obtain a lossless-join decomposition into a collection of BCNF relation schemas, but there may be no dependency-preserving decomposition into a collection of BCNF relation schemas

there is always a dependency-preserving, lossless-join decomposition into a collection of 3NF relation schemas

decompose into BCNF:

(1) suppose R is not in BCNF. let X \subset R, A be a single attribute in R, and X->A be an FD that causes a violation of BCNF. decompose R into R-A and XA

(2) if either R-A or XA is not in BCNF, decompose them further by recursive application of this algorithm

==> it cannot discriminate among decomposition alternatives (depending on which dependencies we choose to guide the next decomposition step)

decompose into 3NF (omitted)

need a schema refinement step following ER design

other kinds of dependencies (omitted)

Chapter 20. Physical database design and tuning

Chapter 21. Security and authorization

three main objectives: secrecy, integrity, availability --> security policy --> security mechanisms

other factors: security leaks in OS or network, human factors

views: limit access to sensitive data; authenticate for e-commerce applications

access control: discretionary or mandatory

discretionary access control: access rights, or privileges -- allow user to access data object in certain manner (read or modify): GRANT and REVOKE in SQL

mandatory access control: systemwide policies that cannot be changed by individual users -- database object: security class; user: clearance

GRANT:

GRANT privileges ON object TO users [ WITH GRANT OPTION ]

several privileges can be specified: SELECT(read, including columns added later through ALTER TABLE), INSERT(column-name, omit for all columns), UPDATE(column-name), DELETE, REFERENCES(column-name, define foreign keys)

If a user has a privilege with the grant option, he or she can pass it to another user by using the GRANT command; a user who creates a base table has all applicable privileges on it; a user who creates a view has precisely those privileges on the view that the user has on every one of the views or base tables used to define the view.

only owner of a schema can CREATE, ALTER, and DROP -- these cannot be granted or revoked

(role-based authorization: CREATE ROLE, DROP ROLE)

privileges are assigned to authorization IDs (a single user of a group of users) -- examples in Sec 21.3

REVOKE:

REVOKE [ GRANT OPTION FOR ] privileges ON object FROM users { RESTRICT | CASCADE }

one of restrict or cascade must be specified

when a GRANT is executed, a privilege descriptor is added to a table maintained by DBMS -- grantor, grantee, granted privilege, whether grant option is included

authorization graph:

revoke should ensure: if node N has an outgoing arc labeled with a privilege, there is a path from the System node to node N in which each arc label contains the same privilege plus the grant option.

grant and revoke on views and integrity constraints: 1. a view may be dropped because a SELECT privilege is revoked from the user who created the view; 2. if the creator of a view gains additional privileges on the underlying tables, he or she automatically gains additional privileges on the view; 3. the distinction between REFERENCES and SELECT is important. (examples in Sec 21.3.1)

mandatory access control (omitted)

security for internet applications: encryption, certificates, digital signatures

role of DBA: system account --> 1. creating new accounts; 2. mandatory control issues

security in statistical databases: intend to permit only statistical queries --> challenge: it is possible to infer protected information from answers to permitted statistical queries

solution: require each query must involve at least some minimum number of rows --> better to limit on the amount of intersection permitted between queries