Root: /* 1 to 1 relationships (1:1) */

2026-02-17T07:22:16Z

1 to 1 relationships (1:1)

← Older revision		Revision as of 08:22, 17 February 2026
Line 85:		Line 85:
	=== 1 to 1 relationships (1:1) ===		=== 1 to 1 relationships (1:1) ===
	Each instance of entity A is related to at most one instance of entity B, and vice versa.		Each instance of entity A is related to at most one instance of entity B, and vice versa.
	[[File:Database 1 to 1.png\|none\|thumb]]		[[File:Database 1 to 1.png\|none\|thumb]]To create a unique relationship between entities, we can make use of SQL with the UNIQUE column constraint.

	=== 1 to N relationships (1:N) ===		=== 1 to N relationships (1:N) ===

Root: /* 1 to 1 relationships (1:1) */

2026-02-17T06:31:03Z

1 to 1 relationships (1:1)

← Older revision		Revision as of 07:31, 17 February 2026
Line 85:		Line 85:
	=== 1 to 1 relationships (1:1) ===		=== 1 to 1 relationships (1:1) ===
	Each instance of entity A is related to at most one instance of entity B, and vice versa.		Each instance of entity A is related to at most one instance of entity B, and vice versa.
			[[File:Database 1 to 1.png\|none\|thumb]]

	=== 1 to N relationships (1:N) ===		=== 1 to N relationships (1:N) ===

Root at 12:48, 14 January 2026

2026-01-14T12:48:42Z

Show changes

Root at 12:42, 14 January 2026

2026-01-14T12:42:22Z

← Older revision		Revision as of 13:42, 14 January 2026
Line 286:		Line 286:
	* Third Normal Form (3NF): Is in 2NF and no non-prime attribute is transitively dependent on the primary key.		* Third Normal Form (3NF): Is in 2NF and no non-prime attribute is transitively dependent on the primary key.
	* Boyce-Codd Normal Form (BCNF): A stronger version of 3NF where every determinant is a candidate key.		* Boyce-Codd Normal Form (BCNF): A stronger version of 3NF where every determinant is a candidate key.
			[[Category:IT Databases]]

Root: Created page with "= Introductory Concepts = Where data comes from: * Real world: This is the part of reality that interests us. It consists of concrete objects. * Logical or conceptual world (knowledge or information): An abstract model of the real world. * Representative world (data): The transition from the abstract model of information to a computer representation. == The Real World == From the real world, we must extract abstractions of what interests us. == The Conceptual World:..."

2026-01-14T12:36:47Z

Created page with "= Introductory Concepts = Where data comes from: * Real world: This is the part of reality that interests us. It consists of concrete objects. * Logical or conceptual world (knowledge or information): An abstract model of the real world. * Representative world (data): The transition from the abstract model of information to a computer representation. == The Real World == From the real world, we must extract abstractions of what interests us. == The Conceptual World:..."

New page

= Introductory Concepts =

Where data comes from:

* Real world: This is the part of reality that interests us. It consists of concrete objects.
* Logical or conceptual world (knowledge or information): An abstract model of the real world.
* Representative world (data): The transition from the abstract model of information to a computer representation.

== The Real World ==

From the real world, we must extract abstractions of what interests us.

== The Conceptual World: Entities, Attributes, and Values ==

All information is characterized by three elements: entity, attribute, and value.

'''Entity''': A distinguishable object from others, whose properties interest us. It is the object of information.

'''Attribute''': The properties that describe the object.

'''Value''': The data itself that characterizes the attribute.

'''Domain''': The set of all valid values an attribute can have.

'''Null''': Determines an unknown value.

'''Key''': An attribute or set of attributes that allows individual entities to be identified.

== The World of Representations ==

The computer representation of an elementary piece of information is called data.

To represent data, a tabular representation is usually used, where each row represents an individual entity and each column identifies an attribute of the entity.

'''Register/Record''': The representation of an entity, equivalent to a row in the table.

'''Field''': The representation of an attribute of an entity.

The set of fields forms the record, and the set of records forms the file or table.

= Conceptual Design: The Entity-Relationship (ER) Model =

The entity-relationship data model is used to model data for use in a database. ER models are directly translatable to the relational model. These models are represented using ER diagrams.

ER modeling is based on three concepts:

* Entities: Real-world objects of which we want to model some properties, and which are distinguishable from other objects.
* Relationships: Associations and interactions between entities.
* Attributes: Properties of interest of entities and some relationships.

== Entities and Attributes ==

In an ER diagram, entity types are represented, defining a collection of similar entities. An entity type is represented with its name in capital letters inside a box.
[[File:Db entites attributes.png|none|thumb]]

== Keys ==

=== Candidate Key ===
A set of attributes that uniquely identifies an entity.

=== Simple and Composite Keys ===
A simple key consists of a single attribute. A composite key consists of two or more attributes.

=== Primary Key ===
The candidate key chosen by the database designer to uniquely identify entities.

=== Alternative Key ===
Candidate keys that were not chosen as the primary key.

=== Synthetic Keys ===
An artificial key created when no natural candidate key exists or when they are too complex.

== Relationships ==

=== Degree of Relationships ===
Refers to the number of entities participating in a relationship.
[[File:Db relations degree.png|none|thumb]]

=== Attributes in Relationships ===
Relationships can also have attributes that describe the association.
[[File:Database relations attributes.png|none|thumb]]

== Connectivity of Binary Relationships ==

=== 1 to 1 Relationships (1:1) ===
Each instance of entity A is related to at most one instance of entity B, and vice versa.

=== 1 to N Relationships (1:N) ===
One instance of entity A can be related to many instances of entity B, but B is related to only one of A.
[[File:Database relations 1 N.png|none|thumb]]

=== N to M Relationships (N:M) ===
Instances of entity A can be related to many instances of entity B, and vice versa.
[[File:Database relations n m.png|none|thumb]]

== Minimum Connectivities ==
Specifies the minimum number of times an entity instance can participate in a relationship (usually 0 or 1).
[[File:Database minimum connectivities.png|none|thumb]]

== Recursive Relationships ==
When an entity is related to itself.
[[File:Database recursive relationship.png|none|thumb]]

== Ternary Relationships ==
Relationships involving three different entities.
[[File:Database ternary relationships.png|none|thumb]]

== Connectivity of Ternary Relationships ==
Defines the mapping constraints (1:1:1, 1:1:N, 1:N:M, N:M:P) within a three-way association.
[[File:Database connectivity ternary relationships.png|none|thumb]]

== Types of Entities ==

=== Strong and Weak Entities ===
Strong entities exist independently. Weak entities depend on a "parent" entity for their existence and identification.

== Generalization/Specialization ==
A hierarchy where a "supertype" entity shares common attributes with "subtype" entities.

== Associative Entities ==
A relationship that is treated as an entity so it can participate in other relationships.

= The Relational Model =

== Fundamental Concepts in the Relational Model ==

=== Domain ===
The set of valid values for an attribute.

=== Registries ===
The set of rows in a table.

=== Degree ===
The degree is the number of attributes in a table.

== Properties of a Table ==

* It has a unique name within the database.
* No duplicate rows.
* Column entries are atomic.
* Column entries are from the same domain.
* Operations combining different data types are not allowed.
* Each attribute has a unique name.
* The order of columns is insignificant.
* The order of rows is insignificant.

=== Terminology ===

Terminology table (Term | Alt 1 | Alt 2):
* Relation | Table | File
* Tuple | Row | Record
* Attribute | Column | Field

= Integrity Rules and Constraints =

Constraints allow a designer to specify the semantics of data. They are rules that force the DBMS to check that data satisfies the semantics.

== Domain Integrity ==
The domain restricts the values of attributes in the relation.

=== Entity Integrity ===
Requires that every table has a primary key. Neither the PK nor any of its parts can contain null values.

=== Referential Integrity ===
Requires that a foreign key must either have a corresponding primary key or be null. It prevents "orphan records" (FK pointing to a non-existent PK).

Note: When configuring referential integrity, the PK and FK must have the same data type and come from the same domain.

=== Rules for Foreign Keys ===
Additional rules for when a parent record is modified or deleted:
* Cascade Update: Propagates changes to child records.
* Cascade Delete: Deletes related child records.

== Business Rules ==
Additional rules specified by users or administrators.

=== Cardinality and Connectivity ===
Constraints based on the number of related entities.

=== Types of Relationships ===
Specific business logic defining how entities interact.

=== Optional and Mandatory Relationships ===
Determines if an entity must or may participate in a relationship.

= Translating the ER Model to the Relational Model =

== 1 to 1 Relationships ==
The primary key of one side is placed as a foreign key (FK) in the other.

== 1 to N Relationships ==
The primary key from the "1" side is passed to the "many" (N) side as a foreign key.

== N to M Relationships ==
A new table is created containing the primary keys of both entities as a composite primary key.

== Weak Entities ==
The primary key of the weak entity is composed of the primary key of the strong entity plus a discriminator.

== Recursive Relationships ==
The primary key is mapped as a foreign key within the same table, using a different name.

== Ternary Relationships ==

=== Example of 1-1-1 Ternary Relationship ===
Handled by merging keys into one of the entities or creating a junction table depending on specific constraints.

=== Example of N-M-1 Ternary Relationship ===
The PKs of the "N" and "M" sides are usually moved to a new table along with the "1" side's PK.

=== Example of N-M-P Ternary Relationship ===
A new table is created with the PKs of all three entities.

=== Example of Transformation of a Hierarchical Relationship ===
Conversion of sub-types into either separate tables or a single table with a type indicator.

== Issues in ER to Relational Transformation ==

=== The Deadlock (Circular Reference) ===
Occurs when Table A references Table B, and Table B references Table A.

Solution 1: Create Table A first without the FK. Create Table B with the FK to A. Then add the FK constraint to Table A.
Example:
DEPARTMENTS(Id, ..., idEmployee)
EMPLOYEES(IdEmployee, ..., idDepartment) where idDepartment references DEPARTMENTS.

Solution 2: Create a third table (Table C) to manage the relationship so that the initial tables do not reference each other directly.
Example:
DEPARTMENTS(Id, ...)
EMPLOYEES(IdEmployee, ..., idDepartment)
MANAGED_BY(idDepartment, idEmployee)

The second solution is generally better for avoiding insertion problems.

== Summary of ER to Relational Transformations ==

Transformation mappings (ER Model -> Relational Model):
* Entity -> Table
* Attributes -> Columns
* Weak Entity -> New table with owner's PK
* Attributes -> Same fields
* Primary Key -> Primary Key
* 1:1 Relationship -> Pass the key to one side as FK
* 1:N Relationship -> Pass the key of the "1" side to the "many" side as FK
* N:N or Non-binary -> New table
* N-ary Relationship -> New table

= ER Modeling =

== Relational Design and Redundancy ==
Redundancy is normally undesirable. Poor designs lead to:
* Information repetition (Redundancy).
* Inability to represent information.
* Insertion Anomaly: Inconsistent information inserted.
* Update Anomaly: Incorrect existing information changes.
* Deletion Anomaly: Loss of unintended data when a record is deleted.

A good design must:
* Store necessary info with minimum redundancy.
* Maintain minimum links between relations for ease of use.
* Improve query performance.
* Minimize update problems.

This is achieved through Normalization and understanding Functional Dependencies (FD).Normalization

The process of determining how much redundancy exists in a table.

=== Normal Forms ===
The primary key of a relation always functionally determines the rest of the attributes.

Intention: What we want to store (attributes).
Extension: The set of values (records).

Functional Dependency (FD): A constraint {X} → {Y} where the value of X uniquely determines the value of Y.
Full Functional Dependency: When no subset of {X} can determine {Y}.

=== Design Anomalies ===
Modification Anomalies: Forcing changes in all tuples containing a fact.
Deletion Anomalies: Losing elementary facts unintentionally.
Insertion Anomalies: Being unable to record a fact because other data is missing.

=== Theory of Normalization ===

* First Normal Form (1NF): All attributes are atomic (no repeating groups).
* Second Normal Form (2NF): Is in 1NF and every non-prime attribute is fully functionally dependent on the primary key.
* Third Normal Form (3NF): Is in 2NF and no non-prime attribute is transitively dependent on the primary key.
* Boyce-Codd Normal Form (BCNF): A stronger version of 3NF where every determinant is a candidate key.

ER and relational model - Revision history

Root: /* 1 to 1 relationships (1:1) */

Root: /* 1 to 1 relationships (1:1) */

Root at 12:48, 14 January 2026

Root at 12:42, 14 January 2026