Transcript tables - Department of Computer Science and Information Systems

Introduction to
Relational Databases
and MySQL
Lecturer: Jerry Smallwood
Email: [email protected]
Introduction to Database Technology
Session 1
DT-Sat Class Schedule






Sessions 1 & 2: Sat 14th November
Sessions 3 & 4: Sat 21st November
TMA Self-Study Session: Sat 28th November
Sessions 5 & 6: Sat 5th December (TMA Due)
Sessions 7 & 8: Saturday 12th December
FMA Due: 2pm on Monday 11th January 2010
Introduction to Database Technology
Session 1
DT Assessment (2009/10)
TMA (Tutor Marked Assignment)




The TMA carries 25% of the total marks
requires you to produce a design for a database for Miles Better,
a small car hire company
TMA Self-Study Session: Wed 11th November
TMA Submission Deadline: 6pm on Wed 18th November
FMA (Final Module Assignment)



FMA carries 75% of the total marks
requires you to create the Miles Better car hire database that
you designed for the TMA
FMA Submission Deadline: 2pm on Monday 11th January 2010
Introduction to Database Technology
Session 1
ITApps Assessment (2009/10)
ITApps Assessment Process (2009/10)



Full details on the ITApps website:
http://www.dcs.bbk.ac.uk/itapps/assessment.html
Note: Late submissions is possible but at a penalty (mark
capped at 40% of total marks for that assignment)
Note: note issue of ‘mitigating circumstances’
Chair ITApps/FdScIT Exam Board



