02bEntities

Database Management

Mick McQuaid
mcq@utexas.edu

University of Texas at Austin

12 May 2026

Entities

Databases keep track of entities - things in the real world that we want to store information about. These can be physical objects, like a person, a book, or a car, or events, like a sale or a meeting. Usually entities are named as nouns or gerunds (verb acting as a noun, usually ending in -ing). For example, a person is an entity, a sale is an entity, and a meeting is an entity.

In the previously displayed books table, the entity described by rows is a book. There are no rows in the table describing anything else.

The word entity is commonly used in two ways. It may mean the rows collectively, or it may used as a synonym for entity instance, which is a row. When we refer to a row as an entity, we really mean an entity instance.

Attributes

Entities have characteristics called attributes. These may be represented as nouns or as adjectives. The attributes themselves might be entities. If so, they are described in a separate table. For example, a book might have an author, a genre, and a publication date. The author, genre, and publication date are attributes of the book. The author is an entity, and might be described in a separate table. The genre is an attribute, but it might also be an entity if there are other attributes of genres that we want to store. The publication date is an attribute, and is not an entity.

Datatypes

Each attribute of an entity has a specific data type and no column in a table can have two or more data types. Specific implementations of databases accept different numbers and storage classes of data types. This can complicate matters considerably!

SQLite’s storage classes

SQLite, the database management system we’ll use, has 5 storage classes:

  • NULL
  • INTEGER
  • REAL
  • TEXT
  • BLOB

These storage classes almost correspond to data types.

The NULL storage class

The NULL storage class represents a NULL value. NULL is a special value that means “no value” or “unknown value”. It is not the same as zero, empty string, false, or the text “N/A”. NULL is a value that is not present. It is not exactly a data type, but a value that can be stored in any column. Note that this seems to violate the rule that a column can have only one data type. That’s why we call NULL a storage class rather than a data type.

The INTEGER storage class

The INTEGER storage class represents an integer value. It can be stored as a 1, 2, 3, 4, 5, 6, 7, or 8 byte signed integer. This is the most efficient storage class for integers. The default is an 8 byte signed integer.

The REAL storage class

The REAL storage class represents a floating-point value. It is stored as an 8-byte IEEE 754 floating-point number. This is the most efficient storage class for floating-point numbers.

The TEXT storage class

The TEXT storage class represents a text value. It is stored as a string of characters. The maximum length of a TEXT value is 1,000,000,000 characters.

The BLOB storage class

The BLOB storage class represents a binary large object. It is stored as a sequence of bytes. The maximum size of a BLOB is 1,000,000,000 bytes.

The Boolean data type

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

SQLite recognizes the keywords “TRUE” and “FALSE”, as of version 3.23.0 (2018-04-02) but those keywords are really just alternative spellings for the integer literals 1 and 0 respectively.

Date and time data types

SQLite does not have a separate date or time data type. Instead, dates and times are stored as TEXT, REAL, or INTEGER values. The TEXT format is ISO 8601 format, which is YYYY-MM-DD HH:MM:SS.SSS. The REAL format is Julian day numbers (day of the year). The INTEGER format is Unix time (number of seconds since 1970-01-01 00:00:00 UTC).

Type affinities

This is a concept that is needed so that SQLite can be compatible with other SQL databases. It is a way of specifying what type of data a column is likely to contain. It is not a hard requirement, but a suggestion to the database engine. The type affinities are:

  • TEXT
  • NUMERIC
  • INTEGER
  • REAL
  • BLOB

There is a table in this week’s reading that lists the type affinities.

Type affinities for integers

When you create a database, you do it with an SQL statement called CREATE. In SQLite, we can use the sqlite .open command to create the database file, then CREATE TABLE SQL statements to create the tables. Those CREATE TABLE statements may need to be compatible with other SQL databases, such as Oracle or PostgreSQL. If a CREATE TABLE statement contains any of the following keywords, the corresponding column will be assigned the INTEGER type affinity:

  • INT
  • INTEGER
  • TINYINT
  • SMALLINT
  • MEDIUMINT
  • BIGINT
  • UNSIGNED BIG INT
  • INT2
  • INT8

The rules for type affinity

The first rule for type affinity is as follows:

  1. If the declared type contains the string “INT”, the affinity is INTEGER.

All the examples in the preceding frame satisfy this rule.

The other rules can be found in the reading for this week, along with a table of examples.

END

Colophon

This slideshow was produced using quarto

Fonts are Roboto, Roboto Light, and Victor Mono Nerd Font