THEORY

SONA MODERN PUBLIC SCHOOL

HOLIDAYS HOME WORK

CLASS – XII

SUBJECT – INFORMATICS PRACTICES (065)

[*Complete the THEORY questions in Notes Copy]

Chapter – 1 Networking & Communication

SMPS  is an educational organization.  It is planning to setup its India campus at Chennai with its head office at Delhi.  The Chennai campus has 4 buildings – Admin, Engineering, Business and Media.  You as a network expert have to suggest the best network related solutions for their problems raised in (i) to (xv), keeping in mind the distance between the buildings and other given parameters.

Shortest distances between various buildings:
ADMIN to ENGINEERING 55m
ADMIN to BUSINESS 90m
ADMIN to MEDIA 50m
ENGINEERING to BUSINESS 55m
ENGINEERING to MEDIA 50m
BUSINESS to MEDIA 45m
DELHI Head Office to CHENNAI Campus 2175km
Various Buildings No. of Computers
ADMIN             110
ENGINEERING 75
BUSINESS 40
MEDIA 12
DELHI Head Office 20

(i) Suggest the most appropriate location of the server inside the Chennai campus (out of 4 buildings), to get the best connectivity for maximum number of computers.  Justify your answer.

(ii) Suggest & draw the cable layout to efficiently connect various buildings within the Chennai campus for connecting the computers.

(iii) The organization is planning to link its branch office situated in the city in a hilly region where cable connection is not feasible, suggest an economic way to connect it with reasonably high speed.

(iv) Suggest the placement of the following devices with justification :

   (a) Hub / Switch

   (b) Repeater

(v) Mention the economic technology to provide internet accessibility to all buildings.

(vi) Suggest the most suitable building to install the modem / internet connecting device, so that all the four building could easily access the internet.

(vii) Suggest the type of network (out of LAN, MAN, WAN) for connecting each of the following buildings :

   (a) Head office and Admin building

   (b) Admin building and Media building

(viii) Name the type of network formed while connecting two mobile phones using bluetooth

(ix) Which of the following communication media, will you suggest to be procured by the organization for connecting their Chennai buildings with Delhi Head office for very high speed communication:

   (a) Ethernet cable  (b) Optical fibre  (c)  Telephone cable

(x) Which wireless media, will you suggest to be procured by the organization for connecting their Chennai buildings with Delhi Head office for very high speed communication:

(xi) Which wireless channel should be opted by the SMPS to connect to students of all over the world.

(xii) Which device you will suggest to be placed / installed in each of these buildings to efficiently connect all the computers within these organization.

(xiii) The organization is planning to connect its Branch office in the closest big city, which is more than 250km from Chennai campus, which type of network out of LAN, MAN, WAN will be formed? Justify your answer.

(xiv) Which hardware device will you suggest to be procured by the organization to be installed to protect and control the internet uses within the Chennai campus.

(xv) Suggest topology/cable or wire layout for connecting all the four buildings of Chennai campus, according to following criteria:

    (a) Number of computers (Server)

    (b) Distance between the buildings (Shortest distance)

Chapter – 2  Open Source Software

Q 1       Explain the following terms:

            (i) Free Software          (ii) Open Source Software         (iii) Freeware    (iv) Shareware

            (v) ASCII                     (vi) ISCII                                  (vii) UNICODE (viii) Proprietary Software

            (ix) True Type Font      (x) Open Type Font                  (xi) Phonetic Text Entry

(xii) Keymap Based Text Entry             (xii) Name any four Indian Scripts of scripting languages

Chapter – 13  MYSQL

Q 1.        Consider the following tables DOCTOR and SALARY. Write SQL commands for the statements (i) to (viii) and give outputs for SQL queries (ix) to (xii)                                                                                                             

        Table: DOCTOR

ID NAME DEPT SEX EXPERIENCE
101 JOHN ENT M 12
104 SMITH ORHTOPEDIC M 5
107 GEORGE CARDIOLOGY M 10
114 LARA SKIN F 3
109 SUNNY MEDICINE F 9
105 JOHNSON ORTHOPEDIC M 10
117 LUCY ENT F 3
111 BILL MEDICINE F 12
130 MORPHY ORTHOPEDIC M 15

        Table: SALARY

ID BASIC ALLOWANCE CONSULTATION
101 12000 1000 300
104 23000 2300 500
107 32000 4000 0
114 12000 5200 100
109 42000 1700 200
125 18900 1690  
135 21700 2600 300

Write the queries for the following

        (i)    Display NAME of all doctors who are in ‘MEDICINE’ having more than 10 years of experience.

(ii)   Display average salary of all doctors working in ‘ENT’ department using the tables DOCTOR and    SALARY. (where SALARY = BASIC + ALLOWANCE )

  •   Display DEPT and total experience of each DEPT for all male doctors.
  •   Display highest CONSULTATON fee among female doctors.
  •   Display ID and NAME of all female doctors in ascending order of ID.
  •   Display NAME of doctors whose CONSULTATION = NULL.
  •   Increase BASIC of all doctors of ENT department with 10%.
  • Display details of all doctors whose NAME starts with alphabet ‘J’.

Write the output for the following queries

  • SELECT COUNT(*) FROM DOCTOR WHERE SEX=’F’;
  • SELECT DEPT, MIN(EXPERIENCE), MAX(EXPERIENCE) FROM DOCTOR

GROUP BY  DEPT;

  • SELECT AVG(CONSULTATION) FROM SALARY;
  • SELECT NAME, DEPT, BASIC FROM DOCTOR  D, SALARY  S     

                                WHERE D.ID = S.ID AND SEX=’M’;

Q 2. Consider the following tables GAMES and PLAYER. Write SQL commands for the statements (i) to (viii) and give outputs for SQL queries (ix) to (xii)             

        Table: GAMES

GCode GameName Number PrizeMoney ScheduleDate
101 Carom Board 2 5000 2004-01-23
102 Badminton 2 12000 2003-12-12
103 Table Tennis 4 8000 2004-02-14
105 Chess 2 9000 2004-01-02
108 Lawn Tennis 4 25000 2004-03-19

Table: PLAYER

PCode Name Gcode
1 Nabi Ahmad 101
2 RaviSahai 108
3 Jatin 101
4 Nazneen 103

Write the queries for the following

  •    To increase the PrizeMoney of all games by 10% whose PrizeMoney is less than 10000.

(ii)      To display details of those games which are having PrizeMoney is in the range of 2000 to 5000 in    ascending order of GCodes.

(iii) To display GCode, GameNamefrom GAMES table and Name from PLAYER table with their matching GCode.

  •        To delete rows from GAMES table whose ScheduleDate = NULL.
  •        To display GCode, minimum and maximum PrizeMoney of each GCode.
  •        To add a new column as GameType Varchar(8).
  •        To display details of all GAMES in descending order of GCode.
  • To add a new row into table PLAYER as (1, Ahmed, 104).

Write the output for the following queries

  • SELECT COUNT(DISTINCT (Number)) FROM GAMES;
  • SELECT MAX(ScheduleDate),MIN(ScheduleDate) FROM GAMES;
  • SELECT SUM(PrizeMoney) FROM GAMES;
  • SELECT  DISTINCT  GameName  from  GAMES;

Chapter – 14  Database Transaction

Q 2 Write TCL statements for the following :                                                                                                                                                                      

Start  MYSQL  session;

Create a table named Student with columns ROLL, NAME and  MARKS.

Start a transaction and insert two rows to the Student table.

Verify the inserts by SELECT statement.

Commit the changes.

Start another transaction.

Delete a row that was recently inserted.

Verify that the row has been deleted.

Rollback  the changes.

Verify that the delete has been cancelled.

Board Questions from Chapter 1 & 2 (Year 2019)

Q 1

  • ABC bank has many computers connected  to a building.  What type of  network is formed?  Name two hardware resources that can be shared by the computers connected in the bank network.
  • Write one example of situation when you would use a Remote Access Software such as Team Viewer
  • Distinguish between ASCII and UNICODE.  Write two points.
  • What are Cookies on a computer?
  • Explain in brief any two Freedoms offered by open  source  software.

Board Questions from Chapter 1 & 2 (Year 2018)

Q 1

  • ABC company wants to link its computers in the Head Office in New Delhi to its office in Sydney.  Name the type of network that will be formed.  Which Communication media should be used to form this Network?
  • Which of the following is / are not communication media?

(i) Microwaves   (ii)  Optical Fiber cable  (iii)  Node  (iv)  Radio waves

Identify which of the above mentioned communication media are wired and which ones are wireless media.

  •  Write two examples each of the software in the following categories:
  • Open Source Operating System       (ii)        Open Source Web Browser
  • Expand the following terms:
  • GSM          (ii)        I P
  •  Name the devices:
  • This device constantly looks at all the data entering and exiting your connection.  It can block or reject data in response to an established rule.
  • This device connects multiple nodes to form a network.  It redirects the received information only to the intended node(s).   
PRACTICAL

 

