-- CS3540 Test Data Generator

set serveroutput on

DROP TABLE registration;
DROP TABLE student;

CREATE TABLE STUDENT ( StudentID NUMBER(8) NOT NULL,
			 FirstName VARCHAR2(20),
			 MiddleName VARCHAR2(20),
			 LastName VARCHAR2(35),
			 Birthdate DATE,
			 GPA NUMBER(5,3), PRIMARY KEY(StudentID));
			 
CREATE TABLE registration ( RegistrationID NUMBER(6),
		CourseID NUMBER(4),
		RegDate DATE,
		StudentID NUMBER(8),
		PRIMARY KEY(RegistrationID),
CONSTRAINT FK_StudentID FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID));

CREATE OR REPLACE PROCEDURE myDataGen IS
	TYPE myArrayNameType IS TABLE OF VARCHAR2(20) 
		INDEX BY BINARY_INTEGER;
		
	myFirstName myArrayNameType;
	myMiddleName myArrayNameType;
	myLastName myArrayNameType;
	myBirthdate DATE;
	myGPA NUMBER(5,3);
	myStudentID NUMBER(8);
	myRandDay NUMBER;
	mySystemDate DATE;
--	TIME1 VARCHAR2(50);

	i number;
	a number;
	b number;
	c number;
	
	myRegStartDate DATE;
	myRegDate DATE;
	myCourseID myArrayNameType;
	maxstudents NUMBER(8);

BEGIN

	myFirstName(1) := 'Jack';
	myFirstName(2) := 'John';
	myFirstName(3) := 'Hugo';
	myFirstName(4) := 'Kate';
	myFirstName(5) := 'Clair';

	
	myMiddleName(1) := 'Miles';
	myMiddleName(2) := 'Ethan';
	myMiddleName(3) := 'Goodwin';
	myMiddleName(4) := 'Lilly';
	myMiddleName(5) := 'Ana';

	
	myLastName(1) := 'Shephard';
	myLastName(2) := 'Locke';
	myLastName(3) := 'Reyes';
	myLastName(4) := 'Austen';
	myLastName(5) := 'Littleton';

	
	myBirthdate := '01-JAN-89';
	myGPA := '3.5';
	mySystemDate := SYSDATE();
	myRegStartDate := '01-NOV-08';
	
	myCourseID(1) := '1030';
	myCourseID(2) := '2550';
	myCourseID(3) := '2650';
	myCourseID(4) := '3540';
	myCourseID(5) := '4790';

---------------------------------
-- Start time
---------------------------------
	select 'Begin: ' || to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') 
		Into TIME1
		from dual;
	dbms_output.put_line(TIME1);
----------------------------------

	
	i := 1;
	LOOP
		SELECT dbms_random.value(2.0,4.0) into myGPA FROM dual;
		SELECT dbms_random.value(6570,23725) into myRandDay FROM dual;
		myBirthdate := mySystemDate - myRandDay;
		SELECT dbms_random.value(1,5) into a FROM dual;
		SELECT dbms_random.value(1,5) into b FROM dual;
		SELECT dbms_random.value(1,5) into c FROM dual;
		INSERT INTO STUDENT (StudentID, FirstName, MiddleName, LastName, Birthdate, GPA) 
			VALUES (i, myFirstName(a),myMiddleName(b), myLastName(c), myBirthdate, myGPA);
			i := i+1;
		EXIT WHEN i > 50000;
	END LOOP;
	maxstudents := i-1;
	
	i := 1;
	LOOP
		myRegDate := myRegStartDate+dbms_random.value(1,75);
		SELECT dbms_random.value(1,maxstudents) into myStudentID FROM dual;
		SELECT dbms_random.value(1,5) into a FROM dual;
		INSERT INTO REGISTRATION (RegistrationID, CourseID, RegDate, StudentID) 
					 VALUES (i , myCourseID(a), myRegDate, myStudentID);
		i := i+1;
		EXIT WHEN i > 250000;
	END LOOP;
-----------------------------------
-- Display End Time
-----------------------------------
	select 'End: ' || to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') 
		Into TIME1
		from dual;
	dbms_output.put_line(TIME1);
-----------------------------------

END;
/

execute myDataGen;