How a Database Works

Storing Data in a DBMS

A table (also called a relation) is the basic unit of a data storage in a database management system (DBMS). Each table includes different columns that represent different attributes of the data, such as name, age, and address. Each row (also called tuple or record) in the table contains an entity, which is a singular, identifiable, and separate object.

The following table can represent various entities such as employees, customers, or students. To distinguish which type of entity this table contains, an additional column would be needed that contains a unique ID. This column could be labeled Employee ID, Customer ID, or Student ID.

First Name

Last Name

Address

City

State

Zip Code

James

Bond

555 Cedar Lane

Oakville

CA

31024

Jane

Plummer

825 Joles Drive

Jefferson City

MO

51248

Michael

Holmes

3574 Maple Lane

Salem

OR

36845

Susan

White

159 Round Circle

Cheyenne

WY

26863

Harold

Minor

321 Choice Blvd

Montpelier

VT

20785

Relational DBMS

Although there are various methods for building database management systems, one of the most accepted method is the relational database management system, or RDBMS. The relational model simplifies the data complexity by organizing it into a set of related tables or entities.

Using the student example explained in a previous step, instead of having one large, unwieldy table with all of the student’s information, you could store groups of information in various tables and connect or relate them using the Student ID, which is a unique identifier. In one table you might have student information like name, address, and email, and so forth. In another table, you could have student academic information such as courses, grades, and credits.

Entities and Attributes

An entity reflects a real-world item or a concept for which we like to store data. Attributes define the characteristics of the entity and can include facts, aspects, properties, or details about an entity.

In a RDBMS, each entity or table has to have a unique identifier or primary key. For example, consider the "Student" entity below. The Student Identifier is the unique identifier or primary key (PK), and below it are the attributes that represent the student.

The entity “Student” contains the primary key (PK): Student Identifier. The entity also contains the attributes: Name, Sex, Birth date, Address, and School.

Student Entity

Entities and Relationships

Here are some general guidelines regarding entities and their relationships:

  • Entities can represent real objects (e.g., student, employee, product) or items/concepts of interest (e.g., address, course, project).
  • Entities can have attributes, but attributes cannot have smaller parts.
  • Entities can have relationships between them, but an attribute belongs to a single entity.
  • A relationship is an association between two or more entities realized by common attributes.
  • When a table's primary key is used in another table, it is called a foreign key.

For example, the Student entity includes many attributes that are directly a part of it. However, the student address is stored as another entity because it includes its own attributes as shown below. Therefore, these two entities should be related to each other. They are related to each other through a common attribute of "Student Identifier." In general, entities’ relationships are implemented by using common attributes.

The entity “Student” is related to the entity “School” by the same PK “Student Identifier.” The details of “School” include: Major, Graduation Date, and GPA. The entity “Student” has details of Name, Sex, Birth date, Address, and School.

Relationship Between Student and Enrollment Entities

Entity-Relationship Diagrams

When there are many entities, there will be many relationships. For example, each student at a university is an entity. The university stores a large amount of information about each student, including personal information, course information, and grades.

An entity-relationship diagram (ERD) can be used to visualize how the database is organized and how all the different entities are related. As you can see in this ERD, each item is an entity. Attributes are repeated in other entities, and the lines show how they are related through common attributes. For example, the Student Course entity is related to the Student entity by using the common attribute Student Number. Note: PK = Primary Key, PF = Primary Foreign Key, also known as FK.

Lines indicate relations between the entity “Student” and entity “Student Course” containing attributes Student Number PF, Course Number PF, and Offering Department PF; entity Course with attributes Name, Description, Course Number PK, Semester Hours, Course Level, and Offering Department PF; entity Section with attributes Instructor, Semester, Year, Course, Section Number PK, Course Number, PF, and Code PF. Other entities also shown with similar relations to entity “Student.”

Example of Student Entity Relationship Diagram (ERD)

Structured Query Language (SQL)

SQL is the standard language used to manipulate RDBMSs. Different SQL statements called SQL Subset Languages are used to perform various tasks. Here are some examples:

  • DDL: Data Definition Language. These statements are used to:
    • create tables
    • delete tables
    • modify tables
  • DML: Data Manipulation Language. These statements are used to:
    • add data
    • update data
    • delete data
    • retrieve data
  • TCL: Transaction Control Language. These statements are used to manage changes made to the database.
  • DCL: Data Control Language. These statements are used to assign and revoke database rights and permissions.

SQL Syntax and Example

The general form of a typical SQL Select statement lets you select different fields or attributes from one of several tables with certain relationships between them. For example, if you want to list all student numbers and names from the student table, you write a SQL statement that looks like this:

     Select Student_Identifier, Name from Student;

The above statement will tell the RDBMS to go to the Student table and retrieve all the students' names and numbers.

Check Your Knowledge

Choose the best answer to each question:
Question 1
True or false: An attribute defines the characteristics of the entity and can include facts, aspects, properties, or details about that entity.
True
False
Question 2
True or false: An entity is a real-world item or a concept for which we store data.
True
False
Question 3
True or false: Relationship and entity refer to the same thing.
True
False
Question 4
True or false: A table is a data structure used to organize information.
True
False
Question 5
True or false: DDLs are statements used to create, delete, or modify tables.
True
False
Question 6
True or false: DMLs are statements used to add, update, delete, or retrieve data.
True
False
Question 7
True or false: TCLs are statements used to add and update data in a database.
True
False
Question 8
True or false: DCLs are statements used to create or delete tables from a database.
True
False