Description of the illustration comsc008.eps

This figure shows the relational tables in schema HR 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 schema OE 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.