Hillcrest High SchoolAthletic Department

BMIS 441 – 02 Group #1 Term Project

Hannah HeffnerRonzai Saurombe

Kyle Callow

I. Project Scenario 

Hillcrest High School is a new public school located in Western Washington. Because it is a newly established institution, the athletic department is limited in sports teams at the time. Currently the athletic department has 5 Men and Women’s sports teams: Women's Volleyball, Men's Tennis, Women's and Men's Soccer, and Men’s basketball. The athletic department has been struggling in finding a way to organize and manage their athletes, staff, and stadium locations. We have been in communication with the Athletic Director over the last month. He has asked us to come in, analyze their current system and identify current and future needs, then he is asking us to implement a system that will help him better manage this department. The high school is projected to grow drastically over the next few years and wants to be as prepared as possible. 

II. Objectives Formulate a system that helps the athletic department keep track of

athlete information, team information, budgets, staff salaries, player eligibility status and more.

Provide easy, accessible usage to the Athletic director and Database Administrator

o Allow the Athletic Director and Database Administrator to manage private information regarding salaries of coaches, trainers, and compliance officers

Generate player reports for coaches and other staff that include details about athletes

Create views that enable members of the athletic department to see game-time locations and the max-capacity that each stadium can hold

o Help admissions and the ticket office predict how many tickets they will sell

III. Systems Development Life Cycle (SDLC)

1. Analysis

In order for Hillcrest High School’s Athletic department to best serve the needs of its student athlete and athletic staff, a database should be created to maintain organization for keeping track of budgets, eligibility status, athlete information, staff salaries, and the relationship between teams, coaches and athletes. The database will allow the athletic department to be more efficient at manipulating and inserting information about current and new players together with the staff including coaches, officers and trainers. Our team will be responsible for coming up with the appropriate ERD model for the athletic department database. We will use this ERD model to set up the database and make sure that it is ready for the use of the department. Following the implementation of the database, we are going to come up with some queries and run them in order to test whether the needs and wants of the athletic department are satisfied. An example of a scenario will be checking whether specific players are eligible to compete. This scenario will involve the compliance officers reviewing the players’ paperwork to make

sure they meet all the requirements needed to compete. By doing so, there are making it easier for the coaches and department in general to know whether or not a player or team is eligible to compete. By using this database, we can maintain accurate information about all players, coaches and staff members.

2. Design

Our team will utilize the normalization process to make sure that the database is easy to understand and well organized in order to minimize anomalies within data relationships. Our goal is to increase efficiency when pulling info from each of our 6 tables. This will help the athletic department minimize data redundancy and enforce referential integrity. The process of normalization will help with the manipulation and insertion of data in the database. Our design is based on the flow of information from all athletes and staff members and how it all integrates to useful information for the athletic department. First, we came up with our design with three relational tables and removed all multivalued attributes. Following that process, we identified the partial dependencies present thus putting us in 1st Normal Form. Then, we broke down these 3 tables into 6 smaller, simpler ones. This put us in 3rd Normal Form since there were no transitive dependencies present. These tables will help us better organize and sort data by assuring that no anomalies exist and less data redundancy is likely to occur.

A. ERD

B. Normalization Process Zero Normal Form

First Normal Form

Second/Third Normal Form

C. Order of Entering Data

1.Trainers 2.Coaches 3.Compliance Officers 4.Stadium 5.Athletes 6.Athletic Team

3. Implementation

A. Table Creation and Insertion

DROP TABLE ATHLETIC_STADIUM CASCADE CONSTRAINTS;DROP TABLE TRAINER CASCADE CONSTRAINTS;DROP TABLE COMPLIANCE_OFFICER CASCADE CONSTRAINTS;DROP TABLE COACHES CASCADE CONSTRAINTS;DROP TABLE ATHLETIC_TEAM CASCADE CONSTRAINTS;DROP TABLE ATHLETES CASCADE CONSTRAINTS;

-----------------------------------------------------CREATE TABLE ATHLETIC_STADIUM(Stadium_Code NUMBER(3),Stadium_Name VARCHAR2(20), Stadium_Capacity NUMBER(10), CONSTRAINT athleticstadium_stadiumcode_pk PRIMARY KEY(Stadium_Code)); INSERT INTO ATHLETIC_STADIUMVALUES('110','Tiger Field', '1000');INSERT INTO ATHLETIC_STADIUMVALUES('120','Rudolph Arena', '700');INSERT INTO ATHLETIC_STADIUMVALUES('130','Stevens Center', '300');

-----------------------------------------------------CREATE TABLE TRAINER(Trainer_ID NUMBER(4),Trainer_Name VARCHAR2(20),Salary NUMBER(8,2),Trainer_Phone VARCHAR2(12), CONSTRAINT trainer_trainerid_pk PRIMARY KEY(Trainer_ID));

INSERT INTO TRAINERVALUES('1001', 'Elbert Finland', 40000,'425-932-1281');INSERT INTO TRAINERVALUES('1002', 'Georgie Campbell', 63000,'425-224-4234');INSERT INTO TRAINERVALUES('1003', 'Richard Frost', 56000,'425-333-0972');INSERT INTO TRAINERVALUES('1004', 'Pit Smith', 57000,'425-438-3954');

-----------------------------------------------------CREATE TABLE COMPLIANCE_OFFICER(Officer_ID NUMBER(4), Officer_Name VARCHAR2(20), Salary NUMBER(8,2),Officer_Phone VARCHAR2(12), CONSTRAINT complianceofficer_officerid_pk PRIMARY KEY(Officer_ID));

INSERT INTO COMPLIANCE_OFFICERVALUES ('2001', 'Malcolm Fish', 85000,'425-654-4580');INSERT INTO COMPLIANCE_OFFICERVALUES ('2002', 'Alice Cumberbatch', 79000,'206-238-7643');INSERT INTO COMPLIANCE_OFFICERVALUES ('2003', 'Rick Sanchez', 73000,'425-948-9571');

-----------------------------------------------------CREATE TABLE COACHES(Coach_ID NUMBER(4), Coach_Name VARCHAR2(20), Salary NUMBER(8,2),Coach_Phone VARCHAR2(12),Team_ID NUMBER(2), CONSTRAINT coaches_IDs_pk PRIMARY KEY(Coach_ID));

INSERT INTO COACHESVALUES (5001, 'Michael Jones', 95000,'425-658-5583', 10);INSERT INTO COACHESVALUES (5002, 'Gavin Free', 110000,'206-802-6168', 20);INSERT INTO COACHESVALUES (5003, 'Ryan Haywood', 55000,'425-692-4573', 30);INSERT INTO COACHESVALUES (5004, 'Geoff Ramsay', 54000,'206-208-6983', 40);INSERT INTO COACHESVALUES (5005, 'Trevor Colins', 67000,'425-302-1583', 50);

