Database Systems: Concepts

Outline of Database

• Databases consolidate and manage data in an integrated manner, an alternative to file-based methods.

Difference Between Database and File

• There is a risk of overlapping data items and inconsistency with file-based methods because a file gets created for each program.
• Databases prevent data overlap and inconsistency through integrated management.
• Overlapping data items are recorded in multiple files in file-based systems.
• Data inconsistency occurs when modifications are not consistently updated across all files in file-based systems.

Required Functions for a Database

• Data sharing is identical data accessible to multiple users.
• Data independence ensures program unaffected by data modifications.
• Data maintenance keeps data accurate and consistent.
• Data fault countermeasures quickly deal with database faults.
• Data security protection is secured data by setting access rights.

Database Design

• Data needs to be analyzed to design the database.

Data Model

• Data model is modeling data relationships for computer handling and is classified by viewpoint.
• Conceptual data model is modeling without a specific database. It identifies data to be handled.
• Logical data model (external model) is modeling with a specific database and defines interrelations.
• Physical data model (internal model) is physically modeling for a specific database product and defines the internal structure.

Logical Data Models

• Hierarchical model represents data relations as a hierarchy (one-to-many relationship), implemented as HDB.
• Network model represents data as a hierarchical structure (many-to-many relationship), implemented as NDB.
• Relational model is a data model representing data in a two-dimensional table, implemented as RDB.
• Structured database is where the layout is logically recognized by the user, such as HDB or NDB.
• The relational Database (RDB) that implements the relational model is the focus.

Relational Model

• Relational model describes data in two-dimensional tables.
• Relation is the entire table.
• Tuples are rows in the table.
• Attributes (fields) are columns in the table.
• Occurrences (or instances) are values recorded in the table.
• Domain is a set of occurrences an attribute can take (includes data types and constraint conditions).
• Relation is a subset of the direct product of domains.
• Direct product is a set operation that obtains all combinations.
• Subset is a set included in a certain set.
• Relational databases utilize relational operations (relational algebra) and set operations based on set theory.

Relational Operations

• Selection extracts tuples that satisfy specified conditions.
• Projection extracts specified attributes (columns).
• Join combines multiple tables into one.
• Division extracts tuples identical to all tuples in another table.
• Union extracts all tuples from two tables, excluding overlaps.
• Difference extracts tuples excluding those belonging to another table.
• Product extracts tuples appearing commonly in two tables.
• Direct product combines all tuples in two tables.

Manipulations of Relational Databases

• Insert adds new data (tuples) into a relation (table).
• Update changes (updates) data (tuples) in a relation (table).
• Delete removes data (tuples) from a relation (table).

Conceptual Design of Databases

• Conceptual database design involves creating conceptual data models, which requires initial data analysis identifying data needs for the operations where the database will be used.
• Standardized data items are achieved through rules like naming conventions to prevent duplication.
• Metadata (names, meanings, attributes) is recorded in the data dictionary for the users in DD/D (Data Dictionary/Directory) to prevent synonyms or homonyms.
• The E-R model (E-R diagram) is commonly used for conceptual data models, representing content with entities and relationships.

Constituent Elements of E-R Model (E-R diagram)

• Entity is a managed object represented by a rectangle.
• Relationship is between entities, represented by a rhombus.
• Attribute is characteristics/properties of entities/relationships, represented by an ellipse.
• Cardinality represents the multiplicity of the relationship, and it defines the relationship as "one-to-one", "one-to-many", and "many-to-many".

Logical Design of Databases

• Logical database design creates logical data models, with consideration for implementation.
• Structured databases define files/records/fields (items).
• Relational databases define tables/tuples/attributes.
• Relational database table design decides on attributes for managing data items.
• Primary key and foreign key are considered for each table.

Primary Key

• Primary key is a field or combination that uniquely identifies each tuple (record).
• Composite key of multiple fields is acceptable.
• Primary key constraints include non-NULL (no null values) and unique (no duplication).

Foreign Key

• Foreign key is a field or combination referencing a tuple whose values match the primary key of another table.
• Referential constraint requires that the primary key of the referenced table must have a tuple with the same value.
• Non-NULL, unique, and referential constraints are called consistency constraints, ensuring data is correct.

Data Normalization

• Un-normalized form (UNF) is a general table design, needed before normalization.
• Data normalization designs efficient tables.
• Functional dependency is uniquely decided based on a certain attribute.

