Appendix A
----------------------------------------------------------------------------------------------------------------
The Company Database Schema
Throughout the textbook you will study SQL statements
that query and modify information stored in tables for the Company database. This is one of two databases that are used
with this textbook, the second being the Riverbend Hospital database
described in Appendix B.
Database Schema
Figure A.1 shows a diagram of the six tables that
comprise the Company database. The table
names are underlined in the figure, for example employee, and the column
names are shown inside the rectangles, for example emp_ssn.
Figure A.1
Relationships between the tables are shown with
connecting lines. Consider the line connecting
the employee and dependent tables. Figure A.2
shows you how to interpret the relationship connecting lines.
Figure A.2
Reading from the employee
towards the dependent table, the line
has a circle and three-legged “crow’s foot” meaning this is an optional
relationship (circle) such that each employee can optionally (circle) have no
dependents. The three-legged crow’s foot
means an employee can have one or more dependents. Reading
from the dependent towards the employee table, the line is crossed by
two short lines indicating that this relationship is mandatory in this
direction such that each dependent can belong to one and only one employee – a
dependent cannot exist without an employee.
Figure A.2 shows two other symbol combinations. The mandatory relationship with one or more
dependent table rows per employee row – this would be the situation if the
Company only hired employees who already had a dependent, but this is not the
case so this symbol was not used in Figure A.1.
The other is the optional zero or one employee per dependent and this is
also not the case so this symbol was not used in Figure A.1. However, you may find other relationships
that use these symbol combinations.
Table Definitions
Department Table
The Company is organized into separate
departments. Tables A.1 and A.2 describe
the columns in the department table
and show the test data stored for each department. Each department is identified by a unique
department number (dpt_no) and has a department name. Each department has a manager and date on
which the manager started managing the department. The manager's social security number (dpt_mgrssn) stored in the department table provides a FOREIGN KEY
link to the specific employee that manages a department.
Table A.1
|
TABLE NAME:
department
|
|||
|
Column Name
|
Datatype
|
Size
|
Comments
|
|
dpt_no
|
NUMBER
|
2
|
Primary Key. Number
assigned by the company.
|
|
dpt_name
|
VARCHAR2
|
20
|
Department name.
|
|
dpt_mgrssn
|
CHAR
|
9
|
Foreign Key
link to employee that manages this department.
|
|
dpt_mgr_start_date
|
DATE
|
|
Date a manager was assigned
to supervise the department.
|
Table A.2
|
TABLE DATA: department
|
|||
|
dpt_no
|
dpt_name
|
dpt_mgrssn
|
dpt_mgr_start_date
|
|
7
|
Production
|
999444444
|
05-22-1998
|
|
3
|
Admin and Records
|
999555555
|
01-01-2001
|
|
1
|
Headquarters
|
999666666
|
06-19-1981
|
SQL Example A.1 gives the
CREATE TABLE command for the department
table. Two constraints are named, one
for the department primary key and the other to ensure that each department row
must have a department name (NOT NULL).
/* SQL Example A.1 */
CREATE TABLE department (
dpt_no NUMBER(2)
CONSTRAINT
pk_department PRIMARY KEY,
dpt_name VARCHAR2(20)
CONSTRAINT
nn_dpt_name NOT NULL,
dpt_mgrssn CHAR(9),
dpt_mgr_start_date DATE
);
Dept_Locations Table
Each department may have multiple city locations
within the Company. In order to provide
for normalized data maintenance, a separate dept_locations table stores
location information. Tables A.3 and A.4
describe the columns and actual data stored in the dept_locations table. The
primary key of the dept_locations
table is a composite of dpt_no and dpt_location. There are no non-key columns in the
table. The dpt_no column also serves as a FOREIGN KEY link back to the department table.
Table A.3
|
TABLE NAME:
dept_locations
|
|||
|
Column Name
|
Datatype
|
Size
|
Comments
|
|
dpt_no
|
NUMBER
|
2
|
Primary Key. Number
assigned by the company.
Also Foreign Key link to department table.
|
|
dpt_location
|
VARCHAR2
|
20
|
Primary Key. Department name.
|
Table A.4
|
TABLE DATA:
dept_locations
|
|
|
dpt_no
|
dpt_location
|
|
1
|
Edwardsville
|
|
3
|
Marina
|
|
7
|
St. Louis
|
|
7
|
Collinsville
|
|
7
|
Edwardsville
|
SQL Example A.2 shows the code to create
dept_locations. As you will learn from
your study of Chapter 2, the constraint to create the primary key is a special
form of the PRIMARY KEY clause that is used to create composite keys from two
or more columns. There is also a FOREIGN
KEY clause to specify that dpt_no
references the department column.
/* SQL Example A.2 */
CREATE TABLE dept_locations (
dpt_no NUMBER(2),
dpt_location VARCHAR2(20),
CONSTRAINT pk_dept_locations
PRIMARY KEY (
dpt_no, dpt_location ),
CONSTRAINT fk_dept_loc_no
FOREIGN KEY (dpt_no)
REFERENCES department
) ;
Employee Table
Employee information is stored in the employee
table. Tables A.5 and A.6 describe the
columns in the employee table and the
data stored in the table. Each employee
is identified by their social security number (emp_ssn) as a primary
key.
Table A.5
|
TABLE NAME:
employee
|
|||
|
Column Name
|
Datatype
|
Size
|
Comments
|
|
emp_ssn
|
CHAR
|
9
|
Primary Key. Employee
social security number.
|
|
emp_last_name
|
VARCHAR2
|
25
|
Employee last name.
|
|
emp_first_name
|
VARCHAR2
|
25
|
Employee first name.
|
|
emp_middle_name
|
VARCHAR2
|
25
|
Employee middle name.
|
|
emp_address
|
VARCHAR2
|
50
|
Employee street address.
|
|
emp_city
|
VARCHAR2
|
25
|
City where employee
resides.
|
|
emp_state
|
CHAR
|
2
|
Two-character abbreviation
of the state of residence
|
|
emp_zip
|
CHAR
|
9
|
Zip code for mailing.
|
|
emp_date_of_birth
|
DATE
|
|
Employee date of birth.
|
|
emp_salary
|
NUMBER
|
(7,2)
|
Employee monthly salary.
|
|
emp_parking_space
|
NUMBER
|
4
|
Number of parking space
allocated to the employee.
|
|
emp_gender
|
CHAR
|
1
|
Code – M=male; F=female.
|
|
emp_dpt_number
|
NUMBER
|
2
|
Foreign Key link to department table. Department to which an employee is
assigned.
|
|
emp_superssn
|
CHAR
|
9
|
Foreign Key link to employee record supervising this employee.
|
Table A.6
|
emp_ssn
|
emp_last
_name
|
emp_first
_name
|
emp_
middle_
name
|
emp_address
|
emp_city
|
emp_
state
|
|
999666666
|
Bordoloi
|
Bijoy
|
|
South Main #12
|
Edwardsville
|
IL
|
|
999555555
|
Joyner
|
Suzanne
|
A
|
202 Burns Farm
|
Marina
|
CA
|
|
999444444
|
Zhu
|
Waiman
|
Z
|
303 Lindbergh
|
St. Louis
|
MO
|
|
999887777
|
Markis
|
Marcia
|
M
|
High St. #14
|
Monterey
|
CA
|
|
999222222
|
Amin
|
Hyder
|
|
S. Seaside Apt B
|
Marina
|
CA
|
|
999111111
|
Bock
|
Douglas
|
B
|
#2 Mont Verd Dr.
|
St. Louis
|
MO
|
|
999333333
|
Joshi
|
Dinesh
|
|
#10 Oak St
|
Collinsville
|
IL
|
|
999888888
|
Prescott
|
Sherri
|
C
|
Overton Way #4
|
Edwardsville
|
IL
|
|
emp_zip
|
emp_date_
of_birth
|
emp_salary
|
emp_parking_
space
|
emp_
gender
|
emp_dpt
_number
|
emp_
superssn
|
|
62025
|
11-10-1967
|
55000
|
1
|
M
|
1
|
|
|
93941
|
06-20-1971
|
43000
|
3
|
F
|
3
|
999666666
|
|
63121
|
12-08-1975
|
43000
|
32
|
M
|
7
|
999666666
|
|
93940
|
07-19-1978
|
25000
|
402
|
F
|
3
|
999555555
|
|
93941
|
03-29-1969
|
25000
|
422
|
M
|
3
|
999555555
|
|
63121
|
09-01-1955
|
30000
|
542
|
M
|
7
|
999444444
|
|
66234
|
09-15-1972
|
38000
|
332
|
M
|
7
|
999444444
|
|
62025
|
07-31-1972
|
25000
|
296
|
F
|
7
|
999444444
|
Employees work in
departments. Employees also manage
departments. Thus, Figure A.1 shows
these two relationships: Manage-Dept
and Works-Here. The Manage-Dept
relationship links each row in the department
table to the employee table row for
the employee that manages the department.
The Works-Here relationship
links the employee table to the department table for the department to
which each employee is assigned to work.
You should interpret the Works-Here relationship as meaning that a department can have zero,
one, or more than one assigned employees (crow’s foot), and that each employee
is assigned to one and only one (mandatory) department. SQL Example A.3 shows the CREATE TABLE
command for the employee table. The Works-Here
relationship is implemented by the FOREIGN KEY constraint named fk_emp_dpt.
The Manage-Dept
relationship is interpreted as each department has one and only one manager,
and any given employee may optionally manage a department. Of course, most employees will not manage a
department. You will not find the
FOREIGN KEY constraint to enforce the Manage-Dept
relationship in the CREATE TABLE command for either the employee or department
tables. This is because a constraint
cannot be created for a non-existent table and one of the two tables has to be
created first! Instead, this constraint
is added to the database after all of the tables are created.
Employees are supervised by other employees. This is a unary or recursive relationship
between rows within a single table and is shown as the Supervise relationship in Figure A.1. The Supervise
relationship is enforced by the FOREIGN KEY constraint named fk_emp_superssn that links a given employee to their
supervisor; thus, the employee supervisor’s social security number (emp_superssn)
is stored as a column in the table.
This relationship is covered in detail in Chapter 6.
/* SQL Example A.3 */
CREATE TABLE employee (
emp_ssn CHAR(9)
CONSTRAINT
pk_employee PRIMARY KEY,
emp_last_name VARCHAR2(25)
CONSTRAINT
nn_emp_last_name NOT NULL,
emp_first_name VARCHAR2(25)
CONSTRAINT
nn_emp_first_name NOT NULL,
emp_middle_name VARCHAR2(25),
emp_address VARCHAR2(50),
emp_city VARCHAR2(25),
emp_state CHAR(2),
emp_zip CHAR(9),
emp_date_of_birth DATE,
emp_salary NUMBER(7,2)
CONSTRAINT
ck_emp_salary
CHECK
(emp_salary <= 85000),
emp_parking_space
NUMBER(4)
CONSTRAINT
un_emp_parking_space UNIQUE,
emp_gender CHAR(1),
emp_dpt_number NUMBER(2),
emp_superssn CHAR(9),
CONSTRAINT fk_emp_dpt FOREIGN KEY (emp_dpt_number)
REFERENCES
department
ON DELETE SET
NULL,
CONSTRAINT fk_emp_superssn FOREIGN KEY (emp_superssn)
REFERENCES employee
ON DELETE SET NULL
) ;
Projects Table
Projects for the Company are controlled or
supervised by departments. Each project
is identified by a project number (pro_number) and the firm tracks each
project by name, and location. Tables
A.7 and A.8 show the columns and data for the project table. Figure A.1
shows the Controls-Proj
relationship. A department may have
zero, one or more than one active projects and a project belongs to one and
only one department.
Table A.7
|
TABLE NAME:
project
|
|||
|
Column Name
|
Datatype
|
Size
|
Comments
|
|
pro_number
|
NUMBER
|
2
|
Primary Key. Number
assigned by the company.
|
|
pro_name
|
VARCHAR2
|
25
|
Project name.
|
|
pro_location
|
VARCHAR2
|
25
|
Project location.
|
|
pro_dept_number
|
NUMBER
|
2
|
Foreign Key link to department controlling the project.
|
Table A.8
|
TABLE DATA: project
|
|||
|
pro_number
|
pro_name
|
pro_location
|
pro_dept_number
|
|
1
|
Order Entry
|
St. Louis
|
7
|
|
2
|
Payroll
|
Collinsville
|
7
|
|
3
|
Receivables
|
Edwardsville
|
7
|
|
10
|
Inventory
|
Marina
|
3
|
|
20
|
Personnel
|
Edwardsville
|
1
|
|
30
|
Pay Benefits
|
Marina
|
3
|
SQL Example A.3 gives the CREATE TABLE command for
the project table. The fk_pro_dept_number
FOREIGN KEY constraint implements the Controls-Proj
relationship.
/* SQL Example A.4 */
CREATE TABLE project (
pro_number NUMBER(2)
CONSTRAINT
pk_project PRIMARY KEY,
pro_name VARCHAR2(25)
CONSTRAINT
nn_pro_name NOT NULL,
pro_location VARCHAR2(25),
pro_dept_number NUMBER(2),
CONSTRAINT fk_pro_dept_number FOREIGN KEY (pro_dept_number)
REFERENCES
department
);
Assignment Table
Each employee is assigned to work on zero, one or
more projects. This is reflected in
Figure A.1 by the assignment table
and two relationships: Works-On and Has-Employee. The assignment
table is an association table because it associates the employee and project tables. Tables A.9
and A.10 show the columns and data for the assignment
table. The primary key for assignment
is a composite of the primary key from the employee
table combined with the primary key of the project
table: the work_emp_ssn and work_pro_number columns.
Table A.9
|
TABLE NAME:
assignment
|
|||
|
Column Name
|
Datatype
|
Size
|
Comments
|
|
work_emp_ssn
|
CHAR
|
9
|
Primary Key. Employee
social security number. Also Foreign Key link to employee table.
|
|
work_pro_number
|
NUMBER
|
2
|
Primary Key. Project
number. Also Foreign Key link
to the project table.
|
|
work_hours
|
NUMBER
|
(5,1)
|
Number of hours an employee has worked on a project.
|
|
work_hours_planned
|
NUMBER
|
(5,1)
|
Number of planned (estimated) hours an employee will work on a
project in order to complete their tasking.
|
Table A.10
|
TABLE DATA: assignment
|
||
|
work_emp_ssn
|
work_pro_number
|
work_hours
|
|
999111111
|
1
|
31.4
|
|
999111111
|
2
|
8.5
|
|
999333333
|
3
|
42.1
|
|
999888888
|
1
|
21.0
|
|
999888888
|
2
|
22.0
|
|
999444444
|
2
|
12.2
|
|
999444444
|
3
|
10.5
|
|
999444444
|
1
|
|
|
999444444
|
10
|
10.1
|
|
999444444
|
20
|
11.8
|
|
999887777
|
30
|
30.8
|
|
999887777
|
10
|
10.2
|
|
999222222
|
10
|
34.5
|
|
999222222
|
30
|
5.1
|
|
999555555
|
30
|
19.2
|
|
999555555
|
20
|
14.8
|
|
999666666
|
20
|
|
SQL Example A.5 gives the CREATE TABLE command to
create the assignment table. The PRIMARY KEY clause implements the primary
key in the same fashion as was done earlier for the dept_locations table. Two
FOREIGN KEY constraints, fk_work_emp
and fk_work_pro_number implement the Works-On and Has-Employee relationships by referencing back to the respective employee and project tables. You will
learn about the ON DELETE clauses when you study Chapter 2.
/* SQL Example A.5 */
CREATE TABLE assignment (
work_emp_ssn CHAR(9),
work_pro_number NUMBER(2),
work_hours NUMBER(5,1),
work_hours_planned NUMBER(5,1),
CONSTRAINT pk_assignment
PRIMARY KEY (
work_emp_ssn, work_pro_number ),
CONSTRAINT fk_work_emp
FOREIGN KEY
(work_emp_ssn) REFERENCES employee
ON DELETE
CASCADE,
CONSTRAINT fk_work_pro_number
FOREIGN KEY
(work_pro_number) REFERENCES project
ON DELETE
CASCADE
) ;
Dependent Table
Figure A.1 shows the Has-Dependent relationship between the employee and dependent
tables. Tables A.11 and A.12 describe
the columns and data for the dependent
table. An employee may have zero, one or
more dependents, and a dependent belongs to one and only one employee. The firm only tracks minimal information
about dependents in order to satisfy governmental reporting requirements for
taxation and education purposes. Each dependent table row must reference an
existing employee row in the employee
table. The primary key of dependent is a
composite of dep_emp_ssn and dep_name. The dep_emp_ssn
column also serves to link dependent
rows to the employee table.
Table A.11
|
TABLE NAME:
dependent
|
|||
|
Column Name
|
Datatype
|
Size
|
Comments
|
|
dep_emp_ssn
|
CHAR
|
9
|
Primary Key. Employee
social security number for this
dependent. Also Foreign Key link to employee table.
|
|
dep_name
|
VARCHAR2
|
50
|
Primary Key. Dependent
name.
|
|
dep_gender
|
CHAR
|
1
|
Dependent gender coded: M=male; F=female.
|
|
dep_date_of_birth
|
DATE
|
|
Dependent date of birth.
|
|
dep_relationship
|
VARCHAR2
|
10
|
Relationship of dependent
to employee, e.g., daughter, spouse, son.
|
Table A.12
|
TABLE DATA:
dependent
|
||||
|
dep_emp_ssn
|
dep_name
|
dep_gender
|
dep_date_of_birth
|
dep_relationship
|
|
999444444
|
Jo Ellen
|
F
|
04-05-1996
|
DAUGHTER
|
|
999444444
|
Andrew
|
M
|
10-25-1998
|
SON
|
|
999444444
|
Susan
|
F
|
05-03-1975
|
SPOUSE
|
|
999555555
|
Allen
|
M
|
02-29-1968
|
SPOUSE
|
|
999111111
|
Jeffery
|
M
|
01-01-1978
|
SON
|
|
999111111
|
Deanna
|
F
|
12-31-1978
|
DAUGHTER
|
|
999111111
|
Michelle
|
F
|
05-05-1957
|
SPOUSE
|
SQL Example A.6 gives the CREATE TABLE command for
the dependent table. The PRIMARY KEY constraint named pk_dependent enforces the composite
primary key. The FOREIGN KEY constraint
named fk_dep_emp_ssn implements the
Has-Dependent relationship.
/* SQL Example A.6 */
CREATE TABLE dependent (
dep_emp_ssn CHAR(9),
dep_name VARCHAR2(50),
dep_gender CHAR(1),
dep_date_of_birth DATE,
dep_relationship VARCHAR2(10),
CONSTRAINT pk_dependent PRIMARY KEY (dep_emp_ssn, dep_name),
CONSTRAINT fk_dep_emp_ssn
FOREIGN KEY
(dep_emp_ssn) REFERENCES employee
ON DELETE
CASCADE
) ;
Equipment Table
Equipment is acquired from a local equipment rental
company for projects. When projects are completed, the equipment is returned;
thus, the Company has no need to maintain a stock of equipment. A project can
have zero, one or more items of equipment as reflected by the Located-At
relationship in Figure A.1. Equipment items are identified by an equipment
number (eqp_no), have a description, value, and quantity on hand. The eqp_pro_number
column in the table is a FOREIGN KEY link to the project table for the
project that is using the equipment. Table A.13 and A.14 describe the columns
and data for the equipment table.
work_pro_number
NUMBER(2),
Table A.13
|
TABLE NAME:
equipment
|
|||
|
Column Name
|
Datatype
|
Size
|
Comments
|
|
eqp_no
|
CHAR
|
4
|
Primary Key. Equipment
number.
|
|
eqp_desc
|
VARCHAR2
|
15
|
Equipment description
|
|
eqp_value
|
NUMBER
|
(10,2)
|
Value of one equipment item.
|
|
eqp_qty_on_hand
|
NUMBER
|
3
|
Quantity of this item on
hand for the project.
|
|
eqp_pro_number
|
NUMBER
|
2
|
Foreign key link to project table
|
Table A.14
|
TABLE DATA:
equipment
|
||||
|
eqp_no
|
eqp_desc
|
eqp_value
|
eqp_qty_on_hand
|
eqp_pro_number
|
|
4321
|
Computer, PC
|
1100
|
2
|
3
|
|
2323
|
Table, mobile
|
245.5
|
3
|
2
|
|
6987
|
Computer, PC
|
849.5
|
2
|
1
|
|
1234
|
Chair, mobile
|
78.25
|
4
|
2
|
|
5678
|
Printer
|
172
|
2
|
30
|
|
9876
|
Computer, Ntpad
|
1400.23
|
2
|
30
|
|
4321
|
Computer, PC
|
1100
|
2
|
3
|
/* Create equipment table */
CREATE TABLE equipment (
eqp_no CHAR(4),
CONSTRAINT pk_equipment PRIMARY KEY,
eqp_description VARCHAR2(15),
eqp_value NUMBER(10,2),
eqp_quantity_on_hand
NUMBER(3),
eqp_pro_number NUMBER(2),
CONSTRAINT fk_eqp_pro_number
FOREIGN KEY
(eqp_pro_number) REFERENCES project
ON DELETE CASCADE
);
Additional Table Constraints
Recall that earlier we discussed the Manage-Dept relationship between the department and employee tables. The FOREIGN
KEY constraint for this relationship can be added to the department table definition now that the employee table has been created.
You will study how to alter tables to add constraints in Chapter 2.
/* SQL Example A.7 */
ALTER TABLE department ADD CONSTRAINT fk_dept_emp
FOREIGN KEY (dpt_mgrssn)
REFERENCES
employee (emp_ssn);
Create Company Tables and Indexes
The CreateCompanyDatabase.sql
script code creates the Oracle tables and indexes for the Company
database. The script also populates the
tables with data. The script assigns
explicit names to all table constraints and indexes. When you study Chapter 12, this will make it
easier for you to examine the data dictionary tables and views that store
constraint and index information for the database.
The script first drops each table to
facilitate re-creating the tables and indexes if necessary. This script can be accessed from the textbook
website and is stored in a script file named CreateCompanyDatabase.sql.
Chapter 1 provides you with a detailed explanation of the procedure you
need to follow in order to create your own copy of the Company database.
REM CreateCompanyDatabase.sql
REM Script to create the Company database to
REM accompany Bordoloi/Bock -- An Introduction to SQL
REM First drop necessary constraints and tables that
REM might already exist in order to create a clean database.
ALTER TABLE department
DROP CONSTRAINT
fk_dept_emp;
DROP TABLE dependent;
DROP TABLE assignment;
DROP TABLE employee;
DROP table equipment;
DROP TABLE project;
DROP table dept_locations;
DROP table department;
REM Create tables
REM Create department table
CREATE TABLE department (
dpt_no NUMBER(2)
CONSTRAINT
pk_department PRIMARY KEY,
dpt_name VARCHAR2(20)
CONSTRAINT
nn_dpt_name NOT NULL,
dpt_mgrssn CHAR(9),
dpt_mgr_start_date DATE
);
REM Create dept_locations table
CREATE TABLE dept_locations (
dpt_no NUMBER(2),
dpt_location VARCHAR2(20),
CONSTRAINT pk_dept_locations
PRIMARY KEY (
dpt_no, dpt_location ),
CONSTRAINT fk_dept_loc_no
FOREIGN KEY (dpt_no)
REFERENCES department
) ;
REM Create project table
CREATE TABLE project (
pro_number NUMBER(2)
CONSTRAINT
pk_project PRIMARY KEY,
pro_name VARCHAR2(25)
CONSTRAINT
nn_pro_name NOT NULL,
pro_location VARCHAR2(25),
pro_dept_number NUMBER(2),
CONSTRAINT fk_pro_dept_number FOREIGN KEY (pro_dept_number)
REFERENCES
department
);
REM Create employee table
CREATE TABLE employee (
emp_ssn CHAR(9)
CONSTRAINT
pk_employee PRIMARY KEY,
emp_last_name VARCHAR2(25)
CONSTRAINT
nn_emp_last_name NOT NULL,
emp_first_name VARCHAR2(25)
CONSTRAINT
nn_emp_first_name NOT NULL,
emp_middle_name VARCHAR2(25),
emp_address VARCHAR2(50),
emp_city VARCHAR2(25),
emp_state CHAR(2),
emp_zip CHAR(9),
emp_date_of_birth DATE,
emp_salary NUMBER(7,2)
CONSTRAINT
ck_emp_salary
CHECK
(emp_salary <= 85000),
emp_parking_space
NUMBER(4)
CONSTRAINT un_emp_parking_space
UNIQUE,
emp_gender CHAR(1),
emp_dpt_number NUMBER(2),
emp_superssn CHAR(9),
CONSTRAINT fk_emp_dpt FOREIGN KEY (emp_dpt_number)
REFERENCES
department
ON DELETE SET
NULL,
CONSTRAINT fk_emp_superssn FOREIGN KEY (emp_superssn)
REFERENCES employee
ON DELETE SET NULL
) ;
REM Create assignment table
CREATE TABLE assignment (
work_emp_ssn CHAR(9),
work_pro_number NUMBER(2),
work_hours NUMBER(5,1),
CONSTRAINT pk_assignment
PRIMARY KEY (
work_emp_ssn, work_pro_number ),
CONSTRAINT fk_work_emp
FOREIGN KEY
(work_emp_ssn) REFERENCES employee
ON DELETE
CASCADE,
CONSTRAINT fk_work_pro_number
FOREIGN KEY (work_pro_number)
REFERENCES project
ON DELETE
CASCADE
) ;
REM Create dependent table
CREATE TABLE dependent (
dep_emp_ssn CHAR(9),
dep_name VARCHAR2(50),
dep_gender CHAR(1),
dep_date_of_birth DATE,
dep_relationship
VARCHAR2(10),
CONSTRAINT pk_dependent PRIMARY KEY (dep_emp_ssn, dep_name),
CONSTRAINT fk_dep_emp_ssn
FOREIGN KEY
(dep_emp_ssn) REFERENCES employee
ON DELETE
CASCADE
) ;
REM Create equipment table
CREATE TABLE equipment (
eqp_no CHAR(4)
CONSTRAINT pk_equipment PRIMARY KEY,
eqp_description VARCHAR2(15),
eqp_value NUMBER(10,2),
eqp_quantity_on_hand
NUMBER(3),
eqp_pro_number NUMBER(2),
CONSTRAINT fk_eqp_pro_number
FOREIGN KEY
(eqp_pro_number) REFERENCES project
ON DELETE CASCADE
);
REM Add FOREIGN KEY constraint between the department
REM and employee tables.
ALTER TABLE department ADD CONSTRAINT fk_dept_emp
FOREIGN KEY
(dpt_mgrssn)
REFERENCES employee (emp_ssn);
REM POPULATE TABLES
REM Department rows.
Department manager SSN
REM and
date_mgr_startdate are null.
INSERT INTO department VALUES ( 7, 'Production', NULL, NULL );
INSERT INTO department VALUES ( 3, 'Admin and Records', NULL,
NULL );
INSERT INTO department VALUES ( 1, 'Headquarters', NULL, NULL
);
REM Dept_locations rows.
INSERT INTO dept_locations VALUES ( 1, 'Edwardsville');
INSERT INTO dept_locations VALUES ( 3, 'Marina');
INSERT INTO dept_locations VALUES ( 7, 'St. Louis');
INSERT INTO dept_locations VALUES ( 7, 'Collinsville');
INSERT INTO dept_locations VALUES ( 7, 'Edwardsville');
COMMIT;
REM Project rows.
INSERT INTO project VALUES ( 1, 'Order Entry', 'St. Louis', 7 );
INSERT INTO project VALUES ( 2, 'Payroll', 'Collinsville', 7 );
INSERT INTO project VALUES ( 3, 'Receivables', 'Edwardsville',
7 );
INSERT INTO project VALUES ( 10, 'Inventory', 'Marina', 3 );
INSERT INTO project VALUES ( 20, 'Personnel', 'Edwardsville',
1 );
INSERT INTO project VALUES ( 30, 'Pay Benefits', 'Marina', 3
);
COMMIT;
REM Employee rows.
INSERT INTO employee VALUES( '999666666', 'Bordoloi', 'Bijoy',
NULL, 'South Main #12', 'Edwardsville', 'IL', 62025,
TO_DATE('11-10-1967', 'MM-DD-YYYY'), 55000, 1, 'M', 1, NULL );
INSERT INTO employee VALUES( '999555555', 'Joyner', 'Suzanne',
'A', '202 Burns
Farm', 'Marina',
'CA', 93941,
TO_DATE('06-20-1971', 'MM-DD-YYYY'), 43000, 3, 'F',
3, '999666666' );
INSERT INTO employee VALUES( '999444444', 'Zhu', 'Waiman',
'Z', '303
Lindbergh', 'St. Louis',
'MO', 63121,
TO_DATE('12-08-1975', 'MM-DD-YYYY'), 43000, 32, 'M',
7, '999666666' );
INSERT INTO employee VALUES( '999887777', 'Markis', 'Marcia',
'M', 'High St. #14', 'Monterey', 'CA', 93940,
TO_DATE('07-19-1978',
'MM-DD-YYYY'), 25000, 402, 'F',
3, '999555555' );
INSERT INTO employee VALUES( '999222222', 'Amin', 'Hyder',
NULL, 'S. Seaside Apt. B', 'Marina', 'CA', 93941,
TO_DATE('03-29-1969', 'MM-DD-YYYY'), 25000, 422, 'M',
3, '999555555' );
INSERT INTO employee VALUES( '999111111', 'Bock', 'Douglas',
'B', '#2 Mont Verd
Dr.', 'St. Louis', 'MO', 63121,
TO_DATE('09-01-1955', 'MM-DD-YYYY'), 30000, 542, 'M',
7, '999444444' );
INSERT INTO employee VALUES( '999333333', 'Joshi', 'Dinesh',
NULL, '#10 Oak St.', 'Collinsville', 'IL',
66234,
TO_DATE('09-15-1972', 'MM-DD-YYYY'), 38000, 332, 'M',
7, '999444444' );
INSERT INTO employee VALUES( '999888888', 'Prescott',
'Sherri',
'C', 'Overton Way #4',
'Edwardsville', 'IL', 62025,
TO_DATE('07-31-1972', 'MM-DD-YYYY'), 25000, 296, 'F',
7, '999444444' );
COMMIT;
REM Assignment rows.
INSERT INTO assignment VALUES ( '999111111', 1, 31.4);
INSERT INTO assignment VALUES ( '999111111', 2, 8.5);
INSERT INTO assignment VALUES ( '999333333', 3, 42.1);
INSERT INTO assignment VALUES ( '999888888', 1, 21.0);
INSERT INTO assignment VALUES ( '999888888', 2, 22.0);
INSERT INTO assignment VALUES ( '999444444', 2, 12.2);
INSERT INTO assignment VALUES ( '999444444', 3, 10.5);
INSERT INTO assignment VALUES ( '999444444', 1, NULL);
INSERT INTO assignment VALUES ( '999444444', 10, 10.1);
INSERT INTO assignment VALUES ( '999444444', 20, 11.8);
INSERT INTO assignment VALUES ( '999887777', 30, 30.8);
INSERT INTO assignment VALUES ( '999887777', 10, 10.2);
INSERT INTO assignment VALUES ( '999222222', 10, 34.5);
INSERT INTO assignment VALUES ( '999222222', 30, 5.1);
INSERT INTO assignment VALUES ( '999555555', 30, 19.2);
INSERT INTO assignment VALUES ( '999555555', 20, 14.8);
INSERT INTO assignment VALUES ( '999666666', 20, NULL);
COMMIT;
REM Dependent rows.
INSERT INTO dependent VALUES ( '999444444', 'Jo Ellen', 'F',
TO_DATE('04-05-1996', 'MM-DD-YYYY'), 'DAUGHTER');
INSERT INTO dependent VALUES ( '999444444', 'Andrew', 'M',
TO_DATE('10-25-1998',
'MM-DD-YYYY'), 'SON');
INSERT INTO dependent VALUES ( '999444444', 'Susan', 'F',
TO_DATE('05-03-1975', 'MM-DD-YYYY'), 'SPOUSE');
INSERT INTO dependent VALUES ( '999555555', 'Allen', 'M',
TO_DATE('02-29-1968', 'MM-DD-YYYY'), 'SPOUSE');
INSERT INTO dependent VALUES ( '999111111', 'Jeffery', 'M',
TO_DATE('01-01-1978', 'MM-DD-YYYY'), 'SON');
INSERT INTO dependent VALUES ( '999111111', 'Deanna', 'F',
TO_DATE('12-31-1978', 'MM-DD-YYYY'), 'DAUGHTER');
INSERT INTO dependent VALUES ( '999111111', 'Mary Ellen', 'F',
TO_DATE('05-05-1957', 'MM-DD-YYYY'), 'SPOUSE');
COMMIT;
REM Equipment rows
INSERT INTO equipment VALUES ('4321', 'Computer, PC', 1100, 2,
3 );
INSERT INTO equipment VALUES ('2323', 'Table, mobile', 245.5,
3, 2 );
INSERT INTO equipment VALUES ('6987', 'Computer, PC', 849.5,
2, 1 );
INSERT INTO equipment VALUES ('1234', 'Chair, mobile', 78.25,
4, 2 );
INSERT INTO equipment VALUES ('5678', 'Printer', 172, 2, 30 );
INSERT INTO equipment VALUES ('9876', 'Computer, Ntpad',
1400.23, 2, 30 );
COMMIT;
REM Update department rows to add manager ssn and start date.
UPDATE department SET dpt_mgrssn = '999444444',
dpt_mgr_start_date =
TO_DATE('05-22-1998', 'MM-DD-YYYY')
WHERE dpt_no =
'7';
UPDATE department SET dpt_mgrssn = '999555555',
dpt_mgr_start_date =
TO_DATE('01-01-2001', 'MM-DD-YYYY')
WHERE dpt_no =
'3';
UPDATE department SET dpt_mgrssn = '999666666',
dpt_mgr_start_date =
TO_DATE('06-19-1981', 'MM-DD-YYYY')
WHERE dpt_no = '1';
COMMIT;
REM Count table rows to ensure the script executed properly.
SELECT COUNT(*) "Department Count Is 3" FROM
department;
SELECT COUNT(*) "Dept Locations Count Is 5" FROM
dept_locations;
SELECT COUNT(*) "Project Count Is 6" FROM project;
SELECT COUNT(*) "Employee Count IS 8" FROM employee;
SELECT COUNT(*) "Assignment Count Is 17" FROM
assignment;
SELECT COUNT(*) "Dependent Count IS 7" FROM
dependent;
SELECT COUNT(*) "Equipment Count IS 6" FROM
equipment;
REM End of Script
No comments:
Post a Comment