1 / 33

Database Systems

Learn about the key features, structures, and querying techniques of database management systems for efficient data centralization and integrity. Explore how to retrieve, update, and delete records using SQL.

jvance
Download Presentation

Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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)

More Related