The Queen Anne Curiosity Shop sells both antiques and current-production

The Queen Anne Curiosity Shop sells both antiques and current-production household items that complement or are useful with the antiques. 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 store’s customers include individuals, owners of bed-and-breakfast operations, and local interior designers who work with both individuals and small businesses. 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).

A. Create a sample list of purchased inventory items and vendors, and a second list of customers and sales. Your first list should include inventory data such as a description, manufacturer and model (if available), item cost, and vendor identification and contact data you think should be recorded. The second list should include customer data you think would be important to The Queen Anne Curiosity Shop, along with typical sales data.

B. Describe problems that are likely to occur when inserting, updating, and deleting data in these spreadsheets.

C. Attempt to combine the two lists you created in part A into a single list. What problems occur as you try to do this?

D. Split the spreadsheets you created in part A into tables such that each has only one theme. Create appropriate ID columns.

E. Explain how the tables in your answer to part D will eliminate the problems you identified in part B.

F. What is the relationship between the tables you created from the first spreadsheet and the tables you created from the second spreadsheet? If your set of tables does not already contain this relationship, how will you add it into your set of tables?

Garden Glory is a partnership that provides gardening and yard

Garden Glory is a that provides gardening and yard maintenance services to individuals and organizations. 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 2-34 shows data that Garden Glory collects about properties and services.

A. 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.

B. Given your assumptions in part A, comment on the appropriateness of the following designs:

C. Suppose Garden Glory decides to add the following table:

SERVICE-FEE (PropertyID, ServiceID, Description, Amount)

Add this table to what you consider to be the best design in your answer to part B. Modify the tables from part B as necessary to minimize the amount of data duplication. Will this design work for the data in Figure 2-31? If not, modify the design so that this data will work. State the assumptions implied by this design.

The James River Jewelry Project Questions are available online for

The James River Jewelry Project Questions are available online for Appendix D, which can be downloaded from the textbook’s Web site: . The solutions for these questions will be included in the Instructor’s Manual for each chapter]

James River Jewelry is a small jewelry shop. While James River Jewelry does sell typical jewelry purchased form jewelry vendors, including such items as rings, necklaces, earrings, and watches, it specializes in hard-to-find Asian jewelry. Although some Asian jewelry is manufactured jewelry purchased from vendors in the same manner as the standard jewelry is obtained, many of the Asian jewelry pieces are often unique single items purchased directly from the artisan who created the piece (the term “manufactured” would be an inappropriate description of these pieces). It 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. This credit must be applied to the next (or “11th”) purchase.

Figure D-1 shows data that James River Jewelry collects for its frequent buyer program.

A. 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 retail sales.

B. Given your assumptions in part A, comment on the appropriateness of the following designs:

C. Modify what you consider to be the best design in part B to include a column called AwardPurchaseAmount. The purpose of this column is to keep a balance of the customers’ purchases for award purposes. Assume that returns will be recorded with invoices having a negative PreTaxAmount.

D. Add a new AWARD table to your answer to part C. 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.

Regional Labs is a company that conducts research and development

Regional Labs is a company that conducts research and development work on a contract basis for other companies and organizations. Figure 2-33 shows data that Regional Labs collects about projects and the employees assigned to them.

This data is stored in a relation (table) named PROJECT:

PROJECT (ProjectID, EmployeeName, EmployeeSalary)

A. Assuming that all functional dependencies are apparent in this data, which of the following are true?

B. What is the primary key of PROJECT?

C. Are all the nonkey attributes (if any) dependent on the primary key?

D. In what normal form is PROJECT?

E. Describe two modification anomalies that affect PROJECT.

F. Is ProjectID a determinant? If so, based on which functional dependencies in part A?

G. Is EmployeeName a determinant? If so, based on which functional dependencies in part A?

H. Is (ProjectID, EmployeeName) a determinant? If so, based on which functional dependencies in part A?

I. Is EmployeeSalary a determinant? If so, based on which functional dependencies in part A?

J. Does this relation contain a transitive dependency? If so, what is it?

K. Redesign the relation to eliminate modification anomalies.

Consider a table named ORDER_ITEM, with data as shown in

Consider a table named ORDER_ITEM, with data as shown in Figure 2-26. The schema for ORDER_ITEM is:

ORDER_ITEM (OrderNumber, SKU, Quantity, Price)

