Class 11 basic concept of database | CBSE | NCERT | Term 2

 Database Concepts

Hello friends in this chapter we will learn about basic concept of file and database system and find out difference between file base system and database system. We will about basic terminology used in Relational database management system and also learn about keys used in relational database system used now a days. We use NCERT class xi ip book to create this digital content all credit goes to the ncert. Let start Database concept

In this chapter 

• » Introduction 
• » File System 
• » Database Management System 
• » Relational Data Model 
• » Keys in a Relational Database 

The class teacher marks daily attendance of the students in the attendance register. The teacher records ‘P’ for present or ‘A’ for absent against each student’s roll number on each working day. If class strength is 50 and total working days in  a month are 26, the teacher needs to record 50 × 26 records manually in the register every month. As the volume of data increases, manual data entry becomes tedious. 

Following are some of the limitations of manual record keeping in this example: 

• Entry of student details (Roll number and name) in the new attendance register when the student is promoted to the next class.
• Writing student details on each month’s attendance page where inconsistency may happen due to incorrectly written names, skipped student records, etc.
• Loss of data in case attendance register is lost or damaged.
• Erroneous calculation while consolidating attendance record manually. 

The office staff also manually maintain Student details viz. Roll Number, Name and Date of Birth with respective guardian details viz. Guardian name, Contact Number and Address. This is required for correspondence with guardian regarding student attendance and result. Finding information from a huge volume of papers or deleting/modifying an entry is a difficult task in pen and paper based approach. To overcome the hassles faced in manual record keeping, it is desirable to store attendance record and student details on separate data files on a computerized system, so that office staff and teachers can: 

Simply copy the student details to the new attendance file from the old attendance file when students are promoted to next class.

• Find any data about student or guardian.
• Add more details to existing data whenever a new student joins the school.
• Modify stored data like details of student or guardian whenever required.
• Remove/delete data whenever a student leaves the school.

File System 

A file can be understood as a container to store data in a computer. Files can be stored on the storage device of a computer system. Contents of a file can be texts, computer program code, comma separated values (CSV), etc. Likewise, pictures, audios/videos, web pages are also files. Files stored on a computer can be accessed directly and searched for desired data. But to access data of a file through software, for example, to display monthly attendance report on school website, one has to write computer programs to access data from files. Continuing the example of attendance at school, we need to store data about students and attendance in two separate files. shows the contents of STUDENT file which has six columns, as detailed below:

Limitations of a File System 

File system becomes difficult to handle when number of files increases and volume of data also grows. Following are some of the limitations of file system: 

• Difficulty in Access Files themselves do not provide any mechanism to retrieve data. Data maintained in a file system are accessed through application programs. While writing such programs, the developer may not anticipate all the possible ways in which data may be accessed. So, sometimes it is difficult to access data in the required format and one has to write application program to access data. 
• Data Redundancy Redundancy means same data are duplicated in different places (files). In our example, student names are maintained in both the files. Besides, students with roll numbers 3 and 5 have same guardian name and therefore same guardian name is maintained twice. Both these are examples of redundancy which is difficult to avoid in a file system. Redundancy leads to excess storage use and may cause data inconsistency also. 
• Data Inconsistency Data inconsistency occurs when same data maintained in different places do not match. If a student wants to get changed the spelling of her name, it needs to be  changed in SName column in both the files. Likewise, if a student leaves school, the details need to be deleted from both the files. As the files are being maintained by different people, the changes may not happen in one of the files. In that case, the student name will be different (inconsistent) in both the files.
• Data Isolation Both the files presented at  (STUDENT) and at  (ATTENDANCE) are related to students. But there is no link or mapping between them. The school will have to write separate programs to access these two files. This is because data mapping is not supported in file system. In a more complex system where data files are generated by different person at different times, files being created in isolation may be of different formats. In such case, it is difficult to write new application programs to retrieve data from different files maintained at multiple places, as one has to understand the underlying structure of each file as well. 
• Data Dependence Data are stored in a specific format or structure in a file. If the structure or format itself is changed, all the existing application programs accessing that file also need to be change. Otherwise, the programs may not work correctly. This is data dependency. Hence, updating the structure of a data file requires modification in all the application programs accessing that file. 
• Controlled Data Sharing There can be different category of users like teacher, office staff and parents. Ideally, not every user should be able to access all the data. As an example, guardians and office staff can only see the student attendance data but should not be able to modify/delete it. It means these users should be given limited access (read only) to the ATTENDANCE file. Only the teacher should be able to update the attendance data. It is very difficult to enforce this kind of access control in a file system while accessing files through application programs.

