F27DB Introduction to Database Systems

1
F27DB Introduction to Database Systems

• Dr. Helen Hastie, h.hastie_at_hw.ac.uk
• Room LT2
• Material available on Vision Vision
• (modified from Monica Farrows slides)
• Twitter _at_IntroDBHW and IntroDBHW

2
Overview

• Before computers
• Introduction to databases
• What do they look like?
• Why are they like that?
• What database management systems could we use?
• How to use MySQL in the Linux lab
• A web application with a database
• Designing web pages using XHTML
• Programming PHP scripts to query the database and
  produce web page reports
• Looking ahead to 2nd year module and beyond

3
Before Computerised Databases

• Information could be stored in box of record
  cards for a class of students
• Each card contains -
• name
• address (term and home)
• date of birth
• matric no.

4
What is a database ?

• A database is an organized collection of data,
  systematically organized for easy access and
  analysis
• The box is a table in the database
• The cards are records
• Records are made up of fields
• Having designed and created a database, we can
  then amend, query, sort and report the data

5
Edge-notched card Invented in 1896

• The card held details on literature on the study
  of metals
• The card was preprinted with a fact by each hole,
  maybe a hole for each metal such as copper,
  silver etc.
• You type the information about the name of the
  book in the empty space, and then clip out a
  notch on the edge for every true fact.

6
Another edge-notched card

• This card is just printed with numbers so you
  have to decide for yourself which numbers mean
  which facts.
• Cut the notches out for facts that are true, with
  the clipper tool.

7
Edge-notched cards demo

• Blank preprinted cards
• Add data (a picture)
• Round hole NO
• Make a notch YES
• A simple true/false state is the basis of all
  data stored on computer

SWIM PET STRIPES LEGS
8
Animals information system
9
Edge-notched card retrieving information

• Information is retrieved by putting one or more
  long wire pins through certain holes.
• As the pins were lifted, the cards that were
  notched in the hole positions where the pins were
  inserted would be left behind as rest of the deck
  was lifted by the pins.
• Find all the pets. . .

10
Edge-notched card logical operations

• The cards lifted out match a logical NOT
  operation
• Find all the creatures that do not swim

SWIM PET STRIPES LEGS
11
Edge-notched card logical operations

• Using two or more pins produced a logical AND
  function.
• Find all the creatures who can swim and also has
  legs

SWIM PET STRIPES LEGS
12
Edge-notched card logical operations

• Combining the cards from two different selections
  produced a logical OR.
• Find the creatures with stripes or legs (or both)
• Quite complex manipulations, including sorting
  were possible using these techniques.

SWIM PET STRIPES LEGS
SWIM PET STRIPES LEGS
13
A complete system today
Request with parametersmaybe from forms
Server Program with Functionality Code
Response e.g. htmlfor display
Queries (e.g. SQL)
Order
Browse
Search
Management Programs (application in e.g. java
or maybe also a web app)
Data/ Success?
Data Management (updates)
Queries (e.g. SQL)
Customer email
Data/ Success?
Manager Reports
14
How you will start

• You send commands to the database from a terminal
  window in the Linux lab, EM250
• It is all text-based, no GUI
• We can define tables, view the data and perform
  updates.

Queries (e.g. SQL)
Data/ Success?
15
A complete system for this module

Apache server program
Request with parametersmaybe from forms
My Website
PHP scripts
Server-side PHP scripts
Response e.g. htmlfor display
Queries (e.g. SQL)
Add
Search
View
Data/ Success?
Data Management (updates)
Queries (e.g. SQL)
Data/ Success?
Manager Reports
16
Database Management Systems

• A DBMS provides security, integrity, concurrency
  control and recovery control
• There is a comparison on wikipedia of almost 50
  different DBMSs.
• In the CS department we have
• MySQL industrial strength
• Open source, available from the Linux lab
• Oracle industrial strength
• Oldish version, available from the Linux lab
• Microsoft Access, suitable for small applications
• Installed on each Windows PC

17
DBMS Compatibility

• Relational DBMSs provide the same sort of
  features and use the same language, SQL, to
  define the tables and update and retrieve the
  data
• However, there are differences!
• This module will teach you the basic principles
• If you start using a different DBMS, you will
  have to find out exactly what features that
  particular one provides

18
MySQL

• Initially released in 1995
• Popular for web applications, particularly in
  conjunction with the PHP scripting language
• Continually being improved
• MySQL is available from terminal windows in the
  department Linux lab
• Type in commands and view the results

19
MySQL Limitations

• There are some limitations. E.g.
• It is forgiving of incorrect data. So if are
  trying to insert a date into a column and you
  provide an incorrect date, it helpfully puts in a
  date of 0 and carries on. It only tells you if
  you have switched warnings on.
• Many DBMSs allow you to constrain the values of
  the data. E.g. only M or F for gender. However,
  although MySQL accepts these commands as valid
  commands, they are not acted upon. You will have
  to do your own checks.
• A relational database is usually made up of gt1
  tables, with relationships between these tables.
  So you might wish to constrain the data entered
  in one table to be compatible with data in
  another table. Again, MySQL accepts these
  commands as valid syntatically, but only enforces
  these commands in one type of table (InnoDB). We
  will use these tables.

20
MySQL at home

