Essay About Foreign Key And Primary Key

Oracle Code Handout
Essay Preview: Oracle Code Handout
Report this essay
POS 410
Student Handouts
SQL to hand to Student to Reproduce Database and Queries on their own.
CREATE TABLE department
dname
VARCHAR(15)
NOT NULL,
dnumber INT
NOT NULL,
PRIMARY KEY (dnumber),
UNIQUE (dname)
CREATE TABLE employee
fname
VARCHAR(15)
NOT NULL,
lname
VARCHAR(15)
NOT NULL,
NOT NULL,
salary
DECIMAL(10,2),
superssn INT,
NOT NULL,
PRIMARY KEY(ssn),
FOREIGN KEY(superssn) REFERENCES employee(ssn),
FOREIGN KEY(dno) REFERENCES department(dnumber)
CREATE TABLE project
pname
VARCHAR(15)
NOT NULL,
pnumber
NOT NULL,
NOT NULL,
PRIMARY KEY (pnumber),
UNIQUE (pname),
FOREIGN KEY (dnum) REFERENCES department(dnumber)
CREATE TABLE works_on
NOT NULL,
NOT NULL,
PRIMARY KEY(essn, pno),
FOREIGN KEY(essn) REFERENCES employee(ssn),
FOREIGN KEY(pno) REFERENCES project(pnumber)
dname,
dnumber
INSERT INTO department VALUES(Research, 1001);
INSERT INTO department VALUES(Accounting, 1002);
INSERT INTO department VALUES(Manufacturing, 1003);
INSERT INTO department VALUES(Human Resources, 1004);
fname
lname
salary
superssn dno
INSERT INTO employee VALUES(Eileen,
MacAdoo,
12345987, 65000, 12345987, 1004);
INSERT INTO employee VALUES(Nora,
Watkins,
45123987, 35500, 12345987, 1001);
INSERT INTO employee VALUES(Mary Anne, Lazarro,
32145878, 60000, 12345987, 1003);
INSERT INTO employee VALUES(Clara,
Thompson,
03412344, 53000, 12345987, 1003);
INSERT INTO employee VALUES(Raymond,
Thompson,
02932455, 22200, 12345987, 1002);
INSERT INTO employee VALUES(Ziggy,
Gravellese,
45698755, 35000, 12345987, 1002);
INSERT INTO employee VALUES(Frankie,
Thompson,
32425444, 17500, 12345987, 1001);
INSERT INTO employee VALUES(Jeanne,
Dyer,
52455666, 43000, 12345987, 1001);
INSERT INTO employee VALUES(Tony,
Aero,
52432455, 20000, 12345987, 1003);
INSERT INTO employee VALUES(Jonathon, Gravellese,
98765422, 52300, 12345987, 1003);
pname
pnumber
INSERT INTO project VALUES(projectA, 222, 1001);
INSERT INTO project VALUES(projectB, 333, 1003);
INSERT INTO project VALUES(projectC, 122, 1003);
INSERT INTO project VALUES(projectD, 232, 1002);
INSERT INTO project VALUES(projectE, 244, 1004);
INSERT INTO project VALUES(projectF, 400, 1002);
INSERT INTO works_on VALUES(45123987, 222);
INSERT INTO works_on VALUES(45123987, 333);
INSERT INTO works_on VALUES(45123987, 244);
INSERT INTO works_on VALUES(03412344, 222);
INSERT INTO works_on VALUES(03412344, 122);
INSERT INTO works_on VALUES(12345987, 400);
INSERT INTO works_on VALUES(12345987, 244);
INSERT INTO works_on VALUES(02932455, 222);
INSERT INTO works_on VALUES(02932455, 400);
INSERT INTO works_on VALUES(32425444, 400);
INSERT INTO works_on VALUES(32425444, 333);
INSERT INTO works_on VALUES(32425444, 244);
INSERT INTO works_on VALUES(98765422, 122);
INSERT INTO works_on VALUES(98765422, 244);