Model Answer - ISYS 210
Assignment 2

This answer is adapted from one submitted by a real student (Bryan Duklewski take a bow).
It demonstrates how to use three separate tables to model the person super-type and its contact and student subtypes. 

1. Relational Schema:

Person (person_id, first_name, last_name, middle_name, address_1, address_2,
address_3, city, state, zip, country, phone, fax)
Primary Key (person_id)

Student (person_id, major_1, major_2, minor_1, minor_2, GPA, date_enrolled)
Primary Key (person_id)
Foreign Key (person_id) references person.person_id

Company (company_id, company_name, industry_sector)
Primary_Key (company_id)

Contact (person_id, company_id)
Primary Key (person_id, company_id)
Foreign Key (person_id) references person.person_id
Foreign Key (company_id) references company.company_id

List (code_name, description)
Primary Key(code_name)

List_Item (person_id, code_name)
Primary Key (person_id, code_name)
Foreign Key (person_id) references person.person_id
ForeignKey (code_name) references list.code_name

2. Create Table statements:

CREATE TABLE PERSON (
person_id number(9) NOT NULL,
first_name varchar2(30) NOT NULL,
last_name varchar2(30) NOT NULL,
middle_name varchar2(30),
address_1 varchar2(50) NOT NULL,
address_2 varchar2(50),
address_3 varchar2(50),
city varchar2(30) NOT NULL,
state char(2),
zip varchar2(10) NOT NULL,
country varchar2(30) DEFAULT('USA') NOT NULL,
phone varchar2(20) NOT NULL,
fax varchar2(20),
PRIMARY KEY (person_id)
);

CREATE TABLE STUDENT (
person_id number(9) NOT NULL,
major_1 char(4),
major_2 char(4),
minor_1 char(4),
minor_2 char(4),
GPA number(3,2) DEFAULT(0.00) NOT NULL,
date_enrolled date NOT NULL,
PRIMARY KEY (person_id),
FOREIGN KEY (person_id) REFERENCES person(person_id) ON DELETE CASCADE
);

CREATE TABLE company (
company_id number(9) NOT NULL,
company_name varchar2(30) NOT NULL,
industry_sector varchar2(50) NOT NULL,
PRIMARY KEY (company_id)
);

CREATE TABLE contact (
person_id number(9) NOT NULL,
company_id number(9) NOT NULL,
Primary Key (person_id, company_id),
FOREIGN KEY (person_id) REFERENCES person(person_id)  ON DELETE CASCADE,
FOREIGN KEY (company_id) REFERENCES company(company_id)  ON DELETE CASCADE
);

create table list (
code_name char(10) NOT NULL,
description varchar2(250),
PRIMARY KEY(code_name)drop t
);

CREATE TABLE List_Item (
person_id number(9) NOT NULL,
code_name char(10) NOT NULL,
PRIMARY KEY (person_id, code_name),
FOREIGN KEY (person_id) references person(person_id) ON DELETE CASCADE,
FOREIGN KEY (code_name) references list(code_name)  ON DELETE CASCADE
);

3. Populate data:

/* Notice the use of explicit field lists to simplify this process and
eliminate potential errors. It also means these statements would still work
even if rows were added to the table*/

INSERT INTO PERSON (person_id, first_name, last_name, address_1,
 address_2, city, state, zip, phone)
 VALUES(101, 'Calvin', 'Ripken', 'Edwin',
 '8 Oriole Rd.', 'Aberdeen', 'MD', '21588', '(410)555-9685');

INSERT INTO PERSON (person_id, first_name, last_name, address_1,
 address_2, city, state, zip, phone) 
 VALUES(102, 'Brady', 'Anderson',  '300 Eutaw st.',
 '7th Floor', 'Baltimore', 'MD', '21774', '(410)555-5548');

 INSERT INTO PERSON (person_id, first_name, last_name, address_1,
  address_2, city, state, zip, phone)
 VALUES(103, 'Jerry', 'Garcia', '328 Haight ave.', 
 'Apt B1', 'San Francisco', 'CA', '90254-5568', '(832)555-2267');

INSERT INTO PERSON (person_id, first_name, last_name, middle_name, address_1,
  address_2, city, state, zip, phone, fax) 
 VALUES(104, 'Ron', 'McKernan', 'Pig-Pen', '328 Haight ave.', 
 'Apt B2', 'San Francisco', 'CA', '90254-5568', '(832)555-2276', '(555)584-2267');

