3. Introduction to Databases Flashcards
Data Organization (Data Tier)
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.
Data File Approach (Obsolet)
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
Problems with Traditional Data Processing
- Redundancy = multiple copies of data in different data files
- Inconsistency = Different values saved for one attribute
- Dependencies between data and application = data and software are strongly connected (obsolet) and change in code requires change in data structure
- No exchange of data & lack of flexibility
- No data security = different locations where data is saved doesn’t allow control of data access and -modification
Database Systems (Solution/Purpose, Definition & Functions of a Database)
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
Database Schema (Schema Definition, Data Model Definition)
- 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,..)
Database Structure Elements
Every database needs a structural description of:
- the objects which are represented in the database
- the object’s characteristics
- the relationship between these facts
Data Organization (Kind of Storage, Characteristics)
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
Relational Databases (Entities Definition, Keys Definition)
- 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)
SQL
= Data base language used to interact with relational databases -> most popular & industry standard
Database Management Systems - Overview (Purpose and Characteristics (2))
= 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
Database Management System - Components
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
The ACID-Paradigm
- Atomicity = transactions has to be treated as the smallest, non divisible unit; either all, or no operation is executed at all
- Consistency = the state of a DB may be inconsistent temporarily - but the completion of a transaction must result in a consistent DB state
- Isolation = transactions must not interfere and have to be processed as if it was the only transaction executed on the DB
- Durability = effects of a successful transaction on a database are persistent
Advantages of Using DBMS (5)
- 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)
Data - Application of Independence (Management of Data)
Applications do not directly access or manipulate data
=> DBMS is exclusively managing the data
Transaction (Definition)
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
Rollbacks (Pre-circums for successful Transaction, Consequence if not)
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
Multiple Users (Problem, BUT)
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
Problems caused by multiple users
- Lost Update = overwriting updates due to concurrent data access where one query-update will not be considered
- Dirty Read = Transaction are read from an inconsistent state
- Phantom Read same request lead to different results due to duplicity of queries
Multiple User Synchronization
DBMS provides means to synchronize multiple users:
- Serializability = controlled, concurrent, interleaving execution of a set of transactions; transactions appear to be performed in isolation
- 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
Entity Relationship (ER) Model - Elements (5)
- 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)
Database implementation (4 Steps)
- Defining the business case
- Creating an Entity Relationship Model (ERM)
- Translating the ER model into database tables
- Insert data into the database (SQL)
