[Information processing article notes] Database construction

normal form

• Analysis is performed at the time of design.

Terminology

• Superkey: A set of one or more attributes that uniquely identifies each row, satisfying only uniqueness.
• Candidate key: A minimal set of attributes that can uniquely identify each row in a table, candidates that can become a primary key. Satisfies uniqueness and minimality at the same time
• Primary key: One of the candidate keys, an attribute that satisfies minimum and uniqueness, and cannot have NULL or duplicate values.
• Alternative keys: Candidate keys other than those designated as primary keys.
• Determinator: When x corresponds to Y, X is the determiner and Y is the dependent.

first normal form (1NF)

• All attribute values ​​of attributes belonging to a relation consist of atomic values ​​only.
• All domains belonging to table R consist of only atomic values.

Second Normal Form (2NF)

• When attributes that are not candidate keys are fully functionally dependent on the candidate key
• ? In table R, all non-key fields are functionally dependent on the key, and there is no partial dependence where a subset of the keys is the determinant.

Third Normal Form (3NF)

• Normal form without transitive functional dependencies
• When A->B, B->C, remove A->C relationship
• X->Y: Y is a function dependent on X

BCNF(Boyce and Codd Normal Form)

• ? Separate relations so that the determinant is always a candidate key
• ? The determinant in all functional dependencies that exist in table R is the candidate key.

Fifth Normal Form

• ? When all join dependencies of a relation R are satisfied only through the candidate keys of R
• ? A normal form that is satisfied only by removing join dependencies (JD) that do not go through candidate keys.

Problems that may arise during the normal form process

or higher (Anomaly)

• Unexpected difficulties occur when processing relational operations due to data duplication.

index

• It is a data structure created to help find data easily and quickly, like a document index or dictionary.
• The index attached to the table helps improve processing speed when retrieving data.
• ? The commands for adding and deleting indexes are CREATE and DELETE, respectively.
• In most databases, when you delete a table, the index is also deleted.

Serialization technique for concurrency control (parallel control)

timestamp technique

• A technique to predetermine the processing order between transactions

locking technique

• ? The larger the locking unit, the smaller the number of locks and the simpler the control technique. And the level of concurrency decreases.
• Type shared lock: read only
• exclusive lock: Both reading and writing are impossible, exclusive lock technique
• 2 phase locking: All transactions perform lock and unlock operations separately into an expansion phase and a contraction phase.

Validation Optimistic validation

• Batch inspection after transaction completion

MVCC multi-version concurrency control

• Compare transaction timestamps with timestamps of multiple versions of access data and select a version that guarantees serializability

database design

? Requirements analysis > Conceptual design (information modeling, conceptualization) > Logical design (data modeling) > Physical design (data structuring) > Implementation

1. Concept database design

• DBMS independent conceptual schema design, ? Transaction Modeling

transaction

• . A unit of work to perform one logical function in a database, or a series of operations that must be performed all at once

2. Logical database design

• ? Schema design suitable for target DBMS, transaction interface design, schema evaluation and refinement
• Components of a relational data model: relation, tuple (row), attribute (column), cardinality, degree, schema, instance.
• Cardinality: Number of tuples
• Degree: Number of attributes

relation

A database implemented centered on data relationships

• ? Every tuple has a different value. The same value may be stored, but interpreted as a separate value.

? The order of tuples in a relation is meaningless and is not guaranteed.

Each attribute has a unique name within the relation.

All attribute values ​​have atomic values.

view

• ? A view can create a new view based on another view.
• A view is a type of virtual table, and updates are subject to restrictions. Insert, update, and delete operations on configured content are subject to restrictions.

Views can be created using create view just like creating basic tables.

? Views are not physically implemented, unlike basic tables that exist logically.

Can have its own index.

Provides logical independence.

If you delete the base table or view on which a view is defined, other views defined based on that table or view are also automatically deleted.

Automatic security is provided through access control.

Integrity

• Maintaining data accuracy, consistency, and validity
• Objects of integrity regulations include domains, keys, dependencies, etc.
• Integrity regulations may include elements such as constraints that the data must satisfy and identifiers used when referencing the regulations.
• It refers to the accuracy of matching the data values ​​stored in the database and the actual values ​​of the real world they represent.
• Relation Integrity Rules specify the semantic relationship in the process of manipulating a relation.

