db implementation ms access tables n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
DB Implementation: MS Access Tables PowerPoint Presentation
Download Presentation
DB Implementation: MS Access Tables

Loading in 2 Seconds...

play fullscreen
1 / 21

DB Implementation: MS Access Tables - PowerPoint PPT Presentation


  • 83 Views
  • 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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
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