# Compsci-345

## Class 7: ER Diagrams and Models

### Step 1:

See notes on iPad

### Step 2:

CREATE TABLE B (
    c INTEGER PRIMARY KEY,
    d INTEGER
)

CREATE TABLE A (
    a INTEGER,
    b INTEGER,
    c INTEGER,
    PRIMARY KEY (a, c),
    FOREIGN KEY (c) REFERENCES B (c)
)

CREATE TABLE D (
    g INTEGER PRIMARY KEY,
    h INTEGER
)

CREATE TABLE C (
    a INTEGER,
    c INTEGER,
    e INTEGER,
    PRIMARY KEY (a, c),
    FOREIGN KEY (a) REFERENCES A (a)
)


CREATE TABLE S (
    f INTEGER,
    a INTEGER,
    c INTEGER,
    g INTEGER,
    PRIMARY KEY (a, c, g),
    FOREIGN KEY (a, c) REFERENCES C (a, c),
    FOREIGN KEY (g) REFERENCES D (g)
)

### Step 3:

Part 1:
- PRIMARY KEY:
- Single = TRUE
- Multiple = TRUE
- FOREIGN KEY:
- Single = TRUE
- Multiple = TRUE
- UNIQUE:
- Single = TRUE
- Multiple = TRUE

Part 2:
- PRIMARY KEY: FALSE
- FOREIGN KEY: TRUE
- UNIQUE: TRUE

Part 3:
- PRIMARY KEY: FALSE
- FOREIGN KEY: TRUE
- UNIQUE: FALSE

Part 4:
- PRIMARY KEY: FALSE
- FOREIGN KEY: TRUE
- UNIQUE: TRUE

Part 5:
- PRIMARY KEY: TRUE
- FOREIGN KEY: TRUE
- UNIQUE: TRUE

### Step 4:

Part 1:

CREATE TABLE Product (
    pid INT PRIMARY KEY,
    name VARCHAR(20)
)

CREATE TABLE Inventory (
    pid INT PRIMARY KEY REFERENCES Product (pid), -- Part 1
    quantity INT CHECK (quantity > 0) -- Part 2
)

CREATE TABLE Supplier (
    sid INT PRIMARY KEY,
    name VARCHAR(20),
    address VARCHAR(50)
)

CREATE TABLE Supplies (
    sid INT REFERENCES Supplier (sid) ON DELETE CASCADE, -- Part 1, Part 3
    pid INT REFERENCES Product (pid), -- Part 1
    PRIMARY KEY (sid, pid)
)

CREATE TABLE PurchaseOrder (
    pid INT REFERENCES Product (pid), -- Part 1
    quantity INT
)