[*Complete the PRACTICAL questions on computer and save the Coding (on even page no’s) and Output (on odd page no’s) in a Microsoft Word file with your name and mail the softcopy to my mail id :-  [email protected] ]

(1)        Write the SQL queries for the following table:

Table : CARDEN

CCODE CARNAME MAKE COLOR CAPACITY CHARGES
501 A-STAR SUZUKI RED 3 14
503 INDIGO TATA SILVER 3 12
502 INNOVA TOYOTA WHITE 7 15
509 SX4 SUZUKI SILVER 4 14
510 C-CLASS MERCEDES RED 4 35
  • To create the above table.
  • To display the names of all the silver colored cars.
  • To display name of car, make and capacity of cars in descending order of sitting capacity.
  • To display the make and highest charges at which a vehicle can be hired of each make.
  • To delete the table carden permanently from database.
  • To increase the charges of all cars by 10.
  • To add a new column as :-  PRICE DECIMAL (5,2)
  • To add a new row as :- (515, WAGONR, SUZUKI, MAROON, 4, 13)
  • To display details of all cars whose make starts with alphabet ‘S’.
  • To display details of all red coloured cars whose capacity is greater than 2.

(2)        Write the SQL queries for the following table:

Table : ITEMS

CODE INAME QTY PRICE COMPANY TCODE
1001 DIGITAL PAD 12I 120 11000 XENITA T01
1006 LED SCREEN 40 70 38000 SANTORA T02
1004 CAR GPS SYSTEM 50 21500 GEOKNOW T01
1003 DIGITAL CAMERA 160 8000 DIGICLICK T02
1005 PEN DRIVE 32 GB 600 1200 STOREHOME T03
  • To create the above table.
  • To display the details of all the items in ascending order of item name(i.e. INAME).
  • To display item name and price of all those items, whose price is in the range of 10000 and 22000.
  • To display TCODE and number of items, which are traded by each TCODE.
  • To display the price, item name and quantity of those items which have quantity more than 150.
  • To display details of all items whose iname contains ‘I’ at any place.
  • To increase price of all items by 10%.
  • To add a new column as :- MODEL  CHAR (10).
  • To add a new row as :- (1007, LAPTOP, 25, 25000, DELL, T05).
  • To display details of all items having qty more than 100.

(3)        Write the SQL queries for the following table:

Table: STORE

ltemNo Item Scode Qty Rate LastBuy
2005 Sharpener Classic 23 60 8 2009-06-30
2003 Ball Pen 22 50 25 2010-02-10
2002 Gel Pen Premium 21 150 12 2010-02-24
2006 Gel Pen Classic 21 250 20 2009-03-11
2001 Eraser Small 22 220 6 2009-01-19
2004 Eraser Big 22 110 8 2009-12-02
2009 Ball Pen 0.5 21 180 18 2009-11-03
  • To create the above table.
  • To display details of all the items in the Store table in ascending order of LastBuy.
  •   To display ItemNo and Item name of those items from Store table whose Rate is more than 15.
  •  To display the details of those items whose Qty is more than 110.
  • To display minimum Rate of items for each Supplier individually as per Scode from table Store.
  •  To increase Rate of items by 15% for Scode 22.
  •  To add a new column as :- MAKE CHAR (12).
  •  To add a new row as :- (2010, Fountain Pen, 24, 100, 15, 2009-12-10).
  •  To delete rows of item for Scode 22.
  • To display details of item having ‘Pen’ in item name.

(4)        Write the SQL queries for the following tables:

Table : LAB

No. ItemName Cost QuantityPerItem DateOfPurchase Warranty Operational
1 Computer 60000 9 1996-05-21 2 7
2 Printer 15000 3 1997-05-25 4 2
3 Scanner 13900 1 1998-08-29 3 1
4 Camera 21901 2 1996-06-13 1 2
5 Hub 8000 1 1999-10-14 2 1
6 UPS 5000 5 1996-05-27 1 4
7 Plotter 25000 2 2000-01-11 2 2
  • To create the above above table.
  • To display the Itemname purchased after 1995-02-10.
  • To delete the details of item whose Warranty period less than 2.
  •  To display  ItemName  which are within the Warranty period between 2 to 4 (both inclusive).
  • To display  ItemName in ascending order of the date of purchase where quantity is more than 3.
  •   To add a new row as :- (8, ‘VCR’, 10000, 2, ‘2000-02-20’, 1, 2).
  •    To add a new column as :- MAKE  CHAR (15).
  •    To decrease the Cost of items by 14%.
  •    To display details of item whose ItemName has ‘r’ as last alphabet.
  •    To delete the table permanently.

Write the JAVA coding for the following GUI designed in NetBeans.