Relational Databases
are managed by Relational Database Management Systems. Access, MySQL,
Oracle, Postgress, Interbase and so on are RDBMSs and all use SQL in
some form or another to access data. To be considered RELATIONAL,
a database language (or development platform) must allow a number of
basic operations. These operations have been categorised into DDL, DML
and SECURITY below:
Data Definition
(DDL)
- Users with appropriate
permissions should be able to creating table shaped containers. They
should be able to define special relationships like mandatory/optional
fields and primary/foreign keys
.
- It should be possible to add columns and alter table definitions
.
- Users with approprite authority should be able to populate their
tables (ie. add row instances), modify those rows, view them and remove
them. Note that in some flavours of SQL, explicit permissions are
set for EACH of these operations.
- Users with approprite authority should also able to destroy tables.
In MySQL, table
creation can be achieved either through the use of command-line like
DDL commands, or graphically (using many different graphical user interfaces)
or dynamically via webpage calls. Additionally, MySQL Data definition
can be achieved 'live' via scripts embedded in server-side web pages.
In Access, we use
table builder grids and relationship builders to achieve the same (although
it is possible to create tables and relationships using commands in
Access, it is not a common way to do this).
Data Manipulation
(DML)
Once your data containers
are defined and filled with data, then the next step is doing something
with that data. There is little point in entering data if nothing can
be doneto it - SQL provides many complex and powerful data manipulation
commands which have been logically grouped below. Further explanation
of these groups occurs later in this section.
- SELECTION
- we should be able to see only the rows that are relevant to a particular
problem = row subsetting
(show us rows corresponding
to some condition)
- PROJECTION - we should be able to see only those
columns that are relevant to our problem = column subsetting (display
certain columns only)
- INTERSECTION - viewing data that is common between
compatible tables (list data that is in both places)
- UNION - merging data from compatible tables (merge
this list with this list to produce a single, distinct list)
- DIFFERENCE - listing data that is present in one
table but absent in another - like XOR (show me values that occure
here but do not occur there)
- JOIN - combine rows of 2 or more related tables
to re-join facts (connect rows here with corresponding rows from there)
- ORDERING - cosmetic rearrangement of rows based
onon specific criteria (arrange these rows based on this criteria)
- GROUPING - agglomerating rows according to some
criteria (combine all these values and do someting with the combined
data)
- DERIVATION - using pre-defined functions to work
out certain facts from stored info (using this information, work something
out and display the answer)
Data manipulation
is invariably achieved using QUERIES. There are many types of queries,
some merely requesting data, others forcing calculation, still others
used to alter existing table data.
In this section
of the course you will be given a better than working knowledge of querying
- the power of relational databases lies in the ability to use the stored
data in really flexible ways. You will also be given a visual lexicon
(language/method of representing query ideas so as to better find simplicity
in complex situations).
ACCESS PRIVILEGE
SQL traditionally
allows the designer of the database to determine who is allowed to
access (either see/update) particular fields in a database. The designer
can customise a VIEW of the database, protecting the base tables from
unorthorised access.
MySQL databases
and tables are STRICTLY controlled via username and password. Database
designers assign permission levels to various users - read, add, modify,
delete etc. Unless you have specific permissions to perform actions,
you are prohibited from doing so. The public MUSIC database available
online allows you to merely read the data- you have no add, modify
or delete priveliges. The PUBLIC database is open to any user - all
users have complete permissions over the tables stored there.
In Access, if
you can open the database, you have complete control over it unless
your access to the database is governed by some programmed INTERFACE.
We will not concentrate
on security in this unit, but be aware that any form of commercial
database needs careful security measures to prevent unauthorised or
accidental damage to the data. In the ideal world, security measures
are unnecessary, nothing goes wrong and the information wants to be
free. In the real world shit happens and if 'accidents' can be prevented
they should be.
Relational Diagrams
- 'Query Shorthand'
The following diagramming
method will be used throughout this section to explain (in a physical
way) the action of a query. Often it is easier to design queries, particularly
complex ones, symbolically before attempting to encode them.
This method of query
representation is similar in many ways to QBE (Query By Example) which
is a popular method of querying in modern RDBMS's.
Please note that,
although the following diagrams are drawn very neatly, a scribble
sketch is sufficient for student use (ie. don't get out your ruler
to do these) - they are merely visual aids, hope they help.
A
detailed explanation of SQL Query diagrams is presented in a paper
given by Mr Peter Whitehouse at the 1999 QSITE State Conference with
many of the examples presented being recycled here as well
 |
list
serial numbers and album names from the albums table
|
 |
list
the album name and publishing date of all albums by the artist
"Pink Floyd" arranging the data in reverse chronological
order
|
 |
list
album name and artist along with their age that I have on CD
|
 |
list
albums and the media they are on from either Edgar Froese or Tangarine
Dreat, arranged alphabetically
|
 |
list
all group names without repeats
|
 |
list
artists that I have more than 2 albums of
|
 |
list
artists and album name of all CDs that feature the song called
'blister in the sun'
|
 |
list
artists that do not play bass
|
 |
list
all artists recorded in alphabetical order
|
 |
list
the album name and songs of any album released by Barry Manilow
|
The actual SQL commands
for the diagrams above will be explored in class, along with many more
complex problems. The diagrams are presented to help you 'see' the solution.
Union T1
U T2 (so long as the tables are type compatible)
eg. TableA name age TableB name age
fred 14 cyril 17
marg 12 blue 13
glen 4 fred 14
marg 12
TableA U TableB =
name age
fred 14
marg 12
glen 4
cyril 17
blue 13
Columns involved
in union must be type compatible
Combinations of
the above operations are allowed: (X where b = q [a]) U (Y where c =
y [a]) where column a is compatible
Intersection
T1 intersection T2 (ie. common elements)
TableA intersection TableB =
name age
fred 14
marg 12
Difference
T1 - T2 (ie. those in T1 that are not in T2)
TableA - TableB = name age
glen 4
Joining
(cartesian product) - without constraint
Grades Classes Result - unconstrained
year form year form
5 blue 5 blue
6 X gold 5 gold
7 6 blue
6 gold
7 blue
7 gold
Joining
- another unconstrained join: Produce a 'play list' for GPS
school1 school2 result (unconstrained)
BSHS BSHS school1 school2
NC X NC BSHS BSHS 'a self game'
GT GT BSHS NC
BSHS GT
NC BSHS 'repeat'
NC NC 'a self game'
NC GT
GT BSHS 'repeat'
GT NC 'repeat'
GT GT 'a self game'
a constrained join:
school1 X school2 where school1 < school2
result
school1 school2
BSHS NC
BSHS GT
GT NC
