H1 Fundamental Concepts of Database Management
1.1 Applica*ons of Database Technology
Data is everywhere in different shapes and volumes ® needs to be stored and managed
- Numeric data: number of products in stock
- Alphanumeric data: product name
- Mul?media data: pictures, audio, video
- Biometric data: finger prints, re?na scans
- Spa?al/geographical data: Google Maps (® Geographical informa0on systems GIS)
- Vola?le data: high-frequency trading by investment banks
® Big Data = refers to the huge amounts of data being gathered and analyzed
1.2 Key Defini*ons
A database is a collec?on of related data items within a specific business process or problem
seMng.
® can also capture rela?ons: one order is connected to one supplier,…
® has a target group of users and applica?ons: inventory manager uses the purchase order
system to manage the inventory and issue new orders
A database management systems (DBMS) is the soPware package used to define, create, use
and maintain a database.
® typically consists of several soPware modules, each with their own func?onality
The combina?on of a DBMS and a database is oPen called a database system
1.3 File vs Database Approach to Data Management
1.3.1 The File-Based Approach
In the file-based approach, every applica?on
stored its data into its own dedicated files
® each datafile only contained the data
themselves, the data defini?ons and descrip?ons
were included in each applica?on seperatly
Problems:
- Since each applica?on uses its own data files and many applica?ons use similar data,
duplicate or redundant informa?on will be stored
® waste of storage resources
- Resulted in inconsistent data: data was updated in 1 file, but not in the others
In this approach, there was a strong coupling or dependency between applica?ons and the
data. A structural change in a data file necessitates changes in all applica?ons that use it
® not desirable from a maintenance perspec?ve
It was hard to manage concurrency control = the simultaneous access by different users or
applica?ons to the same data without conflict
,Since the applica?ons each work independently with their own ecosystem of data files, it
was difficult and expensive to integrate applica?ons aimed at providing cross-company
services
1.3.2 The Database Approach
In the database approach, all data are stored and
managed centrally by a DBMS. The applica?ons
now directly interface with the DBMS instead of
with their own files. The DBMS delivers the
desired data at the request of each applica?on.
DBMS stores and manages 2 types of data:
- Raw data
- Metadata: refers to data defini?ons that are now stored in the catalog of the DBMS
® key difference to the file-based approach: the metadata are no longer included in the
paplica?ons, but are now properly managed by the DBMS itself (efficient, consistent)
In file-based approach: every applica?on had to write its own query and access procedures
Now: DBMS provide database languages that facilitate both data querying and access
® e.g: SQL – only specify which data we need, now longer how it should retrieve it
File-based approach results in a strong applica?on-data dependence, whereas the database
approach allows for applica?ons to be independent from the data and the data defini?ons
1.4 Elements of a Database System
1.4.1 Database Model vs Instances
In every database implementa?on, it is important to dis?nguish between the descrip?on of
the data, or data defini?ons, and the actual data.
The database model or database schema provides the descrip?on of the database data at
different levels of detail and specifies the various data items, their characteris?cs, and
rela?onships, constraints, storage details etc.
This model is specified during database design and is not frequently changed
® stored in the catalog, which is the heart of the DBMS
The database state represents the data in the database at a par?cular moment.
® is some?mes called the current set of instances
Unlike the database model, this is expected to change on an ongoing basis through data
manipula?ons (adding, upda?ng, removing data)
e.g.: Database Model vs Database State
Student (number, name, address) Student (r0898914, Emile, Hasselt)
Course (number, name) Course (D048DF, PDBM)
Building (number, address) Building (DV1, Tiensestraat)
3 data items: student, course and building
® can be described in terms of its characteris?cs: student is described by number, name,…
,1.4.2 Data model
A database model comprises different data models, each describing the data from different
perspec?ves.
® the start of every successful database is a good data model!
This provides a clear and unambiguous descrip?on of the data items, their rela?onships, and
various data constraints from a par?cular perspec?ve. There are several data models
developed during a database design process.
A conceptual model provides a high-level descrip?on of the data items (supplier, product)
with their characteris?cs (supplier name, product number) and rela?onships (a supplier
supplies a product)
® communica?on instrument between the informa?on architect and business user to make
sure the data requirements are adequately captured and modeled
Should be:
- Implementa?on independent
- User-friendly
- Close to how the business user perceives the data
The conceptual model will usually be represented using an Enhanced En?ty Rela?onship
(EER) or an object-oriented model
A logical model is a transla?on or mapping of the conceptual data model toward a specific
implementa?on environment
® these logical data items may s?ll be understood by business users, but are not too far
removed from the physical data organiza?on
This logical model can be hierarchical, CODASYL, rela?onal, object-oriented, XML, ….
The logical model can be mapped to an internal data model that represents the data’s
physical storage details
® describes which data are stored where, in what format, ...
This is highly DBMS-specific
The external data model contains various subsets of the data items in the logical model, also
called views, tailored toward the needs of specific applica?ons or groups of users
1.4.3 The Three-Layer Architecture
The three-layer architecture is an essen?al element of every database applica?on and
describes how the different underlying data models are related
We start with the conceptual/logical layer: here
are the conceptual and logical data models.
® both focus on the data items, characteris?cs,
and rela?onships without bothering about the
actual physical DBMS implementa?on
The conceptual model will be refined to a logical
data model based upon the implementa?on
environment
, In the external layer we have the external data model: includes views offering a window on a
carefully selected part of the logical data mode
A view describes the part of the database that a par?cular applica?on or user group is
interested in, hiding the rest of the database
® used to control data access & enforce security
The internal layer includes the internal data model: specifies how the data are stored or
organized physically
Changes in one layer should have no to minimal impact on the others:
- Should be possible to physically reorganize the data with linle impact on the
conceptual/logical or external layer ® physical data independence
- Changes to conceptual/logical model can be made with minimal impact on the
external layer ® logical data independence
e.g. p10-11
1.4.4 Catalog
The catalog is the heart of the DBMS: it contains the data defini?ons, or metadata, of your
database applica?on.
® stores the defini?ons of views, logical and internal data models, and synchronizes these 3
data models to ensure their consistency
1.4.5 Database Users
Various types of users interact with the database
- Informa?on architect: designs the conceptual data model and closely interacts with
the business architect
- Business architect: makes sure the data requirements are fully understood and
modeled
- Database designer: translates the conceptual data model into a logical and internal
data model
- Database administrator (DBA): is responsible for the implementa?on and monitoring
of the database
® sets up the database infrastructure and con?nuously monitors its performance by
inspec?ng KPI’s (e.g. response ?mes, storage space consumed,…)
- Applica?on developer: develops database applica?ons in a general-purpose
programming language such as Java or Python
® provides the data requirements (that are translated by DBA into view defini?ons)
- Business user: will run the applica?ons to perform specific database applica?ons
® can directly query the database
1.4.6 Database Languages
Every database comes with 1 or more accompanying database languages
The data defini?on language (DDL) is used by the DBA to express the database’s external,
logical, and internal data models
® these defini?ons are stored in the catalog
