Modern database

Topics: SQL, Roman numerals, Relational model Pages: 5 (1376 words) Published: September 28, 2014
Part 1

1. Write a script which will Create the needed tables as follows: a. Table name : DEPT
i. Columndatatype Mandatory? PK?FK? DeptnoNUMBER(2)YesYes dname VARCHAR2(30)YesNo locVARCHAR2(20)NoNo

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. Columndatatype Mandatory? PK? FK? EmpnoNUMBER(4)YesYes enameVARCHAR(2)Yes No Hiredate DATE No No

Job VARCHAR2(15) No No
Mgrno number(4) no noyes ( to emp.empno)
Salarynumber(8,2) no no
Commnumber(8,2) no no
Deptnonumber(2) no noyes ( 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...


References: INSERT INTO emp (empno,ename,hiredate,job,mgrno,salary,comm,deptno) VALUES (3000, 'tony ', '01-jan-1977 ', 'president ',,12345.67,200,10);
b
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);
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Database Research Paper
  • Relational Database System – Sql Tutorial Essay
  • Database for Scheduling Essay
  • Database Assignment Essay
  • Database Management Essay
  • Database Paper
  • Essay about Database
  • Essay on Hypermedia database

Become a StudyMode Member

Sign Up - It's Free