Access Tables with Attributes and Validation

Site Map
 

Objective:

To create Access tables that supports your database plan.  To gain experience using the metadata construction tools of Access.  To create a data dictionary.   To see how databases can ensure validity of data via table lookup and validation rules that support the business rules.

Assignment:

Create 3 or more tables as defined by your E-R model. 

Details:

Select "Create table in Design view".

There are 3 columns Field Name (Attribute name), Data type, Description.  In the lower right hand corner is blue text that has a bit of help and changes as you move from column to column. It reminds you that you should press F1 for help.  Access provides lots of help, just ask and always get as close to the "problems" as possible before asking so that your answer will be an answer to the question you have.

Attributes must be atomic.  That means:

Be sure to give complete names like Student_Home_City 
Take care to avoid:

Avoid abbreviations and avoid very long names.  This is compromise.  If you abbreviate something,  stick to that abbreviation scheme throughout the database (consistency is the goal).  For example, if you abbreviate address as "addr", then ALWAYS use addr, student_addr, prof_addr, etc, never use the full word address.

Access permits spaces in attribute names, but most other DBMS do not.  So instead of a space use an underscore or nothing.

Choose the appropriate data type from the dropdown

That will cause the Field Properties box to display proprieties. 

For text fields:

Select a format for date and Number (Byte must be less than  256, but don't cut it close, use only for things that will be under 100.  Integer for 32k or less, again stuff that is in the low thousands, everything else Long Integer.

Default values make entry easier because that value is already keyed, but can be changed.  Required can be dangerous because if the person does not know what goes into the field they will make something up just to get the computer off their back and there is no way to find out what is a made up value and what is a correctly entered value.  If a field is not required and nothing is put there (including no default), then the filed will get a null value and a query can be make to find all rows with null values.

Validation rules ensure good data but like Required if they are too strict, data entry personnel will make up values that satisfy the system.   

Well worded Descriptions of each attributes should be given.  The function of  Access's description field is to provide a Data Dictionary definition for the the attributes in the table.  Do not simply repeat the attribute name in the description.  Don't waste description space repeating the attribute name, provide additional description of what function the attribute plays.  What is it? If validation is used, it should be explained.  Foreign keys should be noted.  Example data provides very descriptive information. 

Attributes should be indexed if queries are made based upon those values.  For example, if the data is frequently requested based upon date, then date should be indexed. 

When you are done, click the close X.  Access won't let you leave without saving.  Select a meaningful entity name for your table.  Access would also like you to give the table a key, it will even give you an Autonumber is you can't thing of a key.  At this point we have not covered keys so tell Access to forget it.  You will identify the primary key later.

Create is one table per entity.  Give each table a meaningful name and Description.  Note that descriptions will appear on the Data Objects window if you select the last icon to the right.  You can then increase/decrease the size of the window, the size of each column (Name, Description and Modified).  You can also sort the tables in ascending or descending order by clicking on the word Name, Description or Modified.

Procedure: