INFORMATION SYSTEMS

1
INFORMATION SYSTEMS

• IS1
• IPT 2004

L4
2
INFORMATION SYSTEMS

• A Database is an organised collection of data.
• Databases may be of many types
• Hierarchical databases
• Network databases
• File managers
• Hypertext

3
INFORMATION SYSTEMS

• Relational database
• All data appears, to the user, to be stored in
  tables
• The query language used in database management
  operate on tables of data and, when retrieving
  information, generates a new table as the result
  of a database operation.

4
INFORMATION SYSTEMS 1
TABLEs contain facts about one and only one
entity type
Animal
Column Names - letter, , , _at_ - length 18
char - spaces
5
INFORMATION SYSTEMS

• To be fully Relational
• Integrity of the stored data must be protected
  from possible from undesirable user action
  (constraints)

6
INFORMATION SYSTEMS

• Query Languages
• Database systems have special languages that
  facilitate and control all access to data.
• Structured Query Language (SQL) developed by IBM
  for use with relational systems.
• Now an internationals standard AS/NZA 39681994

7
INFORMATION SYSTEMS

• SQL
• Define database structures
• Insert, delete and modify data
• Retrieval of data and production of reports.
• Implement integrity constraints
• Control of stored data

8
INFORMATION SYSTEMS

• SQL SELECT Command
• Queries are formulated with the SQL select
  command.
• Select allows data to be retrieved or derived
  from a table and displayed as a new table.
• Used with a FROM clause to get data from a table.

9
INFORMATION SYSTEMS 1
TABLEs contain facts about one and only one
entity type
Animal
Column Names - letter, , , _at_ - length 18
char - spaces
10
INFORMATION SYSTEMS 1
Animal
Relational Schema Animal (name, legs, sex)
11
INFORMATION SYSTEMS 1
Animal
SELECT Name, Legs, Sex FROM Animal
12
INFORMATION SYSTEMS 1
Animal
To select the whole table displayed the following
SQL statement is used SELECT from Animal
13
INFORMATION SYSTEMS 1
Animal
To select just the name and number of legs?
14
INFORMATION SYSTEMS 1
Animal
To select just the name and number of
legs select name, legs from Animal
15
INFORMATION SYSTEMS 1
Animal
16
INFORMATION SYSTEMS 1
Animal
To select just the number of legs?
17
INFORMATION SYSTEMS 1
Animal
To select just the number of legs? Select legs
from Animal
18
INFORMATION SYSTEMS 1
Animal
To select just the number of legs? Select
Distinct legs from Animal
19
INFORMATION SYSTEMS 1
Animal
To select the details of female animals SELECT
FROM t table WHERE c condition
20
INFORMATION SYSTEMS 1
Animal
To select the details of female animals SELECT
FROM animal WHERE sex F
21
INFORMATION SYSTEMS 1
Animal
To select the details of female animals Select
from Animal where Sex F
Boolean
22
INFORMATION SYSTEMS 1
Animal
To select the details of female animals with four
legs Select from Animal where ?
23
INFORMATION SYSTEMS 1
Animal
To select the details of female animals with four
legs SELECT FROM Animal WHERE sex F and
Legs 4
24
INFORMATION SYSTEMS 1
Animal
To select the details of female animals with four
legs SELECT FROM Animal WHERE sex F and
Legs 4
25
INFORMATION SYSTEMS 1
Animal
To select the names animals with four legs
26
INFORMATION SYSTEMS 1
To select the names animals with four
legs SELECT Name FROM Animal WHERE Legs 4
27
INFORMATION SYSTEMS 1
Animal
To select just the number of legs?
28
INFORMATION SYSTEMS 1
Animal
To select just the number of legs? SELECT
distinct legs FROM Animal
29
INFORMATION SYSTEMS 1
Animal
List all the names of the male animals without 2
legs
30
INFORMATION SYSTEMS 1
Animal
List all the names of the male animals without 2
legs select Name from Animal where ?
31
INFORMATION SYSTEMS 1
Animal
List all the names of the male animals without 2
legs SELECT Name FROM Animal WHERE Legs ltgt 2
and Sex M
32
INFORMATION SYSTEMS 1
SQL

• Upper and lower case ignored unless inside a
  string
• Strings are delimited by single quotes
• Select from t displays the columns and rows in
  the order in which they are named, e.g.
• Select a, b from t a
  b
• Select b, a from t b a

33
INFORMATION SYSTEMS 1
Animal
SELECT legs, Name FROM Animal
34
INFORMATION SYSTEMS 1
SELECT legs, Name FROM Animal
35
INFORMATION SYSTEMS 1
Animal
Order by sex
36
INFORMATION SYSTEMS 1
Animal
SELECT FROM Animal order by Sex
37
INFORMATION SYSTEMS

• IS1
• IPT 2003

L4