Introduction to Relational Databases and MySQL

1
Introduction to Relational Databasesand MySQL

• Lecturer Jerry Smallwood
• Email ubaceg4_at_dcs.bbk.ac.uk

2
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

3
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

4
ITApps Assessment (2009/10)

• ITApps Assessment Process (2009/10)
• Full details on the ITApps websitehttp//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 i.harrison_at_bbk.ac.uk
• Contact re. mitigating circumstances or any
  other issues that prevent you submitting the
  TMA/FMA on time.
• Note Lecturers cannot grant extensions to
  deadlines

5
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.

6
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/gl
  ossary/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/us
  ers/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-t
  erms/
• A set of related files that is created and
  managed by a database management system
  (DBMS).www.bradycommunications.com/perspectives/g
  lossary.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/g
  lossary.htm
• Source Google Define database

7
A database consists of

• A collection of data
• Software to store, access and manage the data
  (DBMS)

User B
User A
DBMS
Collection of Data
8
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

9
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

10
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

11
Data Storage

• Data is stored in tables
• Each row is a record
• Each column is an item of data (field) within the
  record

Pet Pet
pet_name pet_type
Sid snake
Minnie mouse
Mickey mouse
Grommit dog
Mog cat
Bubbles goldfish
12
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

13
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?
14
Flat File Database example 2
Multiple items in each order
ID Date OrderItems CustID Title Fname Sname
1 12/06/2008 16 Sparkling Sunshine 3.25 x 2 1 Citrus Zing 2.50 x 3 2 Mr Jack Hill
Table needs a record for each order item
Even more duplication of data!
15
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?

16
Yum Database Entities

• Each of the following entities will be stored in
  a table in the Yum database
• Customers
• Orders
• Order Items
• Juices

17
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

18
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?

19
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

20
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

21
Customer and Order Tables in the Yum Database
Primary key
Foreign key
Customer Table Customer Table Customer Table Customer Table Customer Table
CustID Title Fname Sname Address1
1 Mrs Jill Hill 2 Steep St
2 Mr Jack Hill 2 Steep St
3 Ms Bo Peep 5 Flockington Rd
4 Mr Humpty Dumpty 29 Shell Ave
5 Mr Harley Davidson 5b Bike Alley
6 Miss Mary Contrary 1 Flower Gdns
Order Table Order Table Order Table
OrderID CustID Date
1 2 12/06/2008
2 5 12/06/2008
3 6 15/06/2008
4 2 23/06/2008
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?
22
Database Terminology

• Entity
• Attribute
• Record (row)
• Column or Field
• Table or Relation
• Primary Key
• Foreign Key

23
Now do

• Hands-on Exercise 1
• Explore the Yum Juices sample data

24
Relational Database Management System (RDBMS)

• Software to manage data stored in related tables
• Examples
• MySQL
• Access
• Oracle
• SQL Server

25
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

26
MySQL is

• An Open Source
• Relational Database Management System (RDBMS)

27
Open Source Software

• Free to use, modify and redistribute
• Source code available for modification by users
• Developed collaboratively

28
Finding out about MySQL

• The MySQL website
• http//www.mysql.com/
• The Reference Manual

29
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

30
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.

31
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