Relational Constructs   «Prev  Next»

Lesson 3 Relational Theory
Objective Entity relationship diagram as relational notation.

Representing Entities using Relational Notation

Question: Is representing entities with relational notation easier to understand than representing the same entities graphically?
The answer is that for some students relational notation will seem quite natural and for others it will take some getting used to. I will continue to represent entities using both styles, so if you prefer graphics to relational notation you will have a visual reference to make the explanations more understandable. All I ask is that you learn how to read relational notation. Doing so will allow you to study beyond this course and take advantage of resources that use relational notation to present their material. Relational databases play a critical role in many important computer applications. As is the case whenever enormous amounts of money are at stake, people have spent a huge amount of time and effort building, studying, and refining relational databases. Database researchers usually approach relational databases from one of three points of view.
  1. First Group: The first group approaches the problem from a database-theoretical point of view. These people tend to think in terms of provability, mathematical set theory, and propositional logic. Theorists use phrases such as
    1. relational algebra,
    2. Cartesian product, and
    3. tuple relational calculus.
    This approach is intellectually stimulating and can be a bit intimidating. These researchers focus on logical design and idealized database principles.
  2. Second Group: The second group approaches the topic from a less formal and practical point of view. Their terminology tends to be less precise and rigorous but more intuitive. They tend to use terms that you may have heard before such as table, row, and column. These people focus on physical database design and pay more attention to concrete bits-and-bytes issues dealing with actually building a database and getting the most out of it.
  3. Third Group: The third group tends to think in terms of flat files and the underlying disk structure used to hold data. Though these people are probably in the minority these days, their terms file, record, and field made their way into database nomenclature and have remained. Many of those who still use these terms are programmers and other developers who look at the database from a consumer's " How do I get my data point of view". These differing points of view have led to several different and potentially confusing ways to view relational databases. This can cause some serious confusion, particularly because the different groups have latched on to some of the same terms but used for different meanings. In fact, they sometimes use the term relation in very different ways .



Constraints and Analysis

In the preceding course Relational Database Design, you created a Entity Relationship diagram to track
  1. the products offered by and
  2. orders placed with Stories on CD, Inc.,
a fictional company that sells books on CD principally by mail order.
In this module you will use a modified version of that ER diagram[1] to derive the database objects used to represent CDs, distributors, and orders in the database. When you begin to design the structure of your database, you will represent entities in the database as relations. For example, a relation to store data about items stocked by Stories on CD, Inc. might be expressed this way in relational notation:

CD Column Entity
  1. CDNo
  2. CDTitle
  3. DistID

Relational Notation explained for RDBMS

Relational notation is a way of representing relationships between entities in a data model using mathematical symbols and operators. The purpose of relational notation during the data modeling process is to provide a clear and precise way of describing the relationships between entities, which helps to ensure that the resulting database is well-designed and can effectively meet the needs of the users. In a data model, entities represent objects, concepts, or events that are relevant to the organization or system being modeled. Relationships between entities describe how the entities are related to each other. For example, in a university data model, the entities might include students, courses, and instructors, and the relationships might include "enrolls in", "teaches", and "prerequisite for".
Relational notation uses mathematical symbols and operators such as sets, Cartesian products, and joins to represent the relationships between entities. For example, the notation "student ∩ enrolls in ∩ course" represents the set of all students who are currently enrolled in at least one course.
Using relational notation during the data modeling process helps to ensure that the relationships between entities are clearly defined and can be implemented effectively in a relational database management system. This can help to prevent data inconsistencies and improve the accuracy and efficiency of data retrieval and analysis.
For example, a relation to store data about items stocked by Stories on CD, Inc. might be expressed this way in relational notation:

Relational Database Management Systems

CD Entity to be transformed into table
CD Entity to be transformed into table

  1. CD: This is the entity
  2. Items in the parentheses: These are the attributes of the CD entity
For the scaling of values the distances should be relevant and not absolute.
One of the reasons you use data mining is to figure out targeting.

Relational Notation

Relational notation is a process of transforming an Entity Relationship Diagram into a more friendly and usable type of diagram that is easily readable. This can be done by taking the names of each table and its attributes and ordering them in a specific order. You always start with the primary key(s), which are commonly notated with the underscore, then all other attributes are added. The only rule for attributes is if it happens to be a foreign key it needs to be underscored with a dotted line.

Final Step in Database Modeling Evolution

The final step in the database modeling evolution is applications and how they affect a database model design. An application is a computer program with a user-friendly interface. The user interface could be an Android App communicating with Firebase or a web client which uses HTML, CSS and JavaScript to communicate with MySQL. The end-users use interfaces or screens to access data in a database. Different types of applications use a database in different ways and this can affect how a database model should be designed. Before you set off to figure out a design strategy, you must have a general idea of the kind of applications your database will serve. Different types of database models underpin different types of applications and you must understand where different types of database models apply. It is essential to understand that a well-organized design process is critical to success. In addition, a goal to drive the design process is equally as important as the design itself. There is no sense designing or even building an application and its corresponding database unless it is clear exactly what is you are doing to design.
Note the similarities between this relation and the CD entity in the project ER diagram.

CD entity with primary key CDNo
CD entity with primary key CDNo

Relational Data Modeling
Other elements of relational constructs include the data domain of each field, the primary key of each relation, and any foreign keys. You will learn about each of those elements later in this module. Translating an ER diagram into relational notation[2] is a common step in the logical design stage of the database life cycle.

Once you have created relational notations based on the entities in your ER diagram, you can analyze them with an eye toward:
  1. Ensuring that information is delivered in a consistent manner
  2. Eliminating data redundancy
  3. Preserving existing data when deleting unwanted data
It is possible to perform the analysis in this module and the next with the entities written as they are in the ER diagram, but representing the entities in relational notation makes the task easier in three important ways:
  1. Representing an entity in a text format is much more compact than representing the same entity graphically.
  2. Some of the concepts in this course, such as functional dependencies and transitive dependencies, are customarily written (and are easier to explain) using relational notation.
  3. Finally, if you choose to continue studying database design, you will almost certainly encounter relational notation in other courses or books.

Being able to read and write relational notation is a valuable skill. Relations are expressed in a shorthand called relational notation, a textual interpretation of the ER diagram. The next lesson outlines the characteristics of tables, which are the objects that store database data.
[1] ER diagram: (Entered in the glossary db under entity-relationship diagram.)
A diagram used during the design phase of database development to illustrate the organization of and relationships between data during database design
[2]Relational notation: Relations are expressed in a shorthand called relational notation, a textual interpretation of the ER diagram.

SEMrush Software