Database data models

Data Models

• Data models consist of concepts that describe a database's structure, the operations for manipulating the structure, and constraints.
• Data model constructs define the database structure including the data types, elements e.g. entity, record, table, and relationships between elements.
• Data constraints specify valid data restrictions and are consistently enforced.

Data Model Operations

- Operations specify database retrievals and updates.
- Operations include generic functions (insert, delete, and update) and user-defined operations (e.g., compute_student_gpa, update_inventory).

Categories of Data Models

• Conceptual (high-level, semantic) data models offer concepts close to the user's perception of the data. These are also called entity-based or object-based data models.
• Physical (low-level, internal) data models describe data storage details in the computer and are specified ad-hoc through DBMS manuals.
• Implementation (representational) data models fall between conceptual and physical models and are used in commercial DBMS implementations.
• Self-describing data models combine data description with data values, examples include XML, Key-value stores and some NOSQL systems.

Schemas

• This is the description of a database describing the structure, types, and constraints. Schema diagrams are illustrative displays of database schemas. Schema constructs form a component of the schema i.e. STUDENT, COURSE.

Instances

• A database state, also called a database instance (or occurrence or snapshot), includes all the data in the database at a specific moment. "Instance" can also be applied to individual database components such as record, table or entity.

Database State

• This refers to the current content of the database. The initial database state refers to when the database is originally loaded. A valid state satisfies the structure and constraints of the database.

Schemas vs Instance

• Database schema changes are infrequent.
• Database state changes with every update.
• Schemas are known as intension.
• A state is known as extension.

Three-Schema Architecture

• This supports program-data independence and multiple views of data.
• It is not explicitly used in commercial DBMS products.
• It is useful in explaining database system organization.
• It defines DBMS schemas at three levels: internal, conceptual, and external.

Internal Schema

• At an internal level it describes physical storage structures and access paths, typically using a physical data model e.g. indexes.

Conceptual Schema

• This describes the structure and constraints for the whole database for a community of users.
• This uses a conceptual or implementation data model.

External Schemas

• At the external level to describe the various user views. Usually using the same data model as the conceptual schema.

Mappings Between Schemas

• Mappings are needed to transform requests and data between schema levels.
• Programs refer to an external schema and are mapped by the DBMS to the internal schema for execution.
• Data extracted from the internal DBMS level is reformatted to match the user's external view.

Data Independence

• Logical data independence is the capacity to change the conceptual schema without changing the external schemas or their application programs.
• Physical data independence is the capacity to change the internal schema without changing the conceptual schema. For example, file structures reorganized or new indexes created to improve database performance.
• When lower-level schemas are changed, only the mappings between schemas need to be changed, leaving higher-level schemas and application programs unchanged.

DBMS Languages

• These include Data Definition Language (DDL) and Data Manipulation Language (DML).
• High-level or non-procedural languages include SQL which may be used in a standalone way or be embedded in a programming language.
• Low-level or procedural languages must be embedded in a programming language.

Data Definition Language (DDL)

• DDL is used by DBAs and database designers to specify the conceptual schema. In many DBMSs, it also defines internal and external schemas (views).
• Some DBMSs use separate storage definition language (SDL) and view definition language (VDL) to define internal and external schemas.
• SDL is realized via DBMS commands provided to DBAs and designers.

Data Manipulation Language (DML)

• DML specifies database retrievals and updates. DML commands (data sublanguage) can be embedded in a host language like COBOL, C, C++, or Java.
• Function libraries can also provide DBMS access from a programming language. Alternatively, stand-alone DML commands can be applied directly via a query language.

Types of DML

• High-level or non-procedural languages, like SQL, are "set"-oriented and specify what data to retrieve.
• Low-level or procedural languages like those used to retrieve data one record at a time; constructs such as looping are needed to retrieve multiple records, along with positioning pointers.

DBMS Interfaces

• Interfaces include: stand-alone query languages; programmer interfaces for embedding DML; user-friendly interfaces; and mobile interfaces for transactions via mobile apps.

Programmer Interfaces

• Embedded approaches i.e. embedded SQL (for C, C++, etc.) and SQLJ (for Java).
• Procedure call approaches, e.g. JDBC for Java and ODBC (Open Database Connectivity) for other languages as APIs.
• Database programming language approach, e.g. Oracle's PL/SQL, and scripting languages like PHP and Python for database programs.

User-Friendly DBMS Interfaces

• These include menu-based popular for browsing the web, forms-based, graphics-based with point and click, drag and drop etc, natural language requests in written English, combinations of all the above.

Other DBMS Interfaces

• Natural language for free text queries; speech input and output; web browsers with keyword search; parametric interfaces for specialized users like bank tellers; and interfaces for DBAs to manage user accounts, parameters, and schemas.

Database System Utilities

• Functions performed include: loading data, backing up the database periodically, reorganizing file structures, monitoring performance, generating reports, sorting, user monitoring, compressing data, etc.

Other Tools

• Data dictionary/repository stores schema descriptions, design decisions, program descriptions, user information, and usage standards.
  • Active data dictionaries are accessed by DBMS software and users/DBAs.
  • Passive data dictionaries are accessed by users/DBAs only.
• Application development environments and CASE tools.
  • Examples: PowerBuilder (Sybase), JBuilder (Borland), and JDeveloper 10G (Oracle).

Centralized DBMS

• Combines everything into a single system: DBMS Software, hardware, application programs, and user interface processing.
• Users can connect via remote terminals but all processing happens at the central site.

