任选数据库(Oracle, MySQL, SQL Server)，设计一个小型数据库应用程序。
Requirement
In this assignment, you are asked to design a small database system, create and populate this database using ORACLE, MySQL or SQL Server, etc. and write a few application programs to access the database. The topic of the assignment is to design the information system that the owner of the gallery Newark Modern Art can use to manage his art gallery and his employees. In the following, you are given the requirements for the database design (Section 1) and the requirements for application design (Section 2).
GENERAL GUIDELINES
The following guidelines apply for the assignment:
- The assignment is to be done in groups of two.
- You are required to demonstrate your programs. You should treat these demonstrations as if you were giving them to your customer. So, prepare them professionally. The demonstrations will take place at the end of the term, after classes are over. We will put up a sign-up sheet soon.
- You are required to submit a typed report at the end of the process. This report should minimally cover (a) a summary of the system requirements and any additions you may have made, (b) the entity-relationship design, (c) the (relational) logical database design, and (d) the application program design. For each of these, you should identify the major design decisions that you faced and the design decisions that you made with justifications for those decisions. Also include, as an appendix, a list of the relational instances you have used to populate your database.
- To assist you along the way, I will be available to check your designs. We have set up the milestones:
- The final report is due the last day of class.
- Please note that you are not required to hand in anything after assignment 4; but you can make an appointment to see me to discuss your designs or any doubt you may have.
- Please note that the end of the term is quite tight. In order to avoid problems, start your works as soon as possible.
- Grading will be done as follows (in addition to the assignment4 grade):
- (a) design report: 40%;
- (b) application programs: 60%. The report must be typed and should be written clearly. The presentation (language and communication of ideas) of the report is very important. Have it read by someone before you submit if you wish. Later on, we will post a recommended outline for the report in the newsgroup.
- In general, each member of a group will be assigned the same grade. However, if I notice that one member of the group is doing all (or most of) the work, I reserve the right to assign differential grades.
DATABASE DESIGN REQUIREMENTS
Newark Modern Art accepts original artworks by living contemporary artists to be sold on a commission basis. It currently offers work from about a hundred artists and sells approximately a thousandpieces each year. Theaveragesellingprice is severalthousanddollars. Thereareabout five thousand customers who have purchased pieces from the gallery. The sales staff consists off the gallery owner, and four sales associates. Their activities are supported by an office staff of two people.
Basis Operations
When an artist wishes to sell works, he or she contacts the gallery. The owner of the gallery, visits the artist’s studio and selects the works to be sold through the gallery. If the artist is well known to the gallery, this visit may be eliminated, and the works may be accepted automatically. An artist may submit one or several pieces for sale at a time. The artist, working with the owner, identifies an asking price for each work. The sales staff tries to sell the work at that price, or as close to that price as possible. Customers may negotiate with salespeople, so that the actual selling prince may be below the asking price. If it is below the asking price, the final selling price must be approved by the artist. The commission charged by the gallery is 10% 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 salesperson.
Newark Modern Art promotes the works by holding exhibits featuring various pieces. The exhibits are advertised in newspaper and other media, and potential customers are sent personal invitations. A showing is actually a reception that provides an opportunity for the public to see the pieces and to meet the artist or artists whose works are features. A “one-man show” features works by a single artist, while a theme show features works by multiple artists centered on a single theme, such as “Mediterranean Seascapes.” Works of art that have been featured at a showing remain on display until they are sold or returned to the artists. A piece may be purchased at the showing or at any time afterward. Occasionally, a work may be purchased from the gallery prior to the show and included in the exhibit, marked as” Sold,” to provide the public with a better view of the artist’s work. Not all works are promoted through showings. Some are simply displayed in the gallery. If a work has been at the gallery for six months without being sold, the owner of the gallery contacts the artist and returns the work, unless both agree to continue displaying the work for an additional period of time.
At present, all data relating to artists, unsold works, shows, sales, and customers is kept in paper files. A description card is made up for each work currently on exhibit and placed on the wall or floor stand next to the piece. A copy of the card is also placed in a file. The card lists the artist’s price. Each work is an original, one-of-a-kind piece produced by a single artist. No two artists have the same name. The title of the work must be 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. No prints or reproductions are sold at the gallery. An artist can produce several works in the same year. The type refers to the type of work, which may be painting, sculpture, collage, and so forth. The medium refers to the materials used in the work, such as oil, watercolor, acrylic, marble, or mixed. 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, or other. The size is expressed in units appropriate for the work, for example, for a painting, the size would be the number of inches in width and height, while a sculpture would have three dimensions.
Information Needs
In addition to the data about artists, artworks, shows, sales, and customers currently kept paper files, there are other information needs. The owner realizes that a database could provide more information than is available now from the paper files. He also wants to capture data not currently stored. He would like to keep track of customers who have made purchases and information about the amount of their purchases last year and so far, and to record their preferences. In addition, he foresees that the gallery may begin to accept works owned by collectors as well as works directly from artists. The database design should include the possibility that the owner is not the artist.
APPLICATION DEVELOPMENT REQUIREMENTS
You are expected to write three application programs that will allow the owner of the gallery and his employees to manage customers, artworks, artists, and exhibits. You must make decisions as to the appearance of your forms. There is no standard output format.
Artwork Management
This application will be used to record and retrieve all the information about: artistes (insert,update,delete),collectors(insert,update,delete),customers(insert,update,delete)andartwork submission.
Sales
The sales application will be automating all the business transactions needed to complete the sale. When a purchase is made, a receipt is issued for the buyer, a payment check and stub are made out for the artist, the commission is allocated between Newark Modern Art and the salesperson, and all paper files are updated individually. The transactions will then include: issuing a receipt for the buyer, issuing a payment check and stub for the artist, the commission allocated to the gallery and the salesperson, and update all tables affected. This application will allow to compute and list the commissions received by a salesperson over a certain period of time.
Annual Report
For income tax purposes, Newark Modern Art is required to report the amount of sales by the gallery but also the amount of sales for each artist each year; a task that is very time consuming at present.