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