4.7

Consider the employee database of Figure 4.12. Give an SQL DDL definition of this database. Identify referential-integrity constraints that should hold, and include them in the DDL definition.

---

CREATE TABLE employee ( 
    id INTEGER,
    person_name VARCHAR(50),
    street VARCHAR(50),
    city VARCHAR(50),
    PRIMARY KEY (id)
);
 
CREATE TABLE company ( 
    company_name VARCHAR(50),
    city VARCHAR(50),
    PRIMARY KEY(company_name)
);
 
CREATE TABLE works (
    id INTEGER,
    company_name VARCHAR(50),
    salary numeric(10,2),
    PRIMARY KEY(id),
    FOREIGN KEY (id) REFERENCES employee(id),
    FOREIGN KEY (company_name) REFERENCES company(company_name)
);
 
CREATE TABLE manages ( 
    id INTEGER,
    manager_id INTEGER, 
    PRIMARY KEY (id), 
    FOREIGN KEY (id) REFERENCES employee (id), 
    FOREIGN KEY (manager_id) REFERENCES employee (id)
)

Note that alternative data types are possible. Other choices for *not null attirbutes may be acceptable.