drop table membership;
drop table honorsinfo;
drop table clubs;
drop table students;
drop table advisors;
Create table advisors (ID number(4) constraint advisors_ID_PK Primary Key,
	NAME varchar2(15),
	PHONE varchar2(8));
Create table students (ID number(2) constraint students_ID_PK Primary Key, 
	NAME varchar2(10), 
	PHONE varchar2(8), 
	MAJOR varchar2(4), 
        GPA number(3,1));
Create table clubs (NAME varchar2(10) constraint clubs_name_PK Primary Key,
	ADVISORID number(4) constraint clubs_advisorid_FK References advisors(ID),
	YEARFORMED number(4));
Create table membership (STUDENTID number(2) constraint membership_studentid_FK References students(ID),
	CLUBNAME varchar2(10) constraint membership_clubname_FK References clubs(name),
	constraint membership_PK Primary Key(STUDENTID,CLUBNAME));
Create table honorsinfo (STUDENTID number(2) constraint honorsinfo_studentid_FK References students(ID),
	program varchar2(14), 
	honorsgpa number(3,1), 
	mentor varchar2(12),
	constraint honorsinfo_studentid_PK Primary Key (studentid));
Insert into advisors values (1234,'Dr. Jones','111-1111');
Insert into advisors values (1235,'Dr. Smith','444-4444');
Insert into advisors values (1236,'Dr. Davis','777-7777');
Insert into students values (1,'Billy','111-8881','Actg',3.2);
Insert into students values (2,'Betty','111-8882','Actg',3.4);
Insert into students values (3,'Jim','111-8883','Insy',2.8);
Insert into students values (4,'Jane','111-8884','Actg',2.7);
Insert into students values (5,'Fred','111-8885','Insy',3.9);
Insert into students values (6,'Trisha','111-8886','Actg',3.6);
Insert into clubs values ('Chess',1234,1954);
Insert into clubs values ('Book',1235,1933);
Insert into clubs values ('Scuba',1236,1965);
Insert into clubs values ('Acting',1235,1977);
Insert into clubs values ('Photo',1234,1962);
Insert into clubs values ('Ski',1235,1988);
Insert into membership values (1,'Chess');
Insert into membership values (1,'Book');
Insert into membership values (3,'Photo');
Insert into membership values (4,'Chess');
Insert into membership values (4,'Scuba');
Insert into membership values (4,'Photo');
Insert into membership values (5,'Scuba');
Insert into membership values (5,'Photo');
Insert into honorsinfo values (5,'Database',4.0,'Dr. Jones');
Insert into honorsinfo values (6,'Taxation',4.0,'Dr. Saubert');
Commit
/