Designing a Database (Part I)

1
Designing a Database (Part I)

• Identify all fields needed to produce the
  required information

• Group related fields into tables

• Determine Each Tables Primary Key

• Unique identifier
• Field, Composite, New

• Include a common field in related tables

• Foreign Key

• Avoid Data Redundancy

• Determine the properties of each field

2
Naming Conventions

• 64 characters long
• Letters, numbers, spaces and special characters
  are allowed EXCEPT .!
• Avoid using quotation marks
• Name cannot start with a space
• Table and query name MUST be unique within
  database

Luigis Naming Conventions Lesson 1 Table
Names tbl_Doctor, tbl_Patient
Object Type
Relevant Name
3
Data Types (Access 2003)
Data Type Description Field Size
Text Letters, digits, special characters 1-255 chars
Memo Equivalent to text, longer 1-64000 chars
Number Integer, decimals 1-15 digits
Date/Time Jan 1, 100 to Dec, 31 9999. Multiple Date and Time Formats 8 digits
Currency Same as number, not subject to round-off error 15 digits
Autonumber Access controlled numbers 9 digits
Yes/No Boolean, True/False 1 character
OLE Object Images, video, sound, documents. 1 gig
HyperLink Internet address 2048 chars
Lookup Wizard Field which looks in other table for values Same as primary key of other table
4
Field Properties
Property Description
Field Size Length of input
Format Predefined or custom (ie. Dates)
Input Mask Restricts data input to characters, digits, etc.
Caption Display name on forms
Default Value Automatic Value for new row
Validation Rule Limits input via formulaltgt, gt, lt, Like,
Validation Text Error message that appears if rule is broken
Required Needs data??
Allow Zero Length Strings can be
Indexed Indexing speed up search and sort but slows update
5
Primary Key and Foreign Keys

• Uniquely identifies each record
• No Duplicate Entries (avoids data redundancy)
• Entity Integrity (no null values allowed for
  primary key)
• Access display order is by primary key
• 3 categories of primary keys
• Inherit in field
• Composite Key
• Fields created for specifically PK
• Foreign Key
• field whose values are primary keys from another
  table or null

6
Table Diagrams
tbl_CD tbl_CD tbl_CD
CDID Autonumber Primary Key
CDTitle Text Title of the CD
ArtistId Number Foreign Key to tbl_Artist
Field name
Table Name
Description
Data Type
7
Lab Assignment

• Continue with our hand-held application for the
  hospital
• Part 1 Scheduling
• Doctors who work at multiple hospitals wish to
  have a hand-held application that will keep track
  of their appointments with patients
• Doctor wants to be able to find out on any given
  day, where the appointment is, what time, and the
  patients name
• Doctor also wants general background information
  on the patient

8
Lab Assignment

• TO DO
• Create Table Structures
• Proper data types and field properties
• Primary and Foreign Keys
• Use as few tables as possible to get the job done
• Input Data
• Enter in a minimum of 5 rows for each table (10
  or more would be ideal)
• Test to make sure your restrictions work
• 3) HAND IN (towards Assignment 1)
• - A brief one page summary which includes table
  diagrams and an explanation of your design