Course Enrollment and Administration System

(1)

Course Enrollment and Administration System

Database Application Development

by

Kong Koon Kit

Chan Yin Mo

Law Yu Yeung

Leung Shiu Hong

Advised by

Prof. Frederick H. Lochovsky

Submitted in partial fulfillment

of the requirements for COMP 4981

in the

Department of Computer Science and Engineering

The Hong Kong University of Science and Technology

(2)

1 Introduction ... 1

2 Progress ... 2

Searching for a Sponsor ... 2

System Requirements Capture and System Analysis ... 2

Choice of Database Management System and Programming Language ... 2

Design Phase ... 2

Implementation Phase ... 4

Testing and Evaluation Phase ... 4

3 Project

Planning ... 5

Design Phase ... 5 Implementation Phase ... 5 Testing Phase ... 5 Evaluation Phase ... 6 Schedule ... 6

4 References ... 7

5 Appendices ... 8

Appendix A – Comparison of Different Database Management Systems (DBMS) . 8 Appendix B – Application Description ... 9

Appendix C – Data Description ... 11

Appendix D – Object Model ... 15

Appendix E – Logical Database Design ... 19

Appendix F – Use Case Model ... 26

Appendix G – User Interfaces ... 54

Appendix H – Database Access Requirements ... 76

Appendix I – Gantt Chart ... 79

Appendix J – Job Division ... 80

Appendix K – Meeting Minutes with the YMCA ... 81

(3)

The Hong Kong University of Science & Technology ₁ Department of Computer Science and Engineering

1 Introduction

The need for powerful and flexible data management systems is increasing in science, engineering, business and the personnel fields. The success of an organization depends on its ability to acquire accurate and timely data about its operation, to manage this data effectively, and to use it to analyze its own activities. In our final year project, a web-based course enrollment system for The Chinese YMCA of Hong Kong will be implemented to manage a vast amount of data, and to answer a given query with the relevant information in as little time as possible. The system is built by applying the techniques of Internet programming, database design and database management.

The objective and purpose of the Online Enrollment and Administrative System (OEAS) is to provide real-time registration for the YMCA members and relevant reports for staff. It helps the staff to input and retrieve data in a more efficient way and the members can register for any course or programme at the nearest YMCA centre. The system is also flexible because the user only needs to connect to the Internet and use a web browser to access the system server to use the OEAS Our goal for the project is to develop an OEAS for the YMCA so that the staff in the YMCA can do the course registration for members in every center more efficiently. There are 5 parts: database design, permission setting, course registration, database maintenance and visualization. For developing the centralized database system, the software engineering skills we have learnt are applied. The system requirements capture, system analysis, database and application design, implementation, testing and evaluation are performed. During the system development, we need to show our ability to work with database management systems as well as the users. An opportunity is given for us to integrate and apply the knowledge we have gained from various courses to solve a real world problem and to improve our communication skills in working with our sponsor.

We had encountered different problems during last few months. For example, we did not have much experience in getting the requirements from a real company. We would find that the requirements that we captured are not clear after the meeting with the YMCA. As a result, we needed to send an e-mail or make a phone call with them directly to clarify the ambiguous points. The biggest problem we faced was the design of the database. There are various kinds of activities and offerings in the YMCA and each activity or offering includes different formats of data for the same kind of information. In addition, there are many related data that we need to handle for each offering in the YMCA. It is difficult to store and query on all this information efficiently. In order to have a good logical database design, we have met our advisor, Prof. Lochovsky, to seek advice and make improvements. During these meetings, we did the amendment each time. Our final conceptual database design has been approved and the other phases will be continued.

(4)

The Hong Kong University of Science & Technology ₂ Department of Computer Science and Engineering

2 Progress

Searching for a Sponsor

After having decided to work on this project, we need to find a company to sponsor it. Several companies and organizations have been approached including a trading company, a tutorial centre and our ultimate sponsor, a Christian organization. Brief discussions have been conducted with each of them in order to understand their daily operations and approximate scope of the project they expect. Then, a meeting has been held to compare the scope and achievability of each project and finally, we decided to work on the project of the Chinese YMCA of Hong Kong.

System Requirements Capture and System Analysis

Once we confirmed the sponsor, we started to capture the system requirements and then performed the system analysis. Four meetings have been held with our sponsor about the background, existing database programs, computing facilities and daily operations of the organization. The existing database structure of our sponsor has been studied in order to understand more about the existing system, which greatly helped us in the system requirements capture and system analysis. The system requirements capture has been finished and the application and data description have been constructed. The analysis of the system requirements has been finished and the preliminary use case model and preliminary object diagram have been constructed.

Choice of Database Management System and Programming Language

Some materials have been studied during the summer vacation to prepare for this project. We mainly searched for websites that provided the syntax of programming languages including ASP (Active Server Page), JSP (Java Server Page), DHTML (Dynamic HyperText Markup Language) and PHP (Personal Homepage Programming, Hypertext Preprocessor). One of the websites is www.php.net. This website provides the functions and syntax of PHP. Another website is www.mysql.com, which provides the usage of the MySQL Server. We also found some books for reference. The Core PHP Programming by Leon Atkinson presents the techniques and issues in using PHP. Database Management Systems by Raghu Ramkrishnan and Johannes Gehrke presents the database management skill and design needed to produce the most efficient database system. After having a meeting with our sponsor, we have agreed on using MySQL Server, which is a free and robust database management system, to manage our database. A comparison of different types of DBMS is given in the appendices. In addition, PHP will be used as the programming language and the system will be running under Linux.

Design Phase

System Design

The client application and the server side are running on two different platforms; the client side is running on Windows and the server is running on Linux as shown below.

(5)

The Hong Kong University of Science & Technology ₃ Department of Computer Science and Engineering In order to connect the client and server, the Hypertext Transfer Protocol (HTTP) is chosen in the system design. In addition, to connect the server side program to the database server, the system will connect to our DBMS through the open database connectivity (ODBC) driver, developed by the database server vendor, MySQL AB.

