Database Data Models

Data Models

• It's a set of concepts describing a database's structure.
• It defines the operations and the constraints a database adheres to.

Data Model Structure and Constraints

• Constructs define the structure.
• Elements, data types, and groups like entities, records, and tables, as well as relationships, are included.
• Some contraints restrict valid data, and must always be enforced.

Data Model Operations

• Operations used to specify database retrievals and updates.
• Operations include basic model operations (insert, delete, update) and user-defined operations (compute_student_gpa, update_inventory)

Categories of Data Models

• Conceptual (high-level, semantic) data models provide concepts close to how users perceive data (entity-based/object-based).
• Physical (low-level, internal) data models describe how data is stored in the computer, specified ad-hoc in DBMS manuals.
• Implementation (representational) data models provide concepts falling between conceptual and physical, used in DBMS implementations (e.g., relational data models).

Schemas vs. Instances

• Database schema is the description.
• Schema includes the database structure, data types, and the constraints.
• Schema diagram is an illustrative display of a schema's aspects.
• Schema construct is a schema component or object (e.g., STUDENT, COURSE).
• Database state refers to the actual data stored in a database at a specific time, which is the data collection.
• Database state is also called database instance or snapshot.

Database Schema vs. Database State

• Database content at a moment is the database state.
• Initial database state is when it's initially loaded.
• A valid state satisfies the database structure and constraints.
• The database schema changes infrequently, while the database state changes on every update.
• Schema is also called intension.
• State is also called extension.

Three-Schema Architecture

• It supports DBMS characteristics like program-data independence.
• It supports multiple views of data.
• Internal schema at the internal level describes the physical storage structures and access paths, typically using a physical data model.
• Conceptual schema represents the logical structure at a high level, defining entities, relationships, and constraints relevant to the domain.
• Conceptual schema defines entities, relationships, and constraints without specifying data storage/access.
• It uses a conceptual or implementation data model.
• External schemas on the external level describe user views.
• Commonly uses the same data model as the conceptual schema.
• Mappings transform requests and data through schema levels.
• Programs refer to an external schema, mapped by the DBMS to the internal schema for execution.
• Data from the internal DBMS level is reformatted to match the user's external view (e.g., SQL query results in a Web page).

Data Independence

• Logical data independence is the ability to change the conceptual schema without altering external schemas and their application programs.
• Physical data independence is the ability to alter the internal schema without changing the conceptual schema.
• For instance, file structures can be reorganized or new indices created to improve database performance.

DBMS Languages

• Data Definition Language (DDL).
• Data Manipulation Language (DML).
• High-Level/Non-procedural Languages include SQL.
• Those languages may be used standalone or embedded in a programming language.
• Low Level/Procedural Languages, must be embedded in a programming language.
• DBA and database designers use DDL to specify the concept.
• Many DBMSs use the DDL to define internal/external schemas (views).
• DML specifies database retrievals and updates.
• DML commands (data sub-language) can be embedded in general-purpose programming languages (host languages), like C, C++, or Java.
• Alternatively, stand-alone DML commands can be applied directly (as a query language).

DBMS Interfaces

• Stand-alone query examples include interactive SQL interfaces.
• Programmer interfaces are for embedding DML into programming languages.
• User-friendly interfaces can be menu, forms, or graphics-based.

DBMS Programming Language Interfaces

• Embedded approach example: Embedded SQL (for C, C++, etc.), SQLJ (for Java).
• Procedure call approach example: JDBC (Java), ODBC (other languages).
• Database Programming Language approach example: Oracle's PL/SQL, incorporating SQL; its data types are integral components.

User-Friendly DBMS Interfaces

• Menu-based interfaces are often used for web browsing.
• Forms-based, designed for naive users
• Graphics-based interfaces use functions like point and click etc.
• Natural Language uses requests are written in English.
• Combinations: menus and forms can be used extensively in web database interfaces.

Other DBMS Interfaces

• Speech can act as Input and Ouput.
• Web browsers can be interfaces.
• Parametric interfaces, e.g., function keys for bank tellers.
• Interfaces for the DBA involves creating user accounts, and authorizations.
• It also involves setting system parameters and changing schemas/access paths.

Database System Utilities

• Loading data from files involves data conversion tools.
• Backing up databases periodically on tape.
• Reorganizing database file structures.
• Reporting generation utilities.
• Performance monitoring utilities.
• Sorting, user monitoring and data compression.

Other Tools

• Data dictionary/repository stores schema descriptions, design decisions, user information, usage standards.
• Active data dictionary is accessed by DBMS software/users/DBAs.
• Passive data dictionary is accessed by users/DBAs only.
• Application Development Environments and CASE (computer-aided software engineering) tools includes: PowerBuilder (Sybase), JBuilder (Borland), and JDeveloper 10G (Oracle).

Centralized and Client-Server DBMS Architectures

