E The order of the rows of a relation is insignificant. A The order of the columns in a relation must go from largest to smallest. B All entries in any column must be of the same kind. D A relation may have duplicate rows. E A relation may have multiple names. A It may be unique. B It may be non-unique. C In may identify more than one row. B complex key. C multi-key. D n-key. E candidate key. B used to represent rows in relationships. C a candidate key.
D used to identify unique rows. C a candidate to be the primary key. B referential integrity. C foreign key. D candidate key. E relocated key. Answer: C Diff: 1 Page Ref: A SID is both a primary key and a foreign key. B AdvisorName is a determinant. C AdvisorID is a foreign key. D Phone is a candidate key. E Major is a candidate key. Answer: C Diff: 3 Page Ref: 41 A rule that requires that the values in a foreign key must have a matching value in the primary key to which the foreign key corresponds is called: A normalization.
B a referential integrity constraint. C a key matching constraint. D a functional dependency. E synchronization. Answer: B Diff: 3 Page Ref: 72 42 A surrogate key may be appropriate under which of the following circumstances? A The available candidate keys would produce a lot of data duplication when representing relationships. B The primary key is numeric.
C The available candidate keys would be prone to typographical errors. D The available candidate keys have little meaning to the users. A They are meaningful to the users. B They are numeric. D They are unique. E They are usually hidden on forms and reports. Answer: A Diff: 2 Page Ref: B Identity Increment. C Identity Start. D Identity Seed.
E Identity Property. A A null value can mean that the value is unknown. B A null value is ambiguous. C A null value can mean that the value is known to be blank. D A null value can mean that no value for the field is appropriate. E Null values cannot be avoided. A MedicineCode is a determinant. B MedicineName is a determinant. C Manufacturer is functionally dependent on MedicineCode. D ShelfLife is functionally dependent on MedicineCode. Answer: B Diff: 2 Page Ref: B every candidate key must not be a determinant.
C every primary key must be a surrogate key. D every determinant must be functionally dependent on the primary key. E every primary key must be functionally dependent on every determinant. Answer: A Diff: 2 Page Ref: 79 49 During the normalization process, the remedy for a relation that is not well formed is to: A create a surrogate key.
B create a functional dependency. C break it into two or more relations that are well formed. D combine it with another relation that is well formed. E convert it into a list. Answer: C Diff: 1 Page Ref: 79 50 A table that meets the requirements of a relation is said to be in which normal form? B identify all the foreign keys of a relation.
C identify all the functional dependencies of a relation. D identify all the determinants of a relation. E split the relation into two or more new relations. Answer: A Diff: 1 Page Ref: 79 52 In the normalization process, it is not necessary to: A identify all the candidate keys of a relation.
E determine if every determinant is a candidate key. Answer: B Diff: 2 Page Ref: 79 53 In the normalization process, if you find a candidate key that is not a primary key then you should: A place the columns of the functional dependency in a new relation. B make the determinant of the functional dependency the primary key of the new relation. C leave a copy of the determinant as a foreign key in the original relation. D All of the above E None of the above Answer: E Diff: 3 Page Ref: 75 54 In the normalization process, if you find a candidate key that is not a determinant then you should: A place the columns of the functional dependency in a new relation.
D All of the above E None of the above Answer: D Diff: 2 Page Ref: 79 55 In the normalization process, if you find that every determinant in a relation is a candidate key then you have determined that: A the relation is well formed.
B the relation needs to be broken into two or more new relations. C surrogate keys in the relation may not be correctly linked to other relations. D the relation needs to have foreign keys added in order to be correctly linked to other relations. E referential integrity constraints concerning the relation need to be established.
B problems changing data. C problems deleting data. B by the Relationships button on the Home command tab. C in the Relationships window. D in the Table window of the table containing the primary key. E in the Table window of the table containing the foreign key. B by entering the name of the primary key in the appropriate table in Design View. C by dragging the primary key column of one table onto the foreign key column of the other table in the Relationships window.
D by dragging the foreign key column of one table onto the primary key column of the other table in the Relationships window. E by dragging the primary key column of one table onto the primary key column of the other table in the Relationships window. Surrogate keys are convenient and may improve database performance particularly when the candidate keys are composite and have long fields in them.
Why are the values of surrogate keys normally hidden from users on forms, queries, and reports? Surrogate keys are normally hidden because they usually have no meaning to the users.
Explain the term foreign key and give an example. A foreign key creates the relationship between the tables; its key value corresponds to a primary key in a relation other than the one where the key is a primary key. Explain how primary keys and foreign keys are denoted in this book.
Primary keys are underlined and foreign keys are in italics. Define the term referential integrity constraint and give an example of one. How does the referential integrity constraint contribute to database integrity? The referential integrity constraint is a rule specifying that every value of a foreign key matches a value of the primary key. Give an example of a null value other than one from this chapter , and explain each of the three possible interpretations for that value.
The subscriber may be dead, but the date of death is unknown, and the value is appropriate, but not none. Define the terms functional dependency and determinant, using an example not from this book.
A functional dependency is a logical relationship in which the value of one item in the relationship can be determined by knowing the value of the other item. The item on the left—the one whose value is known—is called the determinant.
Explain the differences in your answers to questions 2. Define the term primary key in terms of functional dependencies. A primary key is one or more attributes that functionally determines all of the other attributes in the relation. If you assume that a relation has no duplicate data, how do you know there is always at least one primary key?
Because the collection of all the attributes in the relation can identify a unique row. How does your answer to question 2. In your own words, describe the nature and purpose of the normalization process. The purpose of the normalization process is to prevent update problems in the tables relations in the database.
The nature of the normalization process is that we break up relations as necessary to ensure that every determinant is a candidate key. Each relation will only have one theme in it. Examine the data in the Veterinary Office List—Version One in Figure see page 63 , and state assumptions about functional dependencies in that table. What is the danger of making such conclusions on the basis of sample data? For example, two owners might have pets with the same name.
Using the assumptions you stated in your answer to question 2. What attribute s can be the primary key of this relation? Attributes that can be the primary key are called candidate keys. Describe a modification problem that occurs when changing data in the relation in question 2.
Changes to owner data may need to be made in several rows. Deleting data for the last pet of an owner deletes owner data as well. Examine the data in the Veterinary Office List—Version Two in Figure see page 63 , and state assumptions about functional dependencies in that table.
Explain a modification problem that occurs when changing data in the relation in question 2. Same as 2. Apply the normalization process to the Veterinary Office List—Version One relation shown in Figure see page 63 to develop a set of normalized relations. Show the results of each of the steps in the normalization process. Is every determinant a candidate key? We can choose either candidate key as primary key.
However, it may be unreasonable to require that PetName be unique. Apply the normalization process to the Veterinary Office List—Version Two relation shown in Figure see page 63 to develop a set of normalized relations. PetName, Date. We will use OwnerPhone. If a student chooses OwnerEmail, the steps will be similar as shown in Exercise 2. What is the multivalue, multicolumn problem? What is a multivalued dependency and how is it resolved in 4NF?
Define and discuss the multivalue, multicolumn problem. Define and discuss a multivalued dependency. The multivalue, multicolumn problem is basically a one-to-many relationship embedded in a single relation.
A multivalued dependency is when a determinant is associated with a set of values. For example, suppose we want to store the names of children for each employee. This causes several problems. Duplicating data in rows will cause update anomalies. In addition, many employees may have less than three children, so many child attributes will remain blank. Suppose we hire a new employee with twelve children! Show an example of this relation for two students, one of whom has three siblings and the other of whom has only two siblings.
State the functional dependencies in this relation. Explain why this relation does not meet the relational design criteria set out in this chapter i. Some attributes are functionally dependent on a part of the composite primary key. Define and discuss 4NF and how 4NF can be used to allow a set of well-formed relations. Multi-valued dependencies are directly handled by 4NF. The multiple values for each row in the original relation are split into a separate relation with a composite key consisting of the multi-value dependency attributes.
Since StudentNumber multidetermines SiblingName, those two attributes should be placed into a new relation with the composite key StudentNumber, SiblingName , and also the attribute SiblingName should be removed from the original relation. Divide this relation into a set of relations that meet the relational design criteria that is, that are well formed. Break into two relations:. Alter question 2. See the answer to Part A of Question 2. Show an example of this relation for two students, one of whom has three siblings and the other of whom has one sibling.
Assume that each student has a single major. Based on your answer to part C, show the data changes necessary to add a second major for the second student. Explain the differences in your answers to parts C and D. Comment on the desirability of this situation. We had to add three rows in the first case—one major for each of the siblings of the student. We only had to add a single row for the student with only one sibling. You can see how the redundant data is rapidly growing. See the answer to Part F of Question 2.
Divide this relation into a set of well-formed relations. In a properly normalized relation, each row of the relation consists of a primary key value which is a determinant and attribute values which are all functionally dependent on the primary key. Thus, properly normalized relations store instances of functional dependencies, and only instances of functional dependencies. So we can say that the purpose of relations is to store instances of functional dependencies.
Here it indicates which product was sold on each line of the table. Use this table and the detailed discussion of normal forms on pages to answer the following questions.
Define 1NF. If not, why not, and what would have to be done to put it into 1NF? If this step requires you to create an additional table, make sure that the new table is also in 1NF. First Normal Form is any table that meets the definition of a relation Figure 2. Define 2NF. If not, why not, and what would have to be done to put it into 2NF? If this step requires you to create an additional table, make sure that the new table is also in 2NF.
Second Normal Form is any table that 1 is in First Normal Form, and 2 all nonkey attributes are determined by the entire primary key. Define 3NF. If not, why not, and what would have to be done to put it into 3NF?
If this step requires you to create an additional table, make sure that the new table and any other tables created in previous steps are also in 3NF. Third Normal Form is any table that 1 is in Second Normal Form, and 2 and no nonkey attributes are determined by any other nonkey attributes.
Define BCNF. If not, why not, and what would have to be done to put it into BCNF? If this step requires you to create an additional table, make sure that the new table and any other tables created in previous steps are also in BCNF. Figure shows data that Regional Labs collects about projects and the employees assigned to them. Assuming that all functional dependencies are apparent in this data, which of the following are true?
Are all the nonkey attributes if any dependent on the primary key? If so, based on which functional dependencies in part A? Is EmployeeSalary a determinant? NO Actually, for the data in Figure , it is a determinant. However, the dataset is too small to validate this determinant, and logically EmployeeSalary is not a determinant! Redesign the relation to eliminate modification anomalies.
Garden Glory is owned by two partners. They employ two office administrators and a number of full- and part-time gardeners. Garden Glory will provide one-time garden services, but it specializes in ongoing service and maintenance. Many of its customers have multiple buildings, apartments, and rental houses that require gardening and lawn maintenance services. Figure shows data that Garden Glory collects about properties and services.
Using these data, state assumptions about functional dependencies among the columns of data. Justify your assumptions on the basis of these sample data and also on the basis of what you know about service businesses. From the data it appears that there are many functional dependencies that could be defined. Some examples are:. There is simply not enough data to reply on it. Logically, it seems that we need one ID column—a surrogate key will be required here. With regard to services, it would seem likely that a given service could be given to the same property, but on different dates.
So, if we had a good determinant for property, then the last functional dependency would be true. Given your assumptions in part A, comment on the appropriateness of the following designs: 1.
The question then becomes: Which one should we keep? Finally the relationship is set up correctly. Now we can have many services even on the same date for one property.
Modify the tables from part B as necessary to minimize the amount of data duplication. Will this design work for the data in Figure ? If not, modify the design so that this data will work. State the assumptions implied by this design. This also means that a service can be applied to multiple, but different, properties on the same date. However, a property may not have the same service on the same date. All of this seems reasonable and will work with the data in Figure Now we need to check with the users.
While James River Jewelry does sell typical jewelry purchased from jewelry vendors, including such items as rings, necklaces, earrings, and watches, it specializes in hard-to-find Asian jewelry. James River Jewelry has a small but loyal clientele, and it wants to further increase customer loyalty by creating a frequent buyer program. In this program, after every 10 purchases, a customer will receive a credit equal to 50 percent of the sum of his or her 10 most recent purchases.
Figure D-1 shows data that James River Jewelry collects for its frequent buyer program. Justify your assumptions on the basis of these sample data and also on the basis of what you know about retail sales. For example, name is not a good determinant in a retail application; there may be many customers with the same name. And why is InvoiceNumber the key for data about a customer? Unfortunately, EmailAddress does not determine InvoiceNumber and therefore is not a sufficient key.
The design breaks up the themes and has a proper foreign key. However, the use of EmailAddress as a primary key may be a problem if two customers share an Email address. However, why was Phone moved? Modify what you consider to be the best design in part B to include a column called AwardPurchaseAmount.
Assume that returns will be recorded with invoices having a negative PreTaxAmount. Assume that the new table will hold data concerning the date and amount of an award that is given after a customer has purchased 10 items.
Ensure that your new table has appropriate primary and foreign keys. For example, the store sells antique dining room tables and new tablecloths. The antiques are purchased from both individuals and wholesalers, and the new items are purchased from distributors. The antiques are unique, although some multiple items, such as dining room chairs, may be available as a set sets are never broken.
The new items are not unique, and an item may be reordered if it is out of stock. New items are also available in various sizes and colors for example, a particular style of tablecloth may be available in several sizes and in a variety of colors. For example, PurchaseItem is not a good determinant in a retail application; there may be many items with the same designator. There may be many customers with the same last name. There may be many customers with the same last name and first name.
Phone will be fairly unique, and combined with FirstName may overcome the problem of two people with the same phone number being in the customer list but not necessarily—what if three students are sharing an apartment, and two of them are Bill Smith and Bill Jones?
However, this will not determine the purchase information of purchase date, etc. There may be many customers with the same last name and first name, and some of them may make a purchase on the same date. The only good thing about this is that it is starting to address the purchase information. If LastName, FirstName was unique, then customers would be limited to one purchase per day.
Same objections as above in 4, except that now customers would be limited to one of a particular item per day. For example, a customer could not purchase two antique chairs on the same day!
Finally the customer and purchase data is effectively broken up, but there is no foreign key to link the two tables. But everything else that was wrong in design 6 above is still a problem.
Moreover, by using InvoiceDate as the foreign key, we limit the customer to only one purchase! The customer can have lots of purchases, but not two of the same thing on the same day.
How does this improve the design? This is called denormalization, and is discussed in Chapter 5. Can you imagine what the data for that table would look like? The primary key problems with both tables are resolved, and now a customer can purchase as many of an item on the same date as he or she wants to! Modify columns and add additional columns as you think necessary. The main problem with the design in part C is that only one item can be included in each sale.
We have good primary and foreign keys, and now a customer can purchase as many of an item on the same date as he or she wants to and all items can be part of just one sale! This limits purchases of a particular item to one per day. This limits purchases of a particular item to one per vendor. However, there is no foreign key to link the tables. Moreover, this design still limits purchases of a particular item to one per day. However, this design still limits purchases of a particular item to one per day.
That said, this is the best design of the bunch. As discussed in part D, this is called denormalization and is discussed in Chapter 5. The primary key problems with both tables are resolved, and now the Queen Anne Curiosity Shop can purchase as many of an item on the same date as needed.
The relations in your design from part D and part F are not connected. Modify the database design so that sales data and purchase data are related. The connection between the two parts of the database design is the item being first purchased and then sold. There is no section of The Access Workbench associated with this appendix.
However, with Windows 10, Microsoft has introduced a continuous update system that has already resulted in some fundamental differences in how different versions of Windows 10 look and operate. For example, in the original version of Microsoft Windows 10, clicking the Windows Start button or pressing the Windows key on the keyboard displayed the menu shown in Figure 1. In this menu, we need to click the All apps button in order to see the All apps menu shown in Figure 2.
One of the changes introduced in the Anniversary Update was a major change to the menu system. Now, as shown in Figure 3, the All apps menu is immediately available when the Start button is used or when the keyboard Windows key is pressed.
0コメント