Tutorial 2
Building a Database and Defining Table Relationships

Objectives:

a. Learn the guidelines for designing database and setting field properties
b. Modify the format of a field in datasheet view
c. Create a table in design view
d. Define fields and specify a table's primary key
e. Modify the structure of a table
f. Import data from an Excel worksheet
g. Crate a table by importing an existing table structure
h. Add fields to a table with the Data type gallery
i. Delete, rename, and move fields
j. Add data to a table by importing a text file
k. Define a relationship between two tables.

Important Points.
1. Rules of Data Normalization: 1st Rule - Eliminate Repeating Groups - Make a separate table for each set of related attributes(fields), and give each table a primary key.
2. When creating a database the first question to ask is what is the output going to be.
3. Identify all of the fields needed to produce the required information.
3a. Decompose the fields into their smallest useful part.
4. Group related fields into tables.
5. Determine each table's primary key.
6. When more than one field is used as a primary key it is called a composite key.
7. Include a common field in related tables.
8. Avoid data redundancy.
9. Rules for naming fields.

a. A name can be up to 64 characters.
b. A name can contain letters, numbers, spaces, and special characters.
c. A name cannot start with a space.
d. A table or query name must be unique with a database. A field name must be unique with a table, but can be used in other tables.
e. Capitalize the first letter of each word in the name.
f. Avoid extremely long names.
g. Use standard abbreviations.
h. Do not use spaces in field names or object names.

10a. Determine the properties of each field.
10. The data type determines what field value you can enter for the field and what other properties the field will have.
11. The Field Size property defines a field value's maximum storage size for text, number, and Auto Number field only.

Table 1
Access Field Types

Field Types
Auto Number A number that is assigned automatically and never changes again.
Currency Dollar amounts
Date/Time Dates and times
Hyper Link Hyper Link address HTTP, FTP, E-mail, .doc, .etc..
Lookup Values that come from another table, a query, or a list of values you supply.
Memo Can contain numbers or letters up to 64,000 including spaces
Number Byte (0 to 255), Integer(-32768 to 32767), Long Integer(-2,147,483,648 to 2147483647), Single precision( 7 decimal places, 4 bytes), Double precision(15 decimal places, 8 bytes). Decimal(28 decimal places, 12 bytes),Replication ID.(16 bytes)
OLE - object Picture, sound, or video
Text Can contain numbers or characters up to 255 bytes including spaces.
Yes/NO Can contain Boolean values (yes/no, true/false, on/off) Can contain only one of two values
Attachment use to attach external files word, excel, graphics and stores the attachments in compressed form.
Calculated Not a true field in table but used in reports, forms and queries to store the results of an expression

12. The structure of the table can be modified at any time. Fields can be added, deleted, and properties changed.
a. Changing the data type of a field in datasheet view: Open the table in datasheet view, select the field, in the data type & Formatting group on the datasheet tab, click data type arrow, click the new data type you want.
b. Changing the format in datasheet view- Open the table in datasheet view, select the field you want to change, in the data type & formatting group, click the format arrow, select the format you want.
c. Creating table in design view - click create tab on the ribbon, in tables group click table design button. You will see:
creating table in design view

 In design view you define or modify a table structure or the properties of the field in a table.
d. Defining a field - in the field name box type the name for the field, press tab, select the data type, use the file properties pane to type or select other fields properties as appropriate.
e.Defining a Date field :  type in birthdate for the field name, select date/time for the field type, then click on the format property:  You will see:
The date field format properties
f. Here you can select one of the date types or you can specify a custom date in the format box. 

Symbol Description
d day of the month in one or two numeric digits, as needed(1 to 31)
dd day of the month in two numeric digits (01 to 31)
ddd first thee letters of the weekday(sun to Sat)
dddd full name of the weekday (Sunday to Saturday)
w day of the week (1 to 7)
ww week of the year (1 to 53)
m month of the year in one or tow numeric digits, as needed (1 to 12)
mm mojnth of the year in two nemeric digits (01 to 12)
mmm first three letters of the month (Jan to Dec)
mmmm full name of the month (January to December)
yy last tow digits of the year (01 to 99)
yyyy full year (0100 to 9999)
Custom Date Formats
13. Primary Key -
a. Unique for each record
b. Can never be null
c. Is the 1st index of the table.
d. Specifying a primary key in design view.
c. display the table in design view, click the field row that you want to be the primary key. You will see:
Selecting a primary key in table design view

