### 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

1. SELECT S.SupplierId, S.City FROM Supplier S
INNER JOIN [Transaction] T ON T.SupplierId=S.SupplierId
INNER JOIN [Part] P ON P.PartId=T.PartId
WHERE P.Name='PART 2'

SELECT S.SupplierId FROM Supplier S
INNER JOIN [Transaction] T ON T.SupplierId=S.SupplierId
INNER JOIN [Part] P ON P.PartId=T.PartId
WHERE P.Name='PART 2' AND S.City='LONDON'

SELECT T.SupplierId, MAX(t.PARTCOUNT) from
(
SELECT TOP 1 S.SupplierId, COUNT(*)AS PARTCOUNT FROM Supplier S
INNER JOIN [Transaction] T ON T.SupplierId=S.SupplierId
GROUP BY S.SupplierId
) T
GROUP BY T.SupplierId

2. SQL*Plus: Release 10.2.0.3.0 - Production on Sat Nov 29 07:12:36 2014

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set serveroutput on
SQL> create table supplier
2 (
3 S varchar(2),
4 supplier_number integer,
5 city varchar(7),
6 primary key(S));

Table created.

SQL> create table parts
2 (
3 P varchar(2),
4 parts_name varchar(5),
5 city varchar(7),
6 primary key(P));

Table created.

SQL> desc supplier

SQL> desc parts

SQL> create table supplierpart
2 (
3 S varchar(2),
4 P varchar(2),
5 quantity number(3),
6 primary key(S,P),
7 foreign key(S) references supplier,
8 foreign key(P) references parts);

Table created.

SQL> desc supplierpart

SQL> insert into supplier values('S1',5,'London');

1 row created.

SQL> insert into supplier values('S2',10,'Paris');

1 row created.

SQL> insert into supplier values('S3',15,'USA');

1 row created.

SQL> insert into supplier values('S4',20,'London');

1 row created.

SQL> select * from supplier;

SQL> insert into parts values('P1','Nut','London');

1 row created.

SQL> insert into parts values('P2','Bolt','Paris');

1 row created.

SQL> insert into parts values('P3','Cog','USA');

1 row created.

SQL> insert into parts values('P4','Screw','London');

1 row created.

SQL> select * from parts;

SQL> insert into supplierpart values('S1','P1',100);

1 row created.

SQL>

SQL> insert into supplierpart values('S2','P2',200);

1 row created.

SQL> insert into supplierpart values('S3','P3',300);

1 row created.

SQL> insert into supplierpart values('S4','P4',400);

1 row created.

SQL> select * from supplierpart;

//Find supplier number and city//

SQL> select supplier.S,city
2 from supplier,supplierpart
3 where supplier.S=supplierpart.S and P='P2';

//Find supplier Number belongs to London//

SQL> select supplier.S
2 from supplier,supplierpart
3 where supplier.S=supplierpart.S and supplier.city='London';

SQL> exit

3. For the limitation of words i was unable to post the outputs.

4. 2.
alter table supply
alter table transection
add constraint FK_transection_sup_id foreign key(sup_id) references supply(sup_id);
alter table part_engineer
alter table transection
add constrain FK_transection_part_id foreign key (part_id) references part_engineer(part_id);
3.alter table supply
4.select distinct(sup_id,city)from supply,part_engineer
where part_id='p2';
5.select sup_id from supply,part_engineer
where city='london'and part_id='p2';

5. create table supplier
(sup_id number(12),
sup_name varchar(18),
sup_city varchar(20));

create table parts
(part_id number(10),
part_name varchar(9));

create table transection
(sup_id number(12),
part_id number(10));

alter table supplier

alter table parts

alter table taransection
add constrant trnsfk foreign key(sup_id)reference on supplier(sp_id),
add constrant trnsfk foreign key(part_id)reference on parts(part_id);

