Himachal Pradesh Board Informatics Practices Syllabus for Class 11


Himachal Pradesh Board Syllabus for Class 11 Informatics Practices

Unit I : Computer System and Business Applications

Evolution of computers; Basics of computer and its operation: Functional Components and their inter-connections, concept of Booting; Hardware concepts; Diagram illustrating main parts of computers;

Central Processing Unit (CPU) : Arithmetic Logic Unit (ALU), Control Unit, Memory Unit (RAM-Random Access Memory & ROM - Read Only Memory) Role of Input, Processing and Output Devices in a computer system.

Input devices : Keyboard, Mouse, Light pen, Touch Screens, Graphics Tablets, Joystick, Mic, MICR, OCR, Scanner, Smart Card reader, Bar code reader, Biometric sensor, web camera, digital camera;

Output Devices : Monitor/Visual Display Unit (VDU), Printer (Dot Matrix Printer, Desk jet/Ink Jet/ Bubble Jet Printer, Laser Printer), Plotter, Speaker).

Secondary Storage Devices : Floppy Disk, Hard Disk, Compact Disk, Magnetic Tape, Digital Video Disk (DVD), Zip Drive; Units of Memory: Bit (Binary Digit), Byte, Kilobyte), Megabyte, Gigabyte.

Software Concepts :
Types of Software : System Software, Utility Software and Application Software.

System Software : Operating System, Language Compilers, Interpreters and Assembler

Operating System : Need of operating systems, Functions of Operating System Types of operating system.

Utility Software : Compression tools, Anti Virus, File Management tools and Disk Management ;

Application Software as a tool : Word Processor, Presentation Tool, Spreadsheet Package, Database Management System; Business software (for example: Inventory Management System, Payroll System, Financial Accounting, Hotel Management, and Reservation System); Development of programming languages - Machine Language, Assembly Language, High Level Language (BASIC, COBOL, FORTRAN, PASCAL, C++); GUI based language - Visual Basic Visual C++; C#, Java, vb.net.

GUI Operating System:
Important : Students/Teachers can also perform similar operation on any operating system. It is advised that the teacher while using any one operating system, give a demonstration of equivalent features for the other operating system.

Windows: General features, Elements of Desktop - Taskbar, Icon, Start button, Shortcuts, Folder, Recycle Bin, My Computer.

Start Menu : Program, Documents, Settings, Find/Search, Help, Run, Shut Down/Log off; Customization of Taskbar, start menu, Display properties (Wallpaper, Font Settings, Colour Settings, Screen Savers);

Program Menu : Accessories - Calculator, Notepad, Paint, Word Pad, Entertainments (CD Player, Sound Recorder, Media Player, Volume (Controller); Internet Browsers – Mozila Firefox, internet Explorer, Netscape Navigator.

Control Panel : Add new hardware; Add new Software, Printer Installation, Date/Time, Mouse, and Regional Settings

Documentation:
Purpose of using word processing software, opening a new/existing document, closing a document, typing in a document, saving a document, print preview, printing a document, setting up of page as per the specifications, selecting a portion of document, copying selected text, cutting selected text, pasting selected text; changing font, size, style, colour of text; Inserting symbol; Formatting: Alignment – Left, Right, Center; Justification : Industries and Business Computing : Types of Industries (Production, Shipping, Travel, Hotel, Insurance, Construction, Automobile), Applications of Business Computing in Industries.


Unit 2 : Introduction to Programming

Programming Methodology :
General Concepts; Modular approach; Stylistic Guidelines: Clarity and Simplicity of Expressions, Names, Comments, Indentation; Documentation and Program Maintenance; Running and Debugging programs, Syntax Errors, Run-Time Errors, Logical Errors; Problem Solving Methodology and Techniques: Understanding of the problem, Identifying minimum number of inputs required for output, Step by step solution for the problem, breaking down solution into simple steps, Identification of arithmetic and logical operations required for solution, Using Control Structure: Conditional control and looping (finite and infinite).

Programming Tool : Visual Basic

Introduction to Programming – Modular Programming, Object Oriented Programming, Event Driven Programming.
About Visual Basic (Object Based Programming Language), Rapid Application Development using Visual Basic; Concept of Project in Visual Basic, VB Project Options – Standard EXE, Active X DLL, Active X EXE, Active X Control, Active X Document DLL, Active X Document EXE, Addin, VB Application Wizard, IIS Application, DHTML Application.

Getting Familiar with Visual Basic User Interface-Pull-Down menus, Toolbar, Toolbox, Project Explorer, Properties Window, Form Layout window, Form, Immediate window; Opening and Closing windows, Resizing and moving windows, Docking windows; Quitting Visual Basic.

Visual Basic Tool Box (Standard Window Controls) - Pointer, Picture Box, Label, Text Box, Frame, Command Button, Check Box, Option Button, Combo Box, List Box, Horizontal Scroll bar, Vertical Scroll bar, Timer, Drive List box,Directory List box, File List box, Shape, Line, Image, Data, OLE; Object Naming Conventions, Event Procedures.

Programming Fundamentals:

Data Types : Integer, Long, Single, Double, Currency, String, Byte, Boolean, Data, Object, Variant;

Variables : Need to use variable, Declaring Variables, Variable Naming Convention, Assigning value to variables, Data Types of variable, Scope and lifetime of Variables (Public and Private);

Control Structures :

Decision Structure - IF, IF- THEN-ELSE, Select Case

Looping Structure - Do While ......Loop, Do ......Loop While, For..... Next, For each ....... Next;

Menu Editor : Concept of Menus, Shortcut menus and Popup menus Designing Menu System, Menu Editor Dialog Box Options (Name, Index, Shortcut, Help Context ID, Negotiate Position, Checked, Enabled, Visible, Window List, Right Arrow, Left Arrow, Up Arrow, Down Arrow, Menu List, Next, Insert, Delete, OK, Cancel), To Create Menu Controls in the Menu Editor, Menu Naming Conventions, Setting the Name Property, Creating a Menu Control Array, Creating Sub Menus, Separating Menu Controls, Assigning Access Keys and Shortcut Keys, Controlling Menus at Run time-Enabling and Disabling Menu Commands, Displaying a Check mark on a Menu Control, Making a Menu Control Invisible, Adding Menu Control at Run time, Displaying Pop-Up Menu.

General Controls (Advance) : Image List, Common Dialog Box, ADO DC, DB Combo, Media Player Control, DB Grid; Adding a Toolbar; Creating an Image List, Adding Images to the Toolbar, To Add Code for the Toolbar Buttons.
Adding Status Bar: Adding Status Bar panels, Adding Time on the panel.
Dialog Boxes : Pre-defined dialog box, Custom dialog box.


Unit 3 : Relational Database Management System

Database Management System:
Introduction to database concepts : Relation/Table, attribute, Tuple/ Rows, field, Data, Concept of String, Number and Data values, Data type and Data Integrity (Domain and Referential Integrity). Candidate key, Alternate Key, Primary Key, Foreign Keys; Data Normalization-first, second, third, BCNF normal form;
Examples of Commercially available Database Management System’s (Back-End) – Oracle, MS-SQL Server, DB2, MySQL, Sybase, INGRES.
Examples of Front End Software’s : Oracle Developer, Visual Basic, Visual C++, Power Builder, Delphi.

RDBMS Tool:
Interface with oracle, Login Screen, Entering Name and Password; Classification of SQL Statements : DML (SELECT, INSERT, UPDATE, DELETE), DDL (CREATE, DROP, ALTER, RENAME, TRUNCATE), DCL (GRANT, REVOKE), TCL (COMMIT, ROLLBACK); SQL SELECT Statement : SQL SELECT statement, Selecting All the Columns, Selecting Specific Column, Column Heading Default, Using Arithmetic Operators, Operator Precedence, Significance of NULL value, NULL values in Arithmetic Expressions, Defining and using Column Alias, Concatenation Operation (||), Duplicate rows and their Elimination (DISTINCT keyword), Role of SQL and SQL* Plus in interacting with RDBMS, Displaying Table Structure (DESC command).

SELECT Statement Continued: Limiting Rows during selection (using WHERE clause), Working with Character Strings and Dates, Using Comparison operators, BETWEEN Operators, IN Operator, LIKE Operator, IS NULL Comparison, Logical Operators, Use of Logical Operators (AND/OR/ NOT Operators), Logical Operator Precedence, ORDER BY Clause, Sorting in Ascending/Descending Order, Sorting By Column Alias Name, Sorting On Multiple Columns.

Functions : SQL Functions, Types of SQL Function (Single Row/Multiple Row), Single Row SQL Functions, Character Functions (Case Conversion/ Character Manipulation), Case Conversion Functions (lower (), InitCap (), UPPER () Character Manipulation Function (CONCAT(), INSTR(), LENGTH(), TRIM(), SUBSTR(), LPAD()), Number Functions (ROUND(), TRUNC(), MOD()), Working with Dates (LAST_DAY(), MONTHS_BETWEEN(), NEXT_DAY(), ADD_MONTHS(), ROUND(), TRUNC()) Arithmetic Operation on Dates, Date Functions and their Usage, Data type Conversion Functions, Implicit and Explicit Conversion, TO_CHAR Function with Dates, TO_CHAR Function for Numbers. TO_NUMBER and TO_DATE Functions, NVL Function and its Usage, DECODE Function and its Usage.

Grouping Records : Concept of Grouping Records and Nested Grouping, Nested Grouping of records, Group Functions, Types of group functions (MAX(), MIN(), AVG(), SUM(), COUNT()), Using AVG and Sum Functions, Using MIN and MAX Functions, Using the COUNT Function, using

COUNT(*), DISTINCT clause with COUNT, Group Functions and Null Values, Using NVL Function with Group Functions, Grouping Records; Group by Clause, Grouping By More than One Column, Illegal Queries with Group By Clause, Excluding Group Results: Having Clause, Nesting Group Functions.

Sub Queries : Concept of Sub-Query, Sub Query to Solve a Problem, Guidelines for Using Sub Queries, Types of Sub-Queries (Single Row and Multiple Row) and (Single Column and Multiple Column); Single Row Sub- Query and its Execution.

Displaying Data From Multiple Tables : Concept of Join, Result of Join, Cartesian Product and Generating Cartesian Product example using Mathematical Set), Types of joins (EQUI, SELF, NON-EQUI, OUTER (LEFT and RIGHT)), Equi-join : Retrieving Records with Equi-join, Additional Search

Conditions using AND operator, Short Naming Convention for Tables (Table Aliases), Non-Equi join and its Implementation, Out-Joint and Its Usage, Self- Joint (Joining a table to itself).

Manipulating Data of A Table/Relation; Concept of DML (Data Manipulation Language), INSERT Statement, Inserting New Rows, Inserting New Rows with Null Values, Inserting Date Values, Use of Substitution Variable to Insert Values, Copying Rows From Another Table, Update Statement to Change Existing Data of a Table, Updating Rows In A Table, Updating Rows Based on Another Table, Delete statement/Removing Row/ Rows from a Table, Deleting Rows Based on condition from another Table. Making Data Manipulation Permanent (COMMIT). Undo Data Manipulation Changes (ROLLBACK).

Database Objects : View, Table, Sequence, index, and Synonyms, DLL (Data Definition Language), Naming Convention, Creating Views, Creating Synonyms, Simple Views and Complex Views, Retrieving Data From a View, Querying a View, Modifying a View.

 

Including Constraints : Constraints, Concept of using Constraints, Constraint Guidelines, Defining Constraints, NOT NULL, UNIQUE KEY, PRIMARY KEY, FOREIGN KEY, FOREIGN KEY Constraint Keywords, CHECK, Adding a Constraint, Dropping a Constraint, Disabling Constraints, Enabling Constraints, Viewing Constraints, Viewing The Columns, Associated with Constraints.

Creation of a Table/Relation; CREATE TABLE Statement, Data types, The DEFAULT option, Creating Tables. Referencing Another User’s Tables, Querying the Database Dictionary to view all tables in the Oracle Database, Creating a Table by using a Sub-Query.


Managing Existing Tables and other Database Objects : The ALTER TABLE Statement, Adding a New Column in a Table. Modifying Existing Column, Dropping a Column, Renaming an Object, Truncating a Table, Adding Comments to a Table, Dropping Views, Dropping Synonyms, Droping Tables; giving permission to other users to work on Created Tables and Revoking it (GRANT and REVOKE statement).


Informatics Practices
(Practical)
Time: 3 hrs
Total Marks: 30

1. Hands on Experience

A problem should be given covering the following:

  • Table definition (The table must include constraints).

  • A form with Lable, Text, Command Button control, List Box, Drive List Box, Directory List Box, File List Box, Tool and Menu Bar (Any 4) DSN to access tables in the database.

  • For data connectivity (Activex Database Control).

  • Change of Text Box Control Properties to view Database fields.


2. Practical File
The practical file should contain print outs from each of the following topics.

  1. Create an application using Visual Basic with a Text Box control to accept a name from the user and print “Hello <Name>” in a message box. e.g. when user types his name as “Kamal Kant” in the text box and clicks OK button, a message “Hellow Kamal Kant should be displayed and if he clicks on Cancel button a message as “Bye Kamal Kant” should appear.

  2. Create an Application having two Text Boxes on the Window. Get Title, First Name and Last Name on its. On clicking Ok button a message should appear by joining Title + First Name + Last Name. e.g. if user enters Prof. in Title, Rajyash in First Name, and Swami as Last Name then the message to be printed should be “Happy Deepawali Prof. Rajyash Swami.”

  3. Create an application to let user guess any number and click a Play button given on the form. On clicking the Play button the application will generate a random number. If the generated number is same as guessed by the user then display a message “You Win” otherwise display a message “You Loose”.

  4. Create an application to Display Image files kept in different folders in the system. The application should allow the user to navigate in the folders and list all Image Files (*.BMP, * JPG) when ever a image file is selected it should get that picture displayed in an Image control.

  5. Create an application having menu bar and tool bar to create a text file, navigate and open text files, edit text file and save changes made by the user.

  6. Create a small application working as a general purpose calculator. (+, –, ´, ¸).

  7. SQL assignments (based on Demo Tables present in the ORACLE database for example Emp table, Dept table and SalGrade table);

  • Display all the records (all columns) from table Emp.

  • Display EmpNo and EName of all employees from table Emp.

  • Display Ename, Sal and Sal added with Comm from table Emp.

  • Display EName joined with Job with heading “Employee”, Sal*12 as “Total Salary” from table Emp.

  • Display distinct Sal of employees from table Emp.

  • Show the Structure of table Dept.

  • Write a Query to display EName and Sal of Employees whose salary is greater than or equal to 3000 from table Emp.

  • Write a Query to display employee Number, name, sal and sal* 12 as Annual Salary whose commission is no NULL from table Emp.

  • Write a Query to display employee name and salary of those employee who don’t have there salary in the range of 1500 to 2000.

  • Write a Query to display name, job, salary, and Hire Date of employees who are hired between February 20, 1981, and May 1, 1981, Order the query in ascending order of Hire Date.

  • Write a Query to display the name and hire data of all employees who were hired in 1982.

  • Write a Query to display the name, job title and salary of employee who do not have manager.

  • Write a Query to display the name of employee whose name contains ‘A’ as third alphabet.

  • Write a Query to display the name of employee whose name contains ‘T’ as the last alphabet.

  • Write a Query to display the name of employee whose name contains ‘M’ as first alphabet ‘L’ as third alphabet.

  • Write a Query to display the name of employee who is having ‘L’ as any alphabet of the name.

  • Write a query to display the current system date.

  • Write a Query to display employee number, name, salary, salary increase by 15% expressed as a whole number. Label the column as New Salary.

  • Write a Query to display the employee’s name and salary review date, which is the date after six months of Hire Date.

  • Write a Query to display the employee’s name and salary review date, which is the date after six months of HireDate in format of ‘Sunday, 7 SEP, 1981’.

  • For each employee display employee name and total number of weeks lapsed between HireDate and Today.

  • For each employee display employee name and total number of days lapsed between HireDate and Today.

  • Create a query that produces display in the following format– <employee name> Earns $<salary> Monthly and working as <Job>.

  • Write a query which displays the employee name with the first letter capitalized and all other letters lower case and length of their name string.

  • Write a Query to display the employee name and commission amount. If the employee does not earn commission, put “No Commission”.

  • Write a query to display the grade of all employees based on the value of the column job as per following scheme :

     

    Job

    Grade

    President

    A

    Manager

    B

    Analyst

    C

    Salesman

    D

    Clerk

    E

    None of the above

    O

  • Write a query to display the EName and DeptNo and DName for all employees using tables Emp and Dept.

  • Write a Query to display employee name, department name and location of all employees who have manager number between 7500 and 7900.

  • Write a Query to display the employee name, department number and all the employees that worked in the same department as the given employee.

  • Write a Query to display employee name and HireDate of employees who are employed after Employee ‘BLAKE’.

  • Write Query to display employee number, name and manager’s name with their manager number.

  • Write a Query to Display the Sum, Average, Highest and Lowest salary of the employees.

  • Write a Query to Display the Sum, Average, Highest and Lowest Salary of the employees grouped by department number.

  • Write a Query to Display the Sum, Average, Highest and Lowest salary of the employees grouped by department number and sub-grouped by job.

  • Write a query to display the number of employee with same job.

  • Write a query to display the average of Highest and lowest salary of each department.

  • Write a query to display the difference of Highest and lowest salary of each department having maximum salary > 4000.

  • Write a query to display the employee name and job for all employee in the same department as ‘ALLEN’. Write a query to display employee name and salary of those who either work in department 10 or have salary greater than employee 7521.

    Before the following exercise please ensure that you are provided with a table Employee with following description.

    Table: Employee

 

Name of Column

 

Type

ID

NUMBER

(4)

First_Name

VARCHAR 2

(30)

Last_Name

VARCHAR 2

(30)

User_ID

VARCHAR 2

(10)

Salary

NUMBER

(9, 2)

 

1. Use DESCRIBE command to ensure the table structure.
2. Add the following data in the above Table as instructed.
 

 

ID

First_Name

Last_Name

User_ID

Salary

1

Dim

Joseph

Jdim

5000

2

Jagannath

Mishra

Jnmishra

4000

3

Siddharth

Mishra

Smishra

8000

4

Shankar

Giri

Sgiri

7000

5

Gautam

Buddha

Bgautam

2000

 

  • Populate table with first record mentioning the column list in the insert clause.

  • Populate table with next two records without mentioning the column list in the insert clause.

  • Populate table with 4th record and enter only ID and First_Name.

  • Populate table with 5th record and enter ID, User_ID, and Last_Name only,.

  • For record with ID = 4 update record with Last_Name User_ID and Salary.

  • For record with ID = 5 update records with First_Name and Salary.

  • Make the changes permanent.

  • Modify the Last_Name, of the employee 3, to Gautam.

  • Modify the Salary and increase it by 1000, for all who get salary less then 5000.

  • Delete the employee record having First_Name as Siddharth.

  • Make the changes permanent.

  • Remove the entire contents of the table.

  • Undo the above step.

  • Create a table Employee1 with columns ID, First_Name and Dept_ID from table Employee and also confirm the existence of table Employee1.

  • Create a view VU_Emp1 which should include column EmpNo. Ename and DeptNo. from the table Emp.

  • Create a view VU_Emp2 which should include column EmpNo, Ename and Dept No. from the table Emp and change the column headings as Emp Number, Employee, Department.

  • Select VIEW-NAME and TEXT from the data dictionary USER_VIEWS.

  • Create the table Department table based on the following table instance chart.

 

Column Name

ID

Name

Data Type

NUMBER

VARCHAR 2

Length

8

25

 

  • Populate the table Department with data from table dept. Including only required columns.

  • Create the table Employee based on the following table instance chart.

 

Column Name

ID

First_Name

Last_Name

Dept_ID

Data Type

NUMBER

VARCHAR2

VARCHAR2

NUMBER

Length

8

25

25

8


1. Rename table Employee1 to Employee2.
2. Drop table Employee2.
3. Drop table Employee and Department.
4. Create table Customer as per following Table Instance Chart.

 

Column Name

Cust_ID

Cust_Name

Cust_Address1

Cust_Address2

Pincode

Cust_Phone

Key Type

 

 

 

 

 

 

Nulls/Unique

 

 

 

 

 

 

FK Table

 

 

 

 

 

 

FK Column

 

 

 

 

 

 

Datatype

NUMBER

VARCHAR2

VARCHAR2

VARCHAR2

NUMBER

VARCHAR2

Length

7

30

20

30

6

10

 

  • Add one column Email of data type VARCHAR2 and size 30 to the table Customer.

  • Change the data type of column pin code to VARCHAR2(10) in the table Customer.

  • Add one more column Customer Income Group of data type VARCHAR2(10).

  • Insert few records with relevant information, in the table.

  • Drop the column Customer Income Group from table Customer.

  • Create table Department as per following Table Instance Chart.

 

Column Name

DeptID

DeptName

DeptLocation

Key Type

Primary

 

 

Nulls/Unique

 

NOT NULL

 

FK Table

 

 

 

FK Column

 

 

 

Datatype

NUMBER

VARCHAR2

VARCHAR2

Length

2

20

20


Create table Employee as per following Table Instance Chart.

 

 

Column Name

EmpID

EmpName

EmpAddress

EmpPhone

EmpSal

DeptID

Key Type

Primary

 

 

 

 

Foreign

Nulls/Unique

 

NOT NULL

 

 

 

 

FK Table

 

 

 

 

 

 

FK Column

 

 

 

 

 

 

Data type

NUMBER

VARCHAR2

VARCHAR2

VARCHAR2

NUMBER

VARCHAR2

Length

6

20

30

10

9,2

2

 

  • Create table Employee1 as per the above Table Instance Chart but now use table level primary key addition method.

  • Create table Employee2 as per the above Table Instance Chart without any constraint while table creation.

  • Add a PRIMARY KEY constraint to the table Employee2 using the EmpID column.

  • Add a FOREIGN KEY reference on the Employee2 table that will ensure that employee records with non-existent departments are to be prohibited.

  • Confirm that constraints were added by querying Constraint_Name and Constraint from USER_CONSTRAINTS relation.

  • Add a NOT NULL constraint to the table Employee2 on column EmpName.

  • Add a CHECK constraint to ensure, at the time of record insertion, that employee records with salary less than 2000 are to be prohibited.

    Disable NOT NULL Constraint on the column EmpName from the table Employee2.

    Drop UNIQUE constraint from the column DeptName in table Department.

  • Create an application to list all the contents of a database table using a data control object in visual basic.

  • Create an Application in Visual basic having Menu Bar, Tool Bar and other controls to View, Add and Modify records present in the Database Tables.


3. Project–05

The following case study is to be used to develop a team project.,

A cable company in Delhi is working since 1998. They have about 2 Lac customers in different zones (North, South, East and West). Company wants to computerize its working, which involves Customer Registration, Customer Billing and Bill Collection on monthly basis.

Develop a Database Handling Software for the company. The software should have option to enter customer data and information of bill collection.
The data entry form should also have option to navigate through the records.

The software should allow to store following information of customer and billing (Normalize this to store data in tabular form)
Customer Name
Customer Address
Customer City
Customer Zone
Customer Pin Code
Customer Phone
Customer Interest (Movies, Games etc.)
Customer Monthly Income
Customer Monthly Installment
Customer Joining Date
Bill Cycle
Bill Collection Date
(Suitable assumptions can be made)

The user interfaces should be designed in visual basic and must be user friendly with correct tab order.

Note :— Similar type of cases can also be encouraged, provided it should include almost every aspect of course undertaken.

4. Viva-Voce
Five questions from topics covered in the curriculum.

Himachal Pradesh Board Best Sellers

In order to keep pace with technological advancement and to cope up with Himachal Pradesh Board examinations, Pearson group has launched Edurite to help students by offering Books and CDs of different courses online.