• This module assumes that you will do all the work
  in the lab
• However, if you wish to work on your own
  computer, you could try installing something like
  easyPHP
• EasyPHP is a WAMP software bundle that installs
  web server services on to the Windows computer
  and allows quick and easy development of PHP and
  MySQL on a localhost (also known as 127.0.0.1).
  The package includes an Apache server, a MySQL
  database, and the PHP extension.
• Unfortunately, departmental staff do not have the
  time to help with this.

21
An example - spies

• Your friendly international spy recruitment
  agency is contemplating installing a database for
  the storage of data about its spies. They would
  like you to advise them on the design and
  implementation of its database.
• The agency requires to store information about
  spies.
• Each spy should have a unique code name, a first
  name, last name, date of birth, sex,
  distinguishing mark, and payment due.

22
The spy table

• Each spy should have a unique code name, a first
  name, last name, date of birth, sex,
  distinguishing mark, and payment due.

code name first name last name date of birth sex mark amount due
007 James Bond 12 December 1972 M mole on chin 5050
bud Fanny Charleston 31 July 1983 F scar on cheek 25.67
freddie John Smith 05 September 1954 M one finger missing 312.5
23
Spy table definition

• First you need to define the table
• What will the table be called?
• What will the columns be called?
• E.g. firstName. Dont use spaces
• What type of data will be stored there? E.g.
• VARCHAR(5) A variable number of characters, up to
  a maximum
• CHAR(1) A fixed number of characters
• DECIMAL (9,2) A decimal number,
• up to a maximum number of characters
• with a fixed number of decimal places
• INTEGER(5) An integer number, up to a maximum
  number of characters
• DATE
• Etc the above types are the most useful ones

24
SQL

• We use MySQL Structured Query Language
• For definition, such as table definition
• For inserting, deleting, updating and retrieving
  data
• Today well just look at a few commands
• CREATE TABLE
• INSERT VALUES
• SELECT

25
CREATE TABLE MySpy

• CREATE TABLE MySpy (
• codeName VARCHAR(10) PRIMARY KEY,
• firstName VARCHAR(20),
• lastName VARCHAR(20),
• dateOfBirth DATE,
• gender CHAR(1),
• mark VARCHAR(20),
• amountDue DECIMAL (9,2),
• )ENGINEINNODB
• The last line specifies the table type.
• Note the Primary Key

26
Primary key

• In a relational database table, you should
  usually specify a primary key
• This is a field which uniquely identifies the
  record
• National insurance number identifies a person
• Country name identifies a country
• Country name and town name together identifies a
  town
• Christchurch in New Zealand and in England

27
Adding in some data

• Use the INSERT command to add some rows to the
  table
• Surround text and dates with single quotes
• Dont put quotes around numbers
• The format of the date is important
• mysqlgtINSERT INTO MySpy VALUES ('007','James',
  'Bond', '1972-12-01','M', 'Mole on chin', 5050)

28
Viewing the data (1)

• To see the complete contents of the table
• mysqlgt SELECT FROM MySpy

29
Viewing the data (1)

• To see the contents ordered e.g. mysqlgt SELECT
  FROM MySpy ORDER BY dateOfBirth

30
Viewing the data (1)

• To see just some of the columnsmysqlgt SELECT
  codeName, firstName, lastName FROM MySpy
• To see just some of the rows
• mysqlgt SELECT FROM MySpy WHERE gender M

31
Viewing the data (1)

• Combine these as appropriate
• mysqlgt SELECT firstName, dateOfBirth FROM MySpy
  WHERE gender M ORDER BY dateOfBirth

32
Viewing the data (2)

• Select some of the rows on multiple conditions
• mysqlgt SELECT FROM MySpy WHERE firstName
  James AND lastName Bond
• mysqlgt SELECT FROM MySpy WHERE firstName
  James OR lastName Bond

33
Viewing the data (2)

• Perform aggregate functions
• mysqlgt SELECT count() FROM MySpy WHERE gender
  M
• mysqlgtSELECT sum(amountDue) FROM MySpy
• mysqlgt SELECT gender, sum(amountDue) FROM
  MySpyGROUP BY gender

34
Using MySQL

• The first lab (after this lecture in EM250) is an
  introduction to MySQL
• Work through the handout so that you become
  familiar with the system
• You enter commands by typing them at the command
  prompt.
• You can use the up arrow to retrieve previous
  commands.
• It is useful to store long commands in a text
  file, and run them from there
• You dont have to retype the whole thing if you
  made a mistake. Although there are MySQL commands
  to edit a command, its easier just to use a
  standard text editor
• Your command is safely stored in case you want to
  run it again or alter it.

35
Helping each other

• In the lab there will be one or two lecturers and
  three lab helpers.
• We want you to learn to use MySQL, not get stuck
  with the linux system in the lab.
• See Partner List for first few labs
• CS/IS students - Help them with linux if
  necessary
• Elective students ask for help if necessary

36
BEFORE TODAYs LAB

• First year CS IS students do nothing
• Others please go to the Computer Science Helpdesk
  in EM1.33
• If you arent registered to use the CS linux lab,
  fill in form and register. Then ask for a MySQL
  account.
• If you have already registered to use any CS dept
  machines for another reason, just ask for a MySQL
  account