Data Normalization Procedure

• First Normalization: Eliminate repeating/derived fields, creates the first normal form table.
• Second Normalization: Split fields dependent on parts of the composite primary key into a separate table, creating a second normal form table. This dependency is Partial functional dependency.
• Third Normalization: Separate fields dependent on fields other than the primary key, creating the third normal form table. This dependency is Transitive functional dependency.
• Full functional dependency designs a table where all fields depend on the primary key by proceeding up to the third normalization.

Physical Design of Databases

• Physical database design creates physical data models considering what database software is being implemented.
• A data type is selected for each attribute and the memory format/mapping on the hard disk is reviewed.
• Disk capacity is estimated, and performance (processing efficiency or access efficiently) is evaluated.
• An index (search key) improves access efficiency.
• Indexing can significantly increase search efficiency when the database size is large for the attributes frequently used in search operations.
• Indexing requires disk capacity and updating of index attributes takes more time.

DBMS (DataBase Management System)

• Database Management System (DBMS) is software that manages databases for effective use.
• DataBase Administrator (DBA) is the person who manages the database using the database software.

Database Definition Function

• Database definition function defines the schema (database description concerning logical structure, storage structure, and physical structure).
• Three-schema architecture mostly defines schema.
• Conceptual schema (Schema) defines the logical structure/name of the overall database.
• External schema (Subschema) defines the database from the user's viewpoint.
• Internal schema (Storage schema) defines the physical structure (storage, organization).
• Data Definition Language (DDL) is the language used for Conceptual schema and External schema.
• Data Manipulation Language (DML) is the language used for External schema.
• Data Storage Definition Language (DSDL) is the language used for Internal schema.

Database Manipulation Function

• Database manipulation function offers an environment for the use of database language (e.g., SQL).
• Host language system using a high-level language uses methods of executing SQL statements, including embedded SQLs.
• Independent language system independently executes SQL statements through interactive SQL and command-driven methods.

Database Control Function

• Database control function improves the reliability and security.

Maintenance Function

• Maintenance function maintains data integrity.
• Double update results in loss of data integrity.
• Exclusive control resolves the problem with a Lock system, and increases the integrity of the data.
• Shared lock is when data is read, reading is permitted for other users.
• Exclusive lock is when data is updated, neither reading nor writing is permitted for other users.
• Deadlock occurs when multiple programs are waiting because of the lock.
• Semaphore system manages databases as resources by means of semaphore.

Security Protection Function

• Security provides data security, and protects critical and confidential data within the databases.
• Encryption encodes recorded content, even if database information is leaked to a third party.
• Access rights setting defines allowed processes for the database in advance.
• Password setting ensures to check if the database user has permission.
• Registration in journal file (log file) records database usage and checks for unauthorized use, working as a supplementary security measure.

Failure Recovery Function

• Failure recovery function restores databases after a failure occurrence.
• Backup file copies the database at a certain point.
• Journal file (log file) records update processes, created in memory and written to a file when the database update process for each transaction.
• A log is prepared by setting a checkpoint at certain time intervals and amount of processing, while the database being updated in the memory is recorded in the checkpoint file.

Recovery Processes

• Rollforward mainly used to recover from physical storage failures.
• Rollback mainly recovers from logical faults, it restores the database to pre-disabling process.
• Committed transactions after a checkpoint are recovered with rollforward.
• Non-committed processes are cancelled (rolled back) and re-executed.

Methods to Restart Databases

• Warm start restarts the database while maintaining the status of the memory, it restores the database to the just it was restarted using the checkpoint file or journal file.
• Cold start restarts the database after clearing all memory; the database is restored to the status just before it was restarted using the latest backup file and journal file.
• Reorganization optimizes the database which can be implemented when repetitive updates result in wasteful storage potentially decreasing access.

Transaction Management of Databases

• Transaction processing requires ACID characteristics.
• Atomicity completes by commit or rollback.
• Consistency is no contradiction/inconsistency of data before and after.
• Isolation occurs when multiple transactions do not interfere.
• Durability retains results after commit, continuously.

SQL (Structured Query Language)

• SQL is a database language that is used for relational databases.
• Data Definition Language (DDL) defines data.
• Data Manipulation Language (DML) operates data.

Data Definition

• Data definition refers to defining the database and table, in addition to view (virtual table).

Definition of Database

• CREATE DATABASE statement is used for defining a database.

