Design

• entity-relationship diagrams with three or more entities, indicating entity name, attributes, name of relationship and cardinality of relationship (one-to-one, one-to-many, many-to-many)
• A data dictionary is used to record details about each attribute of each table in the database. It includes attribute name, key, type, size (for text fields), required and validation. The purpose of a data dictionary is to provide a detailed design of the database.
• Key - This column is used to identify all Primary Keys and Foreign Keys. Each key in a Compound Key should be included as a PK. Remember that a PK might also be a FK e.g. PK/FK.
• Data type/size - Common data types include Text (used to store alphanumeric values, Number (used to store any value that is needed or can be used in a calculation, integer means positive/negative whole number and real has a decimal point), Boolean (used to store one of two possible values, usually true or false), Date and Time.
• Size - Field length - This shows the maximum number of characters for text fields.
• Required - presence check - This indicates if a field must not be empty. All Primary and Foreign Keys are required.
• Presence Check – an attribute cannot be left empty.
• Range Check – ensures value is between a given minimum and maximum
• Restricted Choice – lets user select from a given list of options
• Field Length – checks the maximum number of characters allowed
• Foreign Key – existing value from another table, implemented as a value list using the field from the other table where it is a primary key.
• The cardinality of a relationship defines the number of participants in the relationship. It states the number of entity occurrences in one entity that are associated with one occurrence of the related entity. Cardinality can be:

  • one-to-one
  • one-to-many
  • many-to-many
• In a one-to-one relationship, each entity occurrence in an entity is associated with one, and only one, entity occurrence within a related entity. For example, a School is managed by one, and only one, Headteacher, with a Headteacher managing one, and only one, School.
• In a one-to-many relationship, each entity occurrence in an entity can be associated with one or more entity occurrences in a related entity. For example, a School employs many Teachers and each of those Teachers is employed by one School.
• In a many-to-many relationship, several entity occurrences in an entity can be associated with multiple entity occurrences in a related entity. For example, many Students study several different Subjects and each of those Subjects is studied by many Students. Direct many-to-many relationships between two entities cannot be implemented by a relational database system. To overcome this problem, many-to-

  many relationships can be resolved to form two one-to-many relationships.
• An entity-occurrence diagram illustrates the relationships between the entity occurrences of one entity, with the entity occurrences within a related entity. The creation of an entity-occurrence diagram helps

  to identify the cardinality of the relationship that exists between the two entities.
• In an entity-occurrence diagram, each entity is shown as a tall oval. Inside each entity, each entity occurrence is represented by the value of its identifier and each relationship is illustrated by drawing a line between associated entity occurrences.
• An entity-relationship diagram is a graphical representation of the entities in a system. It is used to summarise the relationship that exists between two or more entities. An entity-relationship diagram indicates:

  • the name of each entity in the system
  • the name of the relationship between two entities
  • the cardinality of the relationship between two entities
  • if required, the name of each attribute can be shown
• A many-to-many relationship cannot be implemented in a database system. Instead, a third entity must be introduced. This new entity replaces the many-to-many relationship with two separate one-to-many relationships. The new entity includes a foreign key from each of the other entities.