-------------------------------------------------------------------

CREATE TABLE ATHLETIC_TEAM(Team_ID NUMBER(2),Coach_ID NUMBER(4), Stadium_Code NUMBER(3),Trainer_ID NUMBER(4),Officer_ID NUMBER(4), CONSTRAINT athleticteam_teamid_pk PRIMARY KEY (Team_ID),

CONSTRAINT athleticteam_coachid_fk FOREIGN KEY (Coach_ID) REFERENCES COACHES (Coach_ID), CONSTRAINT athleticteam_trainerid_fk FOREIGN KEY (Trainer_ID) REFERENCES TRAINER (Trainer_ID), CONSTRAINT athleticteam_officerid_fk FOREIGN KEY (Officer_ID) REFERENCES COMPLIANCE_OFFICER (Officer_ID));

INSERT INTO ATHLETIC_TEAM VALUES (10, '5001', '110', '1001', '2001');

INSERT INTO ATHLETIC_TEAM VALUES (20, '5002', '110', '1001', '2001');

INSERT INTO ATHLETIC_TEAM VALUES (30, '5003', '120', '1002', '2002');

INSERT INTO ATHLETIC_TEAM VALUES (40, '5004', '130', '1003', '2002');

INSERT INTO ATHLETIC_TEAM VALUES (50, '5005', '120', '1004', '2003');

-------------------------------------------------------------------

CREATE TABLE ATHLETES(Athlete_ID NUMBER(4),Team_ID NUMBER(2),Team_name VARCHAR2(20),Athlete_Name VARCHAR2(30),Athlete_Year VARCHAR2(11),Athlete_Address VARCHAR2(35),Gender VARCHAR2(6),Height VARCHAR2(6),Weight VARCHAR2(6),Cleared VARCHAR2(3), CONSTRAINT athletes_pk PRIMARY KEY (Athlete_ID), CONSTRAINT athleticteam_teamid_fk FOREIGN KEY (Team_ID) REFERENCES ATHLETIC_TEAM (Team_ID));

---BOYS SOCCER --1INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4000', '10', 'M Soccer', 'Terry Jones', 'Senior', '1420 Mission St', 'Male', '6.1', '201', 'Yes');

--2INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4001', '10', 'M Soccer', 'Jesse Li', 'Senior', '1234 Sinto St.', 'Male', '5.10', '165', 'Yes');

--3INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4002', '10', 'M Soccer', 'Mike Lens', 'Junior', '502 E. Boone Ave.', 'Male', '6.2', '204', 'Yes');

--4INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4003', '10', 'M Soccer', 'Colin Haggerty', 'Senior', '504 E. Boone Ave.', 'Male', '5.8', '183', 'Yes');

--5INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4004', '10', 'M Soccer', 'Kyle Callow', 'Senior', '328 Owl Ridge Ave.', 'Male', '5.9', '130', 'Yes');

--6INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4005', '10', 'M Soccer', 'Aaron Callow', 'Sophomore', '7314 Ridgecrest St.', 'Male', '5.10', '140', 'Yes');

--7INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4006', '10', 'M Soccer', 'Alex Simmons', 'Freshman', '548 Parkway St.', 'Male', '5.9', '156', 'Yes');

--8INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4007', '10', 'M Soccer', 'Drew Frasier', 'Junior', '9635 Addison St.', 'Male', '5.4', '167', 'Yes');

--9INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4008', '10', 'M Soccer', 'Matt Bokan', 'Sophomore', '2022 Augusta St..', 'Male', '6.2', '175', 'Yes');

--10

INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4009', '10', 'M Soccer', 'Tim McNary', 'Senior', '398 Astor St..', 'Male', '6.0', '184', 'Yes');

--11INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4010', '10', 'M Soccer', 'Jack Bones', 'Freshman', '1228 Mission St..', 'Male', '5.2', '145', 'Yes');

--12INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4011', '10', 'M Soccer', 'Pete McDonald', 'Sophomore', '1021 Dakota St.', 'Male', '5.11', '176', 'Yes');

--13INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4012', '10', 'M Soccer', 'Corey Jackson', 'Senior', '3297 Cincinatti St..', 'Male', '5.4', '194', 'Yes');

--14INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4013', '10', 'M Soccer', 'Raphael Nadal', 'Sophomore', '3829 Sinto St.', 'Male', '5.1', '176', 'Yes');

--15INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4014', '10', 'M Soccer', 'Roger Federer', 'Junior', '1317 Lidgerwood St.', 'Male', '5.10', '157', 'Yes');

--16INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4015', '10', 'M Soccer', 'Andrew Luck', 'Junior', '411 Sinto St.', 'Male', '6.4', '192', 'Yes');

--GIRLS SOCCER --1INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)

VALUES ('4016', '20', 'W Soccer', 'Lizzie Thometz', 'Junior', '428 Regis St.', 'Female', '5.4', '166', 'Yes');

--2INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4017', '20', 'W Soccer', 'Shannon McGrath', 'Junior', '861 Flagstaff Ave.', 'Female', '5.5', '169', 'Yes');

--3INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4018', '20', 'W Soccer', 'Ylena Gazdik', 'Senior', '555 Carroll St.', 'Female', '6.3', '235', 'Yes');

--4INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4019', '20', 'W Soccer', 'Kindall Murie', 'Senior', '8521 Humboldt Dr.', 'Female', '5.8', '185', 'Yes');

--5INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4020', '20', 'W Soccer', 'Emma Russell', 'Freshman', '894 Denver St.', 'Female', '5.4', '145', 'Yes');

--5INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4021', '20', 'W Soccer', 'Hannah Montana', 'Senior', '701 Cougar St.', 'Female', '5.6', '147', 'Yes');

--6INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4022', '20', 'W Soccer', 'Raquel Mouw', 'Junior', '6038 Dordt Ave.', 'Female', '5.6', '149', 'Yes');

--7INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4023', '20', 'W Soccer', 'Kori Swanson', 'Junior', '610 Pacific Ave.', 'Female', '6.4', '209', 'Yes');

--8INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)

VALUES ('4024', '20', 'W Soccer', 'Celia Harrington', 'Senior', '719 Moscow St.', 'Female', '5.4', '160', 'Yes');

--9INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4025', '20', 'W Soccer', 'Ali Eisenbeiss', 'Senior', 'Salt Lake St.', 'Female', '5.7', '145', 'Yes');

--10INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4026', '20', 'W Soccer', 'Katie Keller', 'Senior', '903 Vandal St.', 'Female', '5.6', '140', 'Yes');