Definition of Tables

• CREATE TABLE statement is used for defining a table.
• Column name and Data type are to be specified
• CHAR is the string characters of the specific length (1 byte).
• NCHAR is the string the characters specified length (2 byte).
• INT is the Integer(having precision by the processing system).
• DEC is the Numeric(having integer part and a decimal part).
• DATE is the strings of Year-Month-Date
• PRIMARY KEY is the declaration of primary key.
• FOREIGN KEY is the declaration of foreign key.
• NOT NULL defines null is not allowed.
• UNIQUE defines duplication of occurrence in the table is not allowed.
• CHECK specifies the conditions of occurrence.
• Column constraint is when it is simultaneously performed with column definition.
• Table constraint is when its performed at the end

Definition of View

• View is refers to the table that is virtually set from the real tables and corresponds to external schema and is also referred to as virtual table, and table is also referred to as base table.
• View can record attributes of a single table and new attributes that are created from multiple tables.
• CREATE VIEW statement is used for defining a view.

Definition of Access Right

• Access right is the right for each user to use a database.
• GRANT statement is used for defining access right. The access that can be defined is SELECT, INSERT, DELETE,UPDATE,REFERENCES.

Data Storage

• Data storage is the process of inserting data in a table.
• INSERT statement is used for storing data in units of tuples, also suitable for small amounts of data.

Data Manipulation

• Data reference is most used for data manipulation.
• SELECT statement is mainly used for referring to data.

Reference Without Specifying Conditions

• SELECT statement is used in reference without specifying conditions.
• If asterisk is specified as the column name, all columns are extracted.
• When DISTINCT is specified, a duplication is eliminated.

Reference With Specifying Conditions

• SELECT statement is used in reference with specifying conditions.
• Tuples that satisfy the conditions that are described in the WHERE clause are extracted.
• Comparison operators are the main operators and notations used in extraction conditions (=,<>,<,<=.>,>=).
• Logical operators are the main operators and notations used in extraction conditions (AND,OR,NOT).

Grouping of Data

• Grouping of data refers to handling the tuples where a certain attribute has the same value in a consolidated manner, and functions used for this are referred to as set functions (or aggregate functions).
• GROUP BY clause is used for grouping of data.
• Set functions (SUM,AVG,MIN,MAX and COUNT * , DISTINCT Column name).

Sorting of Data

• Sorting of data refers to rearranging the extraction results in the specified order of a particular attribute.
• ORDER BY clause is used for sorting data.
• Two types of sorting orders can be specified (ASC,DESC).
• A method of Joining Tables involves using JOIN specification in the FROM clause where achieving Cross Join, inner join can be implemented.

Sub Reference (Subquery)

• Sub reference refers to searching for another table on the basis of the search results of a certain table.
• Predicates include IN, ANY, ALL.

Other Methods of Using SQL

• INSERT, UPDATE, DELETE are other data manipulation statements, used to alter data (rows) of a table.
• Cursor is used when data is read to bridge data to host language system by fetching/transferring one row at a time.
• DECLARE, OPEN, FETCH, CLOSE are all basic commands to use a cursor method.

Various Databases

• Databases are applied in various areas and commonly utilized various systems.

Distributed Database

• Distributed database handles databases at multiple sites as a single database.
• Users can use a database without being aware of which database they are accessing, by using RDA (Remote Database Access) system.
• Two-phase commitment executes the update process into two phases .

Data Warehouse

• Data warehouse is a company-wide integrated system that expands the functions of databases and extracts decision-making information for business strategy and ETL (Extract/Transform/Load).
• Data mining analyzes data or regularity required for the management by using mathematical and statistical techniques such as OLAP.

Other Related Techniques

• RDSD registers and manages metadata, such as attribute, meaning, and storage location of data.
• Repository manages source code in data dictionary
• Commercial database is a database that contains independently collected information and that is offered to third parties on charge
• OODB(object orriented database)It is a database that is managed with objects where data and processing integrated . Users can process data by simply giving instructions.
• Multi media database It is a database that can handle information in addition to characters and numbers
• Hypertextdatabase-it is a structure database that manages hypertext used in the internet
• XML database-It is a database that can be used to manage the document of xml and it has the features of that.
• Data mapping refers to creating a table that correlates data between different applications.
• JDBC is a API used for accessing databases from Java, It allows the development of a very general porpuse program.