Csc 8711, Databases and the Web - Programming Project 1

Due: Sunday, February 3rd
Teams of up to 3 members are allowed for this assignment.

Basketball League (PhP/MySQL)

In this programming assignment, you will implement a Web-based solution as well as an Android app for a city basketball league, such the one found at East Marietta Basketball. The league consists of several divisions and each division is made up of several teams. Teams within a division play each other according to a schedule. The league has four types of user:
  1. Director of Basketball Operations (DIRECTOR-ADMIN): This person is the super admin for the league. Normally, she would have the ability to make all updates to the database. But, her main responsibility is to create the divisions and assign commissioners to the division. Upon login, she must have the following capabilities: 
      ADD/DELETE/UPDATE Division and Commissioner Admin
  ADD/DELETE/UPDATE Team
  ADD/DELETE/UPDATE Schedule
  ADD/DELETE/UPDATE Coach Admin
  UPDATE GAME SCORE
  2. Commissioner of Division (COMMISSIONER-ADMIN): This person manages the games within a division. Normally, she would have the ability to make all updates to teams, schedules, and game scores. But, her main responsibility is to create the teams and assign coaches to the teams. Upon login, she must have the following capabilities: 
      ADD/DELETE/UPDATE Team
  ADD/DELETE/UPDATE Schedule
  ADD/DELETE/UPDATE Coach Admin
  UPDATE GAME SCORE
  3. Coach of Team (COACH-ADMIN): This person manages their own game scores. The only updates this person can perform are games scores for their games. It is usually the custom in the league for the winning coach to update the score for a game, but in this system you will allow either coach to update the score for a particular game in which they were involved. Upon login, the coach must have the following capability: 
      UPDATE GAME SCORE
  4. Member of Interested Public: This user can only browse the database from the Web as well as from the Android app. The BROWSE capability of the system is described below.

BROWSE

The main browse page should list the divisions in the league, each hyperlinked to a standings page for the division. Each entry in the standings page should be hyperlinked to a game detail page. Sample pages are shown below: All of the browse pages should have login/password text boxes which should allow administrators to login (these textboxes are not should in the sample pages above). Once successfully logged in, the pages should have appropriate add/delete/update capabilities, depending on the type of user.

Android App

The app should allow the user to browse the database. The main screen should be a scrollable list of division names. Choosing a division should produce a scrollable list of teams (coach name and win-loss record should be displayed - sorted by number of wins) in the division, and choosing a team should display a list of all games for that team (with scores displayed for finished games - sorted by date).

Server side code (servlet): Here is a servlet: EMBLServices.java which takes a GET parameter, query, and returns the result of executing the query. This servlet can be called from the Android app. Please change the MySQL connection string to reflect your id and password.

Client side code: Here is a Java class: MySQLClient.java which encapsulates the code to communicate with the servlet. A sample use of the client class is shown below: 

  ArrayList<String> teams = new ArrayList<String>();
  String result = "";
  MySQLClient mc = 
    new MySQLClient("http://tinman.cs.gsu.edu:9999/~raj/servlet/EMBLServices");
  try {
    String query = "select tno,coach from team Order By tno"; 
    result = mc.runSQL(query);
    String[] rows = result.split("\n");
    for(int i = 1; i < rows.length; i++){
      String[] cols = rows[i].split(",");
      teams.add(cols[1]);
    }
  } catch (Exception e) {
	Log.d("MyAppException",e.getMessage());
    }

MySQL Database

The following is the SQL script for the database. The table structure should not be changed. Sample data is shown for one division. 
drop table division;
create table division (
  dcode varchar(20),
  dname varchar(50),
  primary key (dcode)
);
insert into division values ('G10','10U Girls');
insert into division values ('G13','13U Girls');
insert into division values ('G1418','14-18U Girls');
insert into division values ('B10I','10U Boys Division I');
insert into division values ('B10II','10U Boys Division II');
insert into division values ('B12I','12U Boys Division I');
insert into division values ('B12II','12U Boys Division II');
insert into division values ('B14I','14U Boys Division I');
insert into division values ('B14II','14U Boys Division II');
insert into division values ('B1618I','16-18U Boys Division I');
insert into division values ('B1618II','16-18U Boys Division II');

drop table team;
create table team (
  dcode varchar(20),
  tno integer(2),
  coach varchar(25),
  primary key (dcode,tno),
  foreign key (dcode) references division(dcode)
);
insert into team values ('B1618I',1,'Karcher');
insert into team values ('B1618I',2,'Humble');
insert into team values ('B1618I',3,'Storey');
insert into team values ('B1618I',4,'Morrison');
insert into team values ('B1618I',5,'Komitor');
insert into team values ('B1618I',6,'Richardson');
insert into team values ('B1618I',7,'Wright');
insert into team values ('B1618I',8,'Markowitz');

