Computer Book Class 10 SEE (Governmental)

Examples: i. (9A3)16 = (?)2 0100 1100 4 digits combination = 0100 3 0011 Hexadecimal number = 9 A D Binary equivalent = 1001 1010 1101 (9A3)16 = (100110100011)2 Rough 8421 8421 8421 3 9 A=10 8421 0011 8421 8421 1001 1010 ii. (B0D)16 = (?)2 B 0 Hexadecimal number = Binary equivalent = 1011 0000 (B0D)16 = (101100001101)2 Rough B=11 0 D=13 8421 8421 8421 1011 0000 1101 11. Octal to Hexadecimal number system conversion To convert octal number to hexadecimal number follows the following steps: a. Write binary triple equivalent to each octal number b. Now, again form the group of four digits from right to left. c. Write respective hexadecimal number for each binary group. d. Give the result base 16. 94 Computer Science : Grade 10

Examples: i. (236)8 = (?)16 Octal number =2 3 6 Binary equivalent = 010 011 110 4 digits binary = 1 0 01 1 1 1 0 Hexadecimal equivalent= 9 E (236)8 = (9E)16 12. Hexadecimal to Octal number system conversion To convert hexadecimal number to octal number follows the following steps: a. Write binary equivalent 4 digits group to each hexadecimal number. b. Now, again form the group of three digits from right to left. c. Give the result base 8. Examples: i. (9A3)16 = (?)8 =9 A3 Hexadecimal Binary equivalent = 1001 1010 0011 3 digits binary = 100 110 100 011 Octal equivalent =4 6 4 3 (9A3)16 = (4643)8 Binary Calculation Generally, there are four types of binary calculation. They are: 1. Binary Addition, 2. Binary Subtraction, 3. Binary Multiplication and 4. Binary Division. Computer Science : Grade 10 95

1. Binary Addition Binary addition is similar to decimal numbers addition. Some rules for adding binary numbers are: 0+0=0 0+1=1 1+0=1 1 + 1 = 10 (Write '0' here, carry 1 to next column) Example: 1010 +1 1 0 1 10 1 1 1 Hence, 1010 + 1101 = 1 0 1 1 1. Binary Subtraction Binary subtraction is similar to decimal number subtraction. Some rules for subtracting binary numbers are: 0-0=0 1-0=1 1-1=0 0 - 1 = 1 (with borrow 1 from the left column) 10 111 -1 0 1 10010 Hence, 10 111 – 101 = 1 0 0 1 0. 96 Computer Science : Grade 10

Binary Multiplication Binary multiplication is similar to decimal number multiplication. Some rules for multiplying binary numbers are: 0×0=0 1×0=0 0×1=0 1×1=1 1001 ×101 1001 0000 1001 101101 Hence 1001 ×101 = 101101 Binary Division Binary division is similar to decimal number division. Some rules for dividing binary numbers are: 0÷0=0 0÷1=0 1÷1=1 1 ÷ 0 = undefine 1 0 1 ) 1 1 0 1 0 ( 1 0 1  Quotient -101 110 -101 1  Remainder Hence, Quotient = 101 Remainder=1 Computer Science : Grade 10 97

Bits, Nibbles, Bytes and Word Bits Bits stands binary digits. It is the smallest unit of information in computer. It represents 0 or 1. Nibble The combination of four bits is called a nibble. Examples: 1001, 1000, etc. Byte The combination of eight bits is called a byte. Examples: 10111101, 10110110, 10011110, etc. Word Word is the combination of bits. It is the number of bits which can process and transfer by processor. Computers usually have word size of 8 bits, 16 bits, 32 bits, 64 bits, 128 bits, etc. The different measurement units of data in a computer are: Bit = 1 or 0 1 Kilobyte = 1024byte 1 Petabyte= 1024 TB 1Crumb =2 bits 1 Megabyte= 1024 KB 1Exabyte = 1024 PB 1 Nibble = 4 bits 1 Gigabyte= 1024 MB 1Zettabyte = 1024EB 1Yottabyte = 1024 ZB 1 Byte = 8 bits or 2Nibble or 1 Terabyte = 1024 GB 1character Summary  The group of digits or symbols used to express quantities as the basis for doing different calculations is called number system.  Number system is differentiated by its base or radix.  Decimal number system has base 10 as it uses 10 digits from 0 to 9.  Binary number system has base 2 as it uses 2 digits 0 and 1.  Octal number system has base 8 as it uses 8 digits from 0 to 7. 98 Computer Science : Grade 10

 Hexadecimal number system has base 16 as it uses 16 digits (10 digits and 6 letters, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F).  Computer uses binary, octal and hexadecimal number system according to its model or brand.  A bit is the smallest unit of information on computer. Technical Terms Number system : A set of symbols used to express quantities Binary number : Number system consisting of digits 0 and 1 Octal number : Number system consisting of digits 0 to 7 Decimal number : Number system consisting of digits 0 to 9 Hexadecimal number : Number system consisting of 16 digits (0 to 9 and 6 letters-A to F) Base : The total number of digits available in the number system Byte : The combination of eight bits Exercises 1. Answer the following questions. a) What is the number system? b) Define base or radix of the number system. c) What is hexadecimal number system? d) What is computer word? e) Write different types of number system. f) How many bytes are there in 2GB? 2. Convert the given numbers as indicated. a. Decimal to Binary Conversion. i. (45)10 ii. (88)10 iii. (180)10 iv.(207)10 v. (444)10 vi. (875)10 ix. (2090)10 x. (555)10 vii. (1406)10 viii. (920)10 Computer Science : Grade 10 99

b. Binary to Decimal Conversion. c. i. (1010)2 ii. (11100)2 iii. (10011)2 iv. (101110)2 v. (101011)2 d. e. vi. (10110111)2 vii.(110011)2 viii. (100001)2 f. g. ix. (101010)2 x. (111110)2 h. i. Decimal to Octal Conversion. j. 100 i. (56)10 ii. (123)10 iii. (437)10 iv. (279)10 v. (555)10 ix. (1723)10 x. (2078)10 vi.(684)10 vii. (808)10 viii. (901)10 Octal to Decimal Conversion. i. (114)8 ii. (234)8 iii. (444)8 iv. (567)8 v. (601)8 ix. (732)8 x. (711)8 vi.(723)8 vii. (1230)8 viii. (2233)8 Decimal to Hexadecimal Conversion. i. (76)10 ii. (106)10 iii. (321)10 iv. (808)10 v. (2047)10 vi. (2019)10 vii. (5544)10 Hexadecimal to Decimal Conversion. i. (98)16 ii. (1067)16 iii. (888)16 iv. (AC5)16 v. (A02)16 vi. (FED)16 vii. (1E1)16 viii. (8A0)16 Binary to Octal Conversion. i. (100)2 ii. (1010)2 iii. (11001)2 iv. (100100)2 v. (11100)2 vi. (110110)2 vii. (111110)2 viii. (11001100)2 Octal to Binary Conversion. i. (64)8 ii. (104)8 iii. (224)8 iv. (333)8 v. (467)8 vi. (524)8 vii. (667)8 viii. (7004)8 Binary to Hexadecimal Conversion. i. (100)2 ii. (1010)2 iii. (11100)2 iv. (110011)2 v. (101010)2 viii.(1100111)2 vi. (100110)2 vii. (11001100)2 Hexadecimal to Binary Conversion. i. (81)16 ii. (105)16 iii. (981)16 iv. (1AC)16 v. (B0F)16 vi. (BCD)16 vii. (7BF)16 viii. (90E)16 Computer Science : Grade 10

k. Octal to Hexadecimal Conversion. i. (64)8 ii. (124)8 iii. (205)8 iv. (5434)8 v. (1150)8 vi. (345)8 vii. (734)8 viii. (7314)8 l. Hexadecimal to Octal Conversion. i. (894)16 ii. (1AC)16 iii. (BAD)16 iv. (10001)16 v. (AB6)16 vi. (B00)16 vii. (ABC)16 3. Calculate the following as indicated. a. Perform the following binary addition. i. (10100)2 + (1101)2 ii. (1011)2 + (1101)2 iii. (10111)2 + (1111)2 iv. (1110)2 + (1110)2 v. (10100)2 + (10011)2 vi. (111001)2 + (101100)2 vii. (100111)2 + (11010)2 viii. (100100)2 + (110011)2 b. Perform the following binary subtraction. i. (1100)2 - (1000)2 ii. (1100)2 - (100)2 iii. (10100)2 - (110)2 iv. (101100)2 - (10011)2 v. (1100111)2 - (1011)2 vi. (110011)2 - (10100)2 vii. (100100)2 - (11010)2 viii. (1000010)2 - (110101)2 c. Perform the following binary multiplication. i. (110)2 × (11)2 ii. (101)2 × (10)2 iii. (111)2 × (11)2 iv. (1100)2 × (101)2 v. (11010)2 × (110)2 vi. (101100)2 × (100)2 vii. (11001)2 × (1100)2 viii. (11000)2 × (1001)2 d. Perform the following binary division. i. (111)2 ÷ (10)2 ii. (10110)2 ÷ (10)2 iii. (1101)2 ÷ (11)2 iv. (101101)2 ÷ (101)2 v. (111010)2 ÷ (110)2 vi. (100101)2 ÷ (110)2 vii. (1000100)2 ÷ (1100)2 viii. (1110011)2 ÷ (1010)2 Computer Science : Grade 10 101

e. Perform the following binary simplification. i. (11 ×10)2 + (11)2 ii. (10 ×11)2 + (101)2 iii. (1001 + 110)2 - (1000)2 iv. (1010 × 110)2 ÷ (10)2 v. (100110 + 110)2 ÷ (110)2 vi. (10011)2 × (101)2 vii. (1000 + 100)2 ÷ (11)2 viii. (11100-11)2 × (100)2 Project Work 1. Draw the “conversion of different number system” on a sheet of chart paper and paste it in your class room. 2. Prepare a presentation about different types of number system on computer and present in your class as group work. 102 Computer Science : Grade 10

Unit 2 Database Management System 2.1 Introduction to Database Management System Every organization have to maintain data concerning their activities, business, transaction, employees etc. For example, your school maintains data regarding a student's general information, academic performance, punctuality and discipline. These data need to be kept in such a way that it can be readily available and can be presented easily in desired formats. The data also needs to be updated and appended regularly. Actually, these data are manually maintained in various files in most cases. As you can notice in the below Table - Telephone directory, it has information distributed under the headings, such as S.No. Name, Address, Telephone Number. Furthermore, the entire information is alphabetically organized for easy retrieval. Table - Telephone Directory Address Telephone No. S.No. Name Janakpur, Nepal 041-634544 1. Arjun Yadav Dang, Nepal 082-520907 2. Hari Chaudhary Kavre, Nepal 011-543678 3. Bishnu Devkota Well organized information as above is database. In other words, a collection of systematically organized inter-related data is called a database. Data and Information Raw form of any facts, figures or entities are known as data. Data alone does not give any meaning. For example, Aaradhya, 1000, account, balance etc. are raw data individually does not give any meaning. The processed form of data is known as information. When the data becomes information it gives meaningful result. For example, Aaradhya has 1000 balance in his bank account. Here Aaradhya, 1000, account, balance all have their significant meaning. Thus, we can say that information is the organized collection of inter-related data. Computer Science : Grade 10 103

