Return to IFS 110 Notes!

Tutorial 2

Designing and Documenting a Database

Why Use Access?

n     Speed to market

n     Ease of maintenance and enhancement

n     Flexibility and power to control interface

n     Speed up data conversion (connectivity to existing spreadsheets)

n     Low cost

n     10 – 15 concurrent users easily supported

n     Security capabilities

n     Small size less than 100 MB

n     Ease of upsizing or migrating database to larger platform in future  

Access Performance Considerations

n    Access allows for 255 concurrent users

¨  Not very efficient for more than 20 users

n    Factors effecting efficiency

¨  Number of processors

¨  Disk space and memory on file server

¨  Individual PC processor speed and memory

¨  Network bandwidth and speed

¨  Query efficiency

¨  Types of activities performed by users concurrently

Three-tier architectures

1. Dedicated database server

2. Application server – enforces business rules and logic

3. Front-end application (client-side application) – user interface

n           Access provide upsizing wizard to this environment where Access can continue to be used as the front-end application

¨        Forms, report, data access pages, macros, and modules could still be used after the migration  

Difference between VB and VBA

n     VBA is built around the object model of each application.

n     Word objects (documents, paragraphs, headers, etc.)

n     Excel objects consists of objects such as cells, worksheets, workbooks, etc. 

¨   VBA commands differ between applications.

n     In Access, VBA is used in conjunction with its object model consisting of tables, queries, forms, reports, etc.

n     To summarize

¨   VBA is both a subset and superset of Visual Basic

¨   VBA for Access is used to enhance the functionality of Access.

¨   VBA greatly enhance productivity as a database developer.

Data redundancy

n     Make sure students understand that the goal is to reduce redundancy NOT eliminate it.

¨   By its very definition, a relational database requires some data redundancy because that is how the relationships are established between the tables (i.e., common fields).

n     The process is called normalization and involves analyzing how data items are “dependant” on each other.

¨   Normalization is usually covered over several lecture periods in a database class.  For the purposes of this class, those decisions have been made for the student.

Creating Tables

n    Make sure tables have been copied to some storage medium other then their CD.

¨  Access continually updates a database and so the database must be open for “writing”.

¨  If they copied the database from a CD, they may need to remove the “Read Only” property for the table.

n   The easiest way to do this is to start Window Explorer, locate the file and click on it, right-click , then select properties from the dropdown menu.  In the Properties dialog box, there is a setting for “Read Only”, this needs to be Unchecked.

different versions of Access

n    There may be some question related to different versions of Access.

n    Access 2000 and Access 2002 use the exact same file format and thus are completely compatible.

¨  However, if the student attempts to open one of the databases provided in the data files, in an earlier version than Access 2000, they will have problems.

n   require that students use at least Access 2000 to do their work.

Primary Keys and Foreign Keys

n    Primary Key will uniquely identify a record.

¨  Sometimes the Primary Key consists of two fields, a Composite Key The primary key uniquely identifies a record. 

n    Foreign Key (sometimes called the join field) is a field in a table that refers to the Primary Key in another table.

¨  It is the Primary and Foreign Key combination that form the relationship between the tables.

composite key example

n    Problem: identify the student’s grade in a particular class. 

n    Solution: Student ID AND the Class Name (or Class number). 

¨  This is an example of a composite key, the combination of the Student ID and the Class Number

n   uniquely identify a grade for a particular student in a particular class. 

Indexing

n   Index – separate hidden table that consists of pointers to records or groups of records

¨ Maximum of 32 indexes

¨ Index can have a maximum of 10 fields

¨ Cannot index Memo, Hyperlink, or OLE Object data type fields

n     indexing tables  useful for sorting and searching the table. 

¨   over-indexing may cause a slow-down in processing the transaction because indexes must be updated after each addition and edit.

¨   indexes can be added liberally to tables that will not be transactional tables. 

n   Transactional Table A table in which the data entry process is ongoing

¨   every index actually creates a new file (in the background) and Access must maintain that file.

n   This will also cause the database to grow in file size which can sometimes cause a storage problem.

One-to-one relationship
(not a very common relationship)

n    One-to-one relationship A relationship between two tables exists when one entry in each table corresponds to only one entry in the other table

¨ usually used when large numbers of fields for records and the data would be easier to handle if it were separated into two tables.

¨ advantage is if some of the data is more sensitive then the rest of the data.

One-to-one relationship
(3 cases to split table)

1.  Avoid exceeding 255 field number maximum per table

2. To control access to fields in a table that are sensitive or confidential (payroll information)

3.  Data sparsity - Store data in a separate table when only a subset of the records use those fields

