Database project PowerPoint

BA 384 Database Project Page 1 of 5

BA 384 - DATABASE PROJECT

Students will develop a “turn-key” menu-driven database application using MS Access 2013. Project requirements include completion of several milestones. Each team will need to be prepared to share their work with the class three times during the course. It is understood that each project will be a “work-in-progress” throughout the term and the intent of presenting to the class at the points stated below is for students to share where they are at that point in time, to discuss problems and solutions they have encountered, and to solicit constructive feedback. Due dates are on the class schedule One grade will be provided for the project at the end of the term, based on the final database project and each of the deliverables discussed in the sections below. (Missed deadlines throughout the term will result in “late fees” that will lower your final database project grade.) #1) BRIEF PROJECT PROPOSAL AND PRELIMINARY DATABASE DESIGN Include the following sections in a format that is similar to this document (headings, single-spaced paragraphs, double spaced between). Questions in italics are posed to help you think about what information you should include, but you need not limit your information to this should you have additional information to provide. Bring a printout of this document to class on the day that it is due and POST document as PDF into PROJECT FORUM. § Team Members and Project Name

§ Problem Statement using the format below (exactly).

The problem of <statement of problem> affects <affected people, organizations, or customer groups>. The impact of this is <specific problems>. A successful solution would <describe solution>.

Example: • The problem of keeping track of student volunteer hours using the current manual process affects staff,

students, and advisors. The impact of this is time-consuming work is needed to validate and summarize volunteer hours, and no capability to identify students who are deficient, resulting in delayed student graduation. A successful solution would provide on-demand reports student volunteer activities, summary reports for tracking activities, and exception reports to identify students who are not keeping pace with graduation requirements.

§ Overview of System Requirements:

• Processes: In narrative (full paragraph) form, describe an overall vision of how the database would be integrated into the day-to-day processes. Example: The training coordinator will e-mail the HR clerk with the schedule of classes to be offered and the clerk will enter these classes into the database. Employees will e-mail or call the clerk to sign up for classes and the clerk will add the registration records to the database. On the day of a class, the clerk will print class rosters and put these in the instructor mailboxes. The Instructor will use these rosters to take attendance, and return these to the HR clerk, who will enter attendance information into the system.

• Output: List of desired output of system. (Be specific: such as “Student Activity Report”, “Activity by Organization Report”, “Monthly Sales Reports”)

• Input: What data is needed to generate the desired output? Where will it come from? Examples: weekly time sheets handed in by employees, guest reservations phoned in by customers, student data downloaded from data warehouse, contact information collected from e-mails and phone calls, class attendance sheets collected by instructors, etc.

§ Preliminary E/R Diagram and Database Design • Include a preliminary E/R diagram along with the corresponding business rules. For the E/R diagram

itself, MS Visio is available in the School of Business computer lab on the Ashland campus. If you have access to this program, it is recommended. Otherwise, do your best using the graphic

BA 384 Database Project Page 2 of 5

capabilities in Word, PowerPoint, or Google Draw and paste into your word or shared Google document. Save your diagram in case you need to make changes.

§ Below the illustration, write out the “business rules” and list each table in “standard database notation” as shown below. Underline primary keys and italicize foreign key for each table using standard database notation as shown in the example: tblname (PrimaryKey, ForeignKey, field1, field2. etc.). Include fields that you think you will need (this does not need to be complete at this point, but the more complete your information is, the more feedback I can provide).

• See example below:

Example: Entity Relationship Diagram and Preliminary Table Structure

Business Rules as shown in E/R diagram above: Each Student can have many activities, but might not have any. Each Activity must be completed by one (and only one) student. Each Organization can have many activities, but might not have any. Each Activity must be completed at one (and only one) organization.

Preliminary Table Structure (keys are required, additional fields are optional):

tblStudent (StudentID, FirstName, LastName, Address, City, State, Zip, Phone, Email, GradYear) tblOrganization (OrgID, OrgName, OrgContactFirst, OrgContactLast, OrgAddress, OrgCity…) tblActivity (ActivityID, StudentID, OrgID, ActDate, HrsWorked, ActDescription)

Grading Criteria (15% of project grade) • Relevant content in each proposal section • Organized & professional in appearance • Page footer to include project name, student name(s), page x of y, and date; no errors in spelling or

