Module introduction

Module introduction INF08104: Database Systems Brian Davison, 2011/12

Agenda • What is a database? • Module structure • Organising data • Simple queries • SQLzoo

What is a database? • A model of some real-world situation • A business tool • A technical solution • A valuable asset • A large organised collection of data

A little bit of history • Pre-1950: Negligible electronic data storage • 1950 – 1970: Application of computing to standard data problems • 1970: Edgar Codd, A Relational Model of Data for Large Shared Data Banks • 1970 – 1985: Massive growth in relational database use • 1985 – 2011: Experimentation with object-orientation, digital objects, etc.

The database approach Table name = EMP Schema Data

Database advantages • Data independence • Multi-user access • Data integration • Data integrity • Enforcement of standards • Security • Performance

ANSI-SPARC 3-level architecture

Data independence

The DBMS

Data model components

Data integrity • Type checks • e.g. ensuring a numeric field is numeric and not a character • Redundancy checks • direct or indirect - this check is not automatic in most cases and must be added by the database designer • Range checks • e.g. to ensure a data item value falls within a specified range of values, such as checking dates so that say (age > 0 AND age < 110). • Comparison checks • in this check a function of a set of data item values is compared against a function of another set of data item values. For example, the max salary for a given set of employees must be less than the min salary for the set of employees on a higher salary scale.

Roles • End users • Application programmers • Database administrator

Module structure • Theory • Standard architecture, security, concurrency • Design • Analysis, schema definition, diagrams • Use • SQL, embedded SQL • Administration • Backup & recovery, user management, scripting

Assessment • Coursework 40% • Details in week 4 • Database creation • SQL queries • Deadline week 9 – 1200, Friday 4th November • Exam 60% • Theory • SQL queries

Short break

Let’s organise some data! • Four volunteers • What are they? • What data could we store about them?

Entities and attributes Student Name Phone number Gender Date of birth Course

Entity = STUDENT Attributes Tuple

COUNTRY Entity = Attributes Tuples

Talking to the database • Which records? • Europe • Which columns? • Name • Population

Talking to the database SQL = Structured Query Language SELECT <column names> FROM <table name> WHERE <criteria> Which columns Which records

Query example SELECT name, population FROM country WHERE region = 'Europe'

Results

All columns SELECT * FROM countries WHERE region = 'Europe'

More than one condition SELECT name FROM countries WHERE region = 'Europe' AND name LIKE 'A%'

Ordering the results SELECT name FROM country WHERE region = 'Europe' • AND name LIKE 'A%' ORDER BY name

Armenia and Azerbaijan?

Your turn • You are a telephone company preparing your customers’ monthly bills. • Write a query to identify each customer and their Internet use for last month. The table name is USAGE

SELECT customer_id, gb FROM usage WHERE month = May AND year = 2011 Why is this wrong?

SELECT customer_id, gb FROM usage WHERE month = 'May' AND year = 2011 Solution

Your turn again • You are a university library. • Suggest a query to identify the borrowers with late items. • The name of the table is LOAN • For today's date, use TODAY

SELECT borrower_id FROM loan WHERE due_date < TODAY AND status = 'on loan' Solution

Module introduction

Télécharger la présentation

Plus de contenu associé