classnotes week02

Walter’s Teaching
Pages

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.
    2.03

  • 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

4
Standard Rules

4
Additional suggestions.

Data Types

  • You must
    assign a data type to a field

  • The data
    type
    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.
    2.15

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

Access
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.
2.27


  • 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.
2.29

Deleting Records – Pg. 2.30

Changing Records – Pg. 2.31



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