--11INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4027', '20', 'W Soccer', 'Reilly Coyle', 'Sophomore', '631 Caldwell Blvd.', 'Female', '5.8', '165', 'Yes');

--12INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4028', '20', 'W Soccer', 'Julie Zamzow', 'Senior', '5301 Oregon St.', 'Female', '5.4', '156', 'No');

--13INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4029', '20', 'W Soccer', 'Alyssa Steward', 'Junior', '4207 Rooster St.', 'Female', '5.5', '133', 'Yes');

--15INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4030', '20', 'W Soccer', 'McKenna Dunn', 'Senior', '719 Austin St.', 'Female', '5.6', '177', 'Yes');

--16INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4031', '20', 'W Soccer', 'Monica Harrington', 'Junior', '2023 Cherry Ln.', 'Female', '5.5', '188', 'Yes');

--VOLLEYBALL

--1

INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4032', '30', 'W Volleyball', 'Caitlin Sweeny', 'Senior', '734 Arrow St.', 'Female', '5.7', '199', 'Yes');

--2INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4033', '30', 'W Volleyball', 'Mary Roberts', 'Senior', '6736 Bulldog Ln.', 'Female', '5.4', '166', 'Yes');

--3INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4034', '30', 'W Volleyball', 'Alex Fox', 'Senior', '8901 River St.', 'Female', '5.9', '144', 'No');

--4INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4035', '30', 'W Volleyball', 'Isabella Teviz', 'Senior', '2428 Wooden St.', 'Female', '5.8', '155', 'Yes');

--5INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4036', '30', 'W Volleyball', 'Emma Dawson', 'Junior', '8935 Cow St.', 'Female', '5.6', '165', 'Yes');

--6--INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4037', '30', 'W Volleyball', 'Mia Chen', 'Junior', '6441 Cross St.', 'Female', '5.8', '155', 'Yes');

--7--INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4038', '30', 'W Volleyball', 'Ali Rodriguez', 'Junior', '8900 Goat St.', 'Female', '5.7', '185', 'Yes');

--8INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4039', '30', 'W Volleyball', 'Olivia Williams', 'Sophomore', '456 Lighthouse Ln.', 'Female', '6.1', '241', 'Yes');

--9

INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4040', '30', 'W Volleyball', 'Lily Curtis', 'Sophomore', '2813 Western Ln.', 'Female', '5.5', '161', 'Yes');

--10INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4041', '30', 'W Volleyball', 'Grace Jones', 'Freshman', '5509 Alabama St.', 'Female', '6.3', '231', 'Yes');

--TENNIS--1INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4042', '40', 'M Tennis', 'Kim Jong Il', 'Freshman', '2221 Maple St.', 'Male', '5.7', '181', 'Yes');

--2INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4043', '40', 'M Tennis', 'Kyle Avery', 'Freshman', '2821 Clover St.', 'Male', '5.6', '191', 'Yes');

--3INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4044', '40', 'M Tennis', 'Simon Homodes', 'Sophomore', '6798 Crosby St.', 'Male', '5.10', '166', 'Yes');

--4INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4045', '40', 'M Tennis', 'Emilio Moreno', 'Sophomore', '5051 List St.', 'Male', '5.8', '134', 'Yes');

--5INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4046', '40', 'M Tennis', 'Austin Powers', 'Sophomore', '7023 Jackson Ave.', 'Male', '5.7', '143', 'Yes');

--6INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4047', '40', 'M Tennis', 'Vinny Rekte', 'Freshman', '728 Bay St.', 'Male', '6.3', '227', 'Yes');

--7INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4048', '40', 'M Tennis', 'Peter Pan', 'Senior', '2814 Kemp St.', 'Male', '5.3', '168', 'No');

--8INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4049', '40', 'M Tennis', 'Gary Thompson', 'Senior', '2812 Park St.', 'Male', '5.9', '166', 'Yes');

--9INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4050', '40', 'M Tennis', 'Gary Bell', 'Junior', '2101 Central St.', 'Male', '6.0', '175', 'No');

--10INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4051', '40', 'M Tennis', 'Will Smith', 'Junior', '3423 Hitch St.', 'Male', '5.6', '190', 'Yes');

--11INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4052', '40', 'M Tennis', 'Dwayne The Rock Johnson', 'Senior', '2122 Arthur St.', 'Male', '5.9', '190', 'Yes');

--BOYS BASKETBALL --1INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4053', '50', 'M Basketball', 'Travis Scott', 'Senior', '2121 Auburn St.', 'Male', '5.9', '170', 'Yes');

--2INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4054', '50', 'M Basketball', 'Jeremy Jones', 'Senior', '2101 Communal St.', 'Male', '5.11', '157', 'Yes');

--3INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)

VALUES ('4055', '50', 'M Basketball', 'Michael Jordan', 'Junior', '1221 Jordan St.', 'Male', '6.4', '167', 'No');

--4INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4056', '50', 'M Basketball', 'Dennis Ferris', 'Freshman', '1918 Corn St.', 'Male', '5.8', '165', 'Yes');

--5INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4057', '50', 'M Basketball', 'Clay Thompson', 'Junior', '102 Dock St.', 'Male', '6.1', '145', 'Yes');

--6INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4058', '50', 'M Basketball', 'Cooper Anderson', 'Freshman', '2189 Delta St.', 'Male', '5.10', '156', 'Yes');

--7INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4059', '50', 'M Basketball', 'Nate Long', 'Senior', '2121 Dean St.', 'Male', '5.9', '198', 'Yes');

--8INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4060', '50', 'M Basketball', 'Cilus Melson', 'Junior', '1010 Crescent St.', 'Male', '5.11', '177', 'No');

--9INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4061', '50', 'M Basketball', 'Wayne Winslow', 'Senior', '2208 Danish St.', 'Male', '5.6', '109', 'Yes');

--10INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)VALUES ('4062', '50', 'M Basketball', 'Dmitri Stock', 'Freshman', '6509 Dublin St.', 'Male', '5.8', '199', 'Yes');

4. Queries

SQL> SQL> --Create a view that lists all of the staff members.

SQL> CREATE OR REPLACE VIEW list_of_staff_view (Staff_ID, Staff_Name, Staff_Phone, Salary) AS 2 SELECT Coach_ID Staff_ID, Coach_Name Staff_Name, Coach_Phone, Salary 3 FROM COACHES 4 UNION 5 SELECT Officer_ID, Officer_Name, Officer_Phone, Salary 6 FROM COMPLIANCE_OFFICER 7 UNION 8 SELECT Trainer_ID, Trainer_Name, Trainer_Phone, Salary 9 FROM TRAINER 10 ORDER BY Salary DESC; View created. SQL> SQL> --Create a view that lists all of the male athletes.

