What are the 3 types of anomalies that could find in a not normalized database?
Show At the end of this chapter you should be able to:
INTRODUCTIONData is stored in tables in a database. It can be stored in a single table (called a flat database – as shown in Figure 2.1) or in multiple connected tables (called a relational database – as shown in Figure 2.2). Figure 2.1: Data stored in a single table in a flat database Figure 2.2: Data stored in multiple tables in a relational databaseNew words field – a single bit of Information about a person or an item, for example, age record – a group of related fields about an item or person that is captured in the table Primary key – a field that holds a unique identifier for each record in the database (Unique means there is only one of its kind) Each table consists of fields and records. Fields are the categories that you want to record data for. For example, the music table shown above, contains fields like Title, Artist, Duration and Album. Records refer to the actual data being captured, with each record containing the data of a single item. For example, in the Song table, each record represents a single song, with all the information (like artist and duration) related to that song. Each table may have one compulsory field, called a primary key, which contains a unique identifier for each record in the database. This allows you to refer to a specific record on a table in such a way that it could only refer to one entry. While it is possible to make an existing field of a table a key field, database creators usually create a new field specifically for this purpose. This allows them to make sure that there are no duplicates. Sometimes there are anomalies with databases, you will learn how to get rid of these anomalies. In this chapter, you will design and create a relational database. Sometimes there are anomalies with databases, you will learn how to get rid of these and reach normalisation. DATABASES IN A NUTSHELLData, in principal, is stored in 1s and 0s. The computer still needs to know how these 1s and 0s are organised and how they should be interpreted. In order to do this, computers make use of data structures that describe a specific sequence for data to be organised. This allows the computer to understand how the different bits of data are related and to interpret the data correctly. We know that each letter in the alphabet represents a character. When you add enough characters together, they form a word (like ‘orange’ or ‘love’) that represents a concept. Therefore, allowing communication of useful information between two or more people – even across distances. In order to communicate data and instructions, data such as numbers, letters, characters, special symbol, sounds/phonics, and images are converted into computer-readable form (binary). Once the processing of this data is complete it is converted into human-readable format, the processed data becomes meaningful information. The information becomes knowledge and can be understood and used by humans for different purposes. ACTIVITY 2.1 Revision Activity 2.1.1In your own words, explain what a database is and what it can be used for. Provide an example to support your answer. 2.1.2True or False: A data warehouse uses transaction data from various sources and makes analytical use of the data. 2.1.3Fill in the blanks by choosing the correct term from the list below: [warehouse, tables, current, record, relational, primary, field] a.Data is stored in ____________ in a database. b.A single bit of information about an item or person is a ________. c.When many related fields about an Item are put together the form a ___________. d.A ___________ key contains unique identifiers for each record in a database. e.A database is designed to store _____________ transactions whilst a data ______________ stores a large quantity of historical data. f.A ______________database stores data in multiple tables. UNIT2.1 Characteristics of a good databaseAll good databases should begin with valuable metadata and data. In Chapter 1 we looked at the characteristics of valuable data and how to manage that data. These are illustrated in Figure 2.3 below: Figure 2.3: How to manage valuable databaseLet’s now look at the characteristics of a good database:
There are also many people involved with organising a well-run database. These are:
Activity 2.1 2.1.1Choose a term/concept from COLUMN B that matches a description in COLUMN A. Write only the letter next to the question number (e.g. 5–F). 2.1.2Describe the three types of people who are involved with a database. 2.1.3List the five characteristics of quality data. 2.1.4A school would like to create a database that can:
a. Work in small groups of four or five learners. Discuss each characteristic of a good database and explain these referring to the scenario above. b. Use a mindmap to present your discussion. UNIT2.2 Problems with DatabasesIf a flat-file database is poorly planned, denormalised and inconsistent, it will create problems when trying to insert, delete or modify the records (tables) in the database. This causes anomalies, which make handling the data increasingly difficult as the database grows. It also makes the data integrity harder to maintain. Trying to make the data consistent once an anomaly occurs can become quite difficult. There are three types of problems that can occur in databases:
Example 2.1 Problems that occur in a database For example, look at the below schema that represents information related to a school: AccountInfo Problems:
Activity 2.2 2.2.1List the three types of anomalies and provide an example for each one. 2.2.2Look at the below schema that represents information related to a hospital: AccountInfo List and explain at least four anomalies with this database. To prevent anomalies you need to normalise the database by efficiently organising the data in a database.
Normalisation is a systematic approach of decomposing tables to eliminate data redundancy and Insertion, Modification and Deletion Anomalies. The database designer structures the data in a way that eliminates unnecessary duplication(s) and provides a rapid search path to all necessary information. It is a multi-step process that puts data into tabular form, removing duplicated data from the relation tables. This process of specifying and defining tables, keys, columns, and relationships in order to create an efficient database is called normalisation. Normalisation will reduce the amount of space a database uses and ensure that data is efficiently stored. Without normalisation, database systems can be inaccurate, slow, and inefficient. They might not produce the data that you expect. In practice this means changing your database so that the following requirements are met:
Keys are used to establish and identify relationships between tables and also to uniquely identify any record or row of data inside a table. A key can be a single attribute or a group of attributes (compositeprimary key), where the combination may act as a key. Keys help us to identify any row of data. When designing a database, the four types of key fields are:
Let’s use this example to understand the four main types of keys: Normalisation rules are divided into the following normal forms: FIRST NORMAL FORM (1NF)For a table to be in the First Normal Form, it should follow the following four rules:
SECOND NORMAL FORM (2NF)For a table to be in the Second Normal Form:
This is where an attribute in a table depends on only a part of the primary key and not on the whole key. For example, a table records the primary keys as student_id and the subject_id of each learner. Only the teacher’s name depends on subject. So, the subject_id, and has nothing to do with student_id. THIRD NORMAL FORM (3NF)A table is said to be in the Third Normal Form when:
This is an indirect relationship between values in the same table. Activity 2.3 2.3.1Explain why normalisation is important for a database. 2.3.2How can normalisation be reached? 2.3.3List and describe the four types of key fields that can be used in a database. 2.3.4How would you fix the below table to reach 1N? 2.3.5Give examples, not mentioned above, of the different key fields. 2.3.6Using the information in the table below, give examples of any two keys. CONSOLIDATION ACTIVITY Chapter 2: Database design concepts 1.Choose the correct answer. a.Which of the following scenarios does NOT need a database? A.Storing the credentials of all Gmail accounts. B.Backing up all the information on your personal computer. C.Storing all the webpages of a website. D.Storing all the information about a business’s inventory. b.Which of the following is a characteristic of a good database? A.Making copies of data in a database for backup. B.Keeping the data and application connected and dependant on each other. C.Preventing errors from occurring in the database. D.Storing data in different formats. c.Which of the following is needed to make each record in a database table different? A.Secondary key B.Foreign key C.Primary key D.Alternative key 2.List the four types of keys used to design a database. Design a table to use as an example. 3.In database design what are the requirements for 1NF, 2NF and 3NF? What are 3 anomalies resolved by normalization?The normalization process was created largely in order to reduce the negative effects of creating tables that will introduce anomalies into the database. There are three types of Data Anomalies: Update Anomalies, Insertion Anomalies, and Deletion Anomalies.
What is deletion anomaly in a relation that is not normalized?A deletion anomaly occurs when you delete a record that may contain attributes that shouldn't be deleted. For instance, if we remove information about the last account at a branch, such as account A-101 at the Downtown branch in Figure 10.4, all of the branch information disappears. Figure 10.4.
What are the different types of anomalies?Anomalies can be classified into the following three categories:. Point Anomalies. If one object can be observed against other objects as anomaly, it is a point anomaly. ... . Contextual Anomalies. If object is anomalous in some defined context. ... . Collective Anomalies.. What are the anomalies in database?A database anomaly is a fault within a database, which can occur because of poor planning or when everything is stored in a flat database. A normalization procedure, which combines and splits tables, is usually sufficient to remove this.
|