["Smart Computer Science Book-10 Conversion of Decimal to Octal Follow these points to convert decimal to octal number: \u2022\t Divide the given decimal number by the base of octal number 8. \u2022\t Note down the remainder in the right hand side as shown in the example. \u2022\t Continue this process until the quotient is obtained zero (0) . \u2022\t Collect all the remainders from bottom to top. The obtained value is the final result. Example - 1 8 76 Example - 2 8 98 (76)10 = (?)8 89 (98)10 = (?)8 8 12 4 Remainder 2 81 1 Remainder814 0 0 1 1 \t\t= 114 \t\t= 142 \t\t\u2e2b (76)10 = (114)8 Ans. \t\t\u2e2b (98)10 = (142)8 Ans. Conversion of Octal to Decimal Follow these points to convert Octal to Decimal number: \u2022\t Multiply each digit of the given octal number by 8 with raised to the power of positional notation value. \u2022\t The positional notation value starts from zero that must be positioned from right to left. \u2022\t Add all the products multiplying. Example - 1 Example - 2 (114)8 = (?)8 (142)8 = (?)10 = 1 \u00d7 82 + 1\u00d7 81+ 4 \u00d7 80 = 1 \u00d7 82 + 4\u00d7 81+ 2 \u00d7 80 = 64 + 8 + 4 = 64 + 32 + 2 = 76 = 98 \u2e2b (114)8 = (76)10 Ans. \u2e2b (142)8 = (98)10 Ans. Conversion of Decimal to Hexa-Decimal Follow these points to convert decimal to hexa-decimal number: \u2022\t Divide the given decimal number by the base of hexa-decimal number 16. \u2022\t Note down the remainder in the right hand side as shown in example. \u2022\t Continue this process until the quotient is obtained zero (0). \u2022\t Collect all the remainders from bottom to top. The obtained value is the final result. Approved by Curriculum Development Center (CDC) 151","Number System Remainder Remainder Example - 1 16 146 2 Example - 2 16 46 14 (146)10 = (?)16 16 9 9 (46)10 = (?)16 16 2 2 0 0 \t\t= 92 \t\t= 14 represents E \t\t\u2e2b (146)10 = (92)16 Ans. \t\t= 2E \t\t\u2e2b (46)10 = (2E)16 Ans. Conversion of Hexa-Decimal to Decimal Follow the following points to convert hexa-decimal to decimal number: \u2022\t Multiply each digit of the given hexa-decimal number by 16 with raised to the power of positional notation value. \u2022\t The positional notation value starts from zero that must be positioned from right to left. \u2022\t Add all the products after multiplying. Example - 1 Example - 2 (92)16 = (?)10 (9E)16 = (?)10 = 9 \u00d7 161 + 2\u00d7 160 E represents 14 = 144 + 2 = 9\u00d7 161 + 14\u00d7 160 = 144 + 14 = 146 = 158 \u2e2b (92)16 = (146)10 Ans. \u2e2b (9E)16 = (158)10 Ans. Binary to Octal conversion To convert binary number to octal number, carry up the following rules: \u2022\t Group in the given binary number into three bits from right to left. \u2022\t Write the equivalent octal number of each group from conversion table. \u2022\t Collect the result from left to right of each group and give the base 8 for the final result. Example-1 (101010111)2= (?)8 Group in the given binary number into three digits from right to left. Group in three bits 101 010 111 Octal equivalent 5 2 7 \u2e2b (101010111)2= (527)8 Ans. 152 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 Example-2 (10101011110)2= (?)8 Group in the given binary number into three digits from right to left. Group in three bits 010 101 011 110 Octal Equivalent 2 5 3 6 \u2e2b (10101011110)2= (2538)8 Ans. Octal to Binary conversion To convert the binary number to octal number, carry up the following rules: \u2022\t Separate each octal digit of given octal number \u2022\t Write the equivalent binary number of each octal digit from conversion table. Make sure each group should be of three bits. \u2022\t Collect the result left to right from the each group and give the base 2 for the final result. Example-1 (4521)8= (?)10 Separate each octal digit of given octal number. Octal Digit 45 2 1 3 bits Equivalent 100 101 010 001 \u2e2b (4521)8= (100101010001)2 Ans. Example-2 (4121)8= (?)10 Separate each octal digit of given octal number. Octal Digit 41 2 1 3 bits Equivalent 100 001 010 001 \u2e2b (4121)8= (100001010001)2 Ans. Binary to Hexadecimal conversion To convert the binary number to hexadecimal number, carry up the following rules: Approved by Curriculum Development Center (CDC) 153","Number System \u2022\t Group in the four bits of given binary number from right to left. \u2022\t Write the equivalent hexadecimal number of each group from conversion table. \u2022\t Collect the result left to right from the each group and give the base 16 for the final result. Example-1 (101110101)2= (?)16 Group in the given binary number into four bits from right to left. Group in four bits 0001 0111 0101 Octal equivalent 1 7 5 \u2e2b (101110101)2= (175)16 Ans. Example-2 (10111101101)2= (?)16 Group in the given binary number into four bits from right to left. Group in four bits 0101 1110 1101 Octal equivalent 5 E D \u2e2b (10111101101)2= (5ED)16 Ans. Hexadecimal to Binary conversion To convert binary number to hexadecimal number, follow the following rules: \u2022\t Separate each hexa-decimal digit of given hexa-decimal number \u2022\t Write the equivalent binary number of each digit from conversion table. Make sure each group should be of four bits. \u2022\t Collect the result left to right from the each group and give the base 2 for the final result. Example-1 (5219)16= (?)2 Separate each notation of given hexadecimal number. Hexa decimal digit 5 2 1 9 4 bits equivalent 0101 0010 0001 1001 \u2e2b (5219)16= (0101001000011001)2 Ans. 154 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 Example-2 (72FA)16= (?)2 Separate each notation of given hexadecimal number in group. Hexa decimal digit 7 2 F A 4 bits equivalent 0111 0010 1111 1010 \u2e2b (72FA)16= (0111001011111010)2 Ans. Octal to Hexadecimal conversion To convert the octal number to hexadecimal number, carry up the following rules: \u2022\t Separate each octal digit of given octal number. \u2022\t Write three bits equivalent to each octal number and collect all the bits from left to right. \u2022\t Now, again make the group of four bits of obtained binary number from right to left. \u2022\t Write respective hexadecimal numbers for each binary group using conversion table. \u2022\t Collect all the obtained digits from left to right and give base 16 for the final result. Example-1 (4531)8= (?)16 Separate each octal digit of given octal number. Octal digit 45 3 1 3 bits equivalent 100 101 011 001 (4531)8= (100101011001)2 Again, Group the obtained binary number into four bits from right to left. Group in three digits 1001 0101 1001 Octal equivalent 9 5 9 = (100101011001)2= (959)16 \u2e2b (4531)8= (959)16 Ans. Example-2 (4157)8= (?)16 Separate each octal digit of given octal number. Octal digit 41 5 7 3 bits equivalent 100 001 101 111 Approved by Curriculum Development Center (CDC) 155","Number System (4531)8= (100001101111)2 Again, Grouping the obtained binary number into four bits from right to left. Grouping in three bits 1000 0111 1111 Octal equivalent 8 7 F (100001101111)2= (87F)16 \u2e2b (4531)8= (87F)16 Ans. Hexadecimal to Octal conversion To convert the hexadecimal number to octal number, carry up the following rules: \u2022\t Separate each hexadecimal digit in the group. \u2022\t Write four bits equivalent to each Hexadecimal number and collect all the bits from left to right. \u2022\t Now, again make the group of four bits of obtained binary number from right to left. \u2022\t Write respective octal number for each binary group. \u2022\t Collect all the obtained octal digits from left to right and give base 8 for the final result. Example-1 (529)16= (?)8 Separate each digit of given hexadecimal number. Hexa decimal digit 5 2 9 1001 4 bits equivalent 0101 0010 (529)16= (010100101001)2 Again, Grouping the given binary number into three bits from right to left. Grouping in three bits 101 100 101 001 Octal equivalent 5 4 51 (010100101001)2= (5451)8 \u2e2b (529)16 = (5451)8 Ans. 156 Approved by Curriculum Development Center (CDC)","Example-2 Smart Computer Science Book-10 (5B7)16= (?)8 7 Separate each digit of given hexadecimal number. 0111 Hexadecimal digit 5 B 4 bits equivalent 0101 1011 (5B7)16= (010110110111)2 Again, Grouping the given binary number into three digits from right to left. Grouping in three bits 010 110 110 111 Octal equivalent 2 6 67 (010110110111)2= (2667)8 \u2e2b (5B7)16 = (2667)8 Ans. BINARY CODING A binary code represents data, instructions and information using a two-symbol system. The two-symbol system makes use of \\\"0\\\" and \\\"1\\\". There are different methods for binary coding. Binary coded decimal : Binary coded decimal (BCD) is a system of writing numer- als that assigns a four-digit binary code for each digit 0 through 9 in a decimal (base-10) numeral. The four-bit BCD code for any particular single base 10 digit is its representation in binary notation. EBCDIC : Extended Binary Coded Decimal Interchange Code. It is an 8-bit code (256 combinations) that stores one alphanumeric character or two decimal digits in a byte. ASCII code: ASCII (American Standard Code for Information Interchange) can represent 128 (27) characters. It uses 7 bits to represent each character since the first bit of the byte is always 0. For instance, a capital \\\"T\\\" is represented by 84, or 01010100 in binary. Extended ASCII helps to solve this problem by adding an extra 128 values, for a total of 256 (28) characters, It uses 8 bits. The additional binary values start with a 1 instead of a 0. For example, in extended ASCII, the character \\\"\u00e9\\\" is represented by 233, or 11101001 in binary. Approved by Curriculum Development Center (CDC) 157","Number System Unicode : Unicode may be 8-bit, 16-bit, or 32-bit. Numbers, mathematical notation, popular symbols and characters from all languages are assigned a code point, for example, U+0041 is an English letter \\\"A\\\". DATA MEASUREMENT UNITS IN MEMORY Memory unit is the amount of storage location where data is stored. This storage capacity commonly is expressed in terms of Bytes. The given table explains the main memory storage units: Bit (Binary Digit) : A bit is a binary digit, the smallest unit of memory. A bit can hold only one of two values: 0 or 1, corresponding to the electrical values of off or on, respectively. Nibble : A group of 4 bits is called 'nibble'. Byte : A group of 8 bits is called 'byte' which represents a single character of text in a computer, so it is a smallest addressable unit of memory in many computer architectures. Word: A computer word, like a byte, is a group of fixed number of bits processed as a unit, which varies from computer to computer but it is fixed for each computer. The length of a computer word is called word-size or word length. It may be as small as 8 bits or may be as long as 128 bits. A computer stores the information in the form of computer words. 158 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 Points to Know \u2022\t The number system is a method of counting, calculating, comparing, naming or representing quantities. \u2022\t When we type some letters or words, the computer translates them in numbers as computers can understand that is 0 and 1. \u2022\t Decimal number system consists ten unique notations: 0 to 9. So, its base value is 10. \u2022\t The binary number system consists two unique notations of 1 and 0. So, its base is 2. \u2022\t The octal number system consists eight unique notations: 0 to 7. So, its base is 8. \u2022\t Hexadecimal number system consists 16 different unique notations that is 0 to 9 and A to F. So, Its base is 16. \u2022\t A binary code represents text, computer processor instructions, or any other data using a two- symbol system. \u2022\t BCD is a system of writing numerals that assigns a four-digit binary code for each digit 0 through 9 in a decimal (base-10) numeral. \u2022\t EBCDIC is an 8-bits code (256 combinations) that stores one alphanumeric character or two decimal digits in a byte. \u2022\t ASCII is a 7-bits character set containing 128 characters. Extended ASCII code is 8 bit. Terms to Know Base\t: \t The unique number of digits or combination of digits that a system of counting uses to represent numbers. Positional notation\t : \t The method of denoting numbers by the use of a finite number of digits. Binary\t : \t Number system consisting 0 and 1. Bit\t : \t Smallest unit of data measurement. Nibble\t:\tCollection of four bits. Byte\t:\tCollection of 8 bits. Octal\t : \t A number system having a base of 8. Hexadecimal\t:\tA number system consisted of 0 to 9 and A to F having base 16. Unicode\t:\tAn international encoding standard for use with different languages and scripts. Digit\t:\t Single unit of any one of the numbers 0 through 9. Approved by Curriculum Development Center (CDC) 159","Number System Worksheet Objective Questions 1. Fill in the blanks: a. \t Two notations .....................and ................... are the binary number system. b. \t Binary coded Decimal assigns a ...................... digit binary code for each digit 0 through 9 in decimal numbers. c. \t EBCDIC is an ..................... code. d. \t ASCII code can represent ........................... characters and uses ................... bits to represent each character. e. \t Extended ASCII code uses ...................... bits. f. \t Hexadecimal consists .............................. and ......................... 2. \tWrite 'T' for true and 'F' for false statements: a. \tBit is the smallest unit of data measurement in memory. b. \tNibble is a collection of 8 bits. c.\t Byte is a collection of 4 bits. d.\t The base of the Binary number system is 2. e. \tHexadecimal number system is consisted by 16. f.\t Binary digits 0 represents 'off' and 1 represents 'on' state of electric pulses. 3. Write the full forms: a.\tBits ............................................................................................................. b. ASCII .......................................................................................................... c.\t BCD............................................................................................................ d. EBCDIC ...................................................................................................... Descriptive Questions 1.\t Write short answer of the following questions: a.\t What is number system? b.\t What is binary number system? 160 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 c.\t What is meant by base of number system? d.\t What is octal number system? e.\t What is decimal number system? f.\t What are the numbers and characters set of hexa-decimal number system? g.\t What is ASCII ? h.\t What is BCD ? i.\t What is EBCDIC? j.\t What is unicode? 2. Perform the following binary addition: a. 1 1 1 0 0 b. 1 0 1 0 1 c. 1 0 1 1 0 d. 1 1 1 0 1 +1 0 1 1 1 +1 1 1 0 1 +1 1 0 1 1 +1 1 1 1 0 3. Perform the following binary subtraction: a. 1 0 1 1 0 b. 1 1 1 1 1 c. 1 0 0 0 1 d. 1 1 0 0 0 -1011 -1101 -1111 -1 0 1 0 1 4. Perform the following binary multiplication: a. 1 0 1 0 1 b. 1 0 0 0 1 c. 1 1 1 1 1 d. 1 1 1 0 1 \u00d71 1 \u00d71 1 \u00d71 0 \u00d71 1 5. Perform the following binary division: a. 1101 \u00f7 101 b. 100110 \u00f7 111 c. 1101101 \u00f7 10 d. 110101 \u00f7 11 6. Convert the following binary numbers to decimal numbers: a.\t(1111101)2 = ( ? )10\t b. (101100)2 = ( ? )10 c.\t(1010111)2 = ( ? )10\t d. (1010011)2 = ( ? )10 e.\t(101001)2 = ( ? )10\t f. (1110011) 2 = ( ? )10 7. Convert the following decimal numbers to binary numbers: a.\t(48)10 = ( ? )2\t\t b. (304)10 = ( ? )2 c.\t(104)10 = ( ? )2\t \t d. (148)10 = ( ? )2 e.\t(315)10 = ( ? )2\t\t f. (205)10 = ( ?)2 8. Convert the following decimal numbers to octal numbers: a.\t(40)10 = ( ? )8\t\t b. (204)10 = ( ? )8 c.\t(640)10 = ( ? )8\t d. (438)10 = ( ? ) 8 e.\t(270)10 = ( ? )8\t f. (640)10 = ( ? ) 8 9. Convert the following octal numbers to decimal numbers: a.\t(640)8 = ( ? )10\tb. (470)8 = ( ? )10 Approved by Curriculum Development Center (CDC) 161","Number System c.\t(260)8 = ( ? ) 10\t d. (1220)8 = ( ? ) 10 e.\t(320)8 = ( ? ) 10\t f. (2520)8 = ( ? )10 10. Convert the following decimal numbers to hexa-decimal numbers: a.\t(80)10 = ( ? )16\t\t b. (49)10 = ( ? )16 c.\t(44)10 = (? ) 16\t\t d. (47)10 = ( ? )16 e.\t(143)10 = ( ?)16\t\t f. (185)10 = ( ? )16 11. Convert the following hexa-decimal numbers to decimal numbers: a.\t(240)16 = ( ? )10\t b. (1B8)16 = ( ? )10 b.\t(140)16 = ( ? )10\t d. (4AF)16 = ( ? )10 e.\t(2FC)16 = ( ? )10\t f. (439B)16 = ( ? )10 12. Convert the following octal numbers to binary numbers: a.\t(74)8 = ( ? )2\t\t b. (66)8 = ( ? )2 c.\t(77)8 = (?)2\t\t d. (47)8 = ( ? )2 e.\t(75)8 = ( ? )2\t\t f. (137)8 = ( ? )2 13. Convert the following binary numbers to octal numbers: a.\t(1101)2 = ( ? )8\t\t b. (1011)2 = (? )8 c.\t(1110)2 = ( ? )8\t\t d. (10101)2 = ( ? )8 e.\t(10101)2 = ( ? )8\t f. (10101)2 = ( ? )8 14. Convert the following binary numbers to hexa-decimal numbers: a.\t(10011)2 = ( ? )16\t b. (1101)2 = ( ? )16 c.\t(11010)2 = ( ? )16\t d. (1001)2 = ( ? )16 e.\t(1010)2 = ( ? )16\t f. (1010)2 = ( ? )16 15. Convert the following hexa-decimal numbers to binary numbers: a.\t(86)16 = ( ? )2\t\t b. ( A2B)16 = ( ? )2 c.\t(96)16 = ( ? )2\t\t d. (8BA)16 = ( ? )2 e.\t(F24)16 = ( ? )2\t\t f. (C32)16 = ( ? )2 16. Convert the following octal numbers to hexa-decimal numbers: a.\t(55)8 = ( ? )16\t\t b. (24)8 = ( ? )16 c.\t(234)8 = ( ? )16\t\t d. (101)8 = ( ? )16 e.\t(433)8 = ( ? )16\t\t f. (171)8 = ( ? )16 17. Convert the following hexa-decimal numbers to octal numbers: a.\t(74)16 = ( ? )8\t\t b. (248)16 = ( ? )8 c.\t(1259)16 = ( ? )8\t d. (B66)16 = ( ? )8 e.\t(5B)16 = ( ? )8\t\t f. (E39)16 = ( ? )8 162 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 D M Satabase anagement ystem CHAPTER 8Chapter Includes \u2022\t Introduction to Data and Information \u2022\t Introduction to Database \u2022\t Introduction to DBMS \u2022\t Application of DBMS \u2022\t Introduction to RDBMS and Features \u2022\t Introduction to MS-Access and Advantages \u2022\t Objects of MS-Access Database DATA AND INFORMATION Data : In simple words, data is a collection of facts (numbers, words, measurements, observations, etc,) given to the computer for further processing and get the meaningful result. Individual data does not give any significant meaning. For example, Rahul, 25, red, 45 kg, 20 cm, etc. A picture, image, file, video, pdf, etc. can also be considered data. Information : Information is organized, classified or processed data which has some meaningful values for the receiver. The processed data obtains information on which decisions and actions are based. For example, 50, 60 are marks obtained by a student. After processing these data, it obtains result and grade having meaning. Data DBMS Information INTRODUCTION OF DATABASE A database is an organized collection of data, which can be easily accessed and managed. There are two types of database: i) computerized\/ Electronic database and ii) Manual database. You can organize data into tables including rows and columns, which makes it easier to find relevant information. The database management system allows us to create and manage a database as required. The main purpose of the database is to operate a large amount of information by storing, retrieving, managing and calculating data. The database model can be categorized into four types: i) Relational database\t\t ii) Hierarchical iii) Network database\t\t iv) Object oriented database Out of these four types, relational database model is most popular. Here, our study is based on the relational database management system. Approved by Curriculum Development Center (CDC) 163","Database Management System DBMS (DATABASE MANAGEMENT SYSTEM) A database management system (DBMS) refers to the technology for creating and managing databases. It is a software package which helps to create, retrieve, update, sort, append, organize, and manage data in a database. It also supports to prepare report and print. Some of the popular DBMS software are :MySQL, PostgreSQL, Microsoft Access, MS-SQL Server, FileMaker, Oracle, dBASE, Clipper, and FoxPro. Advantages of DBMS There are several advantages of Database management system. Some of them are as follows: i) \t No redundant data ii) \t High rate of data consistency and integrity iii) \t High data security iv) \t Privacy: Limited access. v) \t Easy access to data vi) \t Easy recovery vii) \t Flexible RDBMS (Relational Database Management System) The RDBMS (Relational Database Management System) is a database management system which stores data in the multiple tables and link to each others to share, update, retrieve and organize . MS-SQL Server, MS-Access, Oracle, MySQL are examples of popular RDBMS software. Nowadays RDBMS is most widely used type of database management system. Features of RDBMS: The common features of the RDBMS are as follows, which are based on RDBMS. Reduce Data Redundancy and Inconsistency : RDBMS works in the data sharing and linking system. So, there is no chance to repeat the same data within a table or multiple tables of the database. The main difference between data redundancy and data inconsistency is that data redundancy is a condition that occurs when the same piece of data exists in multiple places in the database, whereas data inconsistency is a condition that occurs when the same data exists in different formats in multiple tables. Easy Maintenance of Large Databases : Database requires a lot of security and other features like backup and recovery. All these features are contained in RDBMS. It can maintain a database with a lot of data and information. Enhanced Security: Unauthorized persons can not access the database. Specific authorization is assigned for specific user as their need. So, security is very high. 164 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 Anyone can work on it : Users who do not have any technical skills can work on database management system. The query language provided by the DBMS is so easy to understand. If you want to update, insert, delete and search any record, then it is very easy with the help of the queries provided by the DBMS. Support Multi-User Environment : In RDBMS, multiple users can access all kind of data and information stored in single data store. There are certain limits that users can access or view particular data according to the rights assigned to them. Save Storage space and cost : All the database management systems have to save a lot of data. So, the proper integration of data saves much more space in DBMS. It is possible to manage data in which saves the cost of storing data and data entry. Data Integrity: In the RDBMS, completeness, accuracy and consistency level of data are very high because it is maintained through the use of various error-checking methods and validation procedures during the database designing phase. INTRODUCTION OF MS-ACCESS Microsoft Access is a RDBMS software developed by Microsoft company. It uses the Microsoft Jet Database Engine and comes as a part of the Microsoft Office application package. It offers the functionality of a database like to create, retrieve, update, sort, append, organize, and manage data in a database. It is also used to analyze large amounts of information. It also supports the programming capabilities to create easy to navigate screens (forms). MS-Access allows us to create and store data in more than one table as our requirement, and we can establish the relationship with other tables for data manipulation as our convenient using the key field. We can retrieve, update and manage data at a time efficiently from multiple tables. So, it is also called RDBMS software. Advantages\/Features of MS-ACCESS Some common advantages of MS Access application are as: \u2022\t MS-Access is a fully functional relational database management system. \u2022\t Easy to import data from multiple sources into MS-Access. \u2022\t You can easily customize Access database according to personal and company needs. \u2022\t Ms-Access works well with many high level languages that works on Windows OS. \u2022\t It is robust and flexible and it can perform any challenging official or industrial database tasks. \u2022\t MS-Access allows you to link the data in its existing location and use it for viewing, updating, Approved by Curriculum Development Center (CDC) 165","Database Management System querying, and reporting. \u2022\t Allows you to create tables, queries, forms, and reports. OBJECTS OF MS-ACCESS DATABASE When you create a database, Access offers you some objects like Tables, Queries, Forms, Reports, Macros, and Modules. Together, these objects allow you to enter, store, analyze, and compile your data whenever you want. All these objects are stored under a single filename having the extension name .accdb. Table Table is an object of the Ms-Access database which is an arrangement of rows and columns. MS-Access organizes the data in the form of row and columns. Row: Row is known as the complete record which is also known as the tuple. It contains information on one item or individual. Column: Column is known as field which stores a certain type of information of a record. It is also called attribute. Cell: It is a intersection of row and column, where we enter our data. Field Field Selector Cell Record Row selector Query : A query is a request for data action and result . You can use a query to an- swer a simple question, to perform calculation, combine data from different tables, or even add, change, or delete table data. Form : It is an object of Ms-Access database which allows to enter, display and update data in a customized format. Report : This object allows to display data in manageable format for printing. Macro : It automates any action performed in the Ms-Access database. Module : It allows to create an individual section for specific task containing programming statements written in VBA (Visual Basic for Applications) Programming Language. 166 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 Points to Know \u2022\t Data is a collection of facts or raw information given to the computer for further processing to get the meaning full result. Individual data do not give any significant meaning. \u2022\t Information is organized, classified or processed data, which has some meaningful values for the receiver. \u2022\t A database is an organized collection of data, so that it can be easily accessed and managed. \u2022\t A database management system (DBMS) refers to the technology for creating and managing databases. It is a software package which helps to create, retrieve, update, sort, append, organize, and manage data in a database. \u2022\t The database management system which stores data in the form of multiple table and link to each others to share, update, retrieve and organize the data is called RDBMS (Relational Database Management System). \u2022\t Reduce data redundancy, easy maintenance of large databases, enhanced security, anyone can work on it, multi-user environment support, save storage space and cost, data integrity are the features of RDBMS. \u2022\t Microsoft Access is a RDBMS software developed by Microsoft. It uses the Microsoft Jet Database Engine, and comes as a part of the Microsoft Office Package of application. \u2022\t Ms-Access offers you some objects like Tables, Queries, Forms, Reports, Macros, and Modules. \u2022\t Table is an object of the Ms-Access database which is an arrangement of rows and columns. \u2022\t In MS-Access we can retrieve, update and manage data at a time efficiently from multiple tables. So, it is called RDBMS software. \u2022\t Database objects allow you to enter, store, analyze, and compile your data whenever you want. Terms to Know Row\t: \t Horizontal arrangement of data. Column\t : \t Vertical arrangement of data. Data\t : \t Collection of raw, facts or figures of information. Information\t : \t Processed result. Data inconsistency\t :\t Same data exists in different formats in multiple tables. Data Redundancy\t :\t The same piece of data exists in multiple places in the database Data integrity\t : \t Completeness, accuracy and consistency level of data Database object\t:\tAny defined object in a database that is used to analyze, list out, update, modify, organize the information. Queries\t:\tA way of requesting information from the database. Database Index \t:\t A data structure that improves the speed of data retrieval operations. Row\/Field Selector\t :\t The grey box at the left end of each row and head of the column. Approved by Curriculum Development Center (CDC) 167","Database Management System Worksheet Objective Questions 1. Fill in the blanks: a. \t A ................................. is an organized collection of data. b. \t ..................... is collection of raw, facts or figures of a specific entity. c. \t MS-Access uses the Microsoft ....................... database engine. d. \t ...................... is known as the complete record. e. \t ....................... is known as the field of the table. f. \t MS-Access is a ....................... software offered by Microsoft. g.\t ...................... automates any action performed in the MS-Access database. h.\t The alternate name of row is ........................... i.\t The alternate name of the column is .............................. j.\t All the database objects are stored under a single file name called .............. file. 2. Write 'T' for correct statements and 'F' for incorrect one. a. \tThe processed data obtain information. b. \tRelational database model is the most popular model of DBMS. c.\t Processed result has some meaningful values. d.\t Report is a MS-Access database. e. \tMs-Access is a partial relational database management system. f.\t Table is an object of MS-Access database. g.\t Multiple users can access data and information in RDBMS. h.\t Users can access or view particular data according the rights given to them. i.\t Ms-Access supports the programming capabilities. j. \t Completeness, accuracy and consistency level of data are very high in RDBMS. 168 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 3. \tCircle for the correct option: ii) Hierarchical model iv) All of these a. \t The database models are: \t i) \t Relational database model\t\t\t \t iii) \t Network Model\t\t\t\t b. \t Example of DBMS is: \t i) dBASE\t\t\t\t\tii) Foxpro\t\t \t iii) Microsoft Access\t\t\t\t iv) All of these\t c. \t RDBMS can store data in: \t i) Single Table\t\t\t\tii) Multiple Table\t\t \t iii) Both i) & ii)\t\t\t\t iv) None of the these\t d. \t Example of RDBMS is: ii) Oracle \t i) MS-Access\t\t\t\t iv) All of the these\t \t iii) \t MS-SQL Server\t\t\t\t e. \t MS-Access allows you to create: \t i) Table\t\t\t\t\tii) Form \t iii) Query\t\t\t\t\t iv) All of the these\t f. \t MS-Access database object : \t i) Macro\t\t\t\t\tii) Queries \t iii) Forms\t\t\t\t\t iv) All of the these\t g. \t Features of RDBMS: ii) Reduce data redundancy \t i) Data integrity\t\t\t\t iv) All of the these\t \t iii) Enhanced security\t\t\t\t 4. Match the following: Cell a. \tArrangement of row and column Data b. \tQuery RDBMS c. \t Object of database Table d.\t Manageable format for printing Request for result e. \tIntersection of row and column Form f. \t Raw information Report g.\tMS-Access Approved by Curriculum Development Center (CDC) 169","Database Management System Descriptive Questions 1. Write very short answer of the following questions: a.\t What is information? b.\t What is DBMS? c. \t What is database? d.\t What is RDBMS? e.\t What is MS-Access? f.\t What is data integrity? g.\t What is data? 2.\t Write short answer of the following questions: b.\t Write the application fields of DBMS. c.\t Write the features of RDBMS. d. \t Why is MS-Access called RDBMS? e.\t Differentiate between data redundancy and data inconsistency. 170 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 C D Areating atabase with ccess CHAPTER 9Chapter Includes \u2022\t Startup Ms-Access \u2022\t Table Creation \u2022\t Field management \u2022\t Primary key setting \u2022\t Data formatting \u2022\t Data validation \u2022\t Update table design INTRODUCTION Microsoft Access is a database management system. A database is a collection of data in organized form that is stored in a computer system. Database allows users to enter, access and analyze their data quickly and easily. Start up MS-Access To start MS-Access, follow these given following steps: Steps to Follow: Step-1\t:\t Click the 'Search Box'. Step-2\t:\t Type word 'Access'. in the search box. Step-3\t:\t You get the 'Access App' as given in pic- ture. Step-4\t:\t Click the 'Access App'. You notice that start up screen CREATING NEW DATABASE To create the new database, carry up the following steps: Steps to Follow: Step-1 : \tStart up the 'Access'. Approved by Curriculum Development Center (CDC) 171","Creating Database with Access Step-2 : \tClick the 'Blank desktop database' from the startup screen. The new blank database dialog box appears as given. Step-3 :\t Type the database file name and browse the location. Step-4\t:\t Click the Create button. MS-Access creates a database with the extension '.accdb' and displays the table object in Datasheet View. The Navigation Pane displays the table object. \t 172 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 CREATING TABLE Table is an object of the database, which we already studied in the previous chapter. Here, we discuss how to create table. To create a table carry up the following steps: Steps to Follow : Step-1\t:\t Close the default table and get blank sheet, if it is existing after creating the database. Step-2: \tClick the 'Create' tab, From the 'Tables' group, click the 'Table Design' button. You get the table design window as given. Step-3\t: \tType the required 'Field Name' and set the 'Data type'. In this way, complete the table design. (We will study about data type in upcoming chapter) Step-4\t:\t Finally, click the 'File' menu and click the 'Save' option to save the table. You get the 'Save as' dialog box as given. Step-5:\t Type the name of the table and click the 'Ok' button. You may get the message for the primary key. You can click 'Yes' or 'No' as your requirement. We discuss about primary key in upcoming chapter. Approved by Curriculum Development Center (CDC) 173","Creating Database with Access FIELD NAME A field is a single piece of data about one person or one thing. Many fields together make up a complete record. A field is usually a single column within a multi-column table. A field in your database table represents a characteristic of a subject of the table in which it belongs. If the field is appropriately named, it makes easy to identify the characteristic which is represented by field. Naming a Field \u2022\t Field name can not start with blank space. Rules for naming a field name: \u2022\t Field name can be upper, lower or mixed \u2022\t A field name can be up to 64 characters long case. \u2022\t Field name can include letters, numbers, and some special characters like underscore(_) . \u2022\t A field name can not include period (.), an exclamation mark (!), brackets ([ ]) or grave (`). Setting Data Types A field's data type determines what kind of data it can store. After naming a field, you must decide what type of data that field will hold. Before you begin entering data, you should have a grasp of the data types that your system will use. The basic data types are shown in the table below; some data types (such as numbers) may have several attributes which you have to set as required. Data Type Description Maximum size Short Text Alphanumeric data (names, Address, phone, etc.) that do not Up to 255 characters require calculating. Long Text Large amounts of alphanumeric data: sentences and paragraphs. See The Memo data type is now called \u201cLong Up to 64,000 characters Text\u201d for more information on the Long Text details. Number Numeric data. (Byte, integer, long integer, single, double) 1, 2, 4,4, 8 bytes respectively Date\/Time Dates and times. 8 bytes Currency Monetary data, stored with 4 decimal places of precision. 8 bytes AutoNumber Unique value generated by Access for each new record. 4 bytes Yes\/No Boolean (true\/false) data; Access stores the numeric value 1 bit zero (0) for false, and -1 for true. It is a logical data type. OLE Object Pictures, graphs, or other active objects from another Up to about 2 GB Windows-based application like word, excel, document. Hyperlink A link address to a document or file on the Internet. up to 2048 characters Attachments You can attach files such as pictures, documents, Up to about 2 GB spreadsheets, or charts. Calculated You can create an expression for mathematical calculation Dependent on the data type of that uses data from one or more fields. Note, the calculated the Result Type property. Short data type is not available in MDB file formats. Text data up to 243 characters. Lookup Create a field to view the values from another table. Dependent on the data type of Wizard the lookup field. 174 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 Specifying Field Description The field description is specified in the description field of each column which is the explanation of specific column. It helps to remind the purpose of the field. It is an optional. If you specify the field description, it appears in the status bar of the form when you select a field. Setting Primary Key The primary key is a field of table which uniquely identifies each row\/record in the database table. Primary keys must contain unique values and cannot contain NULL value. Every table needs a primary key to identify records uniquely. This primary key can consist of single or multiple columns. It can be serial number, Emp no, Dept no, Emp ID, etc. A primary key which is made up of multiple fields is known as composite key. A column or group of columns that can be used as the primary key of the table is called candidate key. To set the primary key, carry up the following steps: Steps to Follow: Step-1\t: \tOpen the table in 'Design View' by right clicking on Table from the Navigation Pane, and click 'Design View'. Step-2\t : \tRight click on the field selector in which you want to apply primary key. Step-3\t : \tSelect the 'Primary key' option from the context menu. You notice the key icon in the selected field as given. Removing Primary Key Steps to Follow: Stpe- 1: Click 'Primary Key' in the Tools group of \\\"Design View\\\" tab. Or in the context menu. You notice that the small key in the row selector will disappear. The 'Primary Key' button works as a toggle to turn key fields ON or OFF. Approved by Curriculum Development Center (CDC) 175","Creating Database with Access TIPS & Trick \u2022\t To select the primary key for the multiple fields, hold down the Ctrl key and click the Field selector for each field. \u2022\t When you save a table without creating a primary key, Access allows you to automatically create a primary field name ID of Auto number data type. \u2022\t Access does not allow you to delete a primary key field once set. However, if you still want to delete it, first remove the primary key icon, and then delete the field. \u2022\t Primary key ensures entity integrity that there are no duplicate records within the table and that the field that identifies each record within the table is unique and never null. Importance of Primary Key The benefits of using primary keys in relational databases are many. The list below, summarizes the main benefits: \u2022\t Searching records are faster because an index is used, based on the primary key. \u2022\t Using the primary key, you can easily identify and find unique rows in the database table. \u2022\t It allows to update\/delete only specific records by uniquely identifying them. \u2022\t The table\u2019s data is sorted based on the primary key. \u2022\t It helps to avoid inserting duplicate records in a table. \u2022\t It creates referential integrity constraints to set the relationship between tables. Field Properties Pan Every table is made up of fields. Every type of field has own properties, which describe the characteristics and behavior of data added in the field. The 'Field Properties' pane appears on the lower half of the screen in the 'Design View'. It allows us to specify certain characteristics and properties, like size, format, validation rule, caption, default value, etc. of field. Two common data types (short text and number) of the fields properties pane are given below: 176 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 Field Size: Field size refers the maximum number of characters or numbers that a particular field holds. The short text field can hold up to 255 characters. The Long Text field holds up to 64000 characters. The AutoNumber data type stores an integer automatically as you add new record. You can use the AutoNumber data type as a unique record identification for table having no other unique value. Its field size is 4 of bytes. The detail field size of numeric data type is given in the following table: Size Description Byte For integers that range from 0 to 255. Storage requirement is of a single byte. It stores without decimal places. Integer For integers that range from -32,768 to +32,767. Storage requirement is of two bytes. Long Integer For integers that range from -2,147,483,648 to +2,147,483,647. Storage requirement is of four bytes. Single For numeric floating point values that range from -3.4 x 1038 to +3.4 x 1038 and up to seven significant digits. Storage requirement is of four bytes. Double For numeric floating point values that range from -1.797 x 10308 to +1.797 x 10308 and up to 15 significant digits. Storage requirement is of eight bytes. Decimal For numeric values that range from -9.999... x 1027 to +9.999... x 1027. Storage requirement is 12 bytes. Replication ID For storing a GUID (Globally Unique Identifier) that is required for replication. Storage requirement is of 16 bytes. Format Property The format property specifies how the data in the field will be displayed on the screen or printed form. Let's discuss about the built-in formats. Short Text and Long Text Filed formats: Typically, you apply custom formats to Short Text and Long Text data types to make the table data easier to read. Custom formats for Text fields can have up to two sections. Each section contains the format specification for different data in a field. Symbol Meaning Example Data Result (123)456-78 @ Represents requirement of space (@@@)@@@-@@ 12345678 (111) or a character. & Represents optional character. (&&&) &&&& 111 < Change characters into lowercase. < MUNA YADAV muna yadav > Change characters into lowercase. > Manik Lal MANIK LAL Approved by Curriculum Development Center (CDC) 177","Creating Database with Access Number and Currency Format There are six predefined number formats or any other symbols for creating own customized formats. The default format is Decimal Places' property AUTO. You can choose as your requirement. The following table illustrates the number and currency format. Format Number Result General 245.769 245.7 Currency 455.789 $455.76 Fixed 155.789 155.78 Standard 200.788 200.788 Percent 45 45% Scientific 123456.789 1.23E+05 Date\/Time Formats There are some predefined date and time formats which are illustrated in the following table. Size Description It displays the date (mm\/dd\/yy) format, if there is only a date value, it General Date will not display time vice versa. Monday, May 15 2020 Long Date Medium Date 15-May-20 Short Date Long Time 5\/15\/20 Medium Time Short Time 10:30:45 AM 10:30 AM 07:30 Logical Format You can set Yes\/No, True\/False, or On\/Off predefined data format property for the logical data type like male or female, pass or fail, etc. Decimal Place The decimal places are valid for numeric and currency data only. The decimal place numbers vary from 0 to 15 depending on the field size. Input Mask It facilities data entry and controls what the user enters in the field. Such as phone number, social security number, Zip code, Password, Date\/Time, etc. This 178 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 input mask wizard works only in Text and Date field. An input mask can serve the following purposes: \u2022\t It forces you to enter field values in a pattern like 111-111-111 or 111\/111\/111. \u2022\t It can fill in constant characters like dash (-) and slash (\/), and optionally store these characters as a part of the field value, so that the user does not have to enter explicitly. Caption The Caption property is used when you want to display an alternative name for the field. It makes the field name more explanatory. It can contain up to 2048 characters. Default value This is the value which is automatically filled in the field when you add a new record to the table. This can be any value that matches the data type of the field, which can be changed during the data entry. Required Property The 'Required' property supports 'Yes' and 'No'. If you select 'Yes', it always receives value during the data entry. The default value is 'No' which means that may leave the blank field. Allow Zero Length This property is used only in the 'Memo' and 'Text' data type field. If the 'Required' property is set 'Yes' and Allow Zero Length is set 'No' , data must be entered in the field during data entry. If both properties are set to 'Yes', The field may leave empty. Indexed An index property speeds up queries on the indexed fields as well as sorting and grouping operations. For example, if you search for specific employee names in a LastName field, you can create an index for this field to speed up the search for a specific name. The default property value is 'No'. Text Align This property is used for the alignment of the text entered in the field. Data Validation MS-Access data validation is one of the best options to prevent data-entry errors. Access data validation actually checks the entering data by applying a specified set of rules. If your entered data doesn\u2019t match the criteria applied in the field, it shows an error message and rejects your data. Data validation includes two properties: Approved by Curriculum Development Center (CDC) 179","Creating Database with Access i) Validation Rule : A validation rule is a way to restrict input in a table field or a control on a form. Validation rules verify data entered in a field with the standard you specify before saving the record. A validation rule can contain a formula or an expression that evaluates the data in one or more fields and returns a value of \u201cTrue\u201d or \u201cFalse\u201d. It checks and controls data to be entered in the field that means we must set the criteria for data to be entered in the field. ii) Validation Text : Validation text property allows us to specify the message to display, if the data entered in the field is not satisfied with the validation rule property. Some of the common examples of data validation are: Number Validation: Validation number which checks the value falls in the criteria range and displays error message then rejects the entered data. Sample Expression Description <=100 It allows to enter below or equal to 100 <100 It allows to enter below 100 >=1 It allows to enter above or equal to 100 >0 It allows to enter above equal to 100 Between 0 and 100 It allows to enter between 0 to 100 Date Validation: The date validation checks the range of date as number validation. If the range is not matched, it displays error message as mentioned in 'Validation Text' box then rejects this entered date. Sample Expression Description <#01\/01\/2020# It allows to enter a date before January 1st 2020 >#01\/01\/2015# It allows to enter a date after January 1st 2020 >#01\/01\/2015# 5:30 pm It allows to enter a date after 5:30 pm, January 1st 2020 <=#01\/01\/2020# It allows to enter a date before or on January 1st 2020 >=#01\/01\/2015# It allows to enter a date after or on January 1st 2020 > Date () It allows to enter a date after the current date <Date() It allows to enter a date before the current date >Now() It allows to enter a date after the current time of today <Now() It allows to enter a date before the current time of today Text Validation: The verification of text values is performed with 'Like' operator. It compares text to pattern such as value start with, end with or contains specific characters. Some of the expressions of validation text are: 180 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 Sample Expression Description Like \\\"P*\\\" It checks whether the value starts with P, followed with zero or more characters. (* represents zero or more character). Like \\\"*pa\\\" It checks whether the text value ends with characters 'pa'. Like \\\"??pa\\\" It accepts four characters text that ends with 'pa' (? represents one character). Like \\\"###pa\\\" It accepts five characters text that ends with 'pa' . # is similar to ? Like \\\"[pa]???\\\" It accepts five characters with first character is p or a. Like \\\"[A-Z][A-Z] [A-Z]\\\" It accepts word of three letters which can be any letters from A to Z. Boolean Operators in Validation Rule AND, OR and NOT are three boolean operators used in a boolean expression validation rule. These operators combine two conditions for validating field values. Such type of validation rule is applied in the 'Validation Text' box to display the error message, if the validation is failed. Expression Description >=1 AND <=100 It accepts a value of the field within the range 1 to 100 >50 OR <90 It accepts numbers greater than 50 or less than 90 NOT <50 It accepts numbers excluding below 50 Validation Rule Validation Text Saving Table After designing the table, you must save it. To save the table, follow these steps: Steps to Follow: Step-1 : Click File tab and click the 'Save' option. You get the following 'Save as' dialog box. Approved by Curriculum Development Center (CDC) 181","Creating Database with Access Step-2: Type the Table name and press 'Ok' button. Step-3 :You notice your table is created that ap- pears in the navigation pane. Opening an Existing Database To open an existing database, follow these steps. Steps to Follow: Step-1\t:\t Select the 'File and 'Open' option. Step-2\t:\t Select the database file from the list of recent items or browse the location and select database file. Stpe-3: \t Finally, click the 'Open' button from the open dialog box. UPDATE TABLE DESIGN Sometimes, it is necessary to modify the table design which includes: i) Insert a new field\t ii) Delete field\t\t\t iii) Rename a field iv) Move a field\t v) Change Field size\t\t vi) Change data type To update table design, follow these steps: Steps to Follow: Step-1\t: \tOpen the database by clicking on the 'File' menu. Step-2 :\t Right click the desired 'Table' from the navigation tab. Step-3 :\t Click the 'Design View'. The table is open in the design view. 182 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 To Add Field in a Table: Step-1\t:\t Right click on the row selector of field to add new field above it. Step-2\t :\tClick the 'Insert Row' option from the context menu Step-3\t :\tType the 'Field Name' and 'Data Type', then set required properties. To Delete a Field: Step-1\t :\tRight click on the row selector of the field to be deleted. Step-2\t :\tClick the 'Delete Rows' option from the context menu. To Rename a Field Step-1\t:\t Just place your cursor on the field, and type the new name. To Move a Field s Step-1\t:\tSelect the field to be moved by click- ing on field selector. Step-2\t :\tHold the mouse button, drag the field to the position where you want to place it and release the mouse button. To Change a Field Size Step-1\t :\tFor the text field, you can increase or decrease the field size in the 'Field Size' property. Step-2\t :\tIn the number data field, set the different field size of the 'General' group in the field properties pane. Logic Gate! \u2022\t When you change the field size setting larger to smaller, make sure that the value stored in the field fits in the new field size or not. \u2022\t When you change to a field size setting that does not allow the number of decimal places included in your current value, the numbers are rounded off. Approved by Curriculum Development Center (CDC) 183","Creating Database with Access Changing the Field Data Type Sometimes, it may need to change the data type of fields that contains data. The most common data type change comes in the following categories: i) Conversion Other data type to text: If you convert Number to Text data type, it will not perform the mathematical operation. ii) Conversion Text to number, date time, currency or Yes\/No : \u2022\tOnly the text data type stored numbers, period, commas and currency symbol can be converted into a number. \u2022\tText data need to be in correct form of date\/time format to convert text to date\/time data. \u2022\tText data must be in Yes\/No, On\/Off or true\/False format to convert text to Yes\/No data type format. iii) Conversion between number to currency vice versa: You can convert currency to number, if your field can adjust the size and decimal places. iv) Conversion long text to short text vice versa: Conversion Short Text to long Text can be easily performed because size is in favour. But the conversion Long Text to Short Text may favour the size that may lose the values having more than 255 characters. Change data types in Datasheet view Step-1 : \tIn the Navigation Pane, locate and double-click the table that you want to change. Step-2 :\t Access opens the table in Datasheet View. Step-3 :\t Select the field (the column) that you want to change. Step-4\t:\t In the Properties group of Fields tab, click the arrow in the drop-down list next to Data Type, and then select a data type. Step-5\t:\t Save your changes. Change data types in Design View Step-1\t:\t If you have the open table in Datasheet View, right-click the document tab for the table and click Design View. \t\t-or- Step-2 :\t If you do not have the open table, in the Navigation Pane, right-click the table that you want to change, and then click Design View on the shortcut menu. Step-3 :\t Locate the field that you want to change, and select a new data type from the list in the Data Type column. Step-4 :\t Save your changes. 184 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 Points to Know \u2022\t Microsoft Access is a database management system. \u2022\t Database allows users to enter, access and analyze their data quickly and easily. \u2022\t A field is a single piece of data about one person or one thing. Many fields make up a record. \u2022\t A field's data type determines what kind of data it can store. \u2022\t Field description is an explanation regarding the specific column. \u2022\t The PRIMARY KEY constraint uniquely identifies each record in a table. \u2022\t Primary keys must contain unique values and cannot contain NULL values. \u2022\t Every type of field has own properties, which describe the characteristics and behavior of data added in the field. \u2022\t Primary key ensures entity integrity that there are no duplicate records within the table and not null. \u2022\t Field size refers the maximum number of characters or numbers that a particular field holds. \u2022\t The caption property is used when you want to display an alternative name for the field. \u2022\t MS Access data validation is one of the best options to prevent data-entry errors. \u2022\t Validation rules verify data entered in a field with the standard you specify before saving the record. \u2022\t Validation text property allows us to specify the message to display, if the data entered in the field is not satisfied with the validation rule property. Terms to Know Database\t: \t An organized collection of information or data. Record\t : \t Information of any entity stored in row. Datasheet view\t : \t The view in which you can enter and modify the data in a table. Field name\t : \t The name assigned to a field that appears as a column heading in the first row of the table. Design View\t :\t The view in which you can modify the structure of a table. Primary Key\t :\t A field or a combination of fields that uniquely identifies a record. Field properties\t : \t Characteristics and behavior of data added to that field. Data Validation\t:\tTests incoming data to make sure it conforms to a specified set of rules. Navigation Pane\t :\t Left side of the Access interface acts as the table of contents of the database. Approved by Curriculum Development Center (CDC) 185","Creating Database with Access Questions types marked by * are just for testing Worksheet higher ability but can Objective Questions enhance the knowledge 1. Fill in the blanks: for SEE. a. \t A field name can be up to ......................... characters. b. \t The ........................... constraint uniquely identifies each record in a table. c. \t Autonumber field size is of ...................... bytes. d. \t The ......................... property specifies how the data in the field will be dis- played on the screen or printed form. e. \t There are .......................... predefined number formats. f. \t The default format of decimal places property in the number field is ............................. g.\t The caption can contain up to ......................... characters. h.\t The ............................... value which automatically filled in the field when you added a new record. i.\t ................................... property always receives value during the data entry. j.\t .................................... property is used for the alignment of the text entered in the field. k.\t The primary key button works as a ........................... to turn key field 'ON or Off'. l.\t The decimal place numbers vary from ........................... 2. \tMake 'Tick' for correct statements and leave blank for incorrect one. a. \tTable is an object of the database. b. \tField is a single piece of data about a thing. c.\t Field name can include numbers. d.\t Field name can be only in uppercase. e. \tField name accepts the period. f.\t If field description is mentioned, it appears in the status bar of the form. g.\t The primary key contains null values. 186 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 h.\t The primary key must contain unique values. i.\t Ms-Access allows to delete a primary key field once set. j. \t The default value is 'Yes' of Required properties. k.\t Indexed property speeds up searches and sorts performed on the field. l.\t The default value of indexed property is 'No'. 3. \tShade the circle for the correct option: a. \t Database allows to: ii) Access data\t \t i) \t Enter data\t\t\t\t iv) All of these \t iii) \t Analyze data\t\t\t\t b. \t A field name can include: ii) ` Grave\t \t i) \t ! exclamation mark\t\t\t iv) All of these \t iii) \t Under score _\t\t\t\t c. \t Maximum size of short text data type: ii) 255 characters\t \t i) \t 64 characters \t\t\t\t iv) 64000 characters \t iii) \t 50 Characters\t\t\t\t d. \t Maximum size of number data: ii) 2 bytes \t i) \t 1 byte\t\t\t\t\t iv) 8 bytes \t iii) \t 4 bytes \t\t\t\t\t e. \t Size of logical data types: ii) 2 bytes\t \t i) \t 1 byte\t\t\t\t\t iv) 8 bytes \t iii) \t 4 bytes\t\t\t\t\t f. \t Allow Zero Lengths properties is used in: ii) Text Field\t \t i) \t Memo Field\t\t\t\t iv) None of these \t iii) \t Both i) & ii)\t\t\t\t g. \t Data validation includes properties: ii) Validation Text\t \t i) \t Validation Rule\t\t\t\t iv) None of these \t iii) \t Both i) & ii)\t\t\t\t h. \t Boolean operators are: \ti) \tAND\t\t\t\t\tii) OR\t \tiii) \tNOT\t\t\t\t\tiv) All of these i. \t The size of Date\/ Time data type is ii) 8 bytes\t \t i) \t 4 bytes\t\t\t\t\t iv) 64 bytes \t iii) \t 16 bytes\t\t\t\t\t j. \t A data type that can have only two values, True or False. \ti) \tNumber\t\t\t\t\tii) AutoNumber\t \tiii) \tYes\/No\t\t\t\t\tiv) Currency Approved by Curriculum Development Center (CDC) 187","Creating Database with Access 4. Match the following: a. \tTo prevent data-entry errors Caption b. \tSpeed up searches and sorts Validation Text c. \t Always receives value during the data entry Field name d.\t Value which automatically filled in the field Data validation e. \tAn alternative name for the field Indexed f. \t A message helps users which is not valid data Required Property 'Yes' g.\t A single piece of data about one thing Default value Descriptive Questions 1.\t Write very short answer of the following questions: a.\t What is field name? b.\t What is data type? c.\t What do you mean by field description? d.\t What is primary key ? e. \t What is candidate key? f.\t What is composite key? g.\t What is field properties pane? h.\t What is default value? i.\t What is caption? j.\t What is data validation? k.\t What is Validation Rule? l.\t What is Validation Text? m.\t What is updating the table design? n.\t What is logical data type? o.\t What is input mask? 2.\t Write short answer of the following questions: a.\t Why should you appropriately mention the field name? b. \t Why do you use OLE object data type? c.\t Why do you use calculated data type? d.\t When do you use 'Autonumber' data type? e.\t Why do you use primary key? f.\t Why do you use Required property? g.\t Differentiate between long text and short text data type. 188 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 Case Study and Application-based Questions* 1. \tOn the basis of given table, answer the asked questions: a. \t How many tuples and attributes are there in this table? Ans: ............................................................................................................................. b.\t What are the candidate keys in this table? Ans: ............................................................................................................................. c. \t Which attribute is suitable for the primary key in this table? Give your logic? Ans: ............................................................................................................................. d.\t Write the suitable data type for each field. Ans: ............................................................................................................................. e.\t Which property would you set so that the field 'Nationality' receives the value 'Nepali' automatically. Ans: ............................................................................................................................. f.\t Which type of date property format is used in this table design? Ans: ............................................................................................................................. 2. \tLakpa Sherpa is the transportation incharge of the Amarnath School. He has de- cided to prepare the database to store the relevant data about students who are using the school bus. a. Suggest fields name which he must mention in this table. Ans: ............................................................................................................................. b. Suggest suitable data types for the fields. Ans: ............................................................................................................................. c. Which fields can be set as the primary key? Ans: ............................................................................................................................. d. Suggest suitable properties that can be set for each data type. Ans: ............................................................................................................................. 3. \t Dhanwantari Footsal of Katmandu wants to maintain the details of all the members in the MS-Access database table named members_details. Identify and suggest which property of following fields should be set in the field properties pane. Approved by Curriculum Development Center (CDC) 189","Creating Database with Access Field Name Description MemebrID Should be of 4 characters. Name Should be maximum of 50 characters. Gender Male or Female Address Should be maximum of 50 characters Contact It should not be left blank and it should display as (- - -) - - -_- - - - Joining Date Like 25-Oct-19 Sports_Joined Swimming, Football, Cricket, Basket Ball, Kabadi Charge The range 1000 to 5000, otherwise message should be 'Charge out of range.' Category A for Students B for General Public. Ans: ............................................................................................................................. ..................................................................................................................................... ..................................................................................................................................... ..................................................................................................................................... 4. Find the odd field in the following table: i) Table: Students_detail Student_ID S_name Gender Salary Class Contact Ans: ........................................................................................................................... ii) Table: Employee Emp_ID E_name Gender Class Post Salary iii) Write the appropriate caption for the first and second field of both tables. Ans: ........................................................................................................................... 5. Study the following table design and answer the asked questions: Field Name Data Type Description S. No 1, 2, 3, 4, 5.... E_name Rohit, Sovit, Kapil, Samip, Sampda, Kalpana Gender Male or Female Post Manager, Clerk, Assistant, Officer, Peon Contact (977) 443-38703 Joining Date 20-jan-2020 Salary 10000, 25000, 45000, 50000 190 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 a. Identify the suitable data type of the fields. Ans: ........................................................................................................................... b. Which field would you suggest for the primary key? Ans: ........................................................................................................................... 6. Identify the appropriate fields, primary key field, data type, and relevant properties for the following cases: a. \t The table name called 'computer_details' that stores the detail information of computers which comes for servicing in the service center of the company. Ans: ............................................................................................................................. ..................................................................................................................................... ..................................................................................................................................... ..................................................................................................................................... ..................................................................................................................................... ..................................................................................................................................... ..................................................................................................................................... b. \t The table name called 'Patient_details' that stores the detail information of patients who comes to the hospital for regular checkup. Ans: ............................................................................................................................. ..................................................................................................................................... ..................................................................................................................................... ..................................................................................................................................... ..................................................................................................................................... ..................................................................................................................................... ..................................................................................................................................... 7.\t Mohammad Molla has designed a table and enters data. During the data entry, he got some problems as given. a. \t Mohammad Molla got the field name in short form which is very difficult. He wants to display the field name in the explanatory form so that it could understand easily. To solve this problem what modification is required in the table. Ans: ........................................................................................................................... Approved by Curriculum Development Center (CDC) 191","Creating Database with Access b. \t Mohammad Molla got the blank fields but he wants to appear automatic filled value. To solve this problem what should he do? Ans: ........................................................................................................................... c. \t Mohammad Molla can leave the blank in the column but he wants to make such provision that must re- ceive the data. To solve this case what should he do? Ans: ........................................................................................................................... 8. \tWhen the Mohammad Molla typed the text for remarks too long, the system could not allow to type the complete text. a. Why did this problem occur? Ans: ............................................................................................................................. ..................................................................................................................................... b. How should he solve this problem? Ans: ............................................................................................................................. ..................................................................................................................................... Lab Activity-1 1. \t Create a database CLASS10_ACCESS and design a table named 'students_info' with the following structure. Open the table in datasheet view, and add at least 10 records. Field Name Data Type Field Properties Sn Auto number S_name Short Text Date of Birth Date Medium Date Gender Yes\/No True or False Class Number 1 to 10, otherwise display message 'Data out of range.' Section Short Text Lookup wizard : 'A', 'B', 'C', 'D' Contact_Phone Short Text Input Mask (---) ---, ---- Parents_Name Short Text 192 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 Lab Activities-2 1. \tDesign a table named 'Product' with the following fields name and data type then open the table in datasheet view, and add at least 10 records. Field Name Data Type Field Properties Product_Code Auto number Product_Name Short Text Convert all the characters to upper case Quantity Number Rate Number Total_Amount Calculated [Quantity]*[Rate] 2. \tDesign a table named 'Product_Stock' with the following fields name and data type then open the table in datasheet view; and add at least 10 records. Field Name Data Type Field Properties Product_Code Auto number Convert all the characters to upper case Product_Name Short Text Quantity_Purchase Number [Quantity_Purchase]-[Quantity_Sales] Quantity_Sales Number Stock_Quantity Calculated 3. \tDesign a table named 'Marks' with the following fields name and data type then open the table in datasheet view, and add at least 10 records. Field Name Data Type Field Properties Student_ID Auto number Student_Name Short Text Required no blank Title Short Text Required no blank and Allow zero length Maths Number >0 and <=100 and default value is null Computer Number >0 and <=100 and default value is null English Number >0 and <=100 and default value is null Science Number >0 and <=100 and default value is null Nepali Number >0 and <=100 and default value is null Total_Marks Calculated [Maths]+[Computer]+English]+[Science]+[Nepali] Percentage_Marks Calculated [Total_Marks]\/5 Approved by Curriculum Development Center (CDC) 193","Creating Database with Access Lab Activities-3 1. \tDesign a table named 'Emp_salary' with the following fields and data type then open the table in datasheet view, and add at least 10 records. Field Name Data Type Field Properties Emp_ID Auto number Emp_Name Short Text Should be in capital letters and required no blank Gender Yes\/No Post Lookup Wizard Manager, Officer, Assistant, Accountant, Peon, Clerk Basic_Salary Currency Data validation >=10000 and <60000 and Decimal place 2 2. \tDesign a table named 'Guest_Details' with the following fields and data type then open the table in datasheet view, and add at least 10 records. Field Name Data Type Field Properties Guest_ID Auto number Guest_Name Short Text Should be in capital letters and required no blank Gender Yes\/No No_Persons Number Default value is null and required no blank In_Date Date Format medium date and required no blank Out_Date Date Format medium date and required no blank No_of_Rooms Number Default value is null and required no blank Room_Type Lookup Wizard Normal, Deluxe, Super Deluxe, Star Bed_Type Lookup Wizard Single, Double Rate Number Default value is null and required no blank Stayed_Days Calculated [Out_Date]-[In_Date] Tolal_Amount Calculated [Stayed_Days]*[Rate]*No_of_Rooms] 3. \tDesign a table named 'Product_Stock' with the following fields name and data type then open the table in datasheet view, and add at least 10 records. Field Name Data Type Field Properties Product_Code Auto number Define the primary key on this field Product_Name Short Text Convert all the characters to upper case Quantity_Purchase Number Quantity_Sales Number [Quantity_Purchase]-[Quantity_Sales] Stock_Quantity Calculated 194 Approved by Curriculum Development Center (CDC)","Entering & Editing Data Smart Computer Science Book-10 Chapter Includes CHAPTER \u2022\t Viewing table \u2022\t Entering\/adding records in Datasheet View 10 \u2022\t Editing records \u2022\t Row and column management \u2022\t Sorting records \u2022\t Deleting records VIEWING TABLE Viewing table refers to display information stored in table. Ms-Access supports two types of table viewing techniques. \t i) Design View\t ii) Data sheet View Design View Design view takes you behind the scenes to work with the table structure. In design view you have much finer control over how the fields in your data are defined and validated. Basically, it is used to structure and restructure the database table. It is concerned for adding, editing, moving or deleting fields and its properties in the table. Datasheet View Datasheet view refers to rowwise and columnwise viewing of data in the tabular form. The information related to individual records is provided in individual rows and the attributes related to that record are given in the corresponding columns. All the users who work with database applications will not have permission to change the format of the database; instead of this, users are given permissions to edit, update, and add records to the database. So, the Datasheet view always concerns with adding, modifying, updating, searching or deleting records. Switching to Datasheet View To view the table in datasheet view, follow these steps: Steps to Follow: Step-1: Make sure clicked on the 'Design' tab. Step-2\t: From the 'Tools' group, click on the View drop down button. Step-3\t: Click on 'Datasheet View' option. Approved by Curriculum Development Center (CDC) 195","Entering & Editing Data ADDING RECORDS After completion of designing of table in design view, you must enter data in the table in the datasheet view. To add records in the table, follow these steps: Steps to Follow: Step-1\t: Open your table in 'Datasheet view'. Step-2 : \tPlace the cursor in the first field and type the data, then move the cursor in the second field by pressing the tab or arrow key, or just click in a new field, type the data and complete the adding records process. Step-3 : When you start to enter data in the field, new row automatically appears. Just click the first field of new row and enter all the elements in respective field. Step-4 : \tAfter entering the records, close the datasheet window by simply clicking the close button. EDITING RECORDS Sometimes, you may need to edit some existing information from the records of a database table. To edit records, follow these steps: Steps to Follow: Step-1 : \tOpen the table in the 'Datasheet View'. Stpe-2 : \tPlace the mouse cursor in the cell of the record to be edited. Step-3 : \tType new value or correct the existing value. HIDE FIELDS OR COLUMN If you want to hide or unhide the column or field, follow these steps: Steps to Follow: Step-1 :\tClick on the column header. If you want to select more than one column, press Shift key. Step-2\t:\tRight click on the selected column header. 196 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 Step-3 :\t Click the 'Hide Fields' option. UNHIDE FIELD\/COLUMN The hidden fields may need to unhide. To unhide the hidden field, do the following: Steps to Follow: Step-1 :\t Right click on the any heading. Step-3 :\t Click the 'Unhide Fields' option from the context menu. You get unhide column dialog box. Step-4 :\t Click the check box on the field to be unhide. Step-5\t:\t Click the close button. DELETE RECORDS If unnecessary records exist in the database table, we should delete them. To delete the unnecessary records, follow these steps: Steps to Follow: Step-1 :\t If the table is not open, open the table in 'Datasheet View'. Step-2 :\tRight-click on the record header to delete record. Step-3 :\tClick on the 'Delete Record' option from the context menu. You get the conformation message. Step-4 :\t Click on 'Yes'. RESIZE THE COLUMN WIDTH Sometimes data may not fit and not be visible within a column and in some cases, column width may unnecessarily longer. In such a case, we should resize the column width. To resize the column width, follow these steps: Steps to Follow: Step-1 : \tPlace the mouse pointer on the field line, and click and drag right to increase or drag left to decrease size. Or just double click on the field line. Approved by Curriculum Development Center (CDC) 197","Entering & Editing Data You can resize the column width by using the column width dialog box. To do this follow these steps: Steps to Follow: Step-1 : \tRight click on the field header. Step-2\t: \tClick on the 'Field Width' option. You get the column width dialog box. Step-3 : \tType the value in the column width box. Or You can click the standard width check box. Step-4 : \tClick the 'Ok' button. RESIZING ROW HEIGHT Similar to resize the column width, you can resize the row height. To resize row height, follow these steps: Steps to Follow: Step-1 : \tPlace the mouse pointer on the row header, and click and drag up or down from the bottom line of row. You can resize the row height by using the row height dialog box. To do this, carry up the following instructions: Step-1 : \tRight click on the row header. Step-2\t: \t Click on the 'Row Height' option. You get row height dialog box. Step-3 : \tType the value in the Row Height box. Or You can click the standard width check box. Step-4: \t Finally, click the 'Ok' button. FREEZE\/UNFREEZE COLUMNS Freeze refers to make the column fix which always remains visible when you scroll the column left or right. Unfreeze refers to release the freeze feature. To freeze or unfreeze, follow these steps: 198 Approved by Curriculum Development Center (CDC)","Smart Computer Science Book-10 Steps to Freeze: Step-1 :\tOpen the table in 'Data- sheet view'. Step-2 : \tRight click on the column heading. Step-3 : \tClick the 'Freeze Fields' option from the context menu. Steps to Unfreeze: Step-1 : \tRight click on the column heading being unfreeze. Step-2 : \tClick the 'Unfreeze All Fields' option from the context menu. SORTING RECORDS Arrange the records in alphabetical order in ascending or descending order on the basis of specific column is called sorting. After sorting records, you can view the information in alphabetical, numerical, or date order. You can sort in ascending order (alphabetical from A to Z, lowest number to highest number, previous date to latest date) or descending order (alphabetical from Z to A, highest number to lowest number, latest date to previous date). To sort data, follow these steps: Steps to Follow: Step-1 : \tMake sure the table is open. Step-2 : \tRight click on the column header on which you want to sort. Step-3 : Click on 'Sort A to Z option to sort ascending order or 'Sort Z to A' to sort descending order. If you want to undo the sorting features, do the following: Step to Follow: Step-1 : On the 'Home' tab, in the 'Sort & Filter' group, click the 'Remove Sort' button. Approved by Curriculum Development Center (CDC) 199","Entering & Editing Data Points to Know \u2022\t Ms-Access supports two types of table view: i) Design View\tii) Datasheet View \u2022\t Design view takes you behind the scenes to work with the table structure. Basically, it is used to structure and restructure the database table. \u2022\t Datasheet view refers to rowwise and columnwise viewing of data in a tabular form. \u2022\t Data sheet view always concerns with adding, modifying, updating, searching or deleting records. \u2022\t Information of a column can be viewed in alphabetical, numerical, or date order. \u2022\t Freeze refers to make fix which remains visible when you scroll the column left or right. \u2022\t Arrangement of data records in ascending or descending order is known as sorting. Terms to Know Datasheet View\t : \t The view in which you can enter and modify data in the table. Design View\t : \t The view in which you can modify the structure of the table. Hide Field\t : \t Making a column disappear. Editing Record\t : \t Modifying the record. Deleting Record\t :\t Removing unnecessary record. Resize height\/width\t : \t Increase or decrease height or width of the row or column of the table. Freeze column\t:\tMake the column fixed which always remains visible when you scroll the columns. Sorting\t:\tArrangement of data in ascending or descending order. Worksheet Questions types marked by * are just for testing Objective Questions 1. Match the following: higher ability but can enhance the knowledge a. \tRow and columnwise view b. \tStructure\/Restructure of the table for SEE. c. \t Modification of Record d.\t Increase\/decrease height\/width of row or column Freeze column e. \tMaking a column fixed to view during scrolling Sorting Delete Record Datasheet view Design view 200 Approved by Curriculum Development Center (CDC)"]
Search
Read the Text Version
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
- 349
- 350
- 351
- 352
- 353