Database Management System Limitations faced in file system can be overcome by storing the data in a database where data are logically related. We can organise related data in a database so that it can be managed in an efficient and easy way. 

A database management system (DBMS) or database system in short, is a software that can be used to create and manage databases. DBMS lets users to create a database, store, manage, update/modify and retrieve data from that database by users or application programs. Some examples of open source and commercial DBMS include MySQL, Oracle, PostgreSQL, SQL Server, Microsoft Access, MongoDB. A database system hides certain details about how data are actually stored and maintained. Thus, it provides users with an abstract view of the data. A database system has a set of programs through which users or other programs can access, modify and retrieve the stored data. The DBMS serves as an interface between the database and end users or application programs. Retrieving data from a database through special type of commands is called querying the database. In addition, users can modify the structure of the database itself through a DBMS.

Key Concepts in DBMS 

In order to efficiently manage data using a DBMS, let us understand certain key terms: (A) Database Schema Database Schema is the design of a database. It is the skeleton of the database that represents the structure (table names and their fields/columns), the type of data each column can hold, constraints on the data to be stored (if any), and the relationships among the tables. 

• Database schema is also called the visual or logical architecture as it tells us how the data are organised in a database. 
• Data Constraint Sometimes we put certain restrictions or limitations on the type of data that can be inserted in one or more columns of a table. This is done by specifying one or more constraints on that column(s) while creating the tables. For example, one can define the constraint that the column mobile number can only have non-negative integer values of exactly 10 digits. Since each student shall have one unique roll number, we can put the NOT NULL and UNIQUE constraints on the RollNumber column. Constraints are used to ensure accuracy and reliability of data in the database 
• Meta-data or Data Dictionary The database schema along with various constraints on the data is stored by DBMS in a database catalog or dictionary, called meta-data. A meta-data is data about the data.
• Database Instance When we define database structure or schema, state of database is empty i.e. no data entry is there. After Teacher Office Staff Student Guardian Attendance Database Catalog DBMS Software processes Query DBMS Software access database and its definition Query Result Query Result Query Query  StudentAttendance Database Environment loading data, the state or snapshot of the database at any given time is the database instance. We may then retrieve data through queries or manipulate data through updation, modification or deletion. Thus, the state of database can change, and thus a database schema can have many instances at different times. 
• Query A query is a request to a database for obtaining information in a desired way. Query can be made to get data from one table or from a combination of tables. For example, “find names of all those students present on Attendance Date 2000-01-02” is a query to the database. To retrieve or manipulate data, the user needs to write query using a query language called.
• Data Manipulation Modification of database consists of three operations viz. Insertion, Deletion or Update. Suppose Rivaan joins as a new student in the class then the student details need to be added in STUDENT as well as in GUARDIAN files of the Student Attendance database. This is called Insertion operation on the database. In case a student leaves the school, then his/her data as well as her guardian details need to be removed from STUDENT, GUARDIAN and ATTENDANCE files, respectively. This is called Deletion operation on the database. Suppose Atharv’s Guardian has changed his mobile number, his GPhone should be updated in GUARDIAN file. This is called Update operation on the database. 
• Database Engine Database engine is the underlying component or set of programs used by a DBMS to create database and handle various queries for data retrieval and manipulation. 

Relational Data Model 

Different types of DBMS are available and their classification is done based on the underlying data model. A data model describes the structure of the database, including how data are defined and represented, relationships among data, and the constraints. The most commonly used data model is Relational Data Model. Other types of data models include object-oriented data model, entity-relationship data model, document model and hierarchical data model. This book discusses the DBMS based on relational data model.

