Database Schema Design: Relational Model Essentials

Posted on Mar 11, 2025 in Commercialization and Logistics

Conceptual Diagram

Representation of information resources regardless of system users or particular applications and without regard to efficiency issues. It is normally supported by the E/R model.

Logical Schema Global/External

Transformation of the conceptual framework adapted to the data model which supports the DBMS to be used, obtaining a Global Logical Data Schema. The most common is the relational model.

Internal or Physical Schema

Instrumentation as efficiently as possible the logical schema in terms of process requirements, the specific terms of the DBMS to use, and the configuration and characteristics of the hardware and OS.

RELATIONAL MODEL STRUCTURE

The number of attributes is called Grade. The number of rows is called Cardinality.

Representation of a Table or Relationship

Can be expressed by extension (tabular attributes and all rows in the table), or by intension (more common) types of keys:

  • Candidate: The set of attributes that uniquely identifies each row. There is always at least one candidate key.
  • Primary: The candidate key selected to identify the rows of the relationship. A primary key is composite if it consists of more than one attribute.
  • Alternative: Each of the candidates not selected as primary.
  • Foreign: A set of attributes whose values have to match the primary key in another table or relationship. It must have the same domain as the primary key from which it comes. It is used to relate tables. No attribute that is part of the primary key can take a null value.

Restrictions

Restrictions are not permitted structures or occurrences. They can be:

YOUR MODEL (INHERENT)

These are restrictions imposed by the relational model itself. They are as follows:

  • There cannot be two rows (tuples) equal in the table (COMPULSORY PRIMARY KEY (Not Null) and Single (Unique)).
  • The order of rows is not significant.
  • The order of attributes is not relevant.
  • Each attribute can take a single value of the domain (homogeneous set of values) on which it is defined, i.e., there can be no repeating groups. As we shall see, if a board meets this condition, it is said to be in 1st Normal Form.
  • No attribute that is part of the primary key can take null values (Integrity Rule Entity)
ESTABLISHED BY USER (semantics)
  • All the foreign key values must match a primary key value that it refers to or be null (referential integrity).

We also define for each foreign key that will impact operations Erase (Delete) and Update (Update). These two options must be specified separately. We can determine:

  • CASCADA Operation (On Cascade). When you update or delete a primary key value, the corresponding foreign key rows are automatically deleted or updated.
  • Operation Restricted (No Action). Only allowed to delete or modify a key value in the related table if there are no rows with that value in the foreign key.
  • Operation Posted Null (Set Null). If you delete or modify a primary key value referenced, null values are set for foreign keys (Provided it is specified that the foreign key allows null values.)
  • Operation setting to defaults (Set Default). The deletion or modification of a referenced key value leads to a default value in the foreign key.

The user can express constraints through a set of conditions applied to one or more attributes. For example, in SQL, these restrictions can include:

  • Check (Verification). When you perform an update operation, the system checks if the given condition is met. Otherwise, the operation is rejected.
  • Assertion (Assertion). When the condition affects more than one attribute.

Some DBMS (Oracle for example) support other restrictions called triggers (Trigger), in which the user specifies a response or action in a given condition.