Clear Lake Youth Swimming Team Management System (CLYSTMS)

Swim (Swimming Team Management System)
Formerly Clear Lake Youth Swimming Team Management System (CLYSTMS)

SWIM/CLYSTMS is the main project of the course offered in Spring 2017. Swim is the updated version with minor changes.

Script for creating swim for MySQL: CreateSwimDB.sql.txt

1. SWIM/CLYSTMS Specification

The Clear Lake Youth Swimming Team (CLYST) is a company supporting swimming teams for youth in the Clear Lake and surrounding areas. CLYST provides coaches for participating swimmers from the surrounding communities. Services include private lessons, classes, practices, meet supports, etc. There are currently 15 coaches and about 450 swimmers in 10 levels of competency and competition.

CLYST likes to have a powerful database system to support all functions of the company. Your task is to build the database to support a small portion of these functions in a drastically simplified way.

A youth, defined as 20 years old or younger, can be a swimmer. CLYSTMS should keep basic information of every swimmer, including his name, phone and email address. After joining CLYST, swimmers can enjoy services provided by the company, including participation in swimming meetings, which are called meets. To join CLYST, a swimmer must have a primary guardian (called caretaker), who is usually one of his parents. However, the primary caretaker can also be other relatives or older friends. A swimmer may also have other secondary caretakers, such as his other parent, uncles, aunts, etc. The name, phone, and address of a caretaker should be recorded. The relationships between a swimmer and his caretakers should also be stored. A person may be a caretaker for many swimmers. For example, a mother can be the primary caretaker of her two sons and a secondary caretaker for her two daughters and two nieces.

CLYSTMS stores basic information about coaches (names, phones and emails). Coaches have many functions. Only the functions relevant to this portion of CLYSTMS will be discussed.

A swimmer is labeled with a competition level when he joins CLYSM. He may improve and move to higher levels through the time.

Swimmers may compete in events in meets. CLYSTMS makes the jobs of the coaches easier to define and manage meets. A coach in charge of a meet can enter information into CLYSTMS. The coach may modify information about the meet subsequently. Other coaches can access but not modify meet information. However, any coaches may make comments. A meet has a place as the venue, of which its name, address, and phone should be recorded.

A meet may have many events. Each event has a specific competition level. Swimmer can join an event of his level through a Web application or other means. The time the swimmer commits on participating in an event is needed in various reports. A swimmer may participate in many events in the same meet.

A meet may have a list of tasks that need volunteers. Some events do not need volunteers. There is a general description for a list of tasks. The caretakers can be the volunteers. Even though they are called volunteers, for some events, at least one caretakers of a participating swimmer is required to volunteer on at least one task of the meet. If not, certain penalty may apply. The penalty (which may not be monetary) and the penalty amount (when the penalty is monetary) should be stored. A task has a name, an optional comment, and the number of volunteers needed. If the number is missing, it is assumed to be 1. After the coach in charge of a meet sets up a list of tasks, there is a Web application to allow the caretakers to sign up (volunteer) for tasks. The time for the caretaker to commit to volunteer for a task should be recorded. The caretaker may rescind from the commitment. If so, the rescind time should be stored. Whether a caretaker eventually has carried out a committed task should be noted. A comment on a task commitment can be made. The coach making the comment should be noted.

Reports and Functions

Participation Page:

A Web page allows the swimmer to find a meet, see all events in the meet and indicate his commitment to participate in an event of his level.

Caretaker Report: List all caretakers of a swimmer. For example, for swimmer, "Janet Jone", it may be:

Primary caretaker: Bobby Jones, father, since 10/1/2014
Other caretaker: Melinda Jones, mother, since 10/1/2014
Other caretaker: Koby Jones, uncle, since 5/3/2015
Other caretaker: Paula Lee, friend, since 6/1/2016
...

Swimmer's Level History Report: List the competition level history of a swimmer. For example, for swimmer "Janet Jone", it may be:

