3. Introduction to Databases

Question 1

Data Organization (Data Tier)

Answer 1

The Data Tier comprises elements which provide

• persistent storage of data
• reading of this data
• writing access to this data

Within computer systems data can be stored in a hierarchical way.

Question 2

Data File Approach (Obsolet)

Answer 2

Store’s data in a computer’s file system
- information systems are not planned inter-divisional => systems often were planned separately

Sharing of data was not considered:

• redundant data was maintained and processed
• organization of data was tailored to division-specific applications
• data is not easy usable by other applications

Question 3

Problems with Traditional Data Processing

Answer 3

1. Redundancy = multiple copies of data in different data files
2. Inconsistency = Different values saved for one attribute
3. Dependencies between data and application = data and software are strongly connected (obsolet) and change in code requires change in data structure
4. No exchange of data & lack of flexibility
5. No data security = different locations where data is saved doesn’t allow control of data access and -modification

Question 4

Database Systems (Solution/Purpose, Definition & Functions of a Database)

Answer 4

Solution for problems resulting from traditional “file approach”.

• Database = structured collection of data records
• > stores data in a way that a computer program can query the database where the returned data is the answer to the query and turns data into meaningful information

Question 5

Database Schema (Schema Definition, Data Model Definition)

Answer 5

• Schema = structural description of a database
• data model = used to organize the database schema (choice of data model determined the kind of database (e.g. hierarchical, object-oriented, relational,..)

Question 6

Database Structure Elements

Answer 6

Every database needs a structural description of:

• the objects which are represented in the database
• the object’s characteristics
• the relationship between these facts

Question 7

Data Organization (Kind of Storage, Characteristics)

Answer 7

In relational databases data is stored in tables.
-> every table in a DB should have a column with values that uniquely identify the different data records
-> every table in the DB represents a set of entities of the same type
=> allows the easy structuring of data and the access via SQL

Question 8

Relational Databases (Entities Definition, Keys Definition)

Answer 8

• Entity type = specified by a set of attributes
• specific entity = characterized by the attribute values
  An specific entity should be stored exactly once!
• Primary Key = uniquely identifying attribute (e.g. matriculation number, other forms of ID)
• Foreign Key = set of attributes in one table that uniquely identify a row of another table (cross-reference)

Question 9

SQL

Answer 9

= Data base language used to interact with relational databases -> most popular & industry standard

Question 10

Database Management Systems - Overview (Purpose and Characteristics (2))

Answer 10

= a collection of programs which enable the central and efficient management of data

• interface between application software and physical data
• separates the logical structure from the physical data structure

Question 11

Database Management System - Components

Answer 11

DBMS offers several components for the creation, manipulation & evaluation in the database:

• DDL (CREATE, ALTER, DROP)
• DML (INSERT, UPDATE, SELECT, DELETE -> with respect to the foreign key when UPDATE or DELETE: NO ACTION, CASCADE, SET NULL or SET DEFAULT)
• DCL = allows to control access into DB

Question 12

The ACID-Paradigm

Answer 12

1. Atomicity = transactions has to be treated as the smallest, non divisible unit; either all, or no operation is executed at all
2. Consistency = the state of a DB may be inconsistent temporarily - but the completion of a transaction must result in a consistent DB state
3. Isolation = transactions must not interfere and have to be processed as if it was the only transaction executed on the DB
4. Durability = effects of a successful transaction on a database are persistent

Question 13

Advantages of Using DBMS (5)

Answer 13

• Redundancy & Inconsistency Control
• Independence between application and data
• Provides a transaction mechanism
• Allows and manages the access of multiple users
• Manages and enforces the access rights to data (data security)

Question 14

Data - Application of Independence (Management of Data)

Answer 14

Applications do not directly access or manipulate data

=> DBMS is exclusively managing the data

Question 15

Transaction (Definition)

Answer 15

Transaction = a group of atomic operations which is executed as a single unit without interference by other database operations
-> either the entire group of operations is executed or no operation at all

Question 16

Rollbacks (Pre-circums for successful Transaction, Consequence if not)

Answer 16

If a transaction is executed:

• every single operation of the transaction has to be performed
• the resulting database state has to be consistent

=> of one of the two cases does not apply, the entire transaction has to be rolled back

Question 17

Multiple Users (Problem, BUT)

Answer 17

If data is organized on a central unit, data access has to be coordinated.
-> Problem: Multiple users may perform database operations at the same time (= concurrent modification)

-> BUT: Restricting data use to one person at a time would be inefficient/waste of resources
=> critical situations have to be identified in order to avoid them

Question 18

Problems caused by multiple users

Answer 18

1. Lost Update = overwriting updates due to concurrent data access where one query-update will not be considered
2. Dirty Read = Transaction are read from an inconsistent state
3. Phantom Read same request lead to different results due to duplicity of queries

Question 19

Multiple User Synchronization

Answer 19

DBMS provides means to synchronize multiple users:

1. Serializability = controlled, concurrent, interleaving execution of a set of transactions; transactions appear to be performed in isolation
2. Locking = the data that needs to be accessed is locked for a particular transaction and unlocked after successful execution -> only one user at the time

Question 20

Entity Relationship (ER) Model - Elements (5)

Answer 20

• Entity = a model of a concept that exists in real life
• Relation = An association among entities
• Attributes = A property/characteristic of an entity or relationship
• Primary Key = Minimal set of uniquely identifying attributes of an entity
• Cardinalities = quantifies possible relations between entities (One-to-one, One-to-many, Many-to-many)

Question 21

Database implementation (4 Steps)

Answer 21

1. Defining the business case
2. Creating an Entity Relationship Model (ERM)
3. Translating the ER model into database tables
4. Insert data into the database (SQL)