Relationeel datamodel in DBMS: Concepts & Voorbeeld

Wat is het relationele model?

Relationeel model (RM) representeert de database als een verzameling relaties. Een relatie is niets anders dan een tabel met waarden. Elke rij in de tabel vertegenwoordigt een verzameling gerelateerde gegevenswaarden. Deze rijen in de tabel duiden een entiteit of relatie uit de echte wereld aan.

The table name and column names help interpret the meaning of the values in each row. The data are represented as a set of relations. In the relational model, data are stored as tables. However, the physical storage of the data is independent of the way the data are logically organized.

The model was proposed by E. F. Codd in 1970 and remains the foundation of almost every mainstream database in use today. Some popular relational database management systems are:

• DB2 en Informix dynamische server – IBM
• Oracle en RDB – Oracle
• SQL Server en toegang – Microsoft

Relationeel model Concepts in DBMS

1. Attribuut: Each column in a table. Attributes are the properties which define a relation, e.g., Student_Rollno, NAME, etc.
2. tabellen: In the relational model, relations are saved in the table format. It is stored along with its entities. A table has two properties, rows and columns. Rows represent records and columns represent attributes.
3. tupel: It is nothing but a single row of a table, which contains a single record.
4. Relatieschema: Een relatieschema vertegenwoordigt de naam van de relatie met zijn attributen.
5. Mate: The total number of attributes in the relation is called the degree of the relation.
6. Kardinaliteit: The total number of rows present in the table.
7. Kolom: De kolom vertegenwoordigt de reeks waarden voor een specifiek attribuut.
8. Relation instance: A relation instance is a finite set of tuples in the RDBMS system. Relation instances never have duplicate tuples.
9. Relatie sleutel: Every row has one, two, or multiple attributes, which is called the relation key.
10. Attribute domain: Every attribute has some pre-defined value and scope, which is known as the attribute domain.

With the vocabulary in place, the next concern is keeping the data in those relations valid, which is the job of integrity constraints.

relationele Integrity beperkingen

Relational integrity constraints in DBMS refer to conditions which must be present for a valid relation. These relational constraints are derived from the rules in the mini-world that the database represents.

There are many types of integrity constraints. Constraints on the relational database management system are mostly divided into three main categories:

1. Domeinbeperkingen
2. Belangrijkste beperkingen
3. referentieel Integrity beperkingen

Domeinbeperkingen

Domain constraints can be violated if an attribute value is not appearing in the corresponding domain, or if it is not of the appropriate data type.

Domain constraints specify that within each tuple the value of each attribute must be atomic and drawn from the correct domain. Domains are specified as data types, which include standard types such as integers, real numbers, characters, Booleans, and variable length strings.

Voorbeeld:

CREATE DOMAIN CustomerName
CHECK (value NOT NULL)

The example shown demonstrates creating a domain constraint such that CustomerName is not NULL.

Belangrijkste beperkingen

Een attribuut dat op unieke wijze een tupel in een relatie kan identificeren, wordt de sleutel van de tabel genoemd. De waarde van het attribuut voor verschillende tupels in de relatie moet uniek zijn.

Voorbeeld:

In the given table, CustomerID is a key attribute of the Customer table. It is most likely to have a single key for one customer; CustomerID = 1 is only for the CustomerName “Google”. A full treatment of the different key types is covered in the guide to DBMS-sleutels.

referentieel Integrity beperkingen

Referential integrity constraints in DBMS are based on the concept of foreign keys. A foreign key is an important attribute of a relation which should be referred to in other relations. A referential integrity constraint occurs where a relation refers to a key attribute of a different or the same relation. That key element must exist in the referenced table.

Voorbeeld:

In the above example, we have two relations, Customer and BillIng.

The tuple for CustomerID = 1 is referenced twice in the relation Billing. So we know CustomerName “Google” has a billing amount of $300.

Operations in the Relational Model

Four basic update operations are performed on the relational database model: insert, update, delete, and select.

• Insert is used to insert data into the relation.
• Verwijderen wordt gebruikt om tupels uit de tabel te verwijderen.
• Met Modify kunt u de waarden van sommige attributen in bestaande tupels wijzigen.
• Met Selecteren kunt u een specifiek gegevensbereik kiezen.

Whenever one of these operations is applied, the integrity constraints specified on the relational database schema must never be violated.

Invoegen Operatie

The insert operation gives values of the attributes for a new tuple which should be inserted into a relation.

bijwerken Operatie

You can see that in the relation table below, CustomerName ‘Apple’ is updated from Inactive to Active.

Verwijdering Operatie

Om verwijdering te specificeren, selecteert een voorwaarde op de attributen van de relatie de tuple die moet worden verwijderd.

In the above example, CustomerName “Apple” is deleted from the table.

The delete operation could violate referential integrity if the tuple that is deleted is referenced by foreign keys from other tuples in the same databank.

Selecteren Operatie

In the above example, CustomerName “Amazon" is geselecteerd.

Relational Model vs Hierarchical and Network Models

The relational model replaced two earlier approaches, and the contrast explains why it became dominant. The table below sets the three side by side.

Because the relational model addresses data by value rather than by navigating physical links, a high-level language like SQL can express a query without knowing how the data is stored.

Best Practices for Creating a Relational Model

• Data needs to be represented as a collection of relations.
• Each relation should be depicted clearly in the table.
• Rows should contain data about instances of an entity.
• Columns must contain data about attributes of the entity.
• Cells of the table should hold a single value.
• Each column should be given a unique name.
• No two rows can be identical.
• The values of an attribute should be from the same domain.

Advantages of the Relational Database Model

• Eenvoud: A relational data model in DBMS is simpler than the hierarchical and network models.
• Structural independence: The relational database is concerned only with data and not with structure, which can improve the performance of the model.
• Gemakkelijk te gebruiken: The relational model is easy to use, as tables consisting of rows and columns are natural and simple to understand.
• Query capability: It makes it possible for a high-level query language like SQL to avoid complex database navigation.
• Gegevensonafhankelijkheid: The structure of a relational database can be changed without having to change any application.
• schaalbaar: With regard to the number of records or rows and the number of fields, a database can be enlarged to enhance its usability.

Disadvantages of the Relational Model

• Few relational databases have limits on field lengths, which cannot be exceeded.
• Relational databases can sometimes become complex as the amount of data grows and the relations between pieces of data become more complicated.
• Complex relational database systems may lead to isolated databases where information cannot be shared from one system to another.

Veelgestelde vragen