SQL> CREATE OR REPLACE VIEW male_athlete_view (Team_Name, Team_ID, Coach_Name, Athlete_ID, Athlete_Name, Gender, Height, Weight) AS 2 SELECT DISTINCT A.Team_Name, A.Team_ID, C.Coach_NAme, A.Athlete_ID, A.Athlete_Name, A.Gender, A.Height, A.Weight 3 FROM ATHLETES A, ATHLETIC_TEAM AT, COACHES C 4 WHERE A.Gender = 'Male' 5 AND A.Team_ID = AT.Team_ID 6 AND AT.Coach_ID = C.Coach_ID 7 ORDER BY A.Athlete_ID; View created. SQL> --Create a view that lists all of the female athletes. SQL> SQL> CREATE OR REPLACE VIEW female_athlete_view (Team_Name, Team_ID, Coach_Name, Athlete_ID, Athlete_Name, Gender, Height, Weight) AS 2 SELECT DISTINCT A.Team_Name, A.Team_ID, C.Coach_NAme, A.Athlete_ID, A.Athlete_Name, A.Gender, A.Height, A.Weight 3 FROM ATHLETES A, ATHLETIC_TEAM AT, COACHES C 4 WHERE A.Gender = 'Female' 5 AND A.Team_ID = AT.Team_ID 6 AND AT.Coach_ID = C.Coach_ID 7 ORDER BY A.Athlete_ID; View created.

SQL> --Create a view that lists all of the athletes. SQL> SQL> CREATE OR REPLACE VIEW all_athlete_view (Team_Name, Team_ID, Coach_Name, Athlete_ID, Athlete_Name, Gender, Height, Weight) AS 2 SELECT DISTINCT A.Team_Name, A.Team_ID, C.Coach_NAme, A.Athlete_ID, A.Athlete_Name, A.Gender, A.Height, A.Weight 3 FROM ATHLETES A, ATHLETIC_TEAM AT, COACHES C 4 WHERE A.Team_ID = AT.Team_ID 5 AND AT.Coach_ID = C.Coach_ID 6 ORDER BY A.Athlete_ID; View created. SQL> SQL> ------------------------------------------------------- SQL> --1. Display the salary of the Women's soccer coach.

SQL> SELECT Coach_ID, Coach_Name, TO_CHAR (Salary, '$999,999.99') "Yearly Salary" 2 FROM COACHES 3 WHERE Team_ID = '20'; COACH_ID COACH_NAME Yearly Salary ---------- -------------------- ------------ 5002 Gavin Free $110,000.00 SQL> SQL> ------------------------------------------------------- SQL> --2a. List all of the sports teams and number of athletes on each team.

SQL> SELECT DISTINCT Team_ID, Team_Name, COUNT (Athlete_ID) "Number of Athletes" 2 FROM all_athlete_view 3 WHERE Team_ID = '10' 4 OR Team_ID = '20' 5 OR Team_ID = '30' 6 OR Team_ID = '40' 7 OR Team_ID = '50' 8 GROUP BY Team_Name, Team_ID 9 ORDER BY Team_ID; TEAM_ID TEAM_NAME Number of Athletes ---------- -------------------- ------------------ 10 M Soccer 16 20 W Soccer 16 30 W Volleyball 10 40 M Tennis 11

50 M Basketball 10 SQL> SQL> --2b. List all of the male sports teams and number of athletes on each team.

SQL> SELECT DISTINCT Team_ID, Team_Name, COUNT (Athlete_ID) "Number of Athletes" 2 FROM male_athlete_view 3 WHERE Team_ID = '10' 4 OR Team_ID = '20' 5 OR Team_ID = '30' 6 OR Team_ID = '40' 7 OR Team_ID = '50' 8 GROUP BY Team_Name, Team_ID 9 ORDER BY Team_ID; TEAM_ID TEAM_NAME Number of Athletes ---------- -------------------- ------------------ 10 M Soccer 16 40 M Tennis 11 50 M Basketball 10 SQL> SQL> --2c. List all of the female sports teams and number of athletes on each team.

SQL> SELECT DISTINCT Team_ID, Team_Name, COUNT (Athlete_ID) "Number of Athletes" 2 FROM female_athlete_view 3 WHERE Team_ID = '10' 4 OR Team_ID = '20' 5 OR Team_ID = '30' 6 OR Team_ID = '40' 7 OR Team_ID = '50' 8 GROUP BY Team_Name, Team_ID 9 ORDER BY Team_ID; TEAM_ID TEAM_NAME Number of Athletes ---------- -------------------- ------------------ 20 W Soccer 16 30 W Volleyball 10 SQL> SQL> ------------------------------------------------------- SQL> --3. List all of the sophomore athletes.

SQL> SELECT DISTINCT Athlete_Name, Team_ID, Athlete_ID, Team_Name 2 FROM ATHLETES

3 WHERE athlete_year = 'Sophomore' 4 GROUP BY Team_Name, Team_ID, Athlete_ID,Athlete_Name 5 ORDER BY Athlete_ID; ATHLETE_NAME TEAM_ID ATHLETE_ID TEAM_NAME ------------------------------ ---------- ---------- -------------------- Aaron Callow 10 4005 M Soccer Matt Bokan 10 4008 M Soccer Pete McDonald 10 4011 M Soccer Raphael Nadal 10 4013 M Soccer Reilly Coyle 20 4027 W Soccer Olivia Williams 30 4039 W Volleyball Lily Curtis 30 4040 W Volleyball Simon Homodes 40 4044 M Tennis Emilio Moreno 40 4045 M Tennis Austin Powers 40 4046 M Tennis 10 rows selected. SQL> SQL> SQL> ------------------------------------------------------- SQL> --4. List the name of the athletes that are ineligible/not-cleared to play.

SQL> SELECT DISTINCT Athlete_Name, Cleared 2 FROM ATHLETES 3 WHERE Cleared = 'No'; ATHLETE_NAME CLE ------------------------------ --- Michael Jordan No Cilus Melson No Peter Pan No Julie Zamzow No Gary Bell No Alex Fox No

6 rows selected. SQL> SQL> SQL> ------------------------------------------------------- SQL> --5a. List the staff members by largest to smallest salary.

SQL> SELECT Staff_ID, Staff_Name, Staff_Phone, TO_CHAR(Salary, '$999,999.99') "Salary" 2 FROM list_of_staff_view;

