SpEAK:Spring 2011


 * Home
 * Semesters
 * [Spring 2011]

=Profile =

Participants

 * Class: Project by group of students working on Database Design and Development


 * Sponsor: Proxy client: kcs37@cisunix.unh.edu

Abstract

 * Title: "SpEAK" Sp-eech Experiment Accessible Knowledge


 * Authors: Jeff Knight, Andy O'Brien, Paskale Odongo, and Mike Tierney


 * Project Sponsor: Speech Project at the University of New Hampshire at Manchester

Motivation
The Speech Project at the UNHM has the extremely complex task of designing speech recognition software. Implementing this software requires performing hundreds of tedious experiments to record sounds and other information needed for its development. Currently the Speech program has no easy way of storing and organizing these performed experiments for future use.

Objectives
The Speech Lab team is developing a database in the MySQL programming language for Capstone to store and run the queries on their experiments they so desperately need.

Methods
SpEAK is utilizing Google Code project hosting website to act as a central repository for their developed code. Google Code will be a very easy method of version control for the project. The group is communicating and sharing their work products via Google Group pages, and an online Wiki site.

Results
Results of the SpEAK project will incorporate an operational database capable of installing itself onto the user's machine via a script file, and an online wiki site describing the process of development.

Scope
The project scope is to deliver a working error free relational database conforming to the project specifications, by 05/12/2011.

Conclusion
[edit: this was left blank...]

=Activities=

Team Members Roles and Responsibilities
''Note from Mihaela.The project is missing the following. Project purpose and sponsor profile in the Project Description and Conclusion section of the Abstract; System requirements; Database Analysis; brief descriptions of the diagrams (relational model and parent-of and child-of dependency diagrams), definitions of the relational model entities and attributes and summary of the relational model relationships.''

''I'm assigning these tasks as follows. Paskale writes the system requirements and revise project description. Andy writes the database analysis (conceptual model diagram with description and definitions of the conceptual model entities) and a summary of the relational model relationships. Mike writes the other diagrams' descriptions and the definitions of the relational model entities and attributes. Everyone writes SQL queries. Add SQL programmer to your primary roles. Write your queries in a file whose name is your last name and extension .sql. Place the file in your local copy of the repository, add it to version control (use Add command in TortoiseSVN), and commit (do not forget the log message).


 * Paskale Odongo
 * Primary role: System Analyst, SQL Progrmmer and Reviewer
 * Secondary role: Team Leader
 * Responsibilities: Project description, system requirements, meeting agendas and minutes on google group/mediawiki


 * Jeff Knight
 * Primary Role: DB Designer, SQL Programmer and Reviewer
 * Secondary Role:
 * Responsibilities: Database design and updates to create tables SQL script


 * Andrew O'Brien
 * Primary Role: SQL Programmer
 * Secondary Role: IT Support
 * Responsibilities: Implementation (SQL script to create the database tables)


 * Mike Tierney
 * Primary Role: SQL Programmer
 * Secondary Role: Wiki Editor
 * Responsibilities: Implementation (SQL script to populate the database with sample data)

Team Meetings

 * 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
There is no current system in place to store theses experiments. The proposed system will have a database subsystem that manages speech experiments.

Database Subsystem Features
The database subsystem will manage speech experiments data. Once completed, the SpEAk back-end database will give its users the power to store, manage, and deliver important speech research information.

User Interactions
There are three types of users who will interact with the database subsystem: viewer, author, and administrator. Based on the user type, the following interactions will be supported by the database subsystem:

Viewer
 * Can view all experiments
 * Cannot edit or delete experiments
 * Can not create experiments

Author
 * Can view all experiments
 * Can create experiment
 * Can edit the experiments they have created
 * Can not edit other users' experiments
 * Can delete their own experiments
 * Cannot delete other users' experiments

Administrator
 * Can view all experiments
 * Can create an experiment
 * Can edit any experiment
 * Can delete any experiment

GUI Functionality
SpEAK system will have a front-end that support user interactions. A preliminary outline of the SpEAK's GUI has four tabs, View, New, User, and Admin, with the following functionality:

