Weak entity
Encyclopedia
In a relational database
, a Weak Entity is an entity that cannot be uniquely identified by its attributes alone; therefore, it must use a foreign key
in conjunction with its attributes to create a primary key. The foreign key is typically a primary key of an entity it is related to.
In entity relationship diagrams a weak entity set is indicated by a bold rectangle
(the entity) connected by a bold type arrow
to a bold diamond
(the relationship). This type of relationship is called an identifying relationship and in IDEF1X
notation it is represented by an oval entity rather than a square entity for base tables. An identifying relationship is one where the primary key is populated to the child weak entity as a primary key in that entity.
In general (though not necessarily) a weak entity does not have any items in its primary key other than its inherited primary key and a sequence number. There are two types of weak entities: associative entities
and subtype entities. The latter represents a crucial type of normalization, where the super-type entity inherits its attributes to subtype entities based on the value of the discriminator
.
In IDEF1X
, a government standard for capturing requirements, possible sub-type relationships are:
A classic example of a weak entity without a sub-type relationship would be the "header/detail' records in many real world situations such as claims, orders and invoices, where the header captures information common across all forms and the detail captures information specific to individual items.
The standard example of a complete subtype relationship is the party entity. Given the discriminator PARTY TYPE (which could be individual, partnership, C Corporation, Sub Chapter S Association, Association, Governmental Unit, Quasi-governmental agency) the two subtype entities are PERSON, which contains individual-specific information such as first and last name and date of birth, and ORGANIZATION, which would contain such attributes as the legal name, and organizational hierarchies such as cost centers.
When sub-type relationships are rendered in a database, the super-type becomes what is referred to as a base table. The sub-types are considered derived tables, which correspond to weak entities. Referential Integrity
is enforced via cascading updates and deletes.
One of the tables could be called Orders and it would have an order number (primary key) to identify this order uniquely, and would contain a customer number (foreign key
) to identify who the products are being sold to, plus other information such as the date and time when the order was placed, how it will be paid for, where it is to be shipped to, and so on.
The other table could be called OrderItem; it would be identified by a compound key consisting of the order number (foreign key
) and an item line number, plus the product number (foreign key
) that was ordered, the quantity, the price, any discount, any special options, and so on. There may be zero, one or many OrderItem entries corresponding to an Order entry, but no OrderItem entry can exist unless the corresponding Order entry exists. (The zero OrderItem case normally only applies transiently, when the order is first entered and before the first ordered item has been recorded.)
The OrderItem table stores weak entities precisely because an OrderItem has no meaning independent of the Order. Some might argue that an OrderItem does have some meaning on its own; it records that at some time not identified by the record, somebody not identified by the record ordered a certain quantity of a certain product. This information might be of some use on its own, but it is of limited use. For example, as soon as you want to find seasonal or geographical trends in the sales of the item, you need information from the related Order record.
Relational database
A relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system . Colloquial use of the term "relational database" may refer to the RDBMS software, or the relational database itself...
, a Weak Entity is an entity that cannot be uniquely identified by its attributes alone; therefore, it must use a foreign key
Foreign key
In the context of relational databases, a foreign key is a referential constraint between two tables.A foreign key is a field in a relational table that matches a candidate key of another table...
in conjunction with its attributes to create a primary key. The foreign key is typically a primary key of an entity it is related to.
In entity relationship diagrams a weak entity set is indicated by a bold rectangle
Rectangle
In Euclidean plane geometry, a rectangle is any quadrilateral with four right angles. The term "oblong" is occasionally used to refer to a non-square rectangle...
(the entity) connected by a bold type arrow
Arrow (symbol)
An arrow is a graphical symbol such as → or ←, used to point or indicate direction, being in its simplest form a line segment with a triangle affixed to one end, and in more complex forms a representation of an actual arrow...
to a bold diamond
Rhombus
In Euclidean geometry, a rhombus or rhomb is a convex quadrilateral whose four sides all have the same length. The rhombus is often called a diamond, after the diamonds suit in playing cards, or a lozenge, though the latter sometimes refers specifically to a rhombus with a 45° angle.Every...
(the relationship). This type of relationship is called an identifying relationship and in IDEF1X
IDEF1X
IDEF1X is a data modeling language for the developing of semantic data models. IDEF1X is used to produce a graphical information model which represents the structure and semantics of information within an environment or system.IDEF1X permits the construction of semantic data models which may serve...
notation it is represented by an oval entity rather than a square entity for base tables. An identifying relationship is one where the primary key is populated to the child weak entity as a primary key in that entity.
In general (though not necessarily) a weak entity does not have any items in its primary key other than its inherited primary key and a sequence number. There are two types of weak entities: associative entities
Associative Entities
An associative entity is an element of the Entity-relationship model. The database relational model does not offer direct support to many-to-many relationships, even though such relationships happen frequently in normal usage. The solution to this problem is the creation of another table to hold...
and subtype entities. The latter represents a crucial type of normalization, where the super-type entity inherits its attributes to subtype entities based on the value of the discriminator
Discriminator
Discriminator is a typed tag field present in OMG IDL discriminated union type and value definitions that determines which union member is selected in the current union instance. Unlike in some conventional programming languages offering support for unions, discriminator in IDL is not identical to...
.
In IDEF1X
IDEF1X
IDEF1X is a data modeling language for the developing of semantic data models. IDEF1X is used to produce a graphical information model which represents the structure and semantics of information within an environment or system.IDEF1X permits the construction of semantic data models which may serve...
, a government standard for capturing requirements, possible sub-type relationships are:
- Complete subtype relationship , when all categories are known.
- Incomplete subtype relationship, when all categories may not be known.
A classic example of a weak entity without a sub-type relationship would be the "header/detail' records in many real world situations such as claims, orders and invoices, where the header captures information common across all forms and the detail captures information specific to individual items.
The standard example of a complete subtype relationship is the party entity. Given the discriminator PARTY TYPE (which could be individual, partnership, C Corporation, Sub Chapter S Association, Association, Governmental Unit, Quasi-governmental agency) the two subtype entities are PERSON, which contains individual-specific information such as first and last name and date of birth, and ORGANIZATION, which would contain such attributes as the legal name, and organizational hierarchies such as cost centers.
When sub-type relationships are rendered in a database, the super-type becomes what is referred to as a base table. The sub-types are considered derived tables, which correspond to weak entities. Referential Integrity
Referential integrity
Referential integrity is a property of data which, when satisfied, requires every value of one attribute of a relation to exist as a value of another attribute in a different relation ....
is enforced via cascading updates and deletes.
Example
Consider a database that records customer orders, where an order is for one or more of the items that the enterprise sells. The database would contain a table identifying customers by a customer number (primary key); another identifying the products that can be sold by a product number (primary key); and it would contain a pair of tables describing orders.One of the tables could be called Orders and it would have an order number (primary key) to identify this order uniquely, and would contain a customer number (foreign key
Foreign key
In the context of relational databases, a foreign key is a referential constraint between two tables.A foreign key is a field in a relational table that matches a candidate key of another table...
) to identify who the products are being sold to, plus other information such as the date and time when the order was placed, how it will be paid for, where it is to be shipped to, and so on.
The other table could be called OrderItem; it would be identified by a compound key consisting of the order number (foreign key
Foreign key
In the context of relational databases, a foreign key is a referential constraint between two tables.A foreign key is a field in a relational table that matches a candidate key of another table...
) and an item line number, plus the product number (foreign key
Foreign key
In the context of relational databases, a foreign key is a referential constraint between two tables.A foreign key is a field in a relational table that matches a candidate key of another table...
) that was ordered, the quantity, the price, any discount, any special options, and so on. There may be zero, one or many OrderItem entries corresponding to an Order entry, but no OrderItem entry can exist unless the corresponding Order entry exists. (The zero OrderItem case normally only applies transiently, when the order is first entered and before the first ordered item has been recorded.)
The OrderItem table stores weak entities precisely because an OrderItem has no meaning independent of the Order. Some might argue that an OrderItem does have some meaning on its own; it records that at some time not identified by the record, somebody not identified by the record ordered a certain quantity of a certain product. This information might be of some use on its own, but it is of limited use. For example, as soon as you want to find seasonal or geographical trends in the sales of the item, you need information from the related Order record.