MD.TAREQ PARVES

201530444

Lab 7 : Binary Relation with M:N cardinality ratio for a supplier and engineering parts

Consider a binary relationship with M: N cardinality ratio. A supplier supplies many engineering parts, and a part is supplied by many suppliers. Do the following action and assume the circumstances to design the table.

                        a.   Design Table and consider non redundancy

                        b.   Necessary Constraints for cardinality radio.

                        c.   Check Constraint must be used for Suppliers’ City.

                        d.   Find the supplier NUMBER and CITY who supply part P2

e.  Find the supplier NUMBER(S) who belongs to London and   supplied part P2

f.   Find the supplier NUMBER who supplied maximum part

ER_DIAGRAM

Schema_____

create table suplier

(suplier_id number(50),

suplier_name varchar2(50),

suplier_mob number(50),

suplier place varchar2(50)

constraint sidpk primary key(suplier id)

);

create table suplies

(suplier_id varchar2(50),

parts_name number(50),

parts_id number(50),

suplies_id number(50),

suplies_city varchar2(50)

constraint suppk primary key(suplies id),

constraint supfk foreign key(suplier id),

constraint supfk foreign key(parts id)

);

create table patrs

(parts_id number(50),

parts_name varchar2(50),

p.serial_num number(50),

p.price number(50),

suplies_id number(50),

constraint papk primary key(parts id id)

constraint pafk foreign key(suplies id)

);