Member Directory:2011 Spring Home

From Openitware
Jump to: navigation, search



  • Most recent term: Spring 2011
  • Previous term: None

Project Description

The project description states the purpose of the project, introduces the project's sponsor, and summarizes in an abstract what problem the project solves and why the problem is important, what the project will accomplish and how it will do so, and what results are expected and what implications these results have. The role assumed by the developer that is responsible for the project description is System Analyst.


The purpose of this project is to simplify and improve the member directory database for the New Hampshire High Technology Council (NHHTC). We will be working with Matt Cookson and Judy Davidson from NHHTC on this project.

NHHTC Background

In the Spring of 1983 an innovative group of small high technology business owners, business development specialists, government officials and educators formed what is the New Hampshire High Technology Council.

NHHTC Mission

The New Hampshire High Technology Council's purpose is to bring together representatives from the private and public sectors to establish and maintain financial, technical, management, legislative and educational support programs that encourage innovative research and technology-based industrial development in New Hampshire.


Member Directory
Bethany Ross, Aaron Green, John Grossmith, Jonathan Schultz, Scott Callahan
Project Sponsor
New Hampshire High Technology Council


What problem are we solving and why is it important? 

The New Hampshire High Technology Council which brings together representatives from private and public sectors to support programs that encourage research and technology-based development currently has a ineffective database to store membership information.


What will the project accomplish? Concretely!

We will create a new fully, functioning database using MySQL which will allow directors at NHHTC to easily add, edit, delete, and query their member directory. This new database will prevent the errors that were currently being caused by the old database. We began by creating a normalization document and UML Class Diagram to help create the database design.


How do we go about solving the problem? What do we actually do to arrive at a solution and get the final results?

Member Directory's team members work collaboratively through our wiki site. We use version control to produce the SQL code. The SQL code includes creating database and tables, inserting sample data into database, and querying the tables.


What has the project accomplished?

The end result will be a database that will save NHHTC time and money. This will help all that work with NHHTC and members that use their resources. It will also remove the liability of holding credit card information. This was a major concern for the NHHTC and is a goal this group strove to meet.


What are the implications of the results that were obtained?


Team Members

Roles and Responsibilities

Note from Mihaela. The project is missing the following: conclusion section of the abstract; system requirements specification; conceptual model diagram with brief description and definitions of the conceptual model entities; description of the relational model diagram, definitions of the relational model entities with their attributes, and summary of the relational model relationships; brief descriptions of the parent-of and child-of dependency.

I'm assigning the missing elements as follows. Aaron writes the Database Analysis section. Bethany writes the System Requirements section. Jonathan writes descriptions of the relational model diagrams and the parent-of and child-of dependency diagrams. Scott writes the definitions of the relational model entities with their attributes and summary of the relational model relationships. John reviews and revises the project description. Everybody writes SQL queries, which means that everybody assumes the SQL Programmer role. Write your queries in a .sql file which you name with your last name. Place the file in the sql folder of your local copy of the repository, add the file to version control (Add command in TortoiseSVN), and commit (do not forget the log message!).

Aaron Green
Primary role: System Analyst
Secondary role: Team Leader
Responsibilities: System requirements, preparation of meeting agendas and minutes on google group/mediawiki
Bethany Ross
Primary role: SQL Programmer, System Analyst
Secondary role: Wiki Editor
Responsibilities: Project description, SQL script to populate the database with sample data, SQL queries, project wiki editing.
Scott Callahan
Primary role: DB Designer
Secondary role: SQL Programmer, SQL Reviewer
Responsibilities: Normalization in the database design, SQL code review, SQL queries.
John Grossmith
Primary role: SQL Programmer
Secondary role: IT Support Engineer
Responsibilities: SQL script to create the database tables and constraints, Google Code hosted project set-up, SQL queries.
Jonathan Schultz
Primary role: DB Analyst, SQL Programmer, SQL Reviewer
Secondary role: IT Support Engineer
Responsibilities: SQL script to populate the database with sample data, relational model diagram with input from Scott, parent-of UML dependency diagram, child-of UML dependency diagram, SQL code review, SQL queries.

