Transcript IntroductiontoDatabases

Unit 3 IT Applications
Definition
Database Management Software (DBMS) allows you to
 store
 search
 sort
 report on
large quantities of data
Capabilities
 Can create new data calculated from stored data
 Built-in functions allow manipulation of numbers,
date and time and text
 Can create summaries of data to produce reports.
Structure
 The concept of a database is simple: it's a collection of
data in a table. An example is a table of your family as
follows:
Name
Relationship to me
Date of Birth
Occupation
Joseph
Father
15/08/60
Engineer
Marianne
Mother
23/10/63
Teacher
Jeremy
Brother
2/12/88
University
student
Katrina
Sister
21/7/99
Primary school
student
Susie
Me
18/4/92
Secondary school
student
Structure
 Fields:
 Each column in the data table is a field
 Fields can vary in length
 Fields can be of different data types
 Records:
 Each row in the data table is a record
 A complete set of fields
 Tables/Files:
 A collection of records
 A flat file database has only one table
 A relational database has two or more related (connected) tables
 Primary Key:
 Uniquely identifies each record in a table
 Each table must contain a primary key
Creating a database
 When you create a database, you must first create a
data structure table for each table of the database.
 The data structure table contains
 Field name
 Field type
 Field size
 Validation rules and text where applicable
Data structure table
 An example of a data structure table for a DVD rental
store:
Field name
Field type
Field width
DVD title
Text
50
Category
Text
20
Classification
Text
3
Number in
stock
Number
2
Release year
Text
4
Rental price
Number
7
Validation rule
Validation text
Only accept values of M,
MA, PG, G, R
Please select from the
following list
Cannot be <=zero
Please enter the correct
price
Field names
 Do not use spaces in field names (or in the name of
any other database object); use dashes, underlining or
capital letters instead.
 Examples of appropriate field names are:
 EmployeePayRate
 studentfirstname
 Employee_surname
Common field types (used in MS
Access)
Field type
Field can contain:
Max. field size
Text
Alphanumeric characters – use for text or text and numbers
that are not used in calculations
Eg: name, postcode
255 characters
Number
Numeric (integer or fraction) – use for storing numbers to be
used in calculations
Eg: amount, number of adults
8 bytes
Date/time
Dates and times
8 bytes
Currency
Monetary values
AutoNumber
Unique numeric value that is automatically inserted when a
record is added – usually used for the Primary Key
4 bytes
Yes/No
Boolean values – use for True/False fields that can hold
Yes/No or True/False values
1 bit
Memo
Alphanumeric characters that are longer than 255 characters
or text with rich text formatting
>255 characters
Flat file vs Relational
 Flat file databases contain only one table – used for
very simple databases, but becomes inefficient as the
database becomes more complex
 Relational databases contain two or more databases
and are preferred when dealing with a lot of data
 Relational databases are linked by a common field
 Relational databases can be of three types:
 One-to-one relationship
 One-to-many relationship
 Many-to-many relationship
Relationships
 One-to-one relationship: used where a record in one
table is connected to only one record in a second table.
 Eg: the relationship between an airline passenger and
their seat allocation. Each passenger only has one seat
and each seat can only be assigned one passenger.
 One-to-many relationship: used where a record in one
table can be connected to more than one record in a
second table.
 Eg: several employees in an office can share one
telephone extension number
Relationships
 Many-to-many relationship: used where each record in
one table can be connected to several records in a
second table.
 Eg: the relationship between a student table and a
subject table – each student can study several subjects
and for each subject there are many students enrolled
Other key terms and concepts
 Query: use a query to filter information that meets
specified criteria. The criterion can be a number, a
piece of text or an expression.
 Macro: used to automate a routine procedure. When
the macro runs, the tasks are carried out automatically.
 Data structure diagram: shows the relationship
between tables.
 Layout diagram: hand-drawn sketch that shows the
elements and format of a report or form. It includes
placement of fields, labels, fonts to be used, colour,
etc.
Other key terms and concepts
 Validation rule: allows data to be entered into a form
or table if it follows a specified rule. If the data does
not obey the rule, an error message appears.
 Input mask: an electronic validation tool that controls
how data is entered.
 List tool: used to control the data that can be entered
where there are a limited number of allowed values.
The list box shows what values the user can select.