CHAPTER 1: FUNDAMENTAL CONCEPTS OF DBM
FILE VS DATABASE (SLIDE 7)
File Approach:
- Duplicate or redundant information will be stored
- Each application has its own data files, leading to potential redundancy
- Data definitions are included within each application separately. This decentralization can lead to
inconsistencies in how data is defined across applications.
- Every application has its own query and acess procedures (even if they want to acces the same data)
- Different applications could be using older and newer versions of the same data
Database Approach:
- Superior to file approach in terms of efficiency, consistency and maintenance
- Loose coupling between applications and data
- Facilities provided for data querying and retrieval
- Applications don't have their own files
- Ensures that all applications use the same set of definitions
- Less storage compared to the file approach because data is organized more efficiently, and
redundancy is minimized
- Maintance of data and metadata is easier
WHAT IS SQL?
- Database language
o To manage DBMS without having to write a substantial amount of programming code
o That allows different applications to access different subsets of the data necessary for each
application
DATABASE MODEL VS DATABASE STATE (SLIDE 13)
Database model: description of the database at different levels of detail and specifying the various data items
ð Stored in the catalog (heart of the DBMS)
Database state: represents the date in the database at a particular moment => current state of the database
ð So changes when data is updated or removed
1
,ARCHITECTURE (SLIDE 17)
A. Conceptual datamodel
- High level description if the data items with their characteristics and relationships
o Implementation: independent, user-friendly…
o Communication instrument between information architect and business user
- EER
- represents the high-level business requirements
B. Logical datamodel
- Translation or mapping the conceptual data model towards a specific implementation environment
- refines these concepts into a more detailed and implementable structure
o Hierarchical
o CODASYL
o Relational
o Object-oriented
o Extended relational
o XML
o noQSL
ð we do pick a DBMS type
C. Internal datamodel
- To which the logical model is mapped
- Represents the data’s physical storage details
o which data, which format…
D. External datamodel
- Contains various subsets of the data items in the logical model
THE THREE LAYER ARCHITECTURE (SLIDE 19)
1. External layer
2. Conceptual/logical layer
3. Internal layer
a. Internal model is here situated
Changes in any of the layers in the three-layer architecture should minimally impact the other layers
Views: can be defined for a particular application or user group. This facilitates control of data access and
security.
To cater for data independence: we provide interfaces between the layers
DATABASE LANGUAGES (SLIDE 23)
DDL: is used by the DBA to express the database’s external, logical and internal data models (schema)
DML: is used to retrieve, insert, delete, and modify data (state)
ð SQL can be used as both
2
, DATA INDEPENDENCE (SLIDE 25)
Physical: means that changes in the data storage specifications should not impact applications, the defined
views or the the logical model
Logical: means that the software applications are minimally alected by changes in the conceptual or logical
data model
INTEGRITY RULES (SLIDE 30)
Semantic integrity rules are concerned with the meaning and relationships between data, ensuring that the
data accurately represents the real-world context.
- An employee of a department can never earn more than the manager of the department
- A book should have exactly one author
Syntactic integrity rules, on the other hand, focus on the correct structure, format, and representation of data
to maintain consistency at a more superficial level
- The 'Phone Number' field must contain ten digits.
- A book's ISBN should consist of 13 integers
ACID (SLIDE 33)
Atomicity, Consistency, Islation, Durability
Consistency: assures that the database is consistent at all times
Isolation: ensures that the effect of concurrent transactions should be the same if they had been executed in
isolation
Durability: means that effects of each successful transaction can be made permanent
KPI’S DBMS (SLIDE 33)
Response time: denoting the time elapsed between issuing a database request and the successful termination
thereof
Throughput rate: the number of transactions a DBMS can process per unit of time
Space utilization: referring to the space utilized by the DBMS to store both raw data and metadata
3
FILE VS DATABASE (SLIDE 7)
File Approach:
- Duplicate or redundant information will be stored
- Each application has its own data files, leading to potential redundancy
- Data definitions are included within each application separately. This decentralization can lead to
inconsistencies in how data is defined across applications.
- Every application has its own query and acess procedures (even if they want to acces the same data)
- Different applications could be using older and newer versions of the same data
Database Approach:
- Superior to file approach in terms of efficiency, consistency and maintenance
- Loose coupling between applications and data
- Facilities provided for data querying and retrieval
- Applications don't have their own files
- Ensures that all applications use the same set of definitions
- Less storage compared to the file approach because data is organized more efficiently, and
redundancy is minimized
- Maintance of data and metadata is easier
WHAT IS SQL?
- Database language
o To manage DBMS without having to write a substantial amount of programming code
o That allows different applications to access different subsets of the data necessary for each
application
DATABASE MODEL VS DATABASE STATE (SLIDE 13)
Database model: description of the database at different levels of detail and specifying the various data items
ð Stored in the catalog (heart of the DBMS)
Database state: represents the date in the database at a particular moment => current state of the database
ð So changes when data is updated or removed
1
,ARCHITECTURE (SLIDE 17)
A. Conceptual datamodel
- High level description if the data items with their characteristics and relationships
o Implementation: independent, user-friendly…
o Communication instrument between information architect and business user
- EER
- represents the high-level business requirements
B. Logical datamodel
- Translation or mapping the conceptual data model towards a specific implementation environment
- refines these concepts into a more detailed and implementable structure
o Hierarchical
o CODASYL
o Relational
o Object-oriented
o Extended relational
o XML
o noQSL
ð we do pick a DBMS type
C. Internal datamodel
- To which the logical model is mapped
- Represents the data’s physical storage details
o which data, which format…
D. External datamodel
- Contains various subsets of the data items in the logical model
THE THREE LAYER ARCHITECTURE (SLIDE 19)
1. External layer
2. Conceptual/logical layer
3. Internal layer
a. Internal model is here situated
Changes in any of the layers in the three-layer architecture should minimally impact the other layers
Views: can be defined for a particular application or user group. This facilitates control of data access and
security.
To cater for data independence: we provide interfaces between the layers
DATABASE LANGUAGES (SLIDE 23)
DDL: is used by the DBA to express the database’s external, logical and internal data models (schema)
DML: is used to retrieve, insert, delete, and modify data (state)
ð SQL can be used as both
2
, DATA INDEPENDENCE (SLIDE 25)
Physical: means that changes in the data storage specifications should not impact applications, the defined
views or the the logical model
Logical: means that the software applications are minimally alected by changes in the conceptual or logical
data model
INTEGRITY RULES (SLIDE 30)
Semantic integrity rules are concerned with the meaning and relationships between data, ensuring that the
data accurately represents the real-world context.
- An employee of a department can never earn more than the manager of the department
- A book should have exactly one author
Syntactic integrity rules, on the other hand, focus on the correct structure, format, and representation of data
to maintain consistency at a more superficial level
- The 'Phone Number' field must contain ten digits.
- A book's ISBN should consist of 13 integers
ACID (SLIDE 33)
Atomicity, Consistency, Islation, Durability
Consistency: assures that the database is consistent at all times
Isolation: ensures that the effect of concurrent transactions should be the same if they had been executed in
isolation
Durability: means that effects of each successful transaction can be made permanent
KPI’S DBMS (SLIDE 33)
Response time: denoting the time elapsed between issuing a database request and the successful termination
thereof
Throughput rate: the number of transactions a DBMS can process per unit of time
Space utilization: referring to the space utilized by the DBMS to store both raw data and metadata
3
