Normalization Problem 1

A. Data stored about customers and their record club purchases.

  • {Cust-No + Name + Addr + Interest-Code + Interest-Desc +{Rec-Id + Title + Performer + Price + Date-Ordered}}

Assume the following:

  • Rec-Id and Interest-Code are unique.
  • If you know the Interest-Code you can determine Interest-Desc.
  • If you know the Rec-Id you can determine Title, Performer.
  • All customers pay the same price if ordered on the same day.
  • Prices vary from day to day.
  • A customer may order the same record more than once but not on the same day.

B.

STU_NUM / STU_NAME / NUM_CRDTS / ADV_NUM / ADV_NAME / CRS_NUM / CRS_DESCRIP / CRS_GRADE
101 / Kermit Frog / 69 / 1002 / Erik Sand / CS342 / Advanced Access / A
101 / Kermit Frog / 69 / 1002 / Erik Sand / ENG312 / Business Communication / B
102 / Oscar Grouch / 73 / 1005 / Dennis Gash / CS342 / Advanced Access / C
104 / Fozzy Bear / 14 / 1002 / Erik Sand / ENG102 / Freshman Comp II / C
104 / Fozzy Bear / 14 / 1002 / Erik Sand / GER101 / Beginning German I / B
105 / Big Bird / 130 / 1007 / Michael Mac / THEA201 / Drama Performance / A
105 / Big Bird / 130 / 1007 / Michael Mac / ENG312 / Business Communication / C

Key to attribute names:

STU_NUM = student number, STU_NAME= student name, NUM_CRDTS = number of credits earned, ADV_NUM = advisor number, ADV_NAME = advisor name, CRS_NUM = course number, CRS_DESCRIP = course description, CRS_GRADE = grade earned.

Goals:

  • Given the table above, normalize the table so that all resulting tables are in 3NF.

Normalization Problem 2

ABC MANUFACTURING

CUSTOMER ORDER AND PRODUCT APPLICATION

The ABC Manufacturing company has a completely automated application system. The system, however, resides on index files and does not allow for decision support at all. In order to move to ad hoc queries, and "what if" queries, the company has decided to convert the existing system to a database.

Initially, the only criterion for the application is to replace the existing system with a database system. No ad hoc screen or reports have been anticipated. You will see the reports and screens that exist currently.

Use this case to perform the task that the module has asked of you.

Customer Order and Product Application Considerations

1.Each customer must be on file before an order can be placed. The name, address(s), phone number(s), and credit limit must be recorded. All other data items are optional. If there is no shipping address, then the mailing address is used instead. Since customers can have identical names, a customer id has been assigned to each customer.

2. Each order will have a computer generated id number. The order can have up to 10 line items. Discounts can be given to preferred customers and this discount amount will be recorded on the customer's record. Customers without a discount amount will not be given a discount.

3.Each product listed on the order will show the standard price for that product. Discounts will be shown at the bottom of the order form.

4.Orders that can be filled or partially filled are shipped immediately, and the product data is updated accordingly. Orders, or partial orders that cannot be filled will be backordered.

5.As products are manufactured the product data is updated accordingly along with the part inventory data.

6.A customer can place numerous orders. Products can be ordered by many different customers. The same part can be used in numerous products. (eg. a screw can be used in a chair, bar stool etc.)

Normalization Problem 3

A. Data that records information on order.

  • {Order-No + Cust-No + Name + {Prod-No + Prod-Name + Price + Qty}}

Assume the following:

  • Prod-No and Cust-No are unique.
  • Prices are the same for all customers.
  • If you know the Prod-No, you can determine the Prod-Name.
  • If you know the Cust-No, you can determine the Name.

B. Gofar Travel Vehicles

Gofar Travel Vehicles sells new and used recreational vehicles. When new vehicles arrive at Gofar Travel Vehicles from the manufacturer, a new vehicle record is created. Included in the new vehicle record is the following information: vehicle identification number (VIN), name, model, year, name of manufacturer, and cost or amount paid to the manufacturer..

When a customer arrives at Gofar Travel Vehicles, he/she works with a salesperson to discuss a vehicle purchase. The customer can purchase a new or used vehicle. On the new vehicle the customer can add options like a microwave, special lighting, fridge, stove, better seats etc.

When the purchase has been agreed to, a sales invoice is completed by the salesperson. The invoice summarizes the details of the purchase. It will include all customer information, information on the vehicle being purchased and any options (if any), information on the trade-in vehicle and the trade-in dollar amount allowed (if a trade in exists). If the customer requests dealer-installed options, they will be listed on the invoice as well as the price. The invoice also summarizes the final price, plus any applicable taxes (7%) and license fees. The transaction concludes with a customer signature on the sales invoice.

Customers are assigned a customer ID when they make their first purchase from Gofar Travel Vehicles. Name, address, and phone number are recorded for the customer. If there is a trade-in vehicle it is described by a serial number, make, model, and year. Dealer installed options are described by an option code, description, cost from the manufacturer and selling price.

Each invoice will list just one customer and one vehicle sold. It is rare but if a customer wants 2 vehicles then it requires two invoices be prepared. A person does not become a customer until they purchase a vehicle. Over time, a customer may purchase a number of vehicles from Gofar Travel Vehicles.

Every invoice must be filled out by only one salesperson. A new salesperson may not have sold any vehicles, but experienced salespeople have sold many vehicles.

A customer may decide to have no options added to the vehicle, or may choose to add many options. The optional equipment (stove, fridge, fire extinguisher) is stored in the warehouse. An option like a fire extinguisher can be installed on different types of vehicles.

