lecture 8 - database schema.pptx
TRANSCRIPT
![Page 1: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/1.jpg)
1
ICT 321
DATABASE SCHEMA
![Page 2: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/2.jpg)
2
Agenda
• Data model• ANSI/Spark Data Model• Database schema and instances
![Page 3: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/3.jpg)
3
Data Models• Data Model:– A set of concepts to describe the structure of a database,
the operations for manipulating these structures, and certain constraints that the database should obey.
• Data Model Structure and Constraints:– Constructs are used to define the database structure– Constructs typically include elements (and their data
types) as well as groups of elements (e.g. entity, record, table), and relationships among such groups
– Constraints specify some restrictions on valid data; these constraints must be enforced at all times
![Page 4: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/4.jpg)
4
Data Models (continued)• Data Model Operations:– These operations are used for specifying database
retrievals and updates by referring to the constructs of the data model.
– Operations on the data model may include basic model operations (e.g. generic insert, delete, update) and user-defined operations (e.g. compute_student_average_mark, update_inventory)
![Page 5: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/5.jpg)
5
Categories of Data Models• Conceptual (high-level, semantic) data
models:– Provide concepts that are close to the way many
users perceive data. • (Also called entity-based or object-based data
models.)• Physical (low-level, internal) data models:– Provide concepts that describe details of how data
is stored in the computer. These are usually specified in an ad-hoc manner through DBMS design and administration manuals
![Page 6: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/6.jpg)
6
Categories of Data Models• Implementation (representational) data
models:– Provide concepts that fall between the above two,
used by many commercial DBMS implementations (e.g. relational data models used in many commercial systems).
![Page 7: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/7.jpg)
7
ANSI/Spark Data Model• Developed by American National Standard
Institute/ Standards Planning and Requirements Committee)
• Also known as ANSI-SPARC Three-Level Architecture
• This model provides some level of data abstraction and data independence
• Data abstraction generally refers to the suppression of details of data organization and storage, and the highlighting of the essential features for an improved understanding of data
![Page 8: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/8.jpg)
8
ANSI/Spark Data Model• The different levels(schema) of data abstraction
are:1. Physical level or internal level2. Logical level or conceptual level3. View level or external level
![Page 9: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/9.jpg)
9
ANSI/Spark Data Model
ANSI/Spark three level architecture diagram
![Page 10: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/10.jpg)
10
ANSI/Spark Data Model• Data independence means the internal
structure of database should be unaffected by changes to physical aspects of storage I.e. the DBA can change the database storage structures without affecting the users view.
• The data independence can be both logical and physical
![Page 11: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/11.jpg)
11
ANSI/Spark Data Model• Logical Data Independence: – The capacity to change the conceptual schema
without having to change the external schemas and their associated application programs.
• Physical Data Independence:– The capacity to change the internal schema without
having to change the conceptual schema.– For example, the internal schema may be changed
when certain file structures are reorganized or new indexes are created to improve database performance
![Page 12: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/12.jpg)
12
ANSI/Spark Data Model• When a schema at a lower level is changed,
only the mappings between this schema and higher-level schemas need to be changed in a DBMS that fully supports data independence.
• The higher-level schemas themselves are unchanged.– Hence, the application programs need not be
changed since they refer to the external schemas.
![Page 13: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/13.jpg)
13
ANSI/Spark Data Model• 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)
![Page 14: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/14.jpg)
14
ANSI/Spark Data Model• The objective of this architecture is to
separate each user’s view of the database from the way the database is physically represented. There are several reasons why this separation is desirable:1. Each user should be able to access the same
data, but have a different customized view of the data.
2. Users should not have to deal directly with physical database storage details
![Page 15: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/15.jpg)
15
ANSI/Spark Data Model3. The DBA should be able to change the database
storage structures without affecting the users’ views.
4. The internal structure of the database should be unaffected by changes to the physical aspects of storage, such as the changeover to a new storage device.
5. The DBA should be able to change the conceptual structure of the database without affecting all users.
![Page 16: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/16.jpg)
16
View level or external level• The users’ view of the database. • This level describes that part of the database
that is relevant to each user.• Each external schema describes the part of
the database that a particular user group is interested in and hides the rest of the database from that user group.
![Page 17: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/17.jpg)
17
View level or external level- example
Auto insurance database: external schema.
![Page 18: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/18.jpg)
18
Logical level or conceptual level• The community view of the database.• This level describes what data is stored in the
database and the relationships among the data.
• This level contains the logical structure of the entire database as seen by the DBA.
• The conceptual model represents a global view of the entire database as viewed by the entire organization.
![Page 19: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/19.jpg)
19
Logical level or conceptual level• That is, the conceptual model integrates all
external views (entities, relationships, constraints, and processes) into a single global view of the data in the enterprise.
• The conceptual level represents:– all entities, their attributes, and their
relationships;– the constraints on the data;– semantic information about the data;– security and integrity information.
![Page 20: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/20.jpg)
20
Logical level or conceptual level
Auto insurance database: conceptual schema
![Page 21: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/21.jpg)
21
Physical level or internal level• The physical representation of the database
on the computer. This level describes how the data is stored in the database.
• The internal level is concerned with such things as:– storage space allocation for data and indexes;– record descriptions for storage (with stored sizes
for data items);– record placement;– data compression and data encryption techniques.
![Page 22: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/22.jpg)
22
Physical level or internal level• Below this level there is where data are
managed by the operating system under the direction of the DBMS
• The internal schema uses a physical data model and describes the complete details of data storage and access paths for the database.
![Page 23: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/23.jpg)
23
Physical level or internal level
Auto insurance database: internal schema.
![Page 24: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/24.jpg)
24
Database Schemas• The description of a database.• The overall design of the database is called the
database schema• It is a collection of named objects which
provide a logical classification of objects in the database.
• It Includes descriptions of the database structure, data types, and the constraints on the database.
![Page 25: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/25.jpg)
25
Database Schemas
• A schema is also an object in the database. It is explicitly created using the CREATE SCHEMA statement with the current user recorded as the schema owner.
• Schema Diagram:– An illustrative display of (most aspects of) a
database schema.• Schema Construct:– A component of the schema or an object within
the schema, e.g., STUDENT, COURSE.
![Page 26: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/26.jpg)
26
Database SchemasSchema classification
![Page 27: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/27.jpg)
27
Database Schemas - example
![Page 28: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/28.jpg)
28
Database Schema - example
![Page 29: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/29.jpg)
29
Database Instances• Database State:– The actual data stored in a database at a
particular moment in time. This includes the collection of all the data in the database.
– Also called database instance (or occurrence or snapshot).
• The term instance is also applied to individual database components, e.g. record instance, table instance, entity instance
![Page 30: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/30.jpg)
30
database state - example
![Page 31: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/31.jpg)
31
Database Schema vs. Database State
• Database State: – Refers to the content of a database at a moment
in time.• Initial Database State:– Refers to the database state when it is initially
loaded into the system.• Valid State:– A state that satisfies the structure and constraints
of the database.
![Page 32: Lecture 8 - Database Schema.pptx](https://reader033.vdocuments.us/reader033/viewer/2022061417/577c77f51a28abe0548e270e/html5/thumbnails/32.jpg)
32
Database Schema vs. Database State• Distinction– The database schema changes very infrequently. – The database state changes every time the
database is updated.