DLSQL, ORACLE CREATE DATABASE 1. Develop scripts to create a database in Oracle
ID: 3873012 • Letter: D
Question
DLSQL, ORACLE CREATE DATABASE
1. Develop scripts to create a database in Oracle DBMS
Your task is to download the Company data stored in spreadsheet ( Company.xls) and do the followings:
2. Analyze this spreadsheet and develop CREATE TABLE script posted for this database. Do this in two ways
2.1 Foreign key is part of create table command – (unless the relationship does not permit the foreign key to be part of create command).
2.2 Foreign key is not part of the create table but it is implemented using ALTER command
You can refer to the data dictionary that is posted for this assignment
3. Using notepad, develop an INSERT script to populate the Company database in your Oracle schema. Make sure the order of attributes in your INSERT script matches the order of attributes defined in the CREATE TABLE script
4. Place your insert commands after the CREATE TABLE commands and save it as ‘populate_Company_DB’
5. Place your code within an anonymous block (PL/SQL program). This program should include exception handling to catch error and take correct action if error happens
6. Run the program and make sure that database can be created without any error. If you run the script again, it has to run without any errors- (drop, create and insert).
*****************************************************************************************************************************************************************
Please note that there are 2 files, one with foreign keys included in create table and one where the foreignkeys are in alter commands. Please
You can find the Company XLSX file here: https://drive.google.com/open?id=0Bzum6VJXi9lUdzJYa0tZbHhpbUk
Here is my attempt at doing this question. Somewhat unsuccessful, but you can use it to improvise
https://codeshare.io/anPO0j
Explanation / Answer
Checked your code. Everything is fine but one thing is left in "DEPARTMENT" table.
There is a reference key of EMPLOYEE table of SSN column in DEPARTMENT table is left.
can alter the the table using ALTER COMMAND do that the table meets the all requirement primary key and foreign key.
here is the code for altering the DEPARTMENT table.
ALTER TABLE DEPARTMENT
ADD FOREIGN KEY (MANAGER_SSN)
REFERENCES EMPLOYEE(SSN);
this code will add the foreign key in department table.
and one table that is not mension in excel of data dictionary "STAGING_DEPLOCATION"
save the all code using ant text editor with file name extension .sql like "mydatabase.sql"
below are all the code that have to save as a sql file after that execute the file in SQL prompt like:
SQL>@/your_saved_directory_Path/mydatabase.sql
when this will run everything will be creted and inserted in your database. Thanks.
Bewlow are the code for mydatabase.sql file saved.
BEGIN
EXECUTE IMMEDIATE 'drop table DEPARTMENT cascade constraint';
EXECUTE IMMEDIATE 'drop table EMPLOYEE cascade constraint';
EXECUTE IMMEDIATE 'drop table DEPENDENT cascade constraint';
EXECUTE IMMEDIATE 'drop table DEPT_LOCATION cascade constraint';
EXECUTE IMMEDIATE 'drop table LOCATION cascade constraint';
EXECUTE IMMEDIATE 'drop table STAGING_DEPLOCATION cascade constraint';
EXECUTE IMMEDIATE 'drop table PROJECT cascade constraint';
EXECUTE IMMEDIATE 'drop table WORKS_ON cascade constraint';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('');
END;
/
CREATE TABLE DEPARTMENT
(DEPT_NO NUMBER(15,0) NOT NULL,
DEPARTMENTNAME VARCHAR2(15) NOT NULL,
MANAGER_SSN NUMBER(9,0) NULL,
MANAGER_STARTDATE DATE NULL,
PRIMARY KEY (DEPT_NO));
/
/
CREATE TABLE EMPLOYEE
(SSN NUMBER(9,0) NOT NULL,
FIRSTNAME VARCHAR2(15) NOT NULL,
MI VARCHAR2(5) NULL,
LASTNAME VARCHAR2(15) NOT NULL,
DOB DATE NULL,
ADDRESS VARCHAR2(100) NOT NULL,
CITY VARCHAR2(20) NULL,
STATE VARCHAR2(20) NULL,
ZIP VARCHAR2(5) NULL,
SEX VARCHAR2(1) NULL,
SALARY NUMBER(15,0) NULL,
SUPERVISOR_SSN NUMBER(9) NULL,
DEPT_NO NUMBER(15,0) NULL,
PRIMARY KEY (SSN),
FOREIGN KEY(DEPT_NO) REFERENCES DEPARTMENT(DEPT_NO));
/
CREATE TABLE DEPENDENT
(DEPENDENT_NO NUMBER(15,0) NOT NULL,
SSN NUMBER(9,0) NOT NULL,
FIRSTNAME VARCHAR2(15) NOT NULL,
LASTNAME VARCHAR2(15) NULL,
DOB DATE NULL,
SEX VARCHAR2(1) NULL,
RELATIONSHIP VARCHAR2(1) NULL,
PRIMARY KEY (DEPENDENT_NO),
FOREIGN KEY(SSN) REFERENCES EMPLOYEE(SSN));
/
CREATE TABLE DEPT_LOCATION
(DEPT_NO NUMBER(15,0) NOT NULL,
LOCATION_NO NUMBER(15,0) NOT NULL,
ISACTIVE CHAR(10) NULL,
PRIMARY KEY (DEPT_NO, LOCATION_NO),
FOREIGN KEY(DEPT_NO) REFERENCES DEPARTMENT(DEPT_NO)
FOREIGN KEY(LOCATION_NO) REFERENCES LOCATION(LOCATION_NO));
/
CREATE TABLE EMPLOYEE
(SSN NUMBER(9,0) NOT NULL,
FIRSTNAME VARCHAR2(15) NOT NULL,
MI VARCHAR2(5) NULL,
LASTNAME VARCHAR2(15) NOT NULL,
DOB DATE NULL,
CITY VARCHAR2(20) NULL,
STATE VARCHAR2(20) NULL,
ZIP VARCHAR2(5) NULL,
SEX VARCHAR2(1) NULL,
SALARY NUMBER(15,0) NULL,
SUPERVISOR_SSN NUMBER(9) NULL,
DEPT_NO NUMBER(15,0) NULL,
PRIMARY KEY (SSN),
FOREIGN KEY(DEPT_NO) REFERENCES DEPARTMENT(DEPT_NO));
/
CREATE TABLE LOCATION
(LOCATION_NO NUMBER(15,0) NOT NULL,
LOCATION VARCHAR2(15) NULL,
PRIMARY KEY (LOCATION_NO));
/
CREATE TABLE STAGING_DEPLOCATION(
LOCATION VARCHAR2(50) NOT NULL,
DEPARTMENT VARCHAR2(50) NOT NULL,
ISACTIVE CHAR(10) NULL,
PRIMARY KEY (DEPT_NO, LOCATION_NO),
FOREIGN KEY(DEPT_NO) REFERENCES DEPARTMENT(DEPT_NO)
FOREIGN KEY(LOCATION_NO) REFERENCES LOCATION(LOCATION_NO));
/
CREATE TABLE PROJECT
(PROJECT_NO NUMBER(15,0) NOT NULL,
DEPT_NO NUMBER(15,0) NOT NULL,
LOCATION_NO NUMBER(10,0) NULL,
PROJECTNAME VARCHAR2(20) NULL,
PRIMARY KEY (PROJECT_NO),
FOREIGN KEY(DEPT_NO) REFERENCES DEPARTMENT(DEPT_NO),
FOREIGN KEY(LOCATION_NO) REFERENCES LOCATION(LOCATION_NO));
/
CREATE TABLE WORKS_ON
(SSN NUMBER(15,0) NOT NULL,
PROJECT_NO NUMBER(15,0) NOT NULL,
HOURS NUMBER(15,0) NULL,
PRIMARY KEY (SSN, PROJECT_NO));
/
ALTER TABLE DEPARTMENT
ADD FOREIGN KEY (MANAGER_SSN)
REFERENCES EMPLOYEE(SSN)
/
create sequence dependent_seq
start with 1
increment by 1
nocache
nocycle;
SET DEFINE OFF;
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENTNAME, MANAGER_SSN, MANAGER_STARTDATE) VALUES ('1', 'headquarters', '888665555', '1971-06-19');
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENTNAME, MANAGER_SSN, MANAGER_STARTDATE) VALUES ('2', 'administration', '987987987', '1985-01-01');
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENTNAME, MANAGER_SSN, MANAGER_STARTDATE) VALUES ('3', 'research', '333445555', '1978-05-22');
INSERT INTO EMPLOYEE (SSN, FIRSTNAME, MI, LASTNAME, DOB, ADDRESS, CITY, STATE, ZIP, SEX, SALARY, SUPERVISOR_SSN, DEPT_NO) VALUES ('123456789', 'Roberto', 'B', 'Tamburello', '1955-01-09', '731 Fondren', 'Houston', 'TX', '11233'. 'M', '30000', '333445555', '5');
INSERT INTO EMPLOYEE (SSN, FIRSTNAME, MI, LASTNAME, DOB, ADDRESS, CITY, STATE, ZIP, SEX, SALARY, SUPERVISOR_SSN, DEPT_NO) VALUES ('333445555', 'David', 'T', 'Bradley', '1945-12-08', '38 Voss', 'Houston', 'TX', '11233', 'M', '40000', '888665555', '5');
INSERT INTO EMPLOYEE (SSN, FIRSTNAME, MI, LASTNAME, DOB, ADDRESS, CITY, STATE, ZIP, SEX, SALARY, SUPERVISOR_SSN, DEPT_NO) VALUES ('453453453', 'Mary', 'A', 'Dempsey', '1962-07-31', '5631 Rice', 'Houston', 'TX', '11233', 'F', '25000', '333445555', '5');
INSERT INTO EMPLOYEE (SSN, FIRSTNAME, MI, LASTNAME, DOB, ADDRESS, CITY, STATE, ZIP, SEX, SALARY, SUPERVISOR_SSN, DEPT_NO) VALUES ('666884444', 'Ramesh', 'K', 'Narayan', '1952-08-15', '975 Fire Oak', 'Humble', 'TX', '11232', 'M', '38000', '333445555', '5');
INSERT INTO EMPLOYEE (SSN, FIRSTNAME, MI, LASTNAME, DOB, ADDRESS, CITY, STATE, ZIP, SEX, SALARY, SUPERVISOR_SSN, DEPT_NO) VALUES ('888665555', 'James', 'E', 'Borg', '1991-11-10', '450 Stone', 'Houston', 'TX', '11233', 'M', '55000', '', '1');
INSERT INTO EMPLOYEE (SSN, FIRSTNAME, MI, LASTNAME, DOB, ADDRESS, CITY, STATE, ZIP, SEX, SALARY, SUPERVISOR_SSN, DEPT_NO) VALUES ('987654321', 'Terry', 'S', 'Duffy', '1990-06-20', '231 Berry', 'Bellaire', 'TX', '11236', 'F', '43000', '888665555', '4');
INSERT INTO EMPLOYEE (SSN, FIRSTNAME, MI, LASTNAME, DOB, ADDRESS, CITY, STATE, ZIP, SEX, SALARY, SUPERVISOR_SSN, DEPT_NO) VALUES ('987987987', 'Jossef', 'V', 'Goldberg', '1959-03-29', '980 Dallas', 'Houston', 'TX', '11233', 'M', '25000', '987654321', '4');
INSERT INTO EMPLOYEE (SSN, FIRSTNAME, MI, LASTNAME, DOB, ADDRESS, CITY, STATE, ZIP, SEX, SALARY, SUPERVISOR_SSN, DEPT_NO) VALUES ('999887777', 'Terry', 'J', 'Zelaya', '1958-07-19', '3321 Castle', 'Spring', 'TX', '11239', 'F', '25000', '987654321', '4');
INSERT INTO EMPLOYEE (SSN, FIRSTNAME, MI, LASTNAME, DOB, ADDRESS, CITY, STATE, ZIP, SEX, SALARY, SUPERVISOR_SSN, DEPT_NO) VALUES ('999887777', 'John', '', 'Clay', '1938-07-19', '3321 Castle', 'Spring', 'TX', '11239', 'F', '35000', '987654321', '3');
ALTER TABLE DEPARTMENT ADD CONSTRAINT fk_dept FOREIGN KEY (MANAGER_SSN) REFERENCES EMPLOYEE(SSN);
ALTER TABLE EMPLOYEE ADD CONSTRAINT fk_empdept FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT(DEPT_NO);
CREATE SEQUENCE dependent_seq START WITH 1;
insert into DEPENDENT (DEPENDENT_NO,SSN,FIRSTNAME,LASTNAME,DOB,SEX,RELATIONSHIP) VALUES (dependent_seq.nextval,123456789, 'Lili','Tamburello',to_date( '1978-12-31','yyyy-mm-dd'),'F','DAUGHTER');
Insert into DEPENDENT (DEPENDENT_NO,SSN,FIRSTNAME,LASTNAME,DOB,SEX,RELATIONSHIP) VALUES (dependent_seq.nextval,123456789, 'Anna','Tamburello',to_date( '1957-05-05','yyyy-mm-dd'),'F','SPOUSE');
Insert into DEPENDENT (DEPENDENT_NO,SSN,FIRSTNAME,LASTNAME,DOB,SEX,RELATIONSHIP) VALUES (dependent_seq.nextval,123456789, 'Gregory','Tamburello',to_date( '1978-01-01','yyyy-mm-dd'),'M','SON');
Insert into DEPENDENT (DEPENDENT_NO,SSN,FIRSTNAME,LASTNAME,DOB,SEX,RELATIONSHIP) VALUES (dependent_seq.nextval,333445555, 'Alice','Bradley',to_date( '1976-04-05','yyyy-mm-dd'),'F','DAUGHTER');
Insert into DEPENDENT (DEPENDENT_NO,SSN,FIRSTNAME,LASTNAME,DOB,SEX,RELATIONSHIP) VALUES (dependent_seq.nextval,333445555, 'Theodore','Bradley',to_date( '1973-10-25','yyyy-mm-dd'),'M','SON');
Insert into DEPENDENT (DEPENDENT_NO,SSN,FIRSTNAME,LASTNAME,DOB,SEX,RELATIONSHIP) VALUES (dependent_seq.nextval,987654321, 'Abner','Duffy',to_date( '1969-02-28','yyyy-mm-dd'),'M','SPOUSE');
Insert into DEPENDENT (DEPENDENT_NO,SSN,FIRSTNAME,LASTNAME,DOB,SEX,RELATIONSHIP) VALUES (dependent_seq.nextval,987654321, 'Aby','John',to_date( '1970-02-28','yyyy-mm-dd'),'M','SPOUSE');
INSERT INTO DEPT_LOCATION(DEPT_NO, LOCATION_NO, ISACTIVE)
SELECT DEPT_NO, LOCATION_NO, ISACTIVE FROM STAGING_DEPLOCATION A
INNER JOIN LOCATION B ON B.LOCATION = A.LOCATION
INNER JOIN DEPARTMENT D ON D.DEPARTMENTNAME = A.DEPARTMENTNAME;
INSERT INTO DEPT_LOCATION (DEPT_NO, LOCATION_NO, ISACTIVE) VALUES ('headquarters', 'Houston', '');
INSERT INTO DEPT_LOCATION (DEPT_NO, LOCATION_NO, ISACTIVE) VALUES ('administration', 'Stafford', '');
INSERT INTO DEPT_LOCATION (DEPT_NO, LOCATION_NO, ISACTIVE) VALUES ('research', 'Bellaire', '');
INSERT INTO DEPT_LOCATION (DEPT_NO, LOCATION_NO, ISACTIVE) VALUES ('research', 'Houston', '');
INSERT INTO DEPT_LOCATION (DEPT_NO, LOCATION_NO, ISACTIVE) VALUES ('research', 'Sugarland', '');
INSERT INTO LOCATION (LOCATION_NO, LOCATION) VALUES ('1', 'Bellaire');
INSERT INTO LOCATION (LOCATION_NO, LOCATION) VALUES ('2', 'Houston');
INSERT INTO LOCATION (LOCATION_NO, LOCATION) VALUES ('3', 'Stafford');
INSERT INTO LOCATION (LOCATION_NO, LOCATION) VALUES ('4', 'Sugarland');
INSERT INTO PROJECT (PROJECT_NO, DEPT_NO, LOCATION_NO, PROJECTNAME) VALUES ('1', '5', '1', 'ProductX');
INSERT INTO PROJECT (PROJECT_NO, DEPT_NO, LOCATION_NO, PROJECTNAME) VALUES ('2', '5', '4', 'ProductY');
INSERT INTO PROJECT (PROJECT_NO, DEPT_NO, LOCATION_NO, PROJECTNAME) VALUES ('3', '5', '2', 'ProductZ');
INSERT INTO PROJECT (PROJECT_NO, DEPT_NO, LOCATION_NO, PROJECTNAME) VALUES ('10', '4', '3', 'Computerization');
INSERT INTO PROJECT (PROJECT_NO, DEPT_NO, LOCATION_NO, PROJECTNAME) VALUES ('20', '1', '2', 'Reorganization');
INSERT INTO PROJECT (PROJECT_NO, DEPT_NO, LOCATION_NO, PROJECTNAME) VALUES ('30', '4', '3', 'Newbenefits');
INSERT INTO PROJECT (PROJECT_NO, DEPT_NO, LOCATION_NO, PROJECTNAME) VALUES ('1', '5', '1', 'ProductX');
INSERT INTO WORKS_ON (SSN, PROJECT_NO, HOURS) VALUES ('123456789', '1', '33');
INSERT INTO WORKS_ON (SSN, PROJECT_NO, HOURS) VALUES ('123456789', '2', '8');
INSERT INTO WORKS_ON (SSN, PROJECT_NO, HOURS) VALUES ('333445555', '2', '10');
INSERT INTO WORKS_ON (SSN, PROJECT_NO, HOURS) VALUES ('333445555', '3', '10');
INSERT INTO WORKS_ON (SSN, PROJECT_NO, HOURS) VALUES ('333445555', '10', '10');
INSERT INTO WORKS_ON (SSN, PROJECT_NO, HOURS) VALUES ('333445555', '20', '10');
INSERT INTO WORKS_ON (SSN, PROJECT_NO, HOURS) VALUES ('453453453', '1', '20');
INSERT INTO WORKS_ON (SSN, PROJECT_NO, HOURS) VALUES ('453453453', '2', '20');
INSERT INTO WORKS_ON (SSN, PROJECT_NO, HOURS) VALUES ('666884444', '3', '40');
INSERT INTO WORKS_ON (SSN, PROJECT_NO, HOURS) VALUES ('888665555', '20', '');
INSERT INTO WORKS_ON (SSN, PROJECT_NO, HOURS) VALUES ('987654321', '20', '15');
INSERT INTO WORKS_ON (SSN, PROJECT_NO, HOURS) VALUES ('987654321', '30', '20');
INSERT INTO WORKS_ON (SSN, PROJECT_NO, HOURS) VALUES ('987987987', '10', '35');
INSERT INTO WORKS_ON (SSN, PROJECT_NO, HOURS) VALUES ('987987987', '30', '5');
INSERT INTO WORKS_ON (SSN, PROJECT_NO, HOURS) VALUES ('999887777', '10', '10');
INSERT INTO WORKS_ON (SSN, PROJECT_NO, HOURS) VALUES ('999887777', '30', '30');
COMMIT;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.