1 / 22

DB Implementation: MS Access Tables

This guide covers key attributes, data types, field properties, and table design considerations in MS Access for efficient database implementation. Learn how to optimize data storage and validation rules for better database management.

dcox
Download Presentation

DB Implementation: MS Access Tables

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. DB Implementation:MS Access Tables S511 Session 6, IU-SLIS

  2. Outline • Overview of RDB Implementation • Access Table • Data Types • Field Properties • Relationships MS Access File for Lecture S511 Session 6, IU-SLIS

  3. RDB Implementation: Overview • Tables • Translation of ER model in RDBMS construct • For data storage • entities, attributes, relationships • Forms • Data input/display user interface • e.g. data entry form, search form • For viewing, editing, entering, dynamic querying • Queries • Relational algebra operations • combination of join, select, sort, filter, modify, etc. • For data retrieval & manipulation • Reports • Data display output • e.g. monthly reports (set query results) • Forms can manipulate data, but reports cannot S511 Session 6, IU-SLIS

  4. RDB Implementation: Table • Table is the fundamental element in a relational database. • Contains data about a particular subject • row = record = an entity (e.g., an employee, a product) • column = field = an attribute (e.g., last name, product description) • Users seldom see tables if at all. • Data entry is usually done via forms. • Main issues in table design • What are the key attributes? • i.e., primary & foreign keys • What is the data type for each attribute? • e.g. text, number, date, etc. • What are the properties for each attribute? • e.g. size, input/output format, range, etc. S511 Session 6, IU-SLIS

  5. MS Access Table: Data Types MS Access 2007: Data Types & Field Properties S511 Session 6, IU-SLIS

  6. MS Access Table: Field Properties • Field Size • to set the maximum size for Text, Number, or AutoNumber data type. • affects the stored value (via rounding, truncation) • Format • to customize the display/print layout of data • does not affect the stored value (e.g. Currency  $1,234.50) • Input Mask • to set the data input template to guide data entry • e.g. Short Date  MM/DD/YYYY  99/99/0000;0;_ • Validation Rule • to validate/restrict data entry • use with Validation Text to display message on validation failure • an expression that specify valid data values MS Access 2007: Field Properties Quick Reference S511 Session 6, IU-SLIS

  7. Field Property: FieldSize • Text data type • 1 to 255 • Number data type number properties • determines the precision of the number that can be stored • precision = number of significant digits • AutoNumber is 4 byte • use Long Integer for foreign keys that refer to AutoNumber primary keys • For best performance, use smallest sufficient field size S511 Session 6, IU-SLIS

  8. Field Property: TextFormat • Determines how to display the contents of data in tables • does not affect the stored value • For enhancing readability • e.g., credit card/phone number • @@@-@@-@@@@:123456789  123-45-6789 • Syntax • format for non-empty text; format for empty text • e.g., @;”None”  display data as is, or ‘None’ when empty MS Access 2007: Custom Format for Text & Memo data types S511 Session 6, IU-SLIS

  9. Field Property: NumberFormat • Pre-defined formats • Currency e.g. displays ‘1234.567’ as ‘$1,234.56’ • Percent e.g. displays ‘1234.567’ as ‘123456.70%’ (requires size = “double”) • Scientific e.g. displays ‘1234.567’ as ‘1.23E+3’ • Examples • 0;(0);;”Null” • display positive numbers normally, negative numbers in parenthesis, “Null” if the value is null MS Access 2007: Custom Format for Number data types S511 Session 6, IU-SLIS

  10. Field Property: Date/TimeFormat Example: ddd", "mmm d", "yyyy  Fri, Feb 28, 2003 MS Access 2007: Custom Format for Date/Time data types S511 Session 6, IU-SLIS

  11. Field Property: Input Mask • Data input template • a set of literal and mask characters that control what can and cannot be entered in a field • to prevent invalid data entry • e.g. (999) 000-0000;;_ • data entry: (___) ___-____  (812) 855-5111 • data storage: 8128551111 • area code is optional • e.g. >L<?????????????? Input Mask Character Reference • A first or last name with the first letter automatically capitalized MS Access 2007: Input Mask S511 Session 6, IU-SLIS

  12. Field Property: Format vs. Input Mask • Input Mask property • to control how data is entered • affects how data is displayed during data entry • can affect how data is stored • Format property • to display data in a consistent format • affects how data is displayed after it is saved • does not affect how it is stored • Decimal Places property (for Number data type) • specifies the number of decimal places to display • does not affect how it is stored • must use Field Size property to change how a number is stored • has no effect if Format property is blank or set to “General Number” S511 Session 6, IU-SLIS

  13. Field Property: Validation Rule • Validates & Restricts data entry • Validation Text holds the error message to be displayed • use “expression” to define validation rules • expression is a combination of operators, fields, functions, etc. • expression can be constructed using the Expression Builder • Other Data validation in MS Access • Data Type – e.g., Date/Time • Field Size – e.g., 20 char for last name • Required– e.g., Yes to require data entry • Input Masks– e.g., 00000-9999 for zipcode • Examples MS Access 2007: Validation Rule S511 Session 6, IU-SLIS

  14. Access Table: Misc. Field Properties • Indexed • create internal record of the field values for searching • Access updates all of its indexes every time a table record is added or updated • too many indexed fields can slow down data entry • Primary key field should be set to “Yes (No Duplicates)” • exception: set composite primary key fields to “Yes (Duplicates OK)” • Required • Yes/No to indicate whether the data entry is mandatory • Allow Zero Length • Yes/No to indicate whether a zero-length string (“”) is a valid entry (default=yes) • Caption • label for the field (default= field name) • Default Value • value automatically entered for new records • Unicode Compression • compresses unicode character representation • 2 bytes to 1 byte when the first byte is 0 (e.g. English) • IME Mode • set Input Method Editor Mode (e.g. Hangul) MS Access 2007: Field Properties Quick Reference S511 Session 6, IU-SLIS

  15. MS Access: Null vs. Zero-length string • Null • A variable state indicating missing data • Information that may exist but is unknown • i.e., “I don’t know what the value is.” • e.g., CUS_FAXNUM is set to Null initially. • Created by pressing ENTER key in a field • Zero-length string • To handle unknown/non-existent values in required fields • Information that does not exist • i.e., “I know there is no value.” • e.g., CUS_FAXNUM is set to zero-length string after discovering that the customer has no fax. • Created by entering two double quotes (“”) in a field S511 Session 6, IU-SLIS

  16. MS Access: Lookup Field • Lookup Field/Column • Displays a list of choices in a combo box or list box. • The list is retrieved from another table/query or a value list. • Creating a Lookup field via Lookup Wizard • Select Lookup Wizard in Data Type column. • The Lookup Wizard starts • Select the data source in Lookup Wizard • Look up the values in a table or a query, or • Type in the values. • For Table/Query-based lookup field, • Choose a table/query • Select fields (visible + data store) For Value list-based lookup field, • Enter the number of columns to include in the lookup field. • Type the values in the grid • Choose the filed whose value will be used MS Access 2007: Lookup Field S511 Session 6, IU-SLIS

  17. MS Access: Referential Integrity • Necessary Conditions • Matching field from “one” side of related tables • must be a primary key or have the Indexed property set to “Yes (No Duplicates)”. • Related fields must have the same data type. • exception: AutoNumber to Number when both have the same Field Size • Effects • Cannot enter a value in the foreign key field that does not exist in the primary key of the primary table • Cannot delete a record in the primary table if matching records exists in the related table • Cannot change a primary key value in the primary table if that record has related records. • Cascading Updates/Deletes • Allows automatic updates/deletes of all related records along with primary record • e.g. change in primary key value in the primary table will be reflected in foreign key value of the related table • Enabled/disabled in the relationships window S511 Session 6, IU-SLIS

  18. Table How-To: Overview • Create a table. • Table Design in Create tab • Add fields to the table • Data Type • Field Properties • Set the primary key • Save & name the table • Set the relationships S511 Session 6, IU-SLIS

  19. Table How-To: Creation • Create a table in a new database • Click the Microsoft Office Button , and then click New. • In the File Name box, type a file name for the new database. • To browse to a different location to save the database, click the folder icon. • Click Create. • The new database opens, and a new table named Table1 is created and opens in Datasheet view. • Create a table in an existing database • Click the Microsoft Office Button , and then click Open. • In the Open dialog box, select the database that you want to open, and then click Open • On the Create tab, in the Tables group, click Table Design • A new table is inserted in the database and the table opens in Design view S511 Session 6, IU-SLIS

  20. Table How-To: Fields • Add a field to an existing table • Open a table in Design View. • Type a name for the field in the Field Name column. • Select an appropriate data type in the adjacent Data Type column. • Text, Memo, Number, Date/Time, Yes/No, etc. • Set Field Properties. • Field Size, Format, Input Mask, Validation Rule, etc. • Lookup Wizard S511 Session 6, IU-SLIS

  21. Table How-To: Primary Key • Set a table's primary key • Select the table whose primary key you want to set or change. • On the Home tab, in the Views group, click View, and then click Design View. • In the table design grid, select the field or fields that you want to use as the primary key. • To select one field, click the row selector for the field that you want • To select more than one field, hold down CTRL, and then click the row selector for each field • On the Design tab, in the Tools group, click Primary Key. • A key indicator appears to the left of the field or fields that you specify as the primary key • To add an Autonumber primary key • In the Field Name column of an empty row, type a name (e.g., CustomerID) • In the Data Type column, click the drop-down arrow and click AutoNumber. S511 Session 6, IU-SLIS

  22. Table How-To: Relationships • Create table relationships • Open a MS Access database. • On the Database Tools tab, in the Show/Hide group, click Relationships. • On the Design tab, in the Relationships group, click Show Table. • If you have not yet defined any relationships, the Show Table dialog box automatically appears. • Select tables and then click Add. When finished, click Close. • Click Tables in the Show Table dialog box to see only tables. • Drag the primary key from one table to the foreign key in the other table. • To drag multiple fields, press the CTRL key, click each field, and then drag them. • Verify that the field names shown in the Edit Relationships dialog box are correct. • If a field name is incorrect, click the field name and select a new field from the list. • Select the Enforce Referential Integrity check box. • Click Create. S511 Session 6, IU-SLIS

More Related