''View Tab
 * Experiment# (Auto generated)
 * Experiment Title
 * Date created
 * Date modified
 * Authors

''New Tab
 * Experiment # (Auto generated)
 * Authors
 * Create Date
 * Last modified date
 * Description
 * Attachments
 * Save button (front end)

''User Tab
 * This is the page that you would use to login. It would ask for Login Name and password( Front End ).

''Admin Tab
 * Database must be able to handle multiple users at a time( Front End ).
 * Webpage should be accessible to anyone wanting to view experiments( Front End ).
 * However some experiments might need to be hidden from viewer who are not part of the project, so there should be 3 privelege levels( Front End ):
 * Any one can see experiments
 * Only team members can see experiments
 * Admin level, administrators who can add team members.

Operating Environment
The database subsystem will be operating on the MySQL database management system. MySQL runs as a server application that provides multi-user access to a number of databases. Client utilities, such as mysql, mysqladmin, mysqldump, and others are used to create, query, and administer databases in the MySQL system. The database is written in the Structured Query Language (SQL). MySQL runs on virtually all platforms, including Linux, UNIX, and Windows.

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
 * Create Table1 sql code
 * Create Table2 sql code
 * Create Table3 sql code
 * Create Table4 sql code
 * speak sql

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

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

Jeff's Query Examples
Finds how many authors are from UNH and orders them by last name.

++--+---++--++ ++--+---++--++ ++--+---++--++ 4 rows in set (0.06 sec)
 * UserID | LastName | FirstName | Phone     | email                | Campus |
 * 6 | Archer  | Jim       | 6039992343 | archy@yahoo.com      | UNH    |
 * 7 | Barilla | Jose      | 6037512343 | fritobandito@yahoo.c | UNH    |
 * 5 | Knight  | Jeff      | 6038882343 | dawhiteballer@yahoo. | UNH    |
 * 3 | Odongo  | Paskale   | 6034662843 | pasky@yahoo.com      | UNH    |

Displays all experiments from a specific author by joining the author and experiment tables. ++--+---+--+ ++--+---+--+ ++--+---+--+ 4 rows in set (0.00 sec)
 * UserID | LastName | FirstName | ExperimentNumber |
 * 2 | Jonas   | Michael   |              101 |
 * 2 | Jonas   | Michael   |              102 |
 * 2 | Jonas   | Michael   |              104 |
 * 2 | Jonas   | Michael   |              105 |

Paskale's Query Examples
Displays all experiments from the experiment table.

+--+++-+--+---+ +--+++-+--+---+ +--+++-+--+---+ 5 rows in set (0.00 sec)
 * ExperimentNumber | UserID | Title | DateCreated | DateModified | Description   |
 * 101 |     1 | Ears   | 2011-04-24  | 2011-04-25   | super awesome |
 * 102 |     2 | Music  | 2011-05-24  | 2011-06-25   | was great     |
 * 103 |     3 | Listen | 2011-07-24  | 2011-08-25   | wow           |
 * 104 |     4 | Talk   | 2011-03-24  | 2011-06-25   | holy cow      |
 * 105 |    15 | Yell   | 2011-02-24  | 2011-07-25   | stunning      |

Displays all experiment admin from the experiment admin table.

++--+---+--+++ ++--+---+--+++ ++--+---+--+++ 2 rows in set (0.00 sec)
 * UserID | LastName | FirstName | Phone       | email              | Campus |
 * 3 | Allan   | Ray       | 603-584-5485 | rayallan@unh.edu   | Durham |
 * 4 | Pierce  | Paul      | 603-545-8125 | paulpierce@unh.edu | Durham |

Displays all experiment attachments from experment admin table.

+--+--++ +--+--++ +--+--++ 1 row in set (0.00 sec)
 * ExperimentNumber | AttachmentID | AttachmentName |
 * 101 |          15 | super15        |

Displays all experiment URL from experiment_url table.

+--+---+---+ +--+---+---+ +--+---+---+ 1 row in set (0.00 sec)
 * ExperimentNumber | UrlID | UrlName      |
 * 101 |   45 | www.music.com |