e. In this case we are going to make Id the primary key.  Note that the index property before selecting it as primary key is no. Click the primary key button.
You will see:
Id field selected a primary key

f. There is now a key button next to the id field name, and the index now reads yes(No Duplicates).
14. Modifying the structure of an access table.
a. to move a field in the design structure click the row selector for the field you want to change, click it a second time and hold down the mouse, then drag the pointer to the position you want the field to now occupy in the field list.
b. Add a field between two existing fields: select the row below where you want to add a field, in tools group clickinset rows button, define the new field.
c. To modify a fields properties, click on the field in design view, press f6 or click down in the properties menu, make the changes you want to the fields properties.
15. Importing data from an excel worksheet.
a. You can copy and paste from an open file or you can import which allows you to copy the data from a soure without having to open the source file.  Note:  make sure your the column headings in your excel spread sheet match the filed names in the database, not the caption names.
b. Click
external data on ribbon, then click excel, you will see:
import excel dialog box
c. click browse and locate the excel spreadsheet that you want.
d. click append a copy of the records to the table and select the table you want to import to.
e. click ok, You will see:
Import shreadsheet

f. select the worksheet you want to import and click next:  you will see:
Import speadsheet select row header

g.  click next: You will see:
import speadsheet dialog box identify table.

h. Click finish.
16.Creating a table by importing an existing table structure.
a. click External data tab, click access in imports & link group you will see:
Import access table structure

b. Browes to the database you want to import from , click import tables, queries, forms, reports,macros, and modules into the current database, click ok.  You will see:
Import access database objects
c. Click the option button: you will see:
import access database options menu
f. Select definition only option and click ok.You will see:
final import database dialog box

g. Click close without saving import steps.
17. Data type gallery allows you to add a group of releated fields to a table at the same time. Insert your cursor in the datasheet in the field that you want the new fields to be inserted to the left.
a. Click fields, click more fields - you will see:
Data Type Gallery dialog box
b. Scroll down to quick start You will see:
Quick Start menu
c. click address under Quick Start which will insert the address, city, state Provine, Zip Postal, and Country Region fields.
you will see:
dispaly of fields inserted into table

18. Deleting a field from a table structure
a. In datasheet view, click the column heading for the field you wasnt to delete, then in the add delete group on the fields tab, click the delete button.
b. In design view, click the  field name box for the field you want to delete, in the tools group on the design tab, click delete rowes button.
19. Rename fields in design view. Put the cursor in the field name and make the modifications to the name you want.
20. Change data type in design view. Select the field you want, click the right side of the data type box, select the new data type you want and modify the properties that you want.
21. Adding data to a table by importing a text file.
a.Click External data tab, in import & links group, click text file you will see:a.import text file  dialog box

b. browse to file to be imported, click append  a copy of the records to the table, select the table, click OK.You will see:
Import text file second dialog box

c. Select delimited, and click next you will see:

delimited text dialog box

d. Make sure the comma delimiter is selected and click next.  You will see:
import text file dialog box 4

e. click finish- you will see:
import text final dialog box

f. click close:

22. Defining Table  Relationships.

a A join is the process of relating tables together with a common field.
b. A one to many relationship exists
between two tables when one record in the primary table(the one) matches zero or many records in the related table(the many).And one record in the releated table matches only one record in the primary table.
c. Referential integrity -
you can not enter a record in the common field of the related table unless it already exist in the primary field of the primary table. This prevents orphaned records in the related table.
d. Cascade update
permits the change in the value of the primary key and changes the appropriate foreign key values in the related tables.
e. Cascade deletes-
deletes record in the primary table and deletes all records in related tales. Do not check this one.
f. The relationship window illustrates the relationships among the tables. Relationships can be created, modified, or deleted from here.
g. To define a relationship:
Click the database tools tab on the ribbon, in the show/hide group on the database tools tab click the relationships button, You will see:
Releationship window for joins

h. double click the tables you want to create a relationship with, click close on the show table dialog box You will see:
releationship window dialog
i. use the pointer to drag the primary key in the primary table to the common key (foreign key) in the related table(s)
You will see:

Join dialog box

j.  click enforce referential integrity, click cascade update related fields, click create.
You will see:

Join window

Best Practice: When setting up a database in a company, make sure that you discuss the project with everyone who will be using the database. The executive director will request different data then will one of his subordinates. The key person is the data input clerk. Data integrity must be insured and the data input clerk is the one putting the data into the program. If they are unclear as to their data entry requirements data error will be the rule.