Problem 1.
Suppose A is the primary key of table T shown below and C allows only values of 1 or 2.
In a Word or Powerpoint file, draw a complete table T by filling a value of 1 or 2 in column C such that T is third normal form (3NF). Values of A and B columns cannot be changed.
Hint: there could be more than one answer. You earn credits as long as your answer satisfies the requirement.
Problem 2.
Use the same table T given in Problem 1 and repeat the problem except filling three values, each is still either 1 or 2, in C column so T is a second normal form (2NF) but not 3NF. Draw another T table in the same Word or Powerpoint file to include your answers. Be sure you use a clear index to indicate it's a table for Problem 2.
Problem 3.
The following figure shows a screen shot of a sales order form of Carbon River Furniture, a fictitious company. When a customer places an order to buy one or multiple furniture items, one of sales persons enters data on the form to complete an order. Customers may have multiple orders now and then. Every order has a unique order number and every item's number is also unique because that represents one of the company's products. However, the same item could appear in more than one order of same or different customers. Although line numbers or sequence numbers are not shown on the form, they should be considered to easily retrieve and identify each item in an order. Each sales person and customer also have a unique ID because names can duplicate. All data entered are supposed to be stored in a database.
In this problem, you are required to create a diagram in the same Word or Powerpoint file to design a database to support Carbon River's order processing. Your design should meet the following requirements.
(1) This diagram would be similar to the AP database on p.317, except do not overlap or cross any line segments for relationships. Every relationship should be drawn independently. On p.317, it's confusing to read the relationships between GLAccounts and InvoiceLineItems and Vendors.
(2) The diagram uses the notations of Chapter 10 (e.g., p.313 or p.317). Using a pair of symbols for min and max of a multiplicity (as seen in lecture) is not necessary.
(3) Your initial design may use many-to-many relationships but the one you submit for grading must be refined or modified to contain no many-to-many relationships.
(4) Primary key of each table should either be printed in bold face or append "(pk)" as a suffix.
(5) Every entered data on the form, inlcuding the sales person IDs, customer IDs, and line numbers or sequence numbers must be included in your design. Other attributes or fields are not expected.
(6) Every table must be a 3NF.
Hint: to help you verify your design, there should be five tables in total.