Question

create a database model consisting of 1) a summary of the relationships between the entities similar to Figure 6-40; 2) a crow's foot ER diagram for the design using Power Architect. Reminder: a crow's foot ERD does not contain primary keys, foreign keys, or intersection relations. Export your Power Architect diagram to a .pdf and attach it to the assignment drop box.

Figure 6-40

Relationship Cardinality Parent ARTIST WORK CUSTOMER CUSTOMER ARTIST Child WORK TRANS TRANS Type Nonidentifying 1:N M- Nonidentifying 1:N M-M Nonidentifying 1:N O-C MAX MIN | CUSTOMER-ARTISTINTİ Identifying 1:N M-O CUSTOMER ARTIST_INT Identifying 1:N M-O

entities and attributes to use:

CEREAL_MANUFACTURERS (CMID, CMName, CMHomeState)

PRODUCTS (PID, PName)

INGREDIENTS (IID, IName)

SUPPLIERS (SID, SName, SPhone, SStreet, SCity, SState, SZip)

SUPPLIERS_PRICING (SID, IID, Price, PriceExpires) Note: PriceExpires is a date MM/DD/YYYY.

Answer 1

1. Manufacturer can manufacture many products so the relationship is one to many.

product can have many ingredients so the relationship is one to many.

A product can be supplied by many suppliers and hence it is many to many

pricing of ingrdients depends on suppliers and different supplier supplies different ingredients for the product and price of ingredient is fixed for a particular supplier and hence 1 to many from supplier to supplier pricing

Cereal Manufacturers Products CMID: VARCHAR NOT NULL CMName: CHAR NOT NULL CMHomeState: VARCHAR( 8) NOT NULL PID: VARCHAR(8) NOT NULL PName: VARCHAR( 8) NOT NULL Ingredients ID: VARCHAR(8) NOT NULL IName: VARCHAR(24)NOT NULL Suppliers SID: VARCHAR(8) NOT NULL SName: CHAR NOT NULL SPhone: VARCHAR(10) SStreet: CHAR SCity: CHAR SState: VARCHAR(2) SZip: VARCHAR(5) 木 Suppliers Pricing SID: VARCHAR(8) NOT NULL ID: VARCHAR(8) NOT NULL Price: NUMERIC NOT NULL Price Expires: DATE

Relationship Cardinality Child Parent Cereal Manufacturel Product product Supplier Ingredients Supplier Ingredients Product Supplier Pricing Supplier Pricing Identifvin Identifvin non-identifvin non-identifvin non-identifvin Max 1:M 1:M M:M M:M 1:M min M:M M:M M:O O:0 O:0

Product has to be manufactured by someone .product cannot exist without manufacture.hence identifying relation.
ingredients depends on product and hence relation is identifying
manufacturer is mandatory to produce product hence M:M
Ingredient is mandatory for a product hence M:M
supplier is necessary for product .supplier may supply zero product