Search This Blog

Saturday, May 18, 2013

HR Schema

The HR demonstration schema consists of seven tables, linked by primary key to foreign key relationships.
The tables are:

  • REGIONS has rows for major geographical areas.
  • COUNTRIES has rows for each country, which are optionally assigned to a region.
  • LOCATIONS includes individual addresses, which are optionally assigned to a country.
  • DEPARTMENTS has a row for each department, optionally assigned to a location and optionally with a manager (who must exist as an employee).
  • EMPLOYEES has a row for every employee, each of whom must be assigned to a job and optionally to a department and to a manager. The managers must themselves be employees.
  • JOBS lists all possible jobs in the organization. It is possible for many employees to have the same job.
  • JOB_HISTORY lists previous jobs held by employees, uniquely identified by employee_id and start_date; it is not possible for an employee to hold two jobs concurrently. Each job history record will refer to one employee, who will have had one job at that time and may have been a member of one department.
These commands, which could be issued from SQL*Plus or SQL Developer, will make it possible to log on as users HR and OE using the passwords HR:
alter user hr account unlock identified by hr;
These alter user commands can only be issued when connected to the database as a user with DBA privileges, such as the user SYSTEM.

No comments: