Transcript Introduction to on

Introduction to databases
Introduction
Storage
Temporary and Permanent
Unstructured and Structured
Ways to structure information
Simple databases
Relationships, example relationship
Some Database Terminology
SQL – Structured Query Language
Nic Shulver [email protected], Chris Mayer
Introduction to databases
Storage – Temporary and Permanent
Many applications store information for later
use
Storage may be temporary or permanent
Permanent storage is usually in the form of a
file (e.g. a plain text file) stored on a physical
disc drive
Many applications add, modify and retrieve
information from structured data stores, often
called databases
Nic Shulver [email protected], Chris Mayer
Introduction to databases
Storage – Unstructured and Structured
Unstructured information (e.g. the stream of
words in a speech) is easy to store in a plain
text file
Structured information (name, occupation,
date of birth, eye colour – for a set of people)
is easy to store in a database
What is the advantage of using a database?
(The personal information could be stored in a
text file, after all.)
Nic Shulver [email protected], Chris Mayer
Introduction to databases
Ways to structure information
A lot of collected data is repetitive in form
Data may be repetitive in content, too
For example, if we have the following
information for 15 people;
[Age in years], [Name], [Height in metres]
then we have 15 records of 3 fields, in this format;
[whole number], [text], [fractional number]
Nic Shulver [email protected], Chris Mayer
Introduction to databases
More example information
Here is some simple information:
Danny DeVito(USA), Hugh Jackman
(Australia), Halle Berry(USA),
Patrick Stewart (UK), …
This kind of information has a simple structure
or format
We can put the information into a table
Nic Shulver [email protected], Chris Mayer
Introduction to databases
Table (with repeated information)
No.
First Name
Last Name
Origin
1
Danny
DeVito
United States of America
2
Arnold
Schwarzenegger
Austria
3
Halle
Berry
United States of America
4
Patrick
Stewart
United Kingdom
5
Karisma
Kapoor
India
6
Hugh
Jackman
Australia
7
Alec
Guinness
United Kingdom
8
Jack
Nicholson
United States of America
9
Kylie
Minogue
Australia
Nic Shulver [email protected], Chris Mayer
Introduction to databases
Table (no repeated information)
No.
First Name
Last Name
Origin
No.
Country Name
1
Danny
DeVito
1
1
United States of America
2
Arnold
Schwarzenegger
2
2
Austria
3
Halle
Berry
1
3
United Kingdom
4
Patrick
Stewart
3
4
Australia
5
Karisma
Kapoor
5
5
India
6
Hugh
Jackman
4
7
Alec
Guinness
3
8
Jack
Nicholson
1
9
Kylie
Minogue
4
This is an example of
a simple one-to-many
relationship.
Nic Shulver [email protected], Chris Mayer
Introduction to databases
Table (with repeated information)
No.
First
Name
Last Name
Origin
Films
1
Danny
DeVito
United States of
America
Batman Returns
Twins
2
Arnold
Schwarzenegger
Austria
Terminator
True Lies
Twins
Nic Shulver [email protected], Chris Mayer
Introduction to databases
Tables (repeated information)
No.
First Name
Last Name
Origin
No.
Country Name
1
Danny
DeVito
1
1
United States of America
2
Arnold
Schwarzenegger
2
2
Austria
3
United Kingdom
4
Australia
5
India
No.
Films
1
Batman Returns
2
Terminator
1
Hook
1
Twins
2
Twins
This is an example of
several linked tables
Nic Shulver [email protected], Chris Mayer
Introduction to databases
Repeated Information
The previous example does not solve the
problem
What about the other actors/actresses in the
films?
Databases can soon get complicated
Need to think carefully about structure before
you start – may be difficult later on
DSA will only need simple structures in DB
Nic Shulver [email protected], Chris Mayer
Introduction to databases
Some Database Terminology
Rows and Columns
Broad view of the table – each row is a record,
each column is a set of fields
This is analogous to a spreadsheet view
Records and Fields
Detailed view of the information
Each record (entry) in the database is made up of
fields, possibly with some fields from joined tables
Nic Shulver [email protected], Chris Mayer
Introduction to databases
Relationships and Joins
Tables may be joined together – this is very
powerful but can get complex
Database systems which allow join
relationships are known as Relational
Databases (see RDSD)
To build, use and maintain joins between
tables, it is often easiest to use tools built into
database programs (e.g. MS Access, SQLyog)
Nic Shulver [email protected], Chris Mayer
Introduction to databases
SQL – Structured Query Language
Structured Query Language is the language of
databases
It is very widely used in industry – look for
mySQL, PL/SQL, SQLplus, Oracle SQL and
so on
We can use little chunks of SQL in our
applications to ask a database for information
(i.e query the database)
Nic Shulver [email protected], Chris Mayer
Introduction to databases
Structured Query Language
Structured Query Language (SQL), is a set of
commands that all programs and users may
use to access data within databases
Application programs and tools often allow
users to access SQL databases without
directly using SQL, but these applications in
turn must use SQL when executing the user’s
request
Nic Shulver [email protected], Chris Mayer
Introduction to databases
History of SQL
 In June 1970 in the Association of Computer
Machinery (ACM) journal, Communications of the
ACM published the paper, “A Relational Model of Data
for large Shared Data Banks,” by Dr. E. F. Codd
 Codd’s model is the definitive model for relational
database management systems (RDBMS)
 IBM Corporation, Inc. developed Structured English
Query Language(SEQUEL) using Codd’s model
 SEQUEL later became SQL, the standard RDBMS
language
Nic Shulver [email protected], Chris Mayer
Introduction to databases
What can it do?
SQL provides commands for a variety of tasks
including:
querying data,
inserting, updating, and deleting rows in a table,
creating, replacing, altering, and dropping objects,
controlling access to the database and its objects,
guaranteeing database consistency and integrity.
Nic Shulver [email protected], Chris Mayer
Introduction to databases
Conclusion
We need to store some information in a
structured way
Simple tables are conceptually simple
Relational databases provide a powerful and
convenient way to store data
Structured Query Language provides us with
a way to interact with databases
Nic Shulver [email protected], Chris Mayer