Fundamentals-of-Computer-and-IT-UGCA-1902-1.pdf

BCA 1 –IT www.sbs.ac.in CLASS: BCA 1st Sem Batch: 2020-2023 Fundamentals Of Computer & IT Notes as per IKGPTU Syllabus...

BCA 1 –IT www.sbs.ac.in CLASS: BCA 1st Sem Batch: 2020-2023 Fundamentals Of Computer & IT Notes as per IKGPTU Syllabus Name of Faculty: Ms. Srishty Soni Faculty of Computer Applications, SBS College. Ludhiana 1|Page BCA 1 –IT www.sbs.ac.in Detailed Page no Contents Unit-I Human Computer Interface Concepts of Hardware and Software; Data and Information. Functional Units of Computer System: CPU, registers, system bus, main memory unit, cache memory, Inside a computer, SMPS, Motherboard, Ports and Interfaces, expansion cards, ribbon cables, memory chips, processors. Devices: Input and output devices (with connections and practical demo), keyboard, mouse, joystick, scanner, OCR, OMR, bar code reader, web camera, monitor, printer, plotter. Memory: Primary, secondary, auxiliary memory, RAM, ROM, cache memory, hard disks, optical disks. Data Representation: Bit, Byte, Binary, Decimal, Hexadecimal, and Octal Systems, Conversions and Binary Arithmetic (Addition/ Subtraction/ Multiplication) Applications of IT. Unit-II Concept of Computing, Types of Languages: Machine, assembly and High level Language; Operating system as user 10 interface, utility programs. Word processing: Editing features, formatting features, saving, printing, 2|Page BCA 1 –IT www.sbs.ac.in table handling, page settings, spell-checking, macros, mail-merge, equation editors. Unit-III 10 Spreadsheet: Workbook, worksheets, data types, operators, cell formats, freeze panes, editing features, formatting features, creating formulas, using 3|Page BCA 1 IT www.sbs.ac.in formulas, cell references, replication, sorting, filtering, functions, Charts & Graphs. Presentation Graphics Software: Templates, views, formatting slide, slides with graphs, animation, using special features, presenting slide shows. Unit-IV Electronic Payment System: Secure Electronic Transaction, Types of Payment System: Digital Cash, Electronic Cheque, Smart Card, Credit/Debit Card E-Money, Bit Coins and Crypto currency, Electronic Fund Transfer (EFT), Unified 12 Payment Interface (UPI), Immediate Payment System (IMPS), Digital Signature and Certification Authority. Introduction to Bluetooth, Cloud Computing, Big Data, Data Mining, Mobile Computing and Embedded Systems and Internet of Things (IoT) 4|Page BCA 1 IT www.sbs.ac.in Information technology (IT) is the use of any computer, storage, networking and other physical devices, infrastructure and processes to create, process, store, secure and exchange all forms audio video text image of electronic data. 5|Page BCA 1 IT www.sbs.ac.in 6|Page BCA 1 IT www.sbs.ac.in 7|Page BCA 1 IT www.sbs.ac.in Hardware Hardware refers to the physical elements of a computer. This is also sometime called the machinery or the equipment of the computer. Examples of hardware in a computer are the keyboard, the monitor, the mouse and the central processing unit. However, most of a computer's hardware cannot be seen; in other words, it is not an external element of the computer, but rather an internal one, surrounded by the computer's casing (tower). A computer's hardware is comprised of many different parts, but perhaps the most important of these is the motherboard. The motherboard is made up of even more parts that power and control the computer. In contrast to software, hardware is a physical entity. Hardware and software are interconnected, without software, the hardware of a computer would have no function. However, without the creation of hardware to perform tasks directed by software via the central processing unit, software would be useless. Hardware Components Computer hardware is a collection of several components working together. Some parts are essential and others are added advantages. Computer hardware is made up of CPU and peripherals as shown in image below. 8|Page BCA 1 IT www.sbs.ac.in Software Software, commonly known as programs or apps, consists of all the instructions that tell the hardware how to perform a task. These instructions come from a software developer in the form that will be accepted by the platform (operating system + CPU) that they are based on. For example, a program that is designed for the Windows operating system will only work for that specific operating system. A set of instructions that drives computer to do stipulated tasks is called a program. Software instructions are programmed in a computer language, translated into machine language, and executed by computer. Software is capable of performing many tasks, as opposed to hardware which can only perform mechanical tasks that they are designed for. Software provides the means for accomplishing many different tasks with the same basic hardware Software can be categorized into two types −  System software  Application software System Software System software operates directly on hardware devices of computer. It provides a platform to run an application. It provides and supports user functionality. Examples of system software include operating systems such as Windows, Linux, Unix, etc. 9|Page BCA 1 IT www.sbs.ac.in Application Software An application software is designed for benefit of users to perform one or more tasks. Examples of application software include Microsoft Word, Excel, PowerPoint, Oracle, etc. 10 | P a g e BCA 1 IT www.sbs.ac.in Differences between Software and Hardware are sorted out below − Sr.N Software Hardware o. 1 It is a collection of programs to It includes physical components of bring computer hardware computer system. system into operation. 2 It includes numbers, It consists of electronic components alphabets, alphanumeric like ICs, diodes, registers, crystals, symbols, identifiers, keywords, boards, insulators, etc. etc. 3 Software products evolve by Hardware design is based on adding new features to architectural decisions to make it work existing programs to support over a range of environmental hardware. conditions and time. 4 It will vary as per computer It is mostly constructed for all types of and its built-in functions and computer systems. programming language. 5 It is designed and developed The hardware can understand only by experienced programmers low-level language or machine in high-level language. language. 6 It is represented in any high- The hardware works only on binary level language such as BASIC, codes 1’s and COBOL, C, C++, JAVA, etc. 11 | P a g e BCA 1 IT www.sbs.ac.in 0’s. 7 The software is categorized as The hardware consists of input operating system, utilities, devices, output devices, memory, etc. language processor, application software, etc. Functional Components of a Computer Computer: A computer is a combination of hardware and software resources which integrate together and provides various functionalities to the user. Hardware are the physical components of a computer like the processor, memory devices, monitor, keyboard etc. while software is the set of programs or instructions that are required by the hardware resources to function properly. There are a few basic components that aids the working-cycle of a computer i.e. the Input- Process- Output Cycle and these are called as the functional components of a computer. It needs certain input, processes that input and produces the desired output. The input unit takes the input, the central processing unit does the processing of data and the output unit produces the output. The memory unit holds the data and instructions during the processing. Digital Computer: A digital computer can be defined as a programmable machine which reads the binary data passed as instructions, processes this binary data, and displays a calculated digital output. Therefore, Digital computers are those that work on the digital data. Details of Functional Components of a Digital Computer 12 | P a g e BCA 1 IT www.sbs.ac.in  Input Unit : The input unit consists of input devices that are attached to the computer. These devices take input and convert it into binary language that the computer understands. Some of the common input devices are keyboard, mouse, joystick, scanner etc.  Central Processing Unit (CPU) : Once the information is entered into the computer by the input device, the processor processes it. The CPU is called the brain of the computer because it is the control center of the computer. It first fetches instructions from memory and then interprets them so as to know what is to be done. If required, data is fetched from memory or input device. Thereafter CPU executes or performs the required computation and then either stores the output or displays on the output device. The CPU has three main components which are responsible for different functions – Arithmetic Logic Unit (ALU), Control Unit (CU) and Memory registers  Arithmetic and Logic Unit (ALU) : The ALU, as its name suggests performs mathematical calculations and takes logical decisions. Arithmetic calculations include addition, subtraction, multiplication and division. Logical decisions involve comparison of two data items to see which one is larger or smaller or equal.  Control Unit : The Control unit coordinates and controls the data flow in and out of CPU and also controls all the operations of ALU, memory registers and also input/output units. It is also responsible for carrying out all the instructions stored in the program. It decodes the fetched instruction, interprets it and sends control signals to input/output devices until the required operation is done properly by ALU and memory.  Memory Registers : A register is a temporary unit of memory in the CPU. These are used to store the data which is directly used by the processor. Registers can be of different sizes(16 bit, 32 bit, 64 bit and so on) and each register inside the CPU has a specific function like storing data, storing an instruction, storing address of a location in memory etc. The user registers can be used by an assembly language programmer for storing 13 | P a g e BCA 1 IT www.sbs.ac.in operands, intermediate results etc. Accumulator (ACC) is the main register in the ALU and contains one of the operands of an operation to be performed in the ALU.  Memory : Memory attached to the CPU is used for storage of data and instructions and is called internal memory The internal memory is divided into many storage locations, each of which can store data or instructions. Each memory location is of the same size and has an address. With the help of the address, the computer can read any memory location easily without having to search the entire memory. when a program is executed, it’s data is copied to the internal memory ans is stored in the memory till the end of the execution. The internal memory is also called the Primary memory or Main memory. This memory is also called as RAM, i.e. Random Access Memory. The time of access of data is independent of its location in memory, therefore this memory is also called Random Access memory (RAM). Read this for different types of RAMs  Output Unit : The output unit consists of output devices that are attached with the computer. It converts the binary data coming from CPU to human understandable form. The common output devices are monitor, printer, plotter etc. Interconnection between Functional Components A computer consists of input unit that takes input, a CPU that processes the input and an output unit that produces output. All these devices communicate with each other through a common bus. A bus is a transmission path, made of a set of conducting wires over which data or information in the form of electric signals, is passed from one component to another in a computer. The bus can be of three types – Address bus, Data bus and Control Bus. Following figure shows the connection of various functional components: 14 | P a g e BCA 1 IT www.sbs.ac.in The address bus carries the address location of the data or instruction. The data bus carries data from one component to another and the control bus carries the control signals. The system bus is the common communication path that carries signals to/from CPU, main memory and input/output devices. The input/output devices communicate with the system bus through the controller circuit which helps in managing various input/output devices attached to the computer. SMPS A switched-mode power supply (SMPS) is an electronic circuit that converts power using switching devices that are turned on and off at high frequencies, and storage components such as inductors or capacitors to supply power when the switching device is in its non- conduction state. Switching power supplies have high efficiency and are widely used in a variety of electronic equipment, including computers and other sensitive equipment requiring stable and efficient power supply. A switched-mode power supply is also known as a switch-mode power supply or switching- mode power supply. 15 | P a g e BCA 1 IT www.sbs.ac.in Advantages of switched-mode power supplies:  Higher efficiency of 68% to 90%  Regulated and reliable outputs regardless of variations in input supply voltage  Small size and lighter  Flexible technology  High power density Disadvantages:  Generates electromagnetic interference  Complex circuit design  Expensive compared to linear supplies INPUT OUTPUT DEVICES The computer will be of no use unless it is able to communicate with the outside world. Input/Output devices are required for users to communicate with the computer. In simple terms, input devices bring information INTO the computer and output devices bring information OUT of a computer system. These input/output devices are also known as peripherals since they surround the CPU and memory of a computer system. 16 | P a g e BCA 1 IT www.sbs.ac.in Some commonly used Input/Output devices are listed in table below. Input Output Devices Devices Keyboard Monitor Mouse LCD Joystick Printer Scanner Plotter Light Pen Touch Screen I. Input Devices (a) Keyboard It is a text base input device that allows the user to input alphabets, numbers and other characters. It consists of a set of keys mounted on a board Alphanumeric keys It consists of keys for English alphabets, 0 to 9 numbers, and special characters like + / * ( ) etc. 17 | P a g e BCA 1 IT www.sbs.ac.in Function Keys There are twelve function keys labeled F1, F2, F3… F12. The functions assigned to these keys differ from one software package to another. These keys are also user programmable keys. Special-function Keys These keys have special functions assigned to them and can be used only for those specific purposes. Functions of some of the important keys are defined below. Enter It is similar to the ‘return’ key of the typewriter and is used to execute a command or program. Spacebar It is used to enter a space at the current cursor location. Backspace This key is used to move the cursor one position to the left and also delete the character in that position. Delete It is used to delete the character at the cursor position. Insert Insert key is used to toggle between insert and overwrite mode during data entry. Shift This key is used to type capital letters when pressed along with an alphabet key. Also used to type the special characters located on the upper-side of a key that has two characters defined on the same key. Caps Lock Cap Lock is used to toggle between the capital lock features. When ‘on’, it locks the alphanumeric keypad for capital letters input only. 18 | P a g e BCA 1 IT www.sbs.ac.in Control keys Control key is used in conjunction with other keys to provide additional functionality on the keyboard. Alt Also like the control key, Alt key is always used in combination with other keys to perform specific tasks. Esc This key is usually used to negate a command. Also used to cancel or abort executing programs. Numeric Keypad Numeric keypad is located on the right side of the keyboard and consists of keys having numbers (0 to 9) and mathematical operators (+ * /) defined on them. This keypad is provided to support quick entry for numeric data. Cursor Movement Keys These are arrow keys and are used to move the cursor in the direction indicated by the arrow (up, down, left, right). (b) Mouse The mouse is a small device used to point to a particular place on the screen and select in order to perform one or more actions. It can be used to select menu commands, size windows, start programs etc. The most conventional kind of mouse has two buttons on top: the left one being used most frequently. Mouse Actions Left Click : Used to select an item. Double Click : Used to start a program or open a file. Right Click : Usually used to display a set of commands. Drag and Drop : It allows you to select and move an item from one location to another. To achieve this place the cursor over an item on the screen, click the left mouse button and while holding the button down move the cursor to where you want to place the item, and then release it. 19 | P a g e BCA 1 IT www.sbs.ac.in C) Joystick The joystick is a vertical stick which moves the graphic cursor in a direction the stick is moved. It typically has a button on top that is used to select the option pointed by the cursor. Joystick is used as an input device primarily used with video games, training simulators and controlling robots D) Scanner Scanner is an input device used for direct data entry from the source document into the computer system. It converts the document image into digital form so that it can be fed into the computer. Capturing information like this reduces the possibility of errors typically experienced during large data entry. 20 | P a g e BCA 1 IT www.sbs.ac.in II.Output Devices (a) Monitor Monitor is an output device that resembles the television screen and uses a Cathode Ray Tube (CRT) to display information. The monitor is associated with a keyboard for manual input of characters and displays the information as it is keyed in. It also displays the program or application output. Like the television, monitors are also available in different sizes. (b) Liquid Crystal Display (LCD) LCD was introduced in the 1970s and is now applied to display terminals also. Its advantages like low energy consumption, smaller and lighter have paved its way for usage in portable computers (laptops). c) Printer Printers are used to produce paper (commonly known as hardcopy) output. Based on the technology used, they can be classified as Impact or Non-impact printers. Impact printers use the typewriting printing mechanism wherein a hammer 21 | P a g e BCA 1 IT www.sbs.ac.in strikes the paper through a ribbon in order to produce output. Dot-matrix and Character printers fall under this category. Non-impact printers do not touch the paper while printing. They use chemical, heat or electrical signals to etch the symbols on paper. Inkjet, Deskjet, Laser, Thermal printers fall under this category of printers. When we talk about printers we refer to two basic qualities associated with printers: resolution, and speed. Print resolution is measured in terms of number of dots per inch (dpi). Print speed is measured in terms of number of characters printed in a unit of time and is represented as characters-per-second (cps), lines- per-minute (lpm), or pages-per-minute (ppm). Printer uses ink to put data on paper or transparency. There are many types of printers. 1)Dot Matrix Printer 2)Ink Jet Printer 3)Daisy Wheel Printer 4) Laser Printer Speed of the printer is measured in cps(character per second),lpm(line per minute),ppm(pages per minute). The quality of the print is measured in dpi(dots per inch). Some printers can print in color also. Dot matrix, ink jet and laser printers are most commonly used printers. Printer can be classified into two categories according to the technologies used in them. Impact printer and non-impact printer. An impact printer uses a 22 | P a g e BCA 1 IT www.sbs.ac.in device to press/strike something against the ribbon to put a character on the page. Dot matrix, daisy and line printer are impact printers. Non-impact printers print without having a mechanism to strike against a sheet of paper. Ink jet, laser and thermal printer are non-impact printer. Dot Matrix Printer: It uses dots to form a characters on a paper. Dots are made by pins. Dot matrix printer is made up of steel pins which strike the paper through a inked ribbon to create a pattern of tiny dots. The quality of print depends upon the number of steel pins in the machine. Daisy Wheel printer: In this type of printer, the part that puts the characters looks like a wheel. Numbers and letters are arranged in a wheel. And the daisy wheel spins until the correct letter is in position. Then hammer strikes the character against the ribbon, printing it on the paper. Line Printer: It prints a complete line of text at a time. It uses drum or chain with all the characters in the character set on it. The drum or chain moves and prints the necessary characters in the right place. Ink jet printer: It prints the character using fine jet of ink which comes out from the tiny nozzles onto the paper. The ink is kept in reservoir and fed into the firing chambers just below each nozzle. Laser Printer: It uses a beam of light (laser beam) to convert binary data into print. The laser puts an electric charge in the shape of a character on the rotating drum. The dry ink or toner stick only to the drum where it has been charged. These printers are very fast. They can print a whole page at once. 23 | P a g e BCA 1 IT www.sbs.ac.in Thermal Printer: It uses heat to put the characters on the paper. The paper has a spacial coating on it. The printer uses heated wires to turn the paper black. It is basically used for taking ECG in hospital or lab. d) Plotter Plotters are used to print graphical output on paper. It interprets computer commands and makes line drawings on paper using multicolored automated pens. It is capable of producing graphs, drawings, charts, maps etc. Computer Aided Engineering (CAE) applications like CAD (Computer Aided Design) and CAM (Computer Aided Manufacturing) are typical usage areas for plotters. MEMORY Memory is an internal storage area in a computer, which is availed to store data and programs either permanently or temporarily. Computer memory is broadly divided into two groups and they are: 24 | P a g e BCA 1 IT www.sbs.ac.in  Primary memory and  Secondary memory The diagrammatic representation of the classification of computer memory is shown below: Classification of the Computer Memory When the main memory holds instructions and data when a program is executing, the auxiliary memory or secondary memory holds data and programs which are not currently in use and furnishes long term storage. The primary memory and secondary memory are further classified into distinct groups and those are explained in the below diagram: Classification of Primary Memory and Secondary Memory Primary memory: Primary memory is the only type of memory which is directly accessed by the CPU. The CPU continuously reads instructions stored in the primary memory and executes them. Any data 25 | P a g e BCA 1 IT www.sbs.ac.in that has to be operated by the CPU is also stored. The information is transferred to various locations through the BUS. Primary memories are of two types. They are:  RAM  ROM 1. RAM: It stands for Random Access Memory. Here data can be stored temporarily, so this type of memory is called as temporary memory or volatile memory because when power fails the data from RAM will be erased. The information stored in the RAM is basically loaded from the computer’s disk and includes information related to the operating system and applications that are currently executed by the processor. RAM is considered random access because any memory cell can be directly accessed if its address is known. RAM is of distinct types like SRAM, DRAM, and VRAM. 2. ROM: It stands for Read Only Memory. In this, the data will be furnished by the manufacturers regarding the system, so this information can simply be read by the user but cannot add new data or it cannot be modified. ROMs are of distinct types:  PROM – Programmable Read Only Memory  EPROM – Erasable Programmable Read Only Memory  EEPROM – Electrically Erasable Programmable Read Only Memory Secondary memory: Secondary memory or auxiliary memory consists of slower and less expensive device that communicates indirectly with CPU via main memory. The secondary memory stores the data and keeps it even when the power fails. It is used to store or save large data or programs or other information. The secondary storage devices are explained below:  Magnetic disks  Magnetic tape  Optical disk  USB flash drive  Mass storage devices 1.Magnetic disks: Magnetic disks are made of rigid metals or synthetic plastic material. The disk platter is coated on both the surfaces with magnetic material and both the surfaces can be used for storage. The magnetic disk furnishes direct access and is for both small and large computer systems. The magnetic disk comes in two forms:  Floppy disks  Hard disks 26 | P a g e BCA 1 IT www.sbs.ac.in 2. Magnetic tape: magnetic tape is serial access storage medium and it can store a large volume of data at low costs. The conventional magnetic tape is in reels of up to 3600 feet made of Mylar plastic tape. The tape is one-half inch in width and is coated with magnetic material on one side. The reel of tape is loaded on a magnetic tape drive unit. During any read/write operation, the tape is moved from one spool to another in the same way as in the audiocassette tape recorder. The magnetic tape is densely packed with magnetic spots in frames across its width. 3. Optical drives: optical drives are a storage medium from which data is read and to which it is written by lasers. Optical disks can store much more data up to 6GB. Optical store devices are the most widely used and reliable storage devices. The most widely used type of optical storage devices are explained below:  CD – ROM  DVD – ROM  CD – RECORDABLE  CD – REWRITABLE  PHOTO – CD 4. USB flash drives: USB flash drives are removable, rewritable and are physically much smaller drives, which have the weight of less than 30g. In the year of 2010, the storage capacity of the USB flash drives was as large as 256GB. Such devices are a good substitute for floppy disks and CD – ROMs as they are smaller, faster, have thousands of times more capacity, and are more durable and reliable. Until 2005, most desktop and laptop computers had floppy disk drives, but nowadays floppy disk drives have been abandoned in favor of USB ports. The USB connector is often protected inside a removable cap, although it is not likely to be damaged if unprotected. USB flash drives draw power from the computer through external USB connection. The most widely used USB flash drives are the memory cards. 5. Mass storage devices: Mass storage devices refer to the saving of huge data in a persistent manner. Mass storage machines can store up to several trillion bytes of data and hence are used to store or save large databases, such as the information of customers of a big retail chain and library transactions of students in a college. Some of the commonly used mass storage devices are explained below:  Disk array  Automated tape  CD – ROM jukebox Number System 27 | P a g e BCA 1 IT www.sbs.ac.in The technique to represent and work with numbers is called number system. Decimal number system is the most common number system. Other popular number systems include binary number system, octal number system, hexadecimal number system, etc. When we type some letters or words, the computer translates them in numbers as computers can understand only numbers. A computer can understand the positional number system where there are only a few symbols called digits and these symbols represent different values depending on the position they occupy in the number. The value of each digit in a number can be determined using −  The digit  The position of the digit in the number  The base of the number system (where the base is defined as the total number of digits available in the number system) Decimal Number System The number system that we use in our day-to-day life is the decimal number system. Decimal number system has base 10 as it uses 10 digits from 0 to 9. In decimal number system, the successive positions to the left of the decimal point represent units, tens, hundreds, thousands, and so on. Each position represents a specific power of the base (10). For example, the decimal number 1234 consists of the digit 4 in the units position, 3 in the tens position, 2 in the hundreds position, and 1 in the thousands position. Its value can be written as (1 x 1000)+ (2 x 100)+ (3 x 10)+ (4 x l) (1 x 103)+ (2 x 102)+ (3 x 101)+ (4 x l00) 1000 + 200 + 30 + 4 1234 As a computer programmer or an IT professional, you should understand the following number systems which are frequently used in computers. S.No. Number System and Description 1 Binary Number System Base 2. Digits used : 0, 1 28 | P a g e BCA 1 IT www.sbs.ac.in Octal Number System 2 Base 8. Digits used : 0 to 7 Hexa Decimal Number System 3 Base 16. Digits used: 0 to 9, Letters used : A- F Binary Number System The easiest way to vary instructions through electric signals is two-state system – on and off. On is represented as 1 and off as 0, though 0 is not actually no signal but signal at a lower voltage. The number system having just these two digits – 0 and 1 – is called binary number system. Each binary digit is also called a bit. Binary number system is also positional value system, where each digit has a value expressed in powers of 2, as displayed here. In any binary number, the rightmost digit is called least significant bit (LSB) and leftmost digit is called most significant bit (MSB). And decimal equivalent of this number is sum of product of each digit with its positional value. 110102 = 1×24 + 1×23 + 0×22 + 1×21 + 0×20 = 16 + 8 + 0 + 2 + 0 = 2610 Example Binary Number: 101012 29 | P a g e BCA 1 IT www.sbs.ac.in Calculating Decimal Equivalent − Step Binary Number Decimal Number Step 1 101012 ((1 x 24) + (0 x 23) + (1 x 22) + (0 x 21) + (1 x 20))10 Step 2 101012 (16 + 0 + 4 + 0 + 1)10 Step 3 101012 2110 Note − 101012 is normally written as 10101. Octal Number System Characteristics of the octal number system are as follows −  Uses eight digits, 0,1,2,3,4,5,6,7  Also called as base 8 number system  Each position in an octal number represents a 0 power of the base (8). Example 80  Last position in an octal number represents a x power of the base (8). Example 8x where x represents the last position – 1 Octal number system has eight digits – 0, 1, 2, 3, 4, 5, 6 and 7. Octal number system is also a positional value system with where each digit has its value expressed in powers of 8, as shown here − Decimal equivalent of any octal number is sum of product of each digit with its positional value. 7268 = 7×82 + 2×81 + 6×80 = 448 + 16 + 6 = 47010 30 | P a g e BCA 1 IT www.sbs.ac.in Example Octal Number: 125708 Calculating Decimal Equivalent − Step Octal Number Decimal Number Step 1 125708 ((1 x 84) + (2 x 83) + (5 x 82) + (7 x 81) + (0 x 80))10 Step 2 125708 (4096 + 1024 + 320 + 56 + 0)10 Step 3 125708 549610 Note − 125708 is normally written as 12570. Hexadecimal Number System Octal number system has 16 symbols – 0 to 9 and A to F where A is equal to 10, B is equal to 11 and so on till F. Hexadecimal number system is also a positional value system with where each digit has its value expressed in powers of 16, as shown here − Decimal equivalent of any hexadecimal number is sum of product of each digit with its positional value. 27FB16 = 2×163 + 7×162 + 15×161 + 10×160 = 8192 + 1792 + 240 +10 = 1023410 Example 31 | P a g e BCA 1 IT www.sbs.ac.in Hexadecimal Number: 19FDE16 Calculating Decimal Equivalent − Step Binary Decimal Number Number Step 1 19FDE16 ((1 x 164) + (9 x 163) + (F x 162) + (D x 161) + (E x 160))10 Step 2 19FDE16 ((1 x 164) + (9 x 163) + (15 x 162) + (13 x 161) + (14 x 160))10 Step 3 19FDE16 (65536+ 36864 + 3840 + 208 + 14)10 Step 4 19FDE16 10646210 Note − 19FDE16 is normally written as 19FDE. Conversions There are many methods or techniques which can be used to convert numbers from one base to another. We'll demonstrate here the following −  Decimal to Other Base System  Other Base System to Decimal  Other Base System to Non-Decimal  Shortcut method − Binary to Octal  Shortcut method − Octal to Binary  Shortcut method − Binary to Hexadecimal  Shortcut method − Hexadecimal to Binary 32 | P a g e BCA 1 IT www.sbs.ac.in Decimal to Other Base System Steps  Step 1 − Divide the decimal number to be converted by the value of the new base.  Step 2 − Get the remainder from Step 1 as the rightmost digit (least significant digit) of new base number.  Step 3 − Divide the quotient of the previous divide by the new base.  Step 4 − Record the remainder from Step 3 as the next digit (to the left) of the new base number. Repeat Steps 3 and 4, getting remainders from right to left, until the quotient becomes zero in Step 3. The last remainder thus obtained will be the Most Significant Digit (MSD) of the new base number. Example − Decimal Number: 2910 Calculating Binary Equivalent − Step Operation Result Remainder Step 1 29 / 2 14 1 Step 2 14 / 2 7 0 Step 3 7/2 3 1 Step 4 3/2 1 1 Step 5 1/2 0 1 33 | P a g e BCA 1 IT www.sbs.ac.in As mentioned in Steps 2 and 4, the remainders have to be arranged in the reverse order so that the first remainder becomes the Least Significant Digit (LSD) and the last remainder becomes the Most Significant Digit (MSD). Decimal Number − 2910 = Binary Number − 111012. Other Base System to Decimal System Steps  Step 1 − Determine the column (positional) value of each digit (this depends on the position of the digit and the base of the number system).  Step 2 − Multiply the obtained column values (in Step 1) by the digits in the corresponding columns.  Step 3 − Sum the products calculated in Step 2. The total is the equivalent value in decimal. Example Binary Number − 111012 Calculating Decimal Equivalent − Step Binary Number Decimal Number Step 1 111012 ((1 × 24) + (1 × 23) + (1 × 22) + (0 × 21) + (1 × 20))10 Step 2 111012 (16 + 8 + 4 + 0 + 1)10 Step 3 111012 2910 Binary Number − 111012 = Decimal Number − 2910 Other Base System to Non-Decimal System Steps  Step 1 − Convert the original number to a decimal number (base 10).  Step 2 − Convert the decimal number so obtained to the new base number. 34 | P a g e BCA 1 IT www.sbs.ac.in Example Octal Number − 258 Calculating Binary Equivalent − Step 1 − Convert to Decimal Step Octal Number Decimal Number Step 1 258 ((2 × 81) + (5 × 80))10 Step 2 258 (16 + 5 )10 Step 3 258 2110 Octal Number − 258 = Decimal Number − 2110 Step 2 − Convert Decimal to Binary Step Operation Result Remainder Step 1 21 / 2 10 1 Step 2 10 / 2 5 0 Step 3 5 / 2 2 1 Step 4 2 / 2 1 0 Step 5 1 / 2 0 1 Decimal Number − 2110 = Binary Number − 101012 35 | P a g e BCA 1 IT www.sbs.ac.in Octal Number − 258 = Binary Number − 101012 Shortcut method - Binary to Octal Steps  Step 1 − Divide the binary digits into groups of three (starting from the right).  Step 2 − Convert each group of three binary digits to one octal digit. Example Binary Number − 101012 Calculating Octal Equivalent − Step Binary Number Octal Number Step 1 101012 010 101 Step 2 101012 28 58 Step 3 101012 258 Binary Number − 101012 = Octal Number − 258 Shortcut method - Octal to Binary Steps  Step 1 − Convert each octal digit to a 3 digit binary number (the octal digits may be treated as decimal for this conversion).  Step 2 − Combine all the resulting binary groups (of 3 digits each) into a single binary number. Example Octal Number − 258 Calculating Binary Equivalent − 36 | P a g e BCA 1 IT www.sbs.ac.in Step Octal Number Binary Number Step 1 258 210 510 Step 2 258 0102 1012 Step 3 258 0101012 Octal Number − 258 = Binary Number − 101012 Shortcut method - Binary to Hexadecimal Steps  Step 1 − Divide the binary digits into groups of four (starting from the right).  Step 2 − Convert each group of four binary digits to one hexadecimal symbol. Example Binary Number − 101012 Calculating hexadecimal Equivalent − Step Binary Number Hexadecimal Number Step 1 101012 0001 0101 Step 2 101012 110 510 Step 3 101012 1516 Binary Number − 101012 = Hexadecimal Number − 1516 Shortcut method - Hexadecimal to Binary Steps 37 | P a g e BCA 1 IT www.sbs.ac.in  Step 1 − Convert each hexadecimal digit to a 4 digit binary number (the hexadecimal digits may be treated as decimal for this conversion).  Step 2 − Combine all the resulting binary groups (of 4 digits each) into a single binary number. Example Hexadecimal Number − 1516 Calculating Binary Equivalent − Step Hexadecimal Number Binary Number Step 1 1516 110 510 Step 2 1516 00012 01012 Step 3 1516 000101012 Hexadecimal Number − 1516 = Binary Number − 101012 38 | P a g e BCA 1 IT www.sbs.ac.in Applications of Information Technology Every day, people use computers in new ways. Computers and other electronic devices are becoming increasingly affordable. They continue to be more powerful as information- processing tools as well as easier to use. Humans are continually becoming dependant on IT- enabled devices for carrying out simple tasks like remembering a phone number to complex ones like flying a fighter plane. Information Technology has applications in almost all aspects of our life. Some of the important ones are: Science and Engineering: Scientific progress in fields like biotechnology is almost entirely dependent on the use of computers and other microprocessor-controlled devices. Using supercomputers, meteorologists predict future weather by using a combination of observations of weather conditions from many sources, a mathematical representation of the behaviour of the atmosphere, and geographic data. Computer-aided design (CAD) and computer-aided manufacturing (CAM) programs have led to improved products in many fields, especially where designs tend to be very detailed. Computer programs make it possible for engineers to analyse designs of complex structures such as power plants and space stations. Business & Commerce: One of the first and largest applications of computers is keeping and managing business and financial records. Most large companies keep the employment records of all their workers in large databases that are managed by computer programs. Similar programs and databases are used in business functions like billing customers; tracking payments received and payments to be made; and tracking supplies needed and items produced, stored, shipped, and sold. In fact, practically all the information companies need to do business involves the use of computers and Information Technology. Almost all the financial transactions in the world are done electronically. Newer technologies like m- commerce have enabled almost everybody to carry out routine financial transactions on the move. On a smaller scale, many businesses have replaced cash registers with point-of-sale (POS) terminals. These POS terminals not only print a sales receipt for the customer but also send information to a computer database when each item is sold to maintain an inventory of items on hand and items to be ordered. Computers have also become very important in modern factories. Computer-controlled robots now do tasks that are hot, heavy, or hazardous. Robots are also used to do routine, repetitive tasks in which boredom or fatigue can lead to poor quality work. With today's sophisticated hardware, software, and communications technologies, it is often difficult to classify a system as belonging uniquely to one specific application program. Organizations increasingly are consolidating their information needs into a single, integrated 39 | P a g e BCA 1 IT www.sbs.ac.in information system. Management Information System (MIS), with the Chief Information Officer (CIO) at its head, is a whole, new branch of enterprise management. Education: The advent of Information Technology has changed the meaning of the term “literate”, with computer literacy being almost as important as basic literacy in many cases. Computer education is an essential course at the primary level in most schools across the world. With more Information getting digitized every day, and the internet making it accessible to anyone across the world, students are increasingly relying on electronic sources of information rather than physical libraries for their needs. Instructional methodology has also undergone a sea change with use of images, animations, videos, presentations and e- learning to complement traditional techniques. Governance: The concept of e-governance is one of the most novel applications of Information Technology whereby it is changing the lives of millions across the globe. Computerization of Government activities makes it easier to supervise and audit, and makes the administration more responsive to the needs of society. It also bridges the divide between the Government and the people. Technologies like touch-screen kiosks help disseminate information on land records, photo identity cards, pending bills etc. and enable even illiterate people to take more informed decisions. India is leading the world in the effective use of IT for elections. Medicine: Information Technology plays an important role in medicine. For example, a scanner takes a series of pictures of the body by means of computerized axial tomography (CAT) or magnetic resonance imaging (MRI). A computer then combines the pictures to produce detailed three-dimensional images of the body's organs. In addition, the MRI produces images that show changes in body chemistry and blood flow. Most critical life support equipment are programmed to respond to changes in the patient’s status in split- seconds, thereby reducing the response time and risk of human error. Newer concepts like robotic surgery enable specialists to perform surgeries from remote locations. Genomic studies greatly depend on supercomputing power to develop technologies for the future. Entertainment: IT has changed the lifestyle of most people. The convergence of various technologies has created various options for entertainment like games, streaming music and video, digital television broadcasts, satellite radio, animated movies etc. which can be accessed with the help of mobile phones, PDAs, notebook computers or on television either with a cable connection or wirelessly using newer-generation WiFi, CDMA or GPRS technologies. 40 | P a g e BCA 1 IT www.sbs.ac.in Information Technology plays a vital role in most of our daily activities. There is hardly anyone who has not been affected or influenced by IT. With each passing day, newer applications of IT are being developed which increase our interaction with and dependence on IT-enabled devices. Therefore, understanding this technology and using it creatively is imperative to human progress. UNIT-2 Introduction: A language is the main medium of communicating between the Computer systems and the most common are the programming languages. As we know a Computer only understands binary numbers that is 0 and 1 to perform various operations but the languages are developed for different types of work on a Computer. A language consists of all the instructions to make a request to the system for processing a task. From the first generation and now fourth generation of the Computers there were several programming languages used to communicate with the Computer. Here we will go in the detail of the Computer language and its types. Computer Language Description: A Computer language includes various languages that are used to communicate with a Computer machine. Some of the languages like programming language which is a set of codes or instructions used for communicating the machine. Machine code is also considered as a computer language that can be used for programming. And also HTML which is a computer language or a markup language but not a programming language. Similarly there are different types of languages developed for different types of work to be performed by communicating with the machine. But all the languages that are now available are categorized into two basic types of languages including Low-level language and High level language. 41 | P a g e BCA 1 IT www.sbs.ac.in Low Level Language: Low level languages are the machine codes in which the instructions are given in machine language in the form of 0 and 1 to a Computer system. It is mainly designed to operate and handle all the hardware and instructions set architecture of a Computer. The main function of the Low level language is to operate, manage and manipulate the hardware and system components. There are various programs and applications written in low level languages that are directly executable without any interpretation or translation. The most famous and the base of all programming languages “C” and “C++” are mostly used Low level languages till today. Low level language is also divided into two parts are Machine language and Assembly language.  Machine Language is one of the low-level programming languages which is the first generation language developed for communicating with a Computer. It is written in machine code which represents 0 and 1 binary digits inside the Computer string which makes it easy to understand and perform the operations. As we know a Computer system can recognize electric signals so here 0 stands for turning off electric pulse and 1 stands for turning on electric pulse. It is very easy to understand by the Computer and also increases the processing speed. 42 | P a g e BCA 1 IT www.sbs.ac.in The main advantage of using Machine language is that there is no need of a translator or interpreter to translate the code, as the Computer directly can understand. But there are some disadvantages also like you have to remember the operation codes, memory address every time you write a program and also hard to find errors in a written program. It is a machine dependent and can be used by a single type of Computer.  Assembly Language is the second generation programming language that has almost similar structure and set of commands as Machine language. Instead of using numbers like in Machine languages here we use words or names in English forms and also symbols. The programs that have been written using words, names and symbols in assembly language are converted to machine language using an Assembler. Because a Computer only understands machine code languages that’s why we need an Assembler that can convert the Assembly level language to Machine language so the Computer gets the instruction and responds quickly. The main disadvantage of this language is that it is written only for a single type of CPU and does not run on any other CPU. But its speed makes it the most used low level language till today which is used by many programmers. High Level Language: The high level languages are the most used and also more considered programming languages that helps a programmer to read, write and maintain. It is also the third generation language that is used and also running till now by many programmers. They are less independent to a particular type of Computer and also require a translator that can convert the high level language to machine language. The translator may be an interpreter and Compiler that helps to convert into binary code for a Computer to understand. There is various high level programming languages like C, FORTRAN or Pascal that are less independent and also enables the programmer to write a program. UNIT-II Mainly there are two types of computer languages 43 | P a g e BCA 1 IT www.sbs.ac.in Low Level Computer Languages These are machine codes or close to it. Computer cannot understand instruction given in high level languages or in English. It can only understand and execute instructions given in the form of machine languages i.e. the binary number 0 and 1. There are two types of low level computer language. Machine Language A machine language consists of the numeric codes for the operations that a particular computer can execute directly. The codes are strings of 0s and 1s, or binary digits (“bits”), which are frequently converted both from and to hexadecimal (base 16) for human viewing and modification. Machine language instructions typically use some bits to represent operations, such as addition, and some to represent operands, or perhaps the location of the next instruction. Machine language is difficult to read and write, since it does not resemble conventional mathematical notation or human language, and its codes vary from computer to computer. The set of binary codes which can be recognize by the computer is known as the machine code instruction set. A machine language instruction consists of an operation code one or more operands. The operation code specifies that operation that is to be performed e.g. read, record etc. the operands identify the quantities to be operated on e.g. the numbers to be added or the locations where data are stored. But, it is almost impossible to write programs directly in machine code. For this reason, programs are normally written in assembly or high level languages and then are translated in the machine language by different translators. 1. It makes fast and efficient use of the computer 2. It requires no translator to translate the code i.e. directly understood by the computer. 1. All operation codes have to be remembered 2. All memory addresses have to be remembered 3. It is hard to amend or find errors in a program written in the machine language 4. These languages are machine dependent i.e. a particular machine language can be used on only one type of computer. Assembly Languages It was developed to overcome some of the many inconveniences of machine language. This is another low level but a very important language in which operation codes and operands are given in the form of alphanumeric symbols instead of 0’s and 1’s. These alphanumeric symbols will be known as mnemonic codes and can have maximum up to 5 letter combinations e.g. ADD for addition, SUB for subtraction, START LABEL etc. because of this feature it is also known as “Symbolic Programming Language”. This language is very difficult 44 | P a g e BCA 1 IT www.sbs.ac.in and needs a lot of practice to master it because very small English support is given. This symbolic language helps in compiler orientations. The instructions of the assembly language will also be converted to machine codes by language translator to be executed by the computer 1. It is easier to understand and use as compared to machine language 2. It is easy to locate and correct errors 3. It is modified easily 1. Like machine language it is also machine dependent 2. Since it is machine dependent, there programmer should have the knowledge of he hardware also. 45 | P a g e BCA 1 IT www.sbs.ac.in WORD PROCESSING Word processing describes the process of creating or editing a document using a word processor, such as Microsoft Word, Google Docs, or OpenOffice Writer. For example, with a word processor, a student could create a book report and then print it, save it to a disk, display it on the screen, or send it over e-mail. Also, a person looking for a job could create a résumé using a word processor, then e-mail or print and mail it to job recruiters. 46 | P a g e BCA 1 IT www.sbs.ac.in Menu bar Menu bar This is the traditional windows style drop-down menu. When you point to any menu title and click once with the mouse, the menu will open displaying all the commands available under this menu. Clicking on the desired command would tell Word to execute that command. Some commands have ellipses (…) in front of them. These commands have further sub commands. Commands appearing in dim mode cannot be executed unless the prerequisite functions required by that command have been performed, e.g. you cannot use the Copy or Cut command from the Edit menu unless you have selected a piece of text first. Many commands also have a keyboard shortcuts specified against their names. Standard toolbar Standard toolbar Toolbars contain buttons, drop-down menus and other controls that help you to quickly alter the appearance and arrangement of documents by executing a variety of word commands. Toolbars are very helpful and convenient in quickly executing commands without having to go through menus. The standard toolbar contains icons for basic functions like opening files, saving files, printing files, cut, copy, paste etc. Formatting toolbar This contains icons for changing the look of your text (called “formatting” in computer jargon); for example, there are icons for changing fonts, styles, font sizes, text alignment etc. Ruler The Ruler lets you make changes to margins and indents, and helps you create document as per dimensions required. Scroll tools 47 | P a g e BCA 1 IT www.sbs.ac.in These helps you travel within your document. You can go anywhere, up and down, right and left in your document mainly by two ways: Using the horizontal and vertical scroll bars with the help of the mouse; Or using the keyboard to press PgUp, PgDn, Home, End and arrow keys. Status bar Also called the Status Area, this is the normally the last line on your screen. This gives the following information about your work—  Current Page  Section Number  Current/Total pages in the document  Current Cursor Position (where the cursor is presently located)  Current Line Number  Current Column Number  Record Macro-whether macro recording is On or not  Track Revision-whether revisions have been made or not  Extend Selection  Over type mode-whether you are in Insert mode or overwrite mode Cursor Also called the Insertion Pointer, this denotes the place where text, graphics or any other item would be placed when you type, overwrite or insert them. This looks like a tall, skinny toothpick and keeps blinking so that you can locate it easily. Mouse pointer When your mouse pointer looks like an I-beam you should be able to move it freely on the screen. This is used for either placing the cursor at the desired place (take the mouse pointer there and click) or choosing any command either from the menu or from toolbars. The mouse pointer changes shape when in the process of doing certain tasks and the cursor disappears. 48 | P a g e BCA 1 IT www.sbs.ac.in TOOLBARS AND THEIR ICONS Word Standard Toolbar 49 | P a g e BCA 1 IT www.sbs.ac.in 50 | P a g e BCA 1 IT www.sbs.ac.in 51 | P a g e BCA 1 IT www.sbs.ac.in File management Creating a New Document o Click on File Menu o Select and click New option o Otherwise click button on the standard toolbar  Opening an Existing Document: o Click File Menu o Select and click Open option o Otherwise click button on the Standard toolbar. o Double click on the file from the open window  Saving a Document o Click File Menu o Select and click Save button. o Otherwise click button o on the Standard toolbar.  Moving through the document o Open any word document. You can move the cursor to any location on the screen by using the arrow keys on the keyboard. o Right arrow key is used to move one position to the right of the cursor o Left arrow key is used to move one position to the left of the cursor. o Up arrow key is used to move one position to the top of the cursor. o Down arrow key is used to move one position to the down of the cursor. o Page Up key is used to move down the screen at a time o Page down key is used to move up the screen at a time o Hold down Ctrl key and press Home to move to beginning of the 52 | P a g e BCA 1 IT www.sbs.ac.in document. o Hold down Ctrl key and press End to move to end of the document. Editing Word document  Cut , Copy and Paste options  These options will allow you to Cut or Copy a piece of text from one location and to paste at a new location.  To do these functions, o Place the cursor at the begining of the text to be selected. Drag the mouse pointer over the text. The text will now appear in reverse vedeo as shown below:  Jawahar Knowledge Center o Click Edit menu and then click on Cut option (or) click icon on the Standard Toolbar. Move the cursor to the place where you want the text to be pasted. o Click Edit menu and then click Paste option (or) click icon on the Standard Toolbar.  For copying the text from one location to other location the same procedure is to be followed. The difference between Cut and Copy is that while using the Cut option the text will be removed from its original 53 | P a g e BCA 1 IT www.sbs.ac.in location and pasted at a new location, where as when using Copy option a copy of the selected text is pasted at new location without disturbing the original text. 54 | P a g e BCA 1 IT www.sbs.ac.in Searching text Open any document. Click Edit menu and then click Find option. You will get a screen as shown below. In Find What text box type the word you want to find and then click Find Next button. Continue clicking Find Next button until you get the screen shown below. Click OK button and then click X to close Find and Replace dialog box. Replacing text Open any word document. Click Edit menu and then click Replace option. You will get the dialog box as shown below and type the word with which you want to replace. 55 | P a g e BCA 1 IT www.sbs.ac.in Click ReplaceAll button once. You get the below dialog box. Click OK button and then click X to close Find and Replace dialog box. Moving the cursor to a specific page If your word document contains more than one page, you can directly go to specified page by clicking Edit menu and then clicking Go To option. You will get the dialog box as shown below. 10 | P a g e BCA 1 IT www.sbs.ac.in 11 | P a g e BCA 1 IT www.sbs.ac.in In the Enter page number text box, type the required page number as shown below. Click Go To button. Cursor will immediately jump to page 4. Click Close button to close Find and Replace dialog box. Formatting documents Bold, Underline and Italicize the selected text Open a word document. Block the text by first clicking at the start of the text and holding the left mouse button and drag to the desired position and then release the left mouse button. The selected area will be highlighted. Move the mouse pointer to the button on the Standard Toolbar and click once. Move the mouse pointer outside your text and click to release the highlighting. Your text will now appear in BOLD FACE. Like this you can underline or italicize the desired text by using the following buttons 12 | P a g e BCA 1 IT www.sbs.ac.in Left aligning, centering , right aligning and justifying text Left Centre Right Justify  Open a word document.  Block the text by first clicking at the start of the text and holding the left mouse button and drag to the desired position and then release the left mouse button. The selected area will be highlighted.  Move the mouse pointer to Align Left button on the toolbar and click once. Your selected text will be left aligned.  Move the mouse pointer to Align right button on the toolbar and click once. Your selected text will be right aligned.  Move the mouse pointer to Center button on the toolbar and click once. Your selected text will be centered.  Move the mouse pointer to Justify button on the toolbar and click once. Your selected text will be justified. Creating Bulleted and Numberedlist o If a list of items are to be numbered automatically it can be done using Numbered List option  Ex: Microsoft Office consists of MS-Word  MS-Excel  MS-PowerPoint MS-Access  MS-Outlook o The above text is to be selected with mouse. o Click on the Numbered List button on the toolbar o Move out of the text and click to release the highlighting. o Your text will now look like this  MS-Word 13 | P a g e BCA 1 IT www.sbs.ac.in  MS-Excel  MS-PowerPoint  MS-Access  MS-Outlook o Now re-select the text o Click the Bulleted List button on the toolbar. 14 | P a g e BCA 1 IT www.sbs.ac.in The numbers should be replaced with bullets as shown below  MS-Word  MS-Excel  MS-PowerPoint  MS-Access  MS-Outlook Indenting Paragraphs Select a paragraph with the mouse. Click on the Right (increase) Indent b button on the toolbar. Leave the highlighting on and click once more on the Right Indent button. Click once on the Left Indent button. Your text should now be indented by one Tab stop. Each time you click, the paragraph is moved one tab stop. Changing case of text You can change the selected text into either UPPERCASE, lowercase, Title case or tOGGLE cASE Highlight the text. Select the Format menu option Choose Change Case option. You will get the dialog box shown below. 15 | P a g e BCA 1 IT www.sbs.ac.in From the list of options select UPPERCASE to convert lower case into uppercase 16 | P a g e BCA 1 IT www.sbs.ac.in Indenting text with tabs Type your name and address as you would at the head of a letter, but aligned with the left margin e.g. K.Manohar H.No 10-334/3, V.P. Nagar, Malakpet, Hyderabad. Move the cursor to the start of each line and press the Tab key. Just as with the right indent button, your text will move right. How much it moves will depend on the tab settings, which you can change in the Format, Tabs menu as shown below. Font Controlling To get different character styles we can change Font type Click on Format menu 17 | P a g e BCA 1 IT www.sbs.ac.in Select Font option. You will get the following screen. 18 | P a g e BCA-1 IT www.sbs.ac.in You can set Font type, Font Style and Font size and Color of the selected text. Click OK button. Note: The above options are also available on the Formatting Toolbar Font style Font Type Font size Color 19 | P a g e BCA-1 IT www.sbs.ac.in MAIL MERGE Mail merge is a feature within most data processing applications that enables users to send a similar letter or document to multiple recipients. It enables connecting a single form template with a data source that contains information about the recipient’s name, address and other predefined and supported data Mail merge primarily enables automating the process of sending bulk mail to customers, subscribers or general individuals. Mail merge works when a data file is stored that includes the information of the recipients to whom the letter is to be sent. This file can be a spreadsheet or database file containing separate fields for each different type of information to be merged within the letter. The second file is the word document or the letter template. The recipients' information on the letter template is kept empty. When the mail merge process is initiated, the recipients' data from spreadsheet or database is fetched and placed within the empty field in the letter, one by one, until all letters are created. Steps To Perform Mail Merging 1. In a blank Microsoft Word document, click on the Mailings tab, and in the Start Mail Merge group, click Start Mail Merge. 20 | P a g e BCA-1 IT www.sbs.ac.in 1. Click Step-by-Step Mail Merge Wizard. 2. Select your document type. In this demo we will select Letters. Click Next: Starting document. 21 | P a g e BCA-1 IT www.sbs.ac.in 3. Select the starting document. In this demo we will use the current (blank) document. Select Use the current document and then click Next: Select recipients. 22 | P a g e BCA-1 IT www.sbs.ac.in Note that selecting Start from existing document (which we are not doing in this demo) changes the view and gives you the option to choose your document. After you choose it, the Mail Merge Wizard reverts to Use the current document. 23 | P a g e BCA-1 IT www.sbs.ac.in 4. Select recipients. In this demo we will create a new list, so select Type a new list and then click Create. 24 | P a g e BCA-1 IT www.sbs.ac.in o Create a list by adding data in the New Address List dialog box and clicking OK. o Save the list. o Note that now that a list has been created, the Mail Merge Wizard reverts to Use an existing list and you have the option to edit the 25 | P a g e BCA-1 IT www.sbs.ac.in recipient list. o Selecting Edit recipient list opens up the Mail Merge Recipients dialog box, where you can edit the list and select or unselect records. Click OK to accept the list as is. 26 | P a g e BCA-1 IT www.sbs.ac.in 27 | P a g e BCA-1 IT www.sbs.ac.in o Click Next: Write your letter. 5. Write the letter and add custom fields. 28 | P a g e BCA-1 IT www.sbs.ac.in o Click Address block to add the recipients' addresses at the top of the document. o In the Insert Address Block dialog box, check or uncheck boxes and select options on the left until the address appears the way you want it to. 29 | P a g e BCA-1 IT www.sbs.ac.in o Note that you can use Match Fields to correct any problems. Clicking Match Fields opens up the Match Fields dialog box, in which you can associate the fields from your list with the fields required by the wizard. 6. Press Enter on your keyboard and click Greeting line... to enter a greeting. 30 | P a g e BCA-1 IT www.sbs.ac.in 7. In the Insert Greeting Line dialog box, choose the greeting line format by clicking the drop-down arrows and selecting the options of your choice, and then click OK. 8. Note that the address block and greeting line are surrounded by chevrons (« »). Write a short letter and click Next: Preview your letters 31 | P a g e BCA-1 IT www.sbs.ac.in. Preview your letter and click Next: Complete the merge. 32 | P a g e BCA-1 IT www.sbs.ac.in Click Print to print your letters or Edit individual letters to further personalize some or all of the letters. 33 | P a g e BCA-1 IT www.sbs.ac.in UNIT-III SPREADSHEET A spreadsheet or worksheet is a file made of rows and columns that help sort data, arrange data easily, and calculate numerical data. What makes a spreadsheet software program unique is its ability to calculate values using mathematical formulas and the data in cells. Spreadsheet overview Below is a basic example of what a Microsoft Excel spreadsheet looks like, as well as all the important features of a spreadsheet highlighted. 34 | P a g e BCA-1 IT www.sbs.ac.in In the above example, this spreadsheet is listing three different checks, the date, their description, and the value of each check. These values are then added together to get the total of $162.00 in cell D6. That value is subtracted from the check balance to give an available $361.00 in cell D8. Examples and uses of a spreadsheet Although spreadsheets are most often used with anything containing numbers, the uses of a spreadsheet are almost endless. Below are some other popular uses of spreadsheets. 35 | P a g e BCA-1 IT www.sbs.ac.in Finance Spreadsheets are ideal for financial data, such as your checking account information, budgets, taxes, transactions, billing, invoices, receipts, forecasts, and any payment system. Forms Form templates can be created to handle inventory, evaluations, performance reviews, quizzes, time sheets, patient information, and surveys. School and grades Teachers can use spreadsheets to track students, calculate grades, and identify relevant data, such as high and low scores, missing tests, and students who are struggling. Lists Managing a list in a spreadsheet is a great example of data that does not contain numbers, but still can be used in a spreadsheet. Great examples of spreadsheet lists include telephone, to-do, and grocery lists. Sports Spreadsheets can keep track of your favorite player stats or stats on the whole team. With the collected data, you can also find averages, high scores, and statistical data. Spreadsheets can even be used to create tournament brackets. WORKBOOK 36 | P a g e BCA-1 IT www.sbs.ac.in In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file. Below is an example of a spreadsheet called "Sheet1" in an Excel workbook file called "Book1." Our example also has the "Sheet2" and "Sheet3" sheet tabs that are also part of the same workbook. How to create a new workbook To create a new workbook in Microsoft Excel, follow the steps below for your version of Excel. Microsoft Excel 2013 and later 1. Open Excel. 2. Click the File 3. Click New 4. Under Featured, click Blank workbook. Microsoft Excel 2010 1. Open Excel. 2. Click the File tab at the top of the window. 3. Click New 4. Click Blank workbook. Microsoft Excel 2007 1. Open Excel. 2. Click the Office Button 37 | P a g e BCA-1 IT www.sbs.ac.in 3. Select Blank and recent. 4. In the right pane double-click Blank workbook. The Most Common Formatting Commands There are many types of formatting that can be applied to Microsoft Excel worksheets. The most commonly used formatting commands show up on the Home tab in three groups: 1. The Font Group. The font group commands change the appearance of text within a cell or of the cell itself. 2. The Alignment Group. The alignment group commands change the position of text within a cell or cells. 3. The Number Group. The number group commands change the format of numbers and dates within a cell. Formatting changes can be applied to a whole worksheet, a range of cells within a worksheet, individual cells, and sometimes even text within a cell. Selecting Ranges of Cells To select a range of cells in Microsoft Excel: 1. Click on a cell in one of the corners of the range of cells you wish to select 38 | P a g e BCA-1 IT www.sbs.ac.in 2.Hold the left mouse button down and drag horizontally and vertically until the range you wish to select is highlighted: 39 | P a g e BCA-1 IT www.sbs.ac.in 1. Release the click. You can select all the cells in a row by clicking on the number to the left of the row: You can select all the cells in a column by clicking on the letter at the top of the column: 40 | P a g e BCA-1 IT www.sbs.ac.in Adding Color to Worksheet Tabs You can customize your workbook by applying colors to the sheet tabs. This can help differentiate the tabs and make them stand out. To add color to worksheet tabs: 1. Right-click the tab to which you want to add color. 41 | P a g e BCA-1 IT www.sbs.ac.in 2. Select Tab Color 3. Select a color from the displayed options 42 | P a g e BCA-1 IT www.sbs.ac.in Adding Themes to Workbooks You can further customize workbooks and worksheets by using a Microsoft Office theme. Themes apply a set of fonts, colors, and other effects. To apply a theme to a worksheet: 1. On the Page Layout tab, in the Themes group, select Themes. 2. From the drop-down menu, select a theme to apply to the worksheet 43 | P a g e BCA-1 IT www.sbs.ac.in 44 | P a g e BCA-1 IT www.sbs.ac.in Bold, Italicize and Underline Text To bold text in Microsoft Excel: 1. Select the cell or cells in which you wish to bold the text. 2. On the Home tab, in the Font group, click the Bold command. To italicize text in Microsoft Excel: 1. Select the cell or cells in which you wish to italicize the text. 2. On the Home tab, in the Font group, click the Italic command. To underline text in Microsoft Excel: 1. Select the cell or cells in which you wish to underline the text. 2. On the Home tab, in the Font group, click the Underline command. 45 | P a g e BCA-1 IT www.sbs.ac.in Set Font and Font Size To change the font of text or numbers in cells in Microsoft Excel: 1. Select the cell or cells in which you wish to change the font. 2. On the Home tab, in the Font group, click the arrow to the right of the Font command. 3. Select a font from the drop down menu: 46 | P a g e BCA-1 IT www.sbs.ac.in To change the size of the font of text or numbers in cells in Microsoft Excel: 1. Select the cell or cells in which you wish to change the font size. 2. On the Home tab, in the Font group, click the arrow to the right of the Font Size command. Select a font size from the drop down menu: 47 | P a g e  BCA-1 IT www.sbs.ac.in CELL REFERENCING A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. In one or several formulas, you can use a cell reference to refer to:  Data from one or more contiguous cells on the worksheet.  Data contained in different areas of a worksheet.  Data on other worksheets in the same workbook. For example: This formula: Refers to: And Returns: =C2 Cell C2 The value in cell C2. =A1:F4 Cells A1 through F4 The values in all cells, but you must press Ctrl+Shift+Enter after you type in your formula. =Asset-Liability The cells named The value in the cell named Asset and Liability Liability subtracted from the 48 | P a g e BCA-1 IT www.sbs.ac.in This formula: Refers to: And Returns: value in the cell named Asset. {=Week1+Week2} The cell ranges The sum of the values of the named Week1 and cell ranges named Week1 and Week2 Week 2 as an array formula. =Sheet2!B2 Cell B2 on Sheet2 The value in cell B2 on Sheet2. Cell references in Excel are very important. Understand the difference between relative, absolute and mixed reference, and you are on your way to success. Relative Reference By default, Excel uses relative references. See the formula in cell D2 below. Cell D2 references (points to) cell B2 and cell C2. Both references are relative. 1. Select cell D2, click on the lower right corner of cell D2 and drag it down to cell D5. 49 | P a g e BCA-1 IT www.sbs.ac.in Cell D3 references cell B3 and cell C3. Cell D4 references cell B4 and cell C4. Cell D5 references cell B5 and cell C5. In other words: each cell references its two neighbors on the left. Absolute Reference See the formula in cell E3 below. 1. To create an absolute reference to cell H3, place a $ symbol in front of the column letter and row number ($H$3) in the formula of cell E3. 2. Now we can quickly drag this formula to the other cells. 50 | P a g e BCA-1 IT www.sbs.ac.in The reference to cell H3 is fixed (when we drag the formula down and across). As a result, the correct lengths and widths in inches are calculated. Mixed Reference Sometimes we need a combination of relative and absolute reference (mixed reference). 1. See the formula in cell F2 below. 51 | P a g e BCA-1 IT www.sbs.ac.in 2. We want to copy this formula to the other cells quickly. Drag cell F2 across one cell, and look at the formula in cell G2. Do you see what happens? The reference to the price should be a fixed reference to column B. Solution: place a $ symbol in front of the column letter ($B2) in the formula of cell F2. In a similar way, when we drag cell F2 down, the reference to the reduction should be a fixed reference to row 6. Solution: place a $ symbol in front of the row number (B$6) in the formula of cell F2. Result: 52 | P a g e BCA-1 IT www.sbs.ac.in Note: we don't place a $ symbol in front of the row number of $B2 (this way we allow the reference to change from $B2 (Jeans) to $B3 (Shirts) when we drag the formula down). In a similar way, we don't place a $ symbol in front of the column letter of B$6 (this way we allow the reference to change from B$6 (Jan) to C$6 (Feb) and D$6 (Mar) when we drag the formula across). 3. Now we can quickly drag this formula to the other cells. The references to column B and row 6 are fixed. CREATING FORMULAS IN EXCEL Formulas and functions are the building blocks of working with numeric data in Excel. 53 | P a g e BCA-1 IT www.sbs.ac.in What is a formula? It is the simplest form, a formula is an expression made up of cell addresses and arithmetic operators. Formulas can also be made up of discrete values i.e. =6*3. Excel evaluates the formula to a value. An example of a formula looks as follows. =A2 * D2 / 2 HERE,  "=" tells Excel that this is a formula, and it should evaluate it.  "A2" * D2" makes reference to cell addresses A2 and D2 then multiplies the values found in these cell addresses.  "/" is the division arithmetic operator  "2" is a discrete value Formulas practical exercise We will work with the sample data for the home budget to calculate the subtotal.  Create a new workbook in Excel  Enter the data shown in the home supplies budget above.  Your worksheet should look as follows. We will now write the formula that calculates the subtotal Set the focus to cell E4 54 | P a g e BCA-1 IT www.sbs.ac.in Enter the following formula. =C4*D4 HERE,  "C4*D4" uses the arithmetic operator multiplication (*) to multiply the value of the cell address C4 and D4. Press enter key You will get the following result The following animated image shows you how to auto select cell address and apply the same formula to other rows. 55 | P a g e BCA-1 IT www.sbs.ac.in What is a function? (Function Wizard) A function is a predefined formula. Functions allow you to use descriptive names to automatically apply formulas for you. Examples of functions include;  SUM for summation of a range of numbers  AVERAGE for calculating the average of a given range of numbers  COUNT for counting the number of items in a given range The importance of functions Functions increase user productivity when working with excel. Let's say you would like to get the grand total for the above home supplies budget. To make it simpler, you can use a formula to get the grand total. Using a formula, you would have to reference the cells E4 through to E8 one by one. You would have to use the following formula. = E4 + E5 + E6 + E7 + E8 56 | P a g e BCA-1 IT www.sbs.ac.in With a function, you would write the above formula as =SUM (E4:E8) As you can see from the above function used to get the sum of a range of cells, it is much more efficient to use a function to get the sum than using the formula which will have to reference a lot of cells. Common functions Let's look at some of the most commonly used functions in Excel. We will start with statistical functions. S/ FUNCTION CATEGORY DESCRIPTION USAGE N 01 SUM Math & Trig Adds all the values in a range of cells =SUM(E4:E8) 02 MIN Statistical Finds the minimum value in a range of =MIN(E4:E8) cells 03 MAX Statistical Finds the maximum value in a range of =MAX(E4:E8) cells 57 | P a g e BCA-1 IT www.sbs.ac.in 04 AVERAGE Statistical Calculates the average value in a range =AVERAGE(E4 of cells :E8) 05 COUNT Statistical Counts the number of cells in a range =COUNT(E4:E of cells 8) 06 LEN Text Returns the number of characters in a =LEN(B7) string text 07 SUMIF Math & Trig Adds all the values in a range of cells =SUMIF(D4:D that meet a specified criteria. 8,">=1000",C =SUMIF(range,criteria,[sum_range]) 4:C8) 08 AVERAGEI Statistical Calculates the average value in a range =AVERAGEIF( F of cells that meet the specified criteria. F4:F8,"Yes",E =AVERAGEIF(range,criteria,[average_ra 4:E8) nge]) 09 DAYS Date & Time Returns the number of days between =DAYS(D4,C4) two dates 10 NOW Date & Time Returns the current system date and =NOW() time Numeric Functions As the name suggests, these functions operate on numeric data. The following table shows some of the common numeric functions. 58 | P a g e BCA-1 IT www.sbs.ac.in S/N FUNCTION CATEGORY DESCRIPTION USAGE 1 ISNUMBER Information Returns True if the supplied =ISNUMBER(A3) value is numeric and False if it is not numeric 2 RAND Math & Generates a random number =RAND() Trig between 0 and 1 3 ROUND Math & Rounds off a decimal value to =ROUND(3.14455,2) Trig the specified number of decimal points 4 MEDIAN Statistical Returns the number in the =MEDIAN(3,4,5,2,5) middle of the set of given numbers 5 PI Math & Returns the value of Math =PI() Trig Function PI(π) 6 POWER Math & Returns the result of a =POWER(2,4) Trig number raised to a power. POWER( number, power ) 7 MOD Math & Returns the Remainder when =MOD(10,3) Trig you divide two numbers 59 | P a g e BCA-1 IT www.sbs.ac.in 8 ROMAN Math & Converts a number to roman =ROMAN(1984) Trig numerals String functions These functions are used to manipulate text data. The following table shows some of the common string functions. S/ FUNCTION CATEGORY DESCRIPTION USAGE COMME N NT 1 LEFT Text Returns a number of =LEFT("GURU99", Left 4 specified characters 4) Characte from the start (left-hand rs of side) of a string "GURU9 9" 2 RIGHT Text Returns a number of =RIGHT("GURU9 Right 2 specified characters 9",2) Characte from the end (right-hand rs of side) of a string "GURU9 9" 3 MID Text Retrieves a number of =MID("GURU99", Retrievi characters from the 2,3) ng middle of a string from a Characte specified start position rs 2 to 5 and length. =MID (text, start_num, num_chars) 60 | P a g e BCA-1 IT www.sbs.ac.in 4 ISTEXT Information Returns True if the =ISTEXT(value) value - supplied parameter is The Text value to