Intro to DB & MYSQL relational algebra

Exercise 4-2

1. The following figures show an IE diagram and a Chen diagram for the ProAudio database with the relationships and cardinality of the relationships:

Information Engineering diagram:

Chen ER diagram (shown without attributes for clarity):

Chen ER diagram (note that the customer, musician, producer, and sales rep names and address attributes are combined):

2. Data types for the attributes:

Customer(*c_num - INTEGER,

c_first_name - VARCHAR,

c_last_name - VARCHAR,

c_street - VARCHAR,

c_city - VARCHAR,

c_state - VARCHAR,

c_zip - VARCHAR,

c_phone - VARCHAR,

c_cc_num - VARCHAR,

c_exp_date - VARCHAR)

Distributors(*d_num - INTEGER,

d_name - VARCHAR,

d_street - VARCHAR,

d_city - VARCHAR,

d_state - VARCHAR,

d_zip - VARCHAR,

d_phone - VARCHAR,

d_contact - VARCHAR,

d_contact_extension - INTEGER)

Orders(*o_num - INTEGER,

*c_num - INTEGER,

s_num - INTEGER,

o_date - DATE,

o_filled – VARCHAR (Boolean))

Items(*i_num - INTEGER,

i_title - VARCHAR,

d_num - INTEGER,

i_price - DECIMAL,

i_release_date - DATE,

i_genre - VARCHAR)

Musicians(*m_num - INTEGER,

m_first_name - VARCHAR,

m_last_name - VARCHAR)

Producers(*p_num - INTEGER,

p_first_name - VARCHAR,

p_last_name - VARCHAR)

Sale Reps(*s_num - INTEGER,

s_first_name - VARCHAR,

s_last_name - VARCHAR)

Locations(*l_num – INTEGER,

l_street - VARCHAR,

l_city - VARCHAR,

l_state - VARCHAR,

l_zip - VARCHAR,

l_phone - VARCHAR,

l_fax - VARCHAR)