Existing Database

By far, the largest constraint is the existing database. Our company has been selling this database, which is hosted in Microsoft SQL Server 2000, for the past few years. It contains hundreds of thousands of recordings with millions of tracks. For this project, the database administrators of our database do not see why they should change the schema. We currently do not have any requirements that would cause us to change the schema, but the database administrators do not want us to change it. Therefore, we will view the schema as a constraint that we have to live with for the project. If it becomes a critical issue, we might raise it again later, but with some data to back up our needs. The existing data model is shown in Figure 4-1.

LjW

PK

a

A4 me

Artist

J>K

ti_

rurne

HitLí.ijír^

PK

FKl FK2

tfHe hdeisotitf amstia l.i WÜ

Ifcdt

PK

4

Ftf FK2 FK3

<Jj-a Lililí (jcMclg grtlsifct ■eoorfii.igid

ídvfew

PK

tí.

FKl

icoot^lngftl

latlng

neview

h-

pk

ti

mine

Genie

PK

*

Mm«

Figure 4-1: Recordings data model The following sections briefly explain this data model.

Entities

The two primary entities in our data model are Recording and Track. A Recording is something that is publicly released by a music com pa ny on some type of media (compact disc, audiocassette, vinyl, and so on); this company is referred to in the model as a Label. A Track is a complete song. Each Recording also has a title and the date on which it was released. Each Track has a title and duration.

We also have a few secondary entities in our model that we use mostly to normalize the relational data: GenreThis entity represents different musical styles; for example, rock, classical, pop, hip hop, and so on. ArtistThis entity is the name of the person or group that is the performer. LabelThis entity is the company that released the recording.

Review This entity, which describes a critique of the recording, is written by a Reviewer. (Each reviewer has a name.)

Entity Relationships

Each Recording has many Tracks, one Label, one Artist, and many Reviews. Each Track has an Artist and a Genre. Each Review has one Reviewer. We have a special category of "Various Artists" to use as the artist for Recordings and Tracks performed by more than one artist. In the current model, we do not support individual track Reviews.

InFigure 4-1, the rightmost column in each table has special constraints associated with the corresponding attribute (listed in the leftmost column). For example, in the Track table, you see the PK (short for primary key) constraint associated with the trackId attribute, which means that the trackId attribute serves as the primary key in our database Track table. Similarly, the FKx(which stands for foreign key) constraint is used to describe a relationship between two entities. For example, the Track table has a foreign key constraint—FK1—associated with the genreld attribute to indicate that this attribute is used to capture an association between a Track and its Genre.

Team LiB

1 PREVIOUS

Team LiB

0 0

Post a comment