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

9. date of interview (AND interviewer)
10. collector name (preferably first AND last separately)
11. address (preferably street, city, state, zip separately)
12. telephone number (area code AND number)
13. social security number
14. collection type
15. collection medium
16. collection style
17. preferred artist (first AND last names)

Art information form

18. artist name
19. title
20. year completed
21. type
22. medium
23. style
24. size
25. owner (if other than artist )
26. date listed
27. asking price

Sale Invoice

28. invoice number
29. title of work
30. artist (last name AND first name)
31. owner (last name and first name AND related data)
32. buyer (last name AND first name AND related data)
33. Price
34. tax
35. total paid
36. salesperson
37. date

Mailing List form

38. date
39. last name AND first name
40. address (street, city, state, zip)
41. telephone (area code, number)
42. 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

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

Individual artist sales report

b. Personal information

c. Works Sold (title, date listed, type, medium, style, year, asking price, selling price, date sold, total sales)

d. Works Returned (title, date listed, type, medium, style, year, asking price, selling price,date returned)

e. Works For Sale (title, date listed, type, medium, style, year, asking price)

Collectors summary report

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

Individual Collector Sales Report

b. personal information

f. Works Sold (title, date listed, type, medium, style, year, asking price, selling price, date sold, total sales)

g. Works Returned (title, date listed, type, medium, style, year, asking price, selling price,date returned)

h. Works For Sale (title, date listed, type, medium, style, year, asking price)

Works for Sale

a. date of report

b. title, artist, type, medium, style, owner, asking price, date shown, date listed

Sales for week ending

a. date

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

c.

Buyers sales report

a. date

b. last name, first name, address, phone, total purchases last year

c. purchases this year (date purchased, artist title, asking price, selling price, total purchases this year)

Preferred Customer Report

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

Salesperson Performance Report

b. report start date

c. report end date

d. salesperson name, address, SSN, artist, title, asking price, selling price, date sold, sales for period, commission for period)

Artworks held over six months

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

Art Gallery Payment Stub

a. owner name, address, ssn,

b. artist name, work title, type, medium, style, size

c. salesperson

d. selling price, tax, total amount of sale

e. amount remitted to owner

Art Show report

a. title of show

b. opening date

c. closing date

d. featured artist or theme

e. works included

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