Chapter 4: Database concepts II Flashcards
Discuss the outcomes of the first, second, and the third normal forms and the actions required to achieve them.
First normal form
Outcome:
A table has no repeating groups.
Action:
Select a primary key that uniquely identifies each line in the table.
Second normal form
Outcome:
A table is in first normal form and has no partial dependencies.
A partial dependency is where the attribute is not entirely dependent on the primary key but part of it.
Action:
Draw dependency arrows.
Remove partial dependencies and make the attributes entirely dependent on their primary keys.
Third normal form
Outcome:
A table is in second normal form and has no transitive dependencies.
A transitive dependency is where an attribute is dependent on the primary key via a non-key attribute.
Action:
Remove transitive dependencies by making the non-key attribute a primary key in a new table.
Also, leave the non-key attribute as a foreign key in the existing table, so that it remains dependent on the primary key.
What is the primary key?
How can we design a good primary key?
Every table must have a primary key, which is an attribute (or column) that uniquely identifies a particular object (or row) in the table.
Primary keys should be numeric because they are likely to be unique, short and unchanging, whereas a project name, for example, can be duplicated.
Furthermore, alphanumeric combinations – letters in combination with numbers, such as passport numbers – have many more possible combinations and are also appropriate primary keys.
The relationship between the primary key and the rest of the data will be one-to-one.
Therefore, the primary criterion of a primary key is its uniqueness so that it can never point to more than one object (or row) in a table.
Using employee data as an example, what basic problems does the second normal form solve?
The 2NF solves several fundamental problems:
It reduces duplication because there is no need to enter the employee data every time we initiate a new project.
We also save storage space because employee details no longer need to be allocated to every project.
Lastly, we also remove partial dependencies.
What are the normal forms beyond the third normal form?
Are these normal forms used in general business scenarios?
There are higher normalisation processes beyond 3NF.
They include a particular type of 3NF called Boyce-Codd normal form (BCNF), 4NF and 5NF, but are often not performed because the process usually requires artificial assumptions that rarely hold in the business environment.
These assumptions may not be valid all the time and conducting this further normalisation, ironically, can reduce the flexibility that the first three forms intend to achieve.
Illustrate the process of enterprise model development.
First step: Develop a general narrative of the organisation’s operations, including the business processes, policies and business rules.
Second step: Construct E-R diagrams for each area of operations by identifying the internal and external entities and their relationships.
Assign connectivity and cardinalities based on the narrative.
Third step: Have the E-R diagrams reviewed by the areas of the organisation with ownership of the operations, policies and processes.
Fourth step: Make the necessary modifications to incorporate any newly discovered entity-relationship components.
Fifth step: Normalise the entities to ensure well-structured data and to remove data anomalies or data redundancy.
Sixth step: Consolidate the E-R diagrams.
When is an enterprise model ready for database implementation?
An organisation needs to go through the six steps of developing an enterprise model.
The organisation repeats this process until the designers and users agree that the enterprise model (consisting of the combined and reconciled E-R diagrams and normalisations) is complete and represents the relationships and rules that govern the organisation’s entities.
At that point, the organisation is ready to implement the enterprise model as a database.
Briefly describe the nature of the REA Accounting Model.
The REA accounting model defines common patterns in entities and activities found in all organisations.
Through systems mapping and documentation, a thorough understanding of an organisation’s context, entities, business processes, risks, and information needs, forms a basis for the model.
The premise that every exchange in a process involves a resource, event and agent, is also fundamental for the model.
Furthermore, the model also represents organisational exchanges between the various processes and brings them together to form an enterprise system.
Describe the concept of duality in REA.
William McCarthy proposed that in every exchange, such as within the sales process, there are common patterns of inflows and outflows.
This pattern means that for every inflow, or a “take” event, there must be an outflow or a “give” event (illustrated in Figure 4.38 and 4.39).
Describe two differences between an ER model and an REA model.
(1)
We can randomly place entities or the “boxes” in an ER model.
In contrast, the “boxes” of an REA model, which represent resources, events, and agents, must be placed under the R, E, and A columns accordingly.
(2)
ER models do not have events; REA models do.
Discuss the three major components of the client-server architecture.
The client-server architecture includes three major components:
- hardware
- software
- communications middleware
The hardware consists of the physical computers and the physical servers that represent the client and the server.
- Front-end* application software is loaded onto the client computers for the user to interact with the server as part of the client process.
- Back-end* application software is usually loaded onto the server to provide essential background services to the clients.
Communications middleware is the software by which clients and servers communicate.
This middleware layer holds different types of software that aid the transmission of data and control information between the client and the server.
Furthermore, the middleware sits partly on the client and partly on the server, enabling them to interact.
Describe the process of normalisation for database design.
Normalisation aims to reduce or eliminate:
- repeating groups
- data anomalies
- data redundancies
It also helps to form and organise tables to have structural and data independence.
The normalisation process defines and reorganises data so that each entity is a table containing its unique attributes.
We can apply different forms of normalisation to data:
Lower forms reduce repeating groups, data anomalies and data redundancies, whereas higher forms eliminate them.
What is the primary key?
An attribute (or column) that uniquely identifies a particular object (or row).
Each table must have its own primary key.
What is a composite primary key?
Give an example of how it has arisen.
A composite primary key is a primary key that consists of more than one attribute.
It is a primary key (singular) consisting of the minimum number of attributes to identify each line in the table uniquely.
A composite key can arise when you implement the relationship in an M:N relationship between two entities.
The primary key attributes of each entity become the composite primary key of the bridging element.
What is the purpose of normalisation in a database?
The purpose of normalisation is to form tables and keys to eliminate repeating groups and data redundancies.
The elimination of these data anomalies produces a set of controlled redundancies to link tables that provide the most flexible system for the organisation.
Discuss the seven steps required to construct an enterprise model of a business.
The development of an enterprise model is an interactive process involving seven steps.
- Develop a general narrative of the organisation’s operations, including the business processes, policies and business rules.
- From the narrative, construct ER diagrams for each area of operations by identifying the internal and external entities and their relationships.
- Assign connectivities and cardinalities based on the narrative.
- Review the ER diagrams by the areas owning the operations, policies and processes.
- Make necessary modifications to incorporate any newly discovered entity-relationship components.
- Normalise the entities to ensure well-structured data and to remove data anomalies and redundancies.
- Combine and consolidate the normalised entities with the ER diagrams.
Repeat this process until the designers and users agree that the enterprise model is complete and faithfully represents the relationships and rules that govern the organisation’s entities.
