Exercicio de BD

BANCO DE DADOS I

ITAQUAQUECETUBA
2013
INTRODUÇÃO
Neste trabalho estaremos utilizando o software brModelo e o SQL Fiddle para construir um banco de dados e posteriormente fazer pesquisas nele. No brModelo são gerados os modelos conceitual, lógico e físico e no SQL Fiddle são a criadas as tabelas, inseridos os dados e as tabelas são consultadas por meio dos selects.

Modelo conceitual:

Modelo lógico:

MODELO FÍSICO
CREATE TABLE job_history ( start_date date PRIMARY KEY, end_date date, department_id number(3), job_id number(3), employee_id number(3)
);
CREATE TABLE departments ( department_id number(3) PRIMARY KEY, department_name varchar2(30), location_id number(3), employee_id number(3)
);
CREATE TABLE regions ( region_name varchar2(10), region_id number(3) PRIMARY KEY
);
CREATE TABLE jobs ( job_id number(3) PRIMARY KEY, job_title varchar2(15), min_salary float, max_salary float
);
CREATE TABLE locations ( location_id number(3) PRIMARY KEY, street_address varchar2(50), postal_code varchar2(10), city varchar2(30), state_province varchar2(30), country_id number(3)
);
CREATE TABLE countries ( country_id number(3) PRIMARY KEY, country_name varchar2(30), region_id number(3),
FOREIGN KEY(region_id) REFERENCES regions (region_id)
);
CREATE TABLE Auto_1 ( employee_id number(3), possui_employee_id number(3)
);
ALTER TABLE job_history ADD FOREIGN KEY(department_id) REFERENCES departments (department_id);
ALTER TABLE job_history ADD FOREIGN KEY(job_id) REFERENCES jobs (job_id);
ALTER TABLE job_history ADD FOREIGN KEY(employee_id) REFERENCES employees (employee_id);
ALTER TABLE departments ADD FOREIGN KEY(location_id) REFERENCES locations (location_id);
ALTER TABLE locations ADD FOREIGN KEY(country_id) REFERENCES countries (country_id);
CREATE TABLE employees ( employee_id number(3) PRIMARY KEY, first_name varchar2(20), last_name varchar2(20),
email