Database Design Concepts INFO1408

1
Database Design ConceptsINFO1408

• Term 2 week 1
• Data validation and Referential integrity

2
Objectives

• To identify the need for validation and
  verification
• Identify some of the built in constraints used in
  databases

3
Data validation formatting

• When data is entered into a database it is
  important that it is entered consistently- this
  ensures that we can set up queries and macros for
  the users as easily as possible.
• Imagine a query that needed to work on all the
  possible ways someone could enter a date.
• in other words it must be entered in the same
  format.
• If you design and set up a database and enter all
  the data yourself this may not be a problem
  however this is unlikely to be the case in many
  organisations.

4
Formatting Data

• Formatting is the term used to control the
  appearance of your data.
• This formatting can also speed up data entry by
  filling in attribute values with default values
  such as todays date or from other data already
  in the database.
• As the designer you need to decide where
  formatting can be used.

5
Examples

• Post codes
• Phone numbers
• Starting names with capital letters
• Number of digits after the decimal point
• Dates (eg 3/2/04 3 feb 2004)

6
Input masks

• This is a format that is applied when users enter
  data
• An input mask will ensure your user inputs data
  in the correct format.
• E.G Post code
• 1 or 2 letters (capitals) followed by up to 2
  digits, a space, 1 digit then two letters
  (capital)
• LE1 4RT or LE11 5TY or L1 4GH

7
Validation rules

•  
• You can set validation rules for an attribute.
  This ensures that values entered into the
  attribute are appropriate for that attribute.
• E.g.
• Date of birth to be before todays date
• Members date of birth to be before 31.3.94 (i.e.
  less than10 years old)
• Price of a product to be greater than 0
• Salary to be less than 30,000

8
Setting up validation formatting

• All of these are set up in Access for each
  attribute using the attribute property table

9

• Format short date
• Input mask / as spacers
• Caption ( name of field to appear on reports etc)
• Default value value that is automatically
  entered- user must overtype if different- set to
  todays date
• Validation rule date entered must be before or
  equal to todays date
• Validation text- error message to be displayed if
  incorrect date entered
• Required - yes means a value must be entered

10
Caption
Adding a new record to the table
Default date todays date date in short date
format In put mask shows the / as spacers
11
If I try and remove the date and dont put in a
new date the required property comes into
effect. I cant move to the next attribute until
I have entered another date. I am going to add
tomorrows date ( 27/10/04)
12
This date does not agree with the validation
rule. The Validation text is shown as an error
message.
13
Record level validation

• Sometimes you want to check that the value of an
  attribute compares with another attribute.
• E.G End date of agreement must be after start
  date.

14
Set up record level validation by opening the
table in design view and selecting view for the
main menu followed by properties
This validation rule checks that the end date is
after the start date This check is carried out
when you move to the next record ( or row)
15
(No Transcript)
16
Constraints

• Primary Key Constraints
• The database will ensure that no row is added to
  a table that already has the same value for the
  key.
• For composite keys it is possible for one of the
  columns to contain duplicate values in the table
  but the combination of values must be unique

17
Referential Integrity Constraints

• Referential constraints are enforced using
  something called foreign keys.
• A foreign key is a key field of a table found in
  another table as a foreign key.
• This allows links to be made between the tables
• It allows us to reference other tables by
  enforcing the relationships between the entities

18
This arrangement tells us there is a relationship
between student and course. The foreign key sets
up the relationship
19
Referential integrity

• The DBMS will check that when ever a value is
  entered into a foreign key there is a
  corresponding row in the table which contains the
  same value
• i.e. If we enter a course number in the student
  table there will be a row in the course table
  with that course number.

20
This works in several ways

• If we enter a new row in the student table the
  DBMS will check there is a corresponding record
  for the course no in the course table otherwise
  it will reject it.
• If we update the value of course no in the
  student table the DBMS will check there is a
  value for the course no in the course table
  otherwise it will reject it.
• When we try and delete a record from the course
  table it will reject the delete,
• If we are sure we wish the related records to be
  deleted we can set it up automatically to delete
  all the records in the student table which have
  that same value of course no known as a cascade
  delete.

21
Using an example The relationship is between
Classes and Tutor
22
See week 2 lab on how to add this in Access
23
Integrity constraints

• These are constraints to help the accuracy of the
  database- There is no way round them for the user
• Two types
• Not null There must be a value for this
  attribute
• Check constraint - uses a simple logic statement
  to work out whether the value of an attribute is
  acceptable

24
Example of check constraint
Example of not null constraint
25
Summary

• We have covered in this lecture
• Validation ( field and record level)
• Formatting and input masks
• Constraints
• Primary key constraints ( uniqueness)
• Referential constraints ( foreign Keys
• Integrity constraints ( not null and check
  constraints)

26
References

• Nat Mcbride- Introduction to Access 2000