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,
VARCHAR(50),
person_name VARCHAR(50),
street VARCHAR(50),
city PRIMARY KEY (id)
);
CREATE TABLE company (
VARCHAR(50),
company_name VARCHAR(50),
city PRIMARY KEY(company_name)
);
CREATE TABLE works (
id INTEGER,
VARCHAR(50),
company_name numeric(10,2),
salary PRIMARY KEY(id),
FOREIGN KEY (id) REFERENCES employee(id),
FOREIGN KEY (company_name) REFERENCES company(company_name)
);
CREATE TABLE manages (
id INTEGER,
INTEGER,
manager_id 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.