Level 2 (when joined CLYST): 10/1/2014; comment: May need improvement on overall physical condition.
Level 3: 1/5/2015; comment: Faster level promotion than expected.
Level 4: 12/1/2015
Level 5: 5/5/2016; comment: Second best time in 100 free-style among level 4 swimmers.
...

New swimmer report: Reports such as listing the new swimmers (names and emails) of CLYST in 2015, or from 1/20/2013 to 10/1/2014.

Venue Report: List the meets and their dates, starting times and ending times of a specific place.

Meet Schedule: List the titles, start times, end times, competition levels of all events of a specific meet.

Participation Reports: By event of a meet, by swimmer, etc. For example:

Meet: 3/1/2015 meet at Union City Regional Swimming Pool
Event: Female Level 3 100m Freestyle, Start: 11:15am; End: 11:25am.

Swimmer	Commit Time	Participated?	Result	Comment	Coach making comment
Jane Smith	2/12/2016	No		Sickness	Joe Page
Jenny Cox	2/13/2016	Yes	1:15.50; First runner-up	1.5 seconds faster than last meet.	Joe Page
Miranda Lee	2/15/2016	Yes	1:18:05
Paula Lee	2/18/2016	Yes	1:22:10	The second event immediately.	David Jorginlo
...	...	...	...	...

Task Commitment Reports: per meet or per caretaker. For example:

Task commitment Report for the caretaker "Bobby Jone":

   Meet: 3/1/2015 meet at Union City Regional Swimming Pool
   Task: Officiating
   Carried out: yes

   Meet: 3/8/2015 meet at Union City Regional Swimming Pool
   Task: Officiating
   Carried out: no
   Comment by coach "David Jorginlo": last minute sickness.

   Meet: 3/21/2015 meet at Union City Regional Swimming Pool
   Task: Officiating
   Rescinded: on 3/18/2015 15:10:25
   Carried out: no
   ...

2. Data Modeling

2.1 ER Modeling

Homework #4 of Spring 2017 offering:

(1) Download and install "EA Assistant v2.1" from http://highered.mheducation.com/sites/0072942207/student_view0/e_r_assistant.html. You will need it to draw Entity-Relationship Diagram (ERD).

(2) Consider the fictional Clear Lake Youth Swimming Team Management System (CLYSTMS) described above Construct a reasonable ERD to capture the data requirements of the project. List the entities, relationships with multiplicities, and attributes. Note that you should use the Crow's Foot notation as supported by EA Assistant v2.1. EA Assistant supports attributes for relationships and your ERD may have relationship attributes.

Send the .erd, .pdf (of a PDF printout of the .erd file) and .docx (of assumptions) files to the TA. Name the files: H4_<<Your name>>_<<Your Student ID>>.<<file extension>>. Your email subject should be something likes "CSCI 4333 HW #2; Jane Bond 0014007".

Solution:

ERD Diagram

er solution

2.2 UML Model

UML class diagramin Astah: swim_class.asta

3. Conversion of ER Model to Relational Schema

HW #5 of Spring 2017.

Using the suggested ERD solution above (not your own solution) and the specification of HW #4, construct the relation schema to implement the ER diagram for Clear Lake Youth Swimming Team Management System (CLYSTMS). For each relation, underline the PK and list:

all candidate keys: e.g. (1) FacultyId, (2) {FirstName, LastName, DepartmentNumber}
all foreign key references: e.g. (1) ChairFacultyId references Faculty(FacultyId), (2) StudentId references Student(StudentId)
Attributes that can be null (i.e. Nullable): e.g. (1) MiddleInitial, (2) Phone

Use h5sol_template.docx as the template. Rename it as H5_<<Your name>>_<<Your Student ID>>.docx and send it to the TA.

Solution:

Swim_h5sol.pdf

4. Relational Algebra and Relational Calculus

4.1 Relation Algebra

HW #6 Question (1) of Spring 2017.

Use Swim_h5sol.pdf as the relation schema for the Clear Lake Youth Swimming Team Management System (CLYSTMS) project as the basis of this homework.