Purpose of Database Database is used to store, organize and retrieve data. Database can be computerized and non-computerize (manual). A database may have single table or multiple tables. The data in a database are organized in rows and columns. Some examples of database are Telephone directory, mark ledger, attendance register, dictionary etc. Database Management System (DBMS) Database management system (DBMS) is a computerized system that stores data, processes them and provides information in an organized form. DBMS basically deals with the creation of database, its management and retrieval process. Creating, modifying, updating, appending, organizing, sorting, removing, and retrieving are the major tasks performed easily, efficiently and accurately using Database management system. Some of the popular DBMS software are MS-Access, Oracle, MS-SQL Server, MySQL, PostgreSQL etc. These DBMS are capable to handle records in multiple tables on the basis of a key field. It allows a user to view or retrieve records from the multiple linked tables continuously at a time. Features of DBMS The following are the features of a DBMS:  Large volume of data can be stored and updated easily.  Provides data integrity and security.  Easy in data administration or data management.  Provides the data sharing facility.  Reduces the data redundancy (duplication of data).  Provides concurrent access, recovers the data from the crashes.  Supports centralized control 104 Computer Science : Grade 10

Components of Database Table Database is composed of one or more tables. Tables are the building block of database. A single table is used to store data of a specific purpose or subject such as telephone directory, book records in library etc. Since entire data is managed and kept in a table for the future retrieval process it is also called the primary object of database. Table stores large volume of data into rows called records and column called field. When fields and records are combined forms a complete table. Table - Book Records Code Book Name Level Author Year of Secondary Jyoti Jha Publication B302 Computer Science 2009 AD B304 Elementary Maths C209 DBMS Higher Secondary Keshav Pant 2010 AD M405 Micro Economics Bachelor Hari Bhattrai 2012 AD Masters Govinda Rana 2007 AD Record A record or row contains information about single items in a database. It is also called tuple. For example, in Table – Book Records, all the information about a book is record. Here, the combination of Code, Book Name, Level, Author, Year of Publication values forms a record. Code Book Name Level Author Year of Publication Jyoti Jha 2009 AD B302 Computer Science Secondary Field Book Name A field or column contains information about a certain type Computer Science for all records. It is also known as attributes. For example, in Elementary Maths Table – Book Records, Code, Book Name, Level, Author, Year DBMS of Publication are the fields of book records table. Micro Economics Computer Science : Grade 10 105

Summary  A collection of systematically organized inter-related data is called a database.  Raw form of any facts, figures or entities are known as data.  The processed form of data is known as information.  Database management system (DBMS) is a computerized system that stores data process them and provide information in an organized form.  Some of the popular DBMS software are MS-Access, Oracle, MS-SQL Server, MySQL, PostgreSQL etc.  Tables are the building block of database.  A record or row contains information about single items in a database.  A field or column contains information about a certain type for all records. Exercises 1. Answer the below questions. a) What is database? Give some examples of database. b) Differentiate between data and information. c) What is DBMS? Name any four DBMS software. d) What are the components of database? 2. Choose the correct answer. a) .................... is the collection of related information. i. Data ii. Meta data iii. Database iv. Facts b) .................... is the raw form of any facts and figures. i. Information ii. DBMS iii. Records iv. Data c) .................... is a DBMS software. i. MS-Access ii. MS-Excel iii. bdase iv. None of them d) A .................. is a collection of data about a specific topic. i. DBMS ii. Database iii. Table iv. All of them 106 Computer Science : Grade 10

e) In a table, row is called .................. and column is called ................. i. record, field ii. field, record iii. record, tuple iv. None of them 3. State whether the following statements are 'True' or 'False'. a) Collection of any data can be a database. b) MS-Access is an example of DBMS. c) Every record in a table is unique. d) A group of related field that describes a person, place, or thing is called a field. e) DBMS is an application software f) Deleting a field does not delete data in the field. Computer Science : Grade 10 107

2.2 Creating Database Using Microsoft Access An Introduction to Microsoft Access MS-Access is DBMS software developed by Microsoft Corporation. This software is distributed along with the Microsoft Office application package. Microsoft Access 2019 is the latest version available in the market. The Access database consists of several different components. Each component is called an object. Access consists of seven objects. The various objects of MS-Access are Tables, Queries, Forms, Reports, Pages, Macros and Modules. All objects of a database are stored in a single file, and the extension of database file is .accdb. Main features of MS-Access a) Creates sophisticated database quickly. b) Analyse and modified your data easily with queries. c) Creates elaborated reports from your data. d) Makes customized data entry forms. e) Presents your data dynamically on the WWW (World Wide Web). We will explore mainly the following four objects of MS-Access a) Tables All databases must have at least one table. Tables are the primary building block of database. All data are stored and managed in a table. Every table in a database focuses on one specific subject. Table stores large volume of data into rows and column. b) Queries A query is simply the question that we ask about the data stored in the table. Query is also used to perform various actions on the data. For example, \"Show the names of students who have scored greater than or equal to 80 marks in science.\" \"Update the salary of employees by 10%.\" c) Forms Forms are the graphical interface used to enter data into the tables or multiple linked 108 Computer Science : Grade 10

tables. Forms are also used to display the information in specific manner, as well as it permits user to add, modify and delete data in a table. Primarily, fields in the table or query are made available to place in the forms that we create. d) Reports Reports are the presentation of information in desired format. Reports are generally created for the printing purpose of any desired information. With Access, we can create reports of any table or query. For example, report card of your final examination. Starting MS-Access Follow the below steps to run MS-Access: Step 1: Press Windows Key and R at the same time. Run window appears. Step 2: Type ‘msaccess’ as shown in the figure alongside. Step 3: Finally click on OK button. Creating a new database file When MS-Access is started, it gives user a choice of either creating a new database or opening an existing database. Follow the below steps to create a new database file: Computer Science : Grade 10 109

Step 1: Click on the Blank desktop database. A Blank desktop database dialog box will appear. Step 2: Type the File Name. Step 3: Choose the required folder where you want to save. Step 4: Click on Create button. 110 Computer Science : Grade 10

Creating Table in MS-Access Follow the below steps to create a table: Step 1: From Create tab, click on Table Design button of Tables group. Step 2: Add the required Field Name and Data Type. Step 3: Right click on the Table1 tab in the above figure. Step 4: Type Table name. Click on OK. Note: A field name can be up 40 characters long. Adding Fields in a table Follow the below steps to add a field in an existing table: Step 1: Open the database and open the table. Step 2: Right click on the field above which you want to add a new field. Computer Science : Grade 10 111

