[40 points] Q1. Design a database system that facilitates the operations of an online company.

• Assumptions

– As stated, each order is for a single item, either a kit or part.

– The customer is the person who buys the computer kits and parts.

– A customer could possibly place multiple orders for the same kit or part.

– Barcode is unique across all parts and kits.

– Phone number type can be home, mobile, work and other.

– The Computer kit/ Computer Part entity types are like catalogs of kits/parts.

– Computer kit and part type description could be control vocabularies (drop down menu,enumeration). Solution 1

• Textual ER

– Entities:

∗ CUSTOMER: email, address, name(first, middle, last),

{phone (number,type)}

∗ ORDER: orderid, order date, shipped date, shipping address, credit card(number,expiry,CVV,name), quantity

∗ COMPUTER KIT: barcode, name, description, price, {tools}

∗ COMPUTER PART: barcode, serial number, type, specifications, price

– Relationships:

∗ KIT SALES: <CUSTOMER,COMPUTER KIT, ORDER> 1:1:M, PARTIAL/PARTIAL/TOTAL

∗ PARTS SALES: <CUSTOMER, COMPUTER PART, ORDER> 1:1:N, PARTIAL/PARTIAL/TOTAL

∗ CONSISTS OF: <COMPUTER KIT, COMPUTER PART> 1:Y, TOTAL/PARTIAL

• Diagrammatic ER

• Assumptions

– Each actor plays a single role/character in a movie.

– Each user has a profile. Profile can be of the primary subscriber or any of the family members.

– Each profile is associated to a subscription and a profile cannot exist without subscrip- tion.

– Each subscription has a type where type can be family or individual.

– Each award has award name (like oscars,academy), category and year. Solution 1

• Textual ER

– Entities:

∗ ACTOR: actor id,name(fname,middle initial, lname), birthday,url,main roles,secondary roles

∗ DIRECTOR: director id,name(fname, middle initial, lname),birthday,url,short movies, regular movies

∗ MOVIE: movie id, title, description, genre, duration,release year,

{awards (award name,category,year)},url, movie rating

∗ PROFILE: profile name

∗ SUBSCRIPTION: subscription id, name(first, last, middle initial), address, email,

{phone (phone number,phone type)}, price, subscription date, last payment date, subscription type

– Relationships:

∗ ACTS: <ACTOR,MOVIE> M:N, PARTIAL/PARTIAL, role

∗ DIRECTS: <DIRECTOR,MOVIE> 1:X, PARTIAL/PARTIAL

∗ RENTS: <PROFILE, MOVIE> Q:R, PARTIAL/PARTIAL, rating, resume watching point

∗ ASSOCIATED TO: <SUBSCRIPTION, PROFILE> 1:Y, PARTIAL/TOTAL

• Diagrammatic ER