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!