INSERT INTO PERSON (person_id, first_name, last_name, address_1,
 address_2, city, state, zip, phone, fax)
 VALUES (105, 'Bob', 'Dylan', '358 S. 12th St.',
 'Apt 317', 'New York', 'NY', '90037', '(542)254-5480', '(542)555 5481');

INSERT INTO PERSON (person_id, first_name, last_name, address_1,
 address_2, address_3, city, country, zip, phone)
 VALUES(106, 'Tommy', 'Haigh', '413 Foggy Dr.',
  '3rd Floor', 'Suite 3', 'London', '23QRZ885', 'England', '20 1115 77789');

INSERT INTO PERSON (person_id, first_name, last_name, address_1,
 address_2, city, state, zip, phone)
 VALUES(107, 'Doug', 'Hill', '123 Yankee La.', 
 'apt 305', 'New York', 'NY', '90036', '(542)254-3366');

INSERT INTO PERSON (person_id, first_name, last_name, address_1,
  address_2, city, state, zip, phone) 
 VALUES(108, 'Penelope', 'Santos', '3405 Race St.',
 '1st Floor', 'Philadelphia', 'PA', '19104', '(255)555-0427');

INSERT INTO PERSON (person_id, first_name, last_name, middle_name, address_1,
 address_2, city, state, zip, phone) 
  VALUES(109, 'Solo', 'Cat', 'The', '3300 Spring Garden St.',
 'Apt. B1', 'Philadelphia', 'PA', '19104', '(215)555-2547');

INSERT INTO PERSON (person_id, first_name, last_name, address_1,
 address_2, city, state, zip, phone)
 VALUES(110, 'Random', 'Student', '3300 Spring Garden St.',
  'Apt. B1', 'Philadelphia', 'PA', '19104', '(215)555-2547');

INSERT INTO PERSON (person_id, first_name, last_name, middle_name, address_1,
  city, state, zip, phone)
 VALUES (111, 'Larry', 'Palazzolo', 'J.', '202 Cayuga Circle', 
 'New Britan', 'PA', '18901', '(215)555-2251');

INSERT INTO company VALUES (10, 'Baltimore Orioles', 'Baseball Team');
INSERT INTO company VALUES (11, 'The Grateful Dead', 'Jam Band');
INSERT INTO company VALUES (12, 'Bob Dylan', 'Folk Artist');

INSERT INTO contact VALUES (101, 10);
INSERT INTO contact VALUES (102, 10);
INSERT INTO contact VALUES (103, 11);
INSERT INTO contact VALUES (104, 11);
INSERT INTO contact VALUES (105, 12);

INSERT INTO student (person_id, major_1, gpa, date_enrolled)
  VALUES (106, 'CS', 3.72, to_date('September, 9, 2000', 'Month, Dd, YYYY'));

INSERT INTO student (person_id, major_1, minor_1, gpa, date_enrolled) 
 VALUES (107, 'PHYS', 'MECE', 3.02, to_date('August, 15, 1997', 'Month, Dd, YYYY'));

INSERT INTO student (person_id, major_1, major_2, gpa, date_enrolled) 
 VALUES (108, 'BUS', 'FIN', 2.95, to_date('January, 30, 1997', 'Month, Dd, YYYY'));

INSERT INTO student (person_id, date_enrolled) 
 VALUES (109, to_date('May, 22, 2001', 'Month, Dd, YYYY'));

INSERT INTO student (person_id, major_1, minor_1, gpa, date_enrolled) 
 VALUES (110, 'IST', 'CS', 1.33, to_date('May, 17, 1997', 'Month, Dd, YYYY'));

INSERT INTO student (person_id, major_1, minor_1, minor_2, gpa, date_enrolled)
 VALUES (111, 'ENG', 'PHIL', 'LIT', 3.82, to_date('June, 20, 1998', 'Month, Dd, YYYY'));

INSERT INTO list VALUES ('KELLY_97', 'List of all students who lived in kelly hall in 1997.');
INSERT INTO list VALUES ('DEADHEADS', 'List of all who enjoy the music of the Grateful Dead.');

INSERT INTO list_item VALUES (107, 'KELLY_97');
INSERT INTO list_item VALUES (108, 'KELLY_97');
INSERT INTO list_item VALUES (110, 'KELLY_97');
INSERT INTO list_item VALUES (111, 'KELLY_97');
INSERT INTO list_item VALUES (103, 'DEADHEADS');
INSERT INTO list_item VALUES (104, 'DEADHEADS');
INSERT INTO list_item VALUES (105, 'DEADHEADS');
INSERT INTO list_item VALUES (109, 'DEADHEADS');
INSERT INTO list_item VALUES (110, 'DEADHEADS');


