1.View the Data in Html on the Instructor S Web Site. Understand the Relationships Between

MIS 231 Project 1

This is the sequence for completing project #1. These instructions assume you have read and understand the database rules and definitions.

1.View the data in html on the instructor’s web site. Understand the relationships between tables.

2.Create a blank database named “your name MIS 231 Project 1.mdb”. Make sure you have saved it on a hard disk such as your M or H drive. Do not save the database to a diskette except to transport the database.

3.Import the data from the Excel fileBased on the database rules you have read, open each table in design mode and designate the data type and field size for each attribute.

5.Again, based on the database rules, designate a primary key for each table.

6.Build input masks for each attribute for which an input mask is appropriate. Example: date or telephone number.

7.Review the tasks in this assignment to decide how many queries and what data retrieval the queries will be required to do.

8.Build the queries. One or more queries will be required based on your design choices.

9.Build the forms to drive the queries.

10.Validate your work by testing your forms and reports.

11.Review the instructions for turning in your work. Email your work to the instructor.

Project 1 Tasks:

1.Create a form that displays the first and last name, student ID, local telephone number and home state of each student.

Hint: queries must be built to support your forms if the correct data is not available in one data source (table). A query will not be required if all and only the data required is available from one table source.

2.Create a form to display the name and telephone number of each faculty member. Include a sub-form that displays the courses each faculty member in the CIS department was scheduled to teach during the fall 2002 semester. Include enough information such that the form and sub-form produce useful information. For example, an instructor needs to know when (day and time), where (building and room), and what to teach (course, section and title).

3.Include appropriate command buttons in each form to navigate and close the form.

4.Test the forms and navigation buttons to ensure they function correctly. The data in the sub-form should represent only the classes to be taught by the instructor whose name appears in the form. The data in the sub-form should change each time you click on the next record navigation button to change the name that appears in the form.

Note: recall this rule, data in a sub-form cannot come from the same source as the data in the form.

Note: a form that is incomplete or lacks sufficient information is useless. Ask this question: What information will the user need to know?

Campus (15 records)
Building / BldgName
ARTS / HayworthFineArtsBuilding
AUD / Memorial Auditorium (Old Fine Arts Bldg)
CHAP / Chas. E. Hayworth Memorial Chapel
COOK / Cooke Hall
Courses(626 records)
Course / Title
ACC203 / Intro to Financial & Mgr Acct
ACC204 / Financial Accounting
ACC205 / Managerial Accounting
ACC306 / Intermed. Acct II
ACC307 / Fundamentals of Cost Accounting
ACC308 / Accounting Information Systems
ACC310 / Auditing
Faculty (167 records)
FacID / Dept / LocId / FacName / Tel
CIS01 / CIS / COOK246 / Dr. Michael Collins / (336) 841-4522
CIS02 / CIS / COOK248 / Mr. Robert Foy / (336) 888-6387
CIS03 / CIS / COOK248 / Mr. Richard Heiges / (336) 841-4603
CIS04 / CIS / COOK241 / Mr. George Parsons / (336) 841-4603
CIS05 / CIS / COOK246 / Dr. Randall Epperson / (336) 841-4604
CRJ01 / CRJ / ROBH232 / Dr. Robert Little / (336) 841-4605
CSC01 / CS / ROBH233 / Dr. Jeffrey Butera / (336) 841-4606
CSC02 / CS / HHSC233 / Dr. William Hightower / (336) 841-4607
CSC03 / CS / HHSC232 / Mr. Roger Shore / (336) 841-4608
ECO01 / BA / COOK227 / Dr. Michael McCully / (336) 841-4609
ECO02 / BA / COOK124 / Dr. Stephanie Crofton / (336) 841-4610
ECO03 / BA / COOK220 / Dr. Gerald Fox / (336) 841-4611
EDU01 / BA / COOK127 / Dr. Thomas Albritton / (336) 841-4612
EDU02 / ED / ROBH125 / Dr. Dennis Carroll / (336) 841-4613
Location (117 records)
LocID / Building / Room
ARTS408 / ARTS / 408
ARTSAUD / ARTS / AUD
CHAPCHAP / CHAP / CHAP
CHAPCONF / CHAP / CONF
COOK106 / COOK / 106
COOK110 / COOK / 110
COOK120 / COOK / 120
COOK124 / COOK / 124
Schedule (2481 records)
TermID / Course / Sec / Days / Start / End / LocID / FacID / Cred
FA01 / ACC403 / 1 / MWF / 09:00AM / 09:50AM / COOK249 / ACC04 / 3
FA01 / ACC499 / 1 / MWF / 11:00AM / 11:50AM / COOK251 / ACC04 / 3
FA01 / ADV101 / 1 / MWF / 11:00AM / 11:50AM / ROBH121 / ADV02 / 3
FA01 / ART106 / 1 / MW / 09:00AM / 10:50AM / FINE322 / 3
FA01 / ART120 / 1 / MWF / 08:00AM / 08:50AM / FINE123 / 3
Students (1551 records)
StuID / StuFirst / StuLast / Class / LocTel / HomeAddress / HomeCity / St / HZip
S02350 / Diana / Gibson / 2000 / (336) 454-3352 / 614 Lewis Street / High Point / NC / 27265
S02351 / Steven / Bosarge / 2001 / (336) 454-3353 / 1921 Tabby Lane / Charleston / WV / 25302
S02352 / Gary / Comer / 2002 / (336) 454-3354 / 10124 Atkins Ridge Drive / Asheboro / NC / 27203
S02353 / Christina / Gallagher / 2003 / (336) 454-3355 / 6202 Guess Road / Jacksonville / FL / 32205
Term (7 records)
TermId / Term
FA01 / Fall 2001
FA02 / Fall 2002
FA99 / Fall 1999
SP01 / Spring 2001

Rule 1: A database does not exist if it does not have the structure and integrity to support business rules.

Rule 2:All tables or relations must have a primary key.

Rule 3:A primary key can contain no nulls and no duplicates.

Rule 4: Unlike a Primary Key, a foreign key MAY contain nulls and duplicates.

Rule 5:A field or attribute can be used as a primary key in ONLY one table.

Rule 6: A primary keymust be a superkey; itshould use the minimum number of fields.

Rule 7:A foreign key may be used in more than one table or relation.

Rule 8: Data must first exist as a primary key before it can exist as a foreign key.

Rule 9: A foreign key can be a foreign key ONLY if it shares the same spelling, data type, and size as its primary key relation.

Rule 10:If the nature of the data will allow a mathematical operation to be performed on it, the attribute must be a value; if not it must be text.

Rule 11: A form and its sub-form may never be based on the same data source.

Naming conventions: (rules for naming objects in a database)

Tables: The name of a table should be a noun or noun phrase that concisely describes the data contained in the record set. Example: tblStudents where the table contains all students without further parametric qualification.

Query: The name of a query should be a phrase that implies action or a verb that describes how data is processed. A query cannot bear the same name as a table. Example: qryMaleDayStudents where the data source contains all students but the criteria of the query results in only male day students that are male.

Form: The name of a form should describe how it is intended to be used. A form may bear the same name as its data source which is always a table or query.

Report: The name of a report should describe the data it contains. A report may bear the same name as its data source which is always a table or query.

Macro: The name of a macro should be a word or phrase that describes what it does. For clarity and simplicity, build on “MacroList” and include individual macros within the list.

Prefixes: Use name prefixes “tbl” and “qry” for tables and queries. The use of prefixes will assist in correctly identifying data sources when using wizards or menus in building forms, reports, macros and switchboards.