- [15 points] The software analyst of company X elicited and documented the functional requirements
below for the Notown Records project. Build the corresponding conceptual model by designing an ERD
using the Peter Chen’s notation.
REQ_01 – Musicians Registration
Rationale: A MUSICIAN is identified by an SSN and has the attribute Name.
REQ_02 – Musicians Addresses Registration
Rationale: An ADDRESS is identified by an AddressID, and has the attributes Number, Street1, Street2,
City, State, Zip Code and Telephone;
REQ_03 – Relationship between Addresses and Musicians
Rationale: Each MUSICIAN must live in a unique ADDRESS, but more than one MUSICIAN can live in
the same ADDRESS.
REQ_04 – Instruments Registration
Rationale: An INSTRUMENT is identified by a Name, and has the attribute Pitch;
REQ_05 – Relationship between Instrument and Musician
Rationale: Each MUSICIAN can play several INSTRUMENTS, and an INSTRUMENT can be played by
REQ_06 – Song Registration
Rationale: A SONG is identified by a Title, and has the attribute Length;
REQ_07 – Relationship between Instrument and Song
Rationale: Each INSTRUMENT can be used in several SONGS, and a SONG can use several
REQ_08 – Relationship between Song and Musician – Authorship
Rationale: Each SONG must have a single author (MUSICIAN), and an author can author several SONGS;
REQ_09 – Relationship between Song and Musician – Performer
Rationale: Each SONG can have several performers (MUSICIAN), and a performer can perform several
REQ_10 – Album Registration
Rationale: An ALBUM is identified by an AlbumID, and has attributes Title, Release_Date, Format, and
the calculated attribute Age.
REQ_11 – Relationship between Musician and Album – Producer
Rationale: Each MUSICIAN can produce several ALBUMS, and an ALBUM must be produced by only
REQ_12 – Relationship between Song and Album
Rationale: Each SONG must belong to only one ALBUM, and an ALBUM can have several SONGS.
- [15 points] Imagine the software development company Y has just hired you as a software analysist. Your
first job is to analyze the ERD below and write down all corresponding functional requirements (let’s
assume this document is missing). Use the template shown in question 1.
- [20 points] Regarding the Notown Records conceptual model produced in question 1, answer the
following two questions:
a. [15 points] Derive the corresponding logical model. You must use SQL Power Architect(http://www.bestofbi.com/page/architect_download_os) tool to draw your diagram as shown in the illustration.
b. [5 points] Show (copy and paste) the physical implementation of the logical model produced in part a for the PostgreSQL database (physical model). See an example below. Assume the database was named “forum” here. Upload your scripts to an online repository.
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: firstname.lastname@example.org 微信:itcsdx