Member Directory:2011 Spring Home

History

 * 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.

Purpose
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.

Sponsor Profile
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 Background

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.
 * NHHTC Mission

Abstract

 * Title: Member Directory


 * Authors: Bethany Ross, Aaron Green, John Grossmith, Jonathan Schultz, Scott Callahan


 * Project Sponsor: New Hampshire High Technology Council

Motivation
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 problem are we solving and why is it important? :

Objectives
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.
 * What will the project accomplish? Concretely!:

Methods
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.
 * How do we go about solving the problem? What do we actually do to arrive at a solution and get the final results?:

Results
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 has the project accomplished?:

Conclusion

 * What are the implications of the results that were obtained?:

=Activities=

Team Members

 * Bethany Ross
 * Aaron Green
 * John Grossmith
 * Jonathan Schultz
 * Scott Callahan

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''.

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.


 * Relational Model Diagram
 * Parent-of Dependency Diagram
 * Child-of Dependency Diagram
 * Normalization Document v1.0
 * Normalization Document v2.0

Implementation
''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.

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      | john@smithandsons.com |
 * Entrepreneur Forum  | Schultz  | Jonathan  | jonathan329@unh.edu   |
 * Human Resources     | Thomas   | Tim       | tim@bb.com            |
 * Human Resources     | Jackson  | Mike      | mike@jackson.com      |
 * Legislature         | Brady    | Tom       | tom@football.com      |
 * Legislature         | Oneil    | Patrick   | patrick@patriots.com  |
 * Membership          | Lennon   | John      | john@lennon.com       |
 * Product of the Year | Garrnet  | Kevin     | keving@celtics.com    |
 * Speed Venture Summit | Jones   | Jack      | jack@jonester.com     |
 * SwIX                | Berry    | Fred      | fred@berry.com        |

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      | fred@berry.com        | Fruitberry.com   | Employed         |
 * Brady   | Tom       | tom@football.com      | patriots.com     | Employed         |
 * Garrnet | Kevin     | keving@celtics.com    | celtics.com      | Employed         |
 * Jackson | Mike      | mike@jackson.com      | jacksonfive.com  | Employed         |
 * Jones   | Jack      | jack@jonester.com     | jonester.com     | Unemployed       |
 * Lennon  | John      | john@lennon.com       | beatles.com      | Unemployed       |
 * Oneil   | Patrick   | patrick@patriots.com  | patriots.com     | Employed         |
 * Schultz | Jonathan  | jonathan329@unh.edu   | unh.com          | Employed         |
 * Smith   | John      | john@smithandsons.com | smithandsons.com | Unemployed       |
 * Thomas  | Tim       | tim@bb.com            | bruins.com       | 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   | patrick@patriots.com | 603-555-9874x  | patriots.com |

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      | fred@berry.com        | Fruitberry.com   |
 * Jones   | Jack      | jack@jonester.com     | jonester.com     |
 * Lennon  | John      | john@lennon.com       | beatles.com      |
 * Smith   | John      | john@smithandsons.com | smithandsons.com |
 * Schultz | Jonathan  | jonathan329@unh.edu   | unh.com          |
 * Garrnet | Kevin     | keving@celtics.com    | celtics.com      |
 * Jackson | Mike      | mike@jackson.com      | jacksonfive.com  |
 * Oneil   | Patrick   | patrick@patriots.com  | patriots.com     |
 * Thomas  | Tim       | tim@bb.com            | bruins.com       |
 * Brady   | Tom       | tom@football.com      | patriots.com     |