Database Concepts - Data Warehouse Systems: Design and Implementation - page 28

CategoryName . To make the relation comply with the third normal form, the
attributes dependent on CategoryID must be removed from the table and a
table Category like the one in Fig. 2.4 must be used to store the data about
categories.
The Boyce-Codd normal form prevents redundancies originated in
functional dependencies. A relation R is in the Boyce-Codd normal form
with respect to a set of functional dependencies F if for every nontrivial
dependency X → Y in F + , X is a key or contains a key of R .Notethatall
relations in Fig. 2.4 are in the Boyce-Codd normal form.
The fourth normal form prevents redundancies such as those in the
table EmployeeTerritories in Fig. 2.7 c. A relation R is in the fourth normal
form with respect to a set of functional and multivalued dependencies F if
for every nontrivial dependency X →→ Y in F + , X is a key or contains
akeyof R . The table above is not in the fourth normal form, since, for
example, there is a multivalued dependency from EmployeeID to KindOfWork ,
and EmployeeID is not a key of the relation. To make the relation comply with
the fourth normal form, the attribute KindOfWork must be removed from the
table, and a table EmpWork ( EmployeeID , KindOfWork ) must be added.
2.4.3 Relational Query Languages
Data stored in a relational database can be queried using different formalisms.
Two kinds of query languages are typically defined. In a procedural language,
a query is specified indicating the operations needed to retrieve the desired
result. In a declarative language, the user only indicates what she wants
to retrieve, leaving to the DBMS the task of determining the equivalent
procedural query that is to be executed.
In this section, we introduce the relational algebra and SQL, which we
will be using in many parts of this topic. While the relational algebra is a
procedural query language, SQL is a declarative one.
Relational Algebra
The relational algebra is a collection of operations for manipulating relations.
These operations can be of two kinds: unary , which receive as argument a
relation and return another relation, or binary , which receive as argument
two relations and return a relation. As the operations always return relations,
the algebra is closed , and operations can be combined in order to compute
the answer to a query. Further, another classification of the operations is as
follows. Basic operations cannot be derived from any combination of other
operations, while derived operations are a shorthand for a sequence of basic
operations, defined in order to make queries easier to express. In what follows,
we describe the relational algebra operations.