Relational Constructs   «Prev  Next»

Lesson 6 Rules for Database Table Rows
Objective List the rules for table rows.

Rules for Database Table Rows

As with columns, relational databases have rules governing table rows:
  1. Each column in a row can contain only one value.
  2. There can be no duplicate rows: each instance of the entity described by the table cannot be represented more than once in the table.
  3. Each row has a primary key, which is a column, or set of columns whose values uniquely identify the row in the table.

Database design requires that every table in a relationship have a primary key. This is the reason why: Consider a table that lists customers for Stories on CD, Inc. If the same customer identification number were listed twice, orders from that customer could be linked to either instance of the customer ID. That would make it impossible to find every order placed by the customer if you searched based on only one of the customer's identification numbers.

Rows can be thought of as Representations of Objects

In relational databases, "rows" can be thought of as representations of objects, but they are not considered objects in the traditional sense as in object-oriented programming (OOP). Here’s a breakdown of how rows relate to data objects and the differences between relational database structures and "actual" data objects:
  1. How Database Rows Represent Objects
    • Database Rows: In a relational database, each row in a table typically represents a single instance of an entity or object. For example, in a Users table, each row could represent a user, with columns storing attributes like name, email, and age.
    • Objects in OOP: In an object-oriented language like Java or C++, an object is an instance of a class that has properties (attributes) and behaviors (methods). For example, a User class may have properties for name and email, along with methods to update or validate user data.
  2. Differences Between Database Structures and Data Objects
    1. Structure vs. Behavior:
      • Tables, Rows, and Columns: In a relational database, tables define the structure of data, with columns representing attributes and rows representing records. These structures do not have behavior; they only hold data.
      • Objects: Data objects in OOP encapsulate both data (attributes) and behavior (methods). This encapsulation allows objects to perform operations on their data, such as calculating values or updating attributes.
    2. Data Representation:
      • Tables/Rows/Columns: These are strictly for data storage and are defined using a schema that enforces rules for data consistency. The relationships between data are established through foreign keys and other constraints.
      • Objects: Objects are designed to model real-world entities more intuitively, with relationships between objects managed using references or pointers. Objects can have complex hierarchies, inheritance, and polymorphic behavior, which databases do not inherently provide.
    3. Data Relationships:
      • Relational Databases: Relationships are established using keys (primary keys, foreign keys) and are limited to predefined structures. You use SQL queries to traverse and manipulate these relationships.
      • OOP Objects: Relationships between objects can be more dynamic and flexible. Objects can interact with each other directly, and their interactions are defined through methods and class hierarchies.
  3. Example Comparison
    • Database Representation:
      • Table: Products
      • Columns: ProductID, Name, Price, CategoryID
      • Row: An individual product like 101, "Laptop", 1200.00, 5
    • OOP Representation:
      • Class: Product
      • Attributes: productID, name, price, category
      • Methods: applyDiscount(), getCategoryName()
  4. When Are Rows Considered Objects?

    In object-relational mapping (ORM) frameworks like Hibernate (Java) or Entity Framework (.NET), rows are often mapped to objects. These frameworks automatically convert database rows into objects and handle the CRUD (Create, Read, Update, Delete) operations, making the relational data more accessible in an object-oriented way.


|Summary
  • Rows in a relational database are structured data representations and do not have behavior.
  • Objects in OOP encapsulate both data and behavior, providing a more complete and flexible way to model real-world entities.
  • The relational model focuses on the efficient and consistent storage of data, while OOP focuses on representing real-world entities with both attributes and behaviors.


Relational Database is a Collection of Tables

You can think of a relational database as a collection of tables, each containing rows and columns. At this level, it looks a lot like a workbook containing several worksheets (or spreadsheets), although a worksheet is much less constrained than a database table is. You can put just about anything in any cell in a worksheet. In contrast, every entry in a particular column of a table is expected to contain the same kind of data. For example, all of the fields in a particular column should contain the same data type. A poorly designed database application may allow the user to introduce some irrelevant kinds of data into other fields. For example, if the database and user interface are not designed properly, you might be able to enter a string such as none in a telephone number field. Where in reality, the field for this column may be a mandatory attribute.
In contrast, the cells of a spreadsheet do not really care what you put in them. The set of values that are allowed for a column is called the domain [1] of the column. For example, a column's domain might be telephone numbers, bank account numbers, shoe sizes, or lamp colors. Domain is closely related to data type but it is not quite the same. A column's data type is the kind of data that the column can hold. The data types that you can use for a column depend on the particular database you are using but typical data types include
  1. integer,
  2. floating point number (a number with a decimal point),
  3. string, and
  4. date.
To see the difference between domain and data type, note that street address (345 Relational Road) and jersey color (red) are both strings. However, the domain for the street address column is a valid physical street address, whereas the domain for the jersey color column is colors (and all colors may not be available if you only allow a few choices). You can think of the data type as the highest level or most general possible domain. For example, an address or color domain is a more restrictive subset of the domain allowing all strings.) The rows in a table correspond to column values that are related to each other according to the purpose of the table. Some relational database management systems will not enforce the no-duplicate-rows rule by default. Double-check your system if it does not activate it manually.
The next lesson describes column domains.

Entities Attributes Exercise

Before moving on to the next lesson, click the link below to read about entities and attributes from the course ER diagram.
Entities Attributes Exercise

[1]domain: The domain determines the type of data values that are permitted for that attribute.

SEMrush Software