Integrity constraints refer to constraints to prevent inaccurate data from being stored in the DB to ensure the accuracy of data contained in the DB.

Entity Integrity (Entity Integrity)? The primary key must not have a NULL value and must have only one value in the relation.

A primary key cannot have duplicate values.

Domain integrity (domain integrity) is a regulation that a given attribute value must belong to a defined domain.

Referential integrity The foreign key value must be null or the same as the primary key value of the reference relation.

The primary key of the table to be referenced by the foreign key must have the same domain and number of attributes. This means that you cannot enter something that does not exist.

custom integrity

관계대수와 관계해석

• Relational algebra is a procedural language, and relational analysis is a non-procedural language.
• Relationship interpretation symbol

operator symbol operator symbol operator symbol

SELECT	σ	UNION	∪	OR	∨
PROJECT	π	INTERSECTION	∩	AND	∧
JOIN	▷◁	DIFFERENCE	-	NOT	￢
DIVISION	÷	CARTESIAN PRODUCT	X	FOR ALL (uniform quantifier)	∀
THERE EXISTS	∃

• Relational algebra operations? SELECT, PROJECT, JOIN, DIVISION
• ? Union, intersection, difference, Cartesian product (multiplication operation) Cartesian product - think of it as a cross join.

3. Physical database design

• The process of converting a logical database expressed in a logical structure into data in a physical structure that can be stored on a physical storage device such as a disk.
• ? Response time, storage space efficiency, transaction throughput
• The goal of physical design is to store data in an efficient manner.
• ? Stored recordset The basic unit of physical database structure

Considerations when designing the storage record form

• ? Data type, distribution of data values, access frequency

Classification according to the functions of SQL

DDL(Data Definition Language)

• Data definition language, a language that determines the framework of the entire data, such as creating, modifying, and deleting data
• CREATE, ALTER, DROP, TRUNCATE CREATE TABLE ? Changes in attribute types are not applicable. Focus on definition and designation

DML(Data Manipulation Language)

• Data manipulation language, a language that searches, modifies, or deletes records entered into a defined database
• SELECT, INSERT, UPDATE, DELETE

DCL(Data Control Language)

• ? A language that accesses databases or grants permissions to objects.
• ? Defines the security and integrity of data.
• GRANT, REVOKE, COMMIT, ROLLBACK GRANT CREATE TABLE TO SOMEONE;
• ROLLBACK: Restores to the original state when a database manipulation task ends abnormally.

transaction

Nature of the transaction

Atomicity

• ? All operations in the transaction must be completed normally or be left in the original state where no operations have been performed at all.
• ALL OR NOTHING

Consistency

• Whenever a transaction successfully completes its execution, it is converted to a consistent database state.

Isolation

• When two or more transactions are executed in parallel at the same time, the operations of another transaction cannot intervene during the execution of one transaction.

Durability

• The results of successfully completed transactions must be permanently reflected even if the system fails.

Status of transaction

Active, Failed, Aborted, Partially Committed, Committed

• Partially Committed: A state immediately after the last operation of the transaction has been executed. All operations have been processed, but the final results of the transaction have not been reflected in the database.

Purpose of database parallel control

• ? Maximize system utilization
• Minimize response time to users
• ? Maximize database sharing
• Maintain database consistency

data model

• Operation: Processing values ​​actually expressed in the conceptual world or computer world according to the data structure.

relational data model

E-R models

Entity (square), Attribute (circle), Relationship (diamond)

• ? Multivalued properties are indicated with double circles.

hierarchical data model

network data model

distributed data system

• ? A distributed database logically belongs to one system, but is physically distributed across multiple computer sites.
• It aims at location transparency, overlapping transparency, parallel transparency, and disability transparency.
• The disadvantage is that the design of the database is relatively difficult, and development and processing costs increase.
• The main components of a distributed database system include distributed processors, distributed databases, and communication networks. ? P2P systems and single databases are not applicable.