Oracle SQL Employee Database Creation Script

This is a Oracle SQL Table creation script I made awhile back in college. After running this, you will have a functional normalized employee database up and running. The script works to setup a clean database instance from scratch and could be used as a good base example for you guys and gals in a relational database class or if you are just wanting to see the syntax. It begins executing commands to create an administrator user. Then, it drops all tables and sequences it might find so it can have a clean slate. Tables are then created, sequencing added, data inserted, a view is created, etc. Take a look at the source to see complete function list. Better yet, run it, and see what you end up with.

/* Employee Database Table Creation Script, Platform - Oracle SQL Server/Developer

-- Begin db connection and clear workspace
-- Also suppresses error messages if tables do not exist

-- Create user cam
CONNECT system/Oracle11g;
prompt>Dropping users
DROP USER cam CASCADE;

prompt>Creating users
CREATE USER cam IDENTIFIED BY cam DEFAULT TABLESPACE users;

prompt>Granting privileges
GRANT ALL PRIVILEGES TO cam;

--Connect as user cam
CONNECT cam/cam;

BEGIN
EXECUTE IMMEDIATE 'DROP VIEW Employee_Labs_vw';

EXECUTE IMMEDIATE 'DROP SEQUENCE EmployeeID_seq';
EXECUTE IMMEDIATE 'DROP SEQUENCE LabID_seq';
EXECUTE IMMEDIATE 'DROP SEQUENCE WorkID_seq';

EXECUTE IMMEDIATE 'DROP TABLE tblEmployeeSchedule';
EXECUTE IMMEDIATE 'DROP TABLE tblLab';
EXECUTE IMMEDIATE 'DROP TABLE tblShift';
EXECUTE IMMEDIATE 'DROP TABLE tblEmployee';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('');
END;
/
-- Begin db create tables

CREATE TABLE tblEmployee
(
EmployeeID                INTEGER             NOT NULL,
EmpFName                 VARCHAR2 (25)    NOT NULL,
EmpLName                 VARCHAR2 (25)   NOT NULL,
EmpSSN                     VARCHAR2 (12)    NOT NULL,
JobTitle                 VARCHAR2 (25),
CONSTRAINT tblEmployee_pk
PRIMARY KEY (EmployeeID)
);

CREATE TABLE tblLab
(
LabID                     INTEGER            NOT NULL,
LabDescr                 VARCHAR2(50),
CONSTRAINT tblLab_pk
PRIMARY KEY (LabID)
);

CREATE TABLE tblShift
(
ShiftID                 INTEGER            NOT NULL,
DayofWeek                 CHARACTER(3),
StartTime                 VARCHAR2 (5),
EndTime                 VARCHAR2 (5),
CONSTRAINT tblShift_pk
PRIMARY KEY (ShiftID)
);

CREATE TABLE tblWorksOn
(
WorkID                    INTEGER        NOT NULL,
EmployeeID                INTEGER        NOT NULL,
LabID                    INTEGER     NOT NULL,
CONSTRAINT tblWorksOn_pk
PRIMARY KEY (WorkID),
CONSTRAINT tblWorksOnEmpID_fk
FOREIGN KEY (EmployeeID) REFERENCES tblEmployee(EmployeeID),
CONSTRAINT tblWorksOnLab_fk
FOREIGN KEY (LabID)    REFERENCES tblLab(LabID)
);

CREATE TABLE tblEmployeeSchedule
(
AssignmentID             INTEGER        NOT NULL,
EmployeeID                 INTEGER        NOT NULL,
LabID                     INTEGER        NOT NULL,
ShiftID                    INTEGER        NOT NULL,
WorkID                    INTEGER        NOT NULL,
CONSTRAINT tblEmployeeSchedule_pk
PRIMARY KEY (AssignmentID),
CONSTRAINT tblEmployeeScheduleEmp_fk
FOREIGN KEY (EmployeeID) REFERENCES tblEmployee(EmployeeID),
CONSTRAINT tblEmployeeScheduleLab_fk
FOREIGN KEY (LabID) REFERENCES tblLab(LabID),
CONSTRAINT tblEmployeeScheduleShift_fk
FOREIGN KEY (ShiftID) REFERENCES tblShift(ShiftID),
CONSTRAINTS tblWorksOn_fk
FOREIGN KEY (WorkID) REFERENCES tblWorksOn(WorkID)
);

-- Sequence
CREATE SEQUENCE EmployeeID_seq
START WITH 125
INCREMENT BY 5;
CREATE SEQUENCE LabID_seq
START WITH 225
INCREMENT BY 5;
CREATE SEQUENCE WorkID_seq
START WITH 335
INCREMENT BY 5;

-- Insert employees into db

INSERT INTO tblEmployee
(EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
VALUES (EmployeeID_seq.NEXTVAL,'Cameron', 'Anglin', '123-45-6789', 'Chief Executive Officer');
INSERT INTO tblEmployee
(EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
VALUES (EmployeeID_seq.NEXTVAL,'Jimi', 'Hendrix', '124-78-9923', 'Scientist');
INSERT INTO tblEmployee
(EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
VALUES (EmployeeID_seq.NEXTVAL, 'Linus', 'Torvalds', '332-05-6512', 'Software Engineer');
INSERT INTO tblEmployee
(EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
VALUES (EmployeeID_seq.NEXTVAL, 'Bill', 'Gates', '909-76-3467', 'Software Engineer');
INSERT INTO tblEmployee
(EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
VALUES (EmployeeID_seq.NEXTVAL, 'Clarke', 'Kent', '250-77-9843', 'Experimental Genetics');
INSERT INTO tblEmployee
(EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
VALUES (EmployeeID_seq.NEXTVAL, 'Tony','Stark', '221-32-3234', 'Electrical Engineer');
INSERT INTO tblEmployee
(EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
VALUES (EmployeeID_seq.NEXTVAL, 'Doc', 'Brown', '676-56-8892','Scientist');
INSERT INTO tblEmployee
(EmployeeID, EmpFName, EmpLName, EmpSSN, JobTitle)
VALUES (EmployeeID_seq.NEXTVAL, 'John','Grishsam', '120-45-2156', 'Accountant');

-- Insert employee Labs into db

INSERT INTO tblLab
(LabID, LabDescr)
VALUES (LabID_seq.NEXTVAL, 'Engineering');
INSERT INTO tblLab
(LabID, LabDescr)
VALUES (LabID_seq.NEXTVAL, 'Particle Physics');
INSERT INTO tblLab
(LabID, LabDescr)
VALUES (LabID_seq.NEXTVAL, 'Experimental Genetics');
INSERT INTO tblLab
(LabID, LabDescr)
VALUES (LabID_seq.NEXTVAL, 'Extraterrestrial Relations ');
INSERT INTO tblLab
(LabID, LabDescr)
VALUES (LabID_seq.NEXTVAL, 'Cryptology');
INSERT INTO tblLab
(LabID, LabDescr)
VALUES (LabID_seq.NEXTVAL, 'Human Resources');
INSERT INTO tblLab
(LabID, LabDescr)
VALUES (LabID_seq.NEXTVAL, 'Finance');
INSERT INTO tblLab
(LabID, LabDescr)
VALUES (LabID_seq.NEXTVAL, 'Board');

-- Insert shift types into db

INSERT INTO tblShift
(ShiftID, DayofWeek, StartTime, EndTime)
VALUES (1,'Mon', '07:00', '3:30');
INSERT INTO tblShift
(ShiftID, DayofWeek, StartTime, EndTime)
VALUES (2,'Tue', '07:30', '3:30');
INSERT INTO tblShift
(ShiftID, DayofWeek, StartTime, EndTime)
VALUES (3,'Wed', '07:30', '3:30');
INSERT INTO tblShift
(ShiftID, DayofWeek, StartTime, EndTime)
VALUES (4,'Thu', '07:30', '3:30');
INSERT INTO tblShift
(ShiftID, DayofWeek, StartTime, EndTime)
VALUES (5,'Fri', '07:30', '3:30');

-- Insert into tblWorksOn

INSERT INTO tblWorksOn
(WorkID, EmployeeID, LabID)
VALUES (WorkID_seq.NEXTVAL,125, 260);
INSERT INTO tblWorksOn
(WorkID, EmployeeID, LabID)
VALUES (WorkID_seq.NEXTVAL,130, 235);
INSERT INTO tblWorksOn
(WorkID, EmployeeID, LabID)
VALUES (WorkID_seq.NEXTVAL,135, 225);
INSERT INTO tblWorksOn
(WorkID, EmployeeID, LabID)
VALUES (WorkID_seq.NEXTVAL,140, 225);
INSERT INTO tblWorksOn
(WorkID, EmployeeID, LabID)
VALUES (WorkID_seq.NEXTVAL,145, 235);
INSERT INTO tblWorksOn
(WorkID, EmployeeID, LabID)
VALUES (WorkID_seq.NEXTVAL,150, 225);
INSERT INTO tblWorksOn
(WorkID, EmployeeID, LabID)
VALUES (WorkID_seq.NEXTVAL,155, 230);
INSERT INTO tblWorksOn
(WorkID, EmployeeID, LabID)
VALUES (WorkID_seq.NEXTVAL,160, 255);

-- Create view

CREATE OR REPLACE FORCE VIEW "EMPLOYEE_LABS_VW" ("EMPFNAME", "EMPLNAME", "LABORATORY") AS
SELECT
e.EmpFName,
e.EmpLName,
l.LabDescr AS Laboratory
FROM
tblEmployee e,
tblWorksOn w,
tblLab l
WHERE
e.EmployeeID = w.EmployeeID AND
l.LabID = w.LabID
ORDER BY e.EmpLName;

COMMIT;

Advertisements