Step 3: Click on Insert Rows. A new blank field is inserted above the selected field. Step 4: Type Field Name and Data Type. Deleting Fields in a table Follow the below steps to add a field in an existing table: Step 1: Open the database and open the table. Step 2: Right click on the field which you want to delete. Step 3: Click on Delete Rows. Defining Data types Data type determines the kind of values that you can store in the field. After you enter the valid field name, it allows a user to select the data type for the field. You can use the Data Type property to specify the type of data stored in a table field such as text for name field, number for class field, Date and time for DOB field etc. Each field can store data consisting of only a single data type. MS-Access supports the following data types: Table - MS-Access Data types Data Type Description Maximum Default Field Used for text or combinations of text Data / Space Size Short Text and numbers, as well as numbers Up to 255 255 those don't require calculations, such Characters as phone numbers. Long Text Lengthy text or combinations of text Up to 64,000 _ and numbers. Characters Number Used for data to be included in 1,2,4 or 8 Bytes Long Integer - Byte mathematical calculations. Stores numbers from 0 to 255 1 byte without decimals. 112 Computer Science : Grade 10

- Integer Stores numbers from -32768 to 2 bytes - Long +32768 without decimals. Integer - Single Stores numbers from -2147483648 4 bytes to +2147483647 without decimals. - Double Date/Time Single precision floating point 4 bytes, 7 numbers from -3.4 x 10-38 to 3.4 decimal places x 1038. Double precision floating point 8 bytes, 15 numbers from -1.797 x 10-308 to decimal places 1.797 x 10308. Used for dates and time data. 8 Bytes Currency Used for currency values. 8 Bytes - _ Auto Number Used for unique sequential 4 Bytes _ (incrementing by 1). _ Yes/No Used for data that can be only one of 1 Bit two possible values, such as Yes/No. _ _ OLE Object Used for OLE objects (such as MS- Up to about 2 _ Word documents, MS-Excel spread GB sheets, pictures, sounds). Hyperlink Used for hyperlinks. Up to 2048 Characters Attachments To attach any supported type of file Up to about 2 GB Lookup Wizard Used to create a field that allows you Dependent on to choose a value from another table the data type of the lookup field Field Description You can enter the description of each field in the Description column. It helps you to remember the use and purpose of a particular field. This is an optional part in a database. It is displayed in the status bar when you select this field on a form. Computer Science : Grade 10 113

Primary Key field Primary Key Primary Key is a special field or group of fields in the table that uniquely identifies each record from the database. To distinguish one record from another, table must contain a unique field named as primary key. The primary key does not accept duplicate value for a field and it does not allow a user to leave the field blank or null. The primary key is an identifier such as a student ID, a Product code, Exam roll no. etc. Hence, primary key is unique to each record. Importance of Primary Key  To identify each record of a table uniquely.  To reduce and control duplication of the record in a table.  To set the relationship between tables. Setting the Primary Key Follow the below steps to create a primary key field: Step 1: Open the table in Design View. Step 2: Click on the field in which you want to apply primary key. Step 3: Click on Design tab. Step 4: Click on Primary Key button from Tools group. A key icon will be displayed on the left side of the primary key field. 114 Computer Science : Grade 10

Field Properties Pane Field properties pane displays list of properties associated with each field data type. To control over the contents of a field we can set the field size, format, validation rule etc. The general properties of fields are given below: Field Properties – ShortText Field Properties – Number a. Field Size You can use the Field Size property to set the maximum size for data stored in the field that is set to the Text or Number data type. For Text data, the amount of data that you can store can be determined by the number of characters you wish to store on it. Maximum is 255 characters. For numeric data, the field size can be set depending upon the type and range of value you wish to store. Below table explains about the numeric data range and storage. Field Size Setting Range of Values Decimal Storage Size Places (in Bytes) Byte 0 to 255 Integer -32768 to 32767 None 1 Long Integer 2147483648 to +2147483647 None 2 Single -3.4 x 10-38 to 3.4 x 1038. None 4 Double -1.797 x 10-308 to 1.797 x 10308 4 7 8 15 Computer Science : Grade 10 115

b. Caption Caption field property is the alternative name given for any field. This helps to make field name more explanatory. The maximum size for this is 2048 characters. It is also a label for a field when used on a form. If caption is not entered, field name is used for label for a field on a form. c. Default Value Default Value field property is one that is displayed automatically for the field when you add a new record to the table. d. Format Format field property allows you to display data in a format different from the way it is actually stored in a table. It is also known as display layout for a field. Depending on the type of data, the option in the Format menu will differ. e. Input Mask Input mask field property specifies the pattern of data that you wish to enter into the specific field. It is simply the control over the data entry. This field property does a couple of tasks in a field. They are:  It forces you to enter field values in a specified pattern.  It can fill in constant characters like dash (-), parenthesis ( ) and slash (/) and optionally store these characters as part of the field value as you do not have to enter these characters at the time of data entry. f. Validation Rule It is used to limit the values that can be entered into a field. Data validation in various cases are explained in the below table. 116 Computer Science : Grade 10

Expression Example Description Validation expression for numbers < <100 Entered value must be less than 100. > >50 Entered value must be greater than 50. <> <>0 Entered value must be not equal to 0. <= <=100 Entered value must be less than or equal to 100. >= >=50 Entered value must be less than or equal to 50. = =10 Entered value must be equal to 10. Between .... Between 0 and Entered value must be within the range of 0 to 100. and ... 100 Validation expression for dates < # 1/30/2012 # Entered date must be before January 30, 2012. > # 1/30/2010 Entered date must be after January 30, 2010 and after 5:30 PM. 5:30 PM # <= #1/30/2012 # Entered date must be before or on January 30, 2012. >= # 1/30/2010 Entered date must be after or on January 30, 2010. > Date() Entered date must be after the current date. < Date() Entered date must be before the current date. > Now() Entered date must be today after the current time or < Now() any other day in future. Entered date must be today before the current time or any other day in past. Validation expression for texts Here * represents zero or more characters. It checks Like \"B*\" that the value starts with B (or b), followed by zero Like \"*up\" or more characters. It checks whether text ends with characters \"up\". Like Like \"?????UP\" Here? represents one character. It accepts 7 characters and ends with UP. Here # also represents one character but it is number. Like \"####CSX\" It accepts 7 characters and ends with CSX and preceded by five numbers. Like \"[BO]????\" It accepts 5 characters and first character is either B or O. Like \"[A-Z] [A- It accepts four characters (Only letters). [A-Z] Z] [A-Z] [A-Z] represents characters ranges from A to Z. Computer Science : Grade 10 117

