Database Management System Libreoffice Unit 3 PDF

Class: X (2024 - 25) Subject: Information Technology PART B Unit 3: Database Management System using LibreOffice Base Chapter 8 - Introduction to Database Management System 1. Management of data is of prime impor...

Class: X (2024 - 25) Subject: Information Technology PART B Unit 3: Database Management System using LibreOffice Base Chapter 8 - Introduction to Database Management System 1. Management of data is of prime importance for any organisation. 2. Primary key and foreign key are the various database objects of RDBMS. 3. Data and Information may be stored in the form of text, graphics, audio or video. 4. Information is the processed or organized form of data. 5. A database is a collection of logically related data items stored in an organised manner. 6. The information being stored in a database can be added, modified, deleted or displayed according to the requirements of the user. 7. The software that is used to create, update and retrieve data is known as database management system (DBMS). 8. The common examples of DBMS are MS Access, Open Office or LibreOffice Base, Oracle, Ingress, MySQL. 9. The data in the database is stored in an organised manner, so that retrieval of the required data is fast and accurate. 10. A database helps in analysis of data based on certain criteria. 11. Data Analysis is easy to find out maximum or minimum value, average or mean using a database. 12. Data Sharing – If the same data set is required for different applications then the database can be shared with other applications. 13. Minimal Data Redundancy can be reduced by using DBMS tools. 14. Data Consistency – By minimising data redundancy, chances of inconsistent data being stored is reduced. 15. Increases Efficiency – Since database tables are properly organised, saving, reading and searching data can be carried out efficiently. 16. Increases Accuracy – Since data redundancy and inconsistency can be minimised in a database, the data is retrieved accurately from the database. 17. A database can be designed in different ways depending on the data being stored. 18. The structure of database is known as data model that describes the manner in which data will be stored and retrieved. 19. A data model consists of components for describing the data, relationships among them 1  and the constraints that hold data. 20. The three different data models are hierarchical data model, network data model and relational data model. 21. In Hierarchical Data model the data is organized into a tree like structure. 22. The data is stored in the form of records. 23. A record is a collection of fields and its data values. 24. In Network Data model, multiple records are linked to same master file. 25. In Relational Data model is based on the principle of setting relationships between two or more tables of the same database. 26. The Relational Database Model was proposed in 1970 by E. F. Codd. 27. The data elements are stored in different tables made up of rows and columns. 28. Entity is a real world object about which information is to be stored in a database. 29. A table is a collection of logically related records. 30. Table is organised as a set of columns, and can have any number of rows. 31. A collection of fields make a record, a collection of records make a table and a collection of tables make a database. 32. Data values are the raw data represented in numeric, character or alphanumeric form. 33. The data values for all the fields related to a person or object is called a record. 34. A record holds the data values of all the fields for a single person or object in a table. 35. A primary key or simply a key is a field that uniquely identifies a row in a table. 36. The key identifier can be the value of a single column or of multiple columns. 37. The primary key is a unique identifier for the table. 38. In a table we use more than one fields to identify a record, it is known as a composite key. 39. A relational database is a collection of related tables. 40. 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. 41. Foreign key helps to build a relation between two tables. 42. All the field values that are eligible to be the primary key are the candidate keys for that table. 43. If Roll Number is made as the primary key, Admission Number is the Alternate key. 44. An object in a database is a structure or a feature that is used to store, represent or retrieve data. 45. A column represents a field or an attribute while a row represents a record. 2 46. A form is a feature of a database using which we can enter data in a table in an easy and user friendly manner. 47. A query is used to retrieve the desired information from the database. 48. The output of a query may be displayed in the form of reports. 49. The usual result of the query is in the form of rows and columns. 50. If we want the report to be formal and in proper layout, then we can use the Reports feature of RDBMS. 51. Information is the processed or organised form of data. 52. A database is a collection of logically related data items stored in an organised manner. 53. The software that is used to create, update and retrieve data is known as database management system (DBMS). 54. Data Model is the structure of database and it describes the manner in which data will be stored and retrieved. 55. In Hierarchical Data Model, the data is organised into a tree like structure. The data is stored in the form of linked records. 56. In Network Data model, multiple records are linked to same master file. 57. The Relational data model is based on the principle of setting relationships between two or more tables of the same database. 58. Entity is a real world object about which information is to be stored in a database. 59. The details associated with the entity are called attributes. 60. A table is a collection of logically related records. It is organised as a set of columns, and can have any number of rows. 61. A field is the smallest entity in the database. These are individual record characteristics and are presented as columns within a table. 62. Data values are the raw data represented in numeric, character or alphanumeric form. 63. 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. 64. A primary key is a field that uniquely identifies a row in a table. 65. The foreign key helps to build a relation between two or more tables in a database. 66. All the field values that are eligible to be the primary key are the candidate keys for that table. 67. Out of the candidate keys, one or two are made as primary keys. The others are the alternate keys. 68. An object in a database is a structure or a feature that is used to store, represent or 3  retrieve data. 69. The various objects in a database are tables, forms, reports and queries. 70. A form is a feature of a database using which we can enter data in a table in an easy and user friendly manner. 71. A query is used to retrieve the desired information from the database. 72. The output of a query may be displayed in the form of reports. Chapter 9 - Starting with LibreOffice Base 1. LibreOffice Base is a free and open source DBMS. 2. LibreOffice Base can be downloaded from www.libreoffice.org and is available for both Linux and Windows operating systems. 3. The memory size of a field varies according to its data type. 4. Text Data Type is a combination of letters, numbers or special characters. 5. Numeric Data Type consists of numbers. 6. Currency data type indicates the monetary values and can be stored using currencies of various countries. 7. Date data type is used to indicate dates and time. 8. In boolean data type there can be only two values- True or False. 9. The Binary data type used to store digitized images and sounds that comes as long string of zeros and ones. 10. In Binary data type it is possible to store photos of the products or employees, or sound snippets or voice messages in Base database. 11. The database in Base is saved with an extension.odb 12. The title bar displays the name of a database and an application in which it is made. 13. The menu bar appears below the title bar. 14. The Seven menu items are File, Edit, View, Insert, Tools, Window and Help. 15. Standard Toolbar is located below the menu bar and it is used to access frequently used tools. 16. Status Bar is located at the bottom of the interface window and it displays information about the type of view of the object in the database. 17. The database pane is located on the left side of the window in Status Bar. 18. LibreOffice Base is the collection of related data objects known as Tables, Forms, Queries, Reports and application modules. 19. Use the keyboard shortcut key Ctrl+O to open an already existing database. 20. A table can be created at the time of creating a database or after creating database. 4  21. The Table Wizard of Base consists of ready-made tables. 22. Field Name is the name of the field assigned at the time of creation of table. 23. Field Type allows to assign a data type to the field. 24. Description allows to describe the purpose of the field and it is not the part of database table, but it is meant for the user to understand the purpose of the field. 25. The Field Properties pane is located at the bottom half of the window while creating Table in Design View. 26. Press Ctrl + S to save the table or click Save icon from the toolbar. 27. Record Selector Box is the text box where the currently active record number is displayed and we may enter the record number that we want to see in this text box. 28. Navigation Buttons are used to scroll vertically in the table. 29. 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. 30. To delete any record from the table, open the table and select the record to be deleted. 31. The record can be deleted by pressing the Del key from the keyboard or selecting the Delete Record option from the Edit menu. 32. Data in a table can be arranged in ascending or descending order. 33. Process of arranging the records in particular order on any filed is called as sorting. 34. Base is a free and open source database component of LibreOffice suit. 35. A data type refers to the type of data that will be stored in that particular field. 36. A table in LibreOffice Base can be created using a wizard or using the Design view. Chapter 10 - Working with Multiple Tables 1. 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. 2. While working with multiple tables, you need to check the redundancy and inconsistency of data. 3. Once the relationship between the two tables has been set, the integrity of data will be managed by the DBMS. 4. Primarily three types of relationships can be set up between two tables in a relational database. 5. A relationship can help prevent data redundancy. 6. Relating Tables in a Database helps prevent missing data by keeping deleted data from getting out of synch. 7. Any updation in the master table is automatically reflected in the transaction tables. 5  8. According to the principle of referential integrity, no unmatched foreign key values should exist in the database. 9. LibreOffice Base will allow only that corresponding record to be entered in the transaction table which already exists in the master table. 10. No action 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. 11. Update cascade 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. 12. Set NULL option assigns NULL value to all the related fields if the master record is deleted or updated. 13. Set default option assigns any fixed default value to all the related fields if the master record is deleted or updated. 14. Relations are set up between the tables to control data redundancy and inconsistency. 15. 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. 16. 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. 17. 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. 18. 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. 19. In Many-to-Many type of relationship, there are multiple records in the master table that correspond to multiple records in the transaction table. 20. According to the principle of referential integrity, no unmatched foreign key values should exist in the database. Chapter 11 – Queries in Base 1. A database is used to store data in an organized manner so as to retrieve it easily and accurately from database. 2. specifications are given to the database in the form of queries. 3. A query is one of the most important features of any DBMS. 4. LibreOffice Base allows us to create a query and even save it as an object in a database. 6 5. Using a query, we can specify the fields that we want to display and also the criterion based on which the records to be filtered. 6. A query can be created in three ways. 7. A query can be created in three ways. They are Using a Wizard, In Design View, In SQL view. 8. Complete detail of the query section contains a summary about the query that has been created. 9. Once the query is created, it can be edited in Design view. 10. Short cut key to run the query is F5. 11. A query is used to retrieve and display data from one or more tables in a database. 12. A specific search criteria is given to the DBMS to view the desired information. 13. The result of the query is displayed in tabular form with field names in columns and the records in rows. Chapter 12 – Forms and Reports 1. Tables in a database are used to store data in an organized manner. 2. Both reports and forms are considered as objects of the database and are present in the Database Pane of the LibreOffice Base User Interface. 3. A form is an object of the database that has a user friendly interface where data can be entered and seen in an attractive and easy-to-read format. 4. Primarily, a form contains field controls arranged in a presentable and user friendly manner. 5. In addition to field controls, it may contain some additional text like titles, headings and names, graphics like logos, list boxes and radio buttons. 6. The two ways to create a form are Using a wizard & Using the Design View. 7. Forms Control Toolbar contains various controls that can be added to the form. 8. The Records toolbar contains the navigation control buttons in the extreme left and with the help of these buttons, we can traverse and view the records in the file. 9. The modification can be to change the background color, font size and color of the text or even positioning of various controls in the form. 10. The shortcut key Ctrl+Click to used to select the label. 11. To change the size of the textbox control, press Ctrl button while clicking on the textbox. 12. When the mouse pointer is placed on a particular control it is called the tool-tip text or help text. 7 13. The forms control toolbar contains various tools to add or edit controls on the form. 14. Designing a form, we may need to enter titles, headings or subheadings. 15. Adding text to the form is called as Labels. 16. To toggle between Design view and Form view press Design Mode button on the Forms Controls toolbar. 17. When we create a form in Design View, all the controls are placed on the form using various tools given in the Forms Control toolbar. 18. A report is another useful feature of a database management system. 19. We have seen that the records that have been extracted using a query are displayed in a simple row and column format. 20. We can create a report based on a table or a query or both. 21. A report has to be generated from multiple tables, a query should be created first and then that query can be used to generate the report. 22. The date will be inserted on the top left corner of the Page Header area. 23. A form is an object of the database that has a user friendly interface where data can be entered and seen in an attractive and easy-to-read format. 24. A form contains field controls arranged in a presentable and user friendly manner. 25. Each field control consists of a label and the field value text box. 26. The forms control toolbar contains various tools to add or edit controls on the form. 27. A report is used to present the retrieved data in an attractive and customized manner. 28. We can create a report based on a table or a query or both. 1-Mark Book Back Chapter 8 - Introduction to Database Management System 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 8  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 6. A is represented as rows in a table. (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. False 2. Data is the collection of raw facts. True 3. A table can be created without a primary key. False 4. Two tables can be related in a network data model. False 5. MS Access is an example of a database. False C. Fill in the blanks 1. The raw facts constitutes Data. 2. An Entity 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 Report. 4. The data values for all the fields related to a person or object is called a Record. 5. All the field values that are eligible to be the primary key are the Candidate keys for that table. Chapter 9 - Starting with LibreOffice Base 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 9  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. 5. Which of the following methods can be used to create a 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. True 2. Memo data type can be used to store descriptive data. True 3. A Boolean data type can have two or more than two values. False 4. We cannot store audio data in LibreOffice Base. False 5. The properties of a field change according to the data type selected. True 6. Field description may or may not be entered while designing a table. True 7. is pressed to move to the last record. False 8. appears when the record is being edited. True 9. A table once created in a database cannot be edited. False 10.Sort dialog box can only help to sort data in ascending order. False 10 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 Text data is a combination of letters, numbers or special characters. 3. Text data type can be used to store Aadhar number. 4. The Binary data type used to store digitized images. 5. The shortcut key to save a table is Ctrl+S.. 6. Tasks Pane on the Base Interface Window displays information about the type of view of the object in the database. 7. A Key icon appears before the field name indicating that it is a primary key. 8. The data can be entered in a table only in Datasheet view. 9. The black pointing arrow just before the field name in a table is called Record pointer. 10. The process of arranging the records in particular order on any filed is called Sorting. Chapter 10 - Working with Multiple Tables 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 database (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 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 11  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) (d) 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. False 2. In a table, a record for a particular entity should not be repeated. True 3. A single field should always have only one data value. False 4. If a table is edited, the records already entered in it are deleted. False 5. The record in master table should be entered before the corresponding record is entered in the transaction table. True 6. In one-to-many relationship, one specific record of the master table has more than one corresponding records in the related transaction table. True 7. The Relationship option is present in the Widows menu. False 8. In a database, the referential integrity is maintained by the user. False 9. A relationship is always set between the tables based on a common field. True 10. If the master record is deleted, the transaction records will always be deleted. False C. Fill in the blanks 1. A table to be edited is displayed in Design view. 2. The most important prerequisite for setting a relationship between the two tables is that there must be a common field between them. 3. In one-to-one relationship, one specific record of a master table has one and only one corresponding record in the transaction table. 12 4. one-to-many is one of the most common types of relationship between the tables in a database. 5. A record being entered in a transaction table must always exist in a master table. 6. The principle of referential integrity helps prevent missing data by keeping deleted data from getting out of synch. 7. Creating relationship between tables restricts the user from entering invalid data in the referenced fields. 8. Data integrity is maintained by DBMS. 9. A relationship between customers and products is an example of Many-to-many relationship. 10. The 10 window is used to set relationships between the tables. Chapter 11 – Queries in Base A. Multiple choice questions 1. Which of the following is refer to asking questions from the database? (a) Report (b) Table (c) Query (d) Database 2. Which of the following are the ways to design a query? (a) Wizard (b) Design View (c) SQL (d) All of the above 3. Which is a flexible way to create a query? (a) Wizard (b) Design View (c) Both (a) and (b) (d) Neither (a) nor (b) 4. Into how many parts is the query design window divided? (a) One (b) Two (c) Three (d) Four 5. Which of the following is NOT true about queries? (a) It can be created using multiple tables (b) Multiple queries can be created in a database (c) A query can run multiple times (d) A query once created cannot be edited 6. Which of the following is the shortcut key to run the query? (a) F3 (b) F4 (c) F5 (d) F6 7. Which of the following functions can be performed on numerical data while designing a query? (a) Sum (b) Minimum (c) Maximum (d) All of the above 8. In a Query Design wizard, which of the following buttons is clicked to move a field from ‘Available fields’ list box to ‘Fields in the query‘ list box ? (a) > (b) < 9 (c) (d) 13 9. Which of the following relational operators can be applied to set the criterion while designing a query in LibreOffice Base? (a) > (b) = (c) != (d) Add Form 10. Which of the following dialog box is present when the Query Design window is opened for the first time to design a query? (a) Add Table (b) Add Query (c) Add Table or Query (d) None of the above 11. Which of the following step is not performed if there is no numerical data to be worked upon in a query? (a) Selection of fields (b) Giving Aliases (c) Summarizing (d) Selection of tables B. State whether the following statements are True or False 1. You can run a query only once. False 2. A query cannot be created from multiple tables. False 3. The shortcut key to run a query is F5. True 4. LibreOffice Base provides us with two ways to create a query. False 5. A query with numerical data cannot be saved. False 6. By default the query result is not sorted. True 7. A query can be used to display the average value of a numerical field. True 8. While designing a query, the criterion can be set on only one field. False 9. Alias is an alternative name for a field in a query. True 10. In query Design window, the visible check box is selected by default. True 11. A query once created using a wizard can only be edited in the Design view. True C. Fill in the blanks 1. A Query is a sort of question asked from a database. 2. The result of the query is displayed in Report form with field names in columns 3. A query can be created in Three ways. 4. The Query Design window is divided into two sections. 5. The shortcut key to run the query is F5. 6. The conditions to filter the records are set in the Criterion row. 7. When a table is selected in a Query wizard, the corresponding fields are displayed in the Available fields list box. 8. The result of the query can be displayed in ascending or descending order of any particular field of the table. 9. At the most three search conditions can be given in the query wizard. 10. The last step of the Query wizard displays the entire Overview of the query. 11. The Design view is a more flexible method to create a query. 14 12. To edit any query, right click on the Query icon of the query that has to be edited. 13. In the Alias row of the Query Design grid, we can type the column heading that will be displayed instead of field name when we run the query. Chapter 12 – Forms and Reports A. Multiple choice questions 1.Which of the following toolbars contain the Label tool? (a) Standard Toolbar (b) Forms Controls Toolbar (c) Records toolbar (d)Formatting toolbar 2. The Record toolbar has the buttons to move to the (a) first record (b) second record (c) last record (d) all records 3. Which of the following is NOT true about forms? (a) It is the front end for data entry (b) It can contain only text fields (c) Graphics can be inserted on the form (d) It can contain only fixed number of records 4. Which of the following keys is pressed to select only textbox on the form? (a) Alt (b) Shift (c) Ctrl (d) Tab 5. Which of the following properties in the Properties: Label Field text box is used to insert a tool- tip on the form? (a) Tool Text (b) Help Text (c) Tool Tip (d) Help Tip 6.Which of the following objects of Libre Office Base is used to display data retrieved from one or more tables in a presentable manner? (a) Query (b) Form (c) Report (d) Panel 7. Which of the following values of Date Format property is selected to view a calendar on the form? (a) Standard (short) (b) Standard (long) (c) Default (d)Standard (Medium) 8. Which of the following commands on the Forms Control toolbar is used to toggle between Design View and Form view? (a) Design Mode (b) Toggle Mode (c) View Mode (d) Print mode 9. Using which of the following objects in a database, can a report be generated? (a) Tables (b) Queries (c) Both a and b (d) Neither a nor b 10.Which of the following components open along with the Report Wizard? (a) Report Builder (b) Add Fields dialog box (c) Both (a) and (b) (d) Neither (a) nor (b) 15 B. State whether the following statements are True or False 1. Report is an object of a database but form is not. False 2. We can choose the layout of the form. True 3. We have to add all fields of the table on the form. False 4. There are two ways n which a form can be created. True 5. A report is generated in a separate window. True 6. Once a control is added on to the form, it cannot be re- positioned. False 7. The Record toolbar has the button to add a new record. True 8. We can create a report only using a table. False 9. By default, the records in a report are sorted in descending order. False 10. We can group data based on a particular field in a report. True 11. A report can have data only in row and column format. False 12. We can insert both date and time of generation of report. True 13. A report once created cannot be edited. False C. Fill in the blanks 1. A form can be used for Enter and view data. 2. Each field control consists of a label and field value 3. A label is a piece of text that specifies the data that should be entered in the field value text box. 4. By default the border of the field text value is displayed in 3D. 5. A tool tip is a small piece of text that is displayed when the mouse pointer is placed on a particular control on the form. 6. The default orientation option for a report is landscape. 7. A Layout is the manner in which the labels, field values, titles etc. will be displayed in the report. 8. The option to insert date and time in the report is present in Insert menu. 9. A Report Wizard contains Six steps. 10. A dynamic type of report changes automatically as the field values in the base table or query change. 16  Chapter -8 Introduction to Database Management System 1. What is a Data? 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. 2. What is an Information? 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 information. Other forms of information are pay-slips, 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. 3. What is a Database? (or) Define a Database. (BB) 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 a 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. 4. What are the Advantages of Database? (BB) 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. 17  Increases Efficiency – Since database tables are properly organized, 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. 5. What is a Data Model? 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. 6. What are the different Data Models in Database? 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. All these records are linked to each other at various levels, thereby forming a hierarchy. For example, the data of a company is stored using a 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. The data of the company is represented using the network data model. 18  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. 7. What is Relational Database Model? 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. 8. What is 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 For example, in a student table, each student has a unique roll no., which forms the primary key. 9. What is Composite Key? 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. 10. What is Foreign Key? 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. 11. Write about some of the common terms used in RDBMS. Relational Database Terminology 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, 19 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 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, below given in table the database contains two related tables. 20  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. 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. 12. What are the 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 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 13. Define 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. A table is the basic unit of any DBMS. The data is first stored in tables in row and column format. A column represents a field or an attribute while a row represents a record. 21 14. Define the term: (a) Data redundancy: Data Redundancy – In the event of requiring the same data field in several tables the data field might get repeated in number of tables. Duplication of record is called data redundancy. This is called as data redundancy. 15. Give one point of difference between (a) Data and Information, Data Information Raw facts and figures is called data Processed data is called information The facts may be related to any person, place, If data is not correct or accurate, the activity or things. It may be stored in the form information obtained by processing such data of text, graphics, audio or video may not be correct. The examples of data are marks scored by the For example, marks obtained by students and students, weights, prices, costs, numbers of their roll numbers is the data, while the report items sold, employee names, product names, card/sheet is the information. addresses, tax codes, registration, marks etc (b) Form and Query, Form Query  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  A form consists of text boxes, labels, radio buttons, information from the database. list boxes, check boxes etc  In simple terms, it is a question asked from the  The data entered through the forms is stored in database. tables. (c) Network and hierarchical data model Network Data Model Hierarchical Data Model  In Network Data model, multiple records are  In this model the data is organized into a tree like linked to same master file structure.  The data is stored in the form of records.  It is also considered as an inverted tree where A record is a collection of fields and its data master is present in the bottom of the tree and the values. branches contain information linked to the  All these records are linked to each other at master. various levels, thereby forming a hierarchy 22 16. Consider the table given below and answer the questions that follow (a) Name the fields in the given table. Fields in the table are  Book_Id  Book Name  Author Name  Price  Publisher (b) Which field should be made the primary key? Book_Id (c) Is there any alternate key in the table? Yes. Book Name (d) How is primary key different from foreign key? Explain with example. Primary Key Foreign Key  A primary key is a unique value that  Foreign key identifies a column or set of columns identifies a row in a table in one (referencing) table that refers to a column  Primary key can’t accept null values or set of  We can have only primary key in the table. column in another (referenced. table.  Foreign key can accept multiple null value.  We can have more than one foreign key in a table. For Example In ‘TEACHER’ table ‘T_ID’ will act as Primary Key while in “DEPARTMENT’ table ‘T_ID’ will act as Foreign Key Chapter -9 Starting with LibreOffice Base 1. Define Data type? 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. 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. 23 2. Explain in detail Data Types in RDBMS? 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. 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.  Text Data Type  Numeric Data Type  Currency Data Type  Date Data Type  Boolean  Binary 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. The table below lists various data types that can store textual data. Text Data Types  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 lists different numeric data types along with the number of bits/bytes it uses and its range. 24  Numeric data types  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 list various forms of date data type.  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. 3. Write the steps to create the database in Libreoffice. 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. In Ubuntu Linux, click on the Base icon located on the left panel or click on the Show Applications to search for its icon. Type Base in the search box. The LibreOffice Base application icon will be displayed. Click on the Base icon to open the Base application. Step 2. A Database Wizard open. It allows you to create a new database or open an existing database. 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 Database option to select the database to be 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 25  would not like to register, so we click and select the radio button with option, ‘No, do not register 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. 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. 4. What are the various components of the Base User Interface of Libreoffice? User Interface of Libre Office Base: Once a database is created, the screen appears. This is the User Interface of Open Office Base. The various components of the Base User Interface are discussed below 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. 5. Write the steps to open the database in Libreoffice. To open an already created database, click File > Open. The Open dialogue box appears. 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. 6. Write the steps to create a table in a database in Libreoffice. 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 26  A table can be created at the time of creating a database or after creating database. To create a table 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. 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 any one table, say Customer from the drop down list. After selecting the Customer table, various fields appear under Available Fields. The required fields from Available fields box can be shifted one by one to Selected fields box using > button. we 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. Then click on Next button. It will move you to step 3. Set primary key. Let us set the CustomerID field to primary key. Click on Next button, that will take you to the complete the process of creating table using wizard and displayed the screen. This screen will give you three choices. By default the “Insert data immediately” option selected. 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, it will open that allow to enter the data in the Customer table. Creating Table in Design View Creating the 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 main Database window, click on the option Create Table in Design View in the Tasks Pane and Table Design Window will be opened. 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 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 changed as per the requirement and are used to control and validate the data that is to be entered. To create a table named Events using in the option Create Table in Design view in the Sports Day 27  database. 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 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 next row. Step 6. Enter the next field by repeating steps 1,2 and 3. Repeat the process for adding all fields in the table. 7. How to set the Primary Key in a Table? (or) How can we define a primary key in a table? 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. Select the Primary Key option from pop up menu. A key icon appears before the field name indicating that it is a primary key. 8. How will you save the 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. Enter the name of table and click on OK button. Tip: Press Ctrl + S to save the table or click Save icon from the toolbar. If the table is being saved after making some changes, simply select File-> Save option. Once the table design is complete, click on Close button on the toolbar to return to the Database screen. The name of the table will appear in the Tables Object Area. 9. How to set the Composite Key in a Table? To set a composite key, i.e. a primary key consisting of two fields, keep the Ctrl key pressed and then click on multiple fields to select them. Thereafter right click on selected fields and choose Primary Key option from the pop up menu. 10. How will you Enter 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 the Open option from the drop down menu. The datasheet view of the table will appear. 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. 28  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 called as data entry. Enter 4-5 records in the Events table. 11. What are the various components available in Navigation option in Table? 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. 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.  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. 12. What do you mean by editing data or modify the data in a table? To edit or modify the previously entered data simply, place the cursor on the field value that has to be edited 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. 13. How will you delete the records from the 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. Clicking on Yes button will finally delete the record, while clicking on the No button will not delete the record. 14. What do you mean by Sorting Data in the Table? (or) Write steps to sort the table in descending order of primary key. Data in a table can be arranged in ascending or descending order. This process of arranging the records In a particular order on any filed is called as sorting. 29  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. 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. Step 3. The table will be sorted in the ascending order of points. 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. 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 selected in this dialog box. Click OK button once done. 15. Define 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. 16. Differentiate between: (a) Memo and Varchar data type Memo Varchar  Stores up to the maximum length indicated  Stores upto the specified length. by user.  The number of bytes allocated depends on  It is used to store some descriptive data the number of characters entered by the user.  It can store more than 255 characters.  Memo data type allows to store text data up to 64,000 characters (b) Number and Decimal data type Number Data type Decimal Data type  Number data type is used to store integers or  Decimal data types includes decimal places, whole numbers. making it ideal for financial calculations or  Numeric must be exactly as precise as it is any calculations requiring precisions. defined.  It is free to allow higher numbers if that’s easier to implement. 30  (c) Design View and Datasheet view of a table Design View Datasheet view  The Design view allows you to view and  Datasheet view is used to enter the data into modify the structure of our table and to set the table. field properties.  Fields are not listed vertically in the  Fields are listed vertically in the design datasheet view. view.  Each row represents a record in the datasheet  Rows do not represent any record in design view. view. 17. Name the menu items present on the Base User Interface. The menu items present on the Base User Interface and it consists of seven menu items are –File, Edit, View, Insert, Tools, Window and Help. All these menu items contain commands that help to perform various operations on the database. 18. Label the components – Title Bar, Database Pane, Tasks Pane, Status Bar of the LibreOffice Base User Interface. Title Bar Status Bar Task Pane Database Pane 19. What is the use of navigation box with respect to tables in a database? The navigation box is used to navigate through various record of the table. Navigation box is present at the bottom of the datasheet window. 31  Chapter -10 Working with Multiple Tables 1. Write about Editing and Deleting Tables. Create 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 the pop menu. In our example we have selected the Customer table. The design view window of the table will be displayed. Do the required modifications 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. A confirmation box to confirm for deletion of the table will be displayed. Click on Yes button to finally delete the table. To rename a table, right click on the table name 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. 2. Explain about Relationships between Tables with an example. While working with multiple tables, you need to check the redundancy and inconsistency of data. The record for a particular entity should neither be repeated nor different data values should appear for 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 a relationship. Let us consider an example of a database containing following two tables–Student_Details and Student_Result. 32  In Table 10.1 (Student_Details), Admission No is the 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. 3. What are the advantages of Relating Tables in a Database? (or) Give any two advantages of relating a table 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 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. 4. Write the steps to create relationships between Tables. There are two tables in the database – Events and EventCategory with a common field as CategoryID. 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. In the middle of the screen there is Add Tables dialog box. Both the tables are listed in the dialog box. 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 an 33  EventCategory table added to the Relationship Area along with all its field list. Step 5. As discussed before, CategoryID is the common field in the two tables. Hence it will be used to set a relationship between the 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. 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, in the Events table, the values of CategoryID might be repeated. Here it is known as referencing field. 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 table first. Only then the corresponding record 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. 5. Explain Referential Integrity with the help of an example. According to the principle of referential integrity, no unmatched foreign key values should exist in the Database. once the relationship between the two tables has been set, the integrity of data will be managed by the DBMS. LibreOffice Base will allow only that corresponding record to be entered in the transaction table which already exists in the master 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 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. 34  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 on 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 many relationship between a teacher and class or teacher and student or seller and products. Many-to-Many relationship 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. It shows the corresponding records in master table and transaction table are in same color. 35 6. What are the four options to maintain referential integrity? 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. 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. 7. How i s redundancy or inconsistency controlled i n a database? Explain with an example. 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. 8. Define referential integrity. Who maintains referential integrity in a database? Referential integrity is used to maintain accuracy and consistency of data in a relationship. According to the principle of referential integrity, no unmatched foreign key values should exist in the Database. The referential integrity in a database is maintained by DBMS. 36 7. Differentiate between one to one relationship and one to many relationship. Give suitable examples to explain your answer. 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. One to Many Relationship: 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 many relationship between a teacher and class or teacher and student 37 8. Explain many to many relationship with an example. Many-to-Many relationship 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. Chapter -11 Queries in Base 1. What is a Query and the purpose of using queries? A query is one of the most important features of any DBMS. Using a query, we can retrieve and display data from one or more tables in a database. This is done by giving specific search criteria to the DBMS so that we are able to view the exact information that we want. LibreOffice Base allows us to create a query and even save it as an object in a database. This helps us to run the query multiple times as and when required. Using a query, we can specify the fields that we want to display and also the criterion based on which the records to be filtered. The information may be retrieved from a single table or from multiple tables. 38  Also the result of the query is displayed in tabular form with field names in columns and the records in a row. For example, in a Student database, if we want to display the names and marks of the students in a particular class from the Result table, who have scored less than 320 aggregate marks, then we need to give the following information to the database: Name of the table – Result Fields to be specified – Name, Marks Criterion – Aggregate marks should be less than 320 2. Name the three ways of creating a query in LibreOffice Base? A query can be created in three ways. (i) Using a Wizard (ii) In Design View (iii) In SQL view 3. Write the steps to create a Query Using a Wizard. There are two tables in the database – Events and EventCategory To create a query using a wizard, follow the following steps. Step 1. Open the Sports Day database. In the Database Design window, click on Queries button present in the Database Pane on the left. Step 2. In the Tasks Area, click on Use Wizard to Create Query… option. The Query Wizard will start. It contains the Steps Pane on the left and the Query Details Area on the right. Step 3. The first step of the wizard is to select fields from the respective tables. For our query, to display Event Name and Winner and the criterion to filter the records based on CategoryID. All these fields are present in Events table. Therefore select Events table from the Tables list box. The corresponding fields are displayed in the Available Fields list box. Step 4. Select Event Name field from the list box and click the right arrow (>) button. The field name will appear in the Fields in the Query list box. Repeat the process for Winner and CategoryID fields. Observe that these three fields are moved to Fields in the Query list box. Note that once the fields are added they can be moved up and down in order by clicking ∧ and ∨ buttons present on the extreme right of the wizard. Clicking on » button moves all fields to Fields in the Query area and « button moves all the fields back to Available fields area. Step 5. Clicking on the Next button will display the screen to select the sorting order. It will set the sorting order. The result of the query can be displayed in ascending or descending order of any particular field of the table. Since we do not want to set in a particular order, so we click on Next button. Step 6. The next step is to set the search conditions or the criteria on the basis of which records will be filtered from the table. This is the step where actually the query is set up or the criterion is given to the database. As per our query, the criterion is to display the records of events with CategoryID as C001. By default, the radio button with option Match all of the following is selected. Step 7. Select Events.CategoryID field from Fields drop down list, is equal to from Condition drop down list and type the value as C001 , and click on Click Next button. Three search conditions can be given at the most in the wizard. Click and select the radio button with option ‘Match any of the following’ if any one of the given condition are to be matched for filtering the records. 39  Step 8. Steps 4, 5 and 6 given in the Steps Pane deal with tasks like summarizing and performing numerical calculations. Such steps are not required if there is no numeric field involved in the query. So you can skip these steps and move directly to “Step 7. Aliases” of the wizard. Step 9. The next step to give alias name i.e. the column header name will be displayed when we run the query. By default the field names will be displayed as column headers. Many times field names are not user friendly, so an alias name which is more readable, is chosen to be displayed in the query output. For example, the name of the field in the Events table is Winner. To display the name of winner as the column header, type ‘Winner Name’ and click on Next button. Step 10. The last step of the Query wizard displays the entire overview of the query. All the steps performed till now are shown in a summarised manner. It includes the following: Name of the Query – By default, the name of the query is Query_Events by default. If desired, type the new name in the text box. The action to be performed after the wizard finishes – By default Display Query option will be selected. Click and select the Modify Query radio button if the query has to be edited in the Design view. Complete detail of the query – This section contains a summary about the query that has been created. Step 11. Click on Finish button. The records with CategoryID as C001 will be displayed. Observe the column headings. Are all of them same as the field names in the Events table? Once the query is created, it can be edited in Design view. 4. Write the steps to create a Query in Design View. Another way to create a query is using the Design view. This is a more flexible method to create a query from either single or a multiple tables of a database. For example, create a query to display records of Athletics category. For this query, records have to be filtered from both Events and EventCategory tables. Open Sports Day database and follow the following steps to create a query in Design View: Step 1. Click Queries icon on the Objects Pane in the Database Window. Step 2. Click Create Query in Design View… icon in the Tasks Pane. The Query Design Window appears. In the middle of the window the Add Table or Query dialog box is displayed. Step 3. Click on the Event table to be used in the query and then click on Add button. Alternatively double click on the Events table. The table will be added to the Tables Pane present at the top of the Query Design window. Step 4. Similarly add EventCategory table to Tables Pane of the Query Design window. Step 5. Click Close button in the Add Table or Query dialog box to close it. The tables Events and EventCategory added will be displayed in the Table pane. Step 6. Next step is to select the fields. For our query we want to display Event Name and Winner from the Events table and Category Name from the EventCategory table. So in the list box of Events table, double click on EventName and Winner field. Similarly add the field CategoryName form EventCategory table. The field name along with the table name is displayed in the Design grid present in the lower half of the Query Design window. 40  Observe that the Visible Check Box is by default selected. This means that all these three fields will be visible when you run the query. If you do not want the data values for the particular field to be displayed, click to deselect the respective check box. Step 7. In the grid, there is a row titled Alias.As mentioned before, it can be used to display meaningful names in the output. For example, instead of Winner, we would just like to display Winner Name. For this, type Winner Name in the Alias text box under Winner column. Step 8. By default, the data that is displayed as a result of the query is not sorted. To sort the records in either ascending or descending order of a particular field, the Sort row is given in the grid. For example, to display the records in alphabetical order of Event Name, select Ascending from the drop down list box visible in the Sort row under Event Name column. Step 9. Once the query is designed, click Run Query ( ) button on the toolbar or press F5 key. The query result will be displayed in the Tables Pane area. Step 10.Click on Save button to save the query. The Save As dialog box will be displayed. Step 11.By default, the Query Name as Query1 will be displayed. Type a different name if required. Click on OK button to save the query. The name of the query will be seen in the Objects area in the Database window. To run the query again and see the result s of the query, double click on the query name. The results of the query will be displayed in a separate window. To close the Query window, click on on close button on the top right corner of the window. 5. Define Editing a Query. To edit any query, click on the Query icon of the query that has to be edited. The list of queries that have been created will be displayed in the Objects Area. Right click on the Query Name in the Objects Area of the Database window. Select Edit option from the drop down menu. The Query Design window will be displayed. Let us apply a criteria to display records of only Athletics For this type Athletics in the Criterion row under the Category Name column. Now, the query design is complete if we run the query, the record of only Athletics category will be displayed. We can use the Criterion row to apply multiple conditions as well. Also note that we can apply all relational operators like , =, != and = for all conditions that can be given in Criterion row. For example, if you want to see only those records where points scored are more than 10, then add Points field to the grid and then set the Criterion for it as >10. Now save and run the query. 6. Name any four mathematical functions that can be applied to numerical data in a query. We can even use certain mathematical functions to find the count, sum, minimum, maximum or average of data values. Let us design a query to display the average points for each category of events. In the Database Window, click Create Query in Design View… button to open the Query Design Window. 41  Add Events and EventsCategory tables. Thereafter follow the following steps to display the average points for each category. Step 1. Add Category Name field from EventCategory table and Points field from Events table. Step 2. Under the Category Name field, in the Function row, click the down arrow. A drop down list will be displayed. Step 3. Select Group option from the drop down list. This option forms the groups for each of the data values in the column of Category Name. Step 4. Similarly, under the Points column, select the Average function from the drop down list. Step 5. Press F5 to run the query. The query result depicting average points in all the categories will be displayed. Step 6. Further, we can also put conditions on groups using the Criterion row. Say, for example to display the average of only Athletics category, type Athletics in the Criterion row under the Category Name column. Now, if we run the query the average of only Athletics category will be displayed in the query result. 7. Define a query? What is the need of creating a query in a database? Query is used to retrieve the data from the database as per our requirement by providing the desired specification. Depending upon given specification, the specific records are searched from the database and then displayed in the desired manner. Need of creating a query in a database As the no. of records increases, finding the desired information in a database becomes difficult. By using queries, we can retrieve the data without going individually through each record in the table(s) and also display them in desired format. 8. Rearrange the steps given below so as to create a query using a wizard. Give Alias Select the fields Set the criterion Set the sorting order Give table name Answer: Give table name Select the fields Set the sorting order Set the criterion Give Alias 9. What all information is seen in the overview (last step) of the Query wizard? The last step of the Query wizard displays the entire overview of the query. All the steps performed till now are shown in a summarised manner It includes the following: Name of the Query –If