DB DESIGN CASE--DAYTON ART GALLERY

Dayton Art Gallery accepts works by living contemporary artists to be sold on a commission basis. It currently offers work from about 100 artists and sells approximately 1000 pieces each year. The average selling price is several thousand dollars. There are about 5,000 customers who have purchased pieces from the gallery. The sales staff consists of the gallery owner, Bob Smith, and four sales associates. Their activities are supported by an office staff of two people.

Basic Operations

When an artist wishes to sell one or more works, he or she contacts the gallery. Bob Smith, the owner, visits the artist’s studio and selects the works to be sold through the gallery, working with the artist to set an asking price for each piece. The sales staff attempts to sell the work at that price, but customers may negotiate with salespeople, so that the actual selling price may be below the asking price. In that case, the final selling price must be approved by the artist. The commission charged by the gallery is 10 percent of the selling price. The gallery splits the commission with the salesperson who makes the sale. Any salesperson can sell any work in the gallery. However, customers work with a single salesperson when they buy each piece, so that the salesperson’s portion of the commission for a single piece goes to only one employee.

The gallery promotes the works by holding showings featuring various pieces. The showings are advertised in newspapers and other media, and preferred customers are sent personal invitations. A showing provides an opportunity for the public to see the pieces and to meet the artist or artists whose works are featured. Works of art that have been featured at a showing remain on display until they are sold or returned to the artist. A piece may be purchased at the showing or any time afterward. Occasionally, a work may be purchased from the gallery prior to the show and included in the exhibit to provide the public with a better view of the artist’s work.

Information Needs

At present, all data relating to artists, unsold works, sales, and customers are kept in separate files. The files have the following data:

Unsold Work: Title, Artist, Type, Medium, Style, Size, Asking Price, Date of Show. Artist: Name, Address, Phone, SoSecNo., Usual Type, Usual Medium, Usual Style, Sales Last Year, Sales Year to Date. Sale: Title, Artist, Cust Name, Cust Add, Date Sold, Salesperson, Selling Price. Customer: Cust Name, Cust Add, Cust Phone, Amt Bought Last Year, Amt Bought Year To Date

The Unsold Work file keeps track of the works currently on exhibit, and is used by salespeople. Each work must have a title, which is unique to the artist but may not be totally unique to the gallery. For example, many artists may have works such as “Composition Number 5,” but no artist has two works with that title. Each work is by a single artist. The type refers to the type of work, which may be painting, sculpture, collage, and so on. The medium refers to the material use in the work, such as oil, watercolor, marble, mixed, and so on. A piece using more than one medium is categorized as mixed. The style means the style of the work which may be contemporary, impressionist, folk, and so on. The size is expressed in units appropriate for the work; for example, for a painting, the size would be the number inches in height and width. Service the amount of sales for each artist. Sales Last Year is a dollar figure giving the total amount of sales for that artist last year, and Sales Year To date gives the total amount of sales so far this year. Information is kept about the usual type, medium, and style of each artist, where these words have the same meanings as in the Unsold Work file. The Customer file keeps track of customers who have made purchases, and includes information about the dollar amount of their purchases last year and so far this year. When a purchase is made, a receipt is made out for the customer,

1

a payment check and stub are made out for the artist, the commission is allocated between the gallery and the salesperson, and all files are updated individually.

Bob realizes that a database would provide more information from the stored data than is available now. He also wants to capture data not presently stored. In addition, he foresees that the gallery may begin to accept works owned by collectors as well as works directly from artist. Your database design should include the possibility that the owner is not the artist, storing both artist data and owner data. He would like to hire you as a consultant to design and implement a database that the office staff can maintain. The database should be capable of producing the following reports:

1. Active Artists: Lists data about each artist, including Artist’s Name, Address, Telephone, Usual Type, Usual Medium, Usual Style, Last Year’s Sales, This Year’s Sales.

2. Collector Owners: Lists data about each work, including Owner’s Name, Address, Telephone, Last Year’s Sales, This Year’s Sales.