Database Design

After having finished the system analysis, we started to design the structure of the database. Several diagrams for database design have been constructed and modified after receiving suggestions from our advisor in a number of meetings with him. All these diagrams were constructed based on the results of our system analysis. Furthermore, we made several other improvements to our database design.

Based on the application and data description we wrote during the system requirement capture and system analysis, construction of a preliminary object model was started. Since there is no existing course enrolment system, we had great flexibility in the design such as designing the interface layout, the invoice format, etc. However, this also gave us difficulties such as how the system actually runs and what kinds of information should be displayed on the web pages. A number of meetings were held with our sponsor in order to clarify the requirements. We reviewed the outcome of these meetings and modified our object model throughout the fall semester in order to reach agreement with our sponsor. We constructed the final ER diagram and our sponsor approved it.

We worked on the logical design, which was based on the application and data description, and the object model. As the object model changed many times, we significantly modified our logical

(6)

The Hong Kong University of Science & Technology ₄ Department of Computer Science and Engineering design. After several meetings with our advisor, we arrived at the final logical design.

Then the writing of the data description was started. We derived the information from all the finalized descriptions, diagrams and models. The data description is fully explained in the definition of Member, Staff, Instructor, Function, Course, Permission, Group, Ability, CourseOffering, FunctionOffering, Venue and Enroll that are in our database design.

Finally, we worked on the functional design. Not only is it based on the previous finalized design, our advisor also recommended that we complete the functional design in the format of a database access requirements. In the database access requirements, we gave the name, detailed description, database input and output, and relations used for each application. Types of users and frequency of usage of each application program are also written in each application program.

Implementation Phase

Before starting to code the application, the Linux operating system and the MySQL database server were set up in the Linux environment. The RedHat 7.2 User Manual for installation of Linux was read for reference. The MySQL Reference Manual was also read for its installation guidelines. When problems occurred in the installation or operations in Linux or MySQL, the above references were read and the solutions could be found.

As MySQL is a free DBMS, functionalities, like transactions and foreign keys, are not supported. A transaction is a single logical unit of work that is formed from collections of operations. Foreign keys are used for checking the referential integrity. Referential integrity is used to ensure that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation. A possible solution has been found which is to check the referential integrity constraint (foreign key) in our client application program and establish an ordering on table locking to prevent deadlock situations when we perform transactions in our system.

In addition, a system utility was found in the sourceforge.net that provided useful tools to configure and test our database design. This software is called phpMyAdmin and it is developed by a voluntary organization. phpMyAdmin is a tool written in PHP and is intended to handle the administration of data transmission of MySQL through the Internet by the HTTP protocol. Currently, it can create and drop tables in the database, delete, edit and add fields, execute any SQL statement and manage keys on fields.

Testing and Evaluation Phase

Because the client application coding has not started, the testing and evaluation of the application has not been started yet. However, testing and evaluation of our design has been done. After the user requirements capture was finished and the application and the data description were constructed, the description was sent to our sponsor to check its validity and completeness. During the construction of the use case model and object model based on the description, the design was checked among our group members and double-checked with our supervisor. After that, when

(7)

The Hong Kong University of Science & Technology ₅ Department of Computer Science and Engineering constructing the logical design, we cross-checked with our previous design and our supervisor also helped us in checking and evaluating our design. The functional design was also checked and revised. Our supervisor gave us much help in improving and maintaining the correctness of the design and all these materials were also sent to our sponsor for checking and evaluation. For our system design, all of the interfaces have been created and have been sent to the sponsorr for verification. The verification showed that the interfaces design can get all the necessary input from the user and give the output for all important information. The testing of our design will be continued until we finish the implementation and confirm that our design is fully-functional without error.

3 Project

Planning

A detailed schedule of our project is listed in the Gantt chart. The development process of our project was divided into four phases, namely design phase, implementation phase, testing phase and evaluation phase.

Design Phase

During the design phase, the application design and the database design were focused. At this stage, the system requirements capture, system analysis, preliminary database design, preliminary application design and the revision of the database design were completed. Currently, the application design is revised and our design will be modified if any problems are found in the implementation phase.

Before starting the implementation of our system, the MySQL database server was set up and our logical database design was mapped into the physical design. After that, the user interface (UI) was designed by Norman. Macromedia Ultradev and Adobe Photoshop were used to draw the UI.

Implementation Phase

After finishing the user interface design, the construction of the client application program will be started by using the programming language PHP. In order to produce a system with fewer bugs and less inconsistency in the user interface, the application will be built incrementally. That is, each of our members will focus on one unit of the application at a time and enhances its functionality step by step. In this way it is hoped that most of the bugs and errors can be detected at an early stage. After checking each unit a number of times, we will integrate it into the main system and thoroughly test the system.

Testing Phase

The testing of the system will be started when we start the implementation. The “Black Box” testing technique will be adopted for testing our database system. With this technique, test cases will be derived to verify the functionality of the system based on the inputs and outputs without regard to the internal workings of the system. The test cases will consist of valid and invalid input conditions. The actual system output will be verified with the predicted output to check if the system works

(8)

The Hong Kong University of Science & Technology ₆ Department of Computer Science and Engineering properly. Firstly, unit testing will be performed, which concentrates on functions in each module. Then, integration testing will be performed to test the interactions between modules. Finally, the whole system will be tested.

Evaluation Phase

A software demonstration and producing a user manual of our designed system will be performed for our sponsor to show the functionalities and user-friendliness of the system. The performance and the compatibility of our system with the existing database our sponsor currently uses will be evaluated, mainly focused on the concurrent access performance of the system. Our sponsor will compare the reliability and user-friendliness during the preliminary uses of the system and give us feedback about the application.

Schedule

Date Deliverables

30/06/01-31/8/01 Find sponsor, search and read the materials for FYP

01/09/01-31/12/01 Requirements capture and refine requirements from YMCA.

03/09/01-27/09/01 Initial conceptual database design and prepare for the proposal presentation.

