Nt1310 Unit 3 Assignment Pdf

Part 1

1. Write a script which will Create the needed tables as follows:
a. Table name : DEPT
i. Column datatype Mandatory? PK? FK? Deptno NUMBER(2) Yes Yes dname VARCHAR2(30) Yes No loc VARCHAR2(20) No No

Ans: CREATE TABLE dept (deptno NUMBER(2) NOT NULL, dname VARCHAR2(30) NOT NULL, loc VARCHAR2(20) CONSTRAINT deptno_pk PRIMARY KEY (deptno));

b. Table name: EMP
i. Column datatype Mandatory? PK? FK? Empno NUMBER(4) Yes Yes
ename VARCHAR(2) Yes No
Hiredate DATE No No Job VARCHAR2(15) No No Mgrno number(4) no no yes ( to emp.empno) Salary number(8,2) no no Comm number(8,2) no no Deptno number(2) no no yes ( to
dept.deptno) Ans: CREATE TABLE emp (empno NUMBER(4) NOT NULL, ename VARCHAR2(2) NOT NULL, hiredate DATE NULL, job VARCHAR2(15) NULL, mgrno NUMBER(4) NULL, salary NUMBER(8,2) NULL, comm NUMBER(8,2) NULL, deptno NUMBER(2) NULL CONSTRAINT empno_pk PRIMARY KEY (empno) CONSTRAINT emp_fk FOREIGN KEY (mgrno) REFERENCES emp (empno) CONSTRAINT dept_fk FOREIGN KEY (deptno) REFERENCES dept (deptno));

2. Draw the E/R diagram based on the above two tables This is to be done with a tool such as Visio or Toad. Notation used must be like the one used in the Hoffer Book

3. Add to the script code that will populate the above two tables with the following data
a. Use Insert statements

