Relational Database Design

Abstract

One of the key activities of an IT department is database design, which is part of the wider activity of software analysis and design. Since the quality of the actual database depends, to a large extent, on the quality of its design, it is important that the methodology and algorithms used are known to be correct with respect to the requirements under consideration. One of the advantages of using relational database systems is that they have a conceptually simple tabular format which is easy to understand. The well-known normal forms, which are formally presented in Section 4.4, give the database designer unambiguous guidelines in deciding which databases are “good” in the quest to avoid “bad” designs that have redundancy problems and update anomalies, which are discussed in Section 4.1. The central idea in relational database design is that all the integrity constraints in the database should be describable in terms of keys and foreign keys. As was shown in Section 3.6 of Chapter 3, keys and foreign keys are just special cases of more general classes of data dependencies, i.e. FDs and INDs, respectively. The classical normal forms considered in Section 4.4 all result in a vertical decomposition of the database. That is, assuming that the decomposition R is lossless this corresponds to being able to recover a relation r over R by projecting r onto R, resulting in a database πschema(R_j) (R) with R_i ∈ R, and then joining the projections. In Section 4.5 we consider the possibility of a horizontal decomposition of a relation schema R, resulting in splitting R into two or more union-compatible relation schemas, i.e. schemas having the same attribute set. In this case a relation r over R will be split into two or more disjoint relations using one or more selection operations and can then be recovered by applying the union operator.