grammar; consistent in use of headings, indents, margins, etc. • E/R diagram pasted into the Word document • Correct E/R diagramming notations for relationships, primary keys, and entity names, neat and orderly,

no crossed lines, consistent sizing and font use • Table structure included with primary and foreign key structure identified and formatted appropriately • Business Rules correspond to E/R diagram • Handed in on-time with student(s) in class to present project overview

#2) UPDATED PROPOSAL AND COMPLETED DATA STRUCTURE In sum, there are two deliverables for this phase and a hard copy must be brought to class on the due date:

a) Updated proposal document with modifications as discussed below (also submit PDF into Forum). b) System documentation of your database structure, generated from Access after you have added tables,

defined fields, established relationships, and entered ‘dummy’ data. With the exception of the relationship report, the System Documentation can be printed from Access and does not need to be included in the PDF submitted.

Updated Proposal/Report: (Same document can be modified/updated over time to become your final system documentation. If working on a team, consider making this a working document and sharing in Google docs.)

1) Problem Statement: Carefully review and adjust if your vision as changed. 2) System Requirements section should be changed to now include the following:

BA 384 Database Project Page 3 of 5

a) Expansion of the narrative that provides context and description of the processes. An overview can be provided, although narrative should also be integrated with the discussion of the forms and reports that your team plans on developing as applicable.

b) Forms: provide information about the data entry forms (and subforms) that you are planning on creating. Include the data source for your forms along with the narrative of our processes. Identify forms using standard naming conventions. Example of one entry:

c) Reports: Provide a list of reports you will be creating with a short description of each. Describe the

purpose of the report and how it will be used. Include selection parameters (what the user will be prompted for), groupings (where you will want to show subtotals), calculations, and the data source. Also note whether this report will be available from a report menu or from a particular form. Example:

3) E/R Diagram and Database Design section:

a) E/R Diagram and Business rules should be updated if changed. b) Relationship report should be added to this section. c) (System documentation printed from Access can just be stapled to the report.)

Data Structure: major steps

1. Create new database in Access (remember, no spaces in name). 2. Add tables and define fields using naming conventions as discussed in class and shown in the “Naming

Conventions” handout. Use appropriate data types, change field lengths, and take the time to review/change other field properties (captions and descriptions, for example) as needed.

3. Establish relationships and enforce referential integrity. (Note: If a foreign key is pointing to a primary key in another table that is an autonumber, the data type for the foreign key must be a ‘long integer’.)

4. Add AT LEAST five records to every table (if applicable). You can add these records from the table datasheets—do not create forms yet. (Note: for one-to-many relationships that have referential integrity enforced, you will need to add a record to the “one” table before you to add records to the “many” table.) Create a diverse data set. For example, if you are dealing with customers, orders, and products, add multiple customers with multiple orders, with multiple products if applicable.

5. Generate your data structure documentation as follows: 1) Open Relationship Window: select Database Tools, Relationships. Be sure that all tables fit on one

page and all fields are shown for each table. o Select Relationship Report from the Design Tab, edit as needed, and then print this report. o Alternative: take a screen capture of the Relationship window and paste into a Word doc. Be

sure that ALL fields show.

frmStudent (source: tblStudent) with frmStudentActivity subform (source: tblActivity): This form will be used by the volunteer coordinator to add, edit, and/or view student information. New student information is added from the Student Directory when the student submits their first activity log. The frmStudentActivity subform will be used to add/view the student’s volunteer activities. The source of the volunteer activity entry will be the Volunteer Log card that is submitted by the student. A student summary report will be available to run directly from the student page.

rptStudentActivity (source: qryStudentActivity using tblStudent and tblActivity): The report will be printed from a button on frmStudent, will show information about the student who is open on the form, and will include a detailed list of all of the volunteer activities and the total hours worked. This report will be printed upon request. Also, once the student has fulfilled their graduation requirements, the report will be added to their student file.

BA 384 Database Project Page 4 of 5

2) Use Access documentation tools to print information for each table. This report can be VERY long, so be sure to edit the settings as described below to limit the level of detail provided (required): o In the Database Tools ribbon, click on the Database Documenter button in the Analyze Group. o Select the Tables Tab and Select All. o Click on the Options… button and change the settings as follows:

§ Include for Tables: Properties only § Include for Fields: Names, Data Types, and Sizes § Include for Indexes: Nothing

