DBMS Chapter 1 PDF

8 Introduction to Database CHAPTER Management System IntroductIon Databases and database systems are essential parts of our life. We have been interacting with databases since a time. Recall the...

8 Introduction to Database CHAPTER Management System IntroductIon Databases and database systems are essential parts of our life. We have been interacting with databases since a time. Recall the process of looking for a word in a dictionary or finding the telephone number of a friend from the telephone directory. With the advancement in ever changing technology, computerized databases are being used to store, manipulate, and manage the database. Today, we use databases in almost all spheres of life. When we go to book railway tickets, to search for a book in a library, to get the salary details, to get the balance amount after withdrawal of money from the ATM and so on, this list can run into several pages. Data being stored in databases can be of varied types like text, images, audio and video. This data is then stored and/or processed so as to get meaningful information. Data and Information The raw facts constitutes data. The facts may be related to any person, place, activity or things. It may be stored in the form of text, graphics, audio or video. This data must be processed by any computing machine in a proper way to generate the useful and meaningful information. The examples of data are marks scored by the students, weights, prices, costs, numbers of items sold, employee names, product names, addresses, tax codes, registration, marks etc. Information is the processed or organized form of data. If data is not correct or accurate, the information obtained by processing such data may not be correct. For example, marks obtained by students and their roll numbers is the data, while the report card/sheet is the 2023-24 information. Other forms of information are pay-slips, notes schedules, worksheet, bar charts, invoices, account returns etc. Similarly the temperature recorded is data which can be processed to find out the maximum or minimum temperature of day and night. Also this data is generally organised in tabular form as shown in the Table 8.1. Table 8.1: Data Stored in Tabular Form Day Day Temperature Night Temperature Sunday 35 15 Monday 37 17 Tuesday 36 16 Wednesday 34 15 Thursday 35 17 Friday 35 16 Saturday 33 14 Databases and DBMS A database is a collection of logically related data items stored in an organised manner. The information being stored in a database can be added, modified, deleted or displayed according to the requirements of the user. The software that is used to create, update and retrieve data is known as database management system (DBMS). It facilitates planning and maintenance of the database for the user. Some of the common examples of DBMS are MS Access, Open Office or LibreOffice Base, Oracle, Ingress, MySQL. In this book, we will use LibreOffice Base version 6.4 to learn various aspects of DBMS. We all know that it is easy to remember names of our friends, but it is difficult to retain their phone numbers in our memory. If details like names and phone numbers are stored in a diary or mobile phone in an orderly manner, then it becomes easy to retrieve the phone number of a required person. Thus, the phone book can be considered a simple database and to manage this database electronically, we will require a database management system or a DBMS. Database ManageMent systeM Using LibreOffice Writer 145 2023-24  Let us discuss some of the advantages of DBMS: Organised Storage – The data in the database is stored in an organised manner, so that retrieval of the required data is fast and accurate. Data Analysis – A database helps in analysis of data based on certain criteria. It is easy to find out maximum or minimum value, average or mean using a database. Data Sharing – If the same data set is required for different applications then the database can be shared with other applications. Hence using a database means making once and using it repeatedly for multiple applications. Minimal Data Redundancy – In the event of requiring the same data field in several tables the data field might get repeated in number of tables. This is called as data redundancy. This can be reduced by using DBMS tools. Data Consistency – By minimising data redundancy, chances of inconsistent data being stored is reduced. For example, it should not happen that the name of the student is changed in one table and not in another. Such inconsistency is reduced by using a DBMS. Let us consider a situation where, for example, Murugan looks after the data management of ABC School. There are two tables in his database– Admission table and Library table. ‘Ram Lal Kumar’ wants to change his name to ‘Ram Kumar’ has recently shifted his house. So, he hands over the application for the same to Murugan. In the absence of a DBMS, Murugan has to change the name of the student separately in the admission table and also in the library table. But as we can set a relation between the two tables using a DBMS, any change in one table is automatically reflected in all the related tables. There is no need to add another record with the changed name. The same record will be updated. Hence, this reduces the chances of data redundancy and inconsistency. Admission Table Roll Number Student Name Class Date of Birth Date of Admission 913 Dipak Kumar 10 06/04/2004 25/06/2018 914 Ram Lal Kumar 10 01/03/2004 24/06/2018 146 Domestic Data entry operator – class X 2023-24  Library Table Student Name Name of the Book Date of Borrow Date of Return Dipak Kumar IT 10/12/2018 25/12/2018 Ram Lal Kumar Science 11/11/2018 24/11/2018 Sham Lal Mathematics 12/11/2018 30/12/2018 Increases Efficiency – Since database tables are properly organised, saving, reading and searching data can be carried out efficiently. Increases Accuracy – Since data redundancy and inconsistency can be minimised in a database, the data is retrieved accurately from the database. Increases Validity – Properties of different data fields can be assigned when a database is planned. So whether or not valid data is being entered can be checked at the data entry stage. This increases the validity of the database. For instance, we may set up a restriction while creating the table that the amount of fees being entered should be more than 1000. Security – Unauthorised access can be controlled by assigning passwords to the users. The data might be translated in such a manner that unauthorised users are not able to read it. This is known as encryption. Both these measures increase the security of the database. Data Models A database can be designed in different ways depending on the data being stored. This structure of database is known as data model that describes the manner in which data will be stored and retrieved. A data model consists of components for describing the data, relationships among them and the constraints that hold data. There are different data models such as hierarchical data model, network data model and relational data model. Hierarchical Data Model In this model the data is organized into a tree like structure. The data is stored in the form of records. A record is a collection of fields and its data values. Database ManageMent systeM Using LibreOffice Writer 147 2023-24  notes All these records are linked to each other at various levels, thereby forming a hierarchy. For example, in Fig. 8.1 the data of a company is stored using a hierarchical data model. Likes Ltd. Personal Information Project Information Emp Name Project Number Salary Project Deadline Designation Employee Incharge Fig. 8.1: Hierarchical Data Model Network Data Model In this model, multiple records are linked to same master file. It is also considered as an inverted tree where master is present in the bottom of the tree and the branches contain information linked to the master. In Fig. 8.2, the data of the company is represented using the network data model. Likes Ltd. Personal Project Information Information Emp Project Project Salary Designation Name Number Deadline Fig. 8.2: Network model Relational Data Model This data model is based on the principle of setting relationships between two or more tables of the same database. It is the most commonly used database model. Let us study about this model in detail. 148 Domestic Data entry operator – class X 2023-24 Relational Database Model notes The Relational Database Model was proposed in 1970 by E. F. Codd. Relational database model is the most common type of database model. The data elements are stored in different tables made up of rows and columns. The data in different tables are related through the use of common fields. So relations are set between tables based on common fields. That is why this model is termed as relational database model. Relational Database Terminology Let us get familiar with some of the common terms used in RDBMS. Entity – It is a real world object about which information is to be stored in a database. For example, if we want to store information about an entity Student in a school, then we need to have his admission number, roll number, name, father’s name, date of birth, etc. These details associated with the entity are called attributes. Each entity is a collection of these attributes associated with it. So roll number, name, admission number, etc., are attributes associated with the entity student. These attributes are represented in the form of columns. Table – A table is a collection of logically related records. It is organised as a set of columns, and can have any number of rows. For example, the Employee table can have columns, namely name, designation, department and have records or rows having data of 100 employees. Field or Columns or attributes – A field is the smallest entity in the database. A collection of fields make a record, a collection of records make a table and a collection of tables make a database. Fields are individual record characteristics and are presented as columns within a table. Data values are stored in a database as fields. A field holds the data values of one type of data for several persons. For example, in the Employee table the field “Emp Name” will hold the names of employees of an organisation. Data Values – Data values are the raw data represented in numeric, character or Database ManageMent systeM Using LibreOffice Writer 149 2023-24  alphanumeric form. Examples of data values are ‘Abhinav Bindra’, ‘26’ ‘shooting’, “Chandigarh”, “10-12-2018”, etc. Record or Row – The data values for all the fields related to a person or object is called a record. It is presented as rows within a table. A record holds the data values of all the fields for a single person or object in a table. For example, in the Employee table with the field names as Name, Designation, Department, the data values of all the fields for an employee may be (‘Abhinav’, ‘Manager’, ‘Finance’) and this forms one record. Primary Key – A primary key or simply a key is a field that uniquely identifies a row in a table. The key identifier can be the value of a single column or of multiple columns. The primary key is a unique identifier for the table. The column or combinations of columns that form the primary key have unique values. At any time, no two rows in the table can neither have same values for the primary key nor can data value for such field be left blank. For example, in a student table, each student has a unique roll no., which forms the primary key. If, in a table we use more than one fields to identify a record, it is known as a composite key. For example, we may form a composite key consisting of fields roll no. and name. Relational Database – A relational database is a collection of related tables. For example, in Fig. 8.3, the database contains two related tables. Relational Database Fields Roll Number Student Name Class Date of Birth Date of Admission 913 Dipak Kumar 10 06/04/2004 25/06/2018 914 Ram Lal Kumar 10 01/03/2004 24/06/2018 Roll Number Book Number Date of Barrow Date of Admission 913 555 10/12/2018 25/12/2018 Tables 914 333 11/11/2018 24/11/2018 Records 915 444 12/11/2018 30/12/2018 Fig. 8.3: Relational database 150 Domestic Data entry operator – class X 2023-24  Foreign Key – If a field or a combination of fields of one table can be used to uniquely identify records of another table, then that particular field is known as the foreign key. This foreign key helps to build a relation between two tables. Consider the example given below. Student Registration Table Enrolment Number Student Name Class Date of Birth Date of Admission XX1234567890 Dipak Kumar 10 06/04/2004 25/06/2018 XX1234567891 Ram Kumar 10 01/03/2004 24/06/2018 Primary key: Enrollment Number Student Marks Table Roll Number Maths Science Vocational Enrolment Number 44983 87 75 80 XX1234567890 44990 74 45 75 XX1234567891 Primary key – Roll Number, Foreign key – Enrollment Number In Student Registration Table, ‘Enrolment_Number’ is the primary key and in the Student Marks Table, ‘Roll_Number’ is the primary key, whereas ‘Enrollment_ Number’ is the foreign key. This foreign key can be used to set a relation between two tables. Candidate Key – All the field values that are eligible to be the primary key are the candidate keys for that table. Such fields can neither be left blank nor can have duplicate values. So in the table Student Marks, Enrollment Number and Roll Number both are candidate keys. Alternate Key – Out of the candidate keys, one or two are made as primary keys. The others are the alternate keys. Hence, if Roll Number is made as the primary key, Admission Number is the Alternate key. Objects of an RDBMS An object in a database is a structure or a feature that is used to store, represent or retrieve data. In fact a database is a collection of these objects that work on multiple sets of data related to each other. Various objects in a database are as discussed below: Table – As mentioned before, a table is the basic unit of any DBMS. The data is first stored in tables Database ManageMent systeM Using LibreOffice Writer 151 2023-24  notes in row and column format. A column represents a field or an attribute while a row represents a record. Forms – A form is a feature of a database using which we can enter data in a table in an easy and user friendly manner. A form consists of text boxes, labels, radio buttons, list boxes, check boxes etc. that give a user friendly interface for entering data. The data entered through the forms is stored in tables. Queries – A query is used to retrieve the desired information from the database. In simple terms, it is a question asked from the database. For example, if we want to view the names of only those students who have scored more than 50 marks, then we post a query. The data set matching the given criterion is retrieved from the table and displayed on the screen. Reports – The output of a query may be displayed in the form of reports. The usual result of the query is in the form of rows and columns. But if we want the report to be formal and in proper layout, then we can use the Reports feature of RDBMS. Let’s Practice Consider the following table and answer the questions that follow. Item Discount Name Price Quantity No. (in%) A001 Pen 20 12 0 A003 Pencil 15 5 1 A010 Notebook 50 25 5 From the above table, identify the primary key. Justify your choice. How many fields and how many records does the table have? summary The raw facts constitutes data. Information is the processed or organised form of data. A database is a collection of logically related data items stored in an organised manner. 152 Domestic Data entry operator – class X 2023-24