Database Management (IT Grade 11)
Overview
This is a set of notes in the Information Technology subject. The central topic discussed is Database Management
Characteristics of quality data
DATABASES
Data is unprocessed numbers, signals or facts and in order for humans to use it and for computers to share it, the data needs to be processed and organised into meaningful information.Once data has been processed into information, this information can be interpreted to provide knowledge, which allows yoou to make decisions based on that knowledge.
A database is designed to store data (facts), these facts are organised within the database so that they can be sorted or extracted in response to a user’s requirements which will allow them to make a decision.
If you were given a History project to research the history of your city, you would be able to search the database for books on written about this subject. With a list of the available books the database provides, you would be able to decide which of them to use for your research to get the information you required for your project.
VALIDATE YOUR DATA
Data validation refers to the process in which you check whether the data is accurate, in the correct format or of the correct type before recording it in your database. This ensures that the data in your database is consistent and accurate. Since data validation is usually created by a programmer, there are thousands of different types of data validation that can be done.
For more information about the database design tips, please follow this link: Database Design Tips
Creating a simple database
The first step to building the SMS database is to create the database. To do this, you need to:
1. Install a database software
KEY TERMINOLOGY IN DATABASE
| Field | A field is part of a record and contains a single piece of data for the subject of the record. |
| Primary key | A field that uniquely identifies each record in the table. |
| Composite primary key | A set of more than one key that, together, uniquely identifies each record. |
| Foreign key | A foreign key is a primary key in one table that appears as a field in another table. |
| Datatype of fields | The fields within a database often require a specific type of data to be input. Data-type that each field stores has to be declared/set. Common datatypes are: text, number, data, time Yes/No,etc. |
| Record | A record is composed of fields and contains all the data about one particular person, company, or item in a database |
| Table | A database table is composed of records and fields that hold data. Each table in a database holds data about a different, but related, subject. |
| Relationship | Is a situation that exists between two relational database tables when one table has a foreign key that references the primary key of the other table. Relationships allow relational databases to split and store data in different tables, while linking disparate data items |
CREATING A DATABASE
Now that your database has been created, you can start thinking about your first table. However, before you can create it you need to answer these four questions:
- What table are you creating?
- Which fields will you include in your table?
- What data type will each of these fields be?
- Which column will be the primary key for your table?
Let’s work through these questions and the practical steps one-by-one.
ENTERING AND ADDING DATA ON A TABLE
MODIFYING DATA ON A TABLE
In general, you should think carefully before deleting any records. Since databases are relational, deleting a field on one record might cause errors on many other tables. In general, it might be better to add a Boolean field to your table that you can use to enable or disable users. Next Section
Working with data & Creating relationships
DESIGNING RELATIONSHIPS
To plan the relationships between your tables, you can create an Entity Relationship Diagram.
The main value of carefully constructing an ERD is that it can readily be converted into a database structure.
There are three components in ERD.
- Entities: Number of tables you need for your database.
- Attributes: Information such as property, facts you need to describe each table.
- Relationships: How tables are linked together.
This section will look at how an ERD can be drawn in two phases:
- First, the tables will be drawn
- Second, the relationships between the tables will be drawn.
Once you have your ERD, your tables and some mock data in your tables, you are ready to set up the relationships between your tables.