Databases

Session - Overview • • • • • •

What is our starting point? Introductions! Databases Database Management System Relational Databases Query Methods Problem areas for student learning.

Databases – Definition • “A database is a persistent organised store of data” • Persistent? – Data is retained even when the application is not in use. • Organised? – The data is logically structured to enable easy storage, retrieval and manipulation of information. • Store of data? – Granular level information, grouped together to describe an entity

Databases - Definition • A database does not have to be electronic. • An address book, that most people have, is a database. The book itself provides a structure to contain the information. • So formally the term database describes both the data and supporting data structures.

Database Management Systems (DBMS)

• A database management system is a software package that manages access to a database. • Microsoft Access, Oracle, SQL Server, MySQL, and many new freebies on the Cloud!

DB

DBMS

Database Management Systems (DBMS)

• A database management system is a software package that manages access to a database. • Microsoft Access, Oracle, SQL Server, MySQL, and many new freebies on the Cloud!

Database System

DB

DBMS

Database Management Systems (DBMS)

Application

SQL

www

DBMS

DB

Database Management Systems (DBMS) • Your database is simply a holding place for your data. It isn’t capable of doing anything other than that. • The DBMS is what allows us to interact with the database. It allows us to design, build and maintain the database and once built we can.....

• Create • Read • Update • Delete

Relational Databases: Entities • An entity is anything which can be uniquely described by a set of attributes (characteristics/properties) – E.g. A gallery, a painting, an artist, a supplier, an exhibition. – A gallery can be uniquely described by its name, address, postcode, telephone number etc. Therefore a gallery is an entity. – Think of attributes that may uniquely describe the other entities.

• An entity is a concept

Relational Databases: Tables • A table is the physical form of an entity. • A field is the physical form of an attribute • E.g. A gallery is an entity which will have its own table where the details of particular galleries may be stored. Fields Name

Address

PostCode

Telephone

Mima

Middlesbrough

TS1 3BA

01642 123456

Tate Modern

London

L13 3RF

0845 123456

Baltic

Gateshead

NE14 1TH

0845 654321

Records

• The collection of data for each name constitutes a record • Within each record the information is held in fields

Organisation Poor Example – too much repetition Artist Name Andy Warhol Andy Warhol Andy Warhol

Artist DOB Artist Origin 06/08/1928 USA 07/08/1928 USA 08/08/1928 USA

Painting Title Marilyn Eight Elvises Campbell's soup

Genre Pop Art Pop Art Pop Art

Date 1961 1963 1962

Organisation

Better Example – but how are they linked? Artist Name Andy Warhol

Artist DOB 06/08/1928

Artist Origin USA

Painting Title Marilyn Eight Elvises Campbell's soup

Genre Pop Art Pop Art Pop Art

Date 1961 1963 1962

Organisation Perfect Example – both tables linked using KEYS! Primary key

Artist ID

Artist Name

Artist DOB

Artist Origin

1

Andy Warhol

06/08/1928 USA

Painting Title

Genre

Date

Artist ID

Marilyn

Pop Art

1961

1

Eight Elvises

Pop Art

1963

1

Campbell's soup Pop Art

1962

1

Foreign Key

Relationships between tables A gallery can display many paintings

• A relational database allows tables with common information to be connected. • E.g An gallery displays paintings, and artists create paintings. • Rather than putting all the information into one table we can split it into 3 to avoid repetition of records • This organisation causes confusion amongst students!

Gallery 0..n

THE DATA MODEL

Painting

1..n Artist

An artist can create many paintings

Major Components of Database System • Tables • The table structures will hold data on areas that are needed, eg galleries, artists and paintings. • Queries • There would be no point having tables of stored data if they cannot access it. A DBMS allows you to set up queries to pull out information, e.g. all artists born before 1850. Queries allow you to select, insert, update and delete data. • Reports • To give formalised output of information and can include data from more than one table, e.g. All artists exhibiting at Mima in January. Can also include summaries/processed calculations.

TIME FOR SOME EXAMPLES

Resources • This is a large subject, with some of the topics only touched upon here, but a full set of workbooks will accompany this workshop • Useful references... • • • • • •

Sample data models Microsoft Dynamic Data Zoho Creator Mendix Caspio Microsoft Lightswitch

• Any questions? • During the break please think about which areas you would like to explore further.....

Take a break!