Question

Employee (employee name, street, city)

works (employee name, company name, salary)

company (company name, city)

manages (employee name, manager name)

Using Oracle. Give an SQL schema definition for the employee database of Figure above. Choose an appropriate domain for each attribute and an appropriate pri- mary key for each relation schema. Include foreign key constraints where needed.

Populate the tables you created for the previous question with data. Be careful about the order you populate the tables with data.

Answer 1

--SQL Schema Definition:

Employee schema:

employee (employee_name VARCHAR (50), street VARCHAR (50), city VARCHAR (50), PRIMARY KEY (employee_name))

--Works schema:

works (employee_name VARCHAR (50), company_name VARCHAR (50), salary INTEGER, FOREIGN KEY (employee_name) REFERENCES employee(employee_name))

--Company schema:

company (company_name VARCHAR (50), city VARCHAR (50), PRIMARY KEY (company_name))

--Manages schema:

manages (employee_name VARCHAR (50), manager_name VARCHAR (50), FOREIGN KEY (employee_name) REFERENCES employee(employee_name))

--Example data population order:

Company

Employee

Works

Manages

Step-by-step explanation:

To create an SQL schema definition for the employee database described in the figure, you can define the following tables with appropriate attributes, primary keys, and foreign key constraints:

1. Table: Employee

- Attributes:

- employee_id (Primary key)

- employee_name

- street

- city

- Domain:

- employee_id: Integer or VARCHAR

- employee_name: VARCHAR

- street: VARCHAR

- city: VARCHAR

2. Table: Works

- Attributes:

- employee_id (Foreign key referencing Employee table)

- company_name

- salary

- Domain:

- employee_id: Integer or VARCHAR

- company_name: VARCHAR

- salary: Numeric or Decimal

3. Table: Company

- Attributes:

- company_name (Primary key)

- city

- Domain:

- company_name: VARCHAR

- city: VARCHAR

4. Table: Manages

- Attributes:

- employee_id (Foreign key referencing Employee table)

- manager_name

- Domain:

- employee_id: Integer or VARCHAR

- manager_name: VARCHAR

The schema definition above includes the necessary primary key constraints for each table. The foreign key constraints are present in the Works and Manages tables, where the employee_id attribute references the primary key of the Employee table.

To populate the tables with data, you would typically use SQL INSERT statements. The order of insertion depends on any dependencies between tables. In this case, you would insert data into the Company table first, followed by the Employee table, and then the Works and Manages tables. The specific data to be inserted would depend on the context or requirements of the application.