Creating a Database for an Educational Institution

NEW PERSPECTIVES ACCESS 2010
TUTORIAL 2 - CASE PROBLEM 1
PINE HILL MUSIC SCHOOL

SKILLS
 Save a database with a new filename
 Change a field's properties in Design view
 Change the data type in Design view
 Create a caption for a field
 Add a field to a table
 Save a table
 Import the structure of a table in an Access database
 Add fields using the Data Type gallery
 Delete a field
 Reorder fields
 Enter a record in Table Datasheet view
 Import data from a text file
 Create a table in Design view
 Set the primary key
 Import data from an Excel worksheet
 Create a one-to-many relationship
PROJECT OVERVIEW
Yuka Koyama uses the Pinehill database to maintain information about the students, teachers, and contracts for her music school. Yuka asks you to help her build the database by updating one table and creating two new tables.
STUDENT START FILE
NP_Access2010_T2_CP1a_FirstLastName_1.accdb (Note: Download your personalized start file from www.cengage.com/sam2010)
 
Instructions
1. Open the file NP_Access2010_T2_CP1a_FirstLastName_1.accdb and save the file as NP_Access2010_T2_CP1a_FirstLastName_2.accdb by clicking the "Save Database As" command in Backstage view. (Note: Verify that your name appears in the first record in the _SAMProjects table before you begin. If it does not, then please download a new copy of the start file from the SAM Web site.)

2. Open the Teacher table, and set field properties as shown in Table 1.
TABLE 1
Field Name Data Type Description Field Size Other
TeacherID Text Primary key 7 Caption = Teacher ID
FirstName Text 20 Caption = First Name
LastName Text 25 Caption = Last Name
Degree Text 3
School Text 50
HireDate Date/Time Format = Short Date
Caption = Hire Date

3. Add a new field as the last field in the Teacher table with the field name Beginners, the Yes/No data type, the Format property of Yes/No, and the caption Takes Beginners. Save the Teacher table. Click the Yes button when a message appears indicating some data might be lost.

4. In the datasheet, specify that the following teachers can take beginners: Schwartz, Eberle, Norris, Tanaka, Culbertson, and Mueller. Save and close the Teacher table.

5. Import the structure of the Student table in the "support_NP_A10_T2_Music.accdb" database into a new table named Student. (The file "support_NP_A10_T2_Music.accdb" is available from the SAM Web site.) Do not save the import steps.

6. Add two fields to the end of the Student table: BirthDate (with Date/Time data type) and Gender (with Text data type).

7. Use the Phone Quick Start selection in the Data Type gallery to add the Business Phone, Home Phone, Mobile Phone, and Fax Number fields between the Zip and BirthDate fields. (Hint: Be sure to make the BirthDate field the active field before adding the new fields.) Delete the Business Phone and Fax Number fields, and then save the Student table.

 
8. Modify the design of the Student table so that it matches the design in Table 2, including the revised field names and data types.
TABLE 2
Field Name Data Type Description Field Size Other
StudentID Text Primary key 7 Caption = Student ID
LastName Text 25 Caption = Last Name
FirstName Text 20 Caption = First Name
Address Text 35
City Text 25
State Text 2
Zip Text 10
HomePhone Text 14
MobilePhone Text 14
BirthDate Date/Time Format = Short Date
Caption = Birth Date
Gender Text F(emale), M(ale) 1

9. Move the LastName field so that it follows the FirstName field. Save your changes to the table design.

10. Add the records shown in Figure 1 to the Student table. Close the Student table.
FIGURE 1

11. Yuka exported the student data that she was maintaining in another computer system to a text file, and she asks you to add this data to the end of the Student table.
a. Specify "support_NP_A10_T2_Student.txt" as the source of the data. (The file "support_NP_A10_T2_Student.txt" is available from the SAM Web site.)
b. Select the option: Append a copy of the records to the table.
c. Select Student as the table.
d. In the Import Text Wizard dialog boxes, choose the option to import delimited data, to use a comma delimiter, and to import the data into the Student table. Do not save the import steps.
 
12. In Design view, create a new table using the table design shown in Table 3. Specify ContractID as the primary key. Save the table using the name Contract.
TABLE 3
Field Name Data Type Description Field Size Other
ContractID Text Primary key 4 Caption = Contract ID
StudentID Text Foreign key 7 Caption = Student ID
TeacherID Text Foreign key 7 Caption = Teacher ID
LessonType Text 25 Caption = Lesson Type
LessonLength Number 30 or 60 minutes Integer Caption = Lesson Length
LessonCost Currency Format = Currency
Decimal Places = 0
Caption = Lesson Monthly Cost
RentalCost Currency Monthly rental charge for instrument Format = Currency
Decimal Places = 0
Caption = Monthly Rental Cost
13. Switch to Datasheet view, and then use the Start and End Dates Quick Start selection in the Data Type gallery to add two Date/Time fields (StartDate and EndDate) between the TeacherID and LessonType fields. (Hint: Be sure to make the LessonType field active before adding the new fields.)

14. Switch to Design view, specify the Short Date Format for the StartDate and EndDate fields, change the field captions to Contract Start Date and Contract End Date (respectively), and then save and close the Contract table.

15. Use the Import Spreadsheet Wizard to add data to the Contract table from an Excel spreadsheet.
a. Specify "support_NP_A10_T2_Lessons.xlsx" as the source of the data. (The file "support_NP_A10_T2_Lessons.xlsx" is available from the SAM website.)
b. Select the option: Append a copy of the records to the table.
c. Select Contract as the table.
d. In the Import Spreadsheet Wizard dialog boxes, choose the Sheet1 worksheet, and import to the Contract table. Do not save the import steps.

16. Open the Contract table and add the records shown in Figure 2.
FIGURE 2

17. Define a one-to-many relationship between the primary Student table and the related Contract table based on the StudentID field. Select the Enforce Referential Integrity option and the Cascade Update Related Fields option.

18. Define a one-to-many relationship between the primary Teacher table and the related Contract table based on the TeacherID field. Select the Enforce Referential Integrity option and the Cascade Update Related Fields option. Save the changes to the Relationships window, and then close the window.

Compact and repair the database, and exit Access. Follow the directions on the SAM Web site to submit your completed project.