Ans: INSERT INTO dept (deptno,dname,loc) VALUES (10, 'Accounting ', 'Boston ');
INSERT INTO dept (deptno,dname,loc) VALUES (20, 'Sales ', 'Boston ');
INSERT INTO dept (deptno,dname,loc) VALUES (30, 'Marketing ', 'Boston ');
INSERT INTO dept (deptno,dname,loc) VALUES (40, 'Receiving ', 'BOSTON ');
/
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (1000, 'smith ', '01-jan-1977 ', 'clerk ',2000,12345.67,200,10);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (1010, 'jones ', '02-jan-1977 ', 'mgr ',2000,212345.67,200,10);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (1020, 'manus ', '01-jan-1988 ', 'test ',2000,12345.67,200,10);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (1030, 'tanus ', '01-jan-1997 ', 'math ',1080,12345.67,200,20);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (1040, 'smath ', '01-jan-2007 ', 'clerk ',1080,12345.67,200,20);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (1050, 'sith ', '01-jan-2008 ', 'clerk ',2000,12345.67,200,10);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES ((1060, 'fith ', '01-jan-1990 ', 'clerk ',2000,12345.67,200,30);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (1070, 'lith ', '012-jan-1991 ', 'spvsr ',2000,12345.67,200,30);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (1080, 'rith ', '01-jan-2001 ', 'clerk ',2000,12345.67,200,30);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (2000, 'keith ', '01-jan-1977 ', 'clerk ',3000,12345.67,200,20);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (3000, 'tony ', '01-jan-1977 ', 'president ',,12345.67,200,10);

b.
Use SQL*Loader
For dept table:
Control File: load data infile 'dept.dat ' "var 3" into table dept fields terminated by ', ' optionally enclosed by '" ' (col1 NUMBER(2), col2 VARCHAR2(30), col3 VARCHAR2(20) )

From Command Prompt: SQLLDR CONTROL=dept.ctl, LOG=bar.log, BAD=baz.bad, DATA=dept.dat USERID=student/student, ERRORS=999, LOAD=2000, DISCARD=toss.dis, DISCARDMAX=5

For emp table:
Control File: load data infile 'emp.dat ' "var 3" into table emp fields terminated by ', ' optionally enclosed by '" ' (col1 NUMBER(4), col2 VARCHAR(2), col3 DATE, col4 VARCHAR2(15), col5 number(4), col6 number(8,2), col7 number(8,2), col8 number(2))

From Command Prompt: SQLLDR CONTROL=emp.ctl, LOG=bar.log, BAD=baz.bad, DATA=emp.dat USERID=student/student, ERRORS=999, LOAD=2000, DISCARD=toss.dis, DISCARDMAX=5

DEPT table
Deptno
Dname
Loc
10
Accounting
Boston
20
Sales
Boston
30
Marketing
Boston
40
Receiving
BOSTON

EMP table 1000 smith 01-jan-1977 clerk 2000 12345.67 200 10 1010 jones 02-jan-1977 mgr 2000 212345.67 200 10 1020 manus 01-jan-1988 test 2000 12345.67 200 10 1030 tanus 01-jan-1997 math 1080 12345.67 200 20 1040 smath 01-jan-2007 clerk 1080 12345.67 200 20 1050 sith 01-jan-2008 clerk 2000 12345.67 200 10 1060 fith 01-jan-1990 clerk 2000 12345.67 200 30 1070 lith 012-jan-1991 spvsr 2000 12345.67 200 30 1080 rith 01-jan-2001 clerk 2000 12345.67 200 30 2000 keith 01-jan-1977 clerk 3000 12345.67 200 20
3000 tony 01-jan-1977 president 12345.67 200 10

Add additional records so that if you were to output the records with their one-to-many relationship the following listing would appear. Write the appropriate SELECT statement to produce the following hierarchical structure as well.

3000 2000 1000 8000 9000 1010 8001 8002 8003 1020 8004 8005 8006 1050 1060 1070 1080 1030 1040

Ans: INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (8000, 'neal ', '01-dec-1977 ', 'mgr ',1000,12345.67,200,10);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (9000, 'peter ', '01-dec-1977 ', 'clerk ',8000,12345.67,200,10);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (8001, 'burke ', '01-dec-1977 ', 'clerk ',1010,12345.67,200,10);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (8002, 'castle ', '01-dec-1977 ', 'clerk ',1010,12345.67,200,10);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (8003, 'rick ', '01-dec-1977 ', 'clerk ',1010,12345.67,200,10);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (8004, 'richard ', '01-dec-1977 ', 'clerk ',1020,12345.67,200,10);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (8005, 'kate ', '01-dec-1977 ', 'clerk ',1020,12345.67,200,10);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (8006, 'kevin ', '01-dec-1977 ', 'clerk ',1020,12345.67,200,10);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (1030, 'roy ', '01-dec-1977 ', 'clerk ',1080,12345.67,200,10);
INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (1040, 'javier ', '01-dec-1977 ', 'clerk ',1080,12345.67,200,10);

select lpad( ' ',2*(level-1)) || to_char(empno) s from emp start with mgrno is null connect by prior empno = mgrno;

4. Add to the script a SELECT statement which will output the department no, dept name,
Ename and hiredate for all department in ‘boston’.

Ans: SELECT deptno AS Department_No, dname AS Department_Name, ename AS Employee_Name, hiredate AS Hire_Date FROM dept d, emp e WHERE e.loc = 'boston '; 5. What do you notice happening when you type ‘boston’ as shown?
Ans: It will throw an error stating that

6. What do you need to do to correct it?
Ans: Convert the case of the data into lowercase and then match it with the string 'boston '

7. Write the script to correct it.
Ans: SELECT deptno AS Department_No, dname AS Department_Name, ename AS Employee_Name, hiredate AS Hire_Date FROM dept d, emp e WHERE LOWER(e.loc) = 'boston ';

8. Even though the users told you that they were certain that the columns they mentioned to you are final, John Mitchell , their supervisor thought of adding the empskillCode( Employee Skill Code) to the EMP table , and he had some good reasons for it, but he did not mention them to his subordinates. Can you think of what these reasons might be? Ans: Every employee can have more than one skill. So it can be a one to many relationship. It has to be added in another table and then then empskillcode column can be included in the emp table.

8. What do you need to do to change the structure of the appropriate table to add the new column?
Make the code change .

Ans: ALTER TABLE emp ADD empskillcode NUMBER(2);

10.What about if we want to efficiently keep track of the lengthy Skill description? What is the best way to implement this ? Ans: have to create the skill table with a column to hold the description of the skills.

11. Make the necessary changes to the ER data model. see attached diagram

12. Write the PL/SQL code which will retrieve a particular record from the EMP table. Ans:

13. Save the output produced by all the above commands in advora_lab1.lis

14.Turn in all scripts/files two weeks from today

see attached script

15. Using the "Toad" tool, create the E/R diagram for the "Musical" database, based on the script given in class, which creates the schema for it.