Description

Homework 1

A couple of students would like to build a startup. They have a very good web crawler that can extract some information about the products sold on the websites. The students gathered the following requirements:

·         Each product has an id, name, description, brand name, type keyword.

o   Products are identified by their ids.

o   The system should not accept products with no name.

o   Each product may have multiple type keyword associated with them.

·         Products are sold in different websites. Crawler daily visits the websites and captures the pricing information.

o   Price is composed of two pieces:  initial price and discounted price. Discounted price must be less than the initial price.

o   Total claimed discount amount should be captured by the system.

·         Each website has a unique identifying URL. The following attributes of websites shall be stored in the website.

o   Address: an address is composed of a country, city, zip number and street number. Each website must have a full address.

o   Phone number(s): A website might have zero or more phone numbers

o   E-mail address: Each website must have exactly one e-mail address

·         A website can supply a product by itself, or via an external supplier.

o   External suppliers cannot exist in the system by themselves, but only through listing their products in a website.

o   A website may have zero or more external supplier

o   An external supplier has a name, phone number, and e-mail address.

·         One product can be sold in different websites with different prices.

·         The price of product can daily change.

The following functional dependencies are known by the students:

·         zip code -> city

·         City -> Country

Please do the followings for the conceptual design:

A.      Draw an ER diagram (25 points)

B.      Convert it to the relational model. State each relation with its primary, candidate and foreign keys. And if an attribute cannot be null, state it. (25 points)

C.      Prove that each relation is anomaly free so that we can perform insert/update and delete operations without any problem. (25 points)

D.      Write create table statement of each relation with the corresponding triggers and constraints/assertions. (25 points)

Please submit your answer to LMS. Late submissions are going to be penalized with 10 points each day.