CS319 Theory of Databases

1. CS319 Theory of Databases Course Review 2004-2005 CS319 Theory of Databases

2. Content of the module 1 • GENERAL BACKGROUND TO DATABASES … • 0. Preface to the Theory of Databases preface • 1. Generalities on Databases intro • 2. Ingres and Quel ingres • 3. Relational Database Models RelMod • [4. SQL sql ] • [5. SQL-EDDI worksheets <cs233 website> ] CS319 Theory of Databases

3. Content of the module 2 • RELATIONAL THEORY: ALGEBRA-CALCULUS • 6. Introduction to Relational Calculus relcalc • 7. Query optimisation opt • 8. From Relational Calculus to Algebra drelcalc • 9. Relational Query languages / modelling state relql CS319 Theory of Databases

4. Content of the module 3 • RELATIONAL DATABASE DESIGN … • [10. Entity-relationship modelling ERmodel] • 11. Decomposition of relational schemes decomp • 12. Functional Dependency depend • 13. Relational Database Design RDBdesign • [14. Normal Forms RDBdesignNF ] CS319 Theory of Databases

5. Content of the module 4 • CRITIQUE AND EVALUATION … • The OO and 3rd Gen Database Manifestos • [Tim Heron : OO and Object-Relational DBs] • Hugh Darwen: The Relational Model and SQL • Hugh Darwen: Temporal data and the Relational Model • 15. Why relational? whyrel • 16. Why not relational? whynotrel • 17. Evaluating the manifestos evalMan CS319 Theory of Databases

6. Preface • Principal theme of the Theory of Databases module: • How do theory and computing practice relate with specific reference to databases? • General orientation especially useful for the two essay questions 1 and 2: Mick Ridley’s reflections • Motivation for • study of relational theory • discussion of practice and historical context CS319 Theory of Databases

7. Generalities on Databases • What is a database? • the database manager module • users, including DBA • data abstraction: logical / physical data independence • conceptual models for databases • Two views of a database: • end-user and application programming environment • means to record external real-world state CS319 Theory of Databases

8. Relational Databases Review • Ingres and QUEL ingres • Relational Database Models RelMod • SQL + SQL-EDDI sql • Relational query languages • scope of expressive power of DDL and DML • examples: QUEL, SQL and ISBL comparison • special characteristics of relational data models • Alternative data modelling approaches • hierarchical, network, object-oriented, E-R etc CS319 Theory of Databases

9. Relational Theory • Introduction to Relational Calculus relcalc • From Relational Calculus to Algebra drelcalc • Mathematical definition of relations • Relational algebra operations and completeness • Relational calculus: tuple and domain, safety • Codd’s Fundamental Theorem of Relational Theory • converting from relational algebra to calculus • converting from relational calculus to algebra CS319 Theory of Databases

10. Relational Query Languages • Relational Query Languages – Modelling State relql • End-user programming using RQLs • Modelling real-world state using RQLs • Extras: • CODASYL slides whyrel (12-20) • Optimisation opt CS319 Theory of Databases

11. Relational Database Design 1 • Relational Database Design RDBdesign • Functional Dependency depend • Decomposition of relational schemes decomp • Theory of database design • functional dependency • Armstrong’s axioms, minimal covers • lossless and dependency-preserving decompositions • Algorithm for constructing a minimal cover • Algorithm for lossless join / dependency preserving CS319 Theory of Databases

12. Relational Database Design 2 • Relational Database Design - NFs RDBdesignNF • Decomposition of relational schemes decomp • Normal forms and anomalies • update, insertion/deletion anomalies • 3NF, BCNF, 4NF, 5NF • data dependencies beyond functional dependencies • Decomposition algorithms (taking account of FDs) • lossless join decomposition into BCNF • lossless, dependency preserving decomposition 3NF CS319 Theory of Databases

13. Theory and Practice of Database Critique • Resources for theory and practice critique • OO and 3rd-generation manifestos • Hugh Darwen: www.thethirdmanifesto.com • The Relational Model and SQL • Temporal Data and the Relational Model • Why relational? whyrel • Why not relational? whynotrel • Evaluating the manifestos evalMan • Extras: Entity-relationship modelling ERmodel CS319 Theory of Databases

14. Format of the Examination Paper • The exam paper has no special sections: • There are 7 questions in all: you answer 4 • Every answer is marked out of 25 • First two questions on the paper are essay questions • The topics of these questions are briefly introduced on the module website CS319 Theory of Databases

15. Essay Themes for 2004-2005 • Exercises designed to help you prepare for essays in Qu 1 and 2: • Exercise 1: Review Ridley’s critique of DB theory and practice. Issues: what Codd's theory did in the 1970s; how academic and commercial developments have influenced practice; how DB theory relates to data modelling in CS (e.g. with reference to data structures and algorithms, and OO modelling).     • Exercise 2: What is the ideal relational query language? – taking account of: the demands made upon RQLs by DB applications as they were initially and have evolved over time; what Codd's relational theory commends for RQL design; your knowledge of RQLs (and SQL and its design flaws in particular); Tutorial D. CS319 Theory of Databases

16. Temporal Tutorial D: a brief reprise 1 • Example of a temporal tutorial D database: • S_DURING (S#, DURING) • - records when a staff member was employed by the university over the period 1980-2000 • SM_DURING (S#, M#, DURING) • - records when a staff member was responsible for teaching the module with code M# from 1980-2000 • DURING is of type ‘interval_year’ CS319 Theory of Databases

17. Temporal Tutorial D: a brief reprise 2 • Typical query: • USING (DURING) SM_DURING {M# , DURING}  • SM_DURING {M# , DURING} is a Tutorial D for projection • Special brackets ( ) are an 'operation invocation modifier'. • Interpretation of the query: • 1. unpack the operand(s) on DURING • 2. Evaluate the relational op invocation on unpacked forms • 3. Pack the result on DURING CS319 Theory of Databases

18. Temporal Tutorial D: a brief reprise 3 • The query • USING (DURING) SM_DURING {M# , DURING}  • returns the periods during which the modules were being taught, in the form of a relation (M#, DURING), where the tuples take the form (module_code, interval_year) where interval_year is such that the module is not recorded as being taught immediately prior or after the specified interval. CS319 Theory of Databases

19. Temporal Tutorial D: a brief reprise 4 • To frame such queries • - need to know some basic Tutorial D syntax • e.g. S_DURING(S#, DURING) • MINUS • SM_DURING(S#, DURING) • returns when S# employed but not teaching • - need to know about predicates on intervals e.g. • … WHERE DURING INCLUDES [1985,1991] CS319 Theory of Databases

20. Temporal Tutorial D: a brief reprise 5 • Other ingredients to be familiar with • U_key constraints: • USING (DURING) KEY(S#, DURING) • USING(DURING) FOREIGN_KEY {S#, DURING} • REFERENCES • S_DURING • “horizontal decomposition”: for temporal extension to present day introduce an auxiliary table: • SM_SINCE(S#, M#, SINCE) CS319 Theory of Databases