1 / 40

DATA ENVIRONMENT

DATA ENVIRONMENT. DATABASE MANAGEMENT SYSTEM 1. Database Architecture. The Three-Level ANSI-SPARC ARCHITECTURE. DBTG (Database Task Group)

wells
Download Presentation

DATA ENVIRONMENT

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DATA ENVIRONMENT DATABASE MANAGEMENT SYSTEM 1

  2. Database Architecture

  3. 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

  4. 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.

  5. The Three-Level ANSI-SPARC ARCHITECTURE

  6. 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

  7. 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.

  8. 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)

  9. 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)

  10. 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

  11. 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)

  12. The Three-Level ANSI-SPARC ARCHITECTURE - EXAMPLE

  13. Schemas and Instances

  14. 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

  15. Instance • the actual content of the database at a particular point in time

  16. 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

  17. 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.

  18. 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.

  19. 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#

  20. Data Independence and ANSI-SPARC Three-Level Architecture

  21. Database languages

  22. 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.

  23. Database Languages • CREATE statements • Create - To make a new database, table, index, or stored query.

  24. Database Languages - Examples • DROP statements • Drop - To destroy an existing database, table, index, or view. • ALTER statements • Alter - To modify an existing database object.

  25. 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 ...

  26. 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.

  27. 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

  28. Data Models

  29. 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.

  30. Data Models • Entity-Relationship model • Relational model • Other models: • object-oriented model • semi-structured data models • Older models: network model and hierarchical model

  31. Entity-Relationship Model Example of schema in the entity-relationship model

  32. 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

  33. 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

  34. 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

  35. A Sample Relational Database

  36. Object Oriented Model

  37. Semi-Structured Data Model

  38. Network Model

  39. Hierarchical Model

  40. Thank you… End of presentation

More Related