27/09/01-3/12/01 Complete the conceptual database design -- Object Model and the data description

02/01/02-15/01/02 Complete the logical database design – Logical Schema 02/01/02-29/01/02 Complete the database access requirement

02/01/02-29/01/02 Complete the application design and data dictionary 02/01/02-29/01/02 Initial interface design

15/01/02-25/01/02 Configure the database management system and the web server in order to start the implementation state

25/01/02-10/02/02 Database Implementation

02/01/02-06/02/02 Prepare for the progress report and presentation

08/02/02-15/03/02 Implementation of the whole system and supported functions.

01/03/02-31/03/02 Test and debug the system thoroughly. And do the evaluation and documentation as a whole.

15/03/02-15/04/02 Prepare to submit the final report and poster. 15/04/02-15/05/02 Prepare for the presentation and the demonstration

(9)

The Hong Kong University of Science & Technology ₇ Department of Computer Science and Engineering

4 References

Books

Prentice Hall (1999). The Core PHP Programming. Leon Atkinson (450pages)

McGraw-Hill (1999).Database Management Systems. Raghu Ramkrishnan and Johannes Gehrke.

Websites

internet.com Corp. (No date). phpbuilder.net [Online]. Available: http://www.phpbuilder.net [2001, Sep 22].

MySQL. (No date). mysql.com [Online]. Available: http://www.mysql.net [2001, Sep 22].

SourceForge.net (No date). sourceforge.net/projects/phpmyadmin [Online] Available:

http://sourceforge.net/projects/phpmyadmin [2002, Jan 28]

Software

MySQL AB Company. (2001). MySQL 3.23, Stable Edition. Available: http://www.mysql.com [2001, Sep, 22].

(10)

The Hong Kong University of Science & Technology ₈ Department of Computer Science and Engineering

5 Appendices

Appendix A — Comparison of Different Database Management Systems (DBMS) Area MySQL Oracle9i PostgreSQL Sybase

Overall

Free Download √ √ √

Free Source √ √

Online Documentation √ √ √ √

License Type Free for

Internal Use Commercial

Free & Commercial Free Unsupported Linux Version Features Client-server √ √ √ √ Simultaneous Connections 101 92 64 25 Multi-threaded √ √ √ Shared SQL Cache √ Row-level Locking √ Backup Utility √ √ √ √ Recovery Utility √ √ √ Parallel Query √ √ Read-only Databases √ √

Multiple Index Type

(B-Tree, hash, etc.) √ √ √

Unique Indexes √ √ √ Multi-column Indexes √ √ √ √ Standards ANSI/ISO SQL92 entry-level √ √ Partial √ ODBC √ √ √ √

Supported Programming Languages

C √ √ √ √ C++ √ √ √ √ Java √ √ √ √ Perl 4 √ √ √ √ Perl 5 OO √ √ √ √ Python √ √ √ Tcl √ √

(11)

The Hong Kong University of Science & Technology ₉ Department of Computer Science and Engineering

Appendix B — Application Description

Logon System

Because the system needs to handle private information of member, staff and instructor, a secure network logon system is necessary. In order to use the Course Enrollment and Administrative System, all users should enter a correct password matched with the login id that is stored in the database. There are four groups of users who will logon to the system:

Member: Persons who are members of the YMCA.

Instructor: Persons who teach a course or hold a function that is offered by the YMCA. General Staff: Persons who work in the center of the YMCA.

Admin Staff: Persons who have permission to do the administration work in the YMCA.

The system only displays functions for the user that are available under his groups after a successful user logon.

Activity Information Management

There are many activities offered by the YMCA. The activities are either classified by Course or Function. All the activities’ information, such as activity id, period of the activity, quota of the activity, etc, should be maintained accurately and efficiently by the user, such that the member can enroll in the activity and the activity is held successfully. Using this function a user can:

Insert a new activity record and its offering record

Make changes to an activity record and its offering record

Insert and modify on several relevant records for the activity, which includes the instructor, the reason of cancelled lesson and remarks of the activity

Delete an activity record and its relevant records about this activity Backup activity information in the database

Search and browse the record information of a given activity Personal Information Management

There are four groups of users in our system as we have mentioned above. All users’ personal information will be stored in the database, such as unique id for each person, home address of the user, date of employment for each staff, etc. Using this function a user can:

Insert a new user personal record

Insert a new record of the personal strength of an instructor Make changes to the personal information record

Make changes to the personal strength of an instructor record

Search and browse the records of the enquiry information about a user Backup personal information in the database

Enrollment Information Management

After there is a new activity offered by the YMCA, all members can enroll in this activity in the center. The member can do the enrollment of the activity through the system in any one of the

(12)

The Hong Kong University of Science & Technology ₁₀ Department of Computer Science and Engineering YMCA centers. The new enrollment record includes the information of the enrolled activity, the information of the member who enroll in the activity, the staff who does this enrollment etc. Using this function a user can:

Insert a new enrollment record

Make changes to the enrollment record

Search and browse the records of the enrollment of each activity Backup enrollment records in the database

Attendance Information Management

After the instructor finishes taking the attendance of the members in an offering, the Help Desk is responsible to enter this attendance information and the instructor attendance information into the database through our system. Using this function a user can:

Insert new member/instructor attendance records of an activity offering Delete an attendance record of a member/instructor from an activity offering

Search and browse the member/instructor attendance records of an activity offering Backup attendance records in the database

Waiting list Information Management

There is a quota for all activities offered by the YMCA. When a new activity is full, the member who wants to enroll in this activity needs to wait until there is a free space for the activity. The waiting list information also shows how many members are interested in the activity. Using this function a user can:

Insert a new wait record for an activity Delete the wait record for an activity

(13)

The Hong Kong University of Science & Technology ₁₁ Department of Computer Science and Engineering

Appendix C — Data Description

The Chinese YMCA of Hong Kong, the sponsor of our final year project, maintains a huge amount of information on members, staff and activities that it offers, all of which will be computerized and managed by a relational DBMS. The Course Enrollment and Administrative System will gather all this information and make use of it to enable the staff to register members for the activities through the system. The database of the system is divided into two major parts, which are People and

Activity.

People Information Person

The Person class contains all the common personal information about people (both members and non-members) in the YMCA. Its attributes include chineseName (i.e., Chinese name), englishName (i.e., English name), address (i.e., home address), telephone (i.e., telephone number), email, dob (date of birth), sex and passport (i.e., HKID or passport number).

Member

There are three subclasses under the type Member, which are GeneralMember, Staff and Instructor. The class Member contains attributes referring to membership details. The memberID (i.e., Member id) is the key. Other attributes include memberPassword, title, type (i.e., individual, family or organization), date (i.e., date membership begins), membershipFee (i.e., membership fee), receiptNumber, branch (i.e. YMCA branch name where the person finished the membership), church, hall, educationLevel (i.e., education level) and occupation.

NonMember

It is not necessary for an instructor of the activities to be a member of the YMCA. All these non-member instructors are grouped into the NonMember class.

Instructor

The instructor, who can be a member or non-member of the YMCA, is responsible for teaching courses or holding functions. This class includes instructorID as the key, inst_password and employDate (i.e., starting date of employment).

GeneralMember

The class GeneralMember refers to the ordinary members who have joined the YMCA and have the right to share the members’ benefits including joining the YMCA activities.

Staff

According to the rules of the Chinese YMCA of Hong Kong, all the staff must also be members of the YMCA. Each staff will be assigned a staffID as the key in the class. Staff also includes a staffPassword.

JobTitle

(14)

The Hong Kong University of Science & Technology ₁₂ Department of Computer Science and Engineering postID (i.e., Staff position) as the key and title (i.e., job title). Each staff has an association with a job_title to find out what is the staff’s position. Each staff has exactly one job title in the YMCA. Ability

The class Ability includes the attribute ability_id, as the key, and description to describe the ability. Each instructor has at least one ability.

Group

The class Group includes the attribute groupID as the key and description to describe the group. Each staff is assigned to one or more groups and one group includes at least one staff.

Permission

The class Permission includes the attribute permissionID as the key and description to describe the permission. Each group has a set of permissions and a permission can be included in many groups.

Centre

This class stores the information of each YMCA center. The attributes include centreID, which is the key, centreName, which refers to the name of the centre, centrAddress, which refers to the address of centre and phone, which is the telephone number of the centre.

Activity Information Activity

There are altogether two main kinds of activities involved in the system, namely Course and

Function. Course refers to interest courses, such as swimming course and homepage design

course. Function refers to functions held by the YMCA, such as wild camp, firm visit, etc. The class Activity includes attributes activityID as the key, description (i.e., activity description), requirement (i.e., the requirement the member needs for the activity) and category (i.e., the category of the activity). An activity is either a function or a course and the staff who is authorized to do so must create each activity and each authorized staff can create many activities.

Offering

The class Offering includes two kinds of offerings: function_offering and course_offering. Many offering details are stored in Offering whose attributes include startTime, endTime, fee, payType, payAmount, enrolled and quota. The startTime and endTime are used to indicate the time range that the offering is held. The attribute fee indicates the tuition fee of the offering. The attribute payType is used to identify whether the instructor salary is calculated by a ratio of course fee, when its value is “ratio”, or by hourly pay, when its value is “hourly”. The payAmount is a ratio value when the payType value is “ratio” and the hourly payment when the payType value is “hourly”. The attribute enrolled is used to count how many members have already enrolled in the offering and quota refers to the offering quota.

CourseOffering

(15)

The Hong Kong University of Science & Technology ₁₃ Department of Computer Science and Engineering course. The CourseOffering attributes include courseOfferingID, which is the key of this class. A course is either regular or irregular.

FunctionOffering

Each function can have many offerings, but each function offering must be offered by exactly one function. The FunctionOffering attributes include functionOfferingID, which is the key, and date. The date is the date that the function is held.

Regular

The course’s start and end dates and meeting days will be stored in this class if the course has regular lessons, for example, a course that will have lessons on every Monday within a certain period. The attributes startDate and endDate are used to indicate the period of the lesson. There are seven attributes, which are mon, tue, wed, thu, fri, sat, and sun, that are used to indicate the weekday that the lesson will be held.

Irregular

The course’s meeting date will be stored in this class if the course has lessons irregularly. The class has only one attribute, date, to indicate the date the lesson will have a meeting.

NoLesson

This class indicates if there is any lesson that is cancelled. Its attributes include date, description and eventType. The date refers to the date on which the lesson is cancelled. The description indicates the reason for the cancellation. The reason for no lesson on that day is classified to one of a type using a number determined by the YMCA and is stored as eventType.

Remark

This class keeps track of the courses or functions that are not operating according to their schedule, such as a change in venue for a particular lesson. Its attributes include date and description. The date refers to the date on which the activity offering was changed and the description refers to the reason for the change. Each Offering class can have no remark or it may have one or more than one remark.

Venue

The information related to the venue will be stored in this class. The attributes include venueID, description, capacity and location. The venueID is the primary key. The description describes the venue. The capacity refers to the capacity of the venue. The location describes the physical location of the venue. Each offering is held in a venue. A venue can be used for many offerings if the capacity is more then the quota of the offering and suitable for that offering’s requirement. Association classes

Enroll

When a member enrolls in an activity, their enrollment will be added to the Enroll association class related to the activity. The attributes of Enroll include date, time, receiptNo and deposit. The date and time indicate the date and time the enrollment is made. receiptNo refers to the receipt number

(16)

The Hong Kong University of Science & Technology ₁₄ Department of Computer Science and Engineering and deposit refers to the deposit that the member has given for the activity. There is a ternary relationship between classes Member, Activity and Staff so that the staff, who is responsible for the particular enrollment is known. Each member can enroll in more then one activity and also each activity can be enrolled in by more then one member up to the capacity of the course venue. Wait

