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 Relatively small schema Large body of homogeneous data Minor or no deductive capability Extensive formal update capability Shared use for both read and write Relatively small body of heterogeneous information Significant deductive capability Typical use: support of an intelligent application. ? Database Management Systems (DBMS) ? ? ? ? ? ? Knowledge-Base Systems (KB S) ? ? ? 0061029: slides 1 of 12 Key DBMS issues: ? Efficiency issues: ? Databases can be very large. Efficient access must be provided despite the size. ? Simplicity issues: ? ? Many potential users are not sophisticated programmers, and so simple means of access must be available. Means of more sophisticated access must also be available. ? Multi-user issues: ? ? ? Concurrency ? Several users may have simultaneous access to the database. Access via views ? Each user has a limited “window” through which the appropriate part of the database is viewed. Authorization ? The access privileges of each user will be limited in a specific way. Robustness issues: ? ? Deadlock must be avoided. A means of recovery from crashes, with minimal loss of data, must be available. 20061029: slides 2 of 12 Data Model Evolution: Model File management Navigational models Relational model Devel. Use Properties Some data independence, but the model invites dependence. Requires procedural queries. Simple, easy to use for non-experts. Strong data independence. Standard nonprocedural query language (SQL). Excellent implementations exist. Limited expressive capability. Powerful expressive capability, but require substantial expertise for use. Popular in niche applications.
Standardization not imminent. Attempt to integrate the simplicity of the relational model with the advanced features of the objectoriented approach. A new standardized query language (SQL:1999) is available, with SQL:20xx on the way. Many “high-end” commercial relational systems embody object-relational features. Attempt to integrate data management with markup languages, principally via XML. Analogy Assembly language Procedural languages Declarative languages Object-oriented languages ? 1950’s – 1970’s 1950’s- Low-level interaction. No data independence. 1950’s – 1960’s1960’s 1970’s Late 1980’s 1990’s 1990’s –
Object-oriented models Object-relational models 1980’s 1990’s Semi-structured models 1990’s 2000’s – ? 20061029: slides 3 of 12 The course focuses on the relational model. Why? ? The relational model is very widely used. The relational model provides a flexible interface which has components appropriate for users at all levels. A standard query language, SQL, is used with virtually all commercial products. Thus, applications have a high degree of portability . The relational model provides strong data independence: the external product is relatively independent of the internal implementaton. The relational model is dominant on microcomputers running Windows operating systems: ? Office suites: ? Microsoft Office: Access ? Lotus SmartSuite: Approach ? Corel Suite: Paradox ? Other microcomputer products: ? dBase All have proprietary graphicalinterfaces, and provide programming-style queries as well. ? ? ? ? ? 20061029: slides 4 of 12 ? The relational model has also been dominant on mainframe database servers, including but not limited to UNIX systems. Recently, many of these systems have become available for the PC UNIX system Linux. (Some are free! ) ? Oracle ? Interbase 7 (Inprise, formerly Borland) ?
Sybase Adaptive Server Enterp rise ? Informix (now owned by IBM) ? IBM DB2 ? PostgreSQL 7. 4, 8. 1 (public domain, very good) There are even some products from Sweden: ? MySQL (GPL) ? Mimer SQL (Upright Database Technology) ? ? 20061029: slides 5 of 12 In the past, this course had used Microsoft Access. Since 2002, PostgreSQL has been be used. Why? ? ? ? The dialect of SQL which is supported under Access is much more limited than the dialects of comprehensive systems. PostgreSQL has matured greatly in the past few years. The Department of Computng Science has an i SQL server, which is administered by the support staff.
The following system will also be used: ? Leap ? A simple relational database system which uses the relational algebra as a query language. ? Although not of commercial importance, use of this alternate query language is very beneficial pedagogically. Students are still free to use Microsoft Access, although it will not be discussed in class. All final versions of SQL assignments must run under PostgreSQL. ? ? 20061029: slides 6 of 12 Database access models: ? SQL is the standard query language for the relational model. There are many access models which are built around SQL. ? ?
Direct SQL: Write and send SQL queries directly to the database system. Hosting SQL within a programming language: ? ? Embedded SQL: SQL statements are embedded in a host programming language, such as C. Generally requires preprocessing. Proprietary hosting languages: (e. g. , Oracle PL/SQL). Proprietary hosting systems: (e. g. , within Microsoft VBA). SQL / CLI ODBC: A vendor- and OSindependent call-interface system (in principle) for SQL. Embedding may be in any of a variety of languages (C, C++ are the most common. ) ? ? ? ? In this course, we will use both direct SQL and ODBC. 20061029: slides 7 of 12 A Rough Course Outline: Introduction to DBMS’ s Knowledge Representation for DBMS’s (10%) ? Entity-Relationship Modelling ? The Relational Model Query Processing and Constraints (40%) ? Query Languages ? Relational Algebra ? Relational Calculus ? SQL ? Views ? Database Programming and the CLI/ODBC Interface ? Dependencies and Normalization Implementation Issues (40%) ? Physical Database Design ? Database System Architecture ? Query Optimization ? Transaction Processing and Concurrency Control ? Recovery ? Security and Authorization Special Topics (10%) ? Object-Oriented and Object-Relational Approaches ? ? ? ? 20061029: slides 8 of 12
Database System Architecture: ? Early approach: one-level The user interacted directly with the storage model. Analogy: assembly-language programming Disadvantages: Impossible to use for non-experts. Difficult to use and error-prone even for experts. Evolution of storage model, or migration to a new architecture, requires a total rebuild of all application programs. ? ? ? ? ? ? 20061029: slides 9 of 12 A more modern approach: two-level External Data Model External/ Internal mapping Internal Storage Model ? Advantages: ? Internal model and/or target architect re may u be changed without requiring a rebuild of applications. Analogy: A high-level programming language. Disadvantages: ? There is a single external model for all. ? 20061029: slides 10 of 12 The ANSI/SPARC three-level architecture: External Data Model 1 … External/ Conceptual mapping Conceptual Data Model External Data Model n Conceptual/ Internal mapping Internal Storage Model ? Advantages: ? Provides two levels of independence: ? The internal storage model is isolated from the conceptual component, as in the two-level architecture. ? Many external views are possible. ? The conceptual model may be redesigned without requiring rebuilds of application programs. 0061029: slides 11 of 12 Data independence: ? Data independence refers to the idea that a more internal level of a database system may be reengineered, or moved to a different architecture, without requiring a total rebuild of the more external layers. The ANSI/SPARC architecture provides two levels of data independence. It is often, however, something of an ideal, even with the systems of today . Usually, in a relational system, both the conceptual schema and the external schemata are relational. Still, the conceptual schema is often designed using a more general tool than the relational model. ? ? ? ? 20061029: slides 12 of 12