STAFF_ID STAFF_NAME STAFF_PHONE Salary ---------- -------------------- ------------ ------------ 5002 Gavin Free 206-802-6168 $110,000.00 5001 Michael Jones 425-658-5583 $95,000.00 2001 Malcolm Fish 425-654-4580 $85,000.00 2002 Alice Cumberbatch 206-238-7643 $79,000.00 2003 Rick Sanchez 425-948-9571 $73,000.00 5005 Trevor Colins 425-302-1583 $67,000.00 1002 Georgie Campbell 425-224-4234 $63,000.00 1004 Pit Smith 425-438-3954 $57,000.00 1003 Richard Frost 425-333-0972 $56,000.00 5003 Ryan Haywood 425-692-4573 $55,000.00 5004 Geoff Ramsay 206-208-6983 $54,000.00 STAFF_ID STAFF_NAME STAFF_PHONE Salary ---------- -------------------- ------------ ------------ 1001 Elbert Finland 425-932-1281 $40,000.00 12 rows selected. SQL> SQL> --5b. List the names of staff members in order of largest to smallest salary under $60,000.

SQL> SELECT Staff_ID, Staff_Name, Staff_Phone, TO_CHAR(Salary, '$999,999.99') "Salary" 2 FROM list_of_staff_view LOSV 3 WHERE LOSV.Salary < '60000'; STAFF_ID STAFF_NAME STAFF_PHONE Salary ---------- -------------------- ------------ ------------ 1004 Pit Smith 425-438-3954 $57,000.00 1003 Richard Frost 425-333-0972 $56,000.00 5003 Ryan Haywood 425-692-4573 $55,000.00 5004 Geoff Ramsay 206-208-6983 $54,000.00 1001 Elbert Finland 425-932-1281 $40,000.00

SQL> SQL> SQL> ------------------------------------------------------- SQL> --6. Which of the male athletes are over 6 ft. tall?

SQL> --List name, Athlete_ID, and height. SQL> SELECT Athlete_ID, Athlete_Name, Height 2 FROM ATHLETES 3 WHERE Gender = 'Male' 4 AND Height > 6.0;

ATHLETE_ID ATHLETE_NAME HEIGHT ---------- ------------------------------ ------ 4000 Terry Jones 6.1 4002 Mike Lens 6.2 4008 Matt Bokan 6.2 4015 Andrew Luck 6.4 4047 Vinny Rekte 6.3 4055 Michael Jordan 6.4 4057 Clay Thompson 6.1 7 rows selected. SQL> SQL> SQL> ------------------------------------------------------- SQL> --7. Display all of the information of both the Men’s tennis and Men’s basketball coaches.

SQL> SELECT Staff_Name, Staff_ID, Staff_Phone, TO_CHAR(Salary, '$999,999.99') "Salary" 2 FROM list_of_staff_view LOSV 3 WHERE EXISTS (SELECT DISTINCT Team_ID 4 FROM ATHLETIC_TEAM AT 5 WHERE AT.Coach_ID = LOSV.Staff_ID 6 AND AT.Team_ID > '30'); STAFF_NAME STAFF_ID STAFF_PHONE Salary -------------------- ---------- ------------ ------------ Geoff Ramsay 5004 206-208-6983 $54,000.00 Trevor Colins 5005 425-302-1583 $67,000.00 SQL> SQL> SQL> ------------------------------------------------------- SQL> --8a. List the male and female athletes that are over 200 lbs. and over 6 ft. tall.

SQL> SELECT Athlete_ID, Athlete_Name, Gender, Height, Weight 2 FROM all_athlete_view AAV 3 WHERE EXISTS (SELECT Height, Weight 4 FROM ATHLETES A 5 WHERE AAV.Athlete_ID = A.Athlete_ID 6 AND A.Weight >= '200' 7 AND A.Height >= '6.0') 8 ORDER BY AAv.Athlete_ID;

ATHLETE_ID ATHLETE_NAME GENDER HEIGHT WEIGHT ---------- ------------------------------ ------ ------ ---- 4000 Terry Jones Male 6.1 201 4002 Mike Lens Male 6.2 204 4018 Ylena Gazdik Female 6.3 235 4023 Kori Swanson Female 6.4 209 4039 Olivia Williams Female 6.1 241 4041 Grace Jones Female 6.3 231 4047 Vinny Rekte Male 6.3 227 7 rows selected. SQL> SQL> --8b. List all of the male athletes that are over 200 lbs. and 6 ft. tall.

SQL> SELECT Athlete_ID, Athlete_Name, Gender, Height, Weight 2 FROM male_athlete_view MAV 3 WHERE EXISTS (SELECT Height, Weight 4 FROM ATHLETES A 5 WHERE MAV.Athlete_ID = A.Athlete_ID 6 AND A.Weight >= '200' 7 AND A.Height >= '6.0') 8 ORDER BY MAv.Athlete_ID; ATHLETE_ID ATHLETE_NAME GENDER HEIGHT WEIGHT ---------- ------------------------------ ------ ------ ---- 4000 Terry Jones Male 6.1 201 4002 Mike Lens Male 6.2 204 4047 Vinny Rekte Male 6.3 227 SQL> SQL> --8c. List all of the female athletes that are over 200 lbs. and 6 ft. tall.

SQL> SELECT Athlete_ID, Athlete_Name, Gender, Height, Weight 2 FROM female_athlete_view FAV 3 WHERE EXISTS (SELECT Height, Weight

4 FROM ATHLETES A 5 WHERE FAV.Athlete_ID = A.Athlete_ID 6 AND A.Weight >= '200' 7 AND A.Height >= '6.0') 8 ORDER BY FAv.Athlete_ID;

ATHLETE_ID ATHLETE_NAME GENDER HEIGHT WEIGHT ---------- ------------------------------ ------ ------ ---- 4018 Ylena Gazdik Female 6.3 235 4023 Kori Swanson Female 6.4 209 4039 Olivia Williams Female 6.1 241 4041 Grace Jones Female 6.3 231

4. Maintenance

In order to ensure that the system stays up to date and current, we will recommend that the Athletic Department hire an IT Administrator. We will brief the IT Administrator on the current logical and design structure of the database. We will also inform he/she on how to operate and maintain the database. In case of emergencies or any other questions, we will give the administrator our contact information. We will have monthly check-ins where we will meet to discuss on any improvements or updates that might be needed. We will be consistently working on improving the structure and usability of the database over time because of the continuous change in the information systems field.

5.Growth and Change

Like we mentioned earlier, the size of the school is projected to increase over the coming years. The athletic department is expected to grow at the same rate as the school, in turn, resulting in more athletes, teams, and staff members. In order to adapt to these future changes we will need to improve on the structure, database size, complexity, and overall scope.

6. Managerial and Organizational Impacts

The easy accessibility and manipulation of the database will benefit the athletic department in a number of ways. We have designed queries that will aid the Athletic Director in finding which athletes are cleared or not. For example, another query will help locate where certain teams play their games. Overall, the database will help increase efficiency throughout the department. The newly implemented database will greatly benefit the Athletic Director and help him better manage his staff, athletes, and facilities as the department continues to increase.

