Theory of Databases Module lecturer Dr Meurig Beynon

Theory of Databases Module lecturer: Dr Meurig Beynon, Room 3. 15 Module Content • Generalities on databases • Database models: Theory and Practice • Entity-relationship models • Relational models: algebraic and logical • Commercial relational languages 10/26/2021 1 CS 319 Theory of Databases

CS 319 Theory of Databases Module Content (cont. ) • Technical Issues and Algorithms • Optimisation • Integrity constraints • Database design • Future Trends: Theory and Practice • Object-oriented models • Extended relational systems • The Relational Model and SQL 10/26/2021 2 CS 319 Theory of Databases

Theory in Computer Science? Theory in computer science is typically viewed as - associated with formal foundations for computing - based on mathematics (can there be any other kind of theory? ) - used to justify and to inform practical principles 10/26/2021 3 CS 319 Theory of Databases

Common perceptions about theory of CS Computer Science means accounting for computing practice using mathematical theory Mathematical theory is the essential foundation: practical computing is its application The mathematical foundations of computer science are the bedrock of the subject - they frame computing in an abstract manner independent of technologies, and how these abstractions are realised in practice is a relatively ephemeral concern 10/26/2021 4 CS 319 Theory of Databases

Ephemeral conveys a number of ideas: temporary reflecting a current situation or fashion not of abiding interest of subordinate significance 10/26/2021 5 CS 319 Theory of Databases

In support of this view of theory … From a mathematical perspective … have a powerful abstract theory of computation: all programming platforms are Turing equivalent in some fundamental sense it is difficult to know what meaning to attach to computational activity for which there is no abstract mathematical model computation has - and always had - a crucial role in mathematics itself 10/26/2021 6 CS 319 Theory of Databases

In support of this view of theory … From a broader scientific perspective … It is possible to regard pure sciences such as physics as establishing an intimate association between mathematical models and our interaction with the natural world CS might plausibly be viewed as an embryonic Science of the Artificial, and we might expect abstract mathematical models to be ideally suited to framing such a discipline 10/26/2021 7 CS 319 Theory of Databases

In support of this view of theory … From a cognitive perspective … reasoning is an important constituent of human thought - logic is seen by many computer / cognitive scientists as the essential constituent of human intelligence, and the computer as an appropriate metaphor for the brain cf. the logicist perspective on artificial intelligence … 10/26/2021 8 CS 319 Theory of Databases

Contrary to this view … Theory has yet to deliver programming tools and principles that meet practical computing needs comprehensively • Functional programming • Formal models of concurrency • Formal methods for software development … represent a partial solution to practical issues 10/26/2021 9 CS 319 Theory of Databases

Contrary to this view … Crucial aspects of practical computing have no satisfactory supporting formal theory • • human-computer interaction end-user programming object-oriented modelling object-oriented software development … mathematical models are of limited value 10/26/2021 10 CS 319 Theory of Databases

Contrary to this view … Some of the most significant aspects of computing practice are pre-theory and subjective in character Consider the observation and experiment involved e. g. • in requirements capture • in customising interfaces for personal use • in software development based on prototyping … implicit knowledge embodied in interaction not explicit in mathematical models 10/26/2021 11 CS 319 Theory of Databases

Contrary to this view … Interaction in the world can only be viewed as accounted for by theory if we accept a reductionist stance on the nature of science: consider the implications / meta-implications of a Theory of Everything in physics Many aspects of human activity and interaction in the world [arguably] involve more than applications of logic: interaction in the world can be viewed as conceptually prior to reasoning, and not explicable in terms of logic: consider e. g. Rodney Brooks's robotics research on themes such as Intelligence without Reason, Intelligence without Representation 10/26/2021 12 CS 319 Theory of Databases

Contrary to this view … Modern developments of computing challenge the mind-body dualism that underlies the idea of the brain as a complex computational device that can be conceptually separated from its embodiment: consider e. g. - brain-mediated communication - meaning as situated - context dependence in natural language Mathematics does not account for situated meaning 10/26/2021 13 CS 319 Theory of Databases