drop table schedule;
create table schedule (
  dcode varchar(20),
  gid integer(3),
  gametime datetime,
  gym varchar(25),
  awayteam integer(2),
  hometeam integer(2),
  awayscore integer(3),
  homescore integer(3),
  primary key (dcode,gid),
  foreign key (dcode,awayteam) references team(dcode,tno),
  foreign key (dcode,hometeam) references team(dcode,tno)
);
insert into schedule values ('B1618I',1,'2012-12-01 18:50:00','East Cobb',6,1,49,52);
insert into schedule values ('B1618I',2,'2012-12-01 10:00:00','East Cobb',7,5,50,64);

insert into schedule values ('B1618I',3,'2012-12-02 13:10:00','Walton HS Main',1,7,54,49);
insert into schedule values ('B1618I',4,'2012-12-02 14:20:00','Walton HS Main',6,4,43,40);
insert into schedule values ('B1618I',5,'2012-12-02 15:30:00','Walton HS Main',5,2,34,63);
insert into schedule values ('B1618I',6,'2012-12-02 16:40:00','Walton HS Main',8,3,47,36);

insert into schedule values ('B1618I',7,'2012-12-08 18:50:00','East Cobb',8,5,43,36);
insert into schedule values ('B1618I',8,'2012-12-08 10:00:00','East Cobb',2,3,81,77);

insert into schedule values ('B1618I',9, '2012-12-09 13:10:00','Walton HS Main',3,7,55,48);
insert into schedule values ('B1618I',10,'2012-12-09 14:20:00','Walton HS Main',5,1,35,48);
insert into schedule values ('B1618I',11,'2012-12-09 15:30:00','Walton HS Main',2,6,53,45);
insert into schedule values ('B1618I',12,'2012-12-09 16:40:00','Walton HS Main',4,8,52,51);

insert into schedule values ('B1618I',13,'2012-12-15 13:00:00','Phillips Arena',7,2,60,70);
insert into schedule values ('B1618I',14,'2012-12-15 14:20:00','Phillips Arena',3,1,55,60);
insert into schedule values ('B1618I',15,'2012-12-15 18:50:00','East Cobb',4,5,43,54);
insert into schedule values ('B1618I',16,'2012-12-15 10:00:00','East Cobb',8,6,39,32);

insert into schedule values ('B1618I',17,'2012-12-16 13:10:00','Walton HS Main',3,5,52,40);
insert into schedule values ('B1618I',18,'2012-12-16 14:20:00','Walton HS Main',7,6,59,31);
insert into schedule values ('B1618I',19,'2012-12-16 15:30:00','Walton HS Main',1,4,52,59);
insert into schedule values ('B1618I',20,'2012-12-16 16:40:00','Walton HS Main',2,8,56,42);

insert into schedule values ('B1618I',21,'2013-1-13 13:10:00','Walton HS Main',3,7,-1,-1);
insert into schedule values ('B1618I',22,'2013-1-13 14:20:00','Walton HS Main',4,5,-1,-1);
insert into schedule values ('B1618I',23,'2013-1-13 15:30:00','Walton HS Main',6,2,46,53);
insert into schedule values ('B1618I',24,'2013-1-13 16:40:00','Walton HS Main',1,8,51,50);

insert into schedule values ('B1618I',25,'2013-1-19 18:50:00','East Cobb',3,1,-1,-1);
insert into schedule values ('B1618I',26,'2013-1-19 10:00:00','East Cobb',4,7,-1,-1);

insert into schedule values ('B1618I',27,'2013-1-20 13:10:00','Walton HS Main',8,1,-1,-1);
insert into schedule values ('B1618I',28,'2013-1-20 14:20:00','Walton HS Main',5,7,-1,-1);
insert into schedule values ('B1618I',29,'2013-1-20 15:30:00','Walton HS Main',2,4,-1,-1);
insert into schedule values ('B1618I',30,'2013-1-20 16:40:00','Walton HS Main',3,6,-1,-1);

insert into schedule values ('B1618I',31,'2013-1-26 18:50:00','East Cobb',4,2,-1,-1);
insert into schedule values ('B1618I',32,'2013-1-26 10:00:00','East Cobb',6,8,-1,-1);

insert into schedule values ('B1618I',33,'2013-1-27 13:10:00','Walton HS Main',3,4,-1,-1);
insert into schedule values ('B1618I',34,'2013-1-27 14:20:00','Walton HS Main',5,6,-1,-1);
insert into schedule values ('B1618I',35,'2013-1-27 15:30:00','Walton HS Main',1,2,-1,-1);
insert into schedule values ('B1618I',36,'2013-1-27 16:40:00','Walton HS Main',7,8,-1,-1);

drop table users;
create table users (
  userid varchar(15),
  password varchar(25),
  usertype integer(1), // 1=LeagueAdmin, 2=DivisionAdmin, 3=CoachAdmin
  divisionCode varchar(20),
  teamNumber integer(2),
  primary key (userid),
  foreign key (divisionCode,teamNumber) references team(dcode,tno)
);
insert into users values ('clark','clark',1,null,null);