Background

CD Selections is a chain of 50 music stores located in California. Annual sales last year were $50 million, and they have been growing at about 3-5 percent per year for the past few years.

Margaret Mooney, Vice President of Marketing, has recently become both excited by and concerned with the rise of Internet sites selling CDs. The Internet has the potential to enable CD Selections to reach beyond its regional focus and significantly increase sales. However, the Internet also poses a significant threat as Web competitors such as CDnow and Amazon continue to take sales from CD Selections traditional stores.

CD Selections currently has a simple web page that provides information about the company and about each of its stores (e.g., map, operating hours, relative size).

The System

Approval has been given to create a new Internet sales system. A description of the new system is as follows:

The Internet sales system will store basic information about the CDs that it can sell over the Internet (e.g., title, artist or group, songs, id number, price, year the album was released, label, awards the album has won* (see note below)). This CD store does NOT track the various groups or solo artists on a CD when more than one group or solo artist performs on it (like a Farm Aid CD or the 60s greatest hits). Frequently one song is recorded on a variety of albums, by a number of artists or groups. This database will only track songs that are on CDs that CD Selections carries.

For business-to-business relationship reasons CD Selections wants to know the number of CDs it is selling from each record label. That is to say CD Selections wants to know how many CD titles they receive from one record label (not how many copies of each CD they receive from the label). A CD is produced by only record label (of course many CDs are produced by a specific record label). In order to re-stock the CDs CD Selection will track the record labels address, the name of their contact person and that persons phone number, and the credit rating associated with each label.

CD Selections also wants to keep information on all the artists for which it sells CDs (first name, last name, group(s) in which they play). Some artists have solo albums and perform with a number of different groups during the same time period. CD selection also wants to know all members of the various groups. Since the composition of a group can change overtime, the database should track when an artist became a member of a band and when they left the band (if they have).

Customers will access the Internet sales system to look for CDs of interest. Some customers will search for specific CDs or CDs by specific artists, while other customers want to browse for interesting CDs in certain categories (e.g., rock, jazz, classical). To facilitate this each CD is categorized as one type of music.

This database WILL NOT track inventory information or customer orders and purchases.

*Note: Although songs, groups and individuals can also receive awards CD Selections is only recording awards won by a CD.

Requirements:

• Build the conceptual model for the CD Selections database represented as an Entity Relationship Diagram using SmartDraw (or other tools) online showing entities, relationships, minimum and maximum cardinalities, identifiers and at least four attributes for each entity (including entitys identifier)
• Add associative entities when needed identifying their relevant attributes; use a minimum of two attributes per each associative entity
• You may list the assumptions in the model, if necessary. Assumptions have to be made only if they are not clearly defined in the above description. You can include the assumptions on the model using SmartDraw text tool (or other tools you wish). A list of unnecessary assumptions will be ignored