• Centralized DBMS integrates everything, including DBMS software, hardware, applications, and user interface processing software.
• Users can connect through remote terminals with processing done at the central site.

Basic 2-tier Client-Server Architectures

• Specialized servers: print, file, DBMS, web, and email.
• Clients access specific servers as required.

Clients

• Clients provide interfaces via software modules to access server resources.
• Clients include diskless machines, PCs, and workstations with client software only.
• Clients connected to servers via networks (LAN, wireless, etc.).

DBMS Server

• It gives database query and transaction services to clients.
• Relational DBMS servers are often SQL servers, query servers, or transaction servers.
• Client applications use APIs (Application Program Interface) to access server databases via standards like ODBC for general connectivity or JDBC for Java.
• Clients and servers need client and server module software installed.

Two Tier Client-Server Architecture

• A client program connects to multiple DBMSs (data sources).
• Data sources may be files or non-DBMS.
• Some object DBMSs move functionality to clients, including data dictionaries, optimizations, and recovery across multiple servers.

Three Tier Client-Server Architectures

• Common construction for web applications.
• Application/Web server stores web connectivity software and access the corresponding data from the database server and businesss logics.
• Acts as a conduit of partially processed data between a server and client.
• It enhances security through controlled database server accessibility, preventing clients from direct access.

Classification of DBMSs

• Based on the data model, including traditional (relational, network, hierarchical) and emerging (object-oriented, object-relational) types.
• Other classifications: single vs. multi-user and centralized vs. distributed.

Variations of Distributed DBMSs (DDBMSs)

• Homogeneous DDBMS.
• Heterogeneous DDBMS.
• Federated or Multidatabase Systems.
• Distributed Database Systems became client-server with database servers supporting a client set, rather than a distributed environment.

Cost Considerations for DBMSs

• Cost ranges from free open-source to millions based on configurations.
• Free relational examples: MySQL, PostgreSQL.
• Commercial DBMSs come with additional specialized modules (time-series, spatial data, document, XML).
• Such modules offer specific functionality when purchased seperately.
• Some are called cartridges in Oracle) or blades.
• licensing options: site license, concurrent users (seat license), single user.

History of Data Models

• Network Model
• Hierarchical Model
• Relational Model
• Object-oriented Data Models
• Object-Relational Models

History of Data Models: Network Model

• Honeywell implemented first network DBMS in 1964-65 (IDS System).
• Heavily supported by CODASYL (Conference on Data Systems Languages) via the DBTG report of 1971.
• Systems implementation included IDMS, DMS 1100, IMAGE, VAX -DBMS.

Network Model: Advantages

• Complex relationships and semantics of add/delete actions can be modeled.
• Record types and relationships allow handling for most modelling situations.
• Languages are navigational, using constructs like FIND, FIND member/owner/NEXT.
• Programmers have the option to use optimal navigation.

Network Model: Disadvantages

• Navigational and procedural nature of processing.
• Complex pointer array.
• Little scope for automated “query optimization”.

History of Data Models: Hierarchical Data Model

• Co-implemented by IBM and North American Rockwell around 1965, resulting in the IMS family.
• IBM has a customer base worldwide with its IMS product.
• It was formalized based on the IMS system.
• The model includes System 2k (SAS inc.).

Hierarchical Model: Advantages

• Simply constructed and operated.
• Corresponds to number of natural hierarchically organized domains.
• Is simple via constructs like GET, GET UNIQUE, GET NEXT etc.

Hierarchical Model: Disadvantages

• Navigational and procedural nature of processing.
• Database is seen as linear arrangement of records.
• There is little scope for query optimisations.

History of Data Models: Relational Model

• E.F. Codd (IBM) proposed it in 1970. The first commercial systems were in 1981-82.
• Exists in DB2, ORACLE, MS SQL Server, SYBASE, INFORMIX).
• Several free open source examples such as MySQL, PostgreSQL currently exist.
• Remains most dominant for developing database applications.
• SQL relational standards follow SQL-89 (SQL1), SQL-92 (SQL2), SQL-99, SQL3.
• Model details are described in chapters 5-11.

History of Data Models: Object-Oriented Data Models

• Several models exists to be implemented in a database system.
• Models of persistent O-O Programming Languages include those such as C++, and Smalltalk.
• Systems include O2, ORION (at MCC - then ITASCA), IRIS (at H.P.).
• Object Database Standard follows ODMG-93, ODMG-version 2.0, ODMG-version 3.0.
• Chapters 20 and 21 describe this model.

History of Data Models: Object-Relational Models

• Latest trend, starting with Informix Universal Server.
• Relational systems incorporate concepts from object databases, leading to object-relational systems.
• Exemplified in the latest versions of Oracle-10i, DB2, and SQL Server and other DBMSs.
• SQL-99 includes standards that will be enhanced.
• Chapter 22 describes the topic.