Lab 3 – Due Tuesday, February 14, 20006

This assignment is to be submitted as a group assignment: 1 copy per group.

I’d prefer that you type it onto this page but if you have already written a significant amount, you may finish on the page you started in class as long as it is legible. Each group member must sign and the signature is an indication that they actually worked on it outside of class.

Group members: ______KEY______

Note that there are additional copies of the handout from class outside my office door in the bin closest to the fire extinguisher.

In class we established that there are the following input documents needed.

  1. Artist information form
  2. Collector (i.e. buyer) information form
  3. Art information form
  4. Sale Invoice
  5. Mailing List form

For each of the above, list the information that needs to be included (i.e. the attributes of the entitities OR show the design of a form to hold the information needed.

Artist information form –

  1. date of interview (AND interviewer)
  2. artist name (preferably first AND last separately)
  3. address (preferably street, city, state, zip separately)
  4. telephone number (area code AND number)
  5. social security number
  6. usual type
  7. usual medium
  8. usual style

Collector (i.e. buyer) information form

  1. date of interview (AND interviewer)
  2. collector name (preferably first AND last separately)
  3. address (preferably street, city, state, zip separately)
  4. telephone number (area code AND number)
  5. social security number
  6. collection type
  7. collection medium
  8. collection style
  9. preferred artist (first AND last names)

Art information form

  1. artist name
  2. title
  3. year completed
  4. type
  5. medium
  6. style
  7. size
  8. owner (if other than artist )
  9. date listed
  10. asking price

Sale Invoice

  1. invoice number
  2. title of work
  3. artist (last name AND first name)
  4. owner (last name and first name AND related data)
  5. buyer (last name AND first name AND related data)
  6. Price
  7. tax
  8. total paid
  9. salesperson
  10. date

Mailing List form

  1. date
  2. last name AND first name
  3. address (street, city, state, zip)
  4. telephone (area code, number)
  5. possible preferences (artist, style, type, medium)

Next indicate the routine reports to be produced using the database. Again, you may do it by naming them and showing the attributes (information) to be included in each OR by drawing a form to hold the desired information. These are the output documents needed.

Active Artists Summary report

  1. name, address, phone, type, medium, style, sales Last Year, Sales YTD

Individual artist sales report

  1. Personal information
  2. Works Sold (title, date listed, type, medium, style, year, asking price, selling price, date sold, total sales)
  3. Works Returned (title, date listed, type, medium, style, year, asking price, selling price,date returned)
  4. Works For Sale (title, date listed, type, medium, style, year, asking price)

Collectors summary report

  1. name, address, phone, pref. artist, pref. medium, pref. style, sales last year, sales YTD

Individual Collector Sales Report

  1. personal information
  1. Works Sold (title, date listed, type, medium, style, year, asking price, selling price, date sold, total sales)
  2. Works Returned (title, date listed, type, medium, style, year, asking price, selling price,date returned)
  3. Works For Sale (title, date listed, type, medium, style, year, asking price)

Works for Sale

  1. date of report
  2. title, artist, type, medium, style, owner, asking price, date shown, date listed

Sales for week ending

  1. date
  2. salesperson, artist, title, owner, buyer, sale date, selling price, commission

Buyers sales report

  1. date
  2. last name, first name, address, phone, total purchases last year
  3. purchases this year (date purchased, artist title, asking price, selling price, total purchases this year)

Preferred Customer Report

  1. artist, title, type, medium, style, customer name, address,pref. artist, pref. type, pref. medium, pref style

Salesperson Performance Report

  1. report start date
  2. report end date
  3. salesperson name, address, SSN, artist, title, asking price, selling price, date sold, sales for period, commission for period)

Artworks held over six months

  1. owner name, owner telephone, artist name, title, date listed, asking price

Art Gallery Payment Stub

  1. owner name, address, ssn,
  2. artist name, work title, type, medium, style, size
  3. salesperson
  4. selling price, tax, total amount of sale
  5. amount remitted to owner

Art Show report

  1. title of show
  2. opening date
  3. closing date
  4. featured artist or theme
  5. works included
  6. artist, title, asking price, status

Lastly, Make a list of assumptions for the Art Gallery Project. (for this you want to include items that are explicitly indicated in the handout as well as those that you think make logical sense (are implied).

  1. Artist names are unique but customer names and collector names are not
  2. for privacy reasons, only people who receive payments from gallery provide their social security numbers
  3. an artist might have many works for sale in the gallery
  4. each work is an original, one of a kind piece (no prints or reproductions)
  5. two works of art can have the same title, but combination of title and artist name is unique
  6. a work of art may be owned by artist or another person (collector)
  7. even if a collector owns the work, the artist’s information is important
  8. a work of art is sold by the gallery only once, the gallery does not re-sell its own works
  9. a work of art might be in more than one show – some works might not be in a show
  10. payment for all sales is made immediately and in full at purchase time
  11. database does not include payroll information except for commission
  12. there are lists of valid values for type, style and medium (and also OTHER)
  13. lists of artists, collectors, buyers, and potential customers are evaluated periodically to determine whether they should be dropped.