7. Conclusion

Throughout the process of creating and implementing this database, we encountered numerous obstacles we were forced to overcome. This project required us to put our

selves in the shoes of the Hillcrest High School Athletic Department in order to understand their needs for this system. Using the skillset that we acquired throughout the semester, we were able to follow the steps of the Systems Development Life Cycle to create a fully functional database. We started with our ERD, which gave us the foundation for our database. Then we moved on to the normalization process in order to further break down our tables and reduce data redundancy. We learned that these two steps are crucial in the database implementation process. This project required a lot of critical thinking and analytical skills. As problems arose, we were able to brainstorm ideas on how to approach the situation at hand. This project offered each of us a valuable experience that could be applicable to future careers in the MIS field.

Spooled Script File:

SQL> CREATE TABLE ATHLETIC_STADIUM 2 (Stadium_Code NUMBER(3), 3 Stadium_Name VARCHAR2(20), 4 Stadium_Capacity NUMBER(10), 5 CONSTRAINT athleticstadium_stadiumcode_pk PRIMARY KEY(Stadium_Code)); Table created. SQL> SQL> INSERT INTO ATHLETIC_STADIUM 2 VALUES('110','Tiger Field', '1000'); 1 row created. SQL> INSERT INTO ATHLETIC_STADIUM 2 VALUES('120','Rudolph Arena', '700'); 1 row created. SQL> INSERT INTO ATHLETIC_STADIUM 2 VALUES('130','Stevens Center', '300'); 1 row created. SQL> SQL> SQL> ----------------------------------------------------- SQL> CREATE TABLE TRAINER 2 (Trainer_ID NUMBER(4), 3 Trainer_Name VARCHAR2(20), 4 Salary NUMBER(8,2), 5 Trainer_Phone VARCHAR2(12), 6 CONSTRAINT trainer_trainerid_pk PRIMARY KEY(Trainer_ID)); Table created. SQL>

SQL> INSERT INTO TRAINER 2 VALUES('1001', 'Elbert Finland', 40000,'425-932-1281'); 1 row created. SQL> INSERT INTO TRAINER 2 VALUES('1002', 'Georgie Campbell', 63000,'425-224-4234'); 1 row created. SQL> INSERT INTO TRAINER 2 VALUES('1003', 'Richard Frost', 56000,'425-333-0972'); 1 row created. SQL> INSERT INTO TRAINER 2 VALUES('1004', 'Pit Smith', 57000,'425-438-3954'); 1 row created. SQL> SQL> SQL> SQL> ----------------------------------------------------- SQL> CREATE TABLE COMPLIANCE_OFFICER 2 (Officer_ID NUMBER(4), 3 Officer_Name VARCHAR2(20), 4 Salary NUMBER(8,2), 5 Officer_Phone VARCHAR2(12), 6 CONSTRAINT complianceofficer_officerid_pk PRIMARY KEY(Officer_ID)); Table created. SQL> SQL> INSERT INTO COMPLIANCE_OFFICER 2 VALUES ('2001', 'Malcolm Fish', 85000,'425-654-4580'); 1 row created. SQL> INSERT INTO COMPLIANCE_OFFICER 2 VALUES ('2002', 'Alice Cumberbatch', 79000,'206-238-7643'); 1 row created. SQL> INSERT INTO COMPLIANCE_OFFICER 2 VALUES ('2003', 'Rick Sanchez', 73000,'425-948-9571'); 1 row created. SQL> SQL> SQL> SQL> ----------------------------------------------------- SQL> CREATE TABLE COACHES 2 (Coach_ID NUMBER(4), 3 Coach_Name VARCHAR2(20), 4 Salary NUMBER(8,2), 5 Coach_Phone VARCHAR2(12), 6 Team_ID NUMBER(2),

7 CONSTRAINT coaches_IDs_pk PRIMARY KEY(Coach_ID)); Table created. SQL> SQL> INSERT INTO COACHES 2 VALUES (5001, 'Michael Jones', 95000,'425-658-5583', 10); 1 row created. SQL> INSERT INTO COACHES 2 VALUES (5002, 'Gavin Free', 110000,'206-802-6168', 20); 1 row created. SQL> INSERT INTO COACHES 2 VALUES (5003, 'Ryan Haywood', 55000,'425-692-4573', 30); 1 row created. SQL> INSERT INTO COACHES 2 VALUES (5004, 'Geoff Ramsay', 54000,'206-208-6983', 40); 1 row created. SQL> INSERT INTO COACHES 2 VALUES (5005, 'Trevor Colins', 67000,'425-302-1583', 50); 1 row created. SQL> SQL> SQL> ------------------------------------------------------------------- SQL> SQL> CREATE TABLE ATHLETIC_TEAM 2 (Team_ID NUMBER(2), 3 Coach_ID NUMBER(4), 4 Stadium_Code NUMBER(3), 5 Trainer_ID NUMBER(4), 6 Officer_ID NUMBER(4), 7 CONSTRAINT athleticteam_teamid_pk PRIMARY KEY (Team_ID), 8 CONSTRAINT athleticteam_coachid_fk FOREIGN KEY (Coach_ID) 9 REFERENCES COACHES (Coach_ID), 10 CONSTRAINT athleticteam_trainerid_fk FOREIGN KEY (Trainer_ID) 11 REFERENCES TRAINER (Trainer_ID), 12 CONSTRAINT athleticteam_officerid_fk FOREIGN KEY (Officer_ID) 13 REFERENCES COMPLIANCE_OFFICER (Officer_ID)); Table created. SQL> SQL> SQL> INSERT INTO ATHLETIC_TEAM 2 VALUES (10, '5001', '110', '1001', '2001'); 1 row created. SQL> SQL> INSERT INTO ATHLETIC_TEAM 2 VALUES (20, '5002', '110', '1001', '2001');

