Featured image

Table of Contents Link to heading

General Terminology Link to heading

Relational NameCommon NameAlternative
relationtable-
attributecolumnfield
tuplerowrecord

Within a table, every column name must be UNIQUE.

Within a Table every row must be UNIQUE (no duplicate data!).

Every Row must have a unique Primary Key that can identify that data row only!

Relations Link to heading

A RELATION defines a real world or conceptual object we collect information about.

When a relation is implemented in a Database Management System (DBMS), it is often called a table.

A relational database (model) consists of a series of relations with distinct names.

  • It is a convention to name relations using the PascalCase.

For example, a School relation stores lecturerInfo, studentID, courses.

Attributes Link to heading

An ATTRIBUTE is a property that describes a relation.

Each attribute must have a unique name in a given relational table.

  • It is a convention to name attributes using the camelCase.

For example, the School table (relation) has columns (attributes) for collecting the Lecturer’s information, Student ID, and Courses.

Every attribute has a domain.

Types of Attributes Link to heading

There are five types of attribute:

  1. Simple attributes are atomic values which cannot be divided further.
  • They provide a single piece of useful information and not consist of subparts or multiple values or repeating information.
  • E.g. A person’s phone number is an atomic value of 10 digits.
  1. Composite attributes are made of more than one simple attribute.
  • E.g. A person’s complete name may have firstName and lastName attributes.
  1. Derived attributes are those whose values are calculated from the values of other attributes.
PurchaseOrder
quantity
price
/total
  • total = quantity * price
  • Thus, total is a Derived Attribute.
  1. Structured attributes are those composed of more than one attribute.
Employee
name
salutation
firstName
lastName
address:
addressLine1
addressLine2
  • The name attribute consists of salutation + firstName + lastName.
  • Thus name is a Structured Attribute.
  1. Single-valued attributes are those which simply contain a single value.
  • E.g. taxFileNumber, socialSecurityNumber, etc.
  1. Multivalued attributes are those which contain more than one values.
  • E.g. A person can have more than one phoneNumber, emailAddress, etc.
  • It violates basic relational theory (single-valued attributes).
  • To resolve a multivalued attribute, place it in a separate table and associate it with the original table.

For example:

Multivaluedattribute

Domains Link to heading

A Domain dictates (determines) the data type and the range of acceptable values of an attribute.

  • A domain of possible values must be associated with every attribute (e.g., integer types, character types, date/time types).

Declaring an attribute to be of a particular domain acts as a constraint on the values that it can take. Domain constraints are the most elementary form of integrity constraint. They are tested easily by the system whenever a new data item is entered into the database.

Common Database data type include:

Data TypeDescription
char(n)Stores a fixed-length n-character string (text)
varchar(n)Variable length character string (text) of maximum size n characters
intAn integer number (whole number)
decimal(m, n)A decimal number of m total digits and n decimal places
dateA date value (day/month/year) – if using British standard
datetimeA date time value (day/month/year hr:min:sec AM/PM)
bitA Boolean value (‘True’ and ‘False’ or 1 and 0)

For example:

Attribute NameDomain
studentNamevarchar(100)
favColourvarchar(10) {red, green, blue}

Table Schemas Link to heading

A TABLE SCHEMA is the overall design of a relational table in a relational database.

Programming analogy: a database schema corresponds to the variable declarations (along with associated type definitions) in a program.

Format: RelationName(attributeNames)

Example: Customer(customerID, firstName, familyName, address, age)

Tuples Link to heading

A TUPLE is an instance of a relation or entity and contains the actual raw data.

To simplify, a Tuple is a row of data.

A relation consists of one or more unordered tuples.

Within a relation, every tuple has a fixed number of values, hence the name ’tuple’.

Put It All Together Link to heading

Terms andNotations