Validation expression with Boolean operators AND >=10 AND <=50 Entered value must be within the range of 10 to 50 OR >500 OR <100 Entered value must be either greater than 500 or less than 100. g. Validation Text Validation Text is the error message that appears if the data entered is invalid according to the specified validation rule. Validation Rule for Section Validation Text Message Box Field – “A” Or “B” h. Required You can use required property to specify whether a value is required in a field or not. So, Yes and No are the options for required property. If this property is set to Yes for a field, the field must receive value during data entry. If this property is set to No for a field, the field can be left blank. i. Indexed You can use the Indexed property to set an index on a field. It speeds up searching and sorting of records based on a field. For example, if you search for specific student name in a SName field, you can create an index for this field to speed up the search for the specific name. By default, indexed property is set as No. It also uses other two settings. 118 Computer Science : Grade 10

Table - Indexed property settings Setting Description No No indexing. Yes (Duplicates OK) The index allows duplicates. Yes (No Duplicates) The index does not allow duplicates. Summary  MS-Access is DBMS software developed by Microsoft Corporation. It is distributed with MS-Office package.  Tables, Queries, Reports and Forms are the four main objects of MS-Access database.  Tables are the primary building block of database.  Data type determines the kind of values that you can store in the field.  Data Type supported by MS-Access are Short Text, Long Text, Number, Date/ Time, Currency, AutoNumber, Yes/No, OLE Object, Hyperlink, Attachment and Lookup Wizard.  Primary Key is a special field or group of fields in the table that uniquely identifies each record from the database.  Caption is the alternative name given for any field.  Indexed property speeds up searching and sorting of records based on a field. Exercises 1. Answer the below questions a) List any four features of MS-Access. b) What is a database? Give any two examples. c) What is database object? List any four database objects. d) What does data type? Name any four data types in MS-Access. e) Which data type is used to store numeric characters or special symbols in MS-Access? Computer Science : Grade 10 119

f) What is primary key? Why is it important to specify? g) List any four advantages of primary key. h) What are field properties? Name any four of them. i) Define indexing. Mention its importance. 2. Choose the best answer. a) What is extension of access database file? i. .DBF ii. .ACCDB iii. .MBD iv. ii & iii b) Object primarily used to store the data in database. i. Table ii. Form iii. Query iv. Report c) A field name can be up to .................. characters long. i. 40 ii. 46 iii. 64 iv. 2048 d) .................. is a data type used for lengthy text and numbers i. Text ii. Long Text iii. OLE Object iv. Lookup Wizard e) Data type that requires 8 bytes of storage. i. Yes/No ii. Currency iii. Long Integer iv. All of them f) The name of caption can be declared up to ……….. characters. i. 1024 ii. 2024 iii. 2048 iv. 2005 g) Memory Space used by an auto number data type is ………………. i. 1 Byte ii. 2 Bytes iii. 4 Bytes iv. 8 Bytes 3. State whether the following statements are 'True' of 'False'. a) Lookup Wizard is a tool that automates many database tasks. b) Default field size of text type of data is 65535 characters. c) Date/Time type of data requires 8 bytes of memory. d) Removing a primary key does not delete a field. e) Validation rule is used to specify whether a value is required in a field. f) Indexed field property speeds up searching and sorting of records based on a field. 120 Computer Science : Grade 10

4. Match the following Group B a. Group A i. 255 Number ii. 8 Bytes Yes/No iii. Long integer Currency iv. 1 bit Text v. Short integer b. Group A Group B Data Redundancy i. Error message Validation Text ii. Pattern of data in a field Input Mask iii. Lookup wizard Validation Rule iv. Data duplication v. Limits the values C. Group A Indexing data Group B Long text i. Retrieves data FoxPro ii. Caption Label for a field iii. Memo field iv. DBMS v. Searching fast 5. Write the most appropriate technical term of following statements.  Field property that limits the values that can be entered into a field  The value automatically enters for the new record  A field or a group of fields that uniquely identifies the records in database.  A message displayed after data is entered and checked into a field.  Field property that defines label for the field Computer Science : Grade 10 121

2.3 Entering and Editing Data Table Datasheet and its Formatting Table datasheet is simply the display of records in row and column format. Using the datasheet view, you can add, modify, search or delete records. There are mainly two views of Table. a) Design View – Related with table structure. You can add, edit or delete field and its properties. b) Datasheet View – Related with records. You can add, modify, search or delete records. Switching to Datasheet View: Follow the below steps to switch from Design View to Datasheet View: Step 1: Click on the Design Tab. Step 2: Click on View drop-down button from Tools group. Step 3: Click on Datasheet View. Adding Records in a Datasheet After the table is created through design view option, you open it in datasheet to enter the records. Follow the below steps to add records in datasheet: Step 1: After you, open datasheet mouse cursor will be placed in the first field of Step 2: first record. Type the required data in the field. Step 3: Press Tab or Arrow to move to the next field. Or, simply place the mouse cursor in the desired field. While entering the data in a field, next row for new record will be automatically displayed. For example, if you are entering the record in first row, second row automatically appears. Repeat the above steps to enter the data in fields till required. 122 Computer Science : Grade 10

