1. Database Systems Marcus Kaiser School of Computing Science Newcastle University

2. Requirements • Remove Redundancy – or at least control it • Data Integrity • Separation of data and program • Prevent inconsistency when systems fail • Allow multiple (simultaneous) interactions • High level of security • Remote access • Access by humans and computers

3. Database Management Systems: Structure DBMS Software Database Query Language User Query Pre-Defined Applications: Report Generator Query by Forms Application Program

4. Database Management Systems - Features • Data Centralization • all data can be stored in one database • all users see the same, consistent set of data • eliminates data redundancy • increases data integrity • clients can connect to the database irrespective of its location.

5. Database Structures

6. Surname Initial Title Department Smith A Mr Sales Smith K Ms Marketing Brown M Mrs Engineering Database Structures • Databases consist of a set of Tables • A Table holds a set of Fields for each Record • e.g. Staff Table Columns or fields Record

7. Creating Tables • Data Definition Language • Used to create tables • Allows you to define: • Table Name • Fields • Name • Format (e.g. 20 characters of Text; Integer)

8. Populating Tables • Once the Tables have been defined they can be filled: • by bulk loading from text files • by bulk loading from other databases • by queries

9. Data Independence • changes to the data can be made without changing the programs which access it • 2 Views of data • Physical View • actual location of the computer holding the data • actual location of the data on a storage device • the actual format of the data • Logical View • view of the records and fields of data as they are seen by the user • presented via the query language • independent of the Physical View • e.g. users can ask for all workers with the surname Smith

10. Querying the Database • Queries are used to: • retrieve data • update fields • delete records • insert records • The “Standard” Query Language is called SQL • Structured Query Language • Almost all Databases support SQL • However there are other approaches

11. The SQL Language

12. SQL: Basic Select • SELECT retrieves information from a table • Simplest form: SELECT columns FROM table Which columns or fields to display The name of the table

13. Surname Initial Title Department Smith A Mr Sales Smith K Ms Marketing Brown M Mrs Engineering Surname Initial Smith A Smith K Brown M Select Example we want to get the names of all members of staff. SELECT Surname, Initial FROM Staff Staff Gives:

14. Personnel Example Revisited (1) • Recall • a Companies Personnel Address Database for the employees: Personnel • What SQL command would give you all: • The telephone numbers? • The addresses?

15. Answer (1) • The telephone numbers? • The addresses?

16. Where Clauses • SELECT columns FROM table can only retrieve whole columns • a WHERE clause can be used to select only the rows (records) of those columns which meet a particular qualifier • e.g. we want to get the names of all staff working in Sales: SELECT Surname, Initial FROM Staff WHERE Department = ‘Sales’ • the qualifier can contain OR or AND: SELECT Surname, Initial FROM Staff WHERE Department = ‘Sales’ AND Surname = ‘Smith’

17. Personnel Example Revisited (2) • Recall • a Companies Personnel Address Database for the employees: Personnel • What SQL command would give you: • The telephone number for A Smith? • The address for C.A. Jones?

18. Answer (2) • The telephone number for A Smith? • The address for C.A. Jones?

19. Sorting • We can sort the result of the query: SELECT Initial, Surname FROM Staff ORDER BY Surname ASC, Initial ASC (ASC = ascending; DESC = descending)

20. Personnel Example Revisited (3) • Recall • a Companies Personnel Address Database for the employees: Personnel • What SQL command would give you: • Name and Town sorted in reverse alphabetical order of Town?

21. Answer (3) • Name and Town sorted in reverse alphabetical order of Town?

22. Number 1 Set Functions • We can also perform operations on sets of data • count (column) counts the number of entries in a column SELECT count(Surname) FROM staff • WHERE Department = ‘Sales’ • returns: • count(*) returns the number of records in a table Column or field

23. Personnel Example Revisited (4) • Recall • a Companies Personnel Address Database for the employees: Personnel • What SQL command would give you: • The number of members of staff who live in Newcastle?

24. Answer (4) • The number of members of staff who live in Newcastle?

25. Family_Name Smith Smith Brown Renaming Fields • Sometimes we want to rename a field • We can use the AS operation SELECT Surname AS Family_Name FROM staff • returns: Column or field

26. Multi-Table Retrieval • We can query more than one table: • e.g. Where does Every Member of Staff Work ? SELECT Staff.Initial,Staff.Surname,Place.City FROM Staff,Place WHERE Staff.Dept = Place.Dept • e.g. Who Works at Hull ? SELECT Staff.Initial,Staff.Surname FROM Staff,Place WHERE Staff.Dept = Place.Dept AND Place.City = ‘Hull’

27. Why Place.Dept = Staff.Dept? • To distinguish between Fields in different tables with the same name we prepend the Field with the name of the Table • Place.Dept • Staff.Dept • But why Place.Dept = Staff.Dept? • When we join the two tables together each record from Place is matched with each record from Staff • So we have every member of Staff Matched with every Place • But we only want the ones that represent real ‘matches’ Sales Marketing Marketing Sales Marketing Marketing Marketing Sales Engineering Marketing Engineering Sales Sales Marketing Sales Engineering Sales

28. Multi-Table Retrieval • What does this do ? What is the Result of the Query ? SELECT Place.City FROM Staff,Place WHERE Staff.Surname = ‘Smith’ AND Staff.Initial = ‘C’ AND Staff.Dept = Place.Dept • Write a Query to Find out the Initial and Department of Jones, who works in Leeds • Write a Query to Find out how many people work in Leeds

29. Answer (5) • What does this do ? What is the Result of the Query ? SELECT Place.City FROM Staff,Place WHERE Staff.Surname = ‘Smith’ AND Staff.Initial = ‘C’ AND Staff.Dept = Place.Dept • Write a Query to Find out the Initial and Department of Jones, who works in Leeds • people work in Leeds

30. Inserting Rows INSERT INTO Staff (Name,Initial,Dept) VALUES (‘Green’,’D’,’Eng’) INSERT INTO Place (Dept,City) VALUES (‘Manufacturing’,’Bristol’)

31. Updating Rows UPDATE Place SET City = ‘Coventry’ WHERE Dept = ‘Sales’ UPDATE Staff SET Dept = ‘Systems’ WHERE Dept = ‘Eng’

32. Deleting Rows DELETE FROM Staff WHERE Name = ‘Smith’ AND Initial = ‘C’

33. Summary • Database Management Systems • Centralize data • Provide remote access • Data is stored in tables • With fields for each piece of data • A set of fields makes a record • Data has physical and logical views • Access data through SQL (Structured Query Language)