a)
SELECT last_name, first_name, middle_name, city, phone FROM person order by
last_name, first_name, middle_name;

b)
SELECT p.last_name, p.first_name, p.middle_name, s.major_1, s.major_2 
 FROM person p, student s, list_item lm 
 WHERE lm.code_name = 'DEADHEADS' 
  and lm.person_id = p.person_id
  and p.person_id = s.person_id 
ORDER BY
 p.last_name, p.first_name, p.middle_name;

commit; /* this allows you to undo the delete */

c)
DELETE person WHERE last_name like 'P%';

rollback /* this puts back the rows*

d)
UPDATE student set major_1 = 'ENGL' WHERE major_1 = 'PHYS';
UPDATE student set major_2 = 'ENGL' WHERE major_2 = 'PHYS';

e)
/* Notice the outer join*/
SELECT p.last_name, co.company_name 
 FROM person p, contact c, company co
 WHERE p.state = 'NY' 
  and p.person_id = c.person_id (+) 
  and c.company_id = co.company_id (+)
  ORDER BY p.last_name;

------------
EXTRA CREDIT
------------

---
 a
---
commit; /* this allows you to undo the delete */

DELETE person
WHERE person_id in 
 (SELECT person_id FROM list_item WHERE code_name = 'KELLY_97');

rollback /* this puts back the rows*/
---
 b
---

SELECT p.last_name, p.first_name, trunc(sysdate - s.date_enrolled) as
days_between
FROM person p, student s
WHERE p.person_id = s.person_id
ORDER BY days_between;

---
 c
---
/* NOTE: assumed NULL value represents undecided major
   also notice that 

SELECT count(person_id)
FROM student s
WHERE s.major_1 is NULL;

---
 d
---

create table meeting(
	meeting_id number(9) NOT NULL,
	name varchar2(40) NOT NULL,
	meeting_date date NOT NULL,
	location varchar2(50) DEFAULT('UNDECIDED') NOT NULL,
	PRIMARY KEY (meeting_id)
);

create table invitation(
	meeting_id number(9) NOT NULL,
	person_id number(9) NOT NULL,
	invitation_date date DEFAULT(sysdate) NOT NULL,
	reply_date date,
      reminded_date date,
	attending char(1) DEFAULT('?') CHECK (attending IN ('Y', 'N', '?')) NOT NULL,
	PRIMARY KEY (meeting_id, person_id),
	FOREIGN KEY (meeting_id) REFERENCES meeting(meeting_id)  ON DELETE CASCADE,
	FOREIGN KEY (person_id) REFERENCES person(person_id)  ON DELETE CASCADE
);

INSERT INTO meeting VALUES (150, 'Band Practice', to_date('June, 20, 2001
16:20', 'Month Dd, YYYY HH24:mi'), 'The Filmore East');
INSERT INTO meeting VALUES (151, 'Group Project Meeting', to_date('May, 30,
2001 19:30', 'Month Dd, YYYY HH24:mi'), 'Tommys House');
INSERT INTO meeting VALUES (152, 'Quizzo', to_date('May, 21, 2001 22:00',
'Month Dd, YYYY HH24:mi'), 'New Deck Tavern');

INSERT INTO invitation (meeting_id, person_id) 
  SELECT 150, Person_ID from List_Item where code_name = 'KELLY_97';
 /* Above will insert invitation records for everyone on KELLY_97 list */
INSERT INTO invitation (meeting_id, person_id) VALUES(151, 106);
INSERT INTO invitation (meeting_id, person_id) VALUES(151, 107);
INSERT INTO invitation (meeting_id, person_id) VALUES(151, 110);
INSERT INTO invitation (meeting_id, person_id) VALUES(152, 110);
INSERT INTO invitation (meeting_id, person_id) VALUES(152, 108);

-------------------------------------
possible UPDATEs to be run upon reply
-------------------------------------

UPDATE invitation set reply_date = sysdate, attending = 'Y' 
 WHERE meeting_id = 151 and person_id = 106;

----------------------------------------
UPDATEs to be run after reminder is sent
----------------------------------------

UPDATE invitation set reminded_date = sysdate 
 WHERE meeting_id = 151 and person_id = 106;