1 row created. SQL> SQL> INSERT INTO ATHLETIC_TEAM 2 VALUES (30, '5003', '120', '1002', '2002'); 1 row created. SQL> SQL> INSERT INTO ATHLETIC_TEAM 2 VALUES (40, '5004', '130', '1003', '2002'); 1 row created. SQL> SQL> INSERT INTO ATHLETIC_TEAM 2 VALUES (50, '5005', '120', '1004', '2003'); 1 row created. SQL> SQL> SQL> ------------------------------------------------------------------- SQL> SQL> CREATE TABLE ATHLETES 2 (Athlete_ID NUMBER(4), 3 Team_ID NUMBER(2), 4 Team_name VARCHAR2(20), 5 Athlete_Name VARCHAR2(30), 6 Athlete_Year VARCHAR2(11), 7 Athlete_Address VARCHAR2(35), 8 Gender VARCHAR2(6), 9 Height VARCHAR2(6), 10 Weight VARCHAR2(6), 11 Cleared VARCHAR2(3), 12 CONSTRAINT athletes_pk PRIMARY KEY (Athlete_ID), 13 CONSTRAINT athleticteam_teamid_fk FOREIGN KEY (Team_ID) 14 REFERENCES ATHLETIC_TEAM (Team_ID)); Table created. SQL> SQL> SQL> ---BOYS SOCCER SQL> --1 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4000', '10', 'M Soccer', 'Terry Jones', 'Senior', '1420 Mission St', 'Male', '6.1', '201', 'Yes'); 1 row created. SQL> SQL> --2 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4001', '10', 'M Soccer', 'Jesse Li', 'Senior', '1234 Sinto St.', 'Male', '5.10', '165', 'Yes');

1 row created. SQL> SQL> --3 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4002', '10', 'M Soccer', 'Mike Lens', 'Junior', '502 E. Boone Ave.', 'Male', '6.2', '204', 'Yes'); 1 row created. SQL>

SQL> --4 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4003', '10', 'M Soccer', 'Colin Haggerty', 'Senior', '504 E. Boone Ave.', 'Male', '5.8', '183', 'Yes'); 1 row created. SQL> SQL> --5 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4004', '10', 'M Soccer', 'Kyle Callow', 'Senior', '328 Owl Ridge Ave.', 'Male', '5.9', '130', 'Yes'); 1 row created. SQL> SQL> --6 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4005', '10', 'M Soccer', 'Aaron Callow', 'Sophomore', '7314 Ridgecrest St.', 'Male', '5.10', '140', 'Yes'); 1 row created. SQL> SQL> --7 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4006', '10', 'M Soccer', 'Alex Simmons', 'Freshman', '548 Parkway St.', 'Male', '5.9', '156', 'Yes'); 1 row created. SQL> SQL> --8 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4007', '10', 'M Soccer', 'Drew Frasier', 'Junior', '9635 Addison St.', 'Male', '5.4', '167', 'Yes'); 1 row created. SQL> SQL> --9

SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4008', '10', 'M Soccer', 'Matt Bokan', 'Sophomore', '2022 Augusta St..', 'Male', '6.2', '175', 'Yes'); 1 row created. SQL> SQL> --10 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4009', '10', 'M Soccer', 'Tim McNary', 'Senior', '398 Astor St..', 'Male', '6.0', '184', 'Yes'); 1 row created. SQL> SQL> --11 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4010', '10', 'M Soccer', 'Jack Bones', 'Freshman', '1228 Mission St..', 'Male', '5.2', '145', 'Yes'); 1 row created. SQL> SQL> --12 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4011', '10', 'M Soccer', 'Pete McDonald', 'Sophomore', '1021 Dakota St.', 'Male', '5.11', '176', 'Yes'); 1 row created. SQL> SQL> --13 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4012', '10', 'M Soccer', 'Corey Jackson', 'Senior', '3297 Cincinatti St..', 'Male', '5.4', '194', 'Yes'); 1 row created. SQL> SQL> --14 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4013', '10', 'M Soccer', 'Raphael Nadal', 'Sophomore', '3829 Sinto St.', 'Male', '5.1', '176', 'Yes'); 1 row created. SQL> SQL> --15 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4014', '10', 'M Soccer', 'Roger Federer', 'Junior', '1317 Lidgerwood St.', 'Male', '5.10', '157', 'Yes'); 1 row created.

SQL> SQL> --16 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4015', '10', 'M Soccer', 'Andrew Luck', 'Junior', '411 Sinto St.', 'Male', '6.4', '192', 'Yes'); 1 row created. SQL> SQL> SQL> SQL> --GIRLS SOCCER SQL> --1 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4016', '20', 'W Soccer', 'Lizzie Thometz', 'Junior', '428 Regis St.', 'Female', '5.4', '166', 'Yes'); 1 row created. SQL> SQL> --2 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4017', '20', 'W Soccer', 'Shannon McGrath', 'Junior', '861 Flagstaff Ave.', 'Female', '5.5', '169', 'Yes'); 1 row created. SQL> SQL> --3 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4018', '20', 'W Soccer', 'Ylena Gazdik', 'Senior', '555 Carroll St.', 'Female', '6.3', '235', 'Yes'); 1 row created. SQL> SQL> --4 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4019', '20', 'W Soccer', 'Kindall Murie', 'Senior', '8521 Humboldt Dr.', 'Female', '5.8', '185', 'Yes'); 1 row created. SQL> SQL> --5 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4020', '20', 'W Soccer', 'Emma Russell', 'Freshman', '894 Denver St.', 'Female', '5.4', '145', 'Yes'); 1 row created. SQL> SQL> --5

SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4021', '20', 'W Soccer', 'Hannah Montana', 'Senior', '701 Cougar St.', 'Female', '5.6', '147', 'Yes'); 1 row created. SQL> SQL> --6 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4022', '20', 'W Soccer', 'Raquel Mouw', 'Junior', '6038 Dordt Ave.', 'Female', '5.6', '149', 'Yes'); 1 row created. SQL> SQL> --7 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4023', '20', 'W Soccer', 'Kori Swanson', 'Junior', '610 Pacific Ave.', 'Female', '6.4', '209', 'Yes'); 1 row created. SQL> SQL> --8 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4024', '20', 'W Soccer', 'Celia Harrington', 'Senior', '719 Moscow St.', 'Female', '5.4', '160', 'Yes'); 1 row created. SQL> SQL> --9 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4025', '20', 'W Soccer', 'Ali Eisenbeiss', 'Senior', 'Salt Lake St.', 'Female', '5.7', '145', 'Yes'); 1 row created. SQL> SQL> --10 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4026', '20', 'W Soccer', 'Katie Keller', 'Senior', '903 Vandal St.', 'Female', '5.6', '140', 'Yes'); 1 row created. SQL> SQL> --11 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4027', '20', 'W Soccer', 'Reilly Coyle', 'Sophomore', '631 Caldwell Blvd.', 'Female', '5.8', '165', 'Yes'); 1 row created.

SQL> SQL> --12 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4028', '20', 'W Soccer', 'Julie Zamzow', 'Senior', '5301 Oregon St.', 'Female', '5.4', '156', 'No'); 1 row created. SQL> SQL> --13 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4029', '20', 'W Soccer', 'Alyssa Steward', 'Junior', '4207 Rooster St.', 'Female', '5.5', '133', 'Yes'); 1 row created. SQL>

