MIS 372

Assignment #2 - Database Creation and Simple Queries

This assignment will help familiarize you with the process of creating and setting up an Access database. You will create a new database and import data into the database. Next, you will make queries on the database.

College Telephone System

The College Telephone System database consists of the following two tables:

User

Field Name	Description	Data Type	Length	Comment or Restriction
UID	User ID Number	Text	5	Primary Key
Last Name	Last Name of User	Text	30
First Name	First Name of User	Text	20
Phone Ext.	Telephone	Number	Integer
Office	Office Location (Room Number and Building Code)	Text	6
Basic Charge	Basic Service Charge (per Month)	Currency
Extra Charges	Extra Charges for Special Services and Long Distance Calls (per Month)	Currency
Staff Code	Staff Code (FAC, PRO, or ADM)	Text		"FAC" or "PRO" or "ADM"
Dept Code	Code of User's Department	Text	3

Department

Field Name	Description	Data Type	Length	Comment or Restriction
Dept Code	Dept Code (Primary Key)	Text	3	Primary Key
Name	Name of Department	Text	14
Location	Location of Departmental Office (Room Number and Building Code)	Text	6
First Name	First Name of Dept. Chair	Text	20
Last Name	Last Name of Dept. Chair	Text	30

Assignment (70%)

Create the "College Telephone System" Access database with tables as defined above including corresponding descriptions, data types, lengths, and restrictions.
Import the data for the tables using the files distributed in class.
Perform the Queries below naming them the same as the question number. (ie, the query used to obtain the answer to question 2 would be saved as Query2).
Save your query answers by copying them to a Word Document.
Submit your Word Document and Access Database via e-mail

Queries (30%)

Write down the number of records imported into each table.
List the User Id, First Name, Last Name, Basic Charge and Extra Charges for all users in the department with the code of MTH.
List the User Id, First Name, Last Name and Phone Ext for all users whose basic charge is at least $16.00 per month
List the User Id, First Name, Last Name, and Extra Charges for all users in the department with a code BIO or MTH who have Extra Charges greater than $40.00. List the records in descending order by Extra Charges.
List the User Id, First Name, Last Name and Phone Ext for all users whose department code is BUS. What were your results? Extra: What can be done to avoid this problem?