¨        Circumstances when certain fields are rarely used, cuts down on wasted storage space

n         (YTD fields payroll)

One-to-many relationship
(Most common type of relationship)

n    One-to-many relationship It exists between two tables when a related table has many records that relate to a single record in the primary table.

¨ Primary Table (or base table) The table that contains data about a person or object when there is only one record that can be associated with that person or object.

¨ classroom example: you could say that there is one teacher and many students. 

Many-to-many relationship

n     Many-to-many relationship A relationship between two tables and a third table, referred to as a junction table.

¨   Junction Table A table that contains common fields from two tables. It is on the many side of a one-to-many relationship with the other two tables

¨   Many-to-many relationships always require a third table to form the relationship as illustrated in Figure 2-10.

n     classic example: of many-to-many relationships is that of students and classes.  A student can have many classes and each class has many students.  Thus, the relationship between students and classes is a many-to-many relationship.

¨  determined by the rules of the organization.  

Referential Integrity

n   Referential integrity The requirement that a foreign key value in a related table match the value of the primary key for some row in the primary table.

¨ Orphan record A record in the related table that does not contain a record in the primary table        

n  This should be avoided in most cases

3 conditions must be met to enforce Referential integrity

1. Field on one side of relationship (in primary table) must be a primary key

2. Fields joining table must be same data type

   n          Exceptions:

   n         AutoNumber field related to Number field with field size property set to Long Integer

   n         AutoNumber field with field size property set to Replication ID related to Number field with field size property set to Replication ID

3. Both tables that are related must be part of the same database format

Rules with Referential Integrity set

n    Cannot enter foreign key value of a related table if primary table does not contain value

n    Cannot delete record from primary table if matching records exist in related tables

n    Cannot change value of primary key field if there is a related record in a related table

¨  Some restrictions such as deleting from primary table those records that have matching records in a related table, and changing the value of key in primary table, can be overridden with Cascade Update and Cascade Delete options

Cascading Updates and Deletes  
 with setting Referential Integrity

¨   Cascading Update option – change in primary key of primary table is automatically updated in all related tables

n   Problem if it has some other significance in other databases not automatically linked to this one

¨   Cascading Delete option – deleting a record in primary table automatically deletes any related records in all related tables

n   Not recommended – allowing deletions to take place without review through a standardized process is an unacceptable risk

n   Domino effect throughout entire database – grave consequences

n   Eradicate years of history in secondary table

n   Selective restore difficult and time consuming

Subdatasheets

n     Subdatasheet A datasheet that is nested within another datasheet and contains data related or joined to the first datasheet.

¨   Subdatasheet – allows viewing and editing of related or joined data in a table, query, form datasheet, or subform

¨   Access automatically creates a subdatasheet in a table that is in a one-to-one or a one-to-many relationship, as long as the Subdatasheet property of table set to Auto, which is the default.

¨   subdatasheet is associated with a related table. 

n   Tables that do not have a related table will not have a subdatasheet.

Dates in Access

n            100-year time span (Jan. 1, 1930 – Dec. 31, 2029) using MM/DD/YY format

3 levels of precautions (correct dates)

1.  Set Access to display four-digit year

   n          Least restrictive approach

2. Force users to enter four-digit year

   n          Apply input mask for all date fields

   n         Effective only on new data

3. Validation rules to test dates

n          Limits entries to particular range based on system date

n            Note: Will use all three methods in text tutorial  

3 parts of Input mask
(separated by semicolons)

1.  Placeholders for data entered

   n          99/99/0000 – 9’s (digits 0-9); 0’s (required numbers 0-9); slashes separators in dates

2.  0 or 1 code (0 default)

   n          0 – access stores literals included in input mask

   n         Literal character in mask not typed, dashes in SSN

   n         Good idea to store literals when creating input mask so that queries will work correctly later

   n          1 – indicates only typed data would be stored

3. Placeholder character – character that appears in the field, represents number of characters user should type (example underscore)  

Documenting Your Database

n      Data Dictionary A list and definition of the individual fields included in each of the tables in your database.

¨     data dictionary is extremely helpful when there are multiple people working on the database

¨     data dictionary makes it possible for all parties involved in the database to easily look up the table structures, their fields, and descriptions of the fields.

¨     The relationships report provides a detailed list of how table are related to one another

n      Database Documenter A feature that allows you to create a data dictionary quickly and easily

¨     Being able to turn to the documentation when trying to determine how a field is used is very useful to developers.

¨     Unless this documentation is kept up to date, it is virtually useless

¨     On very large database projects, often a single person is assigned to maintain the system documentation.

IFS 110 Computer Assignment 2

Return to IFS 110 Notes!