If the activity offering to be enrolled in is full, the request information will be added to the Wait association class, which has attributes position and wait. The position refers to the waiting position on the list and wait is a Boolean, which indicates whether or not the member would like to wait for the activity. If the member does not want to wait for the course or function, this information will be used for research. Each member can wait in many activity’s offerings and also each activity’s offering can be waited for by many members.

StudentAttend

This association class shows the attendance in a course offering or function offering of a student. It has attribute date, which refers to the date the activity was held.

InstructorAttend

This association class shows the attendance in a course or function of the instructor. It has attribute date, which refers to the date the activity was held.

(17)

The Hong Kong University of Science & Technology ₁₅ Department of Computer Science and Engineering

(18)

(19)

(20)

The Hong Kong University of Science & Technology ₁₈ Department of Computer Science and Engineering

Course Enrollment and Administrative System — Association Classes

Enroll

date : type = date

time : type = time

receiptNo : type = char

deposit : type = float

InstructorAttend

date : type = date

StudentAttend

date : type = date

Wait

position : type = integer

wait : type = boolean

(21)

The Hong Kong University of Science & Technology ₁₉ Department of Computer Science and Engineering

Appendix E — Logical Database Design Ability

Attribute Description Type

ability_id Ability id char (5) description Ability description char (50)

Primary key: ability_id Foreign key: None Activity

activity_id Activity id char (10) description Activity description char (50) requirement Activity requirement char (50) activity_type Identifies whether the activity

is a course or a function

char (1)

category Activity category char (10)

Primary key: activity_id Foreign key: None Centre

centre_id Centre id char (5) centre_name Centre name char (20) centre_address Centre address char (50) phone Event description char (8)

Primary key: centre_id Foreign key: None Course_offering

course_offering_id Offering id char (10) activity_id Corresponding activity char (10)

start_time Start time datetime

end_time End time datetime

fee Course fee float

pay_type Payment type of the instructor is either a ratio of the course fee or hourly pay

float

pay_amount Amount paid for the instructor float

venue_id Venue id char (5)

is_regular Is a regular course or not boolean

quota Course quota integer

enrolled Number enrolled integer

Primary key: course_offering_id

Foreign key: activity_id (Activity: activity_id)

(22)

The Hong Kong University of Science & Technology ₂₀ Department of Computer Science and Engineering

Enroll

member_id Member id char (15) offering_id Offering id char (10) activity_id Corresponding activity id char (10)

staff_id Staff id who does the enrollment

char (15)

receipt_no Receipt number char (10)

date Enrollment date date

time Enrollment time datetime

deposit Deposit paid for the course float

Primary key: member_id and offering_id

Foreign key: offering_id (Course_offering: course_offering_id or

Function_offering: function_offering_id) activity_id (Activity: activity_id)

staff_id (Staff: staff_id)

member_id (Member: member_id)

Function_offering

function_offering_id Offering id char (10) activity_id Corresponding activity char (10)

fee Function fee float

pay_type Payment type of the instructor is either a ratio of the course fee or hourly pay

float

pay_amount Amount paid for the instructor float

venue_id Venue char (5)

date Date date start_time Start time datetime

end_time End time datetime

enrolled Number of enrollment integer

quota Course quota integer

Primary key: function_offering_id

Foreign key: activity_id (Activity: activity_id)

venue_id (Venue: venue_id)

Group

group_id Group id char (5)

description Group description char (50)

Primary key: group_id Foreign key: None

(23)

The Hong Kong University of Science & Technology ₂₁ Department of Computer Science and Engineering

Instructor

instructor_id Instructor id char (15) instructor_password Instructor password char (8)

employ_date Employment since date member_id Member id char (15)

Primary key: instructor_id

Foreign key: member_id (Member: member_id) Instructor_ability

instructor_id Instructor id char (15) ability_id Ability id char (5)

Primary key: ability_id, instructor_id Foreign key: ability_id (Ability ability_id)

instructor_id (Instructor: instructor_id)

Instructor_attend

instructor_id Instructor id char () offering_id Offering id char () activity_id Corresponding activity id char (10)

date Date date

Primary key: instructor_id and offering_id

Foreign key: instructor_id (Instructor: instructor_id)

offering_id (Course_offering: course_offering_id or Function_offering: function_offering_id)

activity_id (Activity: activity_id)

Irregular

course_offering_id Offering id char (10)

date Date date

Foreign key: course_offering_id (Course_offering: course_offering_id) Job_title

post_id Position id char (5)

title Job title char (20)

Primary key: post_id Foreign key: None

(24)

The Hong Kong University of Science & Technology ₂₂ Department of Computer Science and Engineering

Member

member_id Member id char (15) member_password Member password char (8)

title Mr/Ms/Mrs/Priest char (5)

type Membership type

1 for individual; 2 for family; 3 for organization

char (1)

chinese_name Chinese name char (8) english_name English name char (20) address Home address char (50) telephone Telephone number char (8)

email Email address char (20)

dob Date of birth date sex Sex (M or F) char (1)

passport Passport number char (10)

date Membership since date

membership_fee Membership fee integer receipt_number Receipt number char (10)

branch YMCA branch char (10)

church Name of church char (10) hall Name of hall char (10) education_level Education level char (10) occupation Occupation name char (20)

Primary key: member_id Foreign key: None Non_member

instructor_id Instructor id char (15) chinese_name Chinese name char (8) english_name English name char (20) address Home address char (50) telephone Telephone number char (8)

email Email address char (20)

dob Date of birth date sex Sex (M or F) char (1)

passport Passport number char (10)

Primary key: instructor_id

(25)

The Hong Kong University of Science & Technology ₂₃ Department of Computer Science and Engineering

No_lesson

date Date date description Lesson description char (50) event_type Reason for no lesson char (1)

Primary: course_offering_id and date

Foreign: course_offering_id (Course_offering: course_offer_id)

Permission

permission_id Permission id char (5) description Permission description char (50)

