Week 9, Lecture 1

1

Week 9, Lecture 1 Databases IntroductionDesign
2
Databases

• Database software is

• application software (like word processing and
  spreadsheet software)
• designed to maintain databases (collections of
  information)

A Database is

• a collection of information stored in an
  organized form on a computer

3
What can we do with a database?

• Store data
• Ask questions (query)
• Update
• Keep track of dependencies
• Present information (reports)
• Restrict access to data (who can see it or change
  it)

4
Disadvantages of NOT using a database

• Data Redundancy (wasted data)
• Data inconsistency (different copies of the same
  data might not agree)
• Data Isolation (data may be held in different
  files in different formats)

5
Database Management System (DBMS)

• is a program or system of programs that can
  manipulate data in a large collection of files
• redundant information is stored in common fields
• different users see different points of view
• may be interactive

6
Relational Database

• A database is relational when files are related
  to each other, such as this Student ID field in
  the Student file.

If a change is made in one file, all files are
updated immediately.
7
Files, Records

• A database is a collection of one or more
  database files

A file is a collection of related information
(records)
8
Records, Fields
A record is the information relating to one
person, product, or event
A field is a discrete chunk of information in a
record
9
Microsoft Access
Menu Bar
Toolbar
Database Window

Object Buttons
10
What is a Table?

• Used to store data about a particular subject
• A table consists of rows (records) and columns
  (fields)
• Each field has a data type
• Each record has a unique key

11
Creating a New Table
12
Table Wizard Screen
13
Defining a Table

• List
• the names of all of the fields
• the data type for each field
• the length where appropriate
• any other constraints (PROPERTIES)
• default values
• Validation rules and text

14
Table Design View
Primary Key
Field Names
Data Type
Description
Field Properties
15
Datatypes

• Text, memo (sentences), number, date/time,
  currency, autonumber (computer assigns unique
  number to each new record), Yes/No (box which if
  tickedyes), OLE object ( link to object like
  picture, document), Hyperlink (link to web
  pages), lookup wizard ( choose from a list of
  choices).

16
Text and Memo

• Text
• Short text
• Up to 255 characters
• Default 50

• Memo
• Lengthy text
• Up to 64,000 characters

17
Numbers - Field Size
18
Numbers - Format

• Remember the difference between representation
  and presentation in spreadsheets?

19
Numbers Decimal Places

• AutoThe number of decimal places is determined
  by the format
• User setDisplays the number specified

20
Date/Time
21
Autonumber

• Can generate three types of number
• Sequential
• Random
• Replication
• When do we use Autonumber?
• Autonumber cant be deleted or changed

22
Properties of fields

• Based on data type chosen, certain properties for
  the field should be set,
• focus on controlling input into the fields
• Field size, format, decimal places, input mask,
  caption, default value, validation rule,
  validation text, required, indexed
• Validation rule a rule that controls the data
  input into a field (start telno with a 9)
• Validation text a message that is displayed when
  there is an error in data input (please start the
  telno with a 9)

23
Default value

• Automatically fill in a certain value for this
  field in new records you add to the table
• Automatic default for numbers is zero.
• Distinguish between zero and Null !!

24
Validation Rule ValidationText

• Validation RuleSet controls over the values that
  this field can have
• Validation TextThe error message that is printed
  out if an error value is put in the field

25
Examples of Validation Rules
26
Required and Indexed

• RequiredThe field MUST contain a valid entry
• Indexes speed up searching
• (Hint index a field that is going to be used in
  a query or in a relationship to join two tables)
• Three options
• No
• Yes (Duplicates OK)
• Yes (No duplicates)
• Keys are automatically indexed

27
Keys

• Tables need keys
• A key in a table is unique
• A Primary Key is a field or combination of fields
  in a table used to identify a record
• Multiple field primary keys are possible
• Lecture Theatre bookings Date/time, Room
• If you dont have a key slower to search, may
  be misleading

28
Defining Primary key

• From created fields in design view, select one
  that is to be used a primary key and press key
  button.
• If no primary key what can be done?
• Create a new field to use as PK
• Choose 2 or more fields that together make a
  primary key (unique combinations for each new
  record)

29
Designing a Table

• Defining a tableTable ? New?Design View (here
  you specify the fields and the properties of each
  field in the table).
• Field namedatatypeDescription
• Datatype controls the what kind of data can be
  put into the field
• Add properties and select Primary Key