3. Works for Sale: Lists data about each work, including title, Artist, Type, Medium, Style, Owner’s Name, Asking Price, Data of Showing.

4. Sales This Week: Lists data about all sales of works during the current week. Divided by sales person, it shows Salesperson Name. For each work sold by that person, it shows Selling Price, Title, Artist, Owner, Customer Name, Customer Address, Date of Sale.

5. Customers: In alphabetical order by name, shows Customer Name, Address, Telephone, Dollar Amount of Last Year’s Purchases, Dollar Amount of This Year’s Purchases.

It should print receipts such as the following:

6. Customer Receipt: Date of Sale, Name, Address, Title, Artist, Type, Medium, Style, Size, Selling Price, Salesperson.

A payment stub for the artist or owner should be printed:

7. Payment: Owner Name, Owner Address, Artist Name, Owner Social Security Number, Title, Type, Medium, Style, Size, Salesperson, Selling Price, amount Remitted.

   Bob would like to target potential customers as well as present ones, by making lists of all those who attend showings or whose names are gathered in other ways. For each present and potential customer, he would like to keep the identifying data now in the customer file, and add information about the customer’s preferences, such as the name of a preferred artist, type, medium, and style. He hopes to increase sales and hold down costs by using this information to make up targeted invitation lists for showings of works that match customer preferences. For example, he would like to be able to get a report such as the following:

8. Preferred Customer Report: Artist, Title, Type, Medium, Style. For each person who might be interested in the work, it lists Customer Name, Customer Address, Preferred Artist, Preferred Type, Preferred Media, Preferred Style.

   Here, the value for artist, type, medium, and style of the piece may match some of the values of the customer preferences, and the gallery can send private invitations to those customers. Bob would also

2

like to be able to combine artist and works data, making lists of all the works, whether sold or unsold, of a particular artist, in a report such as the following:

9. Artist Report: Lists Artist Name, Artist Address. For each work by that artist, it shows Title, Type, Medium, Style, Asking Price, Selling Price, Date Sold.

If a work has been sold, there will be values for Selling Price and Date Sold. Unsold works will have blanks in these areas. He would like a similar report for owners and their works, as follows:

10. Owner’s Report: Shows Owner Name, Owner Address. For each work owned, it lists Artist Name, Title Type, Medium, Style, Asking Price, Selling Price, Date Sold.

Bob might also like reports on the sales of individual salespeople for the current quarter or the past year:

11. Salesperson Performance Report: Lists Report Starting Date, Report Ending Date, Salesperson, Total Sales This Period. For each work sold by that person, it shows Title, Artist, Asking Price, Selling Price.

This report would be generated for a period starting with whatever date is selected (e.g., January first of the current year) and ending with another selected date (e.g., today’s date). It provides an individual listing of each of the works sold by that person during the period, as well as his or her total sales for the period chosen.

REQUIREMENTS

Use the E-R Approach to design an AIS for Dayton Art Gallery so that Bob Smith can get all the information he needs concerning the Sales Cycle. You need to define all the relevant entities, identify the relevant attributes for each master/transaction file, show the primary key and any foreign key(s) of each entity, and define the cardinalities. Also, you need to indicate each entity as a Master (M) or Transaction (T) file.

additional reference/example to help solve this:

Define all the relevant attributes including the PK and FK(s), if any, for the Rental Car Reservation DB Systems.

ENTITY	TYPE	M/T	Attributes
car	Objects	M	VIN, Lic#, make, model, year, mileage, color, # of doors, last oil change, last tire rotation, condition remark, last rental date, .....
customer	People	M	Cust#, Name, contact (for corporate account), CC# (optional), email, phone#, address, special terms, balance, last rental date, ....
reservation	Events	T	Resv#, Cust#, VIN, P-date, P-time, R-date, R-time, special needs, special offers, est. amount, ....
Check-out	Events	T	Contract#, Cust#, VIN, (actual) P-date, (actual) P-time, R-date, R-time, special needs, special offers, est. amount, CC# (required), condition at return, .....