Databases are books, demographic records, student Production - PDF document

Material in course textbook Programmerade system “Computer Science: An Overview” TDA143, 2012-2013 9 th /10 th /11 th Edition, J. Glenn Brookshear Lecture on Databases Chapter 9 Graham Kemp kemp@chalmers.se Room 6475, EDIT Building http://www.cse.chalmers.se/~kemp/ Why study databases? Examples • Banking Banking, ticket reservations, customer – Drove the development of DBMS records, sales records, product records, • Industry inventories, employee records, address – Inventories, personnel records, sales … Databases are books, demographic records, student – Production Control records, course plans, schedules, – Test data surveys, test suites, research data, everywhere! • Research – Sensor data genome bank, medicinal records, time – Geographical data tables, news archives, sports results, e- – Laboratory information management systems commerce, user authentication systems, – Biological data (e.g. genome data) web forums, www.imdb.com, the world wide web, … File-oriented information system Problems with working with files • Redundancy – Updates – Wasted space Customer Payroll Employee Inventory Sales records records records records records • Changing a data format will require all application programs that read/write these files to be changed. Customer • Sharing information between departments Payroll Personnel Purchasing Marketing service department department department department department can be difficult. 1

Database-oriented information A database is … system • a collection of data Customer Marketing service department department • managed by specialised software called a Integrated database management system (DBMS) database (or, informally, a “database system”) Payroll Purchasing department department Personnel • needed for large amounts of persistent, department structured, reliable and shared data Using a DBMS: an overview Centralised control of data • amount of redundancy can be reduced – less inconsistency in the stored data Application Program • stored data can be shared • standards can be enforced • security restrictions can be applied Database Management • data integrity can be maintained – validation done in one place System • conflicting requirements can be balanced • provides data independence – can change storage structure without affecting applications Motivation for database systems Traditional File Structures Needed for large amounts of persistent, structured, reliable and shared data (Ted Codd, 1973) A short digression … • Large amounts: – needs indexing for fast access – needs a load utility • Persistent: – needs schema definition of types which evolves • Structured: – storage schema held with data – query language (e.g. SQL) independent of storage • Shared: – locking mechanism for concurrent update – access control via DBMS – centralised integrity checking • Reliable: – changes to disc pages are logged – commit protects against program of disc crash – can undo (rollback) uncommitted updates 2

UNIX file management Actual organisation is hidden • Just as the file management system in an operating system gives the users the illusion that a text file is stored on disc as a long consecutive sequence of characters … • … a database management system gives the users the illusion that their data are stored on disc in accordance with a data model . Data models Data models: brief overview • “No data model” • Storing data in a computer system – Flat files requires describing the data according to some data model , in a form which can be • “Classical” data models represented directly within the computer. – Hierarchical (tree) – Network (e.g. CODASYL) (graph) • A data model specifies the rules – Relational (Codd, 1970) (tables) according to which data are structured and • Semantic data models, e.g. also the associated operations that are – Entity-Relationship model (Chen, 1976) permitted. – Functional Data Model (Shipman, 1981) – SDM (Hammer and McLeod, 1981) Relational DBMSs Relation Schemas • Very simple model • In the relational data model, a design consists of a set of relation schemas . • Familiar tabular structure • A relation schema has • Has a good theoretical foundation from – a name, and mathematics (set theory) – a set of attributes (+ types): • Industrial strength implementations, e.g. Courses(code, name, teacher) – Oracle, Sybase, MySQL, PostgreSQL, Microsoft SQL Server, DB2 (IBM mainframes) name • Large user community attributes 3

