L03 - Databases Flashcards
Data Organization
The data tier comprises elements which provide persistent storage of data and reading and writing access to this data. Within computer systems, data can be organized in a hierarchical way.
Data File Approach
It stores data in the computer´s file system. Often, information-systems are not planned inter-divisional. In many cases those systems were planned separately from each other and sharing of data was not considered. That lead to redundant data and the organization of data was tailored to the division-specific applications. (Framework Slide 9).
Problems with Traditional Data Processing
Redundancy:
- Multiple copies of a date in different data files.
Inconsistency:
- Different values are saved for one attribute.
Dependencies between application and data:
- Data in files and software for data processing and management are strongly connected. A change in program code requires change in data structure.
No exchange of data/ lack of flexibility:
- Data in files are inflexible with respect to evaluations and relationships. Concurrent access to multiple attributes and choice of desired data element requires expensive searching and sorting procedures.
No data security:
- As data is saved in different at location access to data and their modification cannot be controlled.
Database Systems as the solution
Database Systems can solve many of the problems resulting from the traditional “file approach”. It contains the Database and Database Management System (DBMS). (e.g. HR or paying employees).
A database is a structured collection of data records. It stores data in a way that a computer program may query the database. The returned data is the answer to the query and the extracted data is said to be information.
Database Schema
Every database needs a structural description of the objects which are represented in the database and their characteristics and the relationship between these facts. The schema of the database is a structural description of a database. The data model is used to organize the schema of the database. The choice of a specific model determines the kind of database (e.g. hierarchical, object-oriented, relational, graph-based, document).
Relational Databases
Data is stored as tables. A table is a set of data elements (values) which are organized in rows and columns. Every column is identified by a name and every row is identified by several values composing a data record.
The entity type is specified by a set of attributes (defined by the column names). The specific entity is characterized by the attribute values. Every table in a database should have a column with values which uniquely identify the different data records. Single data records can be retrieved, updated and sorted.
Primary Keys
e.g. matriculation number, number of identity card. If no natural primary key is given, the use of consecutive numbers is common.
Foreign Key
A set of attributes in one table that uniquely identifies a row of another table.
Database Management Systems
DBMS is a collection of programs which enable the central efficient management of data. It is the interface between application program and physical data files. The DBMS separates the physical data (How is data structured and organized on physical storage media) structure from the logical structure (How is data represented to the user).
SQL (Structured Query Language)
SQL is the most popular computer language for relational databases. It is the industry standard.
Database Management System Components
A DBMS offers several components for the creation, manipulation and evaluation of data in a database:
- DDL (Data Definition Language):
- provides means to define the schema of a database (logical view) - DML (Data Manipulation Language):
- is used to process the data in the database and offers constructs to the user/ programmer to retrieve, insert, delete and update data in the database - DCL (Data Control Language):
- allows to control the access to data in the database
DDL
A data definition language (DDL) is a syntax similar to a computer programming language for defining data structures, especially database schemas (Statement: CREATE, ALTER, DROP).
What happens now with the foreign key in the course table, when we UPDATE or DELETE a row in the room table? (example for two connected table course -> room) (Slide 27). When we UPDATE/ DELETE a row in the room table, the foreign key in the course is:
- NO ACTION: The update/deletion is not allowed. The operation is rolled back.
- CASCADE: The foreign key is updated in the course table as well (for update) or the whole row in the course table is deleted as well (for delete).
- SET NULL: The foreign key in the course table is set to null.
- SET DEFAULT: The foreign key in the course table is set to a default value
DML
A data manipulation language (DL) is a family of syntax elements similar to a computer programming language used for inserting, deleting and updating data in a database. Performing read-only queries (e.g. SELECT) of data is sometimes also considered a component of DML. (Statement: INSERT, SELECT, UPDATE, DELETE)
Advantages of Using a DBMS
- Redundancy and inconsistency control
- Independence between application and data
- Provides a transaction mechanism
- Allows and manages the data access of multiple users
- Manages and enforces the access rights to data
Redundancy and Inconsistency Control
A specific entity should be stored once and be uniquely identified by the primary key. Additional information should be stored in a extra table and should be accessed via foreign key. The DBMS provides means to propagate changes to referencing elements in database.