(1) Use relational algebra to answer the following queries.

(a) Provide the names and email addresses of all swimmers.

(b) Provide the level history of the swimmer with Id 2 by showing the Level Ids and the Start Dates of achieving the level.

(d) Provide the event ids, titles, comments on the events and the names of coaches making the comments of all events that Swimmer with Id 4 participated in.

(e) Provide the names of all swimmers participated in 2 or more events (counting all meets).

Solution: Swim_RA_sol.pdf

RA_sol

4.2 Relation Calculus

HW #6 Question (2) of Spring 2017.

(2) Use Domain Relational Calculus (DRC) or Tuple Relational Calculus (TRC) to answer the same queries.

(a) Provide the names and email addresses of all swimmers.

(b) Provide the level history of the swimmer with Id 2 by showing the Level Ids and the Start Dates of achieving the level.

(d) Provide the event ids, titles, comments on the events and the names of coaches making the comments of all events that Swimmer with Id 4 participated in.

(e) Provide the names of all swimmers participated in 2 or more events (counting all meets).

Solution: Swim_RC_sol.pdf

DRC

TRC

5. SQL

5.1 SQL DDL

HW #6 Question (3) of Spring 2017.

(3) Install MySQL in your computer. Give the CREATE TABLE statements to create the CLYSTMS. The CREATE TABLE statements for the tables Coach and LevelHistory are provided below for you:

DROP TABLE IF EXISTS Coach;
CREATE TABLE Coach(
   CoachId    INT UNSIGNED AUTO_INCREMENT,
   LName      VARCHAR(30) NOT NULL,
   FName      VARCHAR(30) NOT NULL,
   Phone      VARCHAR(12) NOT NULL,
   EMail      VARCHAR(60) NOT NULL,
   CONSTRAINT Coach_pk PRIMARY KEY(CoachId)
);

CREATE TABLE LevelHistory(
   LH_Id      INT UNSIGNED AUTO_INCREMENT,
   SwimmerId INT UNSIGNED NOT NULL,
   LevelId    INT UNSIGNED NOT NULL,
   StartDate DATE NOT NULL,
   Comment    VARCHAR(250),
   CONSTRAINT levelhistory_pk PRIMARY KEY(LH_Id),
   CONSTRAINT levelhistory_ck_1 UNIQUE(SwimmerId, LevelId),
   CONSTRAINT levelhistory_swimmer_fk FOREIGN KEY(SwimmerId)
      REFERENCES Swimmer(SwimmerId),
   CONSTRAINT levelhistory_level_fk FOREIGN KEY(LevelId)
      REFERENCES Level(LevelId)
);

You should enforce entity and referential constraints in your SQL statements.

Solution:

CreateSwimDB.sql.txt

5.2 SQL DML: Select

HW #7 of Spring 2017.

Use the suggested solution of HW #5 and HW #6 as the relation schema for the CLYSTMS project as the basis of this homework.

Create CLYSTMS by using the following scripts: createSwimDB.sql.txt. They will be posted after all student submissions on HW #6 are collected. Do not use your own solution.

Provide the SQL statements for the following queries.

(1) Provide the names and phones of all swimmers currently in level (of id) 3.

+--------+-------+--------------+
| FName | LName | Phone |
+--------+-------+--------------+
| Jim | Khan | 832-116-2994 |
| Azalea | Khan | 832-116-2992 |
+--------+-------+--------------+
2 rows in set (0.00 sec)

(4) Provide the name of caretakers who are the primary (main) caretakers of at least two swimmers.

+--------+-------+
| FName | LName |
+--------+-------+
| Azalea | Khan |
+--------+-------+
1 row in set (0.00 sec)

(5) List the names of all caretakers who have volunteered for the task 'Recording' but not the task 'Officiating'. (It does not matter whether the caretaker rescinded, carried out the task, or not.)