Team Meetings

Team members meet during scheduled class to hold team meetings. Outside class, team members communicate using the team's forum at member-directory Google Group.

Meeting agendas and minutes are regularly uploaded to the Team Meetings page.

Work Products

System Requirements

This phase corresponds to specifying the functional and non-functional features that the prototype system will have. The work products of this phase are a list of functional requirements (what the system will do for the user) and a list of non-functional requirements (what constraints apply to the system with regard to the system license, infrastructure, and operation). The developers responsible for these work products assumes the role of System Analyst.

Current System

The system that is currently used is "broken" therefore the Member Directory team found it easier to start a whole new system

Functional Requirements

A relational database is to be designed to help NHHTC track, manipulate, and store data from their online database.

The database is required to hold information about the individual members and organization members.

It is required to have queries that can pull up desired information in lists, such as mailing lists, member information, committee information, organization information and specific data as required by the database managers.

The information must be easy to access and secure.

Use Cases

Non-Functional Requirements

The system will have a front-end which will allow the user to add new members, modify current members information, and delete members. This interface should be easy for the person entering data to use.

Database Analysis

This phase includes the conceptual modeling tasks. The work product of this phase is a description of the database conceptual model. The developers responsible for these tasks assume the role of DB Analyst.

Conceptual Model

Conceptual Model Diagram


Entity Definitions

MEMBER COMMITEE - A listing of each committee that any Member wishes to belong.

MEMBER - An organization or an individual whom has paid and is current with all NHHTC member fees.

CONTACT - A person that which represents either an Individual’s or an Organization’s behalf, and to whom NHHTC can contact regarding member services.

SECONDARY CONTACT - A person for whom a member contact has designated to be an additional point of contact for NHHTC.

INDIVIDUAL MEMBER - A type of Member that wishes to have himself or herself (and not the organization to which he/she works) affiliated with NHHTC services.

ORGANIZATION MEMBER - A non-profit or for-profit institution that has a desire to belong on the NHHTC directory.

ORGANIZATION TYPE - A characterization of an Organization, e.g., telecommunications, agriculture, legal, education, etc..

SALES LEVEL - The amount, in dollars, of annual sales, indicated by levels of less than $1 million, less than $5 million, less than $10 million, and greater than $10 million.

COMMITTEE - The names of available NHHTC committees.

Database Design

This phase corresponds to the logical modeling of the database. The work products of this phase are: detailed description of the database entities, attributes, relationships, and constraints; diagrams for the database model and dependency graphs that show the order in which tables are created and deleted; justification of the normalization forms that apply to the database design. The developers responsible for these tasks assume the role of DB Designer.


This phase corresponds to the physical modeling of the database. The work products of this phase are SQL scripts that create the database, populate it with sample data, and query the database. The developers responsible for these tasks assume the role of SQL Programmer.

Code Base

SQL scripts and design diagrams are stored on the project's central repository on the Google Code site at member-directory.

Query Examples

Primary contact data for all members

Author: Jonathan Schultz

List first name and last name of primary contacts for all organization members. Order output by last name of contact in ascending order. Show the organization member's name, sales level, number of employees, and type of organization.

| firstname | lastname | membertype | saleslevelid | numberofemployees | typeid |
| Fred      | Berry    | Small      |            4 |               150 |      4 |
| Tom       | Brady    | Small      |            1 |                50 |      1 |
| Kevin     | Garrnet  | Small      |            7 |             50044 |      7 |
| Mike      | Jackson  | Big        |            9 |               550 |      9 |
| Jack      | Jones    | Big        |            3 |               250 |      3 |
| John      | Lennon   | Big        |           10 |               150 |     10 |
| Patrick   | Oneil    | Small      |            5 |              5000 |      5 |
| Jonathan  | Schultz  | Small      |            8 |                50 |      8 |
| John      | Smith    | Small      |            2 |               100 |      2 |
| Tim       | Thomas   | Big        |            6 |              4858 |      6 |

