Entities, attributes, and relationships

Posted on by inspiriasoft

Back to: Database (DX) Design Course

0

Entity, Entity Set and Entity Type

  • Entities represent real-world objects, concepts, or things that we want to model and store data about in a database.
  • Examples of entities can include students, courses, employees, customers, products, etc.
  • Each entity has a unique identifier known as a primary key, which distinguishes it from other entities.
  • An object with physical existence (e.g., a lecturer, a student, a car)
  • An object with conceptual existence (e.g., a course, a job, a position)

Another term to know is entity type which defines a collection of similar entities.

An entity set is a collection of entities of an entity type at a particular point of time. In an entity relationship diagram (ERD), an entity type is represented by a name in a box. For example, in Figure below, the entity type is EMPLOYEE.

A yellow rectangle with e1, e2 and en inside. There is an arrow from the yellow box to a blue rectangle with the work EMPLOYEE in capitals. Over the arrow are the words Represent in ER diagram. To the far left it says Entity set.
Figure ERD with entity type EMPLOYEE.

Attributes

Attributes of Entities

  • Attributes define the characteristics or properties of an entity.
  • They describe the data associated with an entity and provide details about it.
  • Examples of attributes for a student entity can include student ID, name, age, gender, address, etc.
  • Attributes can be categorized as either simple (single-valued) or composite (consisting of multiple sub-attributes).

Each entity is described by a set of attributes (e.g., Employee = (Name, Address, Birthdate (Age), Salary).

Each attribute has a name, and is associated with an entity and a domain of legal values. However, the information about attribute domain is not presented on the ERD.

In the entity relationship diagram, shown in Figure 8.2, each attribute is represented by an oval with a name inside.

One blue rectangle with the word EMPLOYEE. This is connected with a line to four separate yellow ovals. Each has a different word inside it: Name, Address, Birthdate, Salary.

Figure: How attributes are represented in an ERD.

Types of Attributes

There are a few types of attributes you need to be familiar with. Some of these are to be left as is, but some need to be adjusted to facilitate representation in the relational model. This first section will discuss the types of attributes. Later on we will discuss fixing the attributes to fit correctly into the relational model.

Simple attributes

Simple attributes are those drawn from the atomic value domains; they are also called single-valued attributes. In the COMPANY database, an example of this would be: Name = {John} ; Age = {23}

Composite attributes

Composite attributes are those that consist of a hierarchy of attributes. Using our database example, and shown in Figure 8.3, Address may consist of Number, Street and Suburb. So this would be written as → Address = {59 + ‘Meek Street’ + ‘Kingsford’}

Blue rectangle with the word EMPLOYEE. Under this are four yellow ovals with the words Name, Address, Birthdate, Salary. There are lines between the rectangle and yellow ovals. Under the Address oval are three white ovals with the words Number, Street, Suburb.
Figure: An example of composite attributes.

Multivalued attributes

Multivalued attributes are attributes that have a set of values for each entity. An example of a multivalued attribute from the COMPANY database, as seen in Figure 8.4, are the degrees of an employee: BSc, MIT, PhD.

Blue rectangle with the word EMPLOYEE. A line connects this to each of five yellow ovals with these words inside the ovals: Degrees, Name, Address, Birthdate, Salary
Figure: Example of a multivalued attribute.

Derived attributes

Derived attributes are attributes that contain values calculated from other attributes. An example of this can be seen in Figure 8.5.  Age can be derived from the attribute Birthdate. In this situation, Birthdate is called a stored attribute, which is physically saved to the database.

Blue rectangle with the word EMPLOYEE, and connected by a line to five different yellow ovals with the words: Age, Name, Address, Birthdate, Salary.

Figure: Example of a derived attribute.

Exploring Different Types of Relationships Between Entities

A. One-to-One Relationship

  • A one-to-one relationship exists when each entity instance in one entity type is associated with exactly one entity instance in another entity type.
  • Example: Each student has one and only one student ID, and each student ID is assigned to only one student.

Types of Relationships

Database tables that use primary and foreign keys to create relationships have varying levels of cardinality, which describes how many possible occurrences of one entity (record in a table) can be associated with the number of occurrences in another (records in another table).


One-to-One Relationship


A one-to-one relationship means that one record in a table will be associated with only one record in the other table. In a one-to-one relationship, the primary key in the first table is often the primary key in the other table as well.

Let’s walk through an example. Suppose when a student enrolls in school they are entered into the tblStudents table. The student record contains basic student information, like StudentID, date of birth, and other identifying information. If a student meets the initial score requirements needed for gifted services, a record will also be entered into the gifted entry table. The Student ID is the primary key for both tblStudents and tblGiftedEntry.


The symbols on the relationship line indicate the type of relationship that exists between these two tables. There can be only one record per student in tblStudents and only one record per student for GiftedEntry. Thus, the cardinality of the two tables is a one-to-one relationship.

One-to-Many Relationship

In a one-to-many relationship, a primary key is joined to a foreign key, meaning there is one record (in the table in which the key is primary) associated with multiple records in other tables (in which the key is foreign)).

Suppose each time our student enters into a new school year, we use their StudentID from the student information table to associate them to their new enrollment record in the tblEnrollment table. StudentID is a primary key in tblStudents but a foreign key in tblEnrollment.

That means we have one record for each student in tblStudents, and we have as many enrollment records for that student as years they are enrolled. (If this student went to school for five years, we would have five records to represent each year they were enrolled.) On the diagram, you see a “1” next to tblStudent and an infinity symbol near tblEnrollment.
These symbols represent the cardinality.

There can only be one record in tblStudents for a student, but an infinite number can exist for that same student in tblEnrollment, and thus this is a one-to-many relationship.

Many-to-Many Relationship

A many-to-many relationship means that you have many records associated with many other records. Data tools can’t resolve a many-to-many relationship without the use of another table that uses the associated keys from each table to serve as a bridge.

Suppose our student records management system also tracks participation in extracurricular activities. Many students are involved in more than one activity;

for example, a student who is in band and on the debate team. There are also many other students in band and on the debate team with this one student. ThetblExtracurriculars table uses the ExtracurricularID field to identify the activity along with the Extra_Name field, which allows us to identify which student is participating in that activity. We also have each student’s basic information in tblStudents.


One record represents the activity (tblExtracurriculars), and one represents the student (tblStudents). In order to allow many students to do many extracurricular activities, we have a bridge table, tblExtraAssignments, that contains the two fields
from our separate records (ExtracurricularID and StudentID). StudentID from the main student information table.

On the diagram, the many-to-many cardinality is represented by a “1” next to our two single records and an infinity symbol next to
our bridge table, tblExtraAssignments.