SQL> --15 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4030', '20', 'W Soccer', 'McKenna Dunn', 'Senior', '719 Austin St.', 'Female', '5.6', '177', 'Yes'); 1 row created. SQL> SQL> --16 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4031', '20', 'W Soccer', 'Monica Harrington', 'Junior', '2023 Cherry Ln.', 'Female', '5.5', '188', 'Yes'); 1 row created. SQL> SQL> SQL> --VOLLEYBALL SQL> SQL> --1 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4032', '30', 'W Volleyball', 'Caitlin Sweeny', 'Senior', '734 Arrow St.', 'Female', '5.7', '199', 'Yes'); 1 row created. SQL> SQL> --2 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4033', '30', 'W Volleyball', 'Mary Roberts', 'Senior', '6736 Bulldog Ln.', 'Female', '5.4', '166', 'Yes'); 1 row created.

SQL> SQL> --3 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4034', '30', 'W Volleyball', 'Alex Fox', 'Senior', '8901 River St.', 'Female', '5.9', '144', 'No'); 1 row created. SQL> SQL> --4 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4035', '30', 'W Volleyball', 'Isabella Teviz', 'Senior', '2428 Wooden St.', 'Female', '5.8', '155', 'Yes'); 1 row created. SQL> SQL> --5 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4036', '30', 'W Volleyball', 'Emma Dawson', 'Junior', '8935 Cow St.', 'Female', '5.6', '165', 'Yes'); 1 row created. SQL> SQL> --6-- SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4037', '30', 'W Volleyball', 'Mia Chen', 'Junior', '6441 Cross St.', 'Female', '5.8', '155', 'Yes'); 1 row created. SQL> SQL> --7-- SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4038', '30', 'W Volleyball', 'Ali Rodriguez', 'Junior', '8900 Goat St.', 'Female', '5.7', '185', 'Yes'); 1 row created. SQL> SQL> --8 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4039', '30', 'W Volleyball', 'Olivia Williams', 'Sophomore', '456 Lighthouse Ln.', 'Female', '6.1', '241', 'Yes'); 1 row created. SQL> SQL> --9 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4040', '30', 'W Volleyball', 'Lily Curtis', 'Sophomore', '2813 Western Ln.', 'Female', '5.5', '161', 'Yes');

1 row created. SQL> SQL> --10 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4041', '30', 'W Volleyball', 'Grace Jones', 'Freshman', '5509 Alabama St.', 'Female', '6.3', '231', 'Yes'); 1 row created. SQL> SQL> SQL> --TENNIS SQL> --1 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4042', '40', 'M Tennis', 'Kim Jong Il', 'Freshman', '2221 Maple St.', 'Male', '5.7', '181', 'Yes'); 1 row created. SQL> SQL> --2 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4043', '40', 'M Tennis', 'Kyle Avery', 'Freshman', '2821 Clover St.', 'Male', '5.6', '191', 'Yes'); 1 row created. SQL> SQL> --3 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4044', '40', 'M Tennis', 'Simon Homodes', 'Sophomore', '6798 Crosby St.', 'Male', '5.10', '166', 'Yes'); 1 row created. SQL> SQL> --4 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4045', '40', 'M Tennis', 'Emilio Moreno', 'Sophomore', '5051 List St.', 'Male', '5.8', '134', 'Yes'); 1 row created. SQL> SQL> --5 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4046', '40', 'M Tennis', 'Austin Powers', 'Sophomore', '7023 Jackson Ave.', 'Male', '5.7', '143', 'Yes'); 1 row created. SQL>

SQL> --6 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4047', '40', 'M Tennis', 'Vinny Rekte', 'Freshman', '728 Bay St.', 'Male', '6.3', '227', 'Yes'); 1 row created. SQL> SQL> --7 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4048', '40', 'M Tennis', 'Peter Pan', 'Senior', '2814 Kemp St.', 'Male', '5.3', '168', 'No'); 1 row created. SQL> SQL> --8 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4049', '40', 'M Tennis', 'Gary Thompson', 'Senior', '2812 Park St.', 'Male', '5.9', '166', 'Yes'); 1 row created. SQL> SQL> SQL> --9 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4050', '40', 'M Tennis', 'Gary Bell', 'Junior', '2101 Central St.', 'Male', '6.0', '175', 'No'); 1 row created. SQL> SQL> --10 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4051', '40', 'M Tennis', 'Will Smith', 'Junior', '3423 Hitch St.', 'Male', '5.6', '190', 'Yes'); 1 row created. SQL> SQL> --11 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4052', '40', 'M Tennis', 'Dwayne The Rock Johnson', 'Senior', '2122 Arthur St.', 'Male', '5.9', '190', 'Yes'); 1 row created. SQL> SQL> SQL> --BOYS BASKETBALL SQL> --1 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared)

2 VALUES ('4053', '50', 'M Basketball', 'Travis Scott', 'Senior', '2121 Auburn St.', 'Male', '5.9', '170', 'Yes'); 1 row created. SQL> SQL> --2 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4054', '50', 'M Basketball', 'Jeremy Jones', 'Senior', '2101 Communal St.', 'Male', '5.11', '157', 'Yes'); 1 row created. SQL> SQL> --3 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4055', '50', 'M Basketball', 'Michael Jordan', 'Junior', '1221 Jordan St.', 'Male', '6.4', '167', 'No'); 1 row created. SQL> SQL> --4 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4056', '50', 'M Basketball', 'Dennis Ferris', 'Freshman', '1918 Corn St.', 'Male', '5.8', '165', 'Yes'); 1 row created. SQL> SQL> --5 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4057', '50', 'M Basketball', 'Clay Thompson', 'Junior', '102 Dock St.', 'Male', '6.1', '145', 'Yes'); 1 row created. SQL> SQL> --6 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4058', '50', 'M Basketball', 'Cooper Anderson', 'Freshman', '2189 Delta St.', 'Male', '5.10', '156', 'Yes'); 1 row created. SQL> SQL> --7 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4059', '50', 'M Basketball', 'Nate Long', 'Senior', '2121 Dean St.', 'Male', '5.9', '198', 'Yes'); 1 row created. SQL>

SQL> --8 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4060', '50', 'M Basketball', 'Cilus Melson', 'Junior', '1010 Crescent St.', 'Male', '5.11', '177', 'No'); 1 row created. SQL> SQL> --9 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4061', '50', 'M Basketball', 'Wayne Winslow', 'Senior', '2208 Danish St.', 'Male', '5.6', '109', 'Yes'); 1 row created. SQL> SQL> --10 SQL> INSERT INTO ATHLETES (athlete_ID, team_ID, team_name, athlete_name, athlete_year, athlete_address, gender, height, weight, cleared) 2 VALUES ('4062', '50', 'M Basketball', 'Dmitri Stock', 'Freshman', '6509 Dublin St.', 'Male', '5.8', '199', 'Yes'); 1 row created.