Adama Science and Technology University
School of Electrical Engineering and Computing
Department of Software Engineering
Corse name :database
Corse code :SEng2208
Name ID
Besufikad Balcha UGE/27679/14
Abdisa Reta UGE/ 27671/14
Yared Solomon UGE/24110/13
Noah Getachew UGE/27722/14
Date of submission : October 16 2023
• Exercise one: Nahom Records has decided to store information about musicians
who perform on its albums (as well as other company data) in a database. The
company has wisely chosen to hire you as a database designer (at your usual
consulting fee of 600Birr/day).
·Each musician that records at Notown has an SSN, a name, an address, and a
phone number.
· Each instrument that is used in songs recorded at Nahom has a name (e.g.,
guitar, synthesizer, flute)
• Each album that is recorded on the Nahom label has a title, a copyright date, a
format(e.g., CD, VCD, DVD), and an album identier.
• Each song recorded at Nahom has a title and an author.
Each musician may play several instruments, and a given instrument may be
played by several musicians.
• Each album has a number of songs on it, but no song may appear on more than
one album.
• Each song is performed by one or more musicians, and a musician may perform
a number of songs.
• Each album has exactly one musician who acts as its producer. A musician may
produce several albums, of course.
• Design a conceptual schema for Nahom and draw an ER diagram for your
schema. Be sure to indicate all key and cardinality constraints and any
assumptions that you make. Identify any constraints that you are unable to
capture in the ER diagram and briefly explain why you could not express them.
Answer :
Conceptual Schema for Nahom Records
Entities:
1. Musician (SSN, Name, Address, PhoneNumber)
2. Instrument (Name)
3. Album (Title, CopyrightDate, Format, AlbumID)
4. Song (Title, Author)
Relationships:
1. Musician plays Instrument (M:N)
2. Album contains Song (1:M)
3. Song is performed by Musician (M:N)
4. Album is produced by Musician (1:1)
Key Constraints:
1. Musician: SSN (Primary Key)
2. Instrument: Name (Primary Key)
3. Album: AlbumID (Primary Key)
4. Song: Title (Primary Key)
Cardinality Constraints:
1. Musician plays Instrument: Many Musicians can play Many
Instruments
2. Album contains Song: One Album can contain Many Songs,
but a Song can only be on One Album
3. Song is performed by Musician: Many Songs can be
performed by Many Musicians, and Many Musicians can
perform Many Songs
4. Album is produced by Musician: One Album is produced by
One Musician, and a Musician can produce Many Albums
Assumptions:
1. Each musician has a unique SSN.
2. Each instrument has a unique name.
3. Each album has a unique AlbumID.
4. Each song has a unique title.
5. A musician can only play one instrument at a time.
6. A song is performed by at least one musician.
7. An album is produced by at least one musician.
Constraints that cannot be captured in the ER diagram:
1. A musician cannot play the same instrument on the same
album twice.
2. A song cannot be performed by the same musician on the
same album twice.
TABLE:
Key Cardinality
Entity Attributes Constrai Constraint
nts s
SSN, Musicia
SSN
Name, n plays
Musicia (Prim
Address, Instrum
n ary
PhoneNum ent
Key)
ber (M:N)
Musicia
Name
n plays
Instrum (Prim
Name Instrum
ent ary
ent
Key)
(M:N)
Title, Albu
Album
CopyrightD mID
contain
Album ate, (Prim
s Song
Format, ary
(1:M)
AlbumID Key)
Song is
Title
perform
Title, (Prim
Song ed by
Author ary
Musicia
Key)
n (M:N)
ER Diagram
Place Telephone
Phone no
address
Home
Album identifier
name speed
Copyright date
Lives
ssn t
Musicians Producer
Album
Appears
Perform
Plays
Instrument id key
Instrument
Songs
Author
Song id
Name
title
Explanation of ER Diagram:Explanation of ER Diagram:
The ER diagram for the Nahom Records database consists of four entities:
Musician, Instrument, Album, and Song. The entities are connected by four
relationships: Musician plays Instrument, Album contains Song, Song is performed
by Musician, and Album is produced by Musician.
The Musician entity has the following attributes: SSN, Name, Address, and
PhoneNumber. The SSN attribute is the primary key of the Musician entity. This
means that each musician must have a unique SSN.
The Instrument entity has the following attribute: Name. The Name attribute is
the primary key of the Instrument entity. This means that each instrument must
have a unique name.
The Album entity has the following attributes: Title, CopyrightDate, Format, and
AlbumID. The AlbumID attribute is the primary key of the Album entity. This
means that each album must have a unique AlbumID.
The Song entity has the following attributes: Title and Author. The Title attribute is
the primary key of the Song entity. This means that each song must have a unique
title.
The Musician plays Instrument relationship is a many-to-many relationship. This
means that a musician can play many instruments, and an instrument can be
played by many musicians.
The Album contains Song relationship is a one-to-many relationship. This means
that an album can contain many songs, but a song can only be on one album.
The Song is performed by Musician relationship is a many-to-many relationship.
This means that a song can be performed by many musicians, and a musician can
perform many songs.
The Album is produced by Musician relationship is a one-to-one relationship. This
means that an album is produced by exactly one musician, and a musician can
produce many albums.