A customer may trade in only one vehicle toward the purchase of a new vehicle. The trade in vehicle may be sold later to another customer, who later trades it in on another Gofar Travel Vehicle. The same vehicle over time can be sold several times.

1) Design the database to handle the above.

There are assumptions to be made.

For this case the selling price is the price the product sells for. There is no negotiating a lower price. This applies to options and the vehicle.

2) What changes to the design would you make if the price shown was a suggested price, but the vehicle could be sold for some other negotiated price.

Normalization Problem 4

A. The following unnormalized data that describe customer's end use of the power (heating, hot water, etc) for a number of electrical utilities.

  • {Cust-No + Name + Addr + Util-No + Util-Name + {End-Use-Code + End-Use-Desc + Year + KWH-Used}}

Assume the following:

  • A customer can have the same end use for different years (e.g., can have electric heating in both 1994 and 1995).
  • KWH-Used is the actual consumption for the customer for the specific end use for the specific year.
  • Util-No and End-Use-Code are unique.

B. Data stored about patients and drugs prescribed to them.

  • {Patient-Id + Name + Addr + {Rx# + Trademark-Drug + Generic-Drug + Date}}

Assume the following:

  • Rx# are unique - never a duplicate for any reason.
  • Trademark-Drug is unique.
  • A refill of a Rx has a new Rx#.
  • If you know Trademark-Drug you can determine Generic-Drug.

Normalization Problem No. 5

B. To do: Put the following fields into 3NF relations.

Field / Description
CustID / Customer id
Name / Customer’s name
First name / Customer’s first name
Last name / Customer’s last name
Address / Customer’s address
BigCust / Contains “yes” if this is a big customer of ours; “no” otherwise
MedCust / Contains “yes” if this is a medium customer of ours; “no” otherwise
SmallCust / Contains “yes” if this is a small customer of ours; “no” otherwise
City / City in which the customer lives
State / State in which the customer lives
Zip / ZIP code of customer’s address
PhoneNos / List of phone numbers at which customer can be reached
OwnerID / ID of the customer who owns the stock
Stock1 / Name of first stock that customer owns
Price1 / Current price of stock1
Dividend1 / Most recent dividend of stock1
Shares1 / Number of shares of stock1 owned by customer
Sold1 / Contains “yes” if sold, “no” if not
SellDate1 / Contains date stock was sold or is blank if not
SellPrice1 / Contains the price at which the stock was sold
Value1 / Value of customer’s holdings in stock1
Stock2 / Symbol of second stock that customer owns
Price2 / Current price of stock2
Dividend2 / Most recent dividend of stock2
Shares2 / Number of shares of stock2 owned by customer
Sold2 / Contains “yes” if sold, “no” if not
SellDate2 / Contains date stock was sold or is blank if not
SellPrice2 / Contains the price at which the stock was sold
Value2 / Value of customer’s holdings in stock2
Stock3 / Symbol of third stock that customer owns
Price3 / Current price of stock3
Dividend3 / Most recent dividend of stock3
Shares3 / Number of shares of stock3 owned by customer
Sold3 / Contains “yes” if sold, “no” if not

Normalization No. 6

Here we are presented with the following form. Note that one section can have only one professor, one professor can teach more than one section, a student can only have one major, several courses can have the same course title, and professors can share an office.

UM BusinessSchool
Class list
Fall semester 1999
Course: BIT 320 / Section: 14
Title: Databases and Information
Instructor ID: NF / Name: Form, Norma L.
Instructor office: D3248
Student # / Name / Major / Grade
38214 / Bright / IS / A
40875 / Cortez / CS / B
51893 / Edwards / IS / A
  1. Perform all the steps of the normalization process. What is the primary key of this relation?
  2. How many primary keys are there?
  3. How many fields are there in the primary key(s)?
  4. Find a partial key dependency in this relation.
  5. Find a transitive dependency in this relation.
  6. Put the this information into 3NF; that is, go through step 12. State any assumptions you have to make.
  7. Now how many primary keys are there?
  8. How many tables are there?

Normalization Problem 7

The following data stores information on construction projects and the employees and equipment associated with them.

  • {Proj-No + Proj-Desc + Mgr-No + Mag-Name + {Emp-No + Emp-Name + Union-Code + Union-Desc} + {Equip-No + Equip-Desc + Time-Period}}

Assume the following:

  • An employee can be assigned to more than one project at any one time. A piece of equipment may be assigned to the same project more than once but not on the same time period (defined by the "Time-Period" attribute).
  • A piece of equipment can be assigned to different projects but not during the same time period.
  • Mgr-No, Emp-No, Union-Code, and Equip-No are all unique.
  • Employees belong to a single union no matter which projects they are assignment to.

Normalization Problem 8

The following data describes information stored about people applying to a graduate school.

  • {Application-No + Name + Street + City + State + Zip-Code + Applic-Date + {Reference-Name + Reference-Addr + Reference-Statement} + {Prior-School-Id + Prior-School-Addr + GPA}}

Assume the following:

  • Applications are for a single application period.
  • An applicant can only apply once during the application period.
  • Reference-Name and Reference-Addr together are unique (but are not unique as separate attributes).
  • Prior-School-Id uniquely idetifies a university or college.
  • An applicant has only one GPA from any specific prior school.
  • References may write a reference statement for more than one applicant. However, the reference statement for different applicants will likely different.
  • Zip codes determines city and state.