ER diagram

The model is a representation of the application's data in code.

model

view

controller

The view is any output representation of information. In this case: tables.

The controller accepts input and converts it to commands for the model or the view.

model

package models;
import play.db.ebean.*;

@Entity
@Table(name = "Players")
public class Players extends Model {
	@Id
	@Column(name = "id")
	public int id;

	@Column(name = "first_name")
	public String first_name;

	@Column(name = "last_name")
	public String last_name;

	@Column(name = "email")
	public String email;

	@Column(name = "telephone")
	public String telephone;

	@OneToOne(cascade = CascadeType.ALL, mappedBy = "myPlayer", fetch = FetchType.EAGER)
	public Brass myBrass;

	@OneToOne(cascade = CascadeType.ALL, mappedBy = "myPlayer", fetch = FetchType.EAGER)
	public Woodwind myWoodwind;

	@OneToOne(cascade = CascadeType.ALL, mappedBy = "myPlayer", fetch = FetchType.EAGER)
	public Keyboard myKeyboard;

	@OneToOne(cascade = CascadeType.ALL, mappedBy = "myPlayer", fetch = FetchType.EAGER)
	public Percussion myPercussion;

	@OneToOne(cascade = CascadeType.ALL, mappedBy = "myPlayer", fetch = FetchType.EAGER)
	public SymphonicString mySymphonicString;

	@ManyToOne
        @JoinColumn(name = "orchestra_id", referencedColumnName = "id")
	public Orchestra myOrchestra;
}

Example with my Players table.

The Play framework manages database connections using  JavaEbean.

view

controller

public class PlayersControl extends Controller {

    public static Result createForm() {
        Form<Players> playersForm = form(Players.class);
        return ok(views.html.playersForm.render(playersForm));
    }
    public static Result save() {
    	Form<Players> playersForm = form(Players.class).bindFromRequest();
    	if (playersForm.hasErrors()) {
    		return badRequest(views.html.playersForm.render(playersForm));
    	}
    	Players myPlayers = playersForm.get();
        myPlayers.setOrchestra(Integer.parseInt(playersForm.data().get("orchestra_id")));
        myPlayers.save();
    	return redirect(routes.PlayersControl.players());
    }
    public static Result createUpdateForm(int id) {
        Form<Players> playersUpdateForm = form(Players.class).fill(Players.find.byId(id));
        return ok(views.html.playersUpdateForm.render(playersUpdateForm,id));
    }
    public static Result update(int id) {
        Form<Players> playersUpdateForm = form(Players.class).fill(Players.find.byId(id)).bindFromRequest();
        if (playersUpdateForm.hasErrors()) {
            return badRequest(views.html.playersUpdateForm.render(playersUpdateForm,id));
        }
        Players updatePlayers = playersUpdateForm.get();
        updatePlayers.setId(id);
        updatePlayers.setOrchestra(Integer.parseInt(playersUpdateForm.data().get("orchestra_id")));
        updatePlayers.update();
        return redirect(routes.PlayersControl.players());
    }
    public static Result players() {
    	return ok(players.render(
    	    Players.find.all()
    	));
    }
    public static Result delete(int id) {
	Players.delete(id);
        return redirect(routes.PlayersControl.players());
    }
}

Create

Update

Delete

Read

create table Brass (
  id                        integer auto_increment not null,
  instrument                varchar(255),
  instrument_key            varchar(255),
  clef                      varchar(255),
  seat                      integer,
  player_id                 integer,
  constraint pk_Brass primary key (id));

create table Keyboard (
  id                        integer auto_increment not null,
  instrument                varchar(255),
  clef                      varchar(255),
  player_id                 integer,
  constraint pk_Keyboard primary key (id));

create table Orchestra (
  id                        integer auto_increment not null,
  name                      varchar(255),
  type                      varchar(255),
  num_players               integer,
  style                     varchar(255),
  constraint pk_Orchestra primary key (id));

create table Percussion (
  id                        integer auto_increment not null,
  instrument                varchar(255),
  type                      varchar(255),
  player_id                 integer,
  constraint pk_Percussion primary key (id));