Step 4: After finishing the adding records in the datasheet, you can close the datasheet simply by clicking on close button. Modify Records in a Datasheet You can open the existing table and can modify the date in datasheet. Follow the below steps to modify records in datasheet: Step 1: Open the table in Datasheet View. Step 2: Put the mouse cursor in the desired cell. Step 3: Type new value or edit the existing value of the desired cell. Delete Records from a Datasheet You can remove the unwanted records of the datasheet. Follow the below steps to delete records in datasheet: Step 1: Open the table in a Datasheet View. Step 2: Right-click on the square box left to Step 3: the record which you want to delete. A Step 4: pop-up menu will appear. Click on Delete Record option. A message box will appear. Click on Yes. Hiding the columns or fields You can temporarily hide a column(s) or field(s) so that you can view more columns in a larger datasheet. Follow the below steps to hide fields: Step 1: Right-click on the column heading Computer Science : Grade 10 123

Step 2: (field name) which you want to hide. A pop-up menu will appear. Click on Hide Fields option. Unhide the hidden fields Follow the below steps to unhide the hidden columns: Step 1: Open the table in Datasheet Step 2: View. Step 3: Right-Click on any Column Step 4: heading. A pop-up menu will Step 5: appear. Click on Unhide Fields option. An Unhide Columns dialog box will appear with the list of fields. Click on the check box of required field to unhide. Click on Close. Freeze Columns You can freeze one or more columns on a datasheet so that they become the leftmost columns and are visible at all times no matter wherever you scroll. Follow the below steps to freeze column(s): Step 1: Open the table in Datasheet Step 2: View. Step 3: Right-Click on the column heading which you want to freeze. A pop-up menu will appear. Click on Freeze Fields option. 124 Computer Science : Grade 10

Unfreeze Columns You can unfreeze columns on a datasheet. Follow the below steps to unfreeze column(s): Step 1: Right-Click on the column heading. A pop-up menu will appear. Step 2: Click on Unfreeze All Fields option. Adjusting Column Width In datasheet we can adjust the column width as required to fit the data in the field. In some case field might contain the longer data that might not be easily visible and in other case field might contain the short data that taking unnecessary longer width. So, to improve from this inefficient view of datasheet we have to adjust the column width. This can be done in the following ways. Method #1 GENDER ROLL NO NAME By dragging the field sizing line from the right most corner of the field header. Method #2 By double clicking on the field sizing line. Method #3: By setting the fixed width in a column width dialog box. Step 1: Right-Click on the header of column. Step 2: Click on Field Width option. A Column Width dialog box will appear. Step 3: Type the required width value. Step 4: Click on OK. Computer Science : Grade 10 125

Adjusting Row Height In datasheet we can also adjust the height of row as required to fit the records in the row. This can be done in below mentioned two ways. Method #1 By dragging the row sizing line from the bottom most corner of the row. Method #2 By setting the row height in a Row Height dialog box. Step 1: Right-Click on the square box left to the required row. A pop-up menu will appear. Step 2: Select Row Height option. A Row Height dialog box will appear. Step 3: Type the required row height. Step 4: Click on OK. Sorting Records The process of arranging all the records in a table either ascending or descending order based on field or fields is known as sorting.  Text types of data are sorted in alphabetical order (i.e. A to Z or Z to A).  Date and Time data are sorted from oldest to newest or newest to oldest.  Number/Currency types of data are sorted in smallest to largest or largest to smallest. Follow the below steps to sort data of Short Text field: Step 1: Open the table in Datasheet View. 126 Computer Science : Grade 10

Step 2: Right-Click on the column heading of a field which you want to sort. A pop-up menu will appear. Step 3: Click on Sort A to Z option. The data will be sorted in alphabetical order. Before Sorting After Sorting Field with Memo, Hyperlink and OLE Objects data types cannot be sorted. After the sorting process, you need to save the table to keep changes. Summary  There are two views of Table: Design View and Datasheet View.  Using the datasheet view, you can add, modify, search or delete records.  You can hide/column and freeze/unfreeze required column(s) of a table.  The process of arranging all the records in a table either ascending or descending order based on field or fields is known as sorting. Exercise 1. Answer the following questions. a) Define datasheet with examples. b) Which view is used to modify a table in MS-Access? c) Mention the process to add records in a datasheet. d) Define freezing and unfreezing the column. How is it done? e) Explain data sorting. List any two advantages of using it. Computer Science : Grade 10 127

2. Tick () the correct answer. a) In a datasheet, what does each column represent? i. Record ii. Field iii. Database iv. Table b) In a datasheet, what does each row represent? i. Record ii. Field iii. Database iv. Table c) A table structure can be modified in ……….. i. Datasheet view ii. Wizard iii. Design view iv. None of them d) The process of arranging all the records in a table either ascending or descending order i. Sorting ii. Indexing iii. Filtering iv. All of them 3. State whether the following statements are 'True' or 'False'. a) We cannot change the structure of a table once it is made. b) We can enter the data using datasheet view. c) You cannot unhide the hidden columns in a table. d) Data can be modified in datasheet view. e) Field with Memo, Hyperlink and OLE Objects data types cannot be sorted. 128 Computer Science : Grade 10

2.4 Querying Database i. Query Query is the question asked for the database. Information stored in any database becomes meaningful only when you will be able to retrieve the desired information. So, using the various types of queries you can retrieve the information that you desire with some specific purpose. So, query is an object of database that is used to view, retrieve, change and analyse records from a table or multiple linked tables based on specified condition. Using a query, you can answer very specific questions about your data that would be difficult to answer by looking at table data directly. You can use queries to filter your data, to perform calculations with your data, and to summarize your data. There are two main types of Query used in Access. Select Query : This type of query serve data from the table as per the user’s request without making any change in the underlying data. Action Query : This type of query changes data in the source table. The examples of Action Query are Update Query, Delete Query and Append Query. a) Select Query Select query is simply used to select and display the relevant data from the database. You can select all or part of data from a single or multiple tables or existing queries and display in datasheet. You can also use a select query to group records and calculate sums, counts, averages, minimum, maximum and other types of totals. Creating a Select Query Follow the below steps to create a select query: Step 1: Open the database file created in MS-Access. Step 2: Click on Create tab. Computer Science : Grade 10 129