In relational model, tables are called relations that store data for different columns. Each table can have multiple columns where each column name should be unique. For example, each row in the table represents a related set of values. Each row of Table 7.5 represents a particular guardian and has related values viz. guardian’s ID with guardian name, address and phone number. Thus, a table consists of a collection of relationships.

• ATTRIBUTE: Characteristic or parameters for which data are to be stored in a relation. Simply stated, the columns of a relation are the attributes which are also referred as fields. For example, GUID, GName, GPhone and GAddress are attributes of relation GUARDIAN.
• TUPLE: Each row of data in a relation (table) is called a tuple. In a table with n columns, a tuple is a relationship between the n related values. 
• DOMAIN: It is a set of values from which an attribute can take a value in each row. Usually, a data type is used to specify domain for an attribute. For example, in STUDENT relation, the attribute RollNumber takes integer values and hence its domain is a set of integer values. Similarly, the set of character strings constitutes the domain of the attribute SName.
• DEGREE: The number of attributes in a relation is called the Degree of the relation. For example, relation GUARDIAN with four attributes is a relation of degree 4. 
• CARDINALITY: The number of tuples in a relation is called the Cardinality of the relation.

Keys in a Relational Database 

The tuples within a relation must be distinct. It means no two tuples in a table should have same value for all attributes. That is, there should be at least one attribute in which data are distinct (unique) and not NULL. That way, we can uniquely distinguish each tuple of a relation. So, relational data model imposes some restrictions or constraints on the values of the attributes and how the contents of one relation be referred through another relation. These restrictions are specified at the time of defining the database through different types of keys as given below: 

Candidate Key A relation can have one or more attributes that takes distinct values. Any of these attributes can be used to uniquely identify the tuples in the relation. Such attributes are called candidate keys as each of them are candidates for the primary key. As shown in Figure 7.4, the relation GUARDIAN has four attributes out of which GUID and GPhone always take unique values. No two guardians will have same phone number or same GUID. Hence, these two attributes are the candidate keys as they both are candidates for primary key. 

Primary Key Out of one or more candidate keys, the attribute chosen by the database designer to uniquely identify the tuples in a relation is called the primary key of that relation. The remaining attributes in the list of candidate keys are called the alternate keys. In the relation GUARDIAN, suppose GUID is chosen as primary key, then GPhone will be called the alternate key. 

Composite Primary Key If no single attribute in a relation is able to uniquely distinguish the tuples, then more than one attribute are taken together as primary key. Such primary key consisting of more than one attribute is called Composite Primary key. In relation ATTENDANCE, Roll Number cannot be used as primary key as roll number of same student will appear in another row for a different date. Similarly, in relation Attendance, AttendanceDate cannot be used as primary key because same date is repeated for each roll number. However combination of these two attributes RollNumber and AttendanceDate together would always have unique value in ATTENDANCE table as on any working day, of a student would be marked attendance only once. Hence {RollNumber,  AttendanceDate} will make the of ATTENDANCE relation composite primary key.

Foreign Key A foreign key is used to represent the relationship between two relations. A foreign key is an attribute whose value is derived from the primary key of another relation. This means that any attribute of a relation (referencing), which is used to refer contents from another (referenced) relation, becomes foreign key if it refers to the primary key of referenced relation. The referencing relation is called Foreign Relation. In some cases, foreign key can take NULL value if it is not the part of primary key of the foreign table. The relation in which the referenced primary key is defined is called primary relation or master relation.

database concepts class 12 notes database concepts pdf class 12 database concepts pdf class 11 database concepts class 11 ncert solutions what is database class 11 dbms class 11 notes database concepts notes database concepts mcq class 11   database concepts pdf class 11 database concepts class 12 notes pdf what is database class 11 mcq on database concepts class 11 class 11 ip database concepts solutions introduction to database concepts pdf dbms class 11 notes database concepts class 11 ppt