Java 2

Week 12

Structured Systems Analysis
Spring Boot Introduction

Structured System Analysis Topics Covered

User Stories (discussed in Week 12)
User Personas (discussed in Week 12)
Use Case Narrative Write-ups
Wireframes
User Interface Drawings
Data Dictionary
ER Diagrams (partly discussed in Week 12)
Data Domain Models (Storage, View)
Data Flow Diagrams
Stored Procedure Signatures
Data Access Class Interfaces
Sequence Diagrams
State Chart Diagrams

User Stories

What is each user's role in our system.
What will the users be doing with the system and why.
Example:
"As a [role], I need to [action] in order to [goal]."
For the Java 3 demo project, I would like to build a web application that manages intramural sports for colleges and universities.
There are three types of users:
- Rec Center Admin
- Student
- Web Application Owner

All Users

As a user, I need to create a personal account and log in securely in order to access the application.
As a user, I need to be able to reset my password if I forget it in order to regain access to my account.
As a user, I need to manage my personal profile information and password in order to keep my details up to date.

Rec Center Admin

As a Rec Center Admin, I need to create, edit, and delete locations, fields, and courts in order to manage our available facilities.
As a Rec Center Admin, I need to create and manage leagues, activities, and one-off games for teams and individuals in order to build out a season's schedule efficiently.
As a Rec Center Admin, I need to view and approve team registrations in order to ensure they meet league requirements before being added to a league.
As a Rec Center Admin, I need to enter and confirm game scores in order to update league standings automatically.
As a Rec Center Admin, I need to send messages to individuals, teams, and entire leagues in order to communicate important updates and announcements.

Student

As a Student, I need to browse and search a list of available leagues, activities, and games in order to find something I want to join.
As a Student, I need to register as an individual or create a new team as a captain in order to participate in an activity.
As a Student, I need to accept or decline an invitation to join a team in order to be added to the roster.
As a Student, I need to view my personal and team schedule in order to know when and where my games are.
As a Student, I need to view league standings and team statistics in order to track my team's performance.
As a Student, I need to receive and reply to messages from the rec center admin and other participants in order to stay informed and coordinate with teammates.

Web Application Owner

As the Web App Owner, I need to create and manage school and Rec Center Admin accounts and set their privileges in order to onboard new customers.
As the Web App Owner, I need a support ticket system to manage and respond to issues from Rec Center Admins in order to provide effective customer service.

User Personas

A user persona is a semi-fictional character based on user research that represents a key segment of a target audience.
Help teams empathize with users, understand their needs and goals, and make user-focused decisions for products and services.
They typically include demographic information, roles, goals, needs, pain points, and a photo.

Persona 1

Name: Brett Baumgart
Role: Intramural Sports Coordinator

Goals:

Maximize student participation in intramural sports.
Streamline scheduling and communication processes.
Ensure accurate record-keeping for waivers, scores, and participation.
Provide a positive and accessible experience for all students.
Reduce the amount of time spent on manual data entry and conflict resolution.

Needs:

An intuitive platform for scheduling, team management, and communication.
Robust reporting features for participation and facility usage.
A reliable system for digital waiver collection and tracking.
Easy integration for embedded schedules within existing university websites.
Excellent customer support and training.

Persona 2

Name: Alex Chen
Role: A highly active and competitive student who loves sports.

Goals:

Easily find and sign up for new leagues and activities.
Stay informed about game times, locations, and any schedule changes.
Communicate with teammates and the Rec Center Admin.
Track standings and game results.
Manage multiple team commitments.
Connect with other students who share similar interests.
Have a simple way to fill out necessary waiver forms.

Needs:

A mobile-first, intuitive interface for browsing and signing up.
Push notifications for schedule updates, game reminders, and messages.
Integrated team chat functionality.
Easy team creation and invitation features.
A clear, accessible dashboard for all current activities and schedules.

Persona 3

