Module introduction

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

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

3. 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

4. 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.

5. The database approach Table name = EMP Schema Data

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

7. ANSI-SPARC 3-level architecture

8. Data independence

9. The DBMS

10. Data model components

11. 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.

12. Roles • End users • Application programmers • Database administrator

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

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

15. Short break

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

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

18. Entity = STUDENT Attributes Tuple

19. COUNTRY Entity = Attributes Tuples

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

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

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

23. Results

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

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

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

27. Armenia and Azerbaijan?

28. 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

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

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

31. 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

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