DB Implementation: MS Access Tables

1. DB Implementation:MS Access Tables Database Design

2. 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 Database Design

3. 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. Database Design

4. MS Access Table: Data Types MS Access 2007: Data Types & Field Properties Database Design

5. 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 Database Design

6. Field Property: FieldSize • Text data type • 1 to 255 • Number data type • determines the precision of the number that can be stored • precision = number of significant digits • Autonumber is 4 byte Database Design

7. 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 Database Design

8. 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 Database Design

9. Field Property: Date/TimeFormat Example: ddd", "mmm d", "yyyy  Fri, Feb 28, 2003 MS Access 2007: Custom Format for Date/Time data types Database Design

10. 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<?????????????? • A first of last name with the first letter automatically capitalized MS Access 2007: Input Mask Database Design

11. 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” Database Design

12. 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 Database Design

13. 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 • 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 Database Design

14. 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 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 Database Design

15. 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 Database Design

16. 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 Database Design

17. 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 Database Design

18. 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 Database Design

19. 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 Database Design

20. 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. Database Design

21. 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. Database Design