Db implementation ms access tables
1 / 21

DB Implementation: MS Access Tables - PowerPoint PPT Presentation

  • Uploaded on

DB Implementation: MS Access Tables. 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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'DB Implementation: MS Access Tables' - abiola

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Db implementation ms access tables

DB Implementation:MS Access Tables

Database Design

Rdb implementation overview
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

Rdb implementation table
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

Ms access table data types
MS Access Table: Data Types

MS Access 2007: Data Types & Field Properties

Database Design

Ms access table field properties
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

Field property field size
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

Field property text format
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

Field property number format
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

Field property date time format
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

Field property input mask
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

Field property format vs input mask
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

Field property validation rule
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

Access table misc field properties
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

Ms access null vs zero length string
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

Ms access lookup field
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

Ms access referential integrity
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

Table how to overview
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

Table how to creation
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

Table how to fields
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

Table how to primary key
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

Table how to relationships
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