Where SKU is a “Stocking Keeping Unit” number, which is similar to a part number. Here it indicates which product was sold on each line of the table. Note that one OrderNumber must have at least one SKU associated with it, and may have several. Use this table and the detailed discussion of normal forms of pages 88-89 to answer the following questions.

A. Define 1NF. Is ORDER_ITEM in 1NF? If not, why not, and what would have to be done to put it into 1NF? Make any changes necessary to put ORDER_ITEM into 1NF. If this step requires you to create an additional table, make sure that the new table is also in 1NF.

B. Define 2NF. Now that ORDER_ITEM is in 1NF, is it also in 2NF? If not, why not, and what would have to be done to put it into 2NF? Make any changes necessary to put ORDER_ITEM into 2NF. If this step requires you to create an additional table, make sure that the new table is also in 2NF.

C.

Define 3NF. Now that ORDER_ITEM is in 2NF, is it also in 3NF? If not, why not, and what would have to be done to put it into 3NF? Make any changes necessary to put ORDER_ITEM 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.

D.

Define BCNF. Now that ORDER_ITEM is in 3NF, is it also in BCNF? If not, why not, and what would have to be done to put it into BCNF? Make any changes necessary to put ORDER_ITEM 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.

Alter question 2.40 to allow students to have multiple majors.

  1. Alter question 2.40 to allow students to have multiple majors. In this case, the relational structure is:

STUDENT (StudentNumber, StudentName, SiblingName, Major)

A. 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.

B. Show the data changes necessary to add a second major for only the first student.

C. Based on your answer to part B, show the data changes necessary to add a second major for the second student.

D. Explain the differences in your answers to parts B and C. Comment on the desirability of this situation.

E. Divide this relation into a set of well-formed relations.

1Write the SQL statements necessary to remove the PET_OWNER table

1Write the SQL statements necessary to remove the PET_OWNER table from the database. Assume that the referential integrity constraint is to be removed. Do not run these commands in an actual database!

PET_OWNER (OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)

PET (PetID, PetName, PetType, PetBreed, PetDOB, OwnerID)

Write and run the SQL statements necessary to create the

Write and run the SQL statements necessary to create the tables and their referential integrity constraints.
  • For the database solutions for the Review Questions about the Art-Course-Database, see the list, data and database files supplied and use:
  • Microsoft Access:
  • DBC-e07-Art-Course-Database-CH03-AppE.accdb
  • SQL Server 2014 Express Edition:
  • DBC-e07-MSSQL-Art-Course-Database-Create-Tables.sql
  • DBC-e07-MSSQL-Art-Course-Database-Insert-Data.sql
  • DBC-e07-MSSQL-Art-Course-Database-SQL-Queries-CH03.sql
  • NOTE: Create a database diagram for the database
  • Oracle Database 11g Release 2:
  • DBC-e07-ODB-Art-Course-Database-Create-Tables.sql
  • DBC-e07-ODB-Art-Course-Database-Insert-Data.sql
  • DBC-e07-ODB-Art-Course-Database-SQL-Queries-CH03.sql
  • MySQL 5.6:
  • DBC-e07-MySQL-Art-Course-Database-Create-Tables.sql
  • DBC-e07- MySQL -Art-Course-Database-Insert-Data.sql
  • DBC-e07- MySQL -Art-Course-Database-SQL-Queries-CH03.sql

Write an SQL statement to add three new rows to

Write an SQL statement to add three new rows to the PET_OWNER table. Assume that OwnerID is a surrogate key and that the DBMS will provide a value for it. Assume, however, that you have only LastName, FirstName, and Phone and that therefore Email is NULL. Use the last three lines of the data provided in Figure 3-19.

PET_OWNER (OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)

PET (PetID, PetName, PetType, PetBreed, PetDOB, OwnerID)

Write SQL Statements to (1) create the BREED table, (2)

Write SQL Statements to (1) create the BREED table, (2) insert the data in Figure3-20 into the BREED table, (3) alter the PET_3 table so that PetBreed is a foreign key referencing BreedName in BREED, and (4) to display the last name, first name, and email of any owner of a pet that has an AverageLifeExpectancy value greater than 15 using a subquery.

PET_OWNER (OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)

PET (PetID, PetName, PetType, PetBreed, PetDOB, OwnerID)