400 likes | 419 Views
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.
E N D
Chapter 8 DATABASE DESIGN AND DEVELOPMENT: A VISUAL APPROACH Raymond Frost – John Day – Craig Van Slyke Chapter 8 Creating Databases with Microsoft Access
Chapter 8 Microsoft Access Data Types Exhibit 8-1: Data Types Available in Microsoft Access
Chapter 8 Sky_Member Database Design Exhibit 8-2: Design and Data for the Skydiving Club Member Database
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
Chapter 8 The Microsoft Access Interface Use this option to create a new database. Exhibit 8-3: Creating a Database in Access
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
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
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
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
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
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
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
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
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
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
Chapter 8 The Completed Table Exhibit 8-13: The Completed SKY_MEMBER Table
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
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
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
Chapter 8 The Arcade Database Design Exhibit 8-17: The Design for the Arcade Database
Chapter 8 Arcade Database Data Exhibit 8-17: The Design for the Arcade Database
Chapter 8 The Member Table This is the completed design for the MEMBER table. Exhibit 8-18: The Design for the MEMBER Table
Chapter 8 The Visit Table This is the completed design for the VISIT table. Exhibit 8-19: The Design for the VISIT Table
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
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
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
Chapter 8 The Completed Relationship The infinity symbol indicates that the relationship is one-to-many. Exhibit 8-23: The Completed Relationship
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
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
Chapter 8 Enrollment Database Design Exhibit 8-26: The Design of the Enrollment Database
Chapter 8 Enrollment Database Data Exhibit 8-26: The Design of the Enrollment Database
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
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
Chapter 8 Hospital Database Design Exhibit 8-29: Design and Data for the Hospital Database
Chapter 8 Hospital Database Data Exhibit 8-29: Design and Data for the Hospital Database
Chapter 8 Acme Database Design Exhibit 8-30: Design and Data for the Acme Database
Chapter 8 AcmeDatabaseData Exhibit 8-30: Design and Data for the Acme Database
Chapter 8 Amazon Database Design Exhibit 8-31: Design and Data for the Amazon Database
Chapter 8 AmazonDatabaseData Exhibit 8-31: Design and Data for the Amazon Database