Step 3: Click on Query Design button from Queries group. QBE (Query By Example) grid will appear. Also, a Show Table dialog box will appear with Step 4: the list of tables and queries created in the database. Step 5: Select the required table based on which you are creating query. Click on Add. Click on Close button. QBE Grid The QBE grid appears with two sections. The upper section shows the list of fields in the selected table and the lower section shows the grid with rows and columns where we define the query. The first row in the grid shows the fields that we have selected from the table. The second row shows the table from which the field is selected. The third row is ‘Sort’. We can select ‘Ascending’, ‘Descending’ or ‘Not sorted’ for sort. If we set sorting option (ascending or descending) for more than one field then the leftmost field for which sorting is enabled becomes the primary sort key and other sorting enabled fields subsequently become the secondary sort keys. Show row has check box for each field. The fields that are checked at this row are displayed and those not checked are not displayed. The fifth row is ‘Criteria’. Here, we can provide criteria or condition to select the records from the table. The last row is ‘or’. Here, we provide the criteria which are tested with OR logical operator with the above condition or criteria mentioned in the row ‘Criteria’. Now, do the following tasks in the QBE grid: Step 1: First you need to bring the required field of table in the grid. We can drag Step 2: or double click each field to bring them in the grid. So, bring the required field of the selected table from the upper section of QBE. Choose the fields and select either Ascending or Descending from the Sort 130 Computer Science : Grade 10

Step 3: row, if you want to display the result in sorted order of any field. Step 4: Remove the tick (√) mark from the checkbox of the Show row, if you don’t want to show field(s). Set the criteria in Criteria row. In this example, “10” is written in the Class field and “A” is written in the Section field. It means only the records of Class 10A are selected in the query. Running the Query After making a query, you need to run. Follow the below steps to run a query: Step 1: Click on the Design tab under Query Tools. Step 2: Click on Run button from Results group. A new datasheet will be displayed based on the criteria and other settings we provided in the query. Result of the above query Details Table – Data source for the Query Results Computer Science : Grade 10 131

Calculation in Select Query As mentioned earlier, select query can be used for mathematical calculation and display the result in new field. Let’s explore the below example to understand how to perform this. Step 1: Create a below table in MS-Access and named as “Marks” Datasheet View – Table Marks Step 2: Design View – Table Marks Step 3: To create a query, click on Create tab and click on Query Design button from Queries group. Step 4: Select the table “Marks” from the Show Table dialog box. Click on Add Step 5: and then Close. Bring all the fields of “Marks” in the QBE grid. In the QBE grid, type a new field name “Total” next to the field “Computer” and use the below expression to calculate Total marks. Total: [English] + [Math] + [Computer] 132 Computer Science : Grade 10

Step 6: Run the query. You will see the new column with calculated total marks. b) Action Query An action query is a query that makes changes to or removes many records in just one operation. Here, we discuss only two types of action query. Below are the four different types of action queries. i) Update Query An update query makes entire changes to a record or group of records in one or more tables. For example, you can increase the salary of all staff by 10% from a single operation in the staff database. With an update query, you can change data in existing tables. Let’s explore the below example to understand how to perform this. Step 1: Create a below table in MS-Access and named as “Staff” Design View – Table Staff Datasheet View – Staff Step 2: To create a query, click on Create tab and click on Query Design button from Queries group. Computer Science : Grade 10 133

Step 3: Select the table “Staff” from the Show Table dialog box. Click on Add and Step 4: then Close. From the Design tab, select Update type from Query Type group. Step 5: As we are going to update the value of Salary field only, bring the field “Salary” of “Marks” in the QBE grid by double-clicking on it. Step 6: Type the below expression in the “Update To” row: [Salary]*1.1 Step 7: Run the query. Step 8: A message box will appear for your confirmation. Click on Yes. And see the below updated result in the table “Staff”. Before Delete – Table Marks Before Update – Table Staff ii) Delete Query A delete query deletes a record or group of records from one or more tables. For example, you can delete the records of those students who have got marks less than 40 in each subject in the marks database. Let’s explore the below example to understand how to perform this. Step 1: Open the database file that has the table “Marks” Step 2: To create a query, click on Create tab and click on Query Design button from Queries group. Step 3: Select the table “Marks” from the Show Table dialog box. Click on Add and then Close. Step 4: From the Design tab, select Delete type from Query Type group. 134 Computer Science : Grade 10

Step 5: You need to give criteria to select records in order to erase. In this example, Step 6: we are erasing the records of those students who have got marks less than 40 in each subject. So, we have to apply condition in all the fields of subjects. Bring all the subject fields in the QBE grid by double-clicking on them. Provide condition in the Criteria row as in below figure. Note: Here, the condition <40 for each subject is given in the different lines of criteria to use OR operator. If you write the condition in the same line of Criteria row, AND operator will be applied. Step 7: A message box will appear for your confirmation. Click on Yes. And open the table to check whether the records are deleted or not. Before Delete – Table Marks After Delete – Table Marks iii) Inserting records into table from queries Using a query, you can insert record into a desired table. Let’s explore the below example to learn how to insert new record using SQL statement. SQL (Structured Query Language) is a language used to communicate with database. We can use several SQL statements to do different tasks with your database. Here, INSERT statement is used to insert record into a table. Computer Science : Grade 10 135

Follow the below steps to insert a new record using INSERT SQL statement: Step 1: Let’s assume you have already created the below table “Marks”. If not, create this table first where you are going to insert new record using query. Step 2: Click Create tab and click Query Design from Queries Group. Step 3: Close the Show Table window. Step 4: From the Design tab, click on the SQL View button in Results group. A SQL View window will appear. Step 5: Type the below INSERT statement in the SQL View window as below figure. insert into Marks values (6,'Rabin Shahi',78,54,32) ; Table Marks In the above INSERT statement, Marks – Name of the table 6,'Rabin Shahi',78,54,32 – Values for 5 fields (Text field value is enclosed within single quote 'Rabin Shahi') Step 6: Run the query. A message will appear for your confirmation. Step 7: Click Yes and open your table to see new records. 136 Computer Science : Grade 10

