402_IT_X-154-230.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  The software that is used to create, update and notes retrieve data is known as database management system (DBMS). Some of the common examples of DBMS are MS Access, Open Office or LibreOffice Base, Oracle, Ingress, MySQL. Data Model is the structure of database and it describes the manner in which data will be stored and retrieved. There are different data models, such as hierarchical data model, network data model and relational data model. In Hierarchical Data Model, the data is organised into a tree like structure. The data is stored in the form of linked records. In Network Data model, multiple records are linked to same master file. The Relational data model is based on the principle of setting relationships between two or more tables of the same database. Entity is a real world object about which information is to be stored in a database. The details associated with the entity are called attributes. A table is a collection of logically related records. It is organised as a set of columns, and can have any number of rows. A field is the smallest entity in the database. These are individual record characteristics and are presented as columns within a table. Data values are the raw data represented in numeric, character or alphanumeric form. 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 primary key is a field that uniquely identifies a row in a table. This foreign key helps to build a relation between two or more tables in a database. All the field values that are eligible to be the primary key are the candidate keys for that table. Database ManageMent systeM Using LibreOffice Writer 153 2023-24  notes Out of the candidate keys, one or two are made as primary keys. The others are the alternate keys. An object in a database is a structure or a feature that is used to store, represent or retrieve data. The various objects in a database are tables, forms, reports and queries. 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 query is used to retrieve the desired information from the database. The output of a query may be displayed in the form of reports. Check Your Progress A. Multiple choice questions 1. Which of the following can be considered as an example of a database? (a) Dictionary (b) Telephone directory (c) Marks Register (d) Newspaper 2. Which of the following is NOT a DBMS? (a) MS Access (b) Open Office Base (c) MS Excel (d) MySQL 3. DBMS stands for ______________________. (a) Data and Books Management System (b) Database Management System (c) Duplicate Books Management System (d) Data Management Multi System 4. Which of the following data models sets a relation between the two or more tables? (a) Relational Data Model (b) Network Data Model (c) Hierarchical Data Model (d) Connection Data Model 5. The details associated with an entity are called ____________. (a) Table (b) Attributes (c) Records (d) Primary key 154 Domestic Data entry operator – class X 2023-24  6. A __________ is represented as rows in a table. notes (a) field (b) attribute (c) record (d) candidate key 7. In which of the following forms can a data value be represented? (a) Numeric (b) Character (c) Alphanumeric (d) All of the above 8. Which of the following uniquely identifies a row in a table? (a) Primary key (b) Alternate key (c) Foreign key (d) Candidate key 9. A ___________is a feature of a database using which we can enter data in a table in an easy and user friendly manner. (a) query (b) report (c) form (d) field 10. A _____________ is a question asked from a database. (a) query (b) report (c) form (d) field B. State whether the following statements are True or False 1. A database cannot be organised. 2. Data is the collection of raw facts. 3. A table can be created without a primary key. 4. Two tables can be related in a network data model. 5. MS Access is an example of a database. C. Fill in the blanks 1. The raw facts constitutes _____________. 2. An _____________ is a real world object about which information is to be stored in a database 3. The output of a query may be displayed in the form of ________. 4. The data values for all the fields related to a person or object is called a _________. 5. All the field values that are eligible to be the primary key are the ___________ keys for that table Database ManageMent systeM Using LibreOffice Writer 155 2023-24  notes D. Answer the following questions 1. Define the terms (a) Database (b) Data redundancy (c) Report 2. Give one point of difference between (a) Data and Information, (b) Form and Query, (c) Network and hierarchical data model 3. Give any four advantages of a DBMS. 4. Consider the table given below and answer the questions that follow Table: Library Book_Id Book Name Author Name Price Publisher F001 Pride and Prejudice Jane Austen 550 ABC S004 Amazing Astronomy E. Shane 1050 ABC C005 IT and Mankind MHA Diwaan 2500 HYM (a) Name the fields in the given table. (b) Which field should be made the primary key? (c) Is there any alternate key in the table? (d) How is primary key different from foreign key? Explain with example. 156 Domestic Data entry operator – class X 2023-24  9 CHAPTER Starting with LibreOffice Base IntroductIon In the previous chapter we have learned about databases and database management system (DBMS). LibreOffice Base is a free and open source DBMS. It can be downloaded from www.libreoffice.org and is available for both Linux and Windows operating systems. Data has to be stored in an organised manner using a DBMS. Also, the data being stored can be a text, number, date or in any other form. So, we need to understand different types of data that can be stored in a table. The data types of the fields have to be specified while creating tables in a database. Thereafter valid data is entered and stored in a table. Data Types The nature of data to be entered for various fields are of different types. For example, names are stored in the form of text, age in numbers, fees in decimal numbers, date of birth in date format and so on. A data type refers to the type of data that will be stored in that particular field. The memory size of a field varies according to its data type. Some commonly used data types are described below. Text Data Type – The text data is a combination of letters, numbers or special characters. No arithmetic calculations can be performed on text data. Examples of text data type is PAN Card Number, Name, Marks, etc. 2023-24  The table below lists various data types that can store textual data. Table 9.1: Text Data Types Name Data type Description Stores up to the maximum length indicated by user. It is used to store some descriptive data having more than 255 characters. Memo data type allows to store text data up to Memo LONGVARCHAR 64,000 characters. Stores exactly the length specified by user. Character data type is used to enter fixed number of characters. It can be used for license number, passport number as they have Text (fix) CHAR fixed number of characters. Stores upto the specified length. The number of bytes allocated depends on the number of characters entered by the user. For example, the address is defined as varchar (50), and if the address entered by the user is of 20 characters Text VARCHAR then only 20 bytes will be occupied in the database. Numeric Data Type – Numeric data types consists of numbers. The numbers can be integer or real numbers on which any type of arithmetic calculations can be performed. For example, 10, -34.8, 90.6789 , -86 are of numeric data type. Table 9.2 lists different numeric data types along with the number of bits/bytes it uses and its range. Table 9.2: Numeric data types Name Data type Signed Range Tiny Integer TYNYINT No 0-255 Small Integer SMALLINT Yes -32768 to 32768 Integer INTEGER Yes -2.14×109 to 2.14×109 BigInt BIGINT Yes -2.3×1018 to 2.3×1018 Number NUMERIC Yes Unlimited Decimal DECIMAL Yes Unlimited Float FLOAT Yes Real REAL Yes 5×10(-324) to 1.79×10(308) Double DOUBLE Yes Currency Data Type – The currency data type indicates the monetary values and can be stored using currencies of various countries. For example $100, £ 500 or Rs. 25.50. Date Data Type – This data type is used to indicate dates and time. For example 12/25/2019, 08:45 AM. The data and time can be stored in various formats. Table 9.3 list various forms of date data type. 158 Domestic Data entry operator – class X 2023-24  Table 9.3: Various forms of Date data type Name Description Date Stores the year, month and day as it is stored in the system. Time Stores the time of the day as hour, minute and second. Timestamp Stores date and time information at once. Boolean – In boolean data type there can be only two values- True or False. This also can be given in multiple formats like Yes/No, True/False, On/Off. Binary – The Binary data type used to store digitized images and sounds that comes as long string of zeros and ones. It is possible to store photos of the products or employees, or sound snippets or voice messages in Base database. Starting with LibreOffice Base Annual Sports Day is being held in Ruhi’s school in which various sports competitions will be conducted. Ruhi has been asked to create a database consisting of various sports activities and its players. She decides to create a database named, ‘Sports Day’ consisting of a table ‘Events’ that contains the following fields: Event_Id - to store Event identification no like E001, E002, etc. Event Name - to store name of the event Date - to store date on which the event will be conducted Winner 1 Name - name of the winner Winner 1 Points - points earned y the winner Let’s Practice What should be the data type of the fields of the table Events that has to be created by Ruhi? Which field should be made the primary key? Once the table with its fields has been decided, Ruhi decides to start creating the database using LibreOffice Base. Follow the following steps to create the database. Step 1. Start the LibreOffice Base as per the standard process of starting the application in Windows or Linux. In Windows, click Start > LibreOffice or double click on the LibreOffice icon on the desktop or Select Base Database option from the bottom left panel. Database ManageMent systeM Using LibreOffice Writer 159 2023-24  In Ubuntu Linux, click on the Base icon located on the left panel as shown in Fig. 9.1(a) or click on the Show Applications as shown in Fig. 9.1(b) to search for its icon. Type Base in the search box. The LibreOffice Base application icon will be displayed as shown in Fig. 9.1(c). Click on the Base icon to open the Base application. Step 2. A Database Wizard opens as shown in Fig. 9.2. It allows you to create a new database or open an existing database. Fig. 9.1(a): Clicking on the Base icon on the left panel to open Base Fig. 9.1(b): Clicking on Show Applications to search for Fig. 9.2: LibreOffice Database Wizard installed packages Tip: To open LibreOffice Base, you may search the application Base on your computer and click on the LibreOffice Base icon. Step 3. Since we want to create a new database, so click Create a new database radio button. If any database that has already been created is to be opened, then select Open an Existing Fig. 9.1(c): Clicking on the searched Base icon Database option to select the database to be to open Base opened. Thereafter click Next button. Step 4. The Next step gives the option to register our database with LibreOffice.org. If we register the database, then our database is made public and hence can be accessed by other people. As of now, we would not like to register, so we click and select the radio button with option, ‘No, do not register the database’. 160 Domestic Data entry operator – class X 2023-24  Fig. 9.3: Decide How to Proceed After Saving the Database Step 5. This step also asks whether you want to open the database for editing or want to create a table using the wizard. The option Open the database for editing is already selected. Step 6. Click Finish button to complete the database creation process. The Save As dialog box appears. Fig. 9.4: Save As dialog box Step 7. Browse for the drive and folder where you want to store your database. Step 8. Type the name (Sports Day) in the File name text box. Step 9. Click Save button. The database in Base is saved with an extension.odb. Also note that if no name is given to the database, then it is saved with a default name as NewDatabse.odb. User Interface of Libre Office Base Once a database is created, the screen as shown in Fig. 9.5 appears. This is the User Interface of Open Office Base. The various components of the Base User Interface are discussed below: Fig. 9.5: User Interface of LibreOffice Base Database ManageMent systeM Using LibreOffice Writer 161 2023-24  Title Bar – The title bar displays the name of a database and an application in which it is made. The windows buttons to maximize, minimize or close the window are located on the right corner of the title bar. Menu Bar – The menu bar appears below the title bar. It consists of seven menu items – File, Edit, View, Insert, Tools, Window and Help. All these menu items contain commands that help to perform various operations on the database. Standard Toolbar – It is located below the menu bar. It is used to access frequently used tools. Status Bar – It is located at the bottom of the interface window. It displays information about the type of view of the object in the database. Database Pane – The database pane is located on the left side of the window. LibreOffice Base is the collection of related data objects known as Tables, Forms, Queries, Reports and application modules. Depending on the object that is selected, the respective Task Pane and Object Area displaying the created object appears. Opening a Database To open an already created database, click File > Open. The Open dialogue box appears as shown in Fig. 9.6. Browse for the folder where the database to be opened is stored. Select the desired database and click on Open button. Tip: Use the keyboard shortcut key Ctrl+O to open an already existing database. Fig. 9.6: Opening database from menu option Creating a Table Once the database is created, we can start working with objects of the database. First and foremost is the creation of the table and then entering data in the table. A table in LibreOffice Base can be created using a wizard or using the Design view. Creating a Table using a Wizard A table can be created at the time of creating a database or after creating database. To create a table 162 Domestic Data entry operator – class X 2023-24 at the time of creating a database in database wizard, select the radio button with option “Create tables using the table wizard”, then click on the Finish button. Since our database is already open, so we will select and click If the database is opened we can select Use Wizard to create a table option from the Tasks Pane. The Table Wizard dialog box will open as Fig. 9.7: Table Wizard shown in Fig. 9.7. Follow the instructions in the wizard to create the table with desired fields. The Table Wizard of Base consists of ready-made tables. Click the Sample tables list box and select select any one table, say Customer from the drop down list. After selecting the Customer table, various fields appears under Available Fig. 9.8: Fields shifted from Available fields to Selected fields Fields. The required fields from Available fields box can be shifted one by one to Selected fields box using > button as shown in Fig. 9.8. You can select all the fields from Available fields to Selected fields in one stroke by clicking on >> button. Click on Next button. It will move you to step 2. Set types and formats as shown in Fig. 9.9. Fig. 9.9: Setting types and formats Then click on Next button. It will move you to step 3. Set primary key. Let us set the CustomerID field to primary key as shown in Fig. 9.10. Click on Next button, that will take you to the complete the process of creating table using wizard and display the screen as shown in Fig. 9.11. This screen will give you three choices. By default the “Insert data immediately” option selected. Fig. 9.10: Setting primary key Database ManageMent systeM Using LibreOffice Writer 163 2023-24  Click on the Finish button to complete the process of creating the table and using wizard. The next screen will allow to enter the data as per the selected fields. The data sheet view interface as as shown in Fig. 9.12 will open that allow to enter the data in the Customer table. Creating Table in Design View Creating the table using wizard Fig. 9.11: Creating table using wizard restricts us to use the same fields in the per-designed tables. It may not solve the purpose in real scenario, as we may require to create a table with the different fields for our purpose. Creating tables using Design View gives us more flexibility to do our work. To create table using Design View, in Fig. 9.12: Datasheet view of Customer table main Database window, click on the option Create Table in Design View in the Tasks Pane and Table Design Window will be opened as shown in Fig. 9.13. The screen is broadly divided into two sections or horizontal panes. The upper half consists of a grid structure with three columns Field Name, Field Type and Description. Field Name – It is the name of the Fig.9.13: Creating Table in Design view field assigned at the time of creation of table. Field Type – It allows to assign a data type to the field. Description – It allows to describe the purpose of the field. It is not the part of database table, but it is meant for the user to understand the purpose of the field. We may or may not enter field description. The Field Properties pane is located at the bottom half of the window. It displays the field properties assigned by the database designer. These properties can also be 164 Domestic Data entry operator – class X 2023-24 changed as per the requirement and are used to control and validate the data that is to be entered. Let us help Ruhi to create a table named Events using in the option Create Table in Design view in the Sports Day database. Follow the follow the following steps to do so. Step 1. Type the first field name (EventId) in the Field Name column. Press Tab key. The cursor moves to the second column i.e. Field Type. Step 2. The Field Type column contains a list box. As you click on the down arrow, it appears and we Fig. 9.14: Selecting data type for field can select the desired data type from the list box. Select the datatype (Varchar). Step 3. Observe that certain properties appear in the Field Properties Pane as the data type is selected. Some of the properties are Entry required, Length, Default value, Format example. Set the desired properties for the entered field. Step 4. Press Tab key to move to the next column. Add any description if you want in the third column. Step 5. Once the properties for the field are set, press Tab key to move to Fig. 9.15: Fields entered using Creating Table next row. in Design View Step 6. Enter the next field by repeating steps 1,2 and 3. Repeat the process for adding all fields in the table. Fig. 9.15 shows the Design View with all the fields. Setting the Primary Key As you have learned that every table must have a primary key that uniquely identifies a record in the table. To make a particular field as the primary key, place the mouse pointer before the field name, say Event Id in our above example and right click. A pop up menu appears. Database ManageMent systeM Using LibreOffice Writer 165 2023-24  Select the Primary Key option from pop up menu as shown in Fig. 9.16. A key icon appears before the field name indicating that it is a primary key. Saving a Table After creating the table you need to save it on the disk. To save the table click on the save button or follow menu option File > Save As. A Save As dialog box is displayed as shown in Fig. 9.17. Enter the name of table and click on OK button. Fig. 9.16: Assigning primary key More to know To set a composite Fig. 9.17: Saving a table key, i.e. a primary key consisting of two Tip: Press Ctrl + S to save the table or click Save icon fields, keep the Ctrl from the toolbar. If the table is being saved after making key pressed and then some changes, simply select File-> Save option. click on multiple Once the table design is complete, click on Close fields to select them. Thereafter right click button on the toolbar to return to the Database screen. on selected fields and The name of the table will appear in the Tables Object choose Primary Key Area as shown in the Fig. 9.18. option from the pop up menu. Fig. 9.18: Event table added to the database Entering Data in a Table To enter data in the table, double click on the created table Events icon in the Tables Object Area on the database screen. Alternatively, we can open the table by right clicking on the desired table and then selecting 166 Domestic Data entry operator – class X 2023-24 the Open option from the drop down menu. The datasheet view of the table will appear as shown in Fig. 9.19. It displays the field names in the top row. These fields are displayed in the same order as they were added while creating the table. Fig. 9.19: Datasheet view of Events table If the number of fields are more and cannot fit in the single row, you can use the horizontal scroll bar to view all the fields. The cursor will be blinking in the second row. Start typing the data value for each field. Use Tab to move to next field. Once all the data values are entered for a single record, the cursor moves to the next record. This process is Fig. 9.20: Data entry in Event table called as data entry. Enter 4-5 records in the Events table as shown in Fig. 9.20. Navigating through the Table The black pointing arrow ( )just before the field name is the record pointer. To navigate through various records of the table, we use the navigation box present at the bottom of the datasheet window as shown in Fig. 9.21. It indicates a current record of the table at any given time. Fig. 9.21: Navigation box The various components of Navigation Box are as follows: Record Selector Box – T his is the text box where the currently active record number is displayed. We may enter the record number that we want to see in this text box. Navigation Buttons – These are used to scroll vertically in the table. Database ManageMent systeM Using LibreOffice Writer 167 2023-24  Press to move to the first record Press ► and ◄ to move to the next and previous records respectively. Press to move to the last record Fig. 9.20 shows the datasheet view after entering four records in the table. Note that the record pointer is on fourth record, the Navigation Bar shows the “Record 4 of 4”. Editing Data To edit or modify the previously entered data simply place the cursor on the field value that has to be edited to edit and enter the new value. The Edit icon ( ) appears before the record that is being edited. This icon is displayed till the table is saved after making the required changes. Press Esc key to cancel the corrections made and restore the original contents. Deleting Records from Table To delete any record from the table, open the table and select the record to be deleted. The record can be deleted by pressing the Del key from the keyboard or selecting the Delete Record option from the Edit menu. Alternatively a record can also be deleted by right clicking on the record and clicking on the Delete Rows option from the pop up menu. Attempting to delete the record will display the Confirmation box as shown in Fig. 9.22. Clicking on Yes button will finally delete the record, while clicking Fig. 9.22: Delete record alert on the No button will not delete the record. Sorting Data in the Table Data in a table can be arranged in ascending or descending order. This process of arranging the records in particular order on any filed is called as sorting. Follow the following steps to sort the table. Step 1. Open the Event table in datasheet view and select the field on which you want to sort. Fig. 9.23: Sorting the Event table Fig. 9.23 shows that the field “Points” is selected on Points field to sort the records in ascending order. 168 Domestic Data entry operator – class X 2023-24 Step 2. From the tool bar click Sort Ascending icon if the table has to be sorted in ascending order of selected field. Alternatively select Sort Descending icon if the table has to be sorted in descending order of selected field. Fig. 9.24: Event table after Step 3. The table will be sorted in the ascending order sorting on Points field of points as shown in Fig. 9.24. Sometimes we may need to sort the table based on more than one fields. In such case, click Sort icon on the toolbar. The Sort Order dialog will be displayed as shown in the Fig. 9.25. Select the appropriate field name and field value according to which the records are to be sorted. Choose the order of sorting ascending or descending as required from the Order drop down box. Multiple fields may be Fig. 9.25: Sort Order dialog box selected in this dialog box. Click OK button once done. Closing LibreOffice Base To close the application window of LibreOffice Base, click on the File > Close or click on the cross (x) button of the LibreOffice Base window. Let’s Practice There are 5 houses in Ruhi’s school – Ganga, Yamuna, Satluj, Beas and Narmada. Once student can participate from each house in a particular event. Create a table Participants with following fields. 1. Event_Id 2. Event Name 3. Ganga 4. Yamuna 5. Satluj 6. Beas 7. Narmada Also add minimum five records in the table. summary Base is a free and open source database component of LibreOffice suit. It can be downloaded from www.libreoffice.org A data type refers to the type of data that will be stored in that particular field. Database ManageMent systeM Using LibreOffice Writer 169 2023-24  notes Various in Base can be categorized into Text , Numeric, Currency and Date. LibreOffice Base is the collection of related data objects known as Tables, Forms, Queries, Reports and application modules. Depending on the object that is selected, the respective Task Pane and Object Area displaying the created object appears on the User Interface Window. A table in LibreOffice Base can be created using a wizard or using the Design view. Practical Exercise Ananthu wants to create a directory containing data – Serial No, Name, Mobile Number, Email id, Date of birth of his friends. Create a table using LibreOffice Base. Make Serial No as primary key. Enter minimum 5 records in the table. Sort the table in alphabetical order of name. Check Your Progress A. Multiple choice questions 1. Which of the following is NOT a type of text data type? (a) Memo (b) Varchar (c) Float (d) Char 2. A currency data type can only store monetary data that is in dollars. (a) True (b) False (c) Neither a nor b (d) Both a and b 3. Which of the following data can a date data type store? (a) Date (b) Time (c) Both date and time (d) Neither date nor time 4. Which of the following is true about LibreOffice Base? (a) It is a spreadsheet software (b) It is free and open source software (c) It can store only character data (d) It is a licensed software. 170 Domestic Data entry operator – class X 2023-24  5. Which of the following methods can be used to create a notes table in Base? (a) Using a table wizard (b) Design View (c) Both a and b (d) Neither a nor b 6. The related objects of a database can be seen in ____________ pane of the Base Database window. (a) Database (b) Task (c) Title Bar (d) Menu Bar 7. Which is the shortcut key to open an existing database? (a) Ctrl+ D (b) Ctrl+O (c) Ctrl+E (d) Ctrl+F 8. The Design view of Table Creation window in LibreOffice Base is divided into ______ sections or panes. (a) 2 (b) 3 (c) 4 (d) 5 9. While entering records in a table, we can move to the next field by pressing the _________ key. (a) Tab (b) Ctrl (c) Enter (d) Shift 10. Which of the following is true about primary key of a table? (a) Every table must have a primary key (b) The data values in primary key field cannot be duplicated. (c) A primary key field cannot be left blank (d) All of the above B. State whether the following statements are True or False 1. The text data can contain special characters. 2. Memo data type can be used to store descriptive data. 3. A Boolean data type can have two or more than two values. 4. We cannot store audio data in LibreOffice Base. 5. The properties of a field change according to the data type selected. 6. Field description may or may not be entered while designing a table. 7. is pressed to move to the last record. 8. appears when the record is being edited. Database ManageMent systeM Using LibreOffice Writer 171 2023-24  notes 9. A table once created in a database cannot be edited. 10. Sort dialog box can only help to sort data in ascending order. C. Fill in the blanks 1. A data type refers to the type of data that will be stored in that particular field. 2. The _____________ data is a combination of letters, numbers or special characters. 3. ________ data type can be used to store Aadhar number. 4. The __________ data type used to store digitized images. 5. The shortcut key to save a table is _______________. 6. ______________ on the Base Interface Window displays information about the type of view of the object in the database. 7. A __________ icon appears before the field name indicating that it is a primary key. 8. The data can be entered in a table only in __________ view. 9. The black pointing arrow just before the field name in a table is called ____________. 10. The process of arranging the records in particular order on any filed is called _______________. D. Answer the given questions 1. Differentiate between: (a) Memo and Varchar data type (b) Number and Decimal data type (c) Design View and Datasheet view of a table 2. Name the menu items present on the Base User Interface, 3. Label the components – Title Bar, Database Pane, Tasks Pane, Status Bar of the LibreOffice Base User Interface. 4. How can we define a primary key in a table? 5. Write steps to sort the table in descending order of primary key. 6. What is the use of navigation box with respect to tables in a database? 172 Domestic Data entry operator – class X 2023-24  10 CHAPTER Working with Multiple Tables IntroductIon We have learned to create tables in a database. Once the tables are added in a database, you may require to edit or delete the table. Also relations are set up between the tables to control data redundancy and inconsistency. This helps in proper maintenance of a database by checking that neither the records are duplicated nor there is variable data value for a particular field in two or more tables. If you set up relations between tables, then adding or updating a record in one table reflect the changes in all the related tables. Editing and Deleting Tables In the previous chapter, we have created a Customer table using a wizard in Sports Day database. It is possible to copy, rename, edit and delete the table of database by right clicking on the table name and using the appropriate option from the pop up menu. Editing a table involves the task such as adding a new field or removing any field in a table or to alter any of the field properties. To edit a table, open the Database User Interface window. Selecting the Table object in Database Pane, the list of tables will be displayed in the Table Area. Right click on the table name and select Edit option from Fig. 10.1: Selecting Edit option from pop up menu the pop menu. In our example we have selected the Customer table as shown in Fig. 10.1. The design view window of the table will be displayed as shown in Fig. 10.2. Do the required modifications 2023-24  and save the table. Also note that the changes so made will not affect the previous records entered in the table. Similarly to delete a table, right click on the table to be deleted, say Customer table and select the Delete option from the pop up menu as shown in Fig. 10.3. A confirmation box to confirm for deletion of the table will be displayed as shown in Fig. 10.4. Click on Yes button to finally delete the table. Fig. 10.2: Table in design To rename a table, right click on the table name view window in the Table Area and select Rename.. option from the pop up menu. A cursor will appear. Type the new name and press the Enter key. Relationships between Tables While working with multiple tables, you need to check the redundancy and inconsistency of data. The record for a particular entity should neither be Fig. 10.3: Selecting Delete option repeated nor different data values should appear for from pop up menu a single entity in the database. This is done by setting relationship between the tables of a database. The most important prerequisite for setting a relationship is that there must be a common field(s) between the two tables to create Fig. 10.4: Confirm Deletion Alert a relationship. Let us consider an example of a database containing following two tables–Student_Details and Student_Result Table 10.1: Student_Details Admission No Name Father’s Name Class DOB 1001 Mampi D K Bose 10 12/3/2004 1005 Harnoor Tej Singh 9 2/5/2005 1110 Sanjeeva B Reddy 10 13/11/2004 1002 Neeru Rajesh Jain 7 14/11/2006 1134 Urjit Mahesh Patel 10 7/7/2003 Table 10.2: Student_Result Roll No Admission No Class Aggregate_Marks 1 1001 10 78 2 1110 10 93 3 1134 10 46 174 Domestic Data entry operator – class X 2023-24  In Table 10.1 (Student_Details), Admission No is the notes primary key. In table 2 (Student_ Result), Roll No is the primary key and Admission No is the foreign key. So each record in Table 10.2 has a value of Admission No that corresponds to a record in Table 10.1 with same value of Admission No. It is important to note that the data types of the common field in both the tables must be same. If they are not same then LibreOffice Base will display an error message and will not allow to set the relationship between the two tables. Once the relationship between the two tables has been set, the integrity of data will be managed by the DBMS. That means once a student’s record has been entered in the Student_Details table, only then that particular Admission No can be entered in the Student_ Result table. The record pertaining to Admission No in Student_Details table is considered as the master record while the corresponding record in the related table (Student_Result) is the transaction record. Therefore Student_Details is called the master table and Student_ Result is called the transaction table. Types of Relationships The type of relationship between any two tables in a database is based on the number of records that are present in the transaction table corresponding to the master table. Primarily three types of relationships can be set up between two tables in a relational database These are: (i) One-to-One (ii) One-to-many (iii) Many-to-Many One-to-One relationship In this type of relationship, one specific record of a master table has one and only one corresponding record in the transaction table. For example, the record for Admission_No in the master table (Student_Detail) will have only one corresponding record of same value of Admission No in the transaction table of Student_ Result. This is because no two students will be given same admission number. Similarly one person can have Database ManageMent systeM Using LibreOffice Writer 175 2023-24  notes only one ticket to get entry into a stadium to view the match. So relationship between Student and Admission number and a person and his ticket number will be one-to-one relationship (Fig. 10.5). Fig. 10.5: One–to-one relationship One-to-Many relationship This is one of the most common types of relationship between the tables in a database. As the name says, in this type of relationship, one specific record of the master table has more than one corresponding records in the related transaction table. For example, one teacher can teach multiple students or multiple classes, or one person can sell multiple products. So we can say that there is a one to may relationship between a teacher and class or teacher and student or seller and products (Fig. 10.6). Fig 10.6: One–to–Many relationship 176 Domestic Data entry operator – class X 2023-24 Many-to-Many relationship notes In this type of relationship, there will be multiple records in the master table that correspond to multiple records in the transaction table as well. Generally this type of relationship is set when certain records have to be saved more than once in both the related tables. For example, a teacher in a school may hold multiple responsibilities such as class teacher, an activity in- charge or examination in-charge. For each responsibility the teacher might be attached with multiple students. So this type of relationship will be many to many relationship. Similarly a shopkeeper may sell multiple products to multiple customers. So many-to-many relationship exists between a product and a customer. Fig. 10.8 shows the corresponding records in master table and transaction table are in same color. Table: Department_Incharges Department Teacher_Id Examination T002 Discipline T765 Co-Curricular T056 Time Table T002 Website Update T765 Table: Activity_Duty Activity Department Teacher_Id UT1 Examination T002 Term1 Examination T002 HomeWork Upload Website Update T765 Timetable Upload Website Update T765 Inter-house Dance Co-Curricular T056 Inter-class Debate Co-Curricular T056 Discipline duties Discipline T765 Fig. 10.8: Many–to-many relationship Advantages of Relating Tables in a Database There are various advantages of relating tables in a database. Few of them are as given below. A relationship can help prevent data redundancy. It helps prevent missing data by keeping deleted data from getting out of synch. This is Database ManageMent systeM Using LibreOffice Writer 177 2023-24  called referential integrity. We will study in detail about referential integrity later in the chapter. Creating relationships between tables restricts the user from entering invalid data in the referenced fields. Any updation in the master table is automatically reflected in the transaction tables. Let’s Practice Name the type of relationship for the following: Citizen and his Driving License Customer and Product Student and Course Team and Match Player and Country Employee and Project Creating Relationships between Tables Let us get back to Sports Day database created in the previous chapter. Let us add another field, CategoryID to the table Events using Edit option as shown in Fig. 10.9. Enter 10 records in the Events table of various categories as shown in Fig. 10.10. Create another table with the name EventCategory with the fields – CategoryID, CategoryName, TeacherIncharge and enter the records as shown in Fig. 10.11. Fig. 10.9: Adding field CategoryID in Event table Hence, there are two tables in the database – Events and EventCategory with a common field as CategoryID. Fig. 10.10: Records entered in Event table with added field CategoryID Fig. 10.11: Records entered in EventCategory table 178 Domestic Data entry operator – class X 2023-24  In Event table, EventID is the primary key and CategoryID is the foreign key. In EventCategory table, CategoryID is the primary key. To set up relationship between these tables follow the following steps. Step 1. From main menu of LibreOffice Base, click on Tools > Relationships… Step 2. The Relationship Design screen will appear as shown in Fig. 10.12. In the middle of the screen there is Add Tables dialog box. Both the tables are listed in the Fig. 10.12: Add Tables dialog box in Relationship dialog box. Design Screen Step 3. In the Add Tables dialog box, click Events table and then click Add button. Similarly add EventCategory table to the Relationship Area. Step 4. Click Close button to close the Add Tables dialog box. Observe that the tables Events and EventCategory table added to the Relationship Area along with all its field list as shown in Fig. 10.13. Step 5. As discussed before, CategoryID is the common field in the two tables. Hence it will be used to Fig. 10.13: Events and EventCategory Tables set a relationship between the added in Relationship Design window two tables. To create a relation between the two tables, we just have to drag the common field CategoryID from the Events table and drop it in EventCategory table. A line connecting both the tables with the common field (CategoryID) appears on the screen as shown in Fig. 10.14. The line is labeled as ‘l’ on the primary key side and as ‘n’ on the foreign key side. Hence the CatgeoryID from EventCategory table as primary key will have unique values and is called referenced field. On the other hand, Fig. 10.14: Relationship between two tables Database ManageMent systeM Using LibreOffice Writer 179 2023-24  in the Events table, the values of CategoryID might be repeated. Here it is known as referencing field. Fig. 10.15 shows that the CategoryID of the Events table is referenced field and CategoryID of the EventCategory table is referencing field. This type of relationship where one value of a table is associated with multiple values in another table is a One-to-many relationship. In this relationship EventsCategory is the master table. So the master record with a particular CategoryID has to be added in EventsCategory Fig 10.15: CategoryID of Events table table first. Only then the corresponding record is referenced field and CategoryID of EventCategory table is referencing field can be added in the Events table. Also, only one record with a specific CategoryID will exist in the master table but multiple values of CategoryID might exist in the transaction table, thereby forming a one-to- many relationship. Referential Integrity According to the principle of referential integrity, no unmatched foreign key values should exist in the database. That means if a record, say Admission No as 1001 is not present or deleted in the master table (Student_Details) of Student database, then there should be no record with Admission no as 1001 in the transaction table (Student_Result) as well. Similarly, in Ruhi’s Sports Day database, if a particular category of sports, say C003 is deleted from the master table EventCategory, then there should be no record with Category as C003 in the transaction table. Likewise, if any student leaves the school and his record is deleted from Student_Details table, then there is no question of his appearing for exams and having a result. Hence corresponding record in the transaction table (Student_Result) should either have NULL value or should be deleted. In Ruhi’s Sports Day database as well, the CategoryID that exists in EventCategory table can only be entered in Events table. As mentioned before, once the relationship between the two tables has been set, the integrity of data will be managed by the DBMS. LibreOffice Base 180 Domestic Data entry operator – class X 2023-24 will allow only that corresponding record to be entered in the transaction table which already exists in the master table. LibreOffice Base gives us following four options to choose from to maintain referential integrity in such cases. No action – This is the default option. This option states that a user should not be allowed to update or delete any record in the master table if any related record exists in the transaction table. Update cascade – This option allows the user to delete or update the referenced field but along with it all the related records in any of the transaction tables will also be deleted or updated. Set NULL – This option assigns NULL value to all the related fields if the master record is deleted or updated. Set default – This option assigns any fixed default value to all the related fields if the master record is deleted or updated. To set the relationship properties double click on the relation line joining the two tables, Events and EventCategory. A Relations dialog box will open as shown in Fig. 10.16. By default the radio button with No action option will be selected. Choose any of the desired option and click OK to set the referential integrity between the two tables. Fig. 10.16: Relations dialog box Let’s Practice Form a table in Sports Day database in which one-to-one relationship can be established. Write steps to create such a relation in LibreOffice Base. summary Relations are set up between the tables to control data redundancy and inconsistency. The most important prerequisite for setting a relationship between the two tables is that there must be a common field(s) between the two tables. Database ManageMent systeM Using LibreOffice Writer 181 2023-24  notes Three types of relationships can be set up between two tables in a relational database. These are One-to-One, One-to-Many and Many-to-Many. In One-to-One type of relationship, one specific record of a master table has one and only one corresponding record in the transaction table. In One-to-Many type of relationship, one specific record of the master table has more than one corresponding records in the related transaction table. In Many-to-Many type of relationship, there are multiple records in the master table that correspond to multiple records in the transaction table. According to the principle of referential integrity, no unmatched foreign key values should exist in the database. Practical Exercises Prakasan lives in Happy Home Society. He wants to create a database so as to store and manage the maintenance dues received from flat owners of the society. Create the following tables for his database. Table: Residents Details Table: Maintenance Dues Flat no Receipt_Id Owner Name Flat No Contact No Date Flat Category Amount Relate the two tables and thereafter enter minimum five records in both the tables. Which type of relationship did you create? In which table did you enter the data first? Uzair manages a boys hostel in an engineering college. He wants to manage the records of the people staying in his hostel by creating a database with the following tables and also set a relationship between the tables. Table: Student_Details Student_Id Name DOB Course Father’s Name Contact No_student 182 Domestic Data entry operator – class X 2023-24  Contact No_ guardian notes Room No Table: Room Details Room No Floor (data value can be first, second or third) Category (data value can be AC / Non- AC) Student Id DOO (Date of occupancy) Monthly Rent Check Your Progress A. Multiple choice questions 1. Which of the following actions can be performed once the tables are created in a database? (a) Add a field in a table (b) Rename a table (c) Delete a table (d) All of the above 2. Which of the following is checked by a DBMS? (a) Redundancy (b) Inconsistency (c) Both (a) and (b) (d) Neither (a) nor (b) 3. Which of the following is required to set a relationship between the two tables? (a) Both the tables must be in different databases (b) Both the tables must have a common field (c) Both the tables must have the same name (d) Both tables must be stored in documents folder only. 4. If a record is added in a master table, which of the following is NOT true for transaction table (a) The record in the master table is called the master record (b) The corresponding record in transaction table can only be entered once. (c) The record in the transaction table is called the transaction record. (d) It is possible to add a record in the master table 5. Which type of relationship exists between a student and the subjects studied by him/her? (a) One-to-one (b) One-to-many (c) Many-to-many (d) All of the above Database ManageMent systeM Using LibreOffice Writer 183 2023-24  notes 6. Consider the following tables. Which type of relationship can be established between the two tables? (a) One-to-one (b) One-to-many (c) Many-to-many (d) None of the above Table 1: Item Table 2: Item_Category Item_Code Category_Code Item_Name Cat_Name Price Item_Code Qty Cat_Disc Category_Code 7. Which of the following menus contains the Relationship option? (a) Edit (b) File (c) Tools (d) View 8. The list of tables to be added is displayed in the __________ dialog box in the Relationship Screen. (a) Add Tables (b) Add Databases (c) Both (a) and (b) (a) Neither (a) nor (b) 9. In the relationship design screen, the relationship between the two tables is done using __________ operation. (a) Click (b) Double Click (c) Drag and Drop (d) Right click 10. Which of the following is NOT an option that can be used to maintain referential integrity in a database? (a) No Action (b) Set NULL (c) Set Default (d) Set Value B. State whether the following statements are True or False 1. Redundancy is preferred in a database. 2. In a table, a record for a particular entity should not be repeated. 3. A single field should always have only one data value. 4. If a table is edited, the records already entered in it are deleted. 5. The record in master table should be entered before the corresponding record is entered in the transaction table. 184 Domestic Data entry operator – class X 2023-24  notes 6. In one-to-many relationship, one specific record of the master table has more than one corresponding records in the related transaction table. 7. The Relationship option is present in the Widows menu. 8. In a database, the referential integrity is maintained by the user. 9. A relationship is always set between the tables based on a common field. 10. If the master record is deleted, the transaction records will always be deleted. C. Fill in the blanks 1. A table to be edited is displayed in ___________ view. 2. The most important prerequisite for setting a relationship between the two tables is that there must be a ________________between them. 3. In ________________relationship, one specific record of a master table has one and only one corresponding record in the transaction table. 4. ___________________is one of the most common types of relationship between the tables in a database. 5. A record being entered in a _________ table must always exist in a ________ table. 6. The principle of _______________helps prevent missing data by keeping deleted data from getting out of synch. 7. Creating ____________ between tables restricts the user from entering invalid data in the referenced fields. 8. Data integrity is maintained by ______________. 9. A relationship between customers and products is an example of __________________ relationship. 10. The __________________ window is used to set relationships between the tables. D. Answer the following questions 1. Give any two advantages of relating a table in a database. 2. How is redundancy or inconsistency controlled in a database? Explain with an example. 3. Define referential integrity. Who maintains referential integrity in a database? 4. Differentiate between one to one relationship and one to many relationship. Give suitable examples to explain your answer. 5. Explain many to many relationship with an example. Database ManageMent systeM Using LibreOffice Writer 185 2023-24  11 CHAPTER Queries in Base IntroductIon A database is used to store data in an organized manner so as to retrieve it easily and accurately from database. To search for the desired record and to retrieve the desired data, we have to give its specifications to DBMS. Such specifications are given to the database in the form of queries. For example, we may have to specify the fields that we want to display or any particular data value based on which the records are to be filtered from the table(s). Therefore, we can say that a query is a sort of question asked from a database. Depending upon specifications given in the query, the specific