DLSQL, ORACLE CREATE DATABASE 1. Develop scripts to create a database in Oracle

ID: 3873533 • 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 download the Excel file named company to see the entity names.

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. Please edit and repost the correct code.
https://codeshare.io/anPO0j

Explanation / Answer

FIRST way:

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 PROJECT cascade constraint';
EXECUTE IMMEDIATE 'drop table WORKS_ON cascade constraint';
EXECUTE IMMEDIATE 'drop sequence dependent_seq';

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));

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(20) NULL,
PRIMARY KEY (DEPENDENT_NO),
FOREIGN KEY(SSN) REFERENCES EMPLOYEE(SSN));

/

CREATE TABLE LOCATION
(LOCATION_NO NUMBER(15,0) NOT NULL,
LOCATION VARCHAR2(15) NULL,
PRIMARY KEY (LOCATION_NO));
/

--Wrong metadata. The datatype is given number in metadata but the data given is of varhar.
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 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 employee drop constraint SYS_C004230560*/
/*
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, to_date('1971-06-19','yyyy-mm-dd'));
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENTNAME, MANAGER_SSN, MANAGER_STARTDATE) VALUES (4, 'administration', 987987987, to_date('1985-01-01','yyyy-mm-dd'));
INSERT INTO DEPARTMENT (DEPT_NO, DEPARTMENTNAME, MANAGER_SSN, MANAGER_STARTDATE) VALUES (5, 'research', 333445555, to_date('1978-05-22','yyyy-mm-dd'));

--Since john and terry has same ssn being PK it wont insert. It is given wrong in metadata
INSERT INTO EMPLOYEE (SSN, FIRSTNAME, MI, LASTNAME, DOB, ADDRESS, CITY, STATE, ZIP, SEX, SALARY, SUPERVISOR_SSN, DEPT_NO) VALUES (123456789, 'Roberto', 'B', 'Tamburello',to_date('1955-01-09','yyyy-mm-dd'), '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',to_date('1945-12-08','yyyy-mm-dd'), '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', to_date('1962-07-31','yyyy-mm-dd'), '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', to_date('1952-08-15','yyyy-mm-dd'), '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', to_date('1991-11-10','yyyy-mm-dd'), '450 Stone', 'Houston', 'TX', '11233', 'M', 55000,null, 1);
INSERT INTO EMPLOYEE (SSN, FIRSTNAME, MI, LASTNAME, DOB, ADDRESS, CITY, STATE, ZIP, SEX, SALARY, SUPERVISOR_SSN, DEPT_NO) VALUES (987654321, 'Terry', 'S', 'Duffy', to_date('1990-06-20','yyyy-mm-dd'), '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', to_date('1959-03-29','yyyy-mm-dd'), '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', to_date('1958-07-19','yyyy-mm-dd'), '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', to_date('1938-07-19','yyyy-mm-dd'), '3321 Castle', 'Spring', 'TX', '11239', 'F', 35000, 987654321,3);

ALTER TABLE DEPARTMENT ADD CONSTRAINT fk_dept FOREIGN KEY (MANAGER_SSN) REFERENCES EMPLOYEE(SSN);
--wrong metadata. In employee table John's dept no is 3 which not in parent table.Thus below Foreign key statement will fail.
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;
*/

--Wrong metadata.The records for the dept_location table cannot be entered as the columns are created with number but data is in varchar.
/*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');
--Wrong metadata. ProductX has duplicate records so only 1 record will get inserted.
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;

Second way:

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('');
END;
/

CREATE TABLE LOCATION
(LOCATION_NO NUMBER(15,0) NOT NULL,
LOCATION VARCHAR2(15) NULL,
PRIMARY KEY (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));

/

--SET DEFINE OFF;

--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');
ALTER TABLE DEPENDENT ADD CONSTRAINT fk_DEPENDENT FOREIGN KEY (SSN) REFERENCES EMPLOYEE(SSN);
/*
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;
*/

COMMIT;

Navigate

DLSQL, ORACLE CREATE DATABASE 1. Develop scripts to create a database in Oracle

DLW Corporation acquired and placed in service the following assets during the y

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

DLSQL, ORACLE CREATE DATABASE 1. Develop scripts to create a database in Oracle

Question

Explanation / Answer

Related Questions

Navigate