INFORMATICS PRACTICES
Learning Objective:
1. To understand the application development environment.
2. To gain programming Skills in Visual Basic and Database Creation in ORACLE.
3. To design, program and develop database application using Visual Basic and ORACLE.
4. To learn database connectivity using Visual Basic as Front-end tool.
Competencies:
1. Student will become familiar with Application Development.
2. Student will be able to develop & debug programs independently.
3. Student can use SQL for storing and retrieving data from the RDBMS.
4. Ability to arrive at a normalized design of tables and other database objects in RDBMS. Also student will acquire programming skills in PL/SQL.
5. Student will be able to develop a Client Server Application using Visual Basic as Front end and oracle as Back end.
INFORMATICS PRACTICES
Commerce Group
Time : 3 Hours CLASS : XI M.M. : 100
---------------------------------------------------------------------------------------------------------------------------------------------------------
S.N. Unit Marks
---------------------------------------------------------------------------------------------------------------------------------------------------------
1. COMPUTER SYSTEM AND BUSINESS APPLICATIONS 15
2. INTRODUCTION TO PROGRAMMING 30
3. RELATIONAL DATABASE MANAGEMENT SYSTEM 30
---------------------------------------------------------------------------------------------------------------------------------------------------------
Unit1. COMPUTER SYSTEM AND BUSINESS APPLICATIONS
Evolution of computers; Basics of computer and its operation: Functional Components and their interconnections, 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, Mike, MICR, OCR, Scanner, Smart Card reader, Barcode 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 tools;
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 languages - Visual Baisc, 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.
Microsoft 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/Logoff; Customization of Taskbar, Start menu, Display properties (Wallpaper, Font Settings, Color Settings, Screen Savers);
Program Menu: Accessories - Calculator, Notepad, MS Paint, Word pad, Entertainments (CD Player, Sound Recorder, Windows Media Player, Volume Controller); Window Explorer and Internet Explorer;
Control Panel: Add new hardware; Add new Software, Printer Installation, Date/Time, Mouse and Regional Settings;
Documentation using MS Word
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, color of text; Inserting symbol; Formatting: Alignment-Left, Right, Center; Justification; Industries and Business Computing: Types of Industries (Production, Shipping, Travel, Hotel, Insurance, Construction, Automobile), Application 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, ActiveX DLL, Active X EXE, Active X Control, ActiveX Document DLL, ActiveX 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 Scrollbar, Vertical Scrollbar, Timer, Drive List Box, Directory List Box, File List box, Shape, Line, Image, Data OLE; Object, Naming Conventions, Event Procedures.
Programming Fundamentals
Data Type: Integer, Long, Single, Double, Currency, String, Byte, Boolean, Date, 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);
Menu Editor: Concept of Menus, Shortcut menus and Popup menus Designing Menu System, Menu Editor Dialog Box Options (Name, Index, Shortcut, HelpContextID, NegotiablePosition, Checked, Enabled, Visible, WindowList, Right Arrow, Left Arrow, Up Arrow, Down Arrow, Menu List, Next, Insert, Delete, OK, Cancel), To Create Menu Controls in theMenu Editor, Menu Naming Conventions, Setting the Name Property, Creating a MenuControl Array, Creating Sub Menus, Separating Menu Controls, Assigning Access Keys and Shortcut Keys, Controlling Menus at Runtime-Enabling and Disabling
Menu Commands, Displaying a Checkmark on a Menu Control, Making a Menu Control Invisible, Adding Menu Control at Runtime, 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 Date 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 : Oracle
ORACLE : Introduction, Version, Two Tier and Three Tier support; 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 Operator (||), 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 Operator , 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, Date type Conversion Functions, Implicit and Explicit Conversion, TO_CHAR Function with Dates, TO_CHAR Functions 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, Nested Gouping 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 Function,
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 toSolve 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: Concpet of Join, Result of Join, Cartesian Product and Generating Cartesian Product example using Mathematical Set), Types of Join (EQUI, SELF, NON-EQUI, OUTER) (LEFT and RIGHT), Equi-join:
Retrieving Records with Equi-join, Additional Search Condition using. AND operator, Short Naming Convention for Tables (Table Aliases), Non-Equi join and its Implementation, Outer-Join and Its Usage, Self-Join (Joining a table to Itself); Manipulating Data of a Table
Relation: Concept of DML (Data Manipulation Language), INSERT Statement, Inserting New Rows with Null Values, Inserting Data 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 Object: View, Table, Sequence, Index, and Synonyms, DDL (Data Definition Language), Naming Convention, Creating Views, Creating Synonysms, Simple Views and Complex Views, Retrieving Data from a View, Quering a View, Modifying a View, Including Constraints: Constrainsts, Concept of using Constraints, Constraint Guidelines, Defining Constraints, NOT NULL, UNIQUE KEY, PRIMARY KEY, FOREIGN KEY, FOREIGH 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, Quering the Database Dictionary to view all tables in the Oracle Database, Creating a Table by Using a Sub-query; Managing Existing Tables and other Databse Object: 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, Dropping Tables; giving permission to other users to work on Created Tables and Revoking it (GRANT and REVOKE statement).
Class XI (Practical)
Duration : 3 Hours Marks: 25
1. Hands of Experience 10
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 table in the databse.
- For data connectivity (Activex Database Control)
- Change of Text box Control Properties to view Databse fields.
2. Practical File 05
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 "Kamla Kant" in the text box and clicks OK button, a message "Hello Kant should be displayed and if he clicks on Cancel button a messages as "Bye Kamal Kant" should appear.
2. Create an Application having two Text Boxes on the Window. Get Title, First Name and Last Name in it. On clicking OK button a message should appear by joining Title+FirstName+Last Name e.g. if user Prof. in Title, Rajyash in First Name, and Swami as Last Name then the message to be printed should be "Happy Deepawli Prof. Rajyash Swami".
3. Creating an applicatin 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 Lose".
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 expose Calculator (+, -, *, รท)
7. Sale Assignment (based on Demo Table present in the ORACLE databse for example Emp table, Dept table and Sal Grade table):
* Display all the records (all columns) from table Emp.
* Display Emp.No and Emp Name of all employees from the 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 name, salary and department number who are not getting commission from table Emp.
* Write a Query to display employee Number, name, sal and sal*12 as Annual Salary whose commiossion is not NULL from table Emp.
* Write a Query to display employee name and salary of those employee who don't have their salary in the range of 1500 to 2000.
* Write a Query to display name, job, salary, and HireDate of employees who are hired between February 20, 1981, and May 1, 1981. Order the query in ascending order of HireDate.
* Write a Query to display the name and hire date 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 contain "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. Lable 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 HireDate.
* 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 Hire Date and Today.
* Create a query that produces display in the following format.
<employee name> Earn $<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 there name strings.
* 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 base 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 table 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 a given employee.
* Write a Query to display employee name and HireDate of employees who are employed after Employee 'BLAKE'.
* Write a Query to display employee number, name and manager's name with thier 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 quey 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 'ALEN'
* Write a query to display employee name and salary of those who either work in department 10 of have salary greater than employee 7521.
Before the following excrcise please ensure that you are provided with a table Employee with following description.
Table : Employee
| Name of Column | Type |
| ID | NUMBER(4) |
| First_Name | VARCHAR2(30) |
| Last_Name | VARCHAR2(30) |
| User_ID | VARCHAR2(10) |
| Salary | NUMBER(9,2) |
* Use DESCRIBE command to ensure the table structute.
* Add the following data in the above Table as instruct
| 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 record with First_Name and Salary.
* Make the changes permanent.
* Modify the Last_Name, of the employee 3, to Gautam.
* Modifiy the Salary and increase it by 1000, for all who get salary less then 5000.
* Delete the employe record having First_Name as Siddharth.
* Make the changes permanent.
* Remove the entire contents of the table.
* Undo the above step.
* Create a table Employeel with columns ID, First_Name and Dept_ID from the table Employee and also confirm the existence of table Employeel.
* Create a view VU_Empl which should include column EmpNo. EName and DeptNo from the table Emp.
* Create a view VU_Emp2 which should include column Emp No, EName and DeptNo from the table Emp and change the column headings as EmpNumber, 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 Nama | ID | Name |
| Data Type | NUMBER | VARCHAR2 |
| 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 | Firs_Name | Last_Name | Dept_ID |
| Date Type | NUMBER | VARCHAR2 | VARCHAR2 | NUMBER |
| Length | 8 | 25 | 25 | 8 |
* Rename table Employee1 to Employee 2.
* Drop table Employee 2.
* Drop table Employee and Department.
* 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/Uinque | ||||||
| Fk Table | ||||||
| Fk Column | ||||||
| Data type | NUMBER | VARCHAR2 | VARCHAR2 | VARCHAR2 | NUMBER | VARCHAR2 |
| Length | 7 | 30 | 20 | 30 | 6 | 10 |
* Add one column Email of data type VARCHAR2 size 30 to the table Customer.
* Change the data type of column pincode to VARCHAR2 (10) in the table Customer.
* Add one more column Customer Income Group of datatype VARCHAR2 (10).
* Insert few record with relevant information, in the table.
* Drop the column Customer Income Group from table Customer.
* Create table Department as per following Table Instnace Chart.
| Column Name | Dept ID | Dept Name | Dept Location |
| Key Type | Primary | ||
| Nulls/Unique | NOTNULL | ||
| Fk Table | |||
| Fk Column | |||
| Data type | NUMBER | VARCHAR 2 | VARCHAR 2 |
| Length | 2 | 20 | 20 |
* Create table Employee as per following Table Instance Chart.
| Column Name | EmpID | EmpName | AmpAddress | EmpPhone | EmpSal | DeptID |
| Key Type | Primary | Foreign | ||||
| Nulls/Unique | NOT NULL | |||||
| Fk Table | Department | |||||
| Fk Column | Dept_ID | |||||
| 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 Employee 2 as per the above Table Instance Chart without any constraint while table creation.
* Add a PRIMARY KEY constraint to the table Employee 2 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 quering Constraint_Name and Constraint from USER_CONSTRAINTS relation.
* Add a NOT NULL constraint to the table Employee 2 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 NOTNULL Constraint on the column EmpName form the table Employee.
* Drop UNIQUE constraint from the column DeptName in table Department.
8. Create an application to list all the contents of a database table using a data control object in visual basic.
9. 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 2 Lac customers in different zones (North, South, East, and West) . Company wants to computerise 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 from 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 Installment
Customer Joining Date
Customer Bill Cycle
Customer 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 05
Five questions from topics covered in the curriculum.