Databases Reference
In-Depth Information
For each multivalued attribute of an entity, create a table comprising the entity's pri-
mary key and the attribute.
Map the relationships to database tables
For each one-to-one relationship between two entities, include the primary key of one
entity as a foreign key in the table belonging to the other. If one entity participates
totally in the relationship, place the foreign key in its table. If both participate totally
in the relationship, consider merging them into a single table.
For each nonidentifying one-to-many relationship between two entities, include the
primary key of the entity on the “1” side as a foreign key in the table for the entity on
the “N” side. Add any attributes of the relationship in the table alongside the foreign
key. Note that identifying one-to-many relationships (between a weak entity and its
owning entity) are captured as part of the entity-mapping stage.
For each many-to-many relationship between two entities, create a new table contain-
ing the primary key of each entity as the primary key, and add any attributes of the
relationship. This step helps to identify intermediate entities.
For each relationship involving more than two entities, create a table with the primary
keys of all the participating entities, and add any attributes of the relationship.
Converting the Music Database ER Model to a Database Schema
Following the mapping rules as just described, we first map entities to database tables:
• For the strong entity
Artist
, we create the table
artist
comprising the attributes
artist_id
and
artist_name
, and designate
artist_id
as the primary key.
• For the weak entity
Album
, we create the table
album
comprising the attributes
album_id
and
album_name
, and include the primary key
artist_id
of the owning
Artist
entity as a foreign key. The primary key of the
album
table is the combination
{
artist_id
,
album_id
}.
• For the weak entity
Track
, we create the table
track
comprising the attributes
track_id
,
track_name
, and
time
, and include the primary key {
artist_id
,
album_id
} of the owning
Album
entity as a foreign key. The primary key of the
track
table is the combination {
artist_id
,
album_id
,
track_id
}.
• For the weak entity
Played
, we create the table
played
comprising the attribute
played
, and include the primary key {
artist_id
,
album_id
,
track_id}
of the owning
Track
entity as a foreign key. The primary key of the
played
table is the combination
{
artist_id
,
album_id
,
track_id
,
played
}.
• There are no multivalued attributes in our design, nor are there any nonweak re-
lationships between our entities, so our mapping is complete here.
You don't have to use consistent names across all tables; for example, you could have
a column
musician
in the
album
table that contains the artist ID that you call