In the above table, one new record is added. Wildcard Characters and Operators in Query Wildcards/ Example Description Operators ? ?????UP It represents a single character. ?????UP shows the records from the specified field that has first any five character before UP. Represents the number of characters. S* */ S* / Like S* retrieves the record from the field that starts from the character S. # Represents any single digit, 0-9. > >100 Shows the record where the specified field is greater than 100. > <200 Shows the record where the specified field is >= >=100 less than 200. <=200 Shows the record where the specified field is <= <>50 greater than or equal to 100. Shows the record where the specified field is <> less than or equal to 200. Between.... Shows the record where the specified field is And not equal to 50. Between #02/04/10# Shows the record between 02/04/10 to And #04/07/12# 04/07/12 date. Computer Science : Grade 10 137

In In(\"Ram\", \"Hari\") Shows the record that has the field value as Ram or Hari. AND >60 And <=80 Shows the record that has specific value from OR \"Bus\" OR \"Car\" 61 to 80 Is Null Shows the record that has specific value Bus IS not Null or car. Shows the record that has no value. Shows the record that has no null (blank) value. Summary  Query is the question asked to database.  There are two main types of Query used in Access: Select Query and Action Query.  Select query is simply used to select and display the relevant data from the database.  An action query is a query that makes changes to or removes many records in just one operation. Examples of action query are update query and delete query. Exercise 1. Answer the following questions. a) What is query? List the different types of query. b) What is the importance of Query in database? c) Differentiate between Select query and Action query. d) Lists the cases in which update query can be used. e) Explain the method of inserting new record into a table using query. 2. Tick the correct answer. a) The frame work for viewing records with some criteria in database is ............ i. Query ii. Form iii. Report iv. Table 138 Computer Science : Grade 10

b) Which query do you use to answer the question “Which employees earn more than $5000 a month”? i. Search query ii. Append query iii. Select query iv. Update query c) Which criteria in query return only those names beginning with the letter “S”? i. name = “S” ii. name = “S*” iii. name = “S?” iv. name = “S#” d) ............... query is used to display the relevant data from the database without making any changes. i. Select ii. Parameter iii. Crosstab iv. Append e) A(n) ............... query makes entire changes to a record or group of records in one or more tables i. Select ii. Make Table iii. Update iv. Append 3. State whether the following statement are 'True' or 'False'. a) A query is used to select fields and records from one or more tables. b) A query can be created without tables. c) You can select all or part of data from a single or multiple linked tables using query. d) Update query can even make changes to the auto number field. Computer Science : Grade 10 139

2.5 Creating and Using Forms Forms Form is one of the MS-Access database objects that is primarily used to create an interface for entering data in a table or multiple linked tables. Forms are basically GUI (Graphical Users Interface) using which users interact with MS-Access database. Form also displays complete record one at a time, so you can view and modify records using the form. Creating form by using the wizard The wizard enables you to select the fields from a table or multiple linked tables, layout, and user interface for the form. Follow the below steps to create forms using the wizard option: Step 1: Open the Access database file. Step 2: Click on Create tab. Step 3: Click on Form Wizard button from Forms group. Form Wizard dialog Step 4: box will appear. Choose the required table from the Tables/Queries drop-down list. Step 5: A list of fields of the selected table will be displayed. Now, you have to select the fields that you want to keep in the form from the Available Fields box. To select fields as per the requirement, click on single arrow button to select 140 Computer Science : Grade 10

a single field or you can also select all the available fields at once by clicking on double arrow button. After selecting fields click on Next button. Step 6: Select the desired layout. In this example, Columnar is chosen. Click Next. Step 7: Type the title of the form. In this example, Marks_Forms is typed. Step 8: Click on Finish button. Now, the form for the Marks table is created as below: First Previous Click here and type Record Record to edit field values Computer Science : Grade 10 New (blank) Record Last Record Next Record 141

Entering Data Using a Form After the form is created, you can enter the data simply typing in the text box. MS-Access form also contains the various control options while entering the data. These control options are, Text box, Combo box, List box, Check box, Label, Command button, Tab control, Image, hyperlink etc. Steps to add records in form datasheet are as follows:  After you open form datasheet, mouse cursor will be placed in the first field of first record. Type the required data in the field.  Press Tab or enter to move to the next field. Or, simply place the mouse cursor in the desired field.  When you finish entering the all the data for the first record, MS-Access automatically prompts for entering the second record and so on. Summary  Form is one of the MS-Access database objects used to view, modify and add records.  The Form Wizard enables you to select the fields from a table or multiple linked tables, layout, and user interface for the form. Exercise 1. Answer the following questions. a) What is form? Mention its uses. b) Explain the process to create form using wizard. c) Why form is required in MS-Access? 2. State whether thefollowing statements are 'True' or 'False'. a) The frame work for entering records in database is form. b) Forms can be used to enter, retrieve, and display information c) Forms can be created without table or query. d) Data changed using form gets changed into a table. e) User interface of the form can be changed as per user's choice. 142 Computer Science : Grade 10

2.6 Creating and Printing Reports Report Report is one of the MS-Access database objects used to present information in an effective and organized format that is ready for printing. Using the report, you can display the information the way you want to view it. A report's record source refers to the fields in the underlying tables and queries. A report need not contain all the fields from each of the tables or queries that it is based on. Creating a report is very similar to creating a form. Creating Report by using Wizard The wizard enables you to create a report in a desired format. The Report Wizard provides you with more flexibility such as you can choose the tables and fields, group the data, sort the data, summarize the data, choose a layout and orientation, apply a style, and title your report. Follow the below steps to create a report using Wizard: Step 1: Open the Access database file. Step 2: Click on Create tab. Step 3: Click on Report Wizard button from Reports group. Report Wizard dialog box will appear. Step 4: Choose the required table from the Tables/Queries drop-down list. Computer Science : Grade 10 143