This figure shows the tables in the HR schema and the columns in each table, as well as dependencies between the tables.

The employees table has the following attributes:

• Columns: employee_id (primary key), first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id.

• Relationships:

  • Column department_id relates each row of the table employees to exactly one value of department_id in the table departments

  • Column job_id relates each row of the table employees to exactly one value of job_id in the table jobs

  • Column employee_id relates each row of the table employees to none or more rows of employee_id in the job_history table

  • Column manager_id references none or one other row of the employees table through a matching employee_id; note that a head of the company would typically not have a manager

• Other relationships:

  • Column employee_id relates rows of the table employees to sales_rep_id columns in the table oe.orders

  • Column employee_id relates rows of the table employees to account_mgr_id columns in the table oe.customers

The jobs table has the following attributes:

• Columns: job_id (primary key), job_title, min_salary, and max_salary

• Relationships:

  • Column job_id relates each row of the table jobs to the corresponding values in the jobs_id column of the table employees

  • Column job_id relates none or more rows of the table jobs to the corresponding values in the jobs_id column of the table job_history

The job_history table has the following attributes:

• Columns: employee_id and start_date (a composite primary key), end_date, job_id, and department_id.

• Relationships:

  • Each job_history record is associated with a record in the table employees through the employee_id.

The departments table has the following attributes:

• Columns: department_id (primary key), department_name, manager_id, and location_id.

• Relationships:

  • Column department_id relates each row of the table departments to none or more rows of the table employees that have corresponding values for department_id.

  • Column manager_id relates each row of the table departments to a row of the table employees that has the corresponding value for employee_id.

  • Column location_id relates each row of the table departments to a row of the table locations that has the corresponding value for location_id.

The locations table has the following attributes:

• Columns: location_id (primary key), street_address, postal_code, city, state_province, and country_id.

• Relationships:

  • Column location_id relates each row of the table locations to none or more rows of the table departments that have the corresponding values for location_id.

  • Column country_id relates each row of the table locations to a row in the table countries that has the corresponding value for country_id.

• Other relationships:

  • Column location_id relates rows of the table locations to none or more rows of the table eo.warehouses that have the corresponding values for loacation_id.

The countries table has the following attributes:

• Columns: country_id (primary key), country_name, and region_id.

• Relationships:

  • Column region_id relates each row of the table countries to a row in the table regions that has the corresponding value for region_id.

  • Column country_id relates each row of the table countries to none or more rows in the table locations that have the corresponding values for country_id.

The regions table has the following attributes:

• Columns: region_id (primary key), and region_name.

• Relationships:

  • Column region_id relates rows of the table regions to none or more rows of the table countries that have the corresponding values for region_id.

This graphic also describes the OE schema and shows the dependencies between the two schemas.

The order_items table has the following attributes:

• Columns: order_id (primary key), line_item_id, product_id, unit_price, and quantity.

• Relationships:

  • Column order_id relates one or more rows of the table order_items to one row of the table orders with the corresponding value of order_id.

  • Column product_id relates one or more rows of the table order_items to one row of the table product_information with the corresponding value of product_id.

The orders table has the following attributes:

• Columns: order_id(primary key), order_date, order_mode, customer_id, order_status, order_total, sales_rep_id, promotion_id

• Relationships:

  • Column order_id relates one row of the table orders to one or more rows of the table order_items with the corresponding values of the order_id.

  • Column customer_id relates one or more rows of the table orders to one row of the table customers with the corresponding value of customer_id.

• Other Relationships:

  • Column sales_rep_id relates one or more rows of the table orders to one row of the table hr.employees with the corresponding value of employee_id.

The product_information table has the following attributes:

• Columns: product_id(primary key), product_name, product_description, category_id, weight_class, warranty_period, supplier_id, product_status, list_price, product_id, min_price, and catalog_url

• Relationships:

  • Column product_id relates one row of the table product_information to one or more rows of the table order_items with the corresponding value of product_id.

  • Column product_id relates one row of the table product_information to zero or more rows of the table product_descriptions with the corresponding value of product_id.

  • Column product_id relates one row of the table product_information to zero or more rows of the table inventories with the corresponding value of product_id.

The product_descriptions table has the following attributes:

• Columns: product_id and language_id (composite primary key), translated_name, and translated_description

• Relationships:

  • Column product_id relates one or more rows of the table product_descriptions to a row of the table product_information with the corresponding value of product_id.

The inventories table has the following attributes:

• Columns: product_id and warehouse_id (composite primary key), and quantity_on_hand.

• Relationships:

  • Column product_id relates one or more rows of the table inventories to one row of the table product_information with the corresponding value of product_id.

  • Column warehouse_id relates one or more rows of the table inventories to one rows of the table warehouses with the corresponding value of warehouse_id.

The customers table has the following attributes:

• Columns: customer_id (primary key), cust_first_name, cust_last_name, cust_address, phone_numbers, nls_language, nls_territory, credit_limit, cust_email, account_mgr_id, cust_geo_location, date_of_birth, marital_status, gender, and income_level

• Relationships:

  • Column customer_id relates rows of the table customers to one or more rows of the table orders with the corresponding value of customer_id.

• Other Relationships:

  • Column acct_mgr_id relates one or more rows of the table customers to one row of the table hr.employees with the corresponding value of employee_id.

The warehouses table has the following attributes:

• Columns: warehouse_id (primary key), warehouse_spec, warehouse_name, location_id, and wh_geo_location

• Relationships:

  • Column warehouse_id relates rows of the table warehouses to one or more rows of the table inventories with the corresponding value of warehouse_id.

• Other Relationships:

  • Column location_id relates one or more rows of the table warehouses to one row of the table hr.locations with the corresponding value of location_id.