Entities, Attributes, Relationships And More

Do you understand the relationships in databases? What is an attribute, entity, or key? How do you use these in designing a database?

| Latoya Thomas | 10 March 2022

Designing a Database

 

Entities

The Type of Information saved in a database is separated into entities. Entities can be people, events locations, and even things. If it doesn't fit into one of these categories, it is most likely a property of an entity, which makes it an attribute.

 Entities are objects or concepts that can easily be uniquely identified from other objects or concepts, such as a username, or an email address. Each is owned by a specific person and only that person can have that particular identifier. An entity set is a collection of similar entities, such as staff members or players.

Identifying Relationships

A relationship is an association among two or more entities. The relationship must be uniquely identified by the participating entities. A relationship can also have descriptive attributes to record additional information about the relationship.

Ternary Relationship Sets

A relationship set does not need to be an association of only two entities. It can involve 3 or more, when applicable.

Recursive Relationships

Recursive relationships occur when an entity participates more than once in a relationship. For example, let's use a supervisor and her employees. The supervisor is still and employee of the company.

Redundant Relationships

Redundant relationships are unnecessary logic that causes unnecessary data to be stored more than once in the database. Redundancy can cause confusion and congestion. Therefore, deleting redundant information is best practice for designing a database.

Let's look at an example of a redundant relationship. There are relationships between a customer and a product, a customer and a sale, and sales to products. Indirectly, there is a relationship between customers and products through sales. Relationships between the customer and the product are made twice, which makes it redundant. Since products are only purchased through a sale, the relationship customers to products can be deleted.

 

Many to Many Relationships (M:N)

M:N relationships need to be solved by splitting them into two 1:N ships since M:N are not directly possible in a database. M:N says that a number of records in one table belong to a number of records from another table. Resolve this issue by creating a new entity that is in between the two related entities.

Attributes

The data elements for each entity are attributes. Attributes are used to describe a particular entity. A person's name, his height and social security number are all attributes. Other examples include the price, manufacturer or type of a product. Each attribute comes from a specified domain. For example, a person's name may be allowed to be a 20-character string, a social security number is a 9 digit integer, etc.

Entity Relationships Diagram (ERD)

An ERD is a type of structural diagram used in database design. It illustrates a database using different symbols and connectors clearly displaying all the data that is to be stored within the database. An entity set is drawn as a rectangle. Attributes are drawn as ovals. Attributes that belong to the primary key are underlined, etc.

Assigning Keys

A primary key is one or more attributes that uniquely identify an entity. A key that consists of two or more attributes is called a composite key. A foreign key, sometimes called a referencing key, is a key used to link two tables together. A Foreign key is a column or a combination of columns who values match a primary key in a different table.

For more on this topic, click here to read "Designing a Database for Better Performance."

All Blog Articles
Tips & Tricks
SQL Tips and Tricks: Find All Query

Find All Query The Problem Your boss has asked you to make changes to an application that will affect an MS SQL Server database, and first needs to know the impact analysis of those changes before...

27 November 2020

Database Administration
Do This Before Designing a Database

Intro to Database Design Welcome to our "Intro to Database Design" series! In this first article, our goal is simply to cover the basics. While you may know much or all of this information, let's...

16 February 2022

Data
Designing a Database for Better Performance

6 Steps to Design a Database Interestingly, when designing a database, much of the hard work is in the planning stages long before physically working with a database management system (DBMS)....

03 March 2022

footerLogo

610-783-0600 info@aspect-consulting.com

20140 Valley Forge Circle King of Prussia, PA 19406

Resources

Aspect Consulting, Inc © 2022      All rights reserved.

Privacy Policy        Title of the document Legal Disclaimer