Datamodeling and Databases Laura de Laat
Chapter 1 – Introduction
1.1 Database-System Applications
A database is the collection of data. A database-management system (DBMS) is a collection
of interrelated data and programs to access those data. A DBMS provides a way to store and retrieve
database information that is convenient and efficient.
1.2 Purpose of Database Systems
An early method of management of data was the file-processing system, where more files and
application programs are used as the time goes by. This is supported by a conventional operating
system, which were used before DBMS’s were introduced. The file-processing system had some
disadvantages:
- Data redundancy and inconsistency: the same information can be stored in several files, using
different programs. This redundancy leads to data inconsistency, where copies of the same
data don’t agree.
- Difficulty in accessing data: it doesn’t allow some data to be retrieved without manually
selection or writing a program.
- Data isolation: writing new application programs can be difficult, because all files can be in
different formats.
- Integrity problems: data values must satisfy consistency constraints. When new constraints
are added, it is difficult to change the programs to make sure the constraints are met.
- Atomicity problems: it is difficult to ensure atomicity, something must happing entirely or not
at all.
- Concurrent-access anomalies: when multiple users want to make adjustments to the data at
the same time, it can happen that it will only adjust to one of the changes.
- Security problems: applying security constraints is difficult.
Due to this difficulties, the development of database systems was stimulated.
1.3 View of Data
A database system provides users with an abstract view of the data; the system hides certain
details of how the data are stored and maintained.
1.3.1 Data Abstractio
The complexity of database-systems is hidden from users through several levels of abstraction:
- Physical level: how the data are stored. It describes complex low-level data structures in
detail.
- Logical level: what data are stored and what relations exists among those data. It describes
relatively simple structures. It involves physical data independence, where the user doesn’t
need to be aware of the complexity that the simple structures may involve.
- View level: describes only part of the entire database, to simplify the users interaction with
the system if they don’t need all information.
1.3.2 Iostaoces aod Schemas
The data in the database at a particular moment is the instance. The overall design of the database is
the schema. Schemas hardly change. The physical schema describes the design at the physical level
and the logical schema describes the design at the logical level. There can also be subschemas, where
schemas at the view level are used.
1
,Datamodeling and Databases Laura de Laat
1.3.3 Data Midels
The data model is a collection of conceptual tools for describing data, data relationships, data
semantics and consistency constraints. It helps to describe the design of a database at the different
levels. There are four different categories of data models:
- Relational Model: uses tables to represent data and the relations among those data. It’s an
example of a record-based model.
- Entity-Relationship Model: uses a collections of entities, basic real world objects, and
relations among these objects.
- Object-Based Data Model: extends the Entity Relationship Model by using encapsulation,
methods and object identity.
- Semistructured Data Model: permits the specification of data where individual data items of
the same type have different sets of attributes / properties.
Ex. the Extensible Markup Language (XML): has become important because of its
applications in data exchange. It represents data that have nested structure and
allows a flexibility in structuring of data.
The network data model and the hierarchical data model preceded the relational data model. They are
hardly used now.
1.4 Database Languages
There are two types of database languages, that together form parts of a single database
language.
1.4.1 Data-Maoipulatio Laoguage
Data manipulation language (DML) enables users to access or manipulate data as organized by the
data model. The types of access are:
- Retrieval of information
- Insertion of new information
- Deletion of information
- Modification of information
Two types of DMLs are:
- Procedural DMLs: user must specify what data are needed and how to get it.
- Declarative / nonprocedural DMLs: user must specify what data are needed without
specifying how to get it.
A query is a statement requesting the retrieval of information. The query language is the part of a
DML that involves this information retrieval. The query language SQL is mostly studied in this book.
1.4.2 Data-Defoitio Laoguage
A data-definition language (DDL) specifies a database schema by a set of definitions. It specifies
additional property of the data. A special type of DDL is data storage and definition language, where
storage structures and access methods that are used are specified. Data values must satisfy certain
consistency constraints. In order to accomplish this, database systems implement integrity constraints
that can be tested:
- Domain constraints: a domain of possible values must be associated with every attribute.
- Referential integrity: helps assure that a value that appears in one relation also appears in
another relation if that is wanted.
- Assertions: conditions that the database must always satisfy.
- Authorization: differentiates for users the type of access they are allowed to have.
2
, Datamodeling and Databases Laura de Laat
The output of the DDL is placed in the data dictionary, which is a special table that can only be
accessed by the database system. It contains metadata; data about data.
1.5 Relational Databases
A relational database uses tables to represent data and the relations among the data. It also
includes a DDL and DML.
1.5.1 Tables
Each column in a table has a unique name. Each table contains records of a particular type, which
defines attributes.
1.5.2 Data-Maoipulatio Laoguage
A query takes as input several tables and returns only one table. It selects certain rows from a given
table, where a condition is met. SQL uses this method.
1.5.3 Data-Defoitio Laoguage
SQL provides a DDL that allows to define tables, integrity constraints and assertions.
1.5.4 Database Access frim Applicatio Prigrams
With SQL, not all computations are possible. It also doesn’t support input from users, output to
displays or communication. Application programs supports the database in doing this. Databases can
be accessed by providing an application program interface or by using a DML precompiler, which
converts the DML statements to normal procedure calls in the host language.
1.6 Database Design
Database design mainly involves the design of the database schema. The focus is on the
writing of database queries and the design of database schemas.
1.6.1 Desigo Pricess
The initial phase of database design is to characterize the data needs of the future database users.
Then, the conceptual-design phase takes place, where the data model is chosen and applied. Here, the
focus is on describing the data and their relations. The conceptual-design process involves what
attributes are captured and how to group these attributes. In the specification of functional
requirements, users describe the operations that will be performed on the data. There are two final
design phases:
- Logical-design phase: the designer maps the high-level conceptual schema onto the
implementation data model.
- Physical-design phase: specifies physical features of the database.
1.6.2 The Eottt-Relatioship Midel
The entity-relationship (E-R) data model uses entities and relations among them. The entities are
described by a set of attributes. The relationship is an association among several entities. The entity
set is the set of all entities with the same type and the relationship set is the set of all relationships of
the same type. The schema of a database can be expressed by an E-R diagram. One way to draw this
is with the Unified Modeling Language (UML). In the E-R diagram in figure 1.1 can be seen that
entity sets are boxes and the relationships are diamonds connecting entity sets.
3
