CBSE Notes for Class 10 Foundation of Information Technology – Database Concepts
A database is a collection of logically related information/data, which is available for one or more users organised in a way, so that it can be easily accessed, managed and updated. It.is actually a place, where related piece of information is stored and various operations can be performed on it by the user.
A database is basically a computer based record/data/information keeping system. Data is raw, unorganized facts and entities relevant to the user need to be processed such as a digital representation of text, numbers, graphical images or sound. The data are stored in such a way that they are independent of the programs used by the people for accessing the data.
e.g. consider the names, telephone numbers and addresses of the relatives etc. You may have recorded this data in an indexed address book or you may have stored it on a hard drive, using application software such as Microsoft Access or OpenOffice.org BASE. Database can be created with the help of given structure:
Information – When data is processed, organised, structured or presented in a given context so as to make it useful is called information.
Character – The value or data are in the form of letter, number or special character.
File – A file is a collection of related record.
NEED FOR A DATABASE
The need for a database arose in the early 1960s in response to the traditional file processing system. In the file processing system, the data is stored in the form of files and a number of application programs are written by programmers to add, modify, delete and retrieve data to and from appropriate files.
However, the file processing system has a number of problems, which are as follows:
- Some information may be duplicate in several files.
- The file processing system lacks the insulation between program and data.
- Handling new queries is difficult, since it requires change in the existing application programs or requires a new application program.
- Unable to maintain data standards and does not provide data sharing.
- In this system, all the integrity rules need to be explicitly programmed in all application programs, which are using that particular data item.
- This system also lacks security features.
- To overcome these problems, database system was designed.
COMPONENTS OF A DATABASE
A database consists of several components. Each component plays an important role in the database system environment.
The major components of database are as follows:
It is raw numbers, characters or facts represented by value. Most of the organisations generate, store and process large amount of data. The data acts as a bridge between the hardware and the software. Data may be of different types such as User data, Metadata and Application Metadata.
It is a set of programs that lies between the stored data and the users of database. It is used to control and manage the overall computerized database. It uses different types of software such as MySQL, Oracle etc.
It is the physical aspect of computer, telecommunication and database, which consists of the secondary storage devices such as magnetic disks, optical discs etc., on which data is stored.
It is the person, who needs information from the database to carry out its primary business responsibilities.
The various types of users which can access the database system are as follows:
Database Administrator (DBA)
A person, who is responsible for managing or establishing policies for the maintenance and handling the overall database management system is called DBA.
The people, who write application programs in programming languages to interact and manipulate the database are called application programmers.
A person, who interacts with the database system to perform different operations on the database like inserting, deleting etc., through menus or forms.
FEATURES OF A DATABASE
Features of database to let you manage your data are as follows:
It is the building block of any relational database model, where all the actual data is defined and entered. A database consists of many tables. Tables (relations) consist of cells at the intersection of records (rows) and fields (columns). Different types of operations are done on the tables such as storing, filtering, retrieving and editing of data. It is also known as file.
Field (Data item)
It is an area (within the record), reserved for a specific piece of data. It is the individual sub-component of one record. It contains set of characters, e.g. customer number, customer name, street address, city, state, phone number, current address etc. Field of a table is also known as column or attribute.
It is the collection of data items of all the fields (information) pertaining to one entity or a complete unit of information, i.e. a person, company , transition etc. Record of a table is also known as row, entity or tuple.
It is an inquiry into the database using the SELECT statement. These statements give you filtered data according to your conditions and specifications indicating the fields, records and summaries which a user wants to fetch from a database. It allows you to extract information from the database based on the conditions that you define in query.
In a database, a form is a window or a screen that contains numerous fields or spaces to enter data. Forms can be used to view and edit your data. It is an interface in user specified layout.
e.g. a user can create a data entry form that looks exactly like a paper form. People generally prefer to enter data into a well-designed form, rather than a table.
When you want to print those records which are fetched from your database, design a report. It is an effective way to present data in a printed format. It allows you to represent data retrieved from one or more tables, so that it can be analysed.
- What is the name given to a collection of logically related data?
- Name the components of a database.
- What is the use of software in database?
- What are the types of users to access the database?
- Explain the following terms:
(a) Queries (b) Attributes (c) Tuples
DATABASE MANAGEMENT SYSTEM (DBMS)
It is a collection of programs that enables users to create, maintain database and control all the access to the database. It is a computer based record keeping system.
The primary goal of the DBMS is to provide an environment that is both convenient and efficient for user to retrieve and store information. It acts as an interface between the application program and the data stored in the database.
DBMS is a software package that manages database, e.g. MySQL, INGRES, MS-ACCESS etc.
DBMS is actually a tool that is used to perform any kind of operation on data in database. It also maintains data consistency in case of multiple users. The purpose of a DBMS is to bridge the gap between information and data. Some basic processes that are supported by a DBMS are as follows:
- Specification of data types, structures and constraints to be considered in an application.
- Storing the data itself into persistent storage.
- Manipulation of the database.
- Querying the database to retrieve desired information.
- Updating the content of the database.
A short list of database applications would include:
- Medical records
Relational DataBase Management System (rdbms)
RDBMS is a type of DataBase Management System that stores data in the form of relations (tables). Relational databases are powerful, so they require few assumptions about how data is related or how, it will be extracted from the database.
An important feature of relational database system is that a single database can be spread across several tables. Base, Oracle, DB2, SAP, Sybase, ASE, Informix, Access etc. are the examples of RDBMS.
WORKING OF A DATABASE
Database is created to operate large quantities of information by input, store, retrieve and manage the information. It is a centralised location which provides an easy way to access the data by several users.
It does not keep the separate copies of a particular data file still a number of users can access the same data at the same time.
As the given diagram shows, DBMS works as an interface between the user and the centralized database. First, a request or a query is forwarded to a DBMS which works (i.e. a searching process is started on the centralized database) on the received query with the available data and if the result is obtained, it is forwarded to the user.
If the output does not completely fulfill the requirements of the user then a rollback (again search) is done and again search process is performed until the desired output is obtained.
Advantages of a Database/DBMS
The centralized nature of database system provides several advantages, which overcome the limitations of the conventional file processing system.
These advantages are as follows:
- Reduce data redundancy Redundancy means ‘duplication of data’. This eliminates the replication of data item in different files, extra processing required to face the data item from a large database. This also ensures data consistency and saves the storage space.
- Enforcing data integrity It means that the data contained in the database is accurate and consistent. Integrity constraints or consistency rules can be applied to database, so that the correct data can be entered into the database.
- Data sharing The data stored in the database can be shared among multiple users or application programs.
- Data security The DBMS ensures that the access of database is done only through an authorized user.
- Ease of application development The application programmer needs to develop the application programs according to the user’s needs.
- Backup and recovery The DBMS provides backup and recovery sub-system that is responsible to recover data from hardware and software failures.
- Multiple views of data A view may be the subset of database. Various users may have different views of the database itself.
- Enforced standards It can ensure that all the data follow the applicable standards.
- Data independence System data descriptions are independent from the application programs.
Disadvantages of a Database/DBMS
There are many advantages of database, but database also have some minor disadvantages.
These disadvantages are as follows:
- Cost of hardware and software Through the use of a database system, new costs are generated due to additional hardware and software requirements.
- Complexity A database system creates additional complexity and requirements.
- Database failures If database is corrupted due to power failure or it is corrupted on the storage media, then our valuable data may be lost or the system will stop working.
- Lower efficiency A database system is a multi-user , software, which is less efficient.
The key is defined as the column or the set of columns of the database table which is used to identify each record uniquely in a relation, e.g. if a table has id, name and address as the column names then each one is known as the key for that table. The key field is a unique identifier for each record, e.g. In Student table, you could use a combination of the lastname and firstname (or perhaps lastname, firstname to ensure you to identify each student uniquely) as a key field.
Types of Key Fields
The following are the types of key fields available in the DBMS system:
A field or a set of fields that uniquely identify each record in a table is known as a pi .ary key. Each relation has at least one column for which each row that must have a unique value. Only one column attributes can be defined as a primary key for each table.
A primary key must possess the following properties:
- It does not allow null values.
- It has a unique index.
- It allows numbers and text both.
e.g. In the Student table, studentld works as a primary key because it contains Ids which are unique for each student.
The set of all attributes which can uniquely identify each tuple of a relation are known as candidate keys. Each table may have one or more candidate keys and one of them will become the primary key. The candidate key of a relation is always a minimal key.
e.g. Column studentld and the combination of firstname and lastname work as the candidate keys for the Student table.
A candidate key must possess the following properties:
- For each row, the value of the key must uniquely identify that row.
- No attribute in the key can be discarded without destroying the property of unique identification.
From the set of candidate keys after selecting one of the key as a primary key, all other remaining keys are known as alternate keys.
e.g. From the candidate keys (studentld, firstname and lastname), if studentld is chosen as a primary key, then the firstname and lastname columns work as alternate keys.
A field of a table (relation) that references the primary key of another table is referred to as foreign key. The relationship between two tables is established with the help of foreign key. A table may have multiple foreign keys and each foreign key can have a different referenced table. Foreign keys play an essential role in database design, when tables are broken apart, then foreign keys make it possible for them to be reconstructed.
e.g. courseld column of Student table (reference table) works as a foreign key as well as a primary key for Course table (referenced table).
DESIGNING A DATABASE
Database design is the process of developing a detailed data model of a database. It describes many different parts of design of an overall database system. It is important to take time while designing a database because good database design is a keystone for creating a database that performs every task effectively, accurately and efficiently.
Steps to Design a Database
There are various steps to design a database which are as follows:
Step 1 – Determine the purpose of your database The first step of designing a database is to determine the purpose and mechanism to design and use it.
Step 2 – Determine the tables Tables are one of the most important elements of a database, consist of rows and columns. To create a well-defined database, you have to keep some conditions which are as follows:
- A table should not contain duplicate information.
- Each table should contain information about one subject.
e.g. One table is used to contain the personal information of the students and the other is used to contain the marks scored by the student.
Step 3 – Determine the fields After creating a table, you need to decide the type and number of fields required for the tables in your database. Each field in a table contains individual facts about the table’s subject.
e.g. A customer table may include company name, address, city, state and phone number fields.
Step 4 – Identify the primary key in a table From the fields of table, you need to identify a primary key which uniquely identifies each individual record of the table. The primary key helps you to reduce data duplication in the table.
Step 5 – Determine the relationship between tables In this step, you need to determine relationship between two or more tables in your database. You can set-up a relationship between tables on the basis of common field between them. Establishing a relationship allows you to fetch any information from both the tables.
Step 6 – Refine the design After you have designed the tables, fields and relationships, its time to study the design and detect any faults that might remain.
Step 7 – Enter data and create other database objects When you are satisfied that the database structure meets the goals you needed, add all your existing data to the tables.
BASIC DATABASE TOOLS
It is a collective term for tools, utilities and assistants to perform database administration, development and performance tuning for all major DBMS platforms.
Some database tools are as follows:
- org BASE (OO.o BASE) It is a Relational DataBase Management System (RDBMS), which establishes the necessary facilities to make open source technology available to the developer communities world wide. The default extension of BASE database is .odb.
- MS-Access It is also known as Microsoft Office Access which is a powerful tool designed by Microsoft to create and format databases. The default extension of access database is .accdb.
- The purpose of DBMS is to bridge the gap between …………….. and ……………..
- In which type of system, data is stored in the form of relations?
- Which key of a relation is always a minimal key?
- Define the term of candidate key.
- What are the basic database tools? Write any two names of database tools.