EquipRD! Documentation - Rules Checking

Rules Checking
Home > Software > Rules Checking

Jump to Rules for a Particular Table:
Users | Parts | Checkout

Rules Checking to Ensure Database Consistency
To ensure that the database remains only in consistent states, attempts to interact with the database must be checked against rules that determine whether the attempt is a valid one. Two factors can make an attempt invalid:

Invalid Input Data Type - The data entered must match the type expected. For example, it would be inconsistent to enter "Sean" into a User ID field expecting a 32-bit integer.

Inconsistent Manipulation of Data - If a value changes, these changes must be reflected throughout the entire database. For example, it would be inconsistent to check out a part against a user and not decrement the value in that part's Available field, as doing so would create an "extra" part in the system that did not physically exist.

Impossible Manipulation of Data - A user must not be able to cause a change in the system that is inconsistent with physical reality or the IMS structure itself. For example, it would be inconsistent to check out a part if that part's Available field equals 0, as it is physically impossible to check out a part that is not available.

The rules below are grouped by table. Each field has its own set of rules. Security-related rules have not yet been devised.

Rules for Users Table (back to top)
Each field's rules are checked on any operation on any field of any record in the Users table. Rules may be added, deleted or updated as the need arises. All numerical fields are unsigned.

UserID
1. Must be an 32-bit integer
2. Primary key (each record must have a unique value in its UserID field)

FirstName
1. Must be 1-20 characters in length
2. Can only contain A-Z, a-z, hyphens and spaces
3. Cannot begin with a space or hyphen

LastName
1. Must be 1-20 characters in length
2. Can only contain A-Z, a-z, hyphens and spaces
3. Cannot begin a space or hyphen

Password
1. Must be 6-20 characters in length
2. Can only contain A-Z, a-z, 0-9

Roles
1. Must be one or more of the following values: 'Student', 'Door Warden', 'Lab Instructor', 'Parts Manager', 'Administrator', 'Designer'.
2. Must contain ALL of the values between 'Student' and the highest value contained.

CollegeYear
1. Must be one of the following values: 'Freshman', 'Sophomore', 'Junior', 'Senior', 'Graduate' or 'Faculty'.

LabSection
1. Must be 1-10 characters in length

LastLoginDate
1. Must be a valid ODBC datetime value.
2. Must be earlier than or equal to current datetime value.
3. Must be later than or equal to datetime value corresponding to EquipRD!'s installation.

Rules for Parts Table (back to top)
Each field's rules are checked on any operation on any field of any record in the Users table. Rules may be added, deleted or updated as the need arises. All numerical fields are unsigned.

PartNumber
1. Must be 1-20 characters in length
2. Can only contain A-Z, a-z, 0-9, hyphens, spaces and forward slashes
3. Cannot begin with a space, hyphen or forward slash
4. One column of primary key (each record must have a unique combination of values in its PartNumber and SerialNumber fields)

PartType
1. Must be 1-40 characters in length
2. Can only contain A-Z, a-z, 0-9, hyphens, spaces and forward slashes
3. Cannot begin with a space, hyphen or forward slash

PartDescription
1. Must be 1-100 characters in length
2. Can only contain A-Z, a-z, 0-9, hyphens, spaces and forward slashes
3. Cannot begin a space, hyphen or forward slash

SerialNumber
1. Must be 1-20 characters in length
2. Can only contain A-Z, a-z, 0-9, hyphens and spaces
3. Cannot begin with a hyphen or forward slash
4. Must equal "None" if item does not require serial numbers
5. One column of primary key (each record must have a unique combination of values in its PartNumber and SerialNumber fields)

Quantity
1. Must be a 16-bit integer
2. Must equal 1 if Serial Number does not equal "None"
3. Quantity = Available + In Use + Reserved + Damaged must evaluate to TRUE

Available
1. Must be a 16-bit integer
2. Available = Quantity - (In Use + Reserved + Damaged) must evaluate to TRUE

InUse
1. Must be a 16-bit integer
2. In Use = Quantity - (Available + Reserved + Damaged) must evaluate to TRUE

Reserved
1. Must be a 16-bit integer
2. Reserved = Quantity - (Available + In Use + Damaged) must evaluate to TRUE

Damaged
1. Must be a 16-bit integer
2. Must equal 0 if Serial Number equals "None"
3. Damaged = Quantity - (Available + In Use + Reserved) must evaluate to TRUE

LastCheckoutDate
1. Must be a valid ODBC datetime value.
2. Must be earlier than or equal to current datetime value.
3. Must be later than or equal to datetime value corresponding to EquipRD!'s installation.

Rules for Checkout Table (back to top)
Each field's rules are checked on any operation on any field of any record in the Users table. Rules may be added, deleted or updated as the need arises. All numerical fields are unsigned.

RowID
1. Must be an 16-bit integer
2. Primary key (each record must have a unique value in its RowID field)

UserID
1. Must be a 32-bit integer
2. Must correspond to a UserID value in Users table

PartNumber
1. Must be 1-20 characters in length
2. Can only contain A-Z, a-z, 0-9, hyphens, spaces and forward slashes
3. Cannot begin with a space, hyphen or forward slash
4. Must correspond to a PartNumber value in Parts table

SerialNumber
1. Must be 1-20 characters in length
2. Can only contain A-Z, a-z, 0-9, hyphens, spaces and forward slashes
3. Cannot begin with a space, hyphen or forward slash
4. Must equal "None" if item does not require serial numbers
5. Must correspond to a SerialNumber value in Parts table

Quantity
1. Must be an integer between 0 and InUse value corresponding to PartNumber, inclusive
2. Sum of all Quantity values in Checkout table corresponding to a given PartNumber must equal InUse value in Parts table for that PartNumber (i.e., the number of a given part checked out to all users must equal the number of that part currently in use)

CheckoutDate
1. Must be a valid ODBC datetime value.
2. Must be earlier than or equal to current datetime value.
3. Must be later than or equal to datetime value corresponding to EquipRD!'s installation.