CST403 Principles of
Database Systems
Database Systems Concepts, Data Schema and Architecture
Ms. Veeramani
Outline
• Data models and its categories
• Schema and instances
• Database languages
Data Models
• 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
• Structure: elements (including its data types), relationships
• Constraint: specify some restrictions on valid data; these constraints
must be enforced at all times
• Operations: specifying database retrievals and updates by referring to
the constructs of the data model
• General operation such as update, insert and delete
• User-defined operation such as compute_student_gpa, update_inventory
Data Model Techniques
• Entity Relationship Model (ERM) or Entity Relationship Diagram
(ERD), will be discussed in Chapter 3
• Unified Modelling Language (UML)
Categories of Data Model
Conceptual Logical Physical
• Define WHAT system • Define HOW the • Describe HOW the
contains system should be system will be
• Provide concepts that implemented implemented in DBMS
are close to the way regardless of the • Provide concepts that
many users perceive DBMS describe details of
data • Provide extra details how data is stored in
• Created by Business to enrich the concept the computer
stakeholders and • Created by Data • Created by DBA and
Data Architects Architects and administrator
Business Analysts
• Conceptual
• Logical
• Physical
Conceptual vs. Logical vs. Physical Data Model
ERD Feature Conceptual Logical Physical
Entity (name) Yes Yes Yes
Relationship Yes Yes Yes
Attribute (column) - Yes Yes
Attribute’s type - Optional Yes
Primary key - - Yes
Foreign key - - Yes
Schemas
• Database schema
• The description of a database
• Includes descriptions of the database structure, data types, and the
constraints on the database
• Defines the database using the schema diagram
• 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
Example of Schema
Instances
and State
• The actual data stored in a
database at a particular
moment in time (state)
• Also called database
instance (or occurrence or
snapshot)
Database state
Three-Schema Architecture
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 (multiple user view)
describes the structure of the whole
database (high-level description) for a
community of users
describes the physical storage structure of
the database
DBMS Languages
• A DBMS has appropriate languages and interfaces to express
database queries and updates
• Database languages can be used to read, store and update the data in
the database
DDL DML
DCL TCL
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) Create • Create objects in the database
• Some of the tasks that
Alter • To alter the structure of the database
come under DDL
Drop • To delete objects from the database
Truncate • To remove all records from a table
Rename • To rename an object
• To comment on the data dictionary
Comment
(metadata)
Data Manipulation Language (DML)
• Used to specify database retrievals and updates
• Can be embedded in a general-purpose programming language (host
language)
• Some of the tasks that Select • To retrieve data from a database
come under DML
Insert • To insert data into a table
• To update existing data within a
Update
table
Delete • To delete all records from a table
Data Control Language (DCL)
• It is used to retrieve the stored or saved data
• Mainly used for user authorization to the database
• Some of the tasks that • To give user access
come under DCL Grant
privileges to a database
• To take back permissions
Revoke
from the user
Transaction Control Language (TCL)
• Used to manage transactions in the database and manage the
changes made by DML-statements
• Some of the tasks that • To permanently save any
come under TCL Commit
transaction into the database
• To restore the database to the
Rollback
last state or any savepoint
• To temporarily save a
transaction so that you can
Savepoint
rollback to that point
whenever necessary
Summary
• The Conceptual Model is to establish the entities, their attributes, and
their relationships
• The Logical Data Model defines the structure of the data elements and set
the relationships between them
• The Physical Data Model describes the database-specific implementation
of the data model
• The database schema changes very infrequently
• The database state changes every time the database is updated
• In order to support multiple user views and program-data independence in
database approach, three-schema architecture was proposed.
