Use the dependency diagram shown in Figure 6.8 to work the following problems.

4 years ago

FIGURE P6.8 Initial Dependency Diagram for Problem 8

a. Break up the dependency diagram in Figure 6.8 to create two new dependency diagrams, one in 3NF and one in 2NF.

The dependency diagrams are shown in Figure P6.8a.

Figure P6.8a The Dependency Diagram for Problem 8a

a. Modify the dependency diagrams you created in Problem 8a to produce a collection of dependency diagrams that are all in 3NF. (Hint: One of your dependency diagrams will be in 3NF, but not in BCNF.)

The solution is shown in Figure P6.8b.

Figure P6.8b The Dependency Diagram for Problem 8b

a. Modify the dependency diagrams in Problem 8b to produce a collection of dependency diagrams that are all in 3NF and BCNF.

The solution is shown in Figure P6.8c. Note that the A, C, and E attributes in the first three structures can be used as foreign keys in the fourth structure.

Figure P6.8c The Dependency Diagrams for Problem 8c

Bijay Satyal

Oct 26, 2021

1. Given the sample records in the CHARTER table shown in Table P6.11, do the following:

2. The manager of a consulting firm has asked you to evaluate a database that contains the table structure shown in Table P6.10.

3. Suppose that you have been given the table structure and data shown in Table 6.9, which was imported from an Excel spreadsheet. The data reflect that a professor can have multiple advisees, can serve on multiple committees, and can edit more than one journal.

4. Suppose you are given the following business rules to form the basis for a database design. The database must enable the manager of a company dinner club to mail invitations to the club’s members, to plan the meals, to keep track of who attends the dinners, and so on.

5. The table structure shown in Table P6.6 contains many unsatisfactory components and characteristics. For example, there are several multivalued attributes, naming conventions are violated, and some attributes are not atomic.

6. To keep track of office furniture, computers, printers, and so on, the FOUNDIT company uses the table structure shown in Table P6.5.

7. Using the STUDENT table structure shown in Table P6.4, do the following:

8. Using the INVOICE table structure shown in Table P6.3, do the following

9. Using the descriptions of the attributes given in the figure, convert the ERD shown in Figure P6.2 into a dependency diagram that is in at least 3NF.

10. Using the descriptions of the attributes given in the figure, convert the ERD shown in Figure P6.1 into a dependency diagram that is in at least 3NF.

11. A table is in 3rd _ normal form when it is in 2nd normal form and there are no transitive dependencies.

12. Suppose that someone tells you that an attribute that is part of a composite primary key is also a candidate key. How would you respond to that statement?

13. How would you describe a condition in which one attribute is dependent on another attribute when neither attribute is part of the primary key?

14. Why is a table whose primary key consists of a single attribute automatically in 2NF when it is in 1NF?

15. What is a surrogate key, and when should you use one?

16. What three data anomalies are likely to be the result of data redundancy? How can such anomalies be eliminated?

17. What is a partial dependency? With what normal form is it associated?

18. The dependency diagram in Figure Q6.8 indicates that a patient can receive many prescriptions for one or more medicines over time. Based on the dependency diagram, create a database whose tables are in at least 2NF, showing the dependency diagram for each table.

19. The dependency diagram in Figure Q6.7 indicates that authors are paid royalties for each book that they write for a publisher. The amount of the royalty can vary by author, by book, and by edition of the book.

Use the dependency diagram shown in Figure 6.8 to work the following problems.

a. Break up the dependency diagram in Figure 6.8 to create two new dependency diagrams, one in 3NF and one in 2NF.

Bijay Satyal

More related questions

1. Given the sample records in the CHARTER table shown in Table P6.11, do the following:

2. The manager of a consulting firm has asked you to evaluate a database that contains the table structure shown in Table P6.10.

3. Suppose that you have been given the table structure and data shown in Table 6.9, which was imported from an Excel spreadsheet. The data reflect that a professor can have multiple advisees, can serve on multiple committees, and can edit more than one journal.

4. Suppose you are given the following business rules to form the basis for a database design. The database must enable the manager of a company dinner club to mail invitations to the club’s members, to plan the meals, to keep track of who attends the dinners, and so on.

5. The table structure shown in Table P6.6 contains many unsatisfactory components and characteristics. For example, there are several multivalued attributes, naming conventions are violated, and some attributes are not atomic.

6. To keep track of office furniture, computers, printers, and so on, the FOUNDIT company uses the table structure shown in Table P6.5.

7. Using the STUDENT table structure shown in Table P6.4, do the following:

8. Using the INVOICE table structure shown in Table P6.3, do the following

9. Using the descriptions of the attributes given in the figure, convert the ERD shown in Figure P6.2 into a dependency diagram that is in at least 3NF.

10. Using the descriptions of the attributes given in the figure, convert the ERD shown in Figure P6.1 into a dependency diagram that is in at least 3NF.

11. A table is in 3rd _ normal form when it is in 2nd normal form and there are no transitive dependencies.

12. Suppose that someone tells you that an attribute that is part of a composite primary key is also a candidate key. How would you respond to that statement?

13. How would you describe a condition in which one attribute is dependent on another attribute when neither attribute is part of the primary key?

14. Why is a table whose primary key consists of a single attribute automatically in 2NF when it is in 1NF?

15. What is a surrogate key, and when should you use one?

16. What three data anomalies are likely to be the result of data redundancy? How can such anomalies be eliminated?

17. What is a partial dependency? With what normal form is it associated?

18. The dependency diagram in Figure Q6.8 indicates that a patient can receive many prescriptions for one or more medicines over time. Based on the dependency diagram, create a database whose tables are in at least 2NF, showing the dependency diagram for each table.

19. The dependency diagram in Figure Q6.7 indicates that authors are paid royalties for each book that they write for a publisher. The amount of the royalty can vary by author, by book, and by edition of the book.

20. Given the dependency diagram shown in Figure Q6.6, answer items 6a-6c:

21. When is a Table in BCNF?

22. When is a table in 3NF?

23. When is a table in 2NF?

24. When is a table in 1NF?

25. What is normalization?

Questions Bank

Quick Links

Website

Resources