Many-to-many (M:N) relationships, although valid in conceptual modelling, are not directly supported in relational database systems. This is because a relational table structure cannot store multiple values in a single cell without violating the rules of normalisation and atomicity. Instead, to implement a many-to-many relationship, a linking table or associative entity is introduced. This table breaks the many-to-many relationship into two one-to-many relationships. For example, if Students enrol in multiple Courses, and each Course has many Students, an Enrolment table is used. It includes foreign keys referencing both StudentID and CourseID, creating two one-to-many relationships: Student to Enrolment and Course to Enrolment. This structure also allows for the addition of extra attributes like EnrolmentDate or FinalGrade, which are specific to the relationship itself. Resolving many-to-many relationships ensures the database adheres to relational principles and supports efficient querying and data integrity.

Logical and physical ERDs represent different stages of database modelling. A logical ERD is used during the conceptual and early design stages. It focuses on the data requirements of a system without worrying about how the data will be stored on hardware. It includes entities, relationships, and cardinality, but avoids implementation details such as column data types, indexing, or table storage details. A physical ERD, on the other hand, is more detailed and specific to the implementation phase. It includes all table structures, primary and foreign keys, data types, constraints, and physical storage details. At the conceptual data modelling stage, the logical ERD is used to communicate with stakeholders and plan the structure of the database in a platform-independent way. This makes it easier to adapt or scale later and ensures the data design aligns with real-world needs before committing to technical details in the physical model.

Yes, ERDs can be used to model hierarchical or recursive relationships, where an entity has a relationship with itself. These are particularly common in organisational structures, such as Employee managing another Employee, or in file systems where a Folder contains other Folders. To represent a recursive relationship in an ERD, the same entity appears at both ends of the relationship line. The relationship is labelled with a verb that describes the association (e.g. manages, contains). It is essential to distinguish between the roles played by the entity in each part of the relationship—for instance, one Employee is the manager, and the other is the subordinate. Cardinality must also be shown on both sides of the relationship line. Recursive relationships help to accurately model real-world data hierarchies, but care must be taken during implementation to ensure that foreign keys correctly reference the appropriate instance of the same table.

Determining whether something should be an entity or an attribute depends on the importance, uniqueness, and relationships of the concept within the data model. Generally, if a concept has multiple attributes of its own, or if it participates in relationships with other entities, it should be modelled as a separate entity. For example, Address might seem like an attribute of Customer, but if the system needs to store multiple addresses per customer or include attributes like Postcode, City, and Country, then Address should be a separate entity. Conversely, if a concept is simply descriptive and does not have relationships or multiple values per entity instance, it is better represented as an attribute. For instance, a Customer’s FirstName or DateOfBirth are attributes because they describe a single piece of information about the customer and are unlikely to have their own independent relationships or properties.

Incorrectly assigning cardinality in an ERD can lead to serious issues in the design and operation of a database. If the model suggests that a relationship is one-to-one when it is actually one-to-many, the database will be unable to store valid data relationships, potentially leading to data loss or inconsistent querying. For example, if a Customer can place multiple Orders but the ERD mistakenly shows a one-to-one relationship, the system might only allow one order per customer. On the other hand, if a many-to-many relationship is incorrectly modelled as one-to-one, it could require extensive redesign later when the mistake is discovered. Misassigned cardinality also affects referential integrity constraints, causing incorrect foreign key placements and limiting how data can be inserted or updated. These issues increase maintenance costs and reduce system reliability. Careful analysis of scenarios and data requirements is essential to ensure cardinality is accurately captured during the modelling phase.