DATA ENVIRONMENT DATABASE MANAGEMENT SYSTEM 1
The Three-Level ANSI-SPARC ARCHITECTURE • DBTG (Database Task Group) • Was appointed by the Conference on Data Systems Languages in 1971 to produced a proposal for a standard terminology and general architecture for database systems • The DBTG recognized the need for a two-level approach: • Schema-system view • Subschema-User views
The Three-Level ANSI-SPARC ARCHITECTURE • ANSI – SPARC (Standards Planning and Requirements Committee) • Produced in 1975 by the American National Standard Institute (ANSI) • A standard terminology and general architecture for database systems • ANSI-SPARC recognized the need for a three-level approach with a system catalog.
The Three-Level ANSI-SPARC ARCHITECTURE • External/View Level • Users’ view of the database. • Describes that part of database that is relevant to a particular user. • Describe how users see the data. • Conceptual/Logical Level • Community view of the database. • Describes what data is stored in database and relationships among the data. • Defines the logical structure
The Three-Level ANSI-SPARC ARCHITECTURE • Internal/Physical Level • Physical representation of the database on the computer. • Describes how the data is stored in the database. • Describes the files and indexes used.
The Three-Level ANSI-SPARC ARCHITECTURE - EXAMPLE • University Database • Conceptual/Logicallevel: • Students(sid: string, name: string, login: string, age: integer, gpa:real) • Courses(cid: string, cname:string, credits:integer) • Enrolled(sid:string, cid:string, grade:string)
The Three-Level ANSI-SPARC ARCHITECTURE - EXAMPLE • Internal/Physical schema: • Relations stored as unordered files. • Index on first column of Students. • External/ViewSchema: • Course_info(cid:string,enrollment:integer)
The Three-Level ANSI-SPARC ARCHITECTURE - EXAMPLE • Conceptual level: • students: • student_IDchar(6) • Department_number char(4) • GRADEnumeric(5) • Internal level: • Stored_std:length = 20 • HeaderType = byte(6), offset = 0 • std#Type = byte(6), offset = 6, Index = STDX • Dept#Type = byte(4), offset = 12 • GRDType = byte(4), offset = 16
The Three-Level ANSI-SPARC ARCHITECTURE - EXAMPLE • External level1: • STD_GRD: • STD# char(6) • GRD integer • External level2: • STD_Dept: • STDNo char(6) • DeptNo char(4)
Schema • The logical structure of the database • a description of a particular collection of data, using the a given data model. • e.g., the database consists of information about a set of customers and accounts and the relationship between them • Physical schema: database design at the internal/physical level • Logical schema: database design at the conceptual/logical level
Instance • the actual content of the database at a particular point in time
Data Independence • the ability to modify the lower level descriptions of a database without causing application programs to berewritten. • separation of the physical and logical data
Types of Data Independence • Logical Data Independence • The ability to modify the conceptual schema without causing application programs to be rewritten • Conceptual schema changes (e.g. addition/removal of entities). • Should not require changes to external schema or rewrites of application programs.
Types of Data Independence • Physical Data Independence • The ability to modify the internal schema without causing conceptual schema to be rewritten • Internal schema changes (e.g. using different file organizations, storage structures/devices). • Should not require change to conceptual or external schemas.
Data Independence • Data independence is achieved throughproper manipulation of the two mappings. • From external schema to conceptual schema • Ex: StdNo to Student_number • From conceptual schema to internal schema • Ex: Student_number to STD#
Database Languages • Data Definition Language (DDL) • Allows the DBA or user to describe and name entities, attributes, and relationships required for the application plus any associated integrity and security constraints. • Used by DBA or database designer to define database schemas.
Database Languages • CREATE statements • Create - To make a new database, table, index, or stored query.
Database Languages - Examples • DROP statements • Drop - To destroy an existing database, table, index, or view. • ALTER statements • Alter - To modify an existing database object.
Database Languages • Data Manipulation Language (DML) • Provides basic data manipulation operations on data held in the database. • Examples: • SELECT ... FROM ... WHERE ... • INSERT INTO ... VALUES ... • UPDATE ... SET ... WHERE ... • DELETE FROM ... WHERE ...
Database Languages • Procedural DML • allows user to tell system exactly how to manipulate data. • a user is required to specify WHAT data is needed as well as HOW to get it. • Example: DMLs for hierarchical and network database systems.
Database Languages • Non-Procedural DML • allows user to state what data is needed rather than how it is to be retrieved. • a user is required to specify ONLY WHAT data is needed but NOT HOW to get it. • Example: SQL
Data Models • Integrated collection of concepts for describing data, relationships between data, and constraints on the data in an organization. • Data Model comprises: • a structural part - which describes the format or organization of data in the database; • a manipulative part - different data structure will have different data operation mechanism; • possibly a set of integrity rules - limitations or rules on what the data can do.
Data Models • Entity-Relationship model • Relational model • Other models: • object-oriented model • semi-structured data models • Older models: network model and hierarchical model
Entity-Relationship Model Example of schema in the entity-relationship model
Entity-Relationship Model • E-R model of real world • Entities (objects) • E.g. customers, accounts, bank branch • Relationships between entities • E.g. Account A-101 is held by customer Johnson • Relationship set depositor associates customers with accounts
Entity-Relationship Model • Widely used for database design • Database design in E-R model usually converted to design in the relational model (coming up next) which is used for storage and processing
Relational Model • Example of tabular data in the relational model Attributes customer- street customer- city account- number customer- name Customer-id Johnson Smith Johnson Jones Smith 192-83-7465 019-28-3746 192-83-7465 321-12-3123 019-28-3746 Alma North Alma Main North A-101 A-215 A-201 A-217 A-201 Palo Alto Rye Palo Alto Harrison Rye
Thank you… End of presentation