Schema vs Instance From schema to database • Schema (or intension or a relation) • The relations of the database schema become – name and attributes of a relation the tables when we implement the database in a Courses(code, name, teacher) DBMS. The tuples become the rows: • Instances ( or extension of a relation ) Courses(code, name, teacher) – the actual data relation schema – a set of tuples : table instance { (’TDA357’, ’Databases’, ’Niklas Broberg’), (’TIN090’, ’Algorithms’, ’Devdatt Dubhashi’) } code name teacher tuples ’TDA357’ ’Databases’ ’Niklas Broberg’ ’TIN090’ ’Algorithms’ ’Devatt Dubhashi’ Keys Composite keys • Relations have keys – attributes whose • Keys can consist of several attributes values uniquely determine the values of all other attributes in the relation. Courses(code, period, name, teacher) Courses(code, name, teacher) {(’TDA357’, 2, ’Databases’, ’Graham Kemp’), key (’TDA357’, 3, ’Databases’, ’Niklas Broberg’)} {(’TDA357’, ’Databases’, ’Niklas Broberg’), (’TDA357’, ’Algorithms’, ’Devdatt Dubhashi’)} Schemas and subschemas Database design • A schema is a description of the entire • We design the conceptual model for our database structure. database using a high-level data model like the Enitity-Relationship model … • A subschema is a description of only a part of the database structure. • … then we translate this design to the relational model (for implementation in an – Tailored to the needs of a user group RDBMS). – Controls access to data 4

Enitity-Relationship Diagram Translation to relations Example: • A relationship between two entities is code name translated into a relation, where the attributes are the keys of the related name Course LecturesIn Room #seats entities. code name teacher name Course Room #seats LecturesIn • A course has lectures in a room. teacher • A course is related to a room by the fact that the course has lectures in that room. Courses(code, name, teacher) Rooms(name, #seats) What? LecturesIn(code, name) • A relationship is often named with a verb (e.g. HasLecturesIn) Translation to relations Relational operators (1) • A relationship between two entities is • Selection translated into a relation, where the – Choose rows from a relation attributes are the keys of the related – State condition that rows must satisfy entities. code name σ condition (T) name Course Room #seats LecturesIn Examples: teacher σ seats>100 (Rooms) Courses(code, name, teacher) σ (code=”TDA143” AND day=”Friday”) (Lectures) Rooms(name, #seats) LecturesIn(code, name) Relational operators (2) Relational operators (3) • Projection R 1 x R 2 – Choose columns from a relation – Cartesian product – State which columns (attributes) – Combine each row of R 1 with each row of R 2 R 1 ⋈ ⋈ condition R 2 ⋈ ⋈ π A (T) – join operator Examples: – Combine row of R 1 with each row of R 2 if the π code (Courses) condition is true π name,seats (Rooms) R 1 ⋈ ⋈ condition R 2 = σ condition (R 1 x R 2 ) ⋈ ⋈ 5

SQL SELECT-FROM-WHERE • SQL = Structured Query Language • Basic structure of an SQL query: • A very high-level declarative language. SELECT attributes FROM tables – Specify what information you want, not how to WHERE tests over rows get that information (like you would in e.g. Java). SELECT A • Based on Relational Algebra FROM T π A (σ C (T)) WHERE C Example: Example: course per teacher course per teacher TDA357 2 Niklas Broberg TDA357 2 Niklas Broberg GivenCourses = GivenCourses = TDA357 4 Rogardt Heldal TDA357 4 Rogardt Heldal TIN090 1 Devdatt Dubhashi TIN090 1 Devdatt Dubhashi SELECT * SELECT * FROM GivenCourses FROM GivenCourses WHERE course = ’TDA357’; WHERE course = ’TDA357’; course per teacher course per teacher Result = Result = What? TDA357 2 Niklas Broberg TDA357 2 Niklas Broberg TDA357 4 Rogardt Heldal TDA357 4 Rogardt Heldal Example: Example: course per teacher course per teacher TDA357 2 Niklas Broberg TDA357 2 Niklas Broberg GivenCourses = GivenCourses = TDA357 4 Rogardt Heldal TDA357 4 Rogardt Heldal TIN090 1 Devdatt Dubhashi TIN090 1 Devdatt Dubhashi SELECT course, teacher SELECT course, teacher FROM GivenCourses FROM GivenCourses WHERE course = ’TDA357’; WHERE course = ’TDA357’; course teacher course teacher Result = Result = What? TDA357 Niklas Broberg TDA357 Niklas Broberg TDA357 Rogardt Heldal TDA357 Rogardt Heldal 6