1 / 39

DATABASE DESIGN AND DEVELOPMENT: A VISUAL APPROACH

DATABASE DESIGN AND DEVELOPMENT: A VISUAL APPROACH. Raymond Frost – John Day – Craig Van Slyke. Chapter 8 Creating Databases with Microsoft Access. Microsoft Access Data Types. Exhibit 8-1: Data Types Available in Microsoft Access. Sky_Member Database Design.

gbrenner
Download Presentation

DATABASE DESIGN AND DEVELOPMENT: A VISUAL APPROACH

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 8 DATABASE DESIGN AND DEVELOPMENT: A VISUAL APPROACH Raymond Frost – John Day – Craig Van Slyke Chapter 8 Creating Databases with Microsoft Access

  2. Chapter 8 Microsoft Access Data Types Exhibit 8-1: Data Types Available in Microsoft Access

  3. Chapter 8 Sky_Member Database Design Exhibit 8-2: Design and Data for the Skydiving Club Member Database

  4. Chapter 8 Creating a Database in Access • Specify database file • Create table • Create columns by • Entering column name • Selecting data type • Specifying field size, when applicable • Indicate primary key • Enter data

  5. Chapter 8 The Microsoft Access Interface Use this option to create a new database. Exhibit 8-3: Creating a Database in Access

  6. Chapter 8 Specifying the Database File Name the file “sky_member” The .mdb extension is the standard file type for Microsoft Access databases. Exhibit 8-4: Specifying the Database File

  7. Chapter 8 The Database Window Use this option to create a new table. The database window provides access to all the types of objects that can be part of a database. Exhibit 8-5: The Database Window

  8. Chapter 8 Access Database Objects • Tables: basic storage units in the database • Queries: saved sets of instructions for retrieving database data • Forms: interactive screens that allow you to work with database data • Reports: display and print reports based on database data • Pages: Web pages published from Access that allow you to view and manipulate data stored in the database • Macros: lists of Access functions that can be used to automate tasks • Modules: stored procedures written in Visual Basic used to automate tasks

  9. Chapter 8 Creating a Table in Design View Type a name for the field, then select a data type. For text fields, enter the size. Exhibit 8-6: Creating a Table in Design View

  10. Chapter 8 Number Data Types For Number fields, select a number type rather than entering a size. On the next page, we will look at a Validation Rule. Exhibit 8-7: Creating a Column with a Number Data Type

  11. Chapter 8 Validation Rules A Validation Rule is a condition that will be applied to the data entered in the field. Here, a rule is being created for the Equip field that requires the user to enter either Y or N. Any other data will be rejected. Exhibit 8-8: Creating a Validation Rule

  12. Chapter 8 The Completed Validation Rule This is what the table design looks like when the Validation Rule is completed. After the equip field is defined, a skill field is also entered. It will have a validation rule limiting input to B, I, and A. Exhibit 8-9:The SKY_MEMBER Table with a Validation Rule

  13. Chapter 8 Creating a Primary Key A primary key is specified by selecting a field and then clicking the primary key button on the toolbar. Note the key symbol added in the box in front of the field name. Exhibit 8-10: Specifying the Primary Key

  14. Chapter 8 Entering Data Once the table is created and saved, click on the datasheet icon to show the table in datasheet view. Data can now be entered in the cells. There is always a blank row at the bottom where a new row can be added. Exhibit 8-11: The Datasheet View of the SKY_MEMBER table

  15. Chapter 8 Using the Validation Rule When entering data in a column that includes a validation rule, Access will display an error message if you try to enter data that violates the rule. Exhibit 8-12: The Error Displayed When Violating a Validation Rule

  16. Chapter 8 The Completed Table Exhibit 8-13: The Completed SKY_MEMBER Table

  17. Chapter 8 Creating a Query A query can be used to retrieve and display data in a table. Queries can be created from the database window. Exhibit 8-14: Creating a Query

  18. Chapter 8 Creating a Query in Design View When the sky_member table is selected, it will be displayed in the design view. Select the “sky_member.*” item in the drop-down list to display all the fields in the table. Exhibit 8-15: Designing the Query

  19. Chapter 8 The Query Display When the Query, Run menu option is used to execute the query, the results will be displayed as shown above Exhibit 8-16: The Results of the Query

  20. Chapter 8 The Arcade Database Design Exhibit 8-17: The Design for the Arcade Database

  21. Chapter 8 Arcade Database Data Exhibit 8-17: The Design for the Arcade Database

  22. Chapter 8 The Member Table This is the completed design for the MEMBER table. Exhibit 8-18: The Design for the MEMBER Table

  23. Chapter 8 The Visit Table This is the completed design for the VISIT table. Exhibit 8-19: The Design for the VISIT Table

  24. Chapter 8 Creating a Relationship When a database contains two tables, the tables can be linked by creating a relationship. Use the Tools, Relationships menu option to create a relationship. This will display the list of tables shown to the right. Use the Add button to add each table to the Relationships View. Exhibit 8-20: The Show Table Dialog Box

  25. Chapter 8 The Relationships View The Relationships View can be used to link the two tables: click and drag the member$email column in the Visit table onto the email column in the Member table to indicate that they match. This will display the Edit Relationships dialog box on the next slide. Exhibit 8-21: The Relationships View

  26. Chapter 8 Editing the Relationship Check the Enforce Referential Integrity box. This will ensure that values entered in the member$email foreign key field match values in the email primary key field in the Member table. Exhibit 8-22: The Edit Relationships Dialog Box

  27. Chapter 8 The Completed Relationship The infinity symbol indicates that the relationship is one-to-many. Exhibit 8-23: The Completed Relationship

  28. Chapter 8 Referential Integrity When a value is entered in the member$email column that does not match a value in the email column of the member table, this error message will be displayed. Exhibit 8-24: The Error Generated When Violating Referential Integrity

  29. Chapter 8 Displaying Related Rows The Datasheet View of a table linked to a second table includes the additional option of displaying the related records. Click on the plus (+) at the beginning of the row. Exhibit 8-25: Displaying Related Rows on the Datasheet

  30. Chapter 8 Enrollment Database Design Exhibit 8-26: The Design of the Enrollment Database

  31. Chapter 8 Enrollment Database Data Exhibit 8-26: The Design of the Enrollment Database

  32. Chapter 8 Creating a Concatenated Key To create a concatenated key, simply highlight both fields in the design view and click on the Primary Key button. The key symbol should be displayed in front of both fields. Exhibit 8-27: Creating a Concatenated Key

  33. Chapter 8 Enrollment Database Relationships In the Relationships View, add all the tables and drag each foreign key to its corresponding primary key. Exhibit 8-28: Relationships for the Enroll Database

  34. Chapter 8 Hospital Database Design Exhibit 8-29: Design and Data for the Hospital Database

  35. Chapter 8 Hospital Database Data Exhibit 8-29: Design and Data for the Hospital Database

  36. Chapter 8 Acme Database Design Exhibit 8-30: Design and Data for the Acme Database

  37. Chapter 8 AcmeDatabaseData Exhibit 8-30: Design and Data for the Acme Database

  38. Chapter 8 Amazon Database Design Exhibit 8-31: Design and Data for the Amazon Database

  39. Chapter 8 AmazonDatabaseData Exhibit 8-31: Design and Data for the Amazon Database

More Related