Primary key: permission_id Foreign key: None

Permission_group

permission_id Permission id char (5)

Primary key: group_id, permission_id Foreign key: group_id (Group: group_id)

permission_id (Permission: permission_id)

Regular

start_date Start date date

end_date End date date

mon Lesson on Monday boolean tue Lesson on Tuesday boolean wed Lesson on Wednesday boolean thu Lesson on Thursday boolean fri Lesson on Friday boolean

sat Lesson on Saturday boolean

sun Lesson on Sunday boolean

(26)

The Hong Kong University of Science & Technology ₂₄ Department of Computer Science and Engineering

Remark

offering_id Offering id char (10) activity_id Corresponding activity id char (10)

date Date date description Event description char (50)

Primary key: offering_id

Function_offering: function_offering_id) activity_id (Activity: activity_id)

Staff

staff_id Staff id char (15)

staff_password Staff password char (8) centre_id Work centre char (5) post_id Staff position char (5) member_id Member id char (15)

Primary key: staff_id

Foreign key: member_id (Member: member_id)

centre_id (Centre: centre_id) post_id (Job_title: post_id)

Staff_group

staff_id Staff id char (15)

Primary key: staff_id, group_id Foreign key: staff_id (Staff: staff_id)

group_id (Group: group_id)

Student_attend

member_id Member id char (15) offering_id Offering id char () activity_id Corresponding activity id char (10)

date Date date

(27)

The Hong Kong University of Science & Technology ₂₅ Department of Computer Science and Engineering

Teach_or_hold

Attribute Description Type

instructor_id Instructor id char (15) offering_id Course or function offering id char (10)

Primary key: instructor_id, offering_id

Function_offering: function_offering_id) instructor_id (Instructor: instructor_id)

Venue

venue_id Venue id char (5)

capacity Capacity of the venue number

description Venue description char (50) location Venue location char (50)

Primary key: venue_id Foreign key: None Wait

member_id Member id char ()

offering_id Offering id char () activity_id Corresponding activity id char (10)

wait Wait or not boolean

position Waiting position integer

(28)

The Hong Kong University of Science & Technology ₂₆ Department of Computer Science and Engineering

(29)

The Hong Kong University of Science & Technology ₂₇ Department of Computer Science and Engineering

1.1 Actors

Actors are classes that define roles that objects external to a system may play. They are used to model users outside of a system that interact directly with the system as part of coherent work units. This includes human users and other systems. In this project, the following actors are identified.

1.1.1 Admin Staff

An admin staff is a person who is authorized to do the administration work in a YMCA centre. The admin staff uses the system to maintain the curriculum information inclusive of members, instructor and activity and to set the permissions and groups for the YMCA staff.

1.1.2 Instructor

An instructor is employed by the YMCA and is responsible to teach courses or hold functions offered by the YMCA.

1.1.3 Help Desk Staff

Help desk staff are the staff who work in a YMCA centre. A help desk staff uses the system to help people to register to be members of the YMCA, help members to register for activities and view the member and activity information.

1.1.4 Member

A member has the right to share the member benefits including joining offered courses and functions. A member uses the system to register for an activity, change his own personal information and view activity details.

(30)

The Hong Kong University of Science & Technology ₂₈ Department of Computer Science and Engineering

1.2 Use Cases

A use case model is made up of actors and use cases. A use case is a piece of system functionality that gives a user a result of value. In other words, a use case model is a representation of the complete functionality of a system.

1.2.1 Use Case: Maintain Activity Information Brief Description

This use case describes how the information of an activity is maintained. It provides the capability of adding, viewing, modifying and deleting this information.

Use-case Diagram

Basic Flow

1. The use case begins when the user chooses to maintain activity information.

{Select Option}

2. The system prompts the user to select an option from: ADD, VIEW, MODIFY or DELETE. 3. If the ADD option is selected

3.1. Perform subflow Add New Activity.

4. If the VIEW option is selected

4.1. Perform subflow Display Activity Information.

5. If the MODIFY option is selected

5.1. Perform subflow Modify Activity Information.

6. If the DELETE option is selected

6.1. Perform subflow Display Activity Information.

6.2. The user selects an activity to delete.

{Confirm Activity Deletion}

6.3. The user confirms the deletion.

{Delete Activity Information}

6.4. The system removes the activity information from the database. 7. The use case ends.

(31)

The Hong Kong University of Science & Technology ₂₉ Department of Computer Science and Engineering Subflows

S1: Add New Activity

1. The system prompts the user to enter a new activity ID.

2. The system loads a blank activity information form onto the screen.

{Enter New Information}

3. The user fills in the required information.

{Confirm Activity Addition}

4. The user confirms the entered information.

5. The system checks the correctness of the format for the entered information.

{Save New Information}

6. The system generates a new activity ID for this activity and saves the information in the database.

S2: Display Activity Information

{Identify Activity}

1. The system prompts the user to enter an activity ID or select the characteristics of the activity.

{Retrieve Activity}

2. The system retrieves and displays a list of activity information matching the criteria. S3: Modify Activity Information

1. Perform subflow Display Activity Information.

2. The user selects an activity to modify.

{Enter Modified Information}

3. The user modifies the activity information.

{Confirm Activity Modification}

4. The user confirms the changes.

5. The system checks the correctness of the format of the modified information.

{Save Modified Information}

(32)

The Hong Kong University of Science & Technology ₃₀ Department of Computer Science and Engineering Alternate Paths

A1: Confirm Action

At any point between {Select Option} and {Confirm Activity Addition} or between {Select

Option} and {Confirm Activity Modification} or between {Select Option} and {Confirm Activity Deletion} if the user cancels the process,

1. The use case ends. A2: Handle Invalid Activity Data

At {Save New Information} or {Save Modified Information} if the entered data is invalid, 1. The system informs the user which data is invalid.

2. The flow of events is resumed at {Enter New Information} or {Enter Modified

Information}, respectively.

A3: Handle Invalid Activity ID