Client-Server Architectures

• Specialized servers with specialized functions i.e. Print server, File server, DBMS server, Web server and Email server.
• Clients can access these specialized servers as needed.
• Clients provide interfaces to access server resources and may be diskless machines, PCs, or workstations with client software installed, connecting via LAN or wireless networks.
• Relational DBMS servers are often called SQL servers, query servers, or transaction servers.
• Applications on clients use an API to access server databases via standard interfaces like ODBC or JDBC.
• Clients and servers need appropriate modules for ODBC or JDBC, and client programs can connect to several DBMSs, known as data sources.

Two Tier Client-Server Architecture

• Two-tier client server has data sources as files or non-DBMS data management software.

Three-Tier Client-Server Architecture

• In three-tier the intermediate layer is called an Application Server or Web Server. This setup is commonly used with web applications.
• Three Tier stores web connectivity software and the business application logic.
• It serves as a conduit for sending partially processed data between the database server and the client.

Three Tier Architecture Advantages

• Security can be enhanced as Databases are only accessible via middle tier, and clients cannot directly access it. Web browsers also contain clients which make it a PC or a mobile device.

Classification of DBMSS

• Based on the data model used i.e. Legacy: Network, Hierarchical.
• Currently used: Relational, Object-oriented, Object-relational.
• Recent Technologies: Key-value storage systems, NOSQL systems: document based, column-based, graph-based and key-value based. Native XML DBMSs.
• Other classifications include: single-user vs. multi-user, and centralized vs. distributed.

Variations of Distributed DBMSs (DDBMSs)

• Homogeneous DDBMS are variations where data is the same.
• Heterogeneous DDBMS are variations where data is different.
• Federated or Multidatabase Systems have loosely coupled with high degrees of autonomy. Distributed Database Systems are now client-server based because they support a set of database servers supporting a set of clients.

Cost Considerations For DBMSS

• Data model systems range from free open-source to millions of dollars. MySQL and PostgreSQL are examples of free relational DBMSs. Commercial DBMS offer additional specialized modules for time-series, spatial data, documents, and XML, that are purchased separately. They are sometimes called cartridges (Oracle) or blades.
• Different licensing options available such as: site license, maximum concurrent users (seat license), and single user.

Other Considerations

• Type of access paths within database systems.
  • E.g.- inverted indexing based (ADABAS). Fully indexed databases provide access by any keyword (used in search engines).
• General Purpose vs. Special Purpose.
  • E.g.- Airline Reservation systems or many others reservation systems for hotel/car etc. Are special purpose OLTP (Online Transaction Processing Systems)

History of Data Models

• These generally include: Network Model, Hierarchical Model, Relational Model, Object-oriented Data Models, and Object-Relational Models.

Network Model

• Honeywell implemented the first network DBMS in 1964-65 (IDS System). It was heavily adopted due to CODASYL report in 1971. Later implemented in systems like IDMS (Cullinet), DMS 1100 (Unisys), IMAGE (H.P.), VAX -DBMS (Digital Equipment Corp).

Network Model Advantages

• It is able to model complex relationships and represents semantics of add/delete on the relationships.
• It can also handle most situations for modeling using record types and relationship types.
• Language is navigational; uses constructs like FIND, FIND member, FIND owner, FIND NEXT within set, GET, etc.

Network Model Disadvantages

• Navigational and procedural nature of processing.
• Database contains a complex array of pointers that thread through a set of records with little scope for automated "query optimization".

Hierarchical Model

• IBM and North American Rockwell initially implemented in 1965 resulting in IBM's IMS family of systems. IBM's IMS product had a very large customer base worldwide and other systems based on this model include System 2k (SAS inc.)

Hierarchical Model Advantages

• Simple to construct and operate.
• Correspond to a number of natural hierarchically organized domains, e.g., organization (“org”) chart.
• Language is simple with constructs like GET, GET UNIQUE, GET NEXT, GET NEXT WITHIN PARENT, etc.

Hierarchical Model Disadvantages

• Navigational and procedural nature of processing.
• Database is visualized as a linear arrangement of records.
• Little scope for "query optimization".

Relational Model

• E.F. Codd (IBM), proposed in 1970, with the first commercial system in 1981-82. The model is now in several commercial products (e.g. DB2, ORACLE, MS SQL Server, SYBASE, INFORMIX) as well as, several free open source implementations, e.g. MySQL, PostgreSQL.
• It is the most dominant model used when developing database applications.
• SQL relational standards: SQL-89 (SQL1), SQL-92 (SQL2), SQL-99, SQL3, ...

Object-Oriented Data Models

• Several models have been proposed for implementation in a database system.
• A set comprises models of persistent O-O Programming Languages such as C++ (e.g., in OBJECTSTORE or VERSANT), and Smalltalk (e.g., in GEMSTONE).
• Additionally, systems like O2, ORION (at MCC - then ITASCA),IRIS (at H.P.- used in Open OODB). Object Database Standard: ODMG-93, ODMG-version 2.0, ODMG-version 3.0.

Object-Relational Models

• The trend to mix object models with relational was started with Informix Universal Server. Relational systems incorporated concepts from object databases leading to object-relational. These are Exemplified in the versions of Oracle, DB2, and SQL Server and other DBMSs. The focus is being place on extending relational DBMSs functionality processing XML, Text and other data types. The term "Object-relational" is receding in the marketplace