+-------------------+-----------------------------+
| caretaker         | number of tasks volunteered |
+-------------------+-----------------------------+
| Azalea Khan       |                           4 |
| Elizabeth Johnson |                           3 |
| Katie Johnson     |                           3 |
| Joseph Khan       |                           2 |
| Jim Khan          |                           1 |
+-------------------+-----------------------------+
5 rows in set (0.00 sec)

(7) For every swimmer, provide her name, her primary caretaker name, current level, signed up meet names, dates and events in the following format.

Solution: h7sol.sql.txt

6. Embedded SQL in Python

HW #8 of Spring 2017.

(1) Install Python and pymysql (a Python's connector to MySQL: https://pypi.python.org/pypi/PyMySQL) in your computer. You need that for (2). (Note that we used MySQL Connector/Python in the last semester, which does not work with the latest version of Python as of this summer.) For pymysql documentation, see http://pymysql.readthedocs.io/en/latest/. (It does not support the number of rows returned, such as the

(2) Write a Python program to generate a meet report in the following exact format. For example:

Meet report
-----------
Meet #1 (UHCL Open at UHCL on 03/03/2016
    Event 50M Butterfly: (9:10:00 to 9:30:00)
    Event 100M Freestyle: (9:40:00 to 9:50:00)
    Event 100M Butterfly: (10:10:00 to 10:30:00)
    Event 200M Freestyle: (10:40:00 to 10:50:00)
Meet #2 (Shell Trial at CLHS on 08/04/2016
    Event 50M Butterfly: (9:15:00 to 9:35:00)
    Event 100M Freestyle: (9:45:00 to 9:55:00)
    Event 100M Butterfly: (10:15:00 to 10:35:00)
    Event 200M Freestyle: (10:45:00 to 10:55:00)
    Event 100M Breaststroke: (11:15:00 to 10:35:00)
Meet #3 (Clear Lake Contest at CLHS on 07/04/2019
    Event 200M Backstroke: (10:15:00 to 10:55:00)
    Event 50M Breaststroke: (11:15:00 to 10:35:00)

Please pay attention to details.

Your program should import dbconfig.py (copy below and save it in the file name in the same directory):

import configparser

# simplistic and no error handling.
def get_mysql_param(filename='dbconfig.ini', section='mysql'):

    config = configparser.ConfigParser()
    config.read(filename)

    return config['mysql']

which in turn reads the config.ini file:

[mysql]
host = localhost
database = c4333s17
user = youraccountname
password = yourpassword

Note that the tables of your CLYSTMS database should be put under the database name 'c4333s17'.Do not use any other database name as the TA will use the same database name for grading, Thus, not using the database name may result in your work not graded.

Solution:

Swim_h8.py.txt

7. Wed DB Development

HW #10 of Spring 2017.

If you have not done so, install XAMPP so you can have Apache in your computer. Follow the steps in the class lecture notes to ensure that Apache supports Python CGI. Also, for pymysql documentation, see http://pymysql.readthedocs.io/en/latest/.

(1) Write a Python CGI application, h10q1.py to accept two HTTP parameters:

sid: Swimmer Id
mid: Meet Id

The Web application displays the events the swimmer signed up for participation in the meet. For examples:

http://.../h10q1.py?sid=1&mid=2

h10_1

http://.../h10q1.py?sid=3&mid=1

h10_2

Host the Python application in your dcm account. Note that dcm server may have problems in using dbconfig.py and dbconfig.ini, so you may include your dam's Myself credential directly in h10q1.py. Mail in your URL for the Web application and the Python code.

Your program filenames should be h10q1.py.

Solution: (Note that the assignment and solution uses CLYSTMS, not SWIM so the output may be a different when port to SWIM.

CLYSTMS_h10q3.py.txt

8. Normalization Analysis

HW #9 of Spring 2017.

Conduct normalization analysis on CLYSTMS by listing the FDs and the highest normal form of each relation. You must use h9q1_template.docx. Complete the task by filling in the row "Normalization Analysis."

Solution:

Swim_Normal.pdf