/*
ISYS 210
Assignment 2

Model Answer -- Second Version

This version uses one table to model person and its two subtypes contact and student.
This design is not as strictly normalized, but is easier to use and results in much
simpler queries. 
*/

CREATE TABLE Company(
Company_ID number(7),
Name varchar2(35) NOT NULL,
Industry_Sector varchar2(35),
PRIMARY KEY(Company_ID)
);


CREATE TABLE PERSON (
Person_ID number(7),
Person_Type char CONSTRAINT PER_TYPECHK CHECK (Person_Type IN ('S', 'C')),
First_Name varchar2(20),
Middle_Name varchar2(15),
Last_Name varchar2(20) NOT NULL,
Address_Line_1 varchar(30),
Address_Line_2 varchar(30),
Address_Line_3 varchar(30),
City varchar(30),
State char(2),
Postal_Code varchar(13),
Country varchar(15),
Tel_No varchar(15),
Date_Enrolled date,
GPA number(9,2),
Major_1 varchar2(5),
Major_2 varchar2(5),
Minor_1 varchar2(5),
Minor_2 varchar2(5),
Company_ID number(7),
CONSTRAINT PER_PK PRIMARY KEY(Person_ID),
CONSTRAINT PER_CO_FK FOREIGN KEY(Company_ID) references Company(Company_ID),
CHECK (
	(Person_Type = 'S')
       OR
        (
	 (Major_1 IS NULL) AND (GPA IS NULL) AND (Date_Enrolled IS NULL)
	 AND (Major_2 IS NULL) AND (Minor_1 IS NULL) AND (Minor_2 IS NULL)
        )
      ),
CONSTRAINT PER_NOTSTUDCO CHECK (Person_Type = 'C' OR Company_ID IS NULL),
CONSTRAINT PER_CONTHASCO CHECK (Person_Type <> 'C' OR NOT Company_ID IS NULL)
);

/* Notice that varchar2 types are used for most of the "optional" fields here -- this
saves space when storing empty fields as well as when storing values of variable length*/

/* Notice the use of constraints --
These enforce ALL the restrictions from the subtype/supertype --
including the idea that a relationship with company is compulsory for contact but
forbidden for student.
Notice also that the use of a type code (C for Contact, S for Student) and additional
constraints make it impossible to be a contact and a student at the same time -- enforcing
the "disjoint" part.
Named constraints make it easier to figure out the error messages later on!
*/
         
CREATE TABLE List(
Code_Name varchar2(10),
Description varchar(40),
PRIMARY KEY(Code_Name)
);

CREATE TABLE List_TO_Person(
Person_ID number(7),
List_Code varchar2(10),
PRIMARY KEY(Person_ID, List_Code),
FOREIGN KEY(Person_ID) references Person(Person_ID) ON DELETE CASCADE,
FOREIGN KEY(List_Code) references List(Code_Name)
);

INSERT INTO company values (5698745, 'Big Bend Pipe', 'Plumbing');
INSERT INTO company values (1026547, 'Johnny Seed', 'Plants');

INSERT INTO person 
 (Person_ID, First_Name, Middle_Name, Last_Name, Address_Line_1,
  City, State, Postal_Code, Country, Tel_No,
  Person_Type, Date_Enrolled, GPA, Major_1, Minor_1)
 VALUES (1235846, 'John', 'Q', 'Public', '1836 West Court Lane',
 'West Chester', 'PA', '19349', 'USA', '610-555-3457', 
 'S', to_date('11-Jul-99'), '2.6', 'PHYS', 'BUSN');
/* Use of an explicit listing of fields to be inserted allows us to leave
many of the fields null without having to carefully count off a large number
of nulls in the insert command. This means that inserting data into the single
table is much easier than inserting into three separate tables */

INSERT INTO person
 (Person_ID, First_Name, Middle_Name, Last_Name, Address_Line_1,
  Address_Line_2,  City, State, Postal_Code, Country, Tel_No,
 Person_Type, Company_ID)
 values (0236419, 'Skip', 'E', 'Quick', '14 None Such Street',
 'Upstairs', 'Sommerset', 'NY', '18567', 'USA', '113-555-4598',
 'C', 5698745);

INSERT INTO person
 (Person_ID, First_Name, Middle_Name, Last_Name, Address_Line_1,
  City, State, Postal_Code, Country, Tel_No,
  Person_Type, Company_ID)
 VALUES
 (0231578, 'Kathy', 'B', 'Good', '225 Chestwick Court',
 'Sothowick', 'WI', '56981-1365', 'USA', '657-555-1156',
 'C', 1026547);

INSERT INTO person
 (Person_ID, First_Name, Middle_Name, Last_Name, Address_Line_1,
  City, State, Postal_Code, Country, Tel_No, 
 Person_Type, Company_ID)
 values (9998564, 'Matt', 'L', 'Double', 'PO Box 456',
 'New York', 'NY', '05678', 'USA', '102-555-1578',
 'C', 1026547);


INSERT INTO person 
 (Person_ID, First_Name, Middle_Name, Last_Name, Address_Line_1,
  Address_Line_2, City, State, Postal_Code, Country, Tel_No,
  Person_Type, Date_Enrolled, GPA, Major_1, Minor_2 )
 VALUES (5648981, 'Jim', 'L', 'Person', '1536 East Lincoln Highway',
'Apartment 29', 'Thorndale', 'PA', '19856', 'USA', '610-555-4564',
 'S', to_date('01-Jul-97'), '3.65', 'PHYS','BUSN');


INSERT INTO person
 (Person_ID, First_Name, Middle_Name, Last_Name, Address_Line_1,
  Address_Line_2, Address_Line_3, City, State, Postal_Code, Country, Tel_No,
  Person_Type, Date_Enrolled, GPA, Major_1)
 values (7895164, 'Susan', null, 'Smith', 'Old West Highway',
 'Door # 2','Behind the Water Pipes', 'Old Bend', 'NY', '15664', 'USA','569-555-2367',
 'S', to_date('10-JAN-89'), '1.65','BUSN');


INSERT INTO list VALUES ('X-MAS', 'Christmas card recipient');
INSERT INTO list VALUES ('HONORS', 'Students who are in the Honors program');

INSERT INTO list_to_person VALUES (236419, 'X-MAS');
INSERT INTO list_to_person VALUES (5648981, 'X-MAS');
INSERT INTO list_to_person VALUES (231578, 'X-MAS');
INSERT INTO list_to_person VALUES (5648981, 'HONORS');
INSERT INTO list_to_person VALUES (7895164, 'HONORS');
INSERT INTO list_to_person VALUES (7895164, 'X-MAS');
INSERT INTO list_to_person VALUES (9998564, 'X-MAS');

/* Compare these queries with the multi-table version.
Note that the joins are much simpler here, because there is less need to go across
multiple tables. This may also help performance in some cases. */

a) 
 SELECT last_name, first_name, city, tel_no 
 FROM person
 ORDER BY last_name;

b)
 SELECT last_name, first_name, person.major_1, person.major_2 
 FROM person, list_to_person
 WHERE list_to_person.list_code='X-MAS'
  AND person.person_id=list_to_person.person_id;

c)
 DELETE FROM person WHERE Last_name LIKE'P%';

d)
 UPDATE Person SET Major_1 = 'ENGL' WHERE Major_1='PHYS';
 UPDATE Person SET Major_2 = 'ENGL' WHERE Major_2='PHYS';

e)
 SELECT last_name, company.name FROM person, company
  WHERE person.state='NY'
  AND person.company_id=company.company_ID(+);