Transcript 2_Creating A Database

Chapter 2
Creating a Database
IT Key Ideas, Dynamic Database Systems, 2002
Databases
A database is a collection of data that is organised.
Given Name
Sieh
Doreen
Neville
Sigmund
Lena
Lavinia
Shane
Robert
Louis
Michelle
Frederich
Wayne
Anita
Annette
Carter
Surname
Allanson
Brereton
Cox
Deacon
Douglas
Elliott
Gonda
Haydon
McKay
McPharlin
Moore
Mousley
Sellers
Stone
Woodhouse
Address
1 Copas St
25 Kingston Tce
49 Kennaway Tce
8 Myer St
31 Regency Rd
25 Alicia Rd
8 Glebe Rd
31 Hardys St
44 Regency Rd
22 Barker St
34 Melton Drv
16 Grote Crs
34 Flinders Tce
37 Marena St
12 Glebe St
Suburb
Black Forrest
Evanswood
Black Forrest
Flinders Vale
Redwood Park
Blackland
Evanswood
Blackvale
Cumberland
Blackland
Evanton
Forrest Hills
Flinders Valley
Flinders Valley
Blackwood
Phone Number
4270 4531
4370 5507
4270 8781
4296 3024
4277 9069
4347 2011
4370 7214
4277 5000
4278 8206
4347 9075
4252 7862
4343 4888
4293 0367
4293 9313
4345 5955
alphabetical order
IT Key Ideas, Dynamic Database Systems, 2002
Database Systems
A database management system consists
of one or more tables.
IT Key Ideas, Dynamic Database Systems, 2002
Database files – Microsoft Access
The creation of a database in Access creates
only one file, with a mdb extension.
This file contains all of the tables, queries,
reports and program modules that may be
created.
The file can be copied, pasted, e-mailed etc.
NB: No Save As option is available in
database software to save the data.
IT Key Ideas, Dynamic Database Systems, 2002
Personal Attributes Database
A typical database is a personal address
book, as in a person’s diary. This
database consists of attributes like
people’s names, address, suburb, phone
number, sex, age.
IT Key Ideas, Dynamic Database Systems, 2002
Exercise
Create a database storing data for the
following attributes
Given Names
Surname
Sex
Date of Birth
Hair Colour
Eye Colour
IT Key Ideas, Dynamic Database Systems, 2002
Database Modelling - Initial Guidelines
Tables have plural names.
Field names are singular.
Calculated data elements are achieved through
queries – rather than being stored as fields.
Data is stored dynamically, eg. Date of Birth. Age is
calculated.
First names and surnames are stored as separate
elements, and should predominantly be outputted as
a full name, by using a calculation. Addresses and
suburbs can also be combined in a calculation.
Suburbs and Postcodes form a (separate)
lookup table.
The interrogation of a database by
applying data processing involves at least
one criterion and sorting.
Planning
page 7
Write down the structure of the table, on
paper or in a word processed document.
Make a table with the column headings as
shown.
Field Name
Data Type
Size
IT Key Ideas, Dynamic Database Systems, 2002
Planning
In the first column enter the names of the
attributes or elements that will be entered
into the database, eg. Surname.
In the second column, write (or type) the type
of data that will be stored in each field, either,
text (for letters), number, date/time or yes/no
(for having a driver's licence, for example).
IT Key Ideas, Dynamic Database Systems, 2002
Field Name
Surname
Data Type
text
Size
IT Key Ideas, Dynamic Database Systems, 2002
Planning
In the third column, enter the largest number
of characters that could be entered for each
field. For example, a field called Given Name
could be as long as 30 if entering just one
name, but as long as 50 or 60 if entering
people's middle names as well as their first
name.
A table that illustrates Field Names, Data
Type and Size - and possibly some other
components or features - is called a Data
Dictionary.
IT Key Ideas, Dynamic Database Systems, 2002
Given Names
Surname
Sex
Date of Birth
Hair Colour
Eye Colour
text
text
text
date/time
text
text
35
30
1
12
10
IT Key Ideas, Dynamic Database Systems, 2002
New Database – Microsoft Access
Create a new database system, using an
appropriate name
eg. Class Details
Create a table within the database and
provide an appropriate name for the table,
eg. Details
IT Key Ideas, Dynamic Database Systems, 2002
Practical 2.1
page 8
Creating the
database
choose a New
Blank Database
IT Key Ideas, Dynamic Database Systems, 2002
Creating a table
page 9
choose Create table in Design view
IT Key Ideas, Dynamic Database Systems, 2002
specifying the fields
Field Name
Data Type
to store the data
Size
Given Names
Text
35
Surname
Text
30
Sex
Text
1
Date of Birth
Date/Time
Hair Colour
Text
12
Eye Colour
Text
10
for each,
enter a field name
select a data type
and for text fields
enter a field size
IT Key Ideas, Dynamic Database Systems, 2002
The field size for
text fields should
be set to the
largest piece of
data likely to be
stored in the field.
Other data types
have specific
sizes.
IT Key Ideas, Dynamic Database Systems, 2002
answer No to primary key
until systems with multiple tables
IT Key Ideas, Dynamic Database Systems, 2002
Components
page 3
Table
A table or file is a collection of related data. For
example, a book with pages listing the details of all
the members in a club.
Record
A record, card or form, is a collection of data for
one individual entity. Each record contains the
same type of data. For example, the information
about a single member is a record (one line of the
membership page).
Field
A field is one element of data, one single
attribute of the entity. For example, the
member’s date of birth is a single element of
data.
IT Key Ideas, Dynamic Database Systems, 2002
a file of cards
cards in a draw
IT Key Ideas, Dynamic Database Systems, 2002
each card contains the same elements of data
IT Key Ideas, Dynamic Database Systems, 2002
for a job, for a bank account, to buy a car
IT Key Ideas, Dynamic Database Systems, 2002
a record
the attributes
about one entity
IT Key Ideas, Dynamic Database Systems, 2002
the attributes
about one entity
a record
Given Names
Perrin Ian
Inga Marigold
Katie Beverly
Benjamin Kris
Romola Jodie
Anna Talia
Belle Shannette
Cherie Xenia
Denby Dane
Ramon Frederich
Surname
O'Keefe
Villios
Curtis
Jury
Neilson
Wheatland
Sym
Finlayson
Manuel
Handsaker
Sex
M
F
F
M
F
F
F
F
M
M
Date of Birth
23/11/80
16/09/79
27/06/84
11/11/88
8/06/82
13/12/84
22/12/82
29/08/33
12/12/87
9/05/81
Hair Colour
auburn
black
light brown
dark brown
red
auburn
light brown
black
light brown
black
Eye Colour
brown
brown
blue
brown
blue
brown
brown
brown
blue
blue
IT Key Ideas, Dynamic Database Systems, 2002
a field
one attribute
IT Key Ideas, Dynamic Database Systems, 2002
one attribute
a field
Given Names
Perrin Ian
Inga Marigold
Katie Beverly
Benjamin Kris
Romola Jodie
Anna Talia
Belle Shannette
Cherie Xenia
Denby Dane
Ramon Frederich
Surname
O'Keefe
Villios
Curtis
Jury
Neilson
Wheatland
Sym
Finlayson
Manuel
Handsaker
Sex
M
F
F
M
F
F
F
F
M
M
Date of Birth
23/11/80
16/09/79
27/06/84
11/11/88
8/06/82
13/12/84
22/12/82
29/08/33
12/12/87
9/05/81
Hair Colour
auburn
black
light brown
dark brown
red
auburn
light brown
black
light brown
black
Eye Colour
brown
brown
blue
brown
blue
brown
brown
brown
blue
blue
IT Key Ideas, Dynamic Database Systems, 2002
Advice
click the View button for Datasheet view
this button toggles between
Datasheet and Design views
Advice
Delete Rows
Advice
to delete a record in datasheet mode
click in the grey column and press Delete
or click the Delete Record button on the toolbar
Data Types
Field name
Data Type
Size
Text
alphanumeric characters
up to 255 characters
Memo
alphanumeric characters
up to 32000 characters
Number
numeric values
1, 2, 4 or 8 bytes
Date/Time
date/time
8 bytes
Currency
monetary values
8 bytes
Auto Number
incrementing numbers
8 bytes
true/false (yes/no) boolean values
1 bit
OLE objects
up to 128 Mb
objects, graphics
Buttons on Toolbars
IT Key Ideas, Dynamic Database Systems, 2002
Searching for data values
to find a particular record
in the database
use the Find facility
Searching for data values
change the Match Option and enter part of the data
value, eg. enter Stana to find Stanaway.
NB: The next name starting with Stana will be found.
Practical 2.2
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Data File
People
page 12-13
What is Constance's surname?
What are Mr Keur's first two initials?
What is Zena's date of birth?
Georgia has a sister. What is her first name and is she
older or younger?
What colour eyes and colour hair does Miss Hartwig
have?
Who was born in 1954?
What is the woman’s name who was born in 1934?
Who was born in November of 35?
When is Tenille’s next birthday?
Who is 56 years of age?
IT Key Ideas, Dynamic Database Systems, 2002
Chapter 2
********************************