At {Retrieve Activity} if an invalid activity ID is entered, 1. The system informs the user that the activity ID is invalid. 2. The flow of events is resumed at {Identify Activity}. A4: Handle Activity With Enrollment

At {Delete Activity Information} if the activity has someone enrolled in it, 1. The system displays a warning message to the user.

(33)

The Hong Kong University of Science & Technology ₃₁ Department of Computer Science and Engineering 1.2.2 Use Case: Maintain Offering Information

Brief Description

This use case describes how the information of offerings is maintained. It provides the capability of adding, modifying and deleting this information.

Basic Flow

1. The use case begins when the user chooses to maintain offering information.

{Select Option}

2. The system prompts the user to select an option from: ADD, VIEW, MODIFY or DELETE. 3. If the ADD option is selected

3.1. Perform subflow Add New Offering.

4. If the VIEW option is selected

4.1. Perform subflow Display Offering Information.

5. If the MODIFY option is selected

5.1. Perform subflow Modify Offering Information. 6. If the DELETE option is selected

6.1. Perform subflow Display Offering Information.

6.2. The user selects an offering to delete.

{Confirm Offering Deletion}

6.3. The user confirms the deletion. (A2) (A5)

{Delete Offering Information}

6.4. The system removes the offering information from the database. 7. The use case ends.

(34)

The Hong Kong University of Science & Technology ₃₂ Department of Computer Science and Engineering Subflows

S1: Add New Offering

1. The system prompts the user to enter a new offering ID.

2. The system loads a blank offering information form onto the screen.

{Enter New Information}

3. The user fills in the required information.

{Confirm Offering Information}

4. The user confirms the entered information.

5. The system checks the correctness of the entered information.

{Save New Information}

6. The system generates a new offering ID for this offering and saves the information in the database.

S2: Display Offering Information

{Identify Offering}

1. The system prompts the user to enter an offering ID or select the characteristics of the offering.

{Retrieve Offering}

2. The system retrieves and displays a list of offering information matching the criteria. S3: Modify Offering Information

1. Perform subflow Display Offering Information.

2. The user selects an offering to modify.

{Enter Modified Information}

3. The user modifies the offering information.

{Confirm Offering Modification}

4. The user confirms the changes.

5. The system checks the correctness of the modified information.

{Save Modified Information}

(35)

The Hong Kong University of Science & Technology ₃₃ Department of Computer Science and Engineering Alternate Paths

A1: Confirm Action

At any point between {Select Option} and {Confirm Offering Addition} or between {Select

Option} and {Confirm Offering Modification} or between {Select Option} and {Confirm Offering Deletion} if the user cancels the process,

1. The use case ends.

A2: Handle Invalid Offering Data

At {Save New Information} or {Save Modified Information} if the entered data is invalid, 1. The system informs the user which data is invalid.

2. The flow of events is resumed at {Enter New Information} or {Enter Modified

Information}, respectively.

A3: Handle Invalid Offering ID

At {Retrieve Offering } if an invalid offering ID is entered, 1. The system informs the user that the offering ID is invalid. 2. The flow of events is resumed at {Identify Offering}. A4: Handle Offering With Enrollment

At {Delete Offering Information} if the offering has someone enrolled in it, 1. The system displays a warning message to the user.

(36)

The Hong Kong University of Science & Technology ₃₄ Department of Computer Science and Engineering 1.2.3 Use Case: Maintain Instructor Information

This use case describes how instructor information is maintained. It provides the capability of adding, modifying, deleting and reviewing information for an instructor.

Basic Flow

1. The use case begins when the user chooses the option – Maintain instructor information. 2. The system prompts the user to select an option from: ADD and VIEW.

3. If the ADD option is chosen, the S1: Add a new instructor subflow is performed.

4. If the VIEW option is chosen, the S1: View instructor information subflow is performed.

5. The user repeats one of the available activities until he/she indicates that no more activity will be taken.

6. The use case ends. Subflows

S1: Add a new instructor

1. The system loads a blank instructor information form onto the screen.

2. The user fills in the corresponding instructor information inclusive of his instructor ID. 3. The user confirms the entered information. (A2)

4. The system checks the correctness of the format for the entered information. (A3) 5. The system saves the information in the database.

6. The system notifies the user that the request has been processed.

7. The system repeats this process from the beginning of this subflow until the user indicates that this activity is complete.

S2: View instructor information

1. The system prompts the user to enter an instructor ID or select the category of instructor. 2. The system retrieves and displays a list of instructor information the user requests. (A4) 3. If the MODIFY option is chosen, the S2.1: Modify instructor information subflow is

performed.

4. If the DELETE option is chosen, the S2.2: Delete an instructor record subflow is performed. 5. The system repeats this process from the beginning of this subflow until the user indicates

(37)

The Hong Kong University of Science & Technology ₃₅ Department of Computer Science and Engineering S2.1 Modify instructor information

1. The system retrieves and displays the instructor information the user requests. 2. The user modifies the instructor information.

3. The user confirms the changes he makes. (A2)

4. The system checks the correctness of the format of the modified information. (A3) 5. The system saves the changes in the database.

6. The system notifies the user that the change has been made.

S2.2 Delete an instructor record

1. The user confirms the deletion. (A2) (A5)

2. The system removes the instructor information from the database. (A5)

Alternate Paths

A2. The user can cancel the process at any time before he confirms the action.

A3. If the entered information has an invalid format, the user is prompted to re-enter it again or terminate the use case.

A4. If an invalid instructor ID, such as a non-existent instructor, is entered, the system will prompt the user to correct it or terminate the use case.

A5. If the instructor whose information is to be deleted has taught any activity in YMCA, the system will reject the deletion and will prompt the user to enter another instructor ID again or terminate the use case.

(38)

The Hong Kong University of Science & Technology ₃₆ Department of Computer Science and Engineering 1.2.4 Use Case: Maintain Staff Information

This use case describes how staff information is maintained. It provides the capability of adding, modifying, deleting and reviewing information for a staff.

