Internal Level Design
- Table – a systematic representation of data.
- Entities – the primary point of interest.
- Attributes – entity characteristics:
- Primary Key – attributes that uniquely identify an item (e.g., SKU Number);
- Foreign Key – a primary key in another table, used to execute a relationship (e.g. item number);
- Non-Key – attributes used to describe the entity (e.g., item description).
- Record – a row that represents one instance of an entity.
To begin internal level design, it is important to learn the key terms. Firstly, a table is used for a systematic representation of collected data. Secondly, entities are the primary point of interest, items that will be described in the table. Thirdly, attributes are entity characteristics. There are three types of attributes: primary key, foreign key, and non-key. Primary keys are attributes that uniquely identify an item, such as an SKU number. Foreign keys are attributes that are used as a primary key in another table, such as the item number. They are used to execute a particular relationship. Non-key attributes are used to describe the entity but do not fit the criteria for foreign or primary keys. An example of this is item description. Lastly, a record is a row in the table that represents a specific instance or entity.
Step 1: Create a table for each entity
- Goal: to gather information about entities.
- One table for each entity defined in the REA diagram.
- Nine separate tables for the Green Goods, Inc. case.
The first step is to create a table for each entity. The goal of defining the entities is to gather information about them. In the example of Green Goods, Inc., we will create a table for each entity defined in the REA diagram. As each entity requires a separate table, there will be nine tables for Green Goods, Inc. case. The table shows the entities and attributes that will be used later.
Step 2: Identify the attributes
- All three types of attributes for each table.
- Primary key validation.
- Foreign keys – primary keys in events, agents, or resources used in related events:
- “Invoice Number” – “Sales” (primary key), “Cash Collection” (foreign key);
- “Customer Number” – “Customers” (primary key), “Sales” (foreign key);
- “Item number” – “Inventory” (primary key), “Sales” (foreign key).
- Non-key attributes:
- must describe entity characteristics;
- cannot be primary or foreign keys;
- example: “Invoice Date” – non-key attribute in “Sales”.
The next step is to define the attributes to be used when constructing a table. To determine your primary keys, you will need to conduct a primary key validation process presented in the table. If an attribute meets the criteria, it can be used as a primary key.
Foreign keys are primary keys in events, agents, or resources used in related events. For example, “Invoice Number” is a primary key in the event “Sales”, which can be used as a foreign key in “Cash Collection”. “Customer Number” is the primary key in the “Customers” agent, which becomes a foreign key in the “Sales” event. In the “Inventory” resource, “Item number” is the primary key. Therefore, “Item number” can be a foreign key in the “Sales” event.
Non-key attributes must describe the characteristics of a particular entity, but they cannot be primary or foreign keys. For example, “Invoice Date” will be a non-key attribute in “Sales”, as it does not differentiate one incident from the others and there is a possibility of duplication. As a result of defining the attributes, we have determined the attributes to be used in each table in the Green Goods, Inc. case.
Step 3: Completeness check
- Number of entities:
- at least 2 events;
- at least 2 agents for each event;
- at least 1 resource for each event (excl. commitment).
- Number of tables:
- at least 1 table for each entity.
- Primary keys:
- 1 primary key per table.
- Foreign keys:
- 1 foreign key for each relationship;
- all foreign keys located in “Events” tables.
- Non-key attributes:
- must describe the entity;
- in “Events” tables, change with every transaction;
- in “Agents” and “Resources” tables, do not change or change less often.
The last step of the process is completeness check. It is critical to ensure that the tables provide accurate information and are structured appropriately. Entities should include at least two events, with at least two agents and one resource for each event. For commitment events, you do not need to have at least one resource.
There should also be at least one table for each entity, one primary key for each table. For each relationship, you need to have at least one foreign key, and all foreign keys should be located in the “Events” tables. Finally, ensure that your non-key attributes describe the entity. In “Events” tables, non-key attributes change with every transaction, but in “Agents” and “Resources” tables, they might not change at all or change less frequently.
