Review of the Relational Model 5DV120 Database System Principles - PowerPoint PPT Presentation

Review of the Relational Model 5DV120 — Database System Principles Ume˚ a University Department of Computing Science Stephen J. Hegner hegner@cs.umu.se http://www.cs.umu.se/~hegner Review of the Relational Model 20130410 Slide 1 of 22

About these Slides • These slides are mostly adapted from those for 5DV119 by the same instructor. • The adaptations use the schema of the Silberschatz-Korth-Sudarsham textbook, rather than that of the Elmasri-Navathe textbook. • These slides will be used as a refresher/review, and will be covered much more rapidly than in the introductory course. • They are intended for those who already know/knew SQL, but need a quick refresher. Review of the Relational Model 20130410 Slide 2 of 22

Three Types of Information Systems Information-retrieval systems (IR): • Search large bodies of information which are not specifically formatted as formal data bases. • Web search engine • Keyword search of a text base • Typically read-only Database management systems (DBMS): • Relatively small schema • Large body of homogeneous data • Minor or no deductive capability • Extensive formal update capability • Shared use for both read and write Knowledge-base systems (KBS): • Relatively small body of heterogeneous information • Significant deductive capability • Typical use: support of an intelligent application Review of the Relational Model 20130410 Slide 3 of 22

The Evolution of Data Models Model Development Use Properties Analogy Low-level interaction. No data File-management 1950’s - 1970’s 1950’s - Assembly language independence. Some data independence, but the model Navigational 1950’s - 1960’s 1960’s - invites dependence. Requires procedural Procedural languages queries. Simple, easy to use for non-experts. Strong data independence. Standard nonprocedural query language Relational 1970’s - Late 1980’s - Declarative languages (SQL). Excellent implementations exist. Limited expressive capability. Powerful expressive capability, but require substantial expertise for use. Object-oriented 1980’s - 1990’s - Object-oriented languages Popular in niche applications. Standardization not imminent. Attempt to integrate the simplicity of the relational model with the advanced features of the object-oriented approach. Object-relational 1980’s 1990’s - ? The most recent SQL standard, as well as many commercial systems, embody such features. Attempt to integrate data management Semi-structured 1990’s 2000’s - ? with markup languages, principally via XML. Review of the Relational Model 20130410 Slide 4 of 22

Foci of the Course Relational Model: 90% • Selected advanced topics in SQL • Systems aspects, including internal data models, query processing, transaction models and implementation, recovery from failure Rationale: The relational model is still, by far, the dominant one used in practice. XML: 10% • The principles of semistructured data • Query languages for semistructured data, including XPath and XQuery . ➳ The emphasis will be upon principles. ➳ Application programming will not be covered in detail. Review of the Relational Model 20130410 Slide 5 of 22

Multi-User Relational Database Systems Open-Source Systems: PostgreSQL: The most comprehensive open-source relational DBMS. MySQL: A popular relational DBMS for small systems. • Widely used to support Web-based applications. HyperSQL: An efficient DBMS written in Java. • The default DBMS bundled with OpenOffice.org SQLite: A compact DBMS written in C. • The default DBMS bundled with LibreOffice. The “big three” commercial relational DBMSs: Oracle Database: IBM DB2: Microsoft SQL Server: (Windows only!) Another commercial relational DBMS of interest: Mimer SQL: Oriented towards embedded systems; based in Uppsala. • All of the commercial systems listed above have “free” versions with limited functionality. • All except SQL Server run on many platforms, including Linux. Review of the Relational Model 20130410 Slide 6 of 22

Single-User Relational Database Systems Microsoft Access: The original PC DBMS for Windows. • Part of the Microsoft Office bundle. • It will cost you ✩✩✩ , ➾➾➾ , or SeKSeKSeK. • Even if you have a Dreamspark Premium account (formerly MSDNAA), you cannot get MS Access for free. � • Runs only under MS Windows, of course. • Support for SQL is not as extensive as in multi-user systems. • No real support for transactions. Kexi: “Microsoft Access for Linux” • Built-in SQLite-based DB server. • Can also use other servers such as PostgreSQL and MySQL. • Not as mature a product as MS Access. • ... but it is open source and free (LGPL). • Can also be compiled for other systems. • A link to the Kexi Web site may be found on the course Web page. Review of the Relational Model 20130410 Slide 7 of 22

