Chapter 3: Database concepts I Flashcards
Define a relational database and discuss its constituting elements.
A relational database is a database that stores data in a collection of carefully defined tables.
A table is a collection of columns or attributes that describe an entity.
A record consists of a set of fields and characterises a person, place or thing linked to the business.
Fields describe a particular characteristic of each record, such as name, address or phone number.
Data or facts produce fields.
Give an example of how data redundancy can occur.
Data redundancy occurs when multiple locations store the same information in an organisation.
For example, both the accounts receivable department and the marketing department has a customer table with each customer’s primary key, address and phone number.
This duplication is an example of data redundancy because the same information is stored twice for each customer within a single database.
If a customer’s details change, there is a risk that employees will make the changes in one place only, resulting in inconsistent and repeated data.
Describe the three types of data anomalies.
We can classify data anomalies into three different types:
- modification
- insertion
- deletion
Modification anomalies can occur when a field value is changed, and not all occurrences are updated.
Insertion anomalies can occur when individuals enter new data into the customer file, and not all occurrences are updated.
Deletion anomalies can occur when the deletion of data about an entity inadvertently deletes data about another entity.
Describe the key responsibilities of a database administrator, database designers, and systems analysts involved in a database system.
Database administrators manage the use of the database and ensure that all the functions are working correctly.
A database serves cross-functional information systems and also exists independent of the separate information systems.
Therefore, a database requires technical specialists, such as database administrators, to monitor performance, deal with changes to the structure and carry out backup procedures.
Ensuring that data has privacy controls, security and backup are the essential tasks of a database administrator.
They also give staff access to different areas of the database, depending on the approved access level of each staff member.
Database designers develop structures and designs that capture data for storage and manipulation, which play a critical role in overcoming some system limitations.
Systems analysts and programmers create the screens, reports and procedures that end users can access to manipulate data in the database system.
What are the advantages of database systems?
We design database systems to eliminate the repetition of data and the incidence of inconsistent data by providing structured data and storing it in one location only.
Structured data allows for data sharing across the organisation and provides the foundation to develop any database.
We considerably reduce the incidence of errors by using independent data structures because we only have one location for entering and manipulating data.
The database management system (DBMS) also reduces program maintenance because there is only one centrally located point where we reprogram all data.
Furthermore, one centrally located point increases an organisation’s speed and flexibility to prepare crucial decision-making information.
DBMS and data centralisation enforces consistent data structures and storage standards across the organisation.
It also aids data and system security by having only one access control point into the database.
For these reasons, privacy, security and adequate backups to the system are also essential.
What are the important reasons for database modelling?
Sound database design is the foundation of useful applications.
It provides a blueprint to build the required tables and relationships in a database application.
Furthermore, different people – managers, administration staff and programmers – view data differently.
Managers see database applications from a decision-making perspective.
Administration staff are more concerned about the database’s ease of use.
Programmers often think about design simplicity, without considering either decision-making or ease of use.
However, a sound database model adequately captures all the different viewpoints.
What are the four steps used to develop ER diagrams?
The development of an entity-relationship (ER) diagram is an iterative process involving four steps:
Step 1. Develop a general narrative of the organisation’s operations, including the business process, policies and business rules.
Step 2. Construct the ER diagram by identifying the internal and external entities and the relationships among them from the narrative.
We can also assign cardinalities and business rules.
Step 3. Review the ER diagram by each area of the organisation owning the operations, policies and processes.
Step 4. Make the necessary modifications and incorporate any new entity-relationship components.
Finally, the designers and users should repeat the process until they agree that the ER diagram fully represents the relationships and rules that govern the organisation’s entities.
What are the three possible types of relationships in ER diagrams?
A one-to-many relationship (1:N) is a relationship between two entities, where the cardinality of one is one and the cardinality of the other is many.
A one-to-one relationship (1:1) is a relationship between two entities, where the cardinality of both is one.
A many-to-many relationship (N:M) is a relationship between two entities, where the cardinality of both is many.
How do we implement 1:N, 1:1 and N:M relationships in a database system?
The general rule for implementing the 1:N (one-to-many) relationship is to enable the primary key of the one to become the foreign key of the many.
In implementing a one-to-one (1:1) relationship, the general rule is to anticipate which side will likely become the many and allocate it as though it was a one-to-many (1:N) relationship now.
In implementing a many-to-many (N:M) relationship, we need to insert a relationship diamond (a linking entity) between both entities to form a composite entity that holds the primary keys of both tables.
It is necessary to split the N:M relationship into two separate 1:N relationships to meet the relationship requirements of a computerised database package.
What are the two approaches to database design?
Discuss their differences and relationship.
The two approaches to database design are the top-down and the bottom-up approach.
The entity-relationship (ER) diagram portrays entities and their relationships from an overall perspective.
It is a strategic or top-down way of viewing the organisation for developing a database model.
Normalisation, on the other hand, is a bottom-up view for designing a database.
In reality, we use both techniques to design a database.
The ER diagrams ensure that we include the upper-level strategic objectives of the organisation.
In contrast, normalisation ensures that we include the lower-level operational aspects because the normalisation process starts with the organisation’s tables, forms and data.
We then reconcile both techniques to ensure the creation of an organisation-wide database model that incorporates strategic objectives with operational data.
What advantages do databases offer for decision-making and reporting processes?
Databases capture, store and manage data as efficiently and effectively as possible.
This process has advantages for decision-making and reporting because data are the building blocks of information.
Accurate, relevant and timely information comes from efficient quality data capture, storage and management.
In other words, when we capture, store and manage data in its lowest form, we can convert it into accurate, relevant and timely information necessary for effective decision making and organisational performance.
Describe the elements of a database system, including the DBMS.
A database is a collection of elements that allow the capture, storage, management and use of data within a database environment.
These elements include:
- hardware
- software
- people
- procedures
- data
Hardware refers to the system’s physical devices, such as the computer and computer peripherals.
The software is the collection of programs used by the computer to run:
- the operating system
- DBMS software
- application programs
- utilities
The DBMS software allows us to manage the database within the database system.
Several people are involved in the database system:
- systems administrators
- database designers
- systems analysts
- programmers
Procedures are the rules that govern the design and use of the database system for the organisation.
The role of the people within the database system is to store data in a central location and to determine:
- which data they will enter into the database
- how they will organise the data
Explain why and how we perform data modelling.
We use data modelling to describe and represent complex real-world data structures.
Data modelling provides a graphical and logical representation of the complexities of real-world data entities and their relationships.
Also, a database designer can use a data model to depict their design’s data structures, relations, characteristics and constraints.
Therefore, it is a communication tool that can provide the blueprint for developing new database structures or create an improved understanding of an organisation’s database.
Data modelling is the foundation of useful applications, and it should encapsulate the views of all people, from managers to programmers, in the model.
We perform data modelling in an interactive four-step process using ER diagrams:
- Develop a general narrative of the organisation’s operations, including the business processes, policies and rules.
- Construct the ER diagram by identifying the internal and external entities and their relationships. Connectivity and cardinalities can also be assigned based on the narrative.
- Each area of the company with ownership of the operations, policies and processes should then review the ER diagram.
- Make the necessary modifications to incorporate any newly discovered entity-relationship components.
Explain where this diagram shows that a supplier can supply many DVD titles.
Explain where this diagram shows that a DVD title can come from many suppliers.
Explain where this diagram shows that a DVD title can have many DVD copies.
Explain where this diagram shows that a DVD copy relates to one title only.
Explain where this diagram shows that a DVD copy can be rented to one customer at a time.
Using the entity-relationship diagram in figure 3.44 for the partial order entry system:
(a) Write relationship sentences for all the relationships.
(b) Discuss what each relationship means in terms of how the business works.
(c) Does the business allow partial picks (i.e. only part of the goods on the sales order to be picked)?
(d) Does the business allow partial shipments (i.e. only part of the goods that have been picked to be shipped)?
(e) Are all shipments invoiced in-full or only partially (i.e. does the business allow partial invoices)?
…
(a) Relationship sentences:
1. One customer can place many sales orders.
2. A sales order relates to only one customer.
1. A sales order can trigger many stock pickings.
2. A stock-picking relates to one sales order.
1. A stock-picking relates one shipment only.
2. A shipment can contain many stock pickings.
1. A shipment is billed on one sales invoice.
2. A sales invoice can contain many shipments.
(b) The relationships between the customer and the sales orders are very standard.
A customer can place many sales orders, and each order relates to back to one customer.
The relationship relating to the picking and shipment are relating to the relationships given.
For example - the business allows partial picks see part (c) below and does not allow for partial shipments see part (d) below, also shipments are invoiced in full see part (e) below.
(c) To answer if the business does allow partial picks (i.e. only part of the goods on the sales order to be picked) we need to look at the relationship between sales order and picking.
The relationship sentences are:
A sales order can trigger many stock pickings.
A stock-picking relates to one sales order.
Since a sales order can trigger many stock pickings if we do not pick everything on the first pick we can go back and pick from this sales order again. Therefore, the business does allow partial picks.
(d) To answer if the business does allow partial shipments (i.e. only part of the goods that have been picked to be shipped) we need to look at the relationship between stock picking and shipment.
The relationship sentences are:
A stock-picking relates one shipment only.
A shipment can contain many stock pickings.
Since a stock-picking relates to one shipment the business does not allow partial shipment.
(e) To answer the question are all shipments invoiced in full or partially (i.e. does the business allow partial invoices)?
We need to look at the relationship between shipment and sales invoices.
The relationship sentences are:
A shipment is billed on one sales invoice.
A sales invoice can contain many shipments.
Since a shipment is billed on one sales invoice.
A shipment is invoiced in full in this case.
For an invoice to be invoiced partially that relationship would need to change to a many.
