Fundamentals of Database Systems
1
Database System Concepts and Architecture
Module 2 Database System Concepts and
Architecture
Course Learning Outcomes:
1. Understand the basic concepts of modern database architecture
2. Familiarize with the term data models, Schema, and Instances
3. Categorize differences between the Three-Schema Architecture
4. Discuss the main types of database languages and interfaces
Data Models, Schemas, and Instances
Using database approach, it provides us some level of data abstraction. Data abstraction is
the suppression of data and highlighting the essential feature of the data to improve the
understanding of that data. Data abstraction is one of the main characteristics of database
approach, it helps the users to perceived data according to their preferred level of detail.
Data model is a collection of concepts that is used to describe the structure of a database.
Structure of a database it is the data type, relationships and constraints that apply to the
data.
Categories of Data Models
Many data models have been proposed, which we can categorize it according to how
they describe the structure of a database. High-level or conceptual data models is a
concept that how many users perceived data, while Low-level or Physical data
models is a concept that shows how the data is stored on the computer. Concepts that
come from physical data models are for computer specialist, not for end users.
Conceptual Data Models
Use three (3) concepts: entities, attributes, and relationships.
• Entity – It is a name for a set of similar things that you can list.
Like objects, events, people.
• Attributes – Provides more specific information about an entity.
• Relationships – Describes how entities are related.
Course Module
Representational or Implementation Data Models
It is between high-level and low-level data models. Provides concepts that can
be understood by the end-user, hides some details of how data is stored in the
database, can be implemented directly.
Schema, Instances, and Database State
There’s a difference between the description of database and the database itself.
The description of database is called Database Schema. It was specified while
designing the database and it’s not changing frequently. Data models have their own
way displaying schemas as diagrams.
A displayed schema is called Schema Diagram.
Figure 2.1 Schema Diagram of a University Database (module 1 figure 1.2)
Objects like STUDENT, COURSE, PREREQUISITE, SECTION, and GRADE_REPORT is
called Schema Construct.
The actual data in the database may change often. For example, the university
database shown in module 1 figure 1.2 changes every time when we add a new course
or enter a new section.
The data in the database is called database state or current set of instances. Each
schema constructs have its own instances. For example, the Student construct has the
information of the students like their names, class or major as its instances.
Fundamentals of Database Systems
3
Database System Concepts and Architecture
Every time there’s a change in the value of data, we also change the one state of the
database into another state.
The Three-Schema Architecture
The objective of the three-schema architecture is to know difference or to separate
the user application from the physical database.
1. Internal Level – it has an internal schema. It describes the physical storage
structure of the database.
2. Conceptual Level – it has a conceptual schema. It describes the structure of
the database for a community of users.
3. External or View Level – it has several external schemas or what they called
the user views.
Figure 2.2 Three Schema Architecture
Course Module
Database Languages
Database Management System provides different languages and interfaces for each
kind of users. Database languages are used to read, update and store the value of the
data in the database.
1. Data Definition Language (DDL)
• Used for specifying database structure or schema.
• Used for creating tables, schema etc. in database.
Sample Operations of DDL
• CREATE – used to create objects in the database.
• DROP – used to delete objects from the database.
• RENAME – used to rename objects from the database.
2. Data Manipulation Language (DML)
• Used for manipulating data in a database.
• Used to manage data within the object schema.
Sample Operations of DML
• SELECT – used to read/retrieve data from a database.
• INSERT – used to insert data into a table.
• DELETE – used to delete all the records from a table.
3. Data Control Language (DCL)
• Used for granting and revoking user access on a specific database.
• Used to give or control privilege in a database.
Sample Operations of DCL
• GRANT – used to grant access to a user
• PROVOKE – used to revoke or take back permission from the user.
4. Transaction Control Language (TCL)
• Used to performed or rollback the changes that we made in the
database using DML commands.
Sample Operations of TCL
• COMMIT – used to persist the changes made by DML commands in
database.
• ROLLBACK – used to rollback the changes made to the database
Fundamentals of Database Systems
5
Database System Concepts and Architecture
Database Interfaces
Database Management System (DBMS) provides user-friendly interfaces for many
users. The interface of DBMS could be a web-client, local client or even a mobile app.
1. Menu-based Interfaces for Web Clients or Browsing
The interface presents a menu that lead to a user through request.
Figure 2.3 Example of a Menu-driven Interface
2. Applications for mobile devices
The interface allows users to access their data through their mobile
phone or mobile device.
Figure 2.3 Example of a Banking app
Course Module
3. Forms-based Interfaces
Display a form to be fill out by the user.
Figure 2.4 Example of a form
4. Keyword-based Database Search
Like Web search engines (engine like Google), which the user can
specify a string or any keywords and expect to get a relevant result.
Figure 2.5 Example of a Web Search Engine
Fundamentals of Database Systems
7
Database System Concepts and Architecture
References and Supplementary Materials
Books and Journals
1. Ramez Elmasri and Shamkant B. Navathe; 2016; Fundamentals of Database Systems;
USA; Pearson
2. Dr. Kashif Qureshi; 2018; Advanced concepts of information technology; educreation
publishing; India.
Online Supplementary Reading Materials
1. RelationalDBDesing; https://www.relationaldbdesign.com/basic-sql/module3/intro-
relational-databases.php; March 31, 2020
2. Advantages of Database Management System;
https://www.tutorialspoint.com/Advantages-of-Database-Management-System;
March 31, 2020
3. DesigningandManagingData;
https://www.academia.edu/36712448/Entity_Relationship_Diagram_ERD_Basics_CIS
_3730_Designing_and_Managing_Data; April 01, 2020
4. DBMS Schemas: Internal, Conceptual, External; https://www.guru99.com/dbms-
schemas.html; April 01, 2020
5. DBMS languages; https://beginnersbook.com/2015/04/dbms-languages/; April 01,
2020
6. DBMS DATABASE Languages; https://www.w3schools.in/dbms/database-
languages/; April 01, 2020
7. IT Release ; http://www.itrelease.com/wp-content/uploads/2018/07/Example-of-
menu-driven-interface.jpg; April 01, 2020
8. Stackoverflow; https://stackoverflow.com/questions/45952410/issue-with-user-
interface-in-windows-form; April 01, 2020
Online Instructional Videos
1. Introduction to Database; https://www.youtube.com/watch?v=8e-
wgQnsFxE&list=PLJ5C_6qdAvBHKccG0ZyOxcf_2YO6r4Q4l; March 21, 2020
2. Three levels of Architecture/DBMS;
https://www.youtube.com/watch?v=j6xh8wKfjkY; April 01,2020
Course Module