Two possible approaches to developing CS [evolutionary] accept that there is an essential duality within computing that distinguishes the hard core science of computing from the soft ephemeral pseudo -science of computing practice, and focus on developing and extending classical theories with a view to giving as comprehensive an account of computing practice as is possible [revolutionary] recognise that computer science as classically conceived is inadequate as a foundation for practical computing and artificial intelligence, and that this inadequacy cannot be resolved without reappraising the whole issue of how mathematical models and activities in the world are connected 10/26/2021 14 CS 319 Theory of Databases

Scope of CS 319 Will use data modelling principles for databases as the arena within which to explore these broad issues concerning the role of theory in computer science Complementary to other modules, e. g. that concern • formal approaches to software development • artificial intelligence • robotics … personal views beyond scope of CS 319 developed in CS 405 An Introduction to Empirical Modelling 10/26/2021 15 CS 319 Theory of Databases

The principal theme of CS 319 How do theory and computing practice relate with specific reference to databases? Key ideas: The counterpart of the classical theory of computation in databases is Codd's relational theory. There is an excellent correspondence between relational theory and practical database application of a certain kind. Relational databases can be seen as a precursor for two principal kinds of computer application: environments for enduser programming and computer-based models of real-world state. 10/26/2021 16 CS 319 Theory of Databases

The principal theme of CS 319 How do theory and computing practice relate with specific reference to databases? Key ideas (cont. ): There aspects to the relational theory (especially those concerned with database design) that challenge the classical view of how an abstract mathematical model represents a situation in the world. Modern database applications oblige us to question the adequacy of relational theory, and to consider the extent to which a relational framework can cope with notions such as objects, interfaces and agents. 10/26/2021 17 CS 319 Theory of Databases

Content of CS 319 To put the key ideas in perspective, address the following topics: An extended account of relational theory and its applications in respect of the development of relational query language and database design. The qualities of the relational model both as it was first conceived by Codd and as it has (and has not) been embodied in practical relational databases. 10/26/2021 18 CS 319 Theory of Databases

Content of CS 319 A critical assessment of the relational database model from the proponents of alternative kinds of database, such as object-oriented and extended relational models. The possible limitations of the relational model where modern computing applications and technologies are concerned. An evaluation of the various database model critiques and a personal perspective on the future of databases. 10/26/2021 19 CS 319 Theory of Databases

Aspects of a Computer Science education … Three kinds of activity involved in learning CS • learning theory • gaining practical knowledge and skills of computing • developing a critical appreciation of how theory and practice are related Principal emphasis in CS 319 is on the first and third aspects … 10/26/2021 20 CS 319 Theory of Databases

Special lecture arrangements weeks 18 and 19 Dr Hugh Darwen was an IBM consultant on databases: he is our guest lecturer in weeks 18 and 19 NOTE: the lectures in week 18 will be on The Relational Model and SQL thus: Monday February 21 st at 2 pm and 5 pm in CS 1. 04 Thursday February 24 th at 2 pm and 5 pm in CS 1. 04 the lectures in week 19 will be on Temporal Data and the Relational Model thus: Monday February 28 th at 2 pm and 5 pm in CS 1. 04 Thursday March 3 rd at 2 pm and 5 pm in CS 1. 04 10/26/2021 21 CS 319 Theory of Databases

Some context for the Theory of Databases Setting the scene Industrial perspectives Academic perspectives Sources for the CS 319 module 10/26/2021 22 CS 319 Theory of Databases

Setting the scene for theory of databases - relations M J Ridley, University of Bradford (TLAD WS 2003) DB systems or DB theory – or “Why don’t you teach ORACLE” “The modern age of databases may [NB! - WMB] have started with relational databases which despite their ubiquitous nature we should remember were looked an as of only academic interest at one time. What surely marks this era [the early 1970 s] out is the sound theoretical basis of that database model and the progress that was made because of this. … But what age are we in now? ” 10/26/2021 23 CS 319 Theory of Databases

Setting the scene for theory of databases - objects Object oriented DBs emerged in the 1980 s in response to the feeling that relational databases were inadequate for certain classes of applications. . . Disadvantages of OODBs Brown, A. W. OODBs: Applications in S/W Engineering, 1991 • no formal semantics • loss of relational simplicity • navigational queries • no general query language 10/26/2021 24 CS 319 Theory of Databases