o Click on OK to save these settings, then click on OK in the Documenter window. This will provide you with a print preview of the documentation report. You should have one page per table - check this before you send to the printer.

3) Come to class with a hard copy of your work, prepared to discuss your data structure and major system components (below) with the class.

Grading Criteria (20% of project grade)

• Hard copy brought to class on the due date. PDF of updated proposal submitted in forum – be sure to include relationship report. Students in class prepared to discuss.

• Documentation from Access can be separate document stapled to end of report. • Page footer to include project name, student name(s), page x of y, and date; no errors in spelling or

grammar; consistent in use of headings/sections, indents, margins, etc. • Quality: must show good progress made, naming conventions must be used consistently, data types must

be appropriate for the fields, and the field lengths should be changed as needed. (Default field size for text fields is 255 characters. Most fields should be significantly smaller than this.)

• Must include plan for forms and reports along descriptive narrative as shown in examples above. • Reports must show that data has been added to each table (this is shown in the table documentation—be

sure you generate your documentation after you’ve entered your dummy data. FINAL PROJECT REQUIREMENTS Presentation: ~15% of project grade: The presentation to the class is primarily a demonstration of your database. Be organized and decide ahead of time what you will be showing us. Remind us what your project is about and start by showing the database structure. The demo should be done within the menus you’ve created only, never in the database window. The database should open to your main menu (switchboard) and the navigation pane should be minimized. Use at least one data entry screen and run at least one report for the class. Final Database: ~30% of project grade: Final databases should be submitted in the Project Forum prior to the last class meeting (students presenting earlier can submit updates before the last class meeting.) Guidelines and criteria: • Appropriate data structure for project with defined relationships and referential integrity enforced • No spaces used in names of tables, forms, reports, or fields • Fields should be the correct data type; should follow generally acceptable naming conventions; and should

use data validation, input masks, data defaults, and lookup features where appropriate • Records should be entered into all tables, and there should be enough varied data to allow for display of

groups and subtotal in reports where appropriate. • Completed menus (switchboards) for all forms and reports that have been completed. • Data-entry form(s) for data input and editing in good working order, balanced, lined up, consistent styles

used. Forms should be made for all tables, with a minimum of two forms developed for input/editing/viewing (or a form with a subform).

• At least two completed, meaningful, and professional reports accessible from the switchboard/menu or from a data entry form (appropriate use of fonts, columns aligned properly, titles complete, etc.)

• All reports that focus on summarizing activities of any kind must prompt for a date range and selected dates must appear up in the heading. Any criteria set in the query or prompted for during run-time must be included in report heading.

BA 384 Database Project Page 5 of 5

• Correct spelling and professional formatting throughout the database. • In rare cases, a single table may be all that is needed for a very simple project. Only individuals can work on

projects that require a single table, and the grade earned cannot be higher than a “B”. • As a general guideline, projects completed by teams of 2 must have a minimum of ten tables, forms, and

reports in total. (Not including the switchboard menus). • The database should be submitted in Moodle before the presentation, and updated, if changed, before the last

class, which is the final due date.

Project Report (System Documentation): ~20% of project grade The project report/system documentation should provide enough information for someone to understand the purpose of the database and be able to take over maintenance and/or development of the database. Sections should include: • Overview of Project (should include Statement of Problem – but language should be changed to refer to the

existing database. Update and expanded from proposal as appropriate) • Database Design: Relationship window (all fields showing) and table documentation. • Menu Screenshots • Form Screenshots (each form – provide brief description about the form -- be sure to identify the data source

(name of table, description of query) and describe any events/automation linked to the form • Report Samples (one for each report – can attach at the end of the report or, preferably, insert screenshots or

pdfs as pictures within the document). If attaching at the end, then include a “Report” section and list each report and any narrative information you wish to include about that report. (Class Roster Report: This report is to be printed by the HR clerk each morning and distributed to instructors.)

• Project Status /Comments: This is important. Discuss what needs to be done next with the system, what you would do differently if you were to start over, problem areas (shortcomings, what doesn’t work), and/or any other issues you wish to address. These comments can be saved for the end, or, if applicable, interspersed into overall narrative.

• This report should be professional in appearance (organized into sections, single-spaced paragraphs, page footers with page numbers, etc). Professional table of contents is required for final submission, so the use of heading levels and an automatically generated table of contents is highly recommended.