Microsoft Access 2013 Design and Implement Powerful Relational DatabasesChapter OverviewDatabase RelationshipsPowerPoint PresentationCompatible Data TypesErrors in RelationshipsRelationship OptionsRelationships ExampleSubformsSlide 10Subforms ExampleAccess UtilitiesGet External DataQuick ReviewSlide 15Slide 16Slide 17Slide 18Slide 19Slide 20Homework 4Microsoft Access 2013Design and Implement Powerful Relational DatabasesChapter 6Chapter OverviewDatabase Relationships–Referential Integrity–Cascade Update and DeleteSubformsHelpful Access UtilitiesDatabase RelationshipsTables should be related on primary and foreign keys with similar data and compatible data types.Set relationships BEFORE entering data in the tables.Click Database Tools Ribbon, click Relationships buttonRelationships WindowCompatible Data TypesPrimary Key Foreign KeyText 1---∞ __________Numeric 1---∞ __________Date 1---∞ __________Autonumber 1---∞ __________Errors in RelationshipsCaused by one of three things:1) Primary and/or foreign keys aren’t set properly in the table design2) Data already exists in table(s) that violates referential integrity3) Data types of related fields are incompatible or incorrectRelationship OptionsReferential Integrity - Ensures that related records in a database are consistent. Generally a good idea to enforce this option. When enforced, relationship line indicates 1 and ∞.Cascade Update & Cascade DeleteRelationships ExampleSubformsA form within a formDisplays data on one form from related tablesCan have subforms within subforms (nested) and multiple subforms on a single formSubformsSubforms ExampleAccess UtilitiesCompact & Repair Database – shrinks size of database. Access database files NEVER decrease in size unless compacted. May need to do this before copying a database to a floppy disk or emailing a database file.Click Office Button, Manage, Compact and Repair DatabaseGet External DataCan be used to import data or link existing database tables.ODBC link allows you to connect to non-Access databases such as Oracle.Quick ReviewWhich is the appropriate pairing for setting relationships?a. Text PK – Hyperlink FKb. Autonumber FK – Numeric PKc. Date PK – Text FKd. None of the aboveQuick ReviewA 1---∞ relationship exists between Physicians and Patients. Which is True?a. Physician ID will appear in the Patients tableb. Patient ID will appear in the Physicians tablec. Both (a) or (b)d. Neither (a) or (b)Quick ReviewWhich of the following will delete the relationship between tables?a. Click the join line connecting the two tables and press the Del keyb. Double click the join linec. Click the join field and press Deld. All of the aboveQuick ReviewEvery table in an Access databasea. Must be related to every other tableb. Must have one or more foreign keysc. Both (a) and (b)d. Neither (a) nor (b)Quick ReviewWhich of the following is true?a. A main form may contain multiple subformsb. A subform may contain another subformc. Both (a) and (b)d. Neither (a) nor (b)Quick ReviewTrue / FalseWhen creating relationships between tables in Access, you must relate the tables on fields with the same data types.Quick ReviewTrue / FalseA many-to-many relationship is implemented by a pair of one-to-many relationships.Homework 4Chapter 6 Guided ExerciseChapter 6 Applied
View Full Document