F27DB Introduction to Database Systems - PowerPoint PPT Presentation

Loading...

PPT – F27DB Introduction to Database Systems PowerPoint presentation | free to view - id: 733553-NTdmN



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

F27DB Introduction to Database Systems

Description:

F27DB Introduction to Database Systems Dr. Helen Hastie, h.hastie_at_hw.ac.uk Room: LT2 Material available on Vision Vision (modified from Monica Farrow s s) – PowerPoint PPT presentation

Number of Views:149
Avg rating:3.0/5.0
Slides: 36
Provided by: JennyC159
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: 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 parameters maybe from forms
Server Program with Functionality Code
Response e.g. html for 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 parameters maybe from forms
My Website
PHP scripts
Server-side PHP scripts
Response e.g. html for 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 columns mysqlgt 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
    MySpy GROUP 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
About PowerShow.com