Musical Instrument Dealer: You operate a business that buys, sells, and repairs bowed musical instruments such as violins, violas, cellos, and acoustic basses. You consider

anybody who either sells an instrument from you or buys one from you to be a customer. Most of the stock you deal in are mass-produced student or orchestra models, but a few are extremely expensive antique instruments. Due to your volume of business, you are moving from paper record-keeping to a relational database.

For each customer, you want the new system to store a customer title (Dr., Prof., Mr., Ms., etc.), first name, middle name/initial, last name, address information, phone number, and e-mail address.

For each instrument you deal with, you want to store its type (violin, cello, etc.), manufacturer, year of manufacture, model (if any), and serial number (if any). Most modern producers of instruments assign a model name and serial number to each instrument they make, but some small artisanal and older classic instrument producers do not. Of the thousands of instruments that pass through your business each year, a very small percentage are classic instruments that have provenance information describing their history. When an instrument has such information, you want the database to store a provenance summary that you will enter in a 255-character text field.

You also want to track transactions involving each instrument. It is common for you to sell an instrument (for example, to a student) and then buy it back again. For each transaction, you need to record a code indicating whether it was a purchase or a sale, which instrument was involved, which customer was involved, the date, and the price.

Sometimes, you perform minor repair work on instruments using your own in-house staff. For each repair, you want to record which instrument was involved, the date, and a description of the repair.

Design a database to hold all this information. You may create synthetic keys as necessary or convenient. If possible, avoid having blank provenance fields for instruments not needing provenance information.