Database Systems to be Used in this Course • PostgreSQL will be used as the main relational system. • Both versions 8.4 and 9.1 will be available for use. • Students will receive at least one database for each. • If you have your own computer, it is easy to install under Linux, MS Windows, and Mac OS. • Some pointers for installation under Linux will be given later in the course. • You are free to (and encouraged to) try other relational DBMSs as well, but they will not be used in the course. • The XML-based system exist-db will also be used. • More later. Review of the Relational Model 20130410 Slide 8 of 22

Some Remarks on Terminology and Pronunciation Database vs. Database (Management) System: • A database is a (usually structured) collection of data. • A database system or database management system (DBMS) is a system for managing databases. • In the popular literature, the word database is sometimes used as a synonym for DBMS. ❉ Even the textbook does this! • This terminology is confusing and its use is to be discouraged. • MySQL and PostgreSQL are DBMSs, not databases! • Calling a DBMS a database is akin to calling JDK a Java program. Pronunciation of SQL: • In research circles, it is usually pronounced as the three letters S-Q-L. • In trade groups, SQL is sometimes pronounced as See-Quel. • This can lead to confusion with the older language SEQUEL, which also has that pronunciation. Review of the Relational Model 20130410 Slide 9 of 22

The Structure of the Relational Model • In the relational model, the data are stored in relations , or tables . • An example from the textbook is shown on the next three slides. • The relation names are shown with a blue background. • The attribute names are shown with a lime background. • The tuples are shown with a bisque background. • The primary keys are underlined. Databases are sets: For each relation, the set of rows consists of distinct elements. • Duplicate rows are not allowed. • The order in which the rows are displayed is of no formal consequence. Review of the Relational Model 20130410 Slide 10 of 22

The University Schema with a Small Database Instance – 1 department course prerequisite dept name building budget course id title dept name credits course id prereq id Biology Watson 90000 BIO-101 Intro. to Biology Biology 4 BIO-301 BIO-101 Comp. Sci. Taylor 100000 BIO-301 Genetics Biology 4 BIO-399 BIO-101 Elec. Eng. Taylor 85000 BIO-399 Computational Biology Biology 3 CS-190 CS-101 Finance Painter 120000 CS-101 Intro. to Computer Science Comp. Sci. 4 CS-315 CS-101 History Painter 50000 CS-190 Game Design Comp. Sci. 4 CS-319 CS-101 Music Packard 80000 CS-315 Robotics Comp. Sci. 3 CS-347 CS-101 Physics Watson 70000 CS-319 Image Processing Comp. Sci. 3 EE-181 PHY-101 CS-347 Database System Concepts Comp. Sci. 3 classroom EE-181 Intro. to Digital Systems Elec. Eng. 3 building room number capacity FIN-201 Investment Banking Finance 3 Packard 101 500 HIS-351 World History History 3 Painter 514 10 MU-199 Music Video Production Music 3 Taylor 3128 70 PHY-101 Physical Principles Physics 4 Watson 100 30 Watson 120 50 student instructor advisor id name dept name tot cred id name dept name salary s id i id 00128 Zhang Comp. Sci. 102 10101 Srinivasan Comp. Sci. 65000 00128 45565 12345 Shankar Comp. Sci. 32 12121 Wu Finance 90000 12345 10101 19991 Brandt History 80 15151 Mozart Music 40000 23121 76543 23121 Chavez Finance 110 22222 Einstein Physics 95000 44553 22222 44553 Peltier Physics 56 32343 El Said History 60000 45678 22222 45678 Levy Physics 46 33456 Gold Physics 87000 76543 45565 54321 Williams Comp. Sci. 54 45565 Katz Comp. Sci. 75000 76653 98345 55739 Sanchez Music 38 58583 Califieri History 62000 98765 98345 70557 Snow Physics 0 76543 Singh Finance 80000 98988 76766 76543 Brown Comp. Sci. 58 76766 Crick Biology 72000 76653 Aoi Elec. Eng. 60 83821 Brandt Comp. Sci. 92000 98765 Bourikas Elec. Eng. 98 98345 Kim Elec. Eng. 80000 98988 Tanaka Biology 120 Review of the Relational Model 20130410 Slide 11 of 22