Setting the scene for theory of databases – obj+rel Object oriented DBs emerged in the 1980 s in response to the feeling that relational databases were inadequate for certain classes of applications. . . The extent to which object-oriented and relational DBs can be unified, as in ‘object-relational’ DBs, remains controversial Ridley cites Maier (2002) on object-relational systems: David Maier speaks out, SIGMOD Record, 31(4), 2002 http: //www. acm. org/sigmod/record/issues/0212/ “there’s not a good design theory the way there was [with] normal form relations for relational databases” “… [the new features] at least [double] the number of [design] choices you have in every instance …” 10/26/2021 25 CS 319 Theory of Databases

Setting the scene for theory of databases – the Web Ridley observes: The Web has made many more people database users and heightened the awareness of databases generally … it raises the issues of the semantic mismatch between the set oriented nature of SQL and most programming languages. … cites: The Asilomar report on Database Research, SIGMOD Record, 27(4), 1998 http: //www. acm. org/sigmod/record/issues/9812/ “Theorem: The WEB changes everything. Corollary: XML is the means” … and raises the question: But where then is our dominant relational paradigm left? 10/26/2021 26 CS 319 Theory of Databases

Perspectives from the computing industry 1 From “Whatever happened to OODBs? ” http: //www. leavcom/db_08_00. html “[In 1998] industry experts touted OODBs as a technology on the rise, well suited for the emerging Internet age … [OODBs] would soon become the primary DB technology, supplanting RDBMSs, which were not designed to handle multimedia data … Fast forward to today, and … RDBs are still by far the most widely used DBs … meanwhile, object-relational DBMSs have added object capabilities to relational DBs … they are gaining popularity and are expected to outsell even RDBs by 2003 …” 10/26/2021 27 CS 319 Theory of Databases

Perspectives from the computing industry 2 Rick Catell (SUN Microsystems): “Object-oriented DBs are doing just fine, and the news of their demise is greatly exaggerated. While their market isn’t as big, they continue to be used in areas like CAD (computer -aided design) and telecommunications, where RDBMSs are not well suited. ” Michael Stonebraker (Informix): “ODBMSs occupy a small niche market that has no broad appeal. The technology is in semi-rigor mortis, and ORDBMSs will corner the market within five years. ” [quotations dating from around the year 2000] 10/26/2021 28 CS 319 Theory of Databases

Perspectives from the computing industry 3 International Data Corporation Opinion from OODB vs ORDBs, Steve Mc. Clure, 1997 “Because of the relative size of the RDBMS vendors’ marketing infrastructures, the ORDBMS market will surpass the size of the ODBMS market in the next five years. Nevertheless, putting object-oriented extensions on RDBMSs is tantamount to adding stereo radios and global navigation systems to horsedrawn carriages. You will have interesting enhancements, but the wrong base vehicle. In the end it won’t be an appropriate vehicle for the information superhighway. ” 10/26/2021 29 CS 319 Theory of Databases

Perspectives from academia 1 M J Ridley, University of Bradford (TLAD WS 2003) DB systems or DB theory – or “Why don’t you teach ORACLE” “Practice without Theory is blind, Theory without Practice is sterile” (Karl Marx) “… most people would agree [that] … we need a theoretical framework in which to talk about DBs if the result is to be productive … The questions that seem to me to need answering are: What is that framework? … “ 10/26/2021 30 CS 319 Theory of Databases

Perspectives from academia 2 M J Ridley, University of Bradford (TLAD WS 2003) DB systems or DB theory – or “Why don’t you teach ORACLE” “… most people would agree [that] … we need a theoretical framework in which to talk about DBs if the result is to be productive … The questions that seem to me to need answering are: What is that framework? … Is it the simple relational model? How important is that framework? (In practical, educational terms how much of the syllabus is this? ) Are there competing theoretical frameworks that need considering? ” 10/26/2021 31 CS 319 Theory of Databases

Perspectives from academia 3 M J Ridley, University of Bradford (TLAD WS 2003) DB systems or DB theory – or “Why don’t you teach ORACLE” “… most people would agree [that] … we need a theoretical framework in which to talk about DBs if the result is to be productive … But is there still a firm theoretical foundation for what we teach? Does that matter? Are we in danger of training students in the features of a particular system (and a convenient web interface for it) rather than educating them about DBs in the broader sense? …” 10/26/2021 32 CS 319 Theory of Databases