Basic Flow

1. The use case begins when the user chooses the option – Maintain staff information. 2. The system prompts the user to select an option from: ADD and VIEW.

3. If the ADD option is chosen, the S1: Add a new staff subflow is performed. 4. If the VIEW option is chosen, the S4: Review a staff subflow is performed.

S1: Add a new staff

1. The system loads a blank staff information form onto the screen.

2. The user fills in the corresponding staff information inclusive of his staff ID. 3. The user confirms the entered information. (A2)

4. The system checks the correctness of the format for the entered information. (A3) 5. The system saves the information in the database.

6. The system notifies the user that the request has been processed.

S2: Review a staff

1. The system prompts the user to enter a staff ID or his characteristics.

2. The system retrieves and displays the staff information that the user requests. (A4) 3. The system displays the staff details that the user requests.

4. If the MODIFY option is chosen, the S2: Modify a staff’s information subflow is performed. 5. If the DELETE option is chosen, the S3: Delete a staff record subflow is performed.

(39)

The Hong Kong University of Science & Technology ₃₇ Department of Computer Science and Engineering S2.1: Modify a staff’s information

1. The system retrieves and displays the staff information the user requests. (A4) 2. The user modifies the staff information.

4. The system checks the correctness of the format of the modified information. (A3) 5. The system saves the changes in the database.

S2.2: Delete a staff record

1. The user confirms the deletion. (A2)

2. The system removes the staff information from the database. 3. The system notifies the user that the request has been processed.

Alternate Paths

A4. If an invalid staff ID, such as a non-existent staff ID, is entered, the system will prompt the user to correct it or terminate the use case.

(40)

The Hong Kong University of Science & Technology ₃₈ Department of Computer Science and Engineering 1.2.5 Use Case: Maintain Member Information

This use case describes how member information is maintained. It provides the capability of modifying, deleting and reviewing information for a member.

Basic Flow

1. The use case starts when the user chooses the option -- Maintain member information. 2. The system prompts the user to choose a desired option from: VIEW.

3. If the REVIEW option is chosen, the S3: View a member subflow is performed.

S1: View a member

1. The system prompts the user to enter a member ID.

2. The system displays the member details that the user requests. (A3)

3. If the MODIFY option is chosen, the S1: Modify a member’s information subflow is performed.

4. If the DELETE option is chosen, the S2: Delete a member subflow is performed.

5. The system repeats this process from the first step of this subflow until the user indicates that this activity is complete.

S1.1: Modify a member’s information

1. The system retrieves and displays the member information the user requests. (A3) 2. The user modifies the member information.

4. The system checks the correctness of the format for the modified information. (A4) 5. The system saves the changes in the database.

7. The system repeats this subflow from the beginning until the user indicates that this activity is complete.

(41)

The Hong Kong University of Science & Technology ₃₉ Department of Computer Science and Engineering S1.2: Delete a member

1. The user confirms the deletion. (A2) (A5)

2. The system removes the member information from the database. 3. The system notifies the user that the request has been processed.

4. The system repeats this subflow from the beginning until the user indicates that this activity is complete.

Alternate Paths

A2. The user can cancel the process at any time before confirming the action.

A3. If an invalid member ID, such as a non-existent member ID, is entered, the system will prompt the user to correct it or terminate the use case.

A5. If the member whose information is to be deleted will take an activity in the YMCA later, the system will reject the deletion and will prompt the user to enter another member ID again or terminate the use case.

(42)

The Hong Kong University of Science & Technology ₄₀ Department of Computer Science and Engineering 1.2.6 Use Case: Maintain Group Information

This use case describes how the information of groups is maintained. It provides the capability of adding, and modifying this information.

Basic Flow

1. The use case begins when the user chooses the option – Maintain staff information.

2. The user enters the staff ID or the characteristic of the instructor to view the staff information. 3. The user selects the staff for whom he wants to set the group.

4. The user clicks the Modify button.

5. The user retrieves the staff and group information from the database and shows it on the screen.

6. The user clicks the displayed groups to assign the selected staff to clicked group. 7. The user un-clicks a displayed group(s) to remove the selected staff from this group. 8. The user confirms the modification. (A2)

9. The system saves the information in the database.

10. The system notifies the user that the request has been processed. 11. The use case ends.

Alternate Paths

(43)

The Hong Kong University of Science & Technology ₄₁ Department of Computer Science and Engineering 1.2.7 Use Case: Maintain Member Attendance

This use case describes how the information of member attendance is maintained. It provides the capability of adding, deleting and reviewing this information.

Basic Flow

1. The use case begins when the user chooses the option – Take Attendance 2. The system prompts the user to enter the offering details. (A3)

3. The user clicks the Browse button and the screen displays a list of member names and the date of offerings.

4. The user clicks the check box if the member attended the selected offering in the corresponding date.

5. The user clicks the Submit button to confirm the attendance record. (A2)

6. The user repeats this activity until he/she indicates that no more activity will be taken. 7. The use case ends.

Alternate Paths

A3. If an invalid offering ID, such as a non-existent offering, is entered, the system will prompt the user to correct it or terminate the use case.

(44)

The Hong Kong University of Science & Technology ₄₂ Department of Computer Science and Engineering 1.2.8 Use Case: Maintain Instructor Attendance

This use case describes how the information of instructor attendance is maintained. It provides the capability of adding, deleting and reviewing this information.

Basic Flow

1. The use case begins when the user chooses the option – Take Attendance 2. The system prompts the user to enter the offering details. (A3)

3. The user clicks the Browse button and the screen displays a list of instructor names and the date of offerings.

4. The user clicks the check box if the instructor attended the selected offering on the corresponding date.

5. The user clicks the Submit button to confirm the attendance record. (A2)

6. The user repeats this activity until he/she indicates that no more activity will be taken. 7. The use case ends.

Alternate Paths

A3. If an invalid offering ID, such as a non-existent offering, is entered, the system will prompt the user to correct it or terminate the use case.