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
Session 2.2
Access
allows you to modify your table’s structure in Design View – Pg. 2.21
Deleting a Field –
Moving a Field – Pg. 2.22
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
|