insert into supplier
values(1111,'X','london');
insert into supplier
values(1100,'Y','Washington');
insert into supplier
values(1122,'Z',Califurniya');
select * from supplyer;

insert into parts
values(01,'P1');
insert into parts
values(02,'P2');
insert into parts
values(03,'P3');
select * from parts;

select supplier.sup_name,supplyer.sup_city from supplier,transection where supplier.sup_id=transection.sup_id and transection.part_id='p2';

select sup_id from supplier,transection where city='london' AND part_id='p2',

6. 1> create table supplys
(sup_id varchar(10),
sup_name varchar(10),
city varchar(10));

1> create table eng_part
(part_id varchar(10),
part_name varchar(10));

1> create table transection
(sup_id varchar(10),
part_id varchar(10),
sub_date date(7));

2> alter table supplys

2> alter table eng_part

2> alter table transection
add constraint fk_sup_id foreign key (sup_id) references supplys(sup_id);

2> alter table transection
add constraint fk_part_id foreign key (part_id) references eng_Part(part_id);

3> alter table supplys
add constraint ck_city check(city in('dhaka','Gazipur','new work'));

4> select distinct sup_id,city from supplys,transection where part_id = 'p2';

5> select sup_id from supplys transection where city = 'Gazipur' and part_id = 'p2';

7. create table supplier
(sup_id number(12),
sup_name varchar(18),
sup_city varchar(20));

create table parts
(part_id number(10),
part_name varchar(9));

create table transection
(sup_id number(12),
part_id number(10));

alter table supplier

alter table parts

alter table taransection
add constrant trnsfk foreign key(sup_id)reference on supplier(sp_id),
add constrant trnsfk foreign key(part_id)reference on parts(part_id);

insert into supplier
values(111,'A','london');
insert into supplier
values(222,'B','Delhi');
insert into supplier
values(333,'C',Dhaka');

select * from supplyer;

insert into parts
values(1,'P1');
insert into parts
values(2,'P2');
insert into parts
values(3,'P3');
select * from parts;

select supplier.sup_name,supplyer.sup_city from supplier,transection where supplier.sup_id=transection.sup_id and transection.part_id='p2';

select sup_id from supplier,transection where city='london' AND part_id='p2',

8. 1.
CREATE TABLE supplier(
sup_id NUMBER(10)PRIMARY KEY,
sup_name VARCHAR2(20),
sup_city VARCHAR2(15));

ALTER TABLE supplier
CHECK (sup_city IN ('Dhaka', 'London', 'Comilla','Gazipur','Barlin'));
2.

CREATE TABLE parts(
sup_id NUMBER(10),
part_name VARCHAR2(10),
CONSTRAINT partsfk FOREIGN KEY(sup_id) REFERENCES supplier(sup_id));
3.
SELECT supplier.sup_id,supplier.sup_city,parts.part_name FROM supplier,parts
WHERE supplier.sup_id=parts.sup_id AND parts.part_name='P2'

SELECT supplier.sup_id,supplier.sup_city,parts.part_name FROM supplier,parts
WHERE supplier.sup_id=parts.sup_id AND parts.part_name='P2'AND supplier.sup_city='London'

9. 1...

create table supplier
(
supplier_id number(15)primary key,
supplier_name varchar2(30),
supplier_city varchar2(30),
);

2...

create table parts
(
part_name varchar2(30),
supplier_id number(15),
constraint sup_fk1_parts foreign key(supplier_id)references supplier(supplier_id)
);

3....

insert into supplier values(201430805,'Md.Monirul Islam','London');
insert into parts values('P2',201430805);

4.....
select supplier.supplier_id,supplier.supplier_city,parts.part_name from supplier,parts where
supplier.supplier_id=parts.supplier_id and parts.part_name='P2';

5.....
select supplier.supplier_id,supplier.supplier_city,parts.part_name from supplier,parts where
supplier.supplier_id=parts.supplier_id and parts.part_name='P2' and supplier.supplier_city='London';

10. id # 201430964

create table supplier(
sup_id number(10)primary key,
sup_name varchar2(15),
sup_city varchar2(15),
constraint check_city
check(sup_city in('Dhaka', 'London', 'Comilla','Gazipur','Barlin'));
);

create table part(
part_id number(10)primary key,
part_name varchar2(11)
);

create table transaction(
sup_id number(10),
part_id number(10),
constraint tran_fk foreign key(sup_id)references supplier(sup_id),
constraint tran_fk2 foreign key(part_id)references part(part_id)
);

insert into supplier
values('11','ab','London');
insert into supplier
values('12','cd','Barlin');
insert into part
values('100','p1');
insert into part
values('200','p2');
select supplier.sup_name,supplier.sup_city from supplier,transaction
where supplier.sup_id=transaction.sup_id and transaction.part_id='p2';
select sup_id from supplier,transaction where city ='London'and part_id='p2';

11. 1...

create table supplier
(
supplier_id number(15)primary key,
supplier_name varchar2(30),
supplier_city varchar2(30),
);

2...

create table parts
(
part_name varchar2(30),
supplier_id number(15),
constraint sup_fk1_parts foreign key(supplier_id)references supplier(supplier_id)
);

3....

insert into supplier values(201430805,'Md.Monirul Islam','London');
insert into parts values('P2',201430805);

4.....
select supplier.supplier_id,supplier.supplier_city,parts.part_name from supplier,parts where
supplier.supplier_id=parts.supplier_id and parts.part_name='P2';

5.....
select supplier.supplier_id,supplier.supplier_city,parts.part_name from supplier,parts where
supplier.supplier_id=parts.supplier_id and parts.part_name='P2' and supplier.supplier_city='London';

12. SHAIKH MD EKHLAS UDDIN
201530322
https://www.dropbox.com/sh/jorpsnbz229wncu/AAApgE4yDY8NGtPa2Q-p0WE2a?dl=0

13. https://www.dropbox.com/sh/jorpsnbz229wncu/AAApgE4yDY8NGtPa2Q-p0WE2a?dl=0

14. Md. mortuza Alom Sizar ID#201530446, Batch-50A.

https://www.dropbox.com/s/o5iwy2ukpo8b1pm/Schema%20%26%20ER%20Diagram.docx?dl=0

15. faiz kabir saif id 201530873 https://www.dropbox.com/s/119khjo7xy4gvyg/MS%20WORD%20%281%29.docx?dl=0

16. https://www.dropbox.com/home?preview=Tareq+Parves.docx
MD.TAREQ PARVES
201530444

17. Name-Nurullah
ID-201530279
https://www.dropbox.com/s/dh04uk8aayfpmys/Binary%20Relation%20with%20M-N%20cardinality%20ratio%20for%20a%20supplier%20and%20engineering%20parts.docx?dl=0

18. Amir Hossain
ID -201530278

https://www.dropbox.com/s/emb1jdpxdzxeg9d/Amir.docx?dl=0

19. https://www.dropbox.com/s/r7fnnosabx5xkuj/Tareq%20class.docx?dl=0
Md.Tareq Parves
ID NO:201530444

20. https://www.dropbox.com/s/n2kx362zpkedvtc/201531064.docx?dl=0

Md.Arman Hodssen
id:201530753

21. Name-Nurullah
Id-201530279

https://www.dropbox.com/s/jaiokff4v97a724/Binary%20Relation%20with%20M-N%20cardinality%20ratio%20for%20a%20supplier%20and%20engineering%20parts.docx?dl=0

22. create table suplier
(suplier_id number(20),
suplier_name varchar2(50),
suplier_mob number(50),
suplier place varchar2(50)
constraint sidpk primary key(suplier_id)
);

create table suplies
(suplier_id varchar2(20),
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(20),
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)
);

23. https://www.dropbox.com/s/5vm207db0yzr76y/Soleman%28Lab7%29.docx?dl=0

27. NAME : MD. ANWER PARVES
BATCH - 50
ID - 201530432
SECTION - A

Supplier Table Creation :
CREATE TABLE Supplier (
S_Id number(10) not null,
Name varchar2(50) not null,
PhoneNo varchar2(40)not null,
City varchar2(20) not null,
CONSTRAINT Supplier_PK PRIMARY KEY (S_Id), CONSTRAINT CHK_City CHECK (City IN ('Dhaka', 'Munshigonj', 'Savar'))
);

Parts Table Creation :
CREATE TABLE Parts (
[P_Id] number(20) not null,
[Origin] varchar2(50) not null,
[Category] varchar2(40)not null,
[Name] varchar2(20) not null,
[Details] varchar2(200) null,
[UnitPrice] DECIMAL(18,2) null,
CONSTRAINT Parts_PK PRIMARY KEY (P_Id)
);

Supplies Table Creation :
CREATE TABLE Supplies (
[SNo] number(20) not null,
[S_Id] number(20) not null,
[P_Id] number(20) not null,
[Quantity] DECIMAL(18,2) null,
[TotalPrice] DECIMAL(18,2) null,
[Discount] DECIMAL(18,2) null,
[GrandTotal] DECIMAL(18,2) null,
[Date] Date not null,
CONSTRAINT Supplies_PK PRIMARY KEY (SNo),
CONSTRAINT Supplier_FK FOREIGN KEY (S_Id) REFERENCES Supplier(S_Id),
CONSTRAINT Parts_FK FOREIGN KEY (P_Id) REFERENCES Parts(P_Id));

29. NURUL HUQ ..batch50b...201521122

30. ansarul haque
201530642

31. Rena id 201530582

create table suplier
(suplier_id number(20),
suplier_name varchar2(40),
suplier place varchar2(50)
constraint sidpk primary key(suplier id)
);
create table suplies
(suplier_id varchar2(20),

parts_name number(50),
parts_id number(20),
suplies_id number(20),
suplies_city varchar2(20)
constraint suppk primary key(suplies id),
constraint supfk foreign key(suplier id),
constraint supfk foreign key(parts id)
);
create table patrs
(parts_id number(20),
parts_name varchar2(30),
p.serial_num number(20),
p.price number(10),
suplies_id number(20),
constraint papk primary key(parts id id)
constraint pafk foreign key(suplies id)
);

32. Name=kopil uddin
Id=201530428
https://www.dropbox.com/s/g8te8a2bco75xey/MS%20WORD.docx?dl=0

33. Md.Rakibur Rahman(Batch 50A) 201530473December 5, 2016 at 8:17 AM

https://www.dropbox.com/s/119khjo7xy4gvyg/MS%20WORD%20%281%29.docx?dl=0

34. create table supplier
(
S_ID number(2) primary key,
S_NAME varchar2(10) not null,
S_CITY varchar2(15) not null,
constraint chk_city check(S_CITY in('London','Newyork','Hongkong'))
);

desc supplier

insert into supplier
values(
1, 'Honda','London'
);
insert into supplier
values(
2, 'BMW','London'
);
insert into supplier
values(
3, 'Toyota','Hongkong'
);
insert into supplier
values(
4, 'Ferrary','Newyork'
);

select * from supplier

create table parts
(
P_ID number(2) primary key,
P_NAME varchar2(10) not null,
S_ID number(2)
);

alter table parts
constraint FK_SID foreign key(S_ID)
references supplier(S_ID)

desc parts

insert into parts
values(
1,'P1','3');
insert into parts
values(
2,'P2','1');
insert into parts
values(
3,'P1','1');
insert into parts
values(
4,'P2','1');
insert into parts
values(
5,'P2','1');
insert into parts
values(
6,'P1','4');
insert into parts
values(
7,'P3','2');

select * from parts

select supplier.S_ID, supplier.S_CITY, parts.P_NAME from supplier, parts where P_NAME='P2'

select supplier.S_ID, supplier.S_CITY, parts.P_NAME from supplier, parts where S_CITY='London' and P_NAME='P2'

35. https://www.dropbox.com/s/p14rgx4u8bjmlz3/Supplier_Parts.doc?dl=0

M.Ziauddin Sarawar
ID: 201530479
Batch 50
Section: A

Md. Ariful Islam
Id: 201530468
Batch:50
Section: A

37. Md. Al Emren
Batch: 50th
ID: (201530464)

https://www.dropbox.com/s/zlaga43fakpjwcu/Data.docx?dl=0

38. Md Tajmul Haque
ID: 201531148
Batch 50
Section: B

create table suplier
(s_id number(10),
s_name varchar2(10),
s_mob number(10),
suplier place varchar2(10)
constraint sidpk primary key(suplier_id)
);

create table suplies
(s_id varchar2(10),
p_name number(10),
p_id number(10),
s_id number(10),
s_city varchar2(10)
constraint suppk primary key(suplies_id),
constraint supfk foreign key(suplier_id),
constraint supfk foreign key(parts_id)
);

create table patrs
(parts_id number(10),
parts_name varchar2(10),
p.serial_num number(10),
p.price number(10),
suplies_id number(10),
constraint papk primary key(parts id_id)
constraint pafk foreign key(suplies_id)
);

39. Md. Hossain Kabir
ID: 201530462
Batch 50
Section: A

https://www.dropbox.com/home?preview=201530462.docx

40. https://www.dropbox.com/home?preview=201530465.docx

Md. Nazmul Islam
Id: 201530465
Batch:50
Section: A

41. Md. Alamgir Hossain
ID_201530470
Batch:50th
CSE (Dip.)

42. Md. Alamgir Hossain
ID_201530470
Batch:50th
Section:A

43. Rezaul Karim Arif
ID: 201320207
Batch : 43rd
Section: A

https://dl.dropboxusercontent.com/u/70398051/Lab%207%20%20Binary%20Relation%20with%20MN%20cardinality%20ratio.docx

45. Kamol (ID:201530879)(batch:50)December 5, 2016 at 10:44 PM

47. Kamol(ID:201530879)Batch:50th

48. à¦†à¦¬্à¦¦ুà¦²্à¦²াà¦¹ à¦†à¦² à¦®াà¦®ুà¦¨
ID 201530451

49. Ashrafuzzaman Rony

https://www.dropbox.com/home

ID_201530466
Batch:50th
Section:A

51. Name : Syed Ahmed
ID: 201210656

https://gist.github.com/imsyedahmed/8ecea6028293fd3fbc17c5c11299a5d7

52. Alija Jerin
ID# 201530047
Batch-50(A)
https://www.dropbox.com/s/wc5p9tarmdz6fr8/Oracle%20Docs.docx?dl=0

53. Abdullah Al Mamun
ID 201530451
Batch 50th (29Dip)
https://www.dropbox.com/home/

54. Md. Ebrahim Sharker Joney
ID 201530463
Batch: 50th

55. Name: Mohe Uddin
ID: 201530686
Batch: 50th
Section: A

56. Name: MD. ABU NAYEEM SAMIULLAH
ID: 201530335
Batch : 50th
Section : A

https://www.dropbox.com/s/ihdnyzo29wu68ez/Assignment%20%28LAB-7%29%20ID_201530335.pdf?dl=0

57. id:201510469;

60. ID:201610456

61. ID: 201610057

63. ID:201520552

64. Name : Md Muenuddin Khan
ID :201610865

65. ID : 201610254

66. ID 201610362
https://www.dropbox.com/s/1y0bzpd9h32m5af/HW%207.txt?dl=0

1. Sir I have submitted this around 12:30 last night but now that was invisible thats why I am submitting this again.

68. ID:-201610058

69. This comment has been removed by the author.

1. ID: 201611036

70. Id:201710494
https://www.dropbox.com/s/q2qhtgzcsj6lp3r/Lab7Binary%20Relation%20with%20MN%20cardinality%20ratio%20for%20a%20supplier%20and%20engineering%20parts.txt?dl=0

## Labels

attribute (2) begin (2) binary (1) cardinality (2) clause (1) columns (1) constraint (1) create (1) data (1) data staging (1) data storage (1) data type (1) database (3) dbms (3) ddbms (1) declare (2) dependent (1) else (2) employee (1) end if (2) end loop (2) entity (2) exit (3) Fibonacci (1) field (1) for (2) for loop (2) foreign key (1) from (3) if (3) informix (1) insert (1) itu (1) ldap (1) loop (1) metadata (1) mod (1) object (1) osi (1) perfect (2) pl/sql (4) plsql (1) prime number (4) program (2) query (1) rdbms (2) record (2) relationship (1) reporting (1) rows (1) schema (2) select (3) sql (3) sybase (1) table (5) warehouse (1) where (1) while (1)