Implementing the Complete Company Database
For Complete YouTube Video: Click Here
In this class, we will be Implementing the Complete Company Database.
We have already discussed the concept to convert the ER-Diagram to Database Schema.
Table of Contents
Implementing the Complete Company Database
The image below is the Database schema for the company database.
In the following line, we will be Implementing the Complete Company Database.
Complete Queries for Implementing the Complete Company Database
Implementing the Complete Company Database Creating the tables.
SQL STATEMENT TO CREATE EMPLOYEE TABLE
CREATE TABLE employee ( Fname varchar (15) NOT NULL, Minit varchar(1), Lname varchar(15) NOT NULL, SSN varchar(9) NOT NULL, Bdate date, Address varchar(30), Sex varchar(1), Salary float, Super_ssn varchar(9), Dno int NOT NULL, PRIMARY KEY (ssn), FOREIGN KEY (Super_ssn) REFERENCES employee(ssn));
SQL STATEMENT TO CREATE DEPARTMENT TABLE
CREATE TABLE department (Dname varchar(15) NOT NULL, Dnumber int NOT NULL, Mgr_ssn varchar(9) NOT NULL, Mgr_start_date date, PRIMARY KEY (Dnumber), UNIQUE(Dname), FOREIGN KEY (Mgr_ssn) REFERENCES employee(ssn));
SQL STATEMENT TO CREATE DEPT_LOCATIONS TABLE
CREATE TABLE dept_locations (Dnumber int NOT NULL, Dlocation varchar(15), PRIMARY KEY (Dnumber, Dlocation), FOREIGN KEY (Dnumber) REFERENCES department (Dnumber));
SQL STATEMENT TO CREATE PROJECT TABLE
CREATE TABLE project (Pname varchar(15) NOT NULL, Pnumber int NOT NULL, Plocation varchar(15), Dnum int NOT NULL, PRIMARY KEY (Pnumber), UNIQUE (Pname), FOREIGN KEY (Dnum) REFERENCES department (Dnumber));
SQL STATEMENT TO CREATE WORKS_ON TABLE
CREATE TABLE works_on (Essn varchar(9) NOT NULL, Pno int NOT NULL, Hours float NOT NULL, PRIMARY KEY (Essn, Pno), FOREIGN KEY (Essn) REFERENCES employee(ssn), FOREIGN KEY (Pno) REFERENCES project(Pnumber));
SQL STATEMENT TO CREATE DEPENDENT TABLE
CREATE TABLE dependent (Essn varchar(9) NOT NULL, Dependent_name varchar(15) NOT NULL, Sex varchar(1), Bdate date, Relationship varchar(8), PRIMARY KEY (Essn, Dependent_name), FOREIGN KEY (Essn) REFERENCES employee(ssn));
Implementing the Complete Company Database Inserting values into the tables.
SQL STATEMENT FOR INSERTING VALUES INTO EMPLOYEE TABLE
INSERT INTO employee (Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary, Super_ssn, Dno) values (‘James’, ‘E’, ‘Borg’, 888665555, ’10-NOV-1937′, ‘430 Stone, Houston, TX’, ‘M’, 55000, NULL, 1);
INSERT INTO employee (Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary, Super_ssn, Dno) values (‘Jennifer’, ‘S’, ‘Wallace’, 987654321, ’20-JUN-1941′, ‘291 Berry, Bellaire, TX’, ‘F’,43000, 888665555, 4);
INSERT INTO employee (Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary, Super_ssn, Dno) values (‘Franklin’, ‘B’, ‘Wong’, 333445555, ’08-DEC-1955′, ‘638 Voss, Houston, TX’, ‘M’, 40000, 888665555, 5);
INSERT INTO employee (Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary, Super_ssn, Dno) values (‘Alicia’, ‘J’, ‘Zelaya’, 999887777, ’19-JAN-1968′, ‘3321 Castle, Spring, TX’, ‘F’, 25000, 987654321, 4);
INSERT INTO employee (Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary, Super_ssn, Dno) values (‘Ramesh’, ‘K’, ‘Narayan’, 666884444, ’15-SEP-1962′, ‘975 Fire Oak, Humble, TX’, ‘M’, 38000, 333445555, 5);
INSERT INTO employee (Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary, Super_ssn, Dno) values (‘Joyce’, ‘A’, ‘English’, 453453453, ’31-JUL-1972′, ‘5631 Rice, Houston, TX’, ‘F’, 25000, 333445555, 5);
INSERT INTO employee (Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary, Super_ssn, Dno) values (‘Ahmad’, ‘V’, ‘Jabbar’, 987987987, ’29-MAR-1969′, ‘980 Dallas, Houston, TX’, ‘M’, 25000, 987654321, 4);
INSERT INTO employee (Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary, Super_ssn, Dno) values (‘John’, ‘B’, ‘Smith’, 123456789, ’09-JAN-1965′, ‘731 Fondren, Houston, TX’, ‘M’, 30000, 333445555, 5);
SQL STATEMENT FOR INSERTING VALUES INTO EMPLOYEE TABLE
INSERT INTO department (Dname, Dnumber, Mgr_ssn, Mgr_start_date) values (‘Research’, 5, 333445555, ’22-MAY-1988′);
INSERT INTO department (Dname, Dnumber, Mgr_ssn, Mgr_start_date) values (‘Administration’, 4, 987654321, ’01-JAN-1995′);
INSERT INTO department (Dname, Dnumber, Mgr_ssn, Mgr_start_date) values (‘Headquaters’, 1, 888665555, ’19-JUN-1981′);
SQL STATEMENT FOR ALTERING EMPLOYEE TABLE TO ADD FOREIGN KEY CONSTRAINT
ALTER TABLE employee ADD CONSTRAINT FK_Manages FOREIGN KEY (Dno) REFERENCES department (Dnumber);
SQL STATEMENT FOR INSERTING VALUES INTO DEPT_LOCATIONS TABLE
INSERT INTO dept_locations (Dnumber, Dlocation) values (1, ‘Houston’);
INSERT INTO dept_locations (Dnumber, Dlocation) values (4, ‘Stafford’);
INSERT INTO dept_locations (Dnumber, Dlocation) values (5, ‘Bellaire’);
INSERT INTO dept_locations (Dnumber, Dlocation) values (5, ‘Sugarland’);
INSERT INTO dept_locations (Dnumber, Dlocation) values (4, ‘Houston’);
SQL STATEMENT FOR INSERTING VALUES IN TO PROJECT TABLE
INSERT INTO project (Pname, Pnumber, Plocation, Dnum) values (‘ProductX’, 1, ‘Bellaire’, 5);
INSERT INTO project (Pname, Pnumber, Plocation, Dnum) values (‘ProductY’, 2, ‘Sugarland’, 5);
INSERT INTO project (Pname, Pnumber, Plocation, Dnum) values (‘ProductZ’, 3, ‘Houston’, 5);
INSERT INTO project (Pname, Pnumber, Plocation, Dnum) values (‘Computerization’, 10, ‘Stafford’, 4);
INSERT INTO project (Pname, Pnumber, Plocation, Dnum) values (‘Reorganization’, 20, ‘Houston’, 1);
INSERT INTO project (Pname, Pnumber, Plocation, Dnum) values (‘Newbenfits’, 30, ‘Stafford’, 4);
SQL STATEMENT FOR INSERTING VALUES INTO WORKS_ON TABLE
INSERT INTO works_on (Essn, Pno, Hours) values (123456789, 1, 32.5);
INSERT INTO works_on (Essn, Pno, Hours) values (123456789, 2, 7.5);
INSERT INTO works_on (Essn, Pno, Hours) values (666884444, 3, 40.0);
INSERT INTO works_on (Essn, Pno, Hours) values (453453453, 1, 20.0);
INSERT INTO works_on (Essn, Pno, Hours) values (453453453, 2, 20.0);
INSERT INTO works_on (Essn, Pno, Hours) values (333445555, 2, 10.0);
INSERT INTO works_on (Essn, Pno, Hours) values (333445555, 3, 10.0);
INSERT INTO works_on (Essn, Pno, Hours) values (333445555, 10, 10.0);
INSERT INTO works_on (Essn, Pno, Hours) values (333445555, 20, 10.0);
INSERT INTO works_on (Essn, Pno, Hours) values (999887777, 30, 30.0);
INSERT INTO works_on (Essn, Pno, Hours) values (999887777, 10, 10.0);
INSERT INTO works_on (Essn, Pno, Hours) values (987987987, 10, 35.0);
INSERT INTO works_on (Essn, Pno, Hours) values (987987987, 30, 5.0);
INSERT INTO works_on (Essn, Pno, Hours) values (987654321, 30, 20.0);
INSERT INTO works_on (Essn, Pno, Hours) values (987654321, 20, 15.0);
INSERT INTO works_on (Essn, Pno, Hours) values (888665555, 20, 0.0);
SQL STATEMENT FOR INSERTING VALUES INTO DEPENDENT TABLE
INSERT INTO dependent (Essn, Dependent_name, Sex, Bdate, Relationship) values (333445555, ‘Alice’, ‘F’, ’05-APR-1986′, ‘Daughter’);
INSERT INTO dependent (Essn, Dependent_name, Sex, Bdate, Relationship) values (333445555, ‘Theodore’, ‘M’, ’25-OCT-1983′, ‘Son’);
INSERT INTO dependent (Essn, Dependent_name, Sex, Bdate, Relationship) values (333445555, ‘Joy’, ‘F’, ’03-MAY-1958′, ‘Spouse’);
INSERT INTO dependent (Essn, Dependent_name, Sex, Bdate, Relationship) values (987654321, ‘Abner’, ‘M’, ’28-FEB-1942′, ‘Spouse’);
INSERT INTO dependent (Essn, Dependent_name, Sex, Bdate, Relationship) values (123456789, ‘Michael’, ‘M’, ’04-JAN-1988′, ‘Son’);
INSERT INTO dependent (Essn, Dependent_name, Sex, Bdate, Relationship) values (123456789, ‘Alice’, ‘F’, ’30-DEC-1988′, ‘Daughter’);
INSERT INTO dependent (Essn, Dependent_name, Sex, Bdate, Relationship) values (123456789, ‘Elizabeth’, ‘F’, ’05-MAY-1967′, ‘Spouse’);
Company Database After Creating and Inserting the values into the database
The image below is the Company Database After Creating and Inserting the values into the database.
Implementing the Complete Company Database will help in our previous classes to fetch the data from the database.
Implementing the Complete Company Database on your own for more practice.