Transcript Relational Model Seminar

Seminar 1: Revisiting Databases and SQL


Purpose

To refresh basic concepts of databases.

To refresh SQL.
Scenario – Human Resources (HR)

In the company's human resource records, each employee
has a unique identification number, email address, job
identification number, salary, and manager. Some employees
earn a commission in addition to their salary, which is also
tracked.

The company also tracks information about jobs within the
organization. Each job has an identification number, job
title, and a minimum and maximum salary range for the job.
Some employees have been with the company for a long
time and have held different jobs within the company. When
an employee switches jobs, the company records the start
date and end date of the former job, the job identification
number, and the department.

The sample company is regionally diverse, so it tracks the
locations of not only its warehouses but also of its
departments. Each of the company's employees is assigned
to a department. Each department is identified by a unique
department code and a short name. Each department is
associated with one location. Each location has a full
address that includes the street address, postal code, city,
state or province, and country code.

For each where it has facilities, the company records the
country name, currency symbol, currency name and the
region where the county resides geographically.
Seminar #1 – Refreshing databases concepts and SQL
CM036: Advanced Databases
1
HR Relational Schema

Entity Relationship Diagram (ERD)

Creating Schema


Download the script file: oracle_script.sql.txt into
U:\CM036\W1 from the Blackboard or
http://computing.unn.ac.uk/staff/cgma2/cm036/W
eek1/
Run the script at the SQL* Plus prompt

e.g. @U:\CM036\W1\oracle_script.sql.txt
Seminar #1 – Refreshing databases concepts and SQL
CM036: Advanced Databases
2
Sample Table Descriptions
Seminar #1 – Refreshing databases concepts and SQL
CM036: Advanced Databases
3
Sample Table Descriptions …
Seminar #1 – Refreshing databases concepts and SQL
CM036: Advanced Databases
4
Tasks for the Seminar
A.
Discuss the following questions with the
student next to you:
1.
2.
3.
4.
B.
What is a database?
What are databases used for?
What is a relational database?
What are the basic elements of the relational data
model?
Write SQL queries using Oracle SQL* Plus to
answer the following:
1.
2.
3.
4.
5.
6.
7.
Show the structure of the DEPARTMENTS table.
Select all data from the table.
Create a query to display the last name, job code,
hire date, and employee number for each
employee, with employee number appearing first.
Create a query to display unique job codes from
the EMPLOYEES table.
Create a query to display the last name and
salary of employees earning more than 12,000.
Display the employee last name, job ID, and start
date of employees hired between February 20,
1998, and May 1, 1998. Order the query in
ascending order by start date.
Write a query to display the last name,
department number, and department name for
all employees.
Display the employee last name and department
name for all employees who have an a
(lowercase) in their last names.
Seminar #1 – Refreshing databases concepts and SQL
CM036: Advanced Databases
5
Tasks for the Seminar …
8.
9.
10.
Display the employee last name and employee
number along with their manager’s last name and
manager number. Label the columns Employee,
Emp#, Manager, and Mgr#, respectively.
Create a query that displays employee last names,
department numbers, and all the employees who
work in the same department as a given employee.
Give each column an appropriate label.
Display the names and hire dates for all employees
who were hired before their managers, along with
their manager’s names and hire dates. Label the
columns Employee, Emp Hired, Manager, and
Mgr Hired, respectively.
Seminar #1 – Refreshing databases concepts and SQL
CM036: Advanced Databases
6