DB Implementation MS Access Tables Database Design Seminar

DB Implementation: MS Access Tables Database Design Seminar 1

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

RDB Implementation: n 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. n 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 Seminar 3

MS Access Table: Data Types Text • Alphanumeric data up to 255 characters. Memo • Alphanumeric data with more than 255 characters • Used for notes or comments: Cannot search or index on memo data type fields Number • Numeric data for fields that need to have computational operations done Auto. Number • Unique values created by Access for each new record (sequential number) → Cannot be changed once created Date/Time • Dates and Time values for the years 100 through 9999. Currency • Monetary values Yes/No • Boolean (true/false, Yes/No, On/Off) → good for checkbox type field Hyperlink • Links that opens a file or a Web address (e. g. URL, email address) Attachment • Any supported files to attach (image, spreadsheet, document, chart, etc. ) Lookup Wizard • Tool to create lookup fields (i. e. , dropdown list) • Displays values derived from existing table/query or a manually specified list Calculated Field • Results of a calculation (must be based on fields in the same table) Database Design Seminar MS Access: When to use which data type 4

MS Access Table: Field Properties Field Size • To set the maximum size for data type (e. g. , Text, Number) • Affects the stored value (via rounding, truncation) & DB size 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; _ • Format vs Input Mask • Display vs Data Entry Validation Rule • To validate/restrict data entry • Use with Validation Text to display message on validation failure • An expression that specify valid data values Database Design Seminar MS Access: Data Type Reference 5

MS Access: u 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. • u List Box Creating a Lookup field via Lookup Wizard Select Lookup Wizard in Data Type column. 1. The Lookup Wizard starts · Select the Data Source in Lookup Wizard 2. Combo Box Look up the values in a table or a query, or Type in the values. · For Table/Query-based lookup field, 3. Choose a table/query ii. Select fields (visible + data store) i. For Value List-based lookup field, Enter the number of columns to include in the lookup field. ii. Type the values in the grid i. 4. Choose the field whose value will be used Database Design Seminar MS Access: Lookup Field 6

MS Access: n 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: Auto. Number to Number when both have the same Field Size n 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. n 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 Seminar 7

Table How-To: 1. Overview Create a table. Table Design in Create tab 2. Add fields to the table Data Type Field Properties 3. Set the primary key Select the PK field Click the Primary Key icon (Design tab) 4. Save & name the table 5. Set the Relationships (Database Tools tab) Database Design Seminar 8

Table How-To: Creation n Create a table in a new database 1. 2. 3. 4. Click the File tab, 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. • A new database opens, and a new table named Table 1 is created and opens in Datasheet View. 1. a 4. a 3. b 3. a 2 4. b 1. b Database Design Seminar 9

Table How-To: Creation n Create a table in an existing database 1. 2. 3. Click the File tab, 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 1. a 1. b 2 3 Database Design Seminar 10

Table How-To: Fields n Add a field to an existing table 1. Open a table in Design View. 2. Type a name for the field in the Field Name column. 3. Select an appropriate data type in the adjacent Data Type column. • 4. Field Properties Text, Memo, Number, Date/Time, Yes/No, etc. Set Field Properties. • • Field Size, Format, Input Mask, Validation Rule, etc. Lookup Wizard Database Design Seminar 11

Table How-To: Primary Key n Set a table's Primary Key 1. 2. 3. 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. • • 4. 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 2. a 3 4 2. b u To add an Autonumber primary key • • In the Field Name column of an empty row, type a name (e. g. , Customer. ID) In the Data Type column, click the drop-down arrow and click Auto. Number. Database Design Seminar 12

Table How-To: Relationships 1. Click Relationships in the Database Tools tab. 2. Click Show Table in the Design tab. 3. Select tables and then click Add. When finished, click Close. 4. Drag the Primary Key from one table to the Foreign Key in the other table. • 5. 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. • 6. Click Create. Database Design Seminar 13

MS Access Field Properties • • Database Design Seminar Field Size Format Input Mask Validation Rule 14

Field Property: Field Size n Text Data Type 1 to 255 n Number Data Type Determines the precision of the number that can be stored • precision = number of significant digits Autonumber is 4 byte Setting Storage Value (Min, Max) Byte (0, 255) N/A 1 byte Integer (-32, 768, 32, 767) N/A 2 bytes Long Integer (– 2, 147, 483, 648, – 2, 147, 483, 647) N/A 4 bytes Single (– 3. 4*1038, +3. 4*1038) 7 4 bytes Double (– 1. 797*10308, 1. 797*10308) 15 8 bytes Decimal (-9. 999*1027 , 9. 999*1027) 28 12 bytes Database Design Seminar Significant Digits Storage Size 15