Perspectives from academia 4 M J Ridley, University of Bradford (TLAD WS 2003) DB systems or DB theory – or “Why don’t you teach ORACLE” “What is the relationship of Database teaching to other aspects of the curriculum such as the study of data structures and object-orientation? And are theories of these subjects? … ” “… is there are more general object theory that informs programming and software development generally. If so can relational theory be located as a subset of that mainstream or does database stand out as an exception? ” 10/26/2021 33 CS 319 Theory of Databases

Perspectives from academia 5 M J Ridley, University of Bradford (TLAD WS 2003) DB systems or DB theory – or “Why don’t you teach ORACLE” Ridley’s own answer to these questions: “… we are in danger of losing sight of firm theoretical ground especially as we are under pressure to include yet more additional facilities of DBMSs … more so as object systems have failed to achieve commercial success commensurate with their early promise and the pressure of the web has shifted the focus of much database work. ” 10/26/2021 34 CS 319 Theory of Databases

Books on Classical Relational Databases Date, C. J. A Database Primer, Addison-Wesley Date, C. J. An Introduction to Database Systems, Addison-Wesley 8 th edition, 2003 Ullman, J. D. Principles of Database Systems 2 nd edition, Computer Science Press 1982 Ullman, J. D. Principles of Database & Knowledge-base Systems Vols 1 & 2, Computer Science Press 1988 Ullman, J. D. A First Course in Database Systems, 2 nd ed Prentice-Hall, 2002 … see http: //www-db. stanford. edu/~ullman/ullman-books. html 10/26/2021 35 CS 319 Theory of Databases

Books on more general database issues Korth, H. F. and Silberschatz Database System Concepts, Mc. Graw-Hill 2 nd ed. 1991, 4 th ed. 2001 Brown, A. W. Object-oriented Databases: Applications in Software Engineering, Mc. Graw-Hill International Series in Engineering, 1991 Gray, P. D. Logic, Algebra & Databases, Ellis-Horwood Series, Computers and their Applications 29 Kent, William Data and Reality: basic assumptions in data processing reconsidered, North-Holland 1987 10/26/2021 36 CS 319 Theory of Databases

Books on Relational Theory in its application Date, C. J. Relational Database Writings 1985 -1989 Addison-Wesley [and other collections in this series] Hugh Darwen and C. J. Date Foundations for Object/Relational Databases: The Third Manifesto 1998 Harlow, Addison-Wesley Longman C. J. Date, Hugh Darwen, M. A. Lorentzos Temporal Data and the Relational Model, Morgan-Kaufmann, 2002 (summarised in Chap. 23 of Date’s Intro to DBs, 8 th ed) Dr Hugh Darwen was an IBM consultant on database until his retirement in 2004: he is our guest lecturer in weeks 18 and 19 10/26/2021 37 CS 319 Theory of Databases

Special lecture arrangements weeks 18 and 19 Dr Hugh Darwen was an IBM consultant on databases: he is our guest lecturer in weeks 18 and 19 NOTE: the lectures in week 18 will be on The Relational Model and SQL thus: Monday February 21 st at 2 pm and 5 pm in CS 1. 04 Thursday February 24 th at 2 pm and 5 pm in CS 1. 04 the lectures in week 19 will be on Temporal Data and the Relational Model thus: Monday February 28 th at 2 pm and 5 pm in CS 1. 04 Thursday March 3 rd at 2 pm and 5 pm in CS 1. 04 10/26/2021 38 CS 319 Theory of Databases

Questions regarding status of DB theory 1 Issues about the context in which DB theory applies: To what extent do theories about databases in general apply ‘exclusively’ to relational databases? To what extent is DB theory specific to applications? - technology / kind of activity / nature of environment … it applies to the 1970 s business context … does it apply now? 10/26/2021 39 CS 319 Theory of Databases

Questions regarding status of DB theory 2 Issues about the scope and nature of DB theory itself: How does DB theory relate to objectives such as - supporting end-user programming? - representing real-world state? How does DB theory relate to other conceptual frameworks (e. g. data modelling in OO / UML / ER) to address broader issues (e. g. typing / non-procedural vs procedural models of computation)? 10/26/2021 40 CS 319 Theory of Databases