7,540
edits
Changes
→Bevezető
:[[File:ClipCapIt-180930-152752.PNG]]
=Fontos fogalmak=
==Segéd fogalmak==
===Referential integrity===
Referential integrity is a property of data stating references within it are valid. In the context of relational databases, it requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute (column) in a different (or the same) relation (table).[1]
For '''referential integrity to hold''' in a relational database, any column in a base table that is declared a foreign key can contain either a null value, or only values from a parent table's primary key or a candidate key. In other words, when a foreign key value is used it must reference a valid, existing primary key in the parent table. For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity.
===ACID===
Atomicity, Consistency, Isolation, Durability
====Atomicity (database systems)====
In database systems, atomicity is one of the ACID (Atomicity, Consistency, Isolation, Durability) transaction properties. An atomic transaction is an indivisible and irreducible series of database operations such that either all occur, or nothing occurs.[1] A guarantee of atomicity prevents updates to the database occurring only partially, which can cause greater problems than rejecting the whole series outright
====Consistency====
Consistency in database systems refers to the requirement that any given database transaction must change affected data only in allowed ways. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.
====Isolation====
Isolation is typically defined at database level as a property that defines how/when the changes made by one operation become visible to other.
===Durability===
In database systems, durability is the ACID property which guarantees that transactions that have committed will survive permanently.
==Normal forms==
Codd introduced the concept of normalization and what is now known as the first normal form (1NF) in 1970.[4] Codd went on to define the second normal form (2NF) and third normal form (3NF) in 1971,[5] and Codd and Raymond F. Boyce defined the Boyce-Codd normal form (BCNF) in 1974.[6]
Informally, a relational database relation is often described as "normalized" if it meets third normal form.[7] Most 3NF relations are free of insertion, update, and deletion anomalies.
===First normal form===
First normal form is an essential property of a relation in a relational database. Database normalization is the process of representing a database in terms of relations in standard normal forms, where first normal is a minimal requirement.
First normal form enforces these criteria:
* Eliminate repeating groups in individual tables.
* Create a separate table for each set of related data.
* Identify each set of related data with a primary key
===Second normal form===
A relation that is in first normal form (1NF) must meet additional criteria if it is to qualify for second normal form. Specifically: a relation is in 2NF if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key of the relation. A non-prime attribute of a relation is an attribute that is not a part of any candidate key of the relation.
Put simply, a relation is in 2NF if it is in 1NF and every non-prime attribute of the relation is dependent on the whole of every candidate key.
===Third normal form===
Third normal form (3NF) is a normal form that is used in normalizing a database design to reduce the duplication of data and ensure referential integrity by ensuring that:-
# the entity is in second normal form
# all the attributes in a table are determined only by the candidate keys of that relation and not by any non-prime attributes.
3NF was designed to improve database processing while minimizing storage costs.
An example of a 2NF table that fails to meet the requirements of 3NF is:
{| class="wikitable"
|+ Tournament Winners
! <u>Tournament</u> !! <u>Year</u> !! Winner !! Winner Date of Birth
|-
|Indiana Invitational||1998||Al Fredrickson||21 July 1975
|-
|Cleveland Open||1999||Bob Albertson||28 September 1968
|-
|Des Moines Masters||1999||Al Fredrickson||21 July 1975
|-
|Indiana Invitational||1999||Chip Masterson||14 March 1977
|}
Because each row in the table needs to tell us who won a particular Tournament in a particular Year, the composite key {Tournament, Year} is a minimal set of attributes guaranteed to uniquely identify a row. That is, {Tournament, Year} is a candidate key for the table.
The breach of 3NF occurs because the non-prime attribute Winner Date of Birth is transitively dependent on the candidate key {Tournament, Year} via the non-prime attribute Winner. The fact that Winner Date of Birth is functionally dependent on Winner makes the table vulnerable to logical inconsistencies, as there is nothing to stop the same person from being shown with different dates of birth on different records.
In order to express the same facts without violating 3NF, it is necessary to split the table into two:
{|
| valign="top" |
{| class="wikitable"
|+ Tournament Winners
! <u>Tournament</u> !! <u>Year</u> !! Winner
|-
|Indiana Invitational||1998||Al Fredrickson
|-
|Cleveland Open||1999||Bob Albertson
|-
|Des Moines Masters||1999||Al Fredrickson
|-
|Indiana Invitational||1999||Chip Masterson
|}
| valign="top" |
{| class="wikitable"
|+ Winner Dates of Birth
! <u>Winner</u> !! Date of Birth
|-
|Chip Masterson||14 March 1977
|-
|Al Fredrickson||21 July 1975
|-
|Bob Albertson||28 September 1968
|}
|}
Update anomalies cannot occur in these tables, because unlike before, '''Winner''' is now a primary key in the second table, thus allowing only one value for '''Date of Birth''' for each '''Winner'''.
==Database Normalization==
Database normalization is the process of restructuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity. It was first proposed by Edgar F. Codd as an integral part of his relational model.
==Denormalization==
=Bevezető=
