oracle.sql

# ENGINE=INNODB;

CREATE TABLE Person (
	id NUMBER(10) NOT NULL,
	name VARCHAR(100) NOT NULL,
	gender VARCHAR(1) NOT NULL,
	email varchar(100) NOT NULL,
	major varchar(3),
	address VARCHAR(100) NOT NULL,
	telephone NUMBER(15) NOT NULL,
	views varchar(200),

	PRIMARY KEY(id),

	CONSTRAINT check_gender 
  		CHECK (check_gender IN('M', 'F') )
)

COMMENT ON TABLE Person IS 'Enhanced Person Table';


CREATE TABLE Skills (
	id NUMBER(10) NOT NULL,
	Skills VARCHAR(200) NOT NULL,
	life_skills VARCHAR(200) NOT NULL,
	professional_skills VARCHAR(200) NOT NULL,

	PRIMARY KEY(id),

	FOREIGN KEY (id) 
        REFERENCES Person(id)
        ON DELETE CASCADE
)


CREATE TABLE Schedule (
	id_skills NUMBER(3) NOT NULL,
	availability VARCHAR(50) NOT NULL,
	frequency NUMBER(3) NOT NULL,
	group_session VARCHAR(50) NOT NULL,

	PRIMARY KEY(id_skills),

	CONSTRAINT check_availability
  		CHECK (check_gender IN('Next Semester', 'Immediate') ),

	FOREIGN KEY (id) 
        REFERENCES Person(id)
        ON DELETE CASCADE
)


# potential mentors
# get variable - compare
# args for potential mentors -> close to skill set

CREATE OR REPLACE PROCEDURE get_potential_mentors
AS
BEGIN
	SELECT * FROM Schedule WHERE availability == 'Immediate';
END;


CREATE OR REPLACE PROCEDURE search_person_name (name VARCHAR(100))
AS
name VARCHAR(100);
BEGIN
	SELECT * FROM Schedule WHERE name LIKE %name%;
END;


CREATE OR REPLACE PROCEDURE search_person_id (id NUMBER)
AS
id NUMBER;
BEGIN
	SELECT * FROM Schedule WHERE id == 'id';
END;


CREATE OR REPLACE PROCEDURE search_person_address (address VARCHAR(100))
AS
address VARCHAR(100);
BEGIN
	SELECT * FROM Schedule WHERE address LIKE %address%;
END;