Extended Learning Module J (Office 2003 Version) Implementing a Database with Microsoft Access,Copyright 2010 by the McGraw-Hill Companies, Inc. All rights reserved.,McGraw-Hill/Irwin,STUDENT LEARNING OUTCOMES,Identify the steps necessary to implement the structure of a relational database using the data definition language provided by Microsoft Access. Demonstrate how to use the data manipulation subsystem in Access to enter and change information in a database and how to query that information. Explain the use of the application generation subsystem in Access to create reports and data entry screens.,Mod J CD-2,MODULE ORGANIZATION,Introduction Learning Outcome #1 Defining Relationships within the Solomon Enterprises Database Entering Information into the Solomon Database Learning Outcome #2 Creating a Simple Query Using One Relation,Mod J CD-3,MODULE ORGANIZATION,Creating an Advanced Query Using More than One Relation Generating a Simple Report Learning Outcome #3 Generating a Report with Grouping, Sorting, and Totals Creating a Data Input Form,Mod J CD-4,INTRODUCTION,The steps to creating a database are Define entity classes and primary keys Define relationships among the entity classes Define fields for each relation (file) Use a data definition language to create the database, which is the focus of this Module,Mod J CD-5,INTRODUCTION,In Figure J.1 (p. J.2) on the following 3 slides, weve recreated the correct database structure from Extended Learning Module C Revisit Module C if you need a refresher,Mod J CD-6,INTRODUCTION,Mod J CD-7,INTRODUCTION,Mod J CD-8,INTRODUCTION,Mod J CD-9,Creating Solomons Database,Data dictionary - contains the logical structure for the information To create the Solomon Enterprise database: Start Microsoft Access Select Blank Database (or select File and New) and then Blank Database on the right side of the screen Select a folder for the database and enter a file name (well use Solomon Enterprises.mdb) Click on Create,Mod J CD-10,Creating Solomons Database,Create a new database,Select a folder and enter a name,Mod J CD-11,Creating Solomons Database,To create a relation (table) in Design view: Make sure the Tables tab is selected and then double-click on Create table in Design view Enter a name, data type, and description (the last is optional) for each field in a given relation Save that structure and repeat the process until youve created the structure for each relation in your database,Mod J CD-12,Creating Solomons Database,First, select these options,Then, enter field names and data types,Mod J CD-13,Creating the Raw Material Relation,Enter the four fields of the Raw Material relation Raw Material ID Raw Material Name QOH Supplier ID Click on the Raw Material ID row and then the key button to designate Raw Material ID as the primary key,Mod J CD-14,Creating the Raw Material Relation,Identification of a primary key,Mod J CD-15,Creating the Concrete Type Relation,Mod J CD-16,Creating the Bill of Material Relation,We created (in Extended Learning Module C) the Bill of Material relation to eliminate the many-to-many relationship between the Concrete Type and Raw Material relations,Mod J CD-17,Creating the Bill of Material Relation,The Bill of Material relation has a primary key composed of two fields (composite primary key): Concrete Type Raw Material ID Composite primary key - consists of the primary key fields from the two intersecting relations,Mod J CD-18,Creating the Bill of Material Relation,Composite primary key,Mod J CD-19,DEFINING RELATIONSHIPS WITHIN SOLOMONS DATABASE,The final structural task is to define how all the relations relate to each other That is, link primary and foreign keys Foreign key - a primary key of one file (relation) that appears in another file (relation),Mod J CD-20,Primary and Foreign Key Logical Ties,Mod J CD-21,Defining Relationships between Relations,To create relationships Click on the Relationships button in the button bar Make each relation appear on the palette by highlighting each relation name and clicking on Add Then click on the Close button,Mod J CD-22,Defining Relationships between Relations,Relationship palette,Select tables and click on Add,Mod J CD-23,Defining Relationships between Relations,Mod J CD-24,ENTERING INFORMATION INTO SOLOMONS DATABASE,To enter information, you simply highlight the desired relation and click on Open,Supplier relation structure,Mod J CD-25,Entering Information,Mod J CD-26,Referential Integrity,Referential integrity ensures consistency. For example, that you dont put a non-existent Supplier ID into the Raw Material relation The relationships we set up for referential integrity guard against bad information Integrity constraints rules that help ensure the quality of the information,Mod J CD-27,Enforcing Referential Integrity,Mod J CD-28,Change the Structure of a Relation,Highlight the relation and click on the Design butt