Chapter 05 DATABASE DESIGN AND DEVELOPMENT A VISUAL

Chapter 05 DATABASE DESIGN AND DEVELOPMENT: A VISUAL APPROACH Raymond Frost – John Day – Craig Van Slyke Chapter 05 Creating Databases with Microsoft Access 1 Database Design and Development: A Visual Approach © 2006 Prentice Hall

Chapter 8 Microsoft Access Data Types Database Design and Development: A Visual Approach ©

Chapter 8 Sky_Member Database Design and Development: A Visual Approach © 2006 Prentice Hall

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 4 Database Design and Development: A Visual Approach © 2006 Prentice Hall

Chapter 8 The Microsoft Access Interface Use this option to create a new database.

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 Database Design and Development: A Visual Approach © 2006 Prentice Hall 6

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 Database Design and Development: A Visual Approach © 2006 Prentice Hall 7

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 8 Database Design and Development: A Visual Approach © 2006 Prentice Hall

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 Database Design and Development: A Visual Approach © 2006 Prentice Hall 9

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. Database Design and Development: A Visual Approach © 2006 Prentice Hall Exhibit 8 -7: Creating a Column with a Number Data Type 10

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 Database Design and Development: A Visual Approach © 2006 Prentice Hall 11

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. Database Design and Development: A Visual Approach © 2006 Prentice Hall Exhibit 8 -9: The SKY_MEMBER Table with a Validation Rule 12

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 Database Design and Development: A Visual Approach © 2006 Prentice Hall 13

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. Database Design and Development: A Visual Approach © 2006 Prentice Hall Exhibit 8 -11: The Datasheet View of the SKY_MEMBER table 14

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. Database Design and Development: A Visual Approach © 2006 Prentice Hall Exhibit 8 -12: The Error Displayed When Violating a Validation Rule 15

Chapter 8 The Completed Table Database Design and Development: A Visual Approach © 2006

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 Database Design and Development: A Visual Approach © 2006 Prentice Hall 17

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 Database Design and Development: A Visual Approach © 2006 Prentice Hall 18

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 Database Design and Development: A Visual Approach © 2006 Prentice Hall 19

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

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.

Chapter 8 The Visit Table This is the completed 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 Database Design and Development: A Visual Approach © 2006 Prentice Hall 24

Chapter 8 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 Database Design and Development: A Visual Approach © 2006 Prentice Hall 25

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 Database Design and Development: A Visual Approach © 2006 Prentice Hall 26

Chapter 8 The Completed Relationship The infinity symbol indicates that the relationship is one-to-many. Exhibit 8 -23: The Completed Relationship Database Design and Development: A Visual Approach © 2006 Prentice Hall 27

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. Database Design and Development: A Visual Approach © 2006 Prentice Hall Exhibit 8 -24: The Error Generated When Violating Referential Integrity 28

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. Database Design and Development: A Visual Approach © 2006 Prentice Hall Exhibit 8 -25: Displaying Related Rows on the Datasheet 29

Chapter 8 Enrollment Database Design and Development: A Visual Approach © 2006 Prentice Hall

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 Database Design and Development: A Visual Approach © 2006 Prentice Hall 32

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 33 Database Design and Development: A Visual Approach © 2006 Prentice Hall