create table Players (
  id                        integer auto_increment not null,
  first_name                varchar(255),
  last_name                 varchar(255),
  email                     varchar(255),
  telephone                 varchar(255),
  orchestra_id              integer,
  constraint pk_Players primary key (id));

create table SymphonicString (
  id                        integer auto_increment not null,
  instrument                varchar(255),
  section                   varchar(255),
  seat                      integer,
  clef                      varchar(255),
  player_id                 integer,
  constraint pk_SymphonicString primary key (id));

create table Woodwind (
  id                        integer auto_increment not null,
  instrument                varchar(255),
  clef                      varchar(255),
  player_id                 integer,
  constraint pk_Woodwind primary key (id));

mysql> select * from information_schema.global_variables 
       where variable_name = 'tx_isolation';
            +---------------+-----------------+
            | VARIABLE_NAME | VARIABLE_VALUE  |
            +---------------+-----------------+
            | TX_ISOLATION  | REPEATABLE-READ |
            +---------------+-----------------+
1 row in set (0.00 sec)

isolation level

Repeatable-Read

Why? Because it's the mySQL default, provides high availibility and serves the purpose of my project.

(also I'm lazy)

normalization

Third-Normal Form

Each attribute in a table is dependent upon the primary key: id.

+----+-------------+----------------+--------+------+-----------+
| id | instrument  | instrument_key | clef   | seat | player_id |
+----+-------------+----------------+--------+------+-----------+
|  1 | Horn        | F              | Bass   |    1 |         1 |
|  2 | Horn        | F              | Bass   |    2 |         2 |
|  3 | Trumpet     | B♭             | Treble |    3 |         3 |
|  4 | Trumpet     | B♭             | Treble |    4 |         4 |
|  5 | French Horn | F              | Bass   |    1 |        36 |
|  6 | French Horn | F              | Bass   |    2 |        37 |
|  7 | French Horn | F              | Bass   |    3 |        38 |
|  8 | French Horn | F              | Bass   |    4 |        39 |
|  9 | French Horn | F              | Bass   |    5 |        40 |
| 10 | French Horn | F              | Bass   |    6 |        41 |
| 11 | French Horn | F              | Bass   |    7 |        42 |
| 12 | Trumpet     | B♭             | Treble |    1 |        43 |
| 13 | Trumpet     | B♭             | Treble |    2 |        44 |
| 14 | Trumpet     | B♭             | Treble |    3 |        45 |
| 15 | Trumpet     | B♭             | Treble |    4 |        46 |
| 16 | Trumpet     | B♭             | Treble |    5 |        47 |
| 17 | Trombone    | B♭             | Tenor  |    1 |        48 |
| 18 | Trombone    | B♭             | Tenor  |    2 |        49 |
| 19 | Trombone    | B♭             | Tenor  |    3 |        50 |
| 20 | Trombone    | B♭             | Tenor  |    4 |        51 |

triggers

delimiter //

CREATE TRIGGER update_players BEFORE INSERT ON Players
	FOR EACH ROW
	BEGIN
		SET @id = new.orchestra_id;
		UPDATE Orchestra 
		SET num_players = num_players + 1
		WHERE id = @id;
	END;//

delimiter ;

delimiter //

CREATE TRIGGER update_players_del AFTER DELETE ON Players
	FOR EACH ROW
	BEGIN
		SET @id = old.orchestra_id;
		UPDATE Orchestra 
		SET num_players = num_players - 1
		WHERE id = @id;
	END;//

delimiter ;

procedure

delimiter //

CREATE PROCEDURE test_trigger (
  IN p_first_name                varchar(255),
  IN p_last_name                 varchar(255),
  IN p_email                     varchar(255),
  IN p_telephone                 varchar(255),
  IN p_orchestra_id              integer
)
BEGIN
	INSERT INTO Players (
            first_name,
            last_name,
            email,
            telephone,
            orchestra_id
        )
	VALUES (
            p_first_name, 
            p_last_name, 
            p_email, 
            p_telephone, 
            p_orchestra_id
        );
END//

delimiter ;

CALL test_trigger (
    'Bob',
    'McTestington',
    'test@example.com',
    '888-847-3821',1
);

TEST

demo

CODE