Ian Harrison, email: [email protected]
Contact re. ‘mitigating circumstances’ or any other issues that
prevent you submitting the TMA/FMA on time.
Note: Lecturers cannot grant extensions to deadlines
Introduction to Database Technology
Session 1
Session 1 - Overview
• Introduces the basic concept of a
database and shows why it is useful.
• Introduces the Yum Juices case study that
will be used throughout this module.
• Introduces you to MySQL, the Open
Source Relational Database Management
System that you will use to study this
module.
Introduction to Database Technology
Session 1
What is a database?
•
an organized body of related information
wordnet.princeton.edu/perl/webwn
•
A collection of data organized for rapid search and retrieval by a computer.
www.clock.org/~jss/glossary/d.html
•
A collection of related data stored in one or more computerized files in a manner
that can be accessed by users or computer programs via a database management
system.
sparc.airtime.co.uk/users/wysywig/gloss.htm
•
A database collects information into an electronic file, for example a list of customer
addresses and associated orders. Each item is usually called a ‘record’ and the
items can be sorted and accessed in many different ways.
www.webmotion.co.uk/resources/d.php
•
A database is an electronic filing collection of information that is organized so that
it can easily be accessed, managed, and updated.
www.salvagedata.com/hard-drive-recovery-terms/
•
A set of related files that is created and managed by a database management
system (DBMS).
www.bradycommunications.com/perspectives/glossary.aspx
•
A collection of information stored in one central location. Many times, this is the
source from which information is pulled to display products or information
dynamically on a website.
www.gravitatedesign.com/glossary.htm
•
Relational data structure used to store, query, and retrieve information.
fwie.fw.vt.edu/tws-gis/glossary.htm
Source: Google “Define: database”
Introduction to Database Technology
Session 1
A database consists of…
• A collection of data
• Software to store, access and manage the
data (DBMS)
User A
DBMS
User B
Collection of Data
Introduction to Database Technology
Session 1
Advantages 1
• Data independence
– Changes to structure of the data do not affect programs used to
access it
• Consistency of data
– Each item only recorded once. No danger of its being updated in
one place but not another
• Reduced redundancy
– Each item of data is only stored in a single place. No duplication
of data
• Data integrity
– Control and check data entry
• Data security
– Restrict access to authorised users
Introduction to Database Technology
Session 1
Advantages 2
• Centralised control
– Database administrator will control who has access to the data
• More information
– Users have access to wider range of data that might previously
have been held in different places and possibly on incompatible
systems
• Greater productivity
– The DBMS query language allows users to design their own
queries and get an immediate response
Introduction to Database Technology
Session 1
Disadvantages
• Cost
– Database systems are complex, difficult, and time-consuming to
design.
– Initial training required for all programmers and users.
– Hardware and software start-up costs.
• Potential loss of data through system failure
– Need for backup and recovery procedures
– Essential to ensure that no data is lost
• Security
– Data may get into the wrong hands
• Privacy
– The increased use of databases means a large amount of
personal information is stored in many different places
Introduction to Database Technology
Session 1
Data Storage
• Data is stored in tables
• Each row is a record
• Each column is an item of data (field) within
the record
Pet
Introduction to Database Technology
pet_name
pet_type
Sid
snake
Minnie
mouse
Mickey
mouse
Grommit
dog
Mog
cat
Bubbles
goldfish
Session 1
Types of Database
• Flat file
– All data in a single table
– You can use a spreadsheet such as Excel to
create a database of this type
• Relational
– Data split into related tables, linked by keys
Introduction to Database Technology
Session 1
Flat File Database – example 1
Yum has stored details of customers and the orders they have placed in a
single table.
What problems might this cause?
What happens when they also record details of the items included in each
order?
Introduction to Database Technology
Session 1
Flat File Database – example 2
Multiple items in each order
ID
Date
OrderItems
CustID
Title
Fname
1
12/06/2008
16 Sparkling Sunshine £3.25 x 2
1 Citrus Zing £2.50 x 3
2
Mr
Jack
Sname
…
Hill
Table needs a record for each order item
Even more duplication of data!
Introduction to Database Technology
Session 1
Relational Database
• Data is split into entities
• An entity is something that the database
stores information about
• A noun
• A person, place, thing, event
• Data for each entity is stored in a separate
table (known as a relation)
• What are the entities in the previous
example?
Introduction to Database Technology
Session 1
Yum Database Entities
• Each of the following entities will be stored
in a table in the Yum database:
– Customers
– Orders
– Order Items
– Juices
Introduction to Database Technology
Session 1
Attributes
• An attribute is a property of an entity
• Each attribute is a column in the table for its associated
entity
• Each row in the table is a record. The attributes are the
fields in the record
• What are the attributes for the customer entity in the Yum
database?
–
–
–
–
–
Customer ID
Title
First name
Surname
…
Introduction to Database Technology
Session 1
Primary Key
• A field (or fields) that uniquely identifies
each record in a table
• All tables in a relational database should
have a primary key
• What would be a suitable primary key for
the customer table in the Yum database?
Introduction to Database Technology
Session 1
Foreign key
• Used to relate one entity to another to
allow the database to retrieve related data
• The primary key of the parent table is
stored in the related record of the child
table
Introduction to Database Technology
Session 1
Foreign key - example
• To relate customers to the orders they
have placed we store the customer ID of
the customer placing the order in the order
table
• Customer ID is the primary key of the
customer table and a foreign key in the
order table
• Each customer ID will be recorded just
once in the customer table but may be
recorded many times in the order table
Introduction to Database Technology
Session 1
Customer and Order Tables in the Yum Database
Primary key
Foreign key
Customer Table
CustID
Order Table
Title
Fname
Sname
Address1
1
Mrs
Jill
Hill
2
Mr
Jack
3
Ms
4
2 Steep St
Order
ID
Cust
ID
Hill
2 Steep St
1
2
12/06/2008
Bo
Peep
5 Flockington Rd
2
5
12/06/2008
Mr
Humpty
Dumpty
29 Shell Ave
3
6
15/06/2008
5
Mr
Harley
Davidson
5b Bike Alley
4
2
23/06/2008
6
Miss
Mary
Contrary
1 Flower Gdns
5
3
23/06/2008
6
1
23/06/2008
7
5
01/08/2008
8
4
02/08/2008
9
3
10/08/2008
10
3
15/08/2008
What is the name of the customer who placed
order number 1?
What are the numbers of the orders placed
by Bo Peep?
Introduction to Database Technology
Date
Session 1
Database Terminology
•
•
•
•
•
•
•
Entity
Attribute
Record (row)
Column or Field
Table or Relation
Primary Key
Foreign Key
Introduction to Database Technology
Session 1
Now do…
• Hands-on Exercise 1
– Explore the Yum Juices sample data
Introduction to Database Technology
Session 1
Relational Database Management
System (RDBMS)
• Software to manage data stored in related
tables
• Examples:
– MySQL
– Access
– Oracle
– SQL Server
Introduction to Database Technology
Session 1
SQL
• Structured Query Language
• Query language for accessing and modifying
data in relational databases
• Used by all RDBMSs
• Data Definition
– Store data
– Tables
• Data Manipulation
– Retrieve information
– Queries
Introduction to Database Technology
Session 1
MySQL is…
An Open Source
Relational Database Management System
(RDBMS)
Introduction to Database Technology
Session 1
Open Source Software
• Free to use, modify and redistribute
• Source code available for modification by
users
• Developed collaboratively
Introduction to Database Technology
Session 1
Finding out about MySQL
The MySQL website:
http://www.mysql.com/
The Reference Manual
Introduction to Database Technology
Session 1
Using MySQL for this Module
• MySQL is a client/server system
• Your database is stored on the School of
Computer Science MySQL server mysqlsrv
– A database has been created for you as you do not
have sufficient permissions to create a database on
this server
• You use the telnet Internet Protocol to connect to
the server
– You need a username and password from your tutor
before you can do this
Introduction to Database Technology
Session 1
Now do…
• Hands-on Exercise 2:
– Connect to your database
– Use basic SQL commands
Make sure you have got your MySQL
username and password from your tutor.
Introduction to Database Technology
Session 1
Review Basic SQL Commands
SHOW DATABASES
USE database_name
SHOW TABLES
CREATE TABLE
SHOW COLUMNS
INSERT INTO table_name
UPDATE table_name
SELECT * FROM table_name
DELETE FROM table_name
DROP table_name
Introduction to Database Technology
Session 1