Data Models. Data Model : A set of concepts to describe the structure of a DB (data types, relationships ), operations for manipulation of the DB, and constraints on the DB. Data Model Operations : Operations for specifying DB retrievals and updates.
A set of concepts to describe the structure of a DB (data types, relationships), operations for manipulation of the DB, and constraints on the DB.
Data Model Operations: Operations for specifying DB retrievals and updates.
Basic operations (insert, delete, modify, …)
User-defined operations (e.g. calculate_credit_rating)
Conceptual (high-level, semantic) data models:
close to the way users perceive data. (Also called entity-based or object-based data models.)
Physical (low-level, internal) data models:
describe details of how data is stored in the computer; managed by a DBMS and an DBA.
Implementation (representational, logical) data models:
fall between the above two, balancing user views with some computer storage details (e.g. most relational data models).
The description of a database. Changes infrequently.
Also called the intension.
Includes descriptions of the DB structure, data types, and constraints.
A diagram of (most aspects of) a database schema.
Data types, relationships, constraints are not shown
A component (or object) of the schema, e.g., STUDENT, COURSE.
The actual data stored in a database at a particular moment in time.
Also called database instance (or occurrenceor snapshot).
Also called the extension.
Database State: the instantaneous content of a DB => changes frequently.
Initial Database State: the state when the DB is loaded.
Valid State: A state that satisfies the structure and constraints of the database.
A quasi-standard created by ANSI in the 1970s to support three of the important DBMS characteristics:
use of a catalog to store the DB description
Defines DBMS schemas at three levels:
Many DBMSs do not support/separate all three levels.
Mappings among schema levels are needed to transform requests and data.
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 (e.g. formatting the results of an SQL query for display in a Web page)
Logical Data Independence: The External Views (applications) are immune to the changes in the conceptual level and physical level schemas.
Physical Data Independence: The External Views (applications) & Conceptual Level Schemas (conceptual and logical schema) are immune to the changes in physical level schemas.
When a schema at a lower level is changed, only the mappings between this schema and higher-level ones need to be changed.
The higher-level schemas themselves are unchanged.
Application programs are unaffected since they refer to the external schemas, hence program/data independence.
Data Definition Language (DDL): Used by the DBA and database designers to specify the conceptual schema of a database. In many DBMSs, the DDL is also used to define internal and external schemas (views).
Some DBMSs (that have a clear separation between conceptual and internal levels) have a separate storage definition language (SDL) and view definition language (VDL) to define internal and external schemas.
Data Manipulation Language (DML):
Used to specify database retrievals and updates
DML commands (data sublanguage) can be embedded in a general-purpose programming language (host language), such as COBOL, C, C++, or Java.
Alternatively, stand-alone DML commands can be applied directly (called a query language).
High Level or Non-procedural Language:
For example, the SQL relational language
Are “set”-oriented and specify what data to retrieve rather than how to retrieve it.
Also called declarative languages.
Low Level or Procedural Language:
Retrieve data one record-at-a-time;
Constructs such as looping are needed to retrieve multiple records, along with positioning pointers.
Stand-alone query language interfaces
Example: Entering SQL queries at the DBMS interactive SQL interface (e.g. SQL*Plus in ORACLE)
Programmer interfaces for embedding DML in programming languages
Menu-based, forms-based, graphics-based, etc.
Which approach does PhP/MySQL use?
Menu-based, popular for browsing on the web
Forms-based, designed for naïve users
Graphics-based (Point’n’Click, Drag’n’Drop etc.)
Natural language: requests in written English, e.g. “give me all products from vendor 10 that cost more than $200”
Combinations of the above