Example: The Supplier Parts DB

Table P contains data on various parts, table S describes the suppliers, and table SP details deliveries. These tables are used in the examples in the text. 

create table P (
  PID     CHAR(6),
  PNAME   CHAR(20),
  COLOR   CHAR(6),
  WEIGHT  NUMERIC(5,1),
  CITY    CHAR(15),
  PRIMARY KEY(PID));

insert into P values ('P1', 'Nut', 'Red', 12, 'London');
insert into P values ('P2', 'Bolt', 'Green', 17, 'Paris');
insert into P values ('P3', 'Screw', 'Blue', 17, 'Rome');
insert into P values ('P4', 'Screw', 'Red', 14, 'London');
insert into P values ('P5', 'Cam', 'Blue', 12, 'Paris');
insert into P values ('P6', 'Cog', 'Red', 19, 'London');

CREATE TABLE S (
  SID     CHAR(5),
  SNAME   CHAR(20),
  STATUS  NUMERIC(5),
  CITY    CHAR(15),
  PRIMARY KEY(SID));

insert into S values ('S1', 'Smith', 20, 'London');
insert into S values ('S2', 'Jones', 10, 'Paris');
insert into S values ('S3', 'Blake', 30, 'Paris');
insert into S values ('S4', 'Clark', 20, 'London');
insert into S values ('S5', 'Adams', 30, 'Athens');

CREATE TABLE SP (
  SID     CHAR(5),
  PID     CHAR(6),
  QTY     NUMERIC(9),
  PRIMARY KEY (SID, PID),
  FOREIGN KEY(SID) REFERENCES S(SID),
  FOREIGN KEY(PID) REFERENCES P(PID));

insert into SP values ('S1', 'P1', 300);
insert into SP values ('S1', 'P2', 200);
insert into SP values ('S1', 'P3', 400);
insert into SP values ('S1', 'P4', 200);
insert into SP values ('S1', 'P5', 100);
insert into SP values ('S1', 'P6', 100);
insert into SP values ('S2', 'P1', 300);
insert into SP values ('S2', 'P2', 400);
insert into SP values ('S3', 'P2', 200);
insert into SP values ('S4', 'P2', 200);
insert into SP values ('S4', 'P4', 300);
insert into SP values ('S4', 'P5', 500);

select * from P;
+-----+-------+-------+--------+--------+
| PID | PNAME | COLOR | WEIGHT | CITY   |
+-----+-------+-------+--------+--------+
| P1  | Nut   | Red   |     12 | London |
| P2  | Bolt  | Green |     17 | Paris  |
| P3  | Screw | Blue  |     17 | Rome   |
| P4  | Screw | Red   |     14 | London |
| P5  | Cam   | Blue  |     12 | Paris  |
| P6  | Cog   | Red   |     19 | London |
+-----+-------+-------+--------+--------+

select * from S;
+-----+-------+--------+--------+
| SID | SName | STATUS | City   |
+-----+-------+--------+--------+
| S1  | Smith |     20 | London |
| S2  | Jones |     10 | Paris  |
| S3  | Blake |     30 | Paris  |
| S4  | Clark |     20 | London |
| S5  | Adams |     30 | Athens |
+-----+-------+--------+--------+

select * from SP;
+-----+-----+-----+
| SID | PID | Qty |
+-----+-----+-----+
| S1  | P1  | 300 |
| S1  | P2  | 200 |
| S1  | P3  | 400 |
| S1  | P4  | 200 |
| S1  | P5  | 100 |
| S1  | P6  | 100 |
| S2  | P1  | 300 |
| S2  | P2  | 400 |
| S3  | P2  | 200 |
| S4  | P2  | 200 |
| S4  | P4  | 300 |
| S4  | P5  | 500 |
+-----+-----+-----+

Exercise: create tables S, P, SP in your Postgres DB via Jupyter notebook

Hints: