Relational-Database Design:Pitfalls in Relational-Database Design.

By -

Pitfalls in Relational-Database Design

Before we continue our discussion of normal forms, let us look at what can go wrong in a bad database design. Among the undesirable properties that a bad design may have are:

• Repetition of information

• Inability to represent certain information

We shall discuss these problems with the help of a modified database design for our banking example: In contrast to the relation schema used in Chapters 3 to 6, sup- pose the information concerning loans is kept in one single relation, lending, which is defined over the relation schema

Lending-schema = (branch-name, branch-city, assets, customer-name, loan-number, amount)

Figure 7.1 shows an instance of the relation lending (Lending-schema). A tuple t in the lending relation has the following intuitive meaning:

• t[assets] is the asset figure for the branch named t[branch-name].

• t[branch-city] is the city in which the branch named t[branch-name] is located.

• t[loan-number] is the number assigned to a loan made by the branch named t[branch-name] to the customer named t[customer-name].

• t[amount] is the amount of the loan whose number is t[loan-number].

Suppose that we wish to add a new loan to our database. Say that the loan is made by the Perryridge branch to Adams in the amount of $1500. Let the loan-number be L-31. In our design, we need a tuple with values on all the attributes of Lending- schema. Thus, we must repeat the asset and city data for the Perryridge branch, and must add the tuple

image

to the lending relation. In general, the asset and city data for a branch must appear once for each loan made by that branch.

The repetition of information in our alternative design is undesirable. Repeating information wastes space. Furthermore, it complicates updating the database. Sup- pose, for example, that the assets of the Perryridge branch change from 1700000 to 1900000. Under our original design, one tuple of the branch relation needs to be changed. Under our alternative design, many tuples of the lending relation need to be changed. Thus, updates are more costly under the alternative design than under the original design. When we perform the update in the alternative database, we must ensure that every tuple pertaining to the Perryridge branch is updated, or else our database will show two different asset values for the Perryridge branch.

That observation is central to understanding why the alternative design is bad. We know that a bank branch has a unique value of assets, so given a branch name we can uniquely identify the assets value. On the other hand, we know that a branch may make many loans, so given a branch name, we cannot uniquely determine a loan number. In other words, we say that the functional dependency

branch-name → assets

holds on Lending-schema, but we do not expect the functional dependency branch- name → loan-number to hold. The fact that a branch has a particular value of assets, and the fact that a branch makes a loan are independent, and, as we have seen, these facts are best represented in separate relations. We shall see that we can use functional dependencies to specify formally when a database design is good.

Another problem with the Lending-schema design is that we cannot represent directly the information concerning a branch (branch-name, branch-city, assets) unless there exists at least one loan at the branch. This is because tuples in the lending relation require values for loan-number, amount, and customer-name.

One solution to this problem is to introduce null values, as we did to handle updates through views. Recall, however, that null values are difficult to handle, as we saw in Section 3.3.4. If we are not willing to deal with null values, then we can create the branch information only when the first loan application at that branch is made.

Worse, we would have to delete this information when all the loans have been paid.

Clearly, this situation is undesirable, since, under our original database design, the branch information would be available regardless of whether or not loans are currently maintained in the branch, and without resorting to null values.

Comments

Post a Comment

Popular posts from this blog

Database System Architectures:Parallel Systems.

By -
Image
Parallel Systems Parallel systems improve processing and I/O speeds by using multiple CPUs and disks in parallel. Parallel machines are becoming increasingly common, making the study of parallel database systems correspondingly more important. The driving force behind parallel database systems is the demands of applications that have to query extremely large databases (of the order of terabytes — that is, 1012 bytes) or that have to process an extremely large number of transactions per second (of the or- der of thousands of transactions per second). Centralized and client – server database systems are not powerful enough to handle such applications. In parallel processing, many operations are performed simultaneously, as opposed to serial processing, in which the computational steps are performed sequentially. A coarse-grai n parallel machine consists of a small number of powerful processors; a massivel y parallel or fine-grai n parallel machine uses thousands of smaller processors....
Read more

DATABASE DESIGN -2 part2

By -
Image
Multi-valued Dependencies and Fourth Normal Form (a) The EMP relation with two MVDs: ENAME —>> PNAME and ENAME —>> DNAME. (b) Decomposing the EMP relation into two 4NF relations EMP_PROJECTS and EMP_DEPENDENTS. (c) The relation SUPPLY with no MVDs is in 4NF but not in 5NF if it has the JD(R1, R2, R3). (d) Decomposing the relation SUPPLY into the 5NF relations R1, R2, and R3.   Multi-valued Dependencies and Fourth Normal Form Definition: A multi-valued dependency (MVD) X —>> Y specified on relation schema R, where X and Y are both subsets of R, specifies the following constraint on any relation state r of R: If two tuples t1 and t2 exist in r such that t1[X] = t2[X], then two tuples t3 and t4 should also exist in r with the following properties, where we use Z to denote (R - (X υ Y)): t3[X] = t4[X] = t1[X] = t2[X]. t3[Y] = t1[Y] and t4[Y] = t2[Y]. t3[Z] = t2[Z] and t4[Z] = t1[Z]. An MVD X —>> Y in R is called a trivial MVD if (a) Y is a subset...
Read more

Database System Architectures:Network Types

By -
Image
Network Types Distributed databases and client – server systems are built around communication networks. There are basically two types of networks: local-are a networks and wide- are a networks . The main difference between the two is the way in which they are distributed geographically. In local-area networks, processors are distributed over small geographical areas, such as a single building or a number of adjacent buildings. In wide-area networks, on the other hand, a number of autonomous processors are distributed over a large geographical area (such as the United States or the en- tire world). These differences imply major variations in the speed and reliability of the communication network, and are reflected in the distributed operating-system design. Local-Area Networks Local-are a network s ( LAN s) (Figure 18.10) emerged in the early 1970s as a way for computers to communicate and to share data with one another. People recognized that, for many enterprises, numerous small...
Read more