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.
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.
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.
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
- create tables
- DML: Data Manipulation Language. These statements are used to:
- add data
- update data
- delete data
- retrieve data
- add 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.