Entities and attributes are the building blocks of database design. They help define what data is stored and how different items in a system are described.
What is an entity?
An entity is something in the real world that we want to store data about in a database. It can be a physical object like a car, a person like a student, or even a concept like a booking or an order. Entities are essential for building databases because they represent the things we are keeping track of.
Key characteristics of entities
An entity is clearly defined and must be distinguishable from others of its kind.
Entities are typically nouns in real-world scenarios. For example: Book, Customer, Invoice, Employee.
Each entity becomes a table in a relational database. The table holds data about multiple instances of that entity.
Each row in the table represents a single instance of the entity. This is also known as a record or a tuple.
Each column in the table represents an attribute, which describes the entity in some way.
Practice Questions
FAQ
Yes, two different entities can have attributes with the same name, and this is common in database design. For example, both Student and Teacher entities might have an attribute called Name or Email. These attributes serve similar purposes but are scoped to their respective entities. In a relational database, each table is independent, so attribute names do not need to be unique across the entire database—only within the table they belong to. The database manages this through table names and fully qualified references. For example, when writing a query, you might specify Student.Name or Teacher.Name to distinguish between them. This approach maintains clarity and avoids conflicts, especially in queries involving joins. Good practice also includes using clear and descriptive attribute names where necessary, such as StudentName or TeacherEmail, to further reduce ambiguity and improve readability, particularly in complex systems involving many entities and relationships.
If an entity does not have a naturally occurring unique attribute that can act as a primary key, a surrogate key is typically used. A surrogate key is an artificial identifier added solely for the purpose of uniquely identifying each record. It usually takes the form of an automatically incremented integer (e.g. 1, 2, 3…) or a randomly generated unique value, depending on the database system. For instance, an entity called Event that has only attributes like Title, Date, and Location might not have any field that reliably ensures uniqueness. In this case, an EventID can be introduced as a surrogate key. This avoids the problems of relying on potentially duplicate or unstable data to identify records. Surrogate keys are especially useful in large-scale systems where relying on real-world data to uniquely identify records is impractical or error-prone. Although surrogate keys add no real-world meaning, they provide consistency and performance benefits.
Optional attributes are fields that may not apply to every instance of an entity. In relational databases, optional attributes are allowed to have null values, which means no data is stored in that field for a particular record. For example, in an Employee entity, the attribute PhoneExtension may not be relevant to employees without a desk phone. In this case, the PhoneExtension attribute can remain empty (null) for those employees. During the design phase, optional attributes must be clearly identified to ensure appropriate validation and data handling. It is important to limit the number of optional attributes where possible, as excessive use of nulls can reduce database efficiency and make queries more complex. When many optional attributes relate only to a subset of records, it may be more appropriate to separate them into a related entity and use a one-to-one or one-to-many relationship. This helps keep the data model normalised and flexible.
Composite attributes are those that can be split into smaller sub-parts, such as FullName (which can be divided into FirstName and LastName) or Address (which could include Street, City, Postcode, etc.). While composite attributes may seem convenient, they violate the principle of atomicity in relational databases. According to first normal form (1NF), each attribute must contain only a single, indivisible value. Using composite attributes makes it difficult to search, sort, or filter data accurately. For instance, searching for all people with the last name "Smith" would be challenging if the full name is stored in a single field. Additionally, composite attributes complicate indexing and retrieval performance. Therefore, it is best practice to split composite attributes into multiple atomic fields during database design. This enables more precise queries, better data validation, and improved flexibility when data requirements evolve. Following this approach also ensures compliance with standard database normalisation rules.
To prevent data duplication, careful selection of attributes and a well-structured entity design are essential. Each attribute should represent a single, non-redundant piece of information about the entity. For example, in a Customer entity, storing both FullAddress and individual components like Street and Postcode can lead to inconsistency and redundancy. Likewise, repeating information across different entities without a clear relationship may cause duplication. Ensuring each entity has a clear and unique primary key also helps prevent duplicate records. Validation rules and database constraints, such as UNIQUE constraints and NOT NULL constraints, can be enforced to prevent the entry of duplicate or null values in fields that should be unique. Normalisation also plays a major role—splitting data into related tables and removing repeated fields ensures that each piece of data is stored only once. Ultimately, designing with integrity and consistency in mind ensures the system remains efficient, accurate, and easy to maintain.
