【文章內(nèi)容簡介】 t there is an account A9732 at the Perryridge branch and that is has a balance of $ write insert into accountvalues (‘A9732’, ‘Perryridge’, 1200)In this example, the values are specified in the order in which the corresponding attributes are listed in the relation schema. For the benefit of users who may not remember the order of the attributes, SQL allows the attributes to be specified as part of the insert statement. For example, the following SQL insert statements are identical in function to the preceding one:insert into account (accountnumber, branchname, balance)values (‘A9732’, ‘Perryridge’, 1200)insert into account (branchname, accountnumber, balance)values (‘Perryridge’, ‘A9732’, 1200) UpdatesIn certain situations, we may wish to change a value in a tuple without changing all values in the tuple. For this purpose, the update statement can be used. As we could for insert and delete, we can choose the tuples to be updated by using a query.Suppose that annual interest payments are being made, and all balances are to be increased by 5 percent. We writeupdate accountset balance = balance * 蘇州大學(xué)本科生畢業(yè)設(shè)計（論文） IXThe preceding update statement is applied once to each of the tuples in account relation. If interest is to be paid only to accounts with a balance of $1000 or more, we can writeUpdate accountSet balance = balance * Where balance = 10002 RelationalDatabase DesignThis chapter continues our discussion of design issues in relational databases. In general, the goal of a relationaldatabase design is to generate a set of relation schemas that allows us to store information without unnecessary redundancy, yet also allows us to retrieve information easily. One approach is to design schemas that are in an appropriate normal form. To determine whether a relation schema is in one of the desirable normal forms, we need additional information about the realworld enterprise that we are modeling with the database. First Normal FormThe first of the normal forms that we study, first normal form, imposes a very basic requirement on relations。 unlike the other normal forms, it does not require additional information such as functional dependencies.A domain is atomic if elements of the domain are considered to be indivisible units. We say that a relation schema R is in first normal form (1NF) if the domains of all attributes of R are atomic.A set of names is an example of a monatomic value. For example, if the schema of a relation employee included an attribute child whose domain elements are sets of names, the schema would not be in first normal form.Composite attributes, such as an attribute address with ponent attributes street and city, also have monatomic domains.Integers are assumed to be atomic, so the set of integers is an atomic domain。 the set of all sets of integers is a monatomic domain. The distinction is that we do 蘇州大學(xué)本科生畢業(yè)設(shè)計（論文） Xnot normally consider integers to have subparts, but we consider sets of integers to have subparts—namely, the integers making up the set. But the important issue is not what the domain itself is, but rather how we use domain elements in our database. The domain of all integers would be monatomic if we considered each integer to be an ordered list of digits. Boyce–Codd Normal FormUsing functional dependencies, we can define several normal forms that represent “good” database designs. In this section we cover BCNF (defined below) DefinitionOne of the more desirable normal forms that we can obtain is Boyce–Codd normal form (BCNF). A relation schema R is in BCNF with respect to a set F of functional dependencies if, for all functional dependencies in F+ of the form α → β, where α ? R and β ? R, at least one of the following holds:? α → β is a trivial functional dependency (that is, β ? α).? α is a superkey for schema R.A database design is in BCNF if each member of the set of relation schemas that constitutes the design is in BCNF. As an illustration, consider the following relation schemas and their respective functional dependencies:? Customerschema = (customername, customerstreet, customercity) customername → customerstreet customercity? Branchschema = (branchname, assets, branchcity) branchname → assets branchcity? Loaninfoschema = (branchname, customername, loannumber, amount) loannumber → amount branchnameWe claim that Customerschema is in BCNF. We note that a candidate key for the schema is customername. The only nontrivial functional dependencies that hold on Customerschema have customername on the left side of the arrow. Since customername is a candidate key, functional dependencies with customername on the left side do not violate the definition of BCNF. Similarly, it can be shown easily that the relation schema Branchschema is in BCNF. The schema Loaninfoschema, however, is not in BCNF.蘇州大學(xué)本科生畢業(yè)設(shè)計（論文） XI Third Normal FormAs we saw earlier, there are relational schemas where a BCNF deposition cannot be dependency preserving. For such schemas, we have two alternatives if we wish to check if an update violates any functional dependencies:? Pay the extra cost of puting joins to test for violations.? Use an alternative deposition, third normal form (3NF), which we present below, which makes testing of updates cheaper. Unlike BCNF, 3NF depositions may contain some redundancy in the deposed schema.We shall see that it is always possible to find a losslessjoin, dependencypreserving deposition that is in 3NF. Which of the two alternatives to choose is a design decision to be made by the database designer on the basis of the application requirements? DefinitionBCNF requires that all nontrivial dependencies be of the form α → β, where α is a superkey. 3NF relaxes this constraint slightly by allowing nontrivial functional d