Committee Member Listing

Author: Bethany Ross

Lists first name, last name, and email of the primary contact along with the committee that the contact serves on. Orders output by Committee in ascending order.

| name                 | LastName | FirstName | email                 |
| Entrepreneur Forum   | Smith    | John      | |
| Entrepreneur Forum   | Schultz  | Jonathan  |   |
| Human Resources      | Thomas   | Tim       |            |
| Human Resources      | Jackson  | Mike      |      |
| Legislature          | Brady    | Tom       |      |
| Legislature          | Oneil    | Patrick   |  |
| Membership           | Lennon   | John      |       |
| Product of the Year  | Garrnet  | Kevin     |    |
| Speed Venture Summit | Jones    | Jack      |     |
| SwIX                 | Berry    | Fred      |        |

Contact Name and Organization Type

Author: Bethany Ross

Lists first name and last name of the primary contact along with the Organization Type that the contact is part of. Orders output by Organization Type in ascending order.

| LastName | FirstName | Name         |
| Brady    | Tom       | Accounting   |
| Berry    | Fred      | Banking      |
| Smith    | John      | Biotech      |
| Garrnet  | Kevin     | Education    |
| Oneil    | Patrick   | IT           |
| Jones    | Jack      | Legal        |
| Schultz  | Jonathan  | Marketing    |
| Thomas   | Tim       | Other        |
| Jackson  | Mike      | Social Media |
| Lennon   | John      | Software     |

Contact Employment Status

Author: Bethany Ross

Lists first name, last name, email and website along with the employment status of the contact. Orders output by last name in ascending order.

| LastName | FirstName | Email                 | Website          | EmploymentStatus |
| Berry    | Fred      |        |   | Employed         |
| Brady    | Tom       |      |     | Employed         |
| Garrnet  | Kevin     |    |      | Employed         |
| Jackson  | Mike      |      |  | Employed         |
| Jones    | Jack      |     |     | Unemployed       |
| Lennon   | John      |       |      | Unemployed       |
| Oneil    | Patrick   |  |     | Employed         |
| Schultz  | Jonathan  |   |          | Employed         |
| Smith    | John      | | | Unemployed       |
| Thomas   | Tim       |            |       | Employed         |

Expiration Date Renewal Warnings For All Members

Author: Scott Callahan

This query specifies that NHHTC wants to see specific contact details, namely the last name, first name, email, and phone listing of any member that is due in 3 months for a renewal of their membership. It's ordered by a member's last name in ascending order.

| LastName | FirstName | Email                | Phone Listing  | Website      |
| Oneil    | Patrick   | | 603-555-9874x  | |

Contact Information for Small Members

Author: John Grossmith

This query lists the First Name and Last Name of the members who is considered Small.

| FirstName | LastName | MemberSize |
| Fred      | Berry    | Small      |
| Tom       | Brady    | Small      |
| Kevin     | Garrnet  | Small      |
| Patrick   | Oneil    | Small      |
| Jonathan  | Schultz  | Small      |
| John      | Smith    | Small      |

Contact Information for Big Members

Author: John Grossmith

This query lists the First Name and Last Name of the members who is considered Big.

| FirstName | LastName | MemberSize |
| Mike      | Jackson  | Big        |
| Jack      | Jones    | Big        |
| John      | Lennon   | Big        |
| Tim       | Thomas   | Big        |

Email Information Members

Author: John Grossmith

This query lists all the emails of members as well as the websites associated with those members.

| LastName | FirstName | Email                 | Website          |
| Berry    | Fred      |        |   |
| Jones    | Jack      |     |     |
| Lennon   | John      |       |      |
| Smith    | John      | | |
| Schultz  | Jonathan  |   |          |
| Garrnet  | Kevin     |    |      |
| Jackson  | Mike      |      |  |
| Oneil    | Patrick   |  |     |
| Thomas   | Tim       |            |       |
| Brady    | Tom       |      |     |