Field Property: Text Format Symbol Description Example @ Required character (including space) (@@@@@): abc ( abc) & Optional character (&&&&&): abc (abc) < Force all characters to lowercase. <(@@@): ABC (abc) > Force all characters to uppercase. >(@@@): abc (ABC) [color] Display the field’s content in the named color >(@@@)[red]: abc (ABC) Display next character as entered. &!: abc! “text” Display the text btw. quotes. &“ kg”: 16 kg * Fill all blank spaces with the next character. &*? : five? ? (display length=8) ! Fill from left instead of right. !*? : five ? ? five (display length=8) n Determines how to display the contents of data in tables u n For enhancing readability u u n does not affect the stored value e. g. , credit card/phone number @@@-@@-@@@@: 123456789 123 -45 -6789 Syntax u u format for non-empty text; format for empty text e. g. , @; ”None” display data as is, or ‘None’ when empty Database Design Seminar MS Acces: Format for a Text Field Number Format 16

Field Property: Number Format Symbol Description Example 0 Display a digit or 0. 00, 000: 1234 01, 234 # Display a digit or nothing. ##, ###: 1234 1, 234 % Multiply by 100 and append % sign ##. #%: 0. 1234 12. 3% Section Description First Format for positive numbers Second Format for negative numbers Third Format for zero values Fourth Format for Null values n Pre-defined formats Currency Percent Scientific n e. g. displays ‘ 1234. 567’ as ‘$1, 234. 56’ e. g. displays ‘ 1234. 567’ as ‘ 123456. 70%’ (requires size = “double”) e. g. displays ‘ 1234. 567’ as ‘ 1. 23 E+3’ Examples Date/Time Format 0; (0); ; ”Null” • display positive numbers normally, negative numbers in parenthesis, “Null” if the value is null Database Design Seminar MS Access: Format a Number or Currency Fields 17

Field Property: Date/Time Format Symbol Description d Day of the month in one or two digits (1 to 31) dd Day of the month in two digits (01 to 31) ddd First three letters of the weekday (Sun to Sat) dddd Fullname of the weekday (Sunday to Saturday) m, mmm, mmmm Month equivalent to d, ddd, dddd y Number of the day in a year (1 to 366) yy Last two digits of the year (01 to 99) yyyy Full year (0100 to 9999) w Day of the week (1 to 7) h/hh, n/nn, s/ss Hours, Minutes, Seconds (1 digit/2 digit) Pre-defined format Description Short/Medium/Long Date e. g. 2/28/2003, 28 -Feb-03, Short/Medium/Long Time e. g. 13: 00, 1: 00 PM, 1: 00 PM General Date Combination of Short date and Long Time as needed e. g. 2/28/2003 1: 00 PM Friday, February 28, 2003 Example: ddd", "mmm d", "yyyy Fri, Feb 28, 2003 Database Design Seminar MS Access: Format a Date/Time Field 18

Field Property: Input Mask Symbol Description 0 Required digit (0 to 9) 9 Optional digit or space (0 to 9) L Required letter ? Optional letter A Required letter or digit a Optional letter or digit & Required character or space C Optional character or space # Required digit, space, or +/- Password Display entry with * Section Description First Specifies the input mask Second Specifies whether the data is stored with the literal display characters. - 0 to include literal characters, 1 or blank to store typed characters only Third Specifies the display (i. e. , mask) character for data input positions n Data input template u a set of literal and mask characters that control what can and cannot be entered in a field • u e. g. (999) 000 -0000; ; _ • • • u to prevent invalid 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 Database Design Seminar MS Access: Control data entry formats with input masks 19

Field Property: Format vs. Input Mask n Input Mask property To control how data is entered Affects how data is displayed during data entry Can affect how data is stored n 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 n 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 Seminar 20

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 Misc. Properties Validation Rule Description <> 0 Entry must be non-zero >1000 Or Is Null Entry must be greater than 1000 or blank Like “A? ? ” Entry must be 5 characters and begin with the letter “A” >= #1/1/96# And <#1/1/97# Entry must be a date in 1996 Database Design Seminar MS Access: Validation Rule 21

Access Table: Misc. Field Properties u 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 → • Primary key field should be set to “Yes (No Duplicates)” → u label for the field (default= field name) Default Value • u Yes/No to indicate whether a zero-length string (“”) is a valid entry Caption • u Yes/No to indicate whether the data entry is mandatory Allow Zero Length • u value automatically entered for new records Unicode Compression • compresses Unicode character representation → u exception: set composite primary key fields to “Yes (Duplicates OK)” Required • u too many indexed fields can slow down data entry 2 bytes to 1 byte when the first byte is 0 (e. g. English) IME Mode • Null vs. Zero Length String set Input Method Editor Mode (e. g. Hangul) Database Design Seminar MS Access: Text Field Properties 22

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 Seminar 23