3.18

Give an SQL schema definition for the employee database of Figure 3.19. Choose an appropriate domain for each attribute and an appropriate primary key for each relation schema. Include any foreign-key constraints that might be appropriate.

---

CREATE TABLE employee ( 
    id VARCHAR(8),
    person_name VARCHAR(30) NOT NULL, 
    street VARCHAR(40), 
    city VARCHAR(30),
    PRIMARY KEY (id)
); 

CREATE TABLE company ( 
    company_name VARCHAR(40), 
    city VARCHAR(30), 
    PRIMARY KEY (company_name)
); 

CREATE TABLE works ( 
    id VARCHAR(8), 
    company_name VARCHAR(40), 
    salary NUMERIC(10,2) CHECK (salary > 10000), 
    PRIMARY KEY (id), 
    FOREIGN KEY (id) REFERENCES employee(id)
        ON DELETE CASCADE, 
    FOREIGN KEY (company_name) REFERENCES company(company_name)
        ON DELETE CASCADE
);

CREATE TABLE manages (
    id VARCHAR(8),
    manager_id VARCHAR(8), 
    PRIMARY KEY (id), 
    FOREIGN KEY (id) REFERENCES employee (id), 
    FOREIGN KEY (manager_id) REFERENCES employee (id)
);