classnotes week02

Walter’s Teaching

Class Notes – Programming Concepts Databases – Week 02

Homepage ] Office Hours ] Class Listings ] Lab Schedule ] Student Login ] Some interesting links ]

Class Notes
– Programming Concepts – Databases

Tutorial 2 –
Maintaining a Database


Session 2.1

Guidelines for Designing a Database – Pg. 2.02

  • DBs are
    useful tool, but only if they are carefully designed so to fit the needs of
    the user.

  • Several
    rules to building a DB:

  • Identify all
    required fields

  • Group
    related fields into tables

  • Determine
    the primary key

    A Composite Key is a primary key
    where several fields are used together as the PK

  • Include a
    common field with the Tables –  Pg.

  • Avoid Data
    Redundancy (Normalization/Normalized Data)

  • Determine
    the properties of each Field – PG. 2.04

    Properties are characteristics of
    each field like the name, size, description, valid values, etc.

Guidelines for Designing Access Tables

Naming Fields and Objects. 
Pg. 2.04

Standard Rules

Additional suggestions.

Data Types

  • You must
    assign a data type to a field

  • The data
    determines what types of information the field will store

  • Figure 2-4
    list the 9 types of data which Access Supports


Field Size – Pg. 2.05

  • Defines the
    maximum storage size for text, numbers, and auto numbers only.

  • Access
    processes smaller data sizes faster, and they require less memory/disk
    storage space so you can optimize your DB performance by using data which is
    "just big enough" – Pg. 2.06

  • Numeric
    Sizes listed on Pg. 2.06

Creating a Table

Defining Fields – Pg. 2.08

  • OrderNum as
    Text – Size 3 – PK

  • CustomerNum
    as Text – Size 3 – FK

  • BillingDate
    as Data – Format mm/dd/yyyy – Date Format Options listed on Pg. 2.12

  • PlacedBy

  • InvoiceAmt

Specifying the Primary Key – Pg. 2.14

  • Access does
    not require a PK

  • Useful to
    have a PK however, some reasons why listed on Pg. 2.14 – 2.15

  • Faster
    response to queries based upon the PK – also referred to as indexing – Pg.

Saving the Table Structure – Pg. 2.16

Records to
add to the table – Pg. 2.17

Saving the DB – Pg. 2.20

  • Access saves
    the active DB to your disk automatically, both on a periodic basis and
    whenever you close the DB.


Session 2.2

allows you to modify your table’s structure in Design View – Pg. 2.21

Deleting a Field –

  • When you delete a field, you also delete all associated
    field values from the table

Moving a Field – Pg. 2.22

  • Use the mouse to select the field you would like to move

  • Hold down with the left mouse button and drag the field to the location
    would like it to be.

Adding a Field – Pg. 2.23

  • To add a field between two existing fields, you must insert a new row.

  •  To insert a row, select the row below where you would like to add the new
    row, right click, and select Insert Row

Changing Field Properties – Pg. 2.24

Copying records from another database/table – Pg.

  • You can copy
    and past records from other tables, even in other DBs

  • But only if
    they have the same “structure” – i.e. same fields (names), of same
    size, in the same order

Using the Windows and Office Clipboards – Pg.

Deleting Records – Pg. 2.30

Changing Records – Pg. 2.31

Copyright ©2001-2002, Walter Wimberly – Instructor and all
’round Cool Guy – IADT