Name: Marc Hauschildt
Role: Founder & CEO, Rec Season

Goals:

Achieve significant market share in the university recreation sector.
Secure recurring revenue through subscription models.
Build a reputation for reliable, innovative, and user-friendly software.
Attract top developer talent.
Generate revenue through advertising partnerships.

Needs:

An admin dashboard for managing university accounts, subscription tiers, and payment statuses.
Functionalities for tracking sales leads and customer interactions.
Analytics and reporting on platform usage, adoption, and revenue.
Module for managing ad inventory, placements, and billing.
Secure and efficient payment gateway integration.
Scalable infrastructure that can support rapid growth.

Cloud Services

Web apps need to be deployed to hosting services.
Not all hosting services support Java and Tomcat.
Some that do include:
- Microsoft Azure
- Amazon Web Services
- Google Cloud
- Oracle Cloud
- Heroku
- Digital Ocean
Sign up for a free Microsoft Azure account for students.
- Go to https://portal.azure.com/
- Click the menu icon in the upper left corner.
- Click "Cost Management + Billing".
- On the Overview > Summary tab, scroll down to find your credits remaining.

Cloud Certifications

Renewing Azure Credits

If you signed up for an Azure license last year, you may receive an email like this one saying you need to renew your license.

Renewing Azure Credits

Find the "Billing Alerts" tile and click the "View alerts" button.

Click the "Review and accept" button if shown to accept any new Terms and Conditions.
Click the "Renew now" button and follow the login prompts.
The Education service may show that you have 0 credits but that the credit expires in 365 days.
If you open your existing App Service, it should run.
Delete existing services and start from scratch.

Create a new Web App

Click on the Azure Portal home page to create an "App Service".
Click the "+ Create" button. Select "Create" next to Web App.
Do not create a database yet.
Choose the "Azure for Students" subscription.
Click to create a new Resource Group. Name it something like "java-kirkwood".
Select your desired region (US Central).
- Note that some US regions don't support free student accounts.
Uncheck "Unique default hostname (preview) on"
Give your web app a unique short name, like "yourname" or "yourname-java".
Select "Java 21" as the runtime stack.
Change the "Java web server stack" to Apache Tomcat 10.1.
Select "Free F1" for the hosting plan - See pricing

Create a new Web App

Click "Create".
The deployment process will take several minutes.
Create a new private GitHub repository called "java2ee-demo".
Commit and push your project to GitHub.
When the Azure deployment is complete, click "Go to Resource".
Copy the web app URL and paste it in the About section of your GitHub repository.
When you open the app, you will be greeted with "Hey, Java Developers!"
Click the Settings tab. Add "mlhaus" as a collaborator.

ER Diagram

An Entity Relationship (ER) Diagram is a type of flowchart that illustrates how “entities” such as users, objects or concepts relate to each other within a system.
Every project needs a users, roles, and permissions table.
- Users can have zero or more roles (role_user)
- Roles can have zero or more permissions (permission_role)

Create Table Statements

Here are CREATE TABLE scripts for a role-based access schema
The tables are created in an order that respects foreign key constraints.
I want everyone's project to use the same user schema. I will create roles like 'ADMIN', 'SCHOOL_ADMIN', and 'SCHOOL_USER'. I will create permissions like 'CREATE_LEAGUE' and 'JOIN_LEAGUE'

-- ---------------------------------
-- 1. `users`
-- Stores the main user account info.
-- ---------------------------------
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


-- ---------------------------------
-- 2. `roles`
-- Stores the available roles (e.g., ADMIN, USER, GUEST).
-- ---------------------------------
CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255)
);


-- ---------------------------------
-- 3. `permissions`
-- Stores specific actions (e.g., CREATE_POST, DELETE_USER).
-- ---------------------------------
CREATE TABLE permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(255)
);


-- ---------------------------------
-- 4. `user_roles` (Junction Table)
-- Maps users to roles (Many-to-Many).
-- ---------------------------------
CREATE TABLE user_roles (
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);


-- ---------------------------------
-- 5. `permission_role` (Junction Table)
-- Maps permissions to roles (Many-to-Many).
-- ---------------------------------
CREATE TABLE permission_role (
    permission_id INT NOT NULL,
    role_id INT NOT NULL,
    PRIMARY KEY (permission_id, role_id),
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);

Insert Statements

Here are the INSERT statements for roles, permissions, and permission_role based on the user stories written.
First, we define the two primary roles. SCHOOL_ADMIN (Rec Center Admin) and STUDENT (Student)

INSERT INTO roles (name, description) VALUES
('SCHOOL_ADMIN', 'Rec Center Admin: Can manage facilities, leagues, scores, and users.'),
('STUDENT', 'Student: Can join leagues, create teams, and view schedules.');

Next, we break down all the user story actions into specific, named permissions.

INSERT INTO permissions (name, description) VALUES
-- Base "User" Permissions (for all logged-in users)
('MANAGE_OWN_PROFILE', 'Allows user to update their personal info and password.'),
('USE_MESSAGING', 'Allows user to send/receive messages with other participants.'),

-- Student Permissions
('VIEW_LEAGUES', 'Allows user to browse and search available leagues and activities.'),
('REGISTER_FOR_LEAGUE', 'Allows user to register as an individual for a league.'),
('CREATE_TEAM', 'Allows user to create a new team as a captain.'),
('MANAGE_TEAM_INVITATIONS', 'Allows user to accept or decline invitations to a team.'),
('VIEW_OWN_SCHEDULE', 'Allows user to view their personal and team game schedule.'),
('VIEW_STANDINGS', 'Allows user to view league standings and team statistics.'),

-- School Admin Permissions
('MANAGE_FACILITIES', 'Allows user to C/R/U/D locations, fields, and courts.'),
('MANAGE_SCHEDULES', 'Allows user to C/R/U/D leagues, activities, and games.'),
('MANAGE_REGISTRATIONS', 'Allows user to view and approve team registrations.'),
('MANAGE_SCORES', 'Allows user to enter and confirm game scores.'),
('SEND_ANNOUNCEMENTS', 'Allows user to send messages to individuals, teams, and leagues.');

Insert Statements

Finally, we use the permission_role junction table to assign the permissions to each role.

-- --- Assign permissions to STUDENT role ---
INSERT INTO permission_role (role_id, permission_id) VALUES
-- Base permissions
((SELECT id FROM roles WHERE name = 'STUDENT'), (SELECT id FROM permissions WHERE name = 'MANAGE_OWN_PROFILE')),
((SELECT id FROM roles WHERE name = 'STUDENT'), (SELECT id FROM permissions WHERE name = 'USE_MESSAGING')),
-- Student-specific permissions
((SELECT id FROM roles WHERE name = 'STUDENT'), (SELECT id FROM permissions WHERE name = 'VIEW_LEAGUES')),
((SELECT id FROM roles WHERE name = 'STUDENT'), (SELECT id FROM permissions WHERE name = 'REGISTER_FOR_LEAGUE')),
((SELECT id FROM roles WHERE name = 'STUDENT'), (SELECT id FROM permissions WHERE name = 'CREATE_TEAM')),
((SELECT id FROM roles WHERE name = 'STUDENT'), (SELECT id FROM permissions WHERE name = 'MANAGE_TEAM_INVITATIONS')),
((SELECT id FROM roles WHERE name = 'STUDENT'), (SELECT id FROM permissions WHERE name = 'VIEW_OWN_SCHEDULE')),
((SELECT id FROM roles WHERE name = 'STUDENT'), (SELECT id FROM permissions WHERE name = 'VIEW_STANDINGS'));


-- --- Assign permissions to ADMIN role ---
-- Admins get all permissions: their own, plus all student permissions.
INSERT INTO permission_role (role_id, permission_id) VALUES
-- Base permissions
((SELECT id FROM roles WHERE name = 'SCHOOL_ADMIN'), (SELECT id FROM permissions WHERE name = 'MANAGE_OWN_PROFILE')),
((SELECT id FROM roles WHERE name = 'SCHOOL_ADMIN'), (SELECT id FROM permissions WHERE name = 'USE_MESSAGING')),
-- Student view permissions (Admins need to see what students see)
((SELECT id FROM roles WHERE name = 'SCHOOL_ADMIN'), (SELECT id FROM permissions WHERE name = 'VIEW_LEAGUES')),
((SELECT id FROM roles WHERE name = 'SCHOOL_ADMIN'), (SELECT id FROM permissions WHERE name = 'REGISTER_FOR_LEAGUE')),
((SELECT id FROM roles WHERE name = 'SCHOOL_ADMIN'), (SELECT id FROM permissions WHERE name = 'CREATE_TEAM')),
((SELECT id FROM roles WHERE name = 'SCHOOL_ADMIN'), (SELECT id FROM permissions WHERE name = 'MANAGE_TEAM_INVITATIONS')),
((SELECT id FROM roles WHERE name = 'SCHOOL_ADMIN'), (SELECT id FROM permissions WHERE name = 'VIEW_OWN_SCHEDULE')),
((SELECT id FROM roles WHERE name = 'SCHOOL_ADMIN'), (SELECT id FROM permissions WHERE name = 'VIEW_STANDINGS')),
-- Admin-specific permissions
((SELECT id FROM roles WHERE name = 'SCHOOL_ADMIN'), (SELECT id FROM permissions WHERE name = 'MANAGE_FACILITIES')),
((SELECT id FROM roles WHERE name = 'SCHOOL_ADMIN'), (SELECT id FROM permissions WHERE name = 'MANAGE_SCHEDULES')),
((SELECT id FROM roles WHERE name = 'SCHOOL_ADMIN'), (SELECT id FROM permissions WHERE name = 'MANAGE_REGISTRATIONS')),
((SELECT id FROM roles WHERE name = 'SCHOOL_ADMIN'), (SELECT id FROM permissions WHERE name = 'MANAGE_SCORES')),
((SELECT id FROM roles WHERE name = 'SCHOOL_ADMIN'), (SELECT id FROM permissions WHERE name = 'SEND_ANNOUNCEMENTS'));

Insert Statements

Here’s a quick example of how you would populate and query users table.

-- Note: 'hashed_password_...' is just a placeholder for a real bcrypt/argon2 hash
INSERT INTO users (first_name, last_name, email, password_hash) VALUES
('Brett', 'School Admin', 'brett.baumgart@kirkwood.edu', 'hashed_password_for_brett'),
('Alex', 'Student', 'alex.student@student.kirkwood.edu', 'hashed_password_for_alex');

-- Assign roles to users
INSERT INTO user_roles (user_id, role_id) VALUES
((SELECT id FROM users WHERE email = 'brett.baumgart@kirkwood.edu'), (SELECT id FROM roles WHERE name = 'SCHOOL_ADMIN')),
((SELECT id FROM users WHERE email = 'alex.student@student.kirkwood.edu'), (SELECT id FROM roles WHERE name = 'EDITOR'));

This is a common query: "What permissions does user 'brett.baumgart@kirkwood.edu' have?"

SELECT
    u.id, u.first_name, u.last_name, u.email
    r.name AS role_name,
    p.name AS permission_name
FROM
    users u
JOIN
    user_roles ur ON u.id = ur.user_id
JOIN
    roles r ON ur.role_id = r.id
JOIN
    permission_role pr ON r.id = pr.role_id
JOIN
    permissions p ON pr.permission_id = p.id
WHERE
    u.email = 'brett.baumgart@kirkwood.edu';