Explore BrainMass
Share

Explore BrainMass

    Creating a Database for an Educational Institution

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    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.

    © BrainMass Inc. brainmass.com October 10, 2019, 6:34 am ad1c9bdddf
    https://brainmass.com/education/learning-teaching/creating-database-educational-institution-548660

    Attachments

    Solution Preview

    Please see the attached Solution.

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

    Solution Summary

    The expert creates a database for an educational institution. A one-to-many relationship between the primary teacher table and the related contract table based on the TeacherID field is defined.

    $2.19