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 .

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:

Relational notation serves as a precise, standardized way to represent the structure of data during the data modeling process, especially when designing relational databases. Its primary purpose is to describe entities (tables), their attributes (columns), and relationships (associations) in a format that is compact, unambiguous, and implementation-ready.
🔍 Purpose of Relational Notation in Data Modeling:

1. Represents Entity Structure Clearly

   Relational notation specifies:

   • Entity names (which become tables)
   • Attributes (which become columns)
   • Primary keys (PK) – underlined or marked
   • Foreign keys (FK) – identified and associated with other entities

   Example:

   Customer(CustID, Name, Address)
   Order(OrderID, OrderDate, CustID)
   

   Here:

   • CustID is a primary key in Customer
   • CustID in Order is a foreign key referencing Customer
2. Documents Relationships

   It describes how tables relate through foreign key constraints. This is especially useful for 1:1, 1:M, or M:N relationships.

   1:M Example:

   Customer(CustID, Name)
   Order(OrderID, OrderDate, CustID)
   

   → One customer can place many orders (1:M).

3. Supports Logical to Physical Design

   Relational notation bridges the gap between:

   • Conceptual design (ERD) and
   • Physical schema (SQL tables)

   This allows DBAs and developers to generate CREATE TABLE scripts and constraints more systematically.

4. Simplifies Communication

   It provides a concise and standardized language that:

   • Data modelers
   • Database designers
   • Developers
   • Business analysts

   can all understand and use to review the schema.

5. Enables Validation and Normalization

   Relational notation makes it easier to:

   • Spot redundancies
   • Verify normal forms (1NF, 2NF, etc.)
   • Ensure data integrity

Function	Description
Structure Definition	Lists tables and their attributes
Key Identification	Marks primary and foreign keys
Relationship Mapping	Shows how tables relate to each other
Bridge to SQL	Provides foundation for CREATE TABLE
Validation Aid	Helps normalize and verify model correctness

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 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.

The difference between relational data modeling and conceptual modeling lies primarily in their level of abstraction, purpose, and artifacts produced. Here’s a clear and structured comparison:
🧠 1. Conceptual Modeling
✅ Definition:
Conceptual modeling is a high-level, technology-agnostic process that defines what the system should contain. It focuses on understanding and representing business requirements and real-world entities.
🎯 Purpose:

• Capture business concepts and rules.
• Define the entities, attributes, and relationships without worrying about how they’ll be implemented.
• Act as a communication bridge between business stakeholders and technical teams.

🧱 Artifacts:

• Entity-Relationship Diagrams (ERDs) (at a conceptual level)
• UML Class Diagrams (sometimes)
• Descriptions of:
  • Entities (e.g., Customer, Order)
  • Attributes (e.g., Name, Date)
  • Relationships (e.g., Places, Owns)

📌 Key Characteristics:

• No primary keys, foreign keys, or data types.
• Platform-independent.
• Emphasizes semantic clarity and business meaning.

🗄️ 2. Relational Data Modeling
✅ Definition:
Relational data modeling is a logical/technical activity that transforms the conceptual model into a format ready for implementation in a relational database system.
🎯 Purpose:

• Define how the data will be stored using the relational model.
• Focus on tables (relations), columns, keys, and constraints.
• Normalize data for efficiency, integrity, and non-redundancy.

Relational Schema Diagrams

SQL DDL Scripts

Definitions of:

• Tables (base tables)
• Columns with data types
• Primary and foreign keys
• Normalization levels

📌 Key Characteristics:

• Includes primary/foreign keys, constraints, and data types.
• Platform-aware (can be mapped directly to SQL).
• Based on relational algebra and database theory.
  • Relational algebra
  • Database theory

🧾 Comparison Table

Feature	Conceptual Modeling	Relational Data Modeling
Abstraction Level	High	Medium to low
Focus	Business concepts	Database structure
Artifacts	ERDs, entity and relationship specs	Tables, keys, normalized schema
Users	Business analysts, data architects	Database designers, developers
Keys/Constraints	Not specified	Fully defined (PK, FK, CHECK, etc.)
Technology Specific?	No	Yes (RDBMS-oriented)
Normalization	Not applicable	Highly relevant

• Conceptual:
  "A Customer places Orders. Each Order contains one or more Products."
• Relational:
  

    CREATE TABLE Customer (
      CustomerID INT PRIMARY KEY,
      Name VARCHAR(100)
    );
  
    CREATE TABLE Orders (
      OrderID INT PRIMARY KEY,
      CustomerID INT,
      FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
    );
    

Relational data modeling is a collection of processes and a set of techniques that records the inventory, shape, size, contents, and rules of data elements used in the scope of a business process to build a complete representation of that scope. Despite the name, a database is not just a collection of data. Rather it is a collection of facts, or in other words true propositions .
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. Learning how to read relational notation is essential to data modeling. Doing so will allow you to study beyond this course and take advantage of resources that use relational notation to present their material.

1. Domain: Determines the type of data values that are permitted for that attribute.
2. Primary key: A field (or combination of fields) that uniquely identifies a record in a table.
3. Foreign key: A field (or combination of fields) used to link tables; a corresponding primary key field occurs in the same database.
4. Data redundancy: Duplication of data in a database.

Conceptual modeling is a very important phase in designing a successful database application. Generally, the term database application refers to a particular database and the associated programs that implement the database queries and updates. For example, a BANK database application that keeps track of customer accounts would include programs that implement database updates corresponding to customer deposits and withdrawals. These programs provide user-friendly graphical user interfaces (GUIs) utilizing forms and menus for the end users of the application. Hence, a major part of the database application will require the design, implementation, and testing of these application programs. Traditionally, the design and testing of application programs has been considered to be part of software engineering rather than database design. In many software design tools, the database design methodologies and software engineering methodologies are intertwined since these activities are strongly related. In this module, we follow the traditional approach of concentrating on the database structures and constraints during conceptual database design. We present the modeling concepts of the Entity-Relationship (ER) model, which is a popular high-level conceptual data model. This model and its variations are frequently used for the conceptual design of database applications, and many database design tools employ its concepts. We describe the basic data-structuring concepts and constraints of the ER model and discuss their use in the design of conceptual schemas for database applications. We also present the diagrammatic notation associated with the ER model, known as ER diagrams.

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.