Code 402 class 10 Open Office

Setting the Primary Key We know that primary key uniquely identifies a record on a table. If none of the fields (alone or combined) qualify for primary key then you can provide an additional field to do so. Here, such primary key field id ITEM_CODE. To set a field as Primary Key, in the Design View, right click on the left hand side gray area showing a green arrow beside the desired field and select Primary Key option from the context menu. Notice that an icon of a key is displayed beside the primary key field now where you had right clicked. Note: If you try saving the table without setting any primary key, database will display a prompt warning you of the same and lets you decide what action you would like to take further. Editing Table Design After saving the table, if you need to make any more Note: changes in the design of the table, right click on the table name in the list of tables and select Edit option When you close the table Data View, records from the context menu or you can select the table entered so far are saved automatically and click Edit… option in Edit menu. however you can press Ctrl+S or select Save Current Record option in File menu Table Data View to save the currently entered record. Once the table is created, it is ready to store the desired data. Double click on the name of the desired table. It will open up in Data View which allows you to key in the data on the table. Data View displays table contents in the form of rows of records. Each record stores the data in the corresponding field which are displayed as named columns. While entering the data, be careful in entering the values as they are, avoid typing any leading or trailing spaces unless they are really there in values. Database will consider any extra spaces and letters as part of the value and that might not give you desired results when you try to find records through a query. Item table Data View is shown here. 5150 Database Management System

LAB EXERCISE Create database FLORAL GARMENTS and create table Item. Then, create table Sales as shown here. TRANSACTION_ID is primary key in Sales table. Create table using Table Wizard 5151 Following are the steps to create table using Table Wizard: 1. Click on Table object > Select Use Wizard to Create Table in Tasks pane. Database Management System

2. In the Table Wizard, select a sample table and add desired fields. Click Next button. 3. Specify desired field properties. E.g. EventID is Integer type and Auto Increment value. User needs not to enter the values for it. Click Next button. 4. Specify for automatic primary key field or select a field to be a Primary key. You may uncheck the checkbox to skip creating any primary key. Click Next button. 5152 5. Specify a relevant, unique name to the table. Click Finish button. Database Management System

After the table is created, you can right click on its name and select Edit option to open it in Design View. Here, select the field BeginTime and click on the browse (...) button of the property Format example. In the Field Format dialog box, select Category Time and select desired time format. Click OK. This way, you can change the formats of other fields too such as StartDate, EndDate and EndTime etc. LAB EXERCISE Open database Library created earlier and create table Book with following fields: Field Name Field Type Size Decimal Places Other details BookID Number 4 None Primary Key Title Varchar 100 None Author Varchar 100 None Publisher Varchar 100 None Price Decimal 4 2 Copies Number 3 Creating table using SQL Command Besides design view and wizard, there is another powerful way of creating table which is called SQL (pronounce “sequel”) – Structured Query Language. An SQL statement is a simple English like set of commands and clauses which help in creating databasee objects, querying desired data from the database, deleting data, updating data and carrying out many other tasks related with data management and user management. You will learn about SQL in more detail in coming sessions. SQL statement Create is used to create table by mentioning table keyword. It is used to define the table name, field names along with their types and other constraints. The syntax of Create Table statement is given here: Database Management System 5153

CREATE TABLE <table name> ( <field name> <data type> <constraint>, <field name> <data type> <constraint>, <field name> <data type> <constraint>, <field name> <data type> <constraint> ); To open SQL Command window, go to Tool menu > SQL option. Type the command and click on Execute button. Any errors in the statement will be reported in the Status textbox otherwise the result of the statement will be displayed in the Output text box. CREATE TABLE “Invitation” ( InvitationID integer primary key, EventID integer not null, Guest_Name Varchar(100) not null, Donation decimal default 1000.00 ) Here, table Invitation has 4 fields. InvitationID will store unique numbers since it is declared as primary key field. EventID will store numbers and not null means that it cannot be empty. Same is for Guest_Name which stores strings of variable character length maximum 100. Donation will store currency value or decimal value. If data entry in it is skipped then by default it will store 1000.00. This is ensured by using default constraint. What is a constraint? A constraint is a way to decide the behaviour of the fields in different ways. For example, a field which should behave like a primary key is declared so by using primary key constraint. Constraints are also applied to check that a valid data value is stored in a field. This way, for various purposes, constraints can be applied on the fields to ensure that they function in a particular way or they store valid data as specified. Notice in the above example how the field InvitationID is declared primary key, how the fields EventID and Guest_Name are declared not to be blank (not null) and how the field Donation must store the value 1000.00 if user skips to enter any value in it (default value 1000.00). 5154 Database Management System

Some common examples of field definitions with constraints in SQL GuestID int GENERATED BY DEFAULT AS This is an auto-increment field with IDENTITY unique values. Refund BOOLEAN default ‘Yes’ This will store either Yes (or 1) or, No (or 0). Create table Invitation (EventID integer foreign key EventID of table Invitation points to (EventID) references \"School_Events\"(\"EventID\") the primary key field EventID of table School_Events. Create table Voter(age int, check(age >=18)) Check constraint validating applies if age of the voter is 18 or more. Employee_age int Check(Employee_age between 18 Checking Employee_age is in the and 65) range of 18 and 65. age int not null, unique(age)) Unique constraint is applied on age to suppress duplicate values in it. Examples of modifying table definition (ALTER TABLE Command) Sometimes requirements change after creating the table. The table definition can be changed such as adding/removing a field or applying/removing a constraint. This is called altering the table. Adding new fields: Syntax : Alter table “table name” add “new field” “type” before “existing field name” E.g., alter table item add item_type varchar(50) before “price”. Here, a new field item_type is added to the table item before the field price Dropping existing fields: Syntax : Alter table “table name” drop “existing field name”. E.g., alter table item drop sale_value Renaming fields: Syntax: Alter table “table name” alter column “field name” rename to “new field name” E.g., alter table item alter column “qty” rename to “QOH” Setting default value: Example: alter table item alter column qty set default 100 Setting NULL/NOT NULL: Example: alter table item alter column price set not null Adding constraint: Syntax: Alter table “table name” add [CONSTRAINT “constraint name”] constraint definition Here, giving constraint name is optional. Constraint name is useful in dropping the constraints. E.g., alter table item constraint “chk_qty” add check(qty > 0) Dropping constraint: Syntax: Alter table “table name” drop CONSTRAINT “constraint name” E.g., alter table item drop constraint “chk_qty” Renaming table: Example: alter table “item” rename to “item_master” Dropping table: Example: drop table item Database Management System 5155

LAB EXERCISE A. Delete the table Book created earlier and create it by giving SQL command. Then, fill the following data in it as shown here. BookID Title Author Publisher Price Copies ------------------------------------------------------------------------------------------------------------------------------ 100 Eye of the Needle Ken Follet Rupa 545 5 101 The Firm John Grisham DigiMedia 399 2 102 The Third Twin Ken Follet Rupa 400 2 103 Clear and Present Danger Tom Clancy Rupa 760 1 104 The Innocent Man John Grisham DigiMedia 324 3 105 The Hunt for Red October Tom Clancy DigiMedia 575 2 Apply following constraints: ¤ Book ID is primary key. ¤ Title should be unique. ¤ Title, author and publisher cannot be blank. ¤ Price should not be 0. ¤ Copies should be by default 2. B. Create a table Employee to store Employee code (PK), Employee Name, Date of birth, date of joining, basic salary, gender and city of posting. ¤ Add a field Contact_Number Varchar(15). ¤ Rename the fields for Employee code and employee name to emp_code and emp_name. ¤ Add a constraint to basic salary to check that it should be at least 1000. ¤ Set default date of joining as today’s date. ¤ Rename the table to Employee_Master. Edit, delete, sort data in table For editing data in the table, open it in Data view and insert data as shown here. 5156 Database Management System

To sort the data on the basis of any field, select the field and click on Sort Ascending or Sort Descending buttons on Table Data toolbar. Note that data is sorted in ascending order of BeginDate field values. To delete a record, simply select it and select Delete Record option in Edit menu. Database Management System 5157

Session-6 Relationships and Referential Integrity Real life databases store the data in more than one table for easier management of data. The tables are related on the basis of primary key fields and foreign keys to retrieve the data from them. Managing data in multiple tables raises the need of maintaining accurate and consistent data. For example, if a business deals in selling 5 items then in sales table no record for a non-existent item should be accepted or if a record in parent table is deleted then the related records in child tables should also be deleted. This concept is called referential integrity. Referential Integrity Referential integrity is the concept to ensure that accurate and consistent data is maintained in the database tables which are related. In Base, referential integrity addresses following 3 aspects: a. Value entered in foreign key in child table must match a primary key value in the parent table. b. Any update in the key of the parent table will automatically update the matching values in child table. This is called Update cascade. c. Any delete operation on a record in the parent table will automatically delete the matching records in child table. This is called Delete cascade. Types of Relationship Relationships between the two tables can be of 3 types: 1. One to many 2. One to one 3. Many to many One to Many Relationship: This means one record in one table has many matching records in the other related table. One is denoted by number 1 and Many is denoted by infinite (∞) sign. For example, an author can write many books. Author 1 writes ∞ Book One to One Relationship: This means one record in one table has exactly one matching record in the other related table. For example, in a school library, one book can be issued to a student at a time. Book 1 can be 1 Student issued to Many to Many Relationship: This means multiple records in one table have many matching records in the other related table. For example, in a college, many students are pursuing many courses. Student ∞ can ∞ Course pursue 5158 Database Management System

Steps to Set up Relationship Between the Tables 1. In Tools menu, click Relationships option. 2. In the Add Tables popup, select the tables and click Add button one-by-one to add desired tables to Relationship Design View. 3. In the table structures, drag desired matching field from one table and drop it to the matching field of the other. The relationship is established and denoted by a thread. 4. Right click on the relationship 5159 thread and select Delete to remove the relationship or Edit to make further changes in the relationship. Database Management System

5. In Relations popup, specify desired update and delete options and click OK. Here, Update cascade and Delete cascade are selected. Let us check the relationship. Below is master table Item. Below, in the table Sales, Item_Code J015 does not exist in related table Item, so an Integrity constraint violation error is displayed indicating J015 is not found in parent table. LAB EXERCISE Create a master table Furniture to store FurnitureID, Furniture Name and Furniture Price. Create another table Sales table to store FurnitureID, SaleDate and SaleQuantity. Relate the two tables so that for any update and delete in table Furniture, update cascade and Delete cascade occur in Sales table. Test the relationship by entering sample data and performing updates and deletions. Session-7 Create and Manage Queries Basically, queries are used to access conditional data from tables. It is very difficult to extract data from the table without a query. Using query, we can request for data of our choice. It is also used to answer questions to perform calculations, combine data from different tables, or to add, change or delete table data. 5160 Database Management System

Create query using Query Wizard To create query using Query Wizard, following are the steps by example. 1. Select Query object and click on Use Wizard to Create Query option in Tasks pane. 2. Select the desired table in first step of Query Wizard and add desired fields in the query. Click Next button. 3. Select the fields on 5161 which you may want to get the data sorted. For example, sort ascending on item type and sort descending on item price. Click Next button. Database Management System

5. Specify if you need to display 4. Specify the search conditions all the records filtered out by such as price smaller than query or only the summary of 1000. the data. Summary is created Match all of the following by selecting the aggregate option filters records that function to apply on the satisfy all of the multiple selected field. E.g., average of conditions. price. Match any of the following option filters records that Here, we shall select Detailed satisfy any of the multiple query radio button. conditions. Click Next button. Click Next button. 6. Enter the names of the fields which should be displayed in your query. Here, field names are taken as alias names. Click Next button. 5162 Database Management System

7. Specify a relevant name to the query. Click Finish button. The query will be created by the specified name. You can double click on the name to run the query and display the output of the query. Fo example, the result of the query Item_with_price_1K_or_less is sown here. LAB EXERCISE Create a master table Furniture to store FurnitureID, Furniture Name and Furniture Price. Create another table Sales table to store FurnitureID, SaleDate and SaleQuantity. Create a query to display the names of those furniture items whose quantity more than 100 is sold before January 1. Enter some sample data that matches the criteria. Create query using Query Design View Design View gives better control over designing a query. 1. Click on Queries object and select Create Query in Design View option in Tasks pane and add desired table. Database Management System 5163

2. From the table structure (field list), drag-drop desired fields which you need to show in the query output into the Field section. Alias is display name of the field which can be changed optionally. Specify sort order in the Sort section exactly below the desired field. In Visible section, uncheck the boxes for the fields you do not need to show on the query result. In Criterion section, specify the record filter criteria. Once done, close query design and save the query with a relevant name. Criteria on multiple fields For multiple criteria, mention the desired criteria in Criterion section below desired field. To specify if all the conditions should be true then keep the criteria in the Criterion section. For having any of the conditions to be true, put them in Or sections as shown here. For example, this query design view shows query that should display details of the jackets of sizes ‘L’, ‘XL’ or ‘32' and whose price id 1000 or more. The output is given here. Note: It is possible sometimes that none of the records in the table satisfy any of the criteria given in the query. In this case, query will show zero records or blank output. BETWEEN Operator Between operator is used to specify a range of numeric or date values. For example, for a field named age the range of age from 25 to 60 can be specified as Between 25 and 60. 5164 Database Management System

LAB EXERCISE Create a master table Furniture to store FurnitureID, Furniture Name and Furniture Price. Create another table Sales table to store FurnitureID, SaleDate and SaleQuantity. Create a query to display the names and SaleDate of those furniture items whose quantity between 10 and 50 is sold on Feb 5. Enter some sample data that matches the criteria. Wildcards Wildcards are a shortcut way to specify value patterns that are shared by multiple values in a table. For example, you may need to display the records of all the students whose names begin with letter 'R' or those having letters 'sh' in their names. Or, you may need to list records of those student whose names have exactly 5 letters. To specify wildcard patterns, LIKE operator is used. There are 2 wildcards: * and ?. * : This symbol means any number of occurrences of any letter. ? : This symbol means exactly one letter. As shown here, LIKE 'J????' means values in field ITEM_NAME beginning with letter 'J' followed by exactly 4 letters. This matches with values JEANS. In this example, LIKE '*L*' means values in field ITEM_PRICE having letter anywhere. This matches with the values PARALLEL, WOOLEN and LEATHER. Other example are LIKE 'D*' means values starting with letter D, LIKE ‘*N’ means values ending with letter N. Database Management System 5165

LAB EXERCISE Create a master table Furniture to store FurnitureID, Furniture Name and Furniture Price. Create another table Sales table to store FurnitureID, SaleDate and SaleQuantity. Create a query to display the following: i. All the details of furniture items who have exactly 5 letters in their name. ii. All the details of furniture items whose names contain letter ‘A’. iii. All the details of furniture items whose names begin with letter ‘T’ and letter ‘E’. iv. All the details of furniture items whose names either contain letter ‘H’ or ‘P’. Perform calculations Queries can store formulas based on the fields to perform calculations. When query executes, the formula is executed and displayed as an additional field but it is not saved in the table. Such fields are called calculated fields. To perform calculations in a query, in Design View, these are the steps: In the Field section, specify the formula. Enter an Alias name for the formula. For example, if table Item has field PRICE and table Sales has field QTY_SOLD then the sale_value can be calculated as PRICE * QTY_SOLD in Field section. Another example is to calculate discount of 10% on PRICE as PRICE * 0.10. Let us calculate sale_value as Item.PRICE * Sales.QTY_SOLD. .One more scenario could be where we wish to know how many days before an item was sold. To calculate differences between dates, DateDiff() is used. It takes 3 arguments: First argument is a defined value for taking difference on the basis of year, month or day. Smaller or earlier date. Larger or later date. For example, DATEDIFF(‘DAY’, SALE_DATE, TODAY) takes difference of ‘days’ between current system date and sale_date values. Note that in Alias section, the name of the calculated field is mentioned as DAYS_SOLD_BEFORE. If we assume that current system date is October 30, 2022 then the output of the query is given here. 5166 Database Management System

Difference of days taken on 30/10/22 between TODAY and SALE_DATE displayed as alias DAYS_SOLD_BEFORE LAB EXERCISE Create a master table Furniture to store FurnitureID, Furniture Name and Furniture Price. Create another table Sales table to store FurnitureID, SaleDate and SaleQuantity. Create a query to calculate and display Sale Amount by multiplying SaleQuantity with Furniture Price. [Hint: Precede field name with table name such as Furniture.Price * Sales.SaleQuantity] Grouping data Many times it is required that calculations are done on the basis of a data group. For example, calculating total price of JEANS and JACKETS separately, or calculating average price sale qualityt of all items, or calculating total of sale made in each region, or counting how may employees are there in each department. To do so, in query design, we need to select keyword Group in the Function section of that field which is used for grouping. Then, we select suitable aggregate function in the Function section of that field on which calculation needs to be done. COUNT, SUM, AVERAGE etc. are aggregate functions because they perform calculation on a group of multiple values. In the example given here, garments are grouped on the basis of item names (JACKET, JEANS) and SUM function is applied on the field PRICE. Thus, total prices of jeans and jackets will be displayed. Compare the output with table data. TOTAL_VALUE is the Alias name given to the calculated field. Actual data in the table Item SUM of PRICE (alias TOTAL_VALUE) grouped by ITEM_NAME Database Management System 5167

LAB EXERCISE Create a master table Furniture to store FurnitureID, Furniture Name, Furniture Material and Furniture Price. Furniture Material can be Wood, Plastic or Metal. Create another table Sales table to store FurnitureID, SaleDate and SaleQuantity. i. Create a query to display total furniture price Furniture-Material-wise. ii. Create a query to display total sale amount (price * sale quantity) Furniture-Material- wise. Editing query To edit the query, right click on it and select Edit option. Session-8 Structured Query Language Some RDBMS like MS Access, Base etc. provide graphical interface to design a query while others do not. Besides design view, there is another powerful way of querying for data from the database and that is called SQL (pronounce “sequel”) – Structured Query Language. An SQL statement is a simple English like set of commands and clauses which help in querying desired data from the database. SQL Command: It is a keyword that denotes the action to be performed on the desired table like CREATE (to create database objects like tables), SELECT (to query data), UPDATE (for data modification) and DELETE (to remove data) SQL Clause: It is a keyword that is used along with SQL command to define the scope of the SQL Command and to determine in which order the data should be displayed in output. E.g., where (to filter records on the basis of condition(s), order by (to sort the output) group by etc. Example of SELECT command without any clause: SELECT ITEM_CODE, ITEM_NAME, PRICE from Item; Here, item code, name and price of all the items will be displayed. SELECT * from Item where price > 100; Here, where is a clause that mentions a condition for the records. Creating Query in SQL View Using SELECT Note: The structural syntax of SELECT command is as follows: An SQL statement can be written without Select<comma separated fields list> any line breaks. Here, line breaks are from<comma separated table names> given easier clarity of the concept and where<condition> and/or <condition>.... make the SQL statement easily readable. order by<field name on which output to be sorted><ASC/ DESC> where and order by clauses are optional unless you need them. Select Create query in SQL view option in the tasks of Query object. In SQL View, type: SELECT *from Item;. Press F5 or Run SQL command directly button in SQL toolbar. 5168 Database Management System

The interface with the SQL statement and with the output will look like this: Note: SQL is not case sensitive in typing the commands but field names should be typed in exact the case they have been created in the table. Let us try some more SQL statements: Display selective fields from the table This statement displays, item code, name and price of all the records in the table. Select ITEM_CODE, ITEM_NAME, PRICE from Item; Display selective fields on the basis of a criteria (condition) Let us display item names, size, type and price of those items whose price is more than 800. Select ITEM_NAME, ITEM_SIZE, ITEM_TYPE, PRICE from Item where PRICE > 800; Let us display the size and type of all the jeans in our store. Select ITEM_NAME, ITEM_SIZE, ITEM_TYPE from Item where ITEM_NAME='JEANS' Note: 1. Text values in condition should be enclosed in single quote, not double quotes. 2. Data value are compared with case sensitivity. Here, JEANS will not match with 'jeans' or 'Jeans' etc. Database Management System 5169

Sorting Output Output of the query result can be sorted in ascending or descending order,on the basis of one or more fields. Let us display the output of above SQL statement in the descending order of price. Select ITEM_NAME, ITEM_SIZE, ITEM_TYPE, PRICE from Item where PRICE > 800 order by PRICE Desc Note: Default sort order is ascending (keyword Asc) Performing Calculations on Fields It is a usual practice to not to store those values in the tables which can be calculated. For example, percentage of some amount. The reason behind such practice is that SQL allows creating simple formulas and apply some aggregate functions on the fields to calculate values. Let us understand this with an example. In the table Item, calculations can be performed on the field PRICE. Let us calculate 10% discount on the price for all the items. Select ITEM_NAME, PRICE, PRICE * 0.1 from Item Here, PRICE * 0.1 is the calculation done on the field PRICE. Notice that title of the calculation is showing the formula in the query output. We can give it an alias name by modifying the statement by using as keyword followed by the alias name. Select ITEM_NAME, PRICE, PRICE * 0.1 as DISCOUNT from Item DISCOUNT is just an alias name or calculated field. It does not exist in the table actually. 5170 Database Management System

Following SQL statement displays price after discount along with all item details. Select Item.*, PRICE * 0.1 as DISCOUNT, PRICE - (PRICE * 0.1) as PRICE_AFTER_DISCOUNT from Item Here, notice Item.* in select command. Here, dot (.) is used as an operator to specify * as all the fields in the table named Item. This is necessary here because * is also used as arithmetic operator for multiplication in the formulas. Item.* tells the application that this * is not arithmetic operator. Following SQL statement displays projected price next year for only jackets as 12.5% more. Select Item.*, PRICE + (PRICE * 0.125) as PROJECTED_PRICE from Item where ITEM_NAME = ‘JACKET’ Assuming that a table Marks has fields eng, math and IT which store marks in the corresponding subjects out of 100. Then total marks can be calculated using + operator with field names to be added: Select (eng + math + IT) as total from Marks This way, using arithmetic operators, calculations can be performed on the fields by creating a formula depending on the requirements. Applying Aggregate Functions on the Fields Sometimes we need to perform calculations on a SUM() applied on field PRICE set of values in a field. For example, calculating total price of all the items. This is done by using built-in aggregate functions. They are called aggregate functions because they perform calculations on a set of values at a time. See these examples. Database Management System 5171

Here, SUM() is returning the total of all values in the field PRICE. Aggregate functions have a set of parentheses in which the name of the field is passed. Here, group by clause is used to calculate SUM of PRICE on unique values in the field ITEM_NAME (i.e. JEANS and JACKET). Some common aggregate functions are SUM, AVG, MAX, MIN and COUNT SUM of PRICE grouped on ITEM_NAME Ÿ SUM() returns the total of the set of given values. Ÿ AVG() returns the average of the set of given values. Ÿ MAX() and MIN() return greatest and smallest value of the set of given values respectively. Ÿ COUNT() applies to non-numeric fields also to return their count. Inserting and Modifying Data in the Table Using SQL, you can insert new records in the table, delete and update (modify) the existing records in the table. To modify data in the table, you need SQL Statement Execution tool. To open it, select SQL option in Tools menu. Execute SQL Statement dialog box will popup. Type the statement in Command to Execute box and click on Execute button. Inserting New Records SQL syntax for inserting new records in the table is: Insert into “<table name>” (comma separated list of fields) Values (comma separated list of values) Note: 1. The table name should be in double quotes. 2. The sequence of fields and that of values should be same. 3. Non numeric values need to be enclosed in single quotes. Let us add details of a new woolen jacket in the table Item. Insert into \"Item\" (ITEM_CODE, ITEM_NAME, ITEM_SIZE, ITEM_TYPE, PRICE) values('J012','JACKET','28','WOOLEN',8500); Deleting Records Deleting records is a very sensitive issue and should be dealt with care. Always consider before hitting Execute button if you really need to delete the intended records. Delete statement should generally have some condition associated with it otherwise all the records in the table will be deleted. 5172 Database Management System

The syntax of Delete statement is: Delete from <table name> Where <criteria> Let us delete all the items which have price less than 700. Delete from “Item” where PRICE < 700; Updating Records Updating records is a also very sensitive issue and should be dealt with care. Always consider before hitting Execute button if you really need to update the intended records. Update statement should generally have some condition associated with it otherwise all the records in the table will be updated with the given same value. Update is used to make changes in the data values of existing records. The syntax for update statement is: Update “<table name>” Set “<field_name>” = '<value> or <formula>' Where <criteria> Let us increase the price of STRETCH type jeans by 5%. update \"Item\" set \"PRICE\" = PRICE + (PRICE * 0.05) where \"ITEM_TYPE\"='STRETCH'; LAB EXERCISE A. Table Book is given here. Book ID Title Author Publisher Price Copies ------------------------------------------------------------------------------------------------------------------------------------ 100 Eye of the Needle Ken Follet Rupa 545 5 101 The Firm John Grisham DigiMedia 399 2 102 The Third Twin Ken Follet Rupa 400 2 103 Clear and Present Danger Tom Clancy Rupa 760 1 104 The Innocent Man John Grisham DigiMedia 324 3 105 The Hunt for Red October Tom Clancy DigiMedia 575 2 To work on the table Book issue SQL commands to find out the following: 1. List all the details of all the books. Database Management System 5173

2. Display title, publisher and price of all the books. 3. Display title, author and price of all the books whose price is less than 400. 4. Who are the authors whom 'Rupa' publication has published? 5. What are the prices of the books written by ‘Tom Clancy’? 6. List details of all the books in descending order of Price. 7. Display the names of the authors with book titles in ascending order of Price. 8. Refer to question 4 and display the output sorted on Book Title. 9. Which books and their authors are published by 'DigiMedia'? 10. What are the titles and prices of books whose more than 2 copies are there in library. 11. Who has written which books cheaper than 300. 12. Here, BookID is primary key. Why not any other field is considered as primary key? Explain briefly. B. Change the price of Book number 5 to 430. C. Delete the all the book which are priced higher than 400. D. Deleted the books which have just 1 copy in the library. E. Write the SQL Statement to create the table Tea as described below. Tea_Code A number to identify each tea flavour Tea_Flavour Stores name of the flavour such as Ginger, Lemon, Honey etc. Size Size is Large, Medium, Small Sugar_Type Sugar, Sugar free Price A value with two decimal places In the above table, which field should be the primary key and why? F. Write the SQL Statement to create the table Flight as described below. Sector For example, KOL-DEL, DEL-BAN, CHN-DEL etc. Date Date of flight Day1 3 letter weekday name Day2 3 letter weekday name Day3 3 letter weekday name Capacity number of seats In the above table, which field should be the primary key? Why?/Why not? Suggest a remedy also if why not. Session-9 Building Forms A Form is a database object that can be used to create a user interface for the database. With the help of 'forms', you can enter, view and change data in an easy manner. A form is an interface between the user and the underlying table whose layout is defined by the user for working conveniently with the data stored on the table. A Form is a collection of buttons, text boxes and labels. These are also known as controls. Let us create a Form for the table Item. 5174 Database Management System

Creating Form with Form Wizard A form can be created in two ways - Using Form Design View and Using Form Wizard. Here, we shall learn to use Form Wizard. 1. Click on the category Forms in Database pane and then select Use Wizard to Create Form… option in the Tasks pane. 2. In the Form Wizard dialog box, do the following: a. i.) Select the desired table from the Table or queries drop-down. Using Shift key, select all the fields in the table that you need to add to the form. ii.) T h e n c l i c k o n > button. You can click also on >> button to add all the fields at once in the form. Then, click Next. b) Next step allows you to insert an already existing form in the form being created. We do not need this step here, so simply click Next. c) Select the arrangement of the controls in the Form and click Next. Database Management System 5175

d) Select the data entry mode either to enter only new records and not to display the records already entered in the table or to display all the records. Here, you can also select to disallow modification and deletion of existing data and entry of new data. Let us select to display all data with every action allowed. Then, click Next. e) Select any display style for the Form and click Next. 3. Specify a name for the form. In our example, it can be named as Item_Form. You can decide to use the form as it is created or to format the look of the form in the form editor. Let us select Work with the form option and click Finish. 5176 Database Management System

Navigating Form and Searching Records Once form is opened in the working mode, you can use the Previous/ Next/ First/ Last Record buttons in the Form Navigation toolbar. If it is not visible, select it from Toolbars submenu under View menu. Short cut Next Record: Ctrl + Alt + Right arrow Last Record: Ctrl + Alt + End Previous Record: Ctrl + Alt + Left arrow First Record: Ctrl + Alt + Home To skip to a blank record for entering new data values, click on New Record button on this toolbar. Enter a new record in the table through this form as shown here. Close the form. Closing it will automatically save the changes and newly added records in the underlying table. You can also click on Save record button in the toolbar. Open the table Item and you will find the newly added record in it. LAB EXERCISE A. For table Book created in previous session create: 1. A form named View_Books only to view the records. 2. A form named Add_Books only to add records of new books in the table. Delete and update should not be allowed through this form. 3. A form named Modify_Books only to modify existing records of books in the table. New entry and delete should not be allowed through this form. B. Open these three forms and test if they are working as desired. Delete records using Forms To delete the current record, click on Delete Record button on the Navigation bar of the form. Database Management System 5177

Editing Form Once a form is created using wizard, to edit it, right click on it and select Edit. The form will open in Form Design View. Using design view, a new form can also be created from scratch. Form contains 3 types of controls: Data Controls: These controls are bound with a field in the concerned table and they display the data value of that field. Calculated Controls: These controls store formulas and functions for calculations. They are not bound with any field in the table. Design Controls: These controls are used in giving desired look to the form and to organise controls over the form. For example, line, rectangle, group box etc. To open a form for editing, right click on the name of the form and select Edit option. Understanding Form Properties If you right click on any form control and select Form Properties option, then Form Properties pane is displayed. It shows the properties related to the data in the table such as Content type, Content, Sort, etc., event handling properties useful for programming and macros. It also shows general form properties such as Name of the form. Understanding Control Properties Once form is opened in the Design View, you can display the properties of a control by double clicking on it or by clicking Control button on the Control toolbar. Usually, when you edit a form created by wizard, the label and text box are grouped together. You can right click on them and select Ungroup. Now, their properties can be displayed separately. In the following example, properties of a Textbox control txtITEM_CODE are displayed such as Enabled property is set to Yes means, value in this control can be edited or clicked by user, Label Field property is bound with the field ITEM_CODE in the Item table. So, it is a bound control. 5178 Database Management System

Setting Properties of the Controls Binding a control with a field in a table ¤ In the Form Properties box, Content property of a control allows to select the desired table, query or SQL command whose data that control should display. To select the desired table, you must select Table in its Content type property. ¤ Once table is selected, right click on the control and select Control Properties. Here, go to Data tab and select the desired field in the Data field property drop-down. Creating a Lookup List Many times, in a child table we need to enter a value which must present in the master (parent) table. For example, when an item is sold then, in Sales table we must enter item code that is present in Item table. For this purpose we can create a list in the Form created for child table in which values come from the parent table. Such list is called lookup list. Here, the Form is created for Sales table and for its field ITEM_CODE, the values are listed from Item table. To achieve this, first draw a List Box control from the Control Toolbox then set the properties like this: ¤ Form properties: Content type: Table Content: Sales ¤ Control properties (Data tab): Data Field: ITEM_CODE Type of list contents: Table List content: Item When you save and open this form, notice the 5th record shows J003 highlighted because that is the code stored in Sales table in 5th record but all 13 item codes are listed from Item table. So, the user can only select item code from these codes while adding a new record. This ensures data integrity as no non-existent item code will be entered in Sales table. Database Management System 5179

General Control Properties Right click on the desired control and select Control Properties. In the properties box, various properties are there. General Properties Unique name given to the control. ¤ Name: ¤ Enabled (Yes/No): If no, disables the control. ¤ Visible (Yes/No): If no, hides the control. ¤ Background color: Sets backgorund colour of the control. ¤ Border: Sets border to flat or 3D look. ¤ Max text length: Sets maximum number of characters to enter in a Text Box. ¤ Text type: Single or multi-line text. ¤ Password character: Character to display in place of letters in a password text box. Changing a Label Background Right click on the Label control and select Control Properties. In the properties box, go to General tab. Scroll down to Background color property drop-down and select the desired colour from the colour box as shown here. Form Controls Bar Form Controls Bar contains the common controls which can be added on the form or can be used to design the form as desired. Here, common form controls are shown. 5180 Database Management System

LAB EXERCISE Create a master table Furniture to store FurnitureID, Furniture Name, Furniture Material, Furniture Price and Out of Stock. Furniture Material can be Wood, Plastic or Metal. Design a new Form using Form Design View. Keep FurnitureID non editable assuming that it is an autoincrement value. Furniture Material should be a List Box. Out of Stock should be a Check Box, checked means Yes otherwise No. At the top of the form display a big title: FURNITURE DATA ENTRY SCREEN Perform some colour and font settings also on the controls to make the form look formally appealing. Session-10 Design Reports A report isused to present the data formally and can be used to print. It is a logical combination of all the raw facts of the database and presents the result with a visual meaning. Reports present a comprehensive summary of the data stored in the database. Reports help in analysing the data and making decisions. Different Sections of Report The Header Section is the top most section of the report. Report headers usually contain titles and logos. Page headers usually contain column headings. The Details Section is the body of the report which contains the main data. All reports must have a detail section. The Footer Section can appear in several places. You can create page footers that appear at the bottom of each report page and display elements such as page numbers. The header and footer sections are optional. Sample report shown here is grouped on ITEM_NAME and ITEM_TYPE. It is made on a query that calculates SALES AMOUNT as PRICE * QTY_SOLD Database Management System 5181

If you don't want to group the data, you don't need to group headers or footers. However, make sure that your reports contain enough information to make them meaningful and easy to understand. Creating Report 1. Click on the category Reports in Database pane and select Use Wizard to Create Report… option in the Tasks pane. In the Report Wizard: 2. From Tables or queries drop- down, select the desired table or query (In our example, let us select query Sales Query1), add the desired fields to the Fields in report list and click Next. 3. If you wish, change the labels of the fields into more readable forms as shown here. Then click Next. 5182 4. Select the fields to group the records on it by selecting the desired fields and clicking on > button. Here, ITEM_NAME and ITEM_TYPE are taken for grouping. Click Next. Database Management System

5. You can sort the report output on the basis of one or more fields. Select the fields on which report data should be sorted in ascending or descending order. Here, data is sorted in ascending order of ITEM_NAME, ITEM_TYPE and PRICE. Click Next. 6. Choose a report layout and page orientation. Then, click Next. 7. Specify the Title of report as shown here along with Dynamic Report option under which, select Create report now option. Finally, click Finish. Dynamic report means every time the report is generated, it will be refreshed with updated data from the source table/query. Another sample report is shown here. Its header has title, details section has report data. The report is grouped on item type and sorted on item name, size and price. Database Management System 5183

LAB EXERCISE A. Create a report named FURNITURE SALES listing FurnitureID, Furniture Name, Furniture Material, Furniture Price, Sale Quantity and Sale Amount as Price * Quantity. You need to create a query on which report must be created. Give the alias name SALE AMOUNT to the calculated field Price * Quantity. B. Practice creating various reports on the tables created in earlier sessions. C. Practice Section D of Assessment in lab. 5184 Database Management System

A.
Select the correct option to answer the following questions – 1 mark each. 1.
Employee code E00009 has joined the company on 10/10/1990 – in this statement, identify the data from the following:
a.
Employee code


b.
Date of joining

c.
Both a) and b)


d.
Company 2.
Which of the following is retrieved from the data stored in a database?
a.
Data




b.
Information
c.
Query




d.
Table 3.
In a flat file system, which of the following is difficult to perform?
a.
updates



b.
deletes
c.
Manage data



d.
All of these 4.
Which of the following is not an advantage of a DBMS?
a.
Data redundancy


b.
Data integrity
c.
Data sharing



d.
None of these 5.
Databases reduce multiple copies of data. Such multiple copies are called which of the following?
a.
Redundant values


b.
Foreign key
c.
Information



d.
Queries 6.
Which of the following component of a database stores the data?
a.
Form




b.
Table
c.
Report



d.
Query 7.
Alternate term for the tables in an RDBMS is ________________.
a.
Relationship



b.
Index
c.
Relation



d.
Application 8.
Which of the following is referred to as tuples in a database table?
a.
Columns



b.
Fields
c.
Rows




d.
All of these 9.
Which of the following is referred to as attributes in a database table?
a.
Columns



b.
Fields
c.
Rows




d.
All of these 10.
Which of the following is the formal presentation of data?
a.
Form




b.
Query
c.
Table




d.
Report Database Management System 5185

11.
Columns eligible to be primary keys are called which of the following?
a.
Foreign keys



b.
Keyset
c.
Composite keys


d.
Alternate keys 12.
There can be duplicate values in a foreign key field.
a.
True




b.
False 13.
Foreign key cannot store null values.
a.
True




b.
False 14.
Which of the following SQL commands are part of DDL?
a.
CREATE



b.
ALTER
c.
DROP




d.
All of these 15.
Which of the following SQL commands are part of DML?
a.
SELECT



b.
INSERT
c.
DELETE



d.
All of these 16.
Which of the following is true about primary key and foreign key.
a.
Foreign key in a child table points to a primary key in the parent table.
b.
Foreign key in a parent table points to a primary key in the child table.
c.
Primary key in a child table points to a foreign key in the parent table.
d.
None of the above. 17.
In which of the following views do you see the field properties?
a.
Table design view


b.
Form design view
c.
Query design view


d.
Table data view 18.
Using a database form which of the following actions can be performed?
a.
Data entry



b.
Data delete
c.
Data modification


d.
All of these 19.
What is the correct syntax of creating a field Student_Name in table Student as primary key?
a.
create table Student(Student_Name, primary key, 100, varchar)
b.
create table Student(Student_Name, 100, varchar, primary key)
c.
create table Student(Student_Name, varchar, primary key, 100)
d.
create table Student(Student_Name, varchar(100), primary key) 20.
Which of the following constraints ensures a pre-set value is entered in the field if user does not enter any value? a.
by default
b.
default c.
primary key

d.
not null 21.
Which of the following constraints ensures that a field should not be left blank?
a.
by default
b.
default c.
primary key

d.
not null 22.
ALTER TABLE command is used to do which of the following?
a.
Add new field


b.
Delete a field
c.
Add a
constraint


d.
All of these 5186 Database Management System

23.
Delete cascade refers to which of the following statements?
a.
Delete on parent table will delete matching records in child table.
b.
Delete on child table will delete matching records in parent table.
c.
Delete on parent table will delete all the records in child table.

d.
Delete on child table will delete all the records in parent table.
24.
Teacher teaches students is which type of relationship?
a.
One-to-one



b.
Many-to-many
c.
One-to-many



d.
Many-to-one

25.
Teachers teach students is which type of relationship?
a.
One-to-one



b.
Many-to-many
c.
One-to-many



d.
Many-to-one 26.
A passenger is allocated one seat in the flight is which type of relationship?
a.
One-to-one



b.
Many-to-many
c.
One-to-many



d.
Many-to-one

27.
What can we mention in the Field section of Query design view?
a.
Field name



b.
Formula
c.
Both a) and b)


d.
Alias name 28.
Between 10 and 25 – this will check which of the following range?
a.
Between 9 and 24


b.
Between 10 and 25
c.
Between 11 and 26


d.
None of these 29.
Find the odd one out:
a.
*
b.
? c.
(
d.
None of these 30.
LIKE
'P?P' refers to which of the following?
a.
PPP
b.
POP c.
Both a) and b) d.
PP 31.
LIKE 'P*P' refers to which of the following?
a.
PPP
b.
POP c.
PP
d.
All of these 32.
In which of the following calculated fields are saved?
a.
Table
b.
Form c.
Both a) and b)
d.
None of these 33.
Which of them is not a clause of SQL command?
a.
Where
b.
Select
c.
Order by
d.
None of these 34.
Which of the following clauses will sort the output in ascending order?
a. Order by price asc
b. Order by price
c. Both a) and b)
d.
None of these 35.
Where clause can be used with which of the following SQL commands?
a.
SELECT
b.
UPDATE
c.
DELETE
d.
All of these 36.
You cannot interact with a control whose ____________________.
a.
Enabled = False


b.
Visible = True
c.
Both a) and b)


d.
Input required = No Database Management System 5187

B.
Answer the following questions – 2 marks each. 1.
What is data? 2.
What is information? 3.
What do you mean by the terms database and DBMS? 4.
List any 2 advantages of a database. 5.
In two lines, explain how databases help businesses. 6.
What is RDBMS? 7.
What is relation in a database? 8.
What is the use of database forms? 9.
What is the significance of database reports? 10.
How query helps in retrieving information from the data stored in the database? 11.
How is VARCHAR is different from CHAR data type in a database? 12.
What is the relation between a foreign key and a primary key? 13.
List any two commands of DDL and DML each. 14.
Why do we need a transaction or child table? 15.
Mention the use of any two field properties of a table. 16.
How will you set a primary key in a table? 17.
Give two basic differences between table data view and table design view. 18.
Write SQL statement to create a table Person to store Full Name, Birth Date, email ID and City. Email ID is primary key. 19.
How will you sort data in a database table? 20.
What do you mean by referential integrity? 21.
List one example of 3 types of relationship each. 22.
What is a wildcard? Mention 2 examples of wildcard. 23.
How will you group data on a field in query design view? 24.
What do you mean by control properties? C.
Answer the following questions – 3 marks each. 1.
What is the significance of a database? 2.
Briefly discuss any 2 major advantages of a database. 3.
How database can help a school like yours? 4.
What is the basic difference between the structure of a flat file system and relational database? 5.
In one line, define the terms table, query, form and report. 6.
What is the significance of foreign key and primary key in a database? 7.
Briefly describe the two logical types of SQL. 8.
List the steps to design a table using table wizard? 9.
How will you add a new field and a new constraint in a table? Explain with an example. 5188 Database Management System

D.
Answer the following questions – 5 marks each. 1. Consider the following table and write SQL statements to answer the questions that follow: SubscriberID Name Connection Pending_Amount ------------------------------------------------------------------------------------------------------------------------------ S0001 Anand Kumar Broadband 2200 S0002 Dinesh Verma Postpaid 3310 S0003 Minal Sen Postpaid 1200 S0004 Suman Kumari Broadband 0 S0005 Ravi Kumar Broadband 900 S0006 Atul Om Postpaid 0 i. Create table Billing with Subscriber ID as primary key, name should not be blank, default connection is Prepaid. ii. Display all the names starting with letter ‘A’. iii. Display the connection type of those who have letter ‘u’ in their name anywhere. iv. Whose names are ending with letter ‘r’? v. Display the interest on non-zero pending amounts as 8% along with total amount. vi. Display total pending amount connection type-wise. vii. Increase non-zero pending amounts by 8% viii. Delete all records having non-zero pending amount. ix. Who is having no pending amount on broadband connection? x. Insert a new record for subscriber code S0007 with suitable assumed values. 2. Perform following alterations in the above table: i. Add a field Connection_Date ii. Add a constraint on pending_amount to check if it is 0 or more. iii. Rename the field ‘Name’ to Subscriber_Name. iv. Rename the table to ‘Pending_Billings’.. Watch & Learn www.eduitspl.com www.youtube.com/edusoftknowledgeverse Database Management System 5189

Unit Web Applications 4 and Security Session-1 Accessibility Options in Windows Computer Accessibility refers to the user friendliness of a computer system for all, regardless of their disability. This is mainly a software issue. How ever, when a combination of hardware and software, it enables a person with a disability or impairment to use a computer. It is known as Assistive Technology. In this session, you will learn about the basic accessibility options available on your computer. There are numerous types of impairment that impact computer usage. These include: ¤ Cognitive impairments and learning disabilities, such as dyslexia, attention deficit-hyperactivity disorder (ADHD) or autism. ¤ Visual impairment such as low- vision, complete or partial blindness, and color blindness. ¤ Hearing impairment including deafness. ¤ Motor or dexterity impairment such as paralysis, cerebral palsy, or carpal tunnel syndrome and repetitive strain injury. Windows 7 includes accessibility options and programs that make it easier to see, hear, and use your computer. Launching Accessibility Options Steps to launch accessibility options in Windows 7 are as below: ¤ Click on Start. ¤ Click on Control Panel. ¤ Click the Ease of Access link. ¤ Click Ease of Access Center link. Setting up Keyboard Access Click Make the keyboard easier to use in the Ease of Access Center. Turn on Mouse Keys checkbox to allow the user make use of arrow keys on the numeric keypad to move the keys on screen and Enter key to click. For example, user can press top arrow and left arrow key to move mouse pointer diagonally upwards towards left side. Turn on Sticky Keys checkbox allows user to keep the key active when it is pressed once and when any other key is pressed then it is processed 5190 Web Applications and Security

by the computer with the currently active key. For example, if user has pressed Window key on the keyboard then it will remain active. Now, if user clicks only letter E then it will ne interpreted as Windows+E and Windows Explorer will open up. Once enabled, sticky keys icon will appear in the system tray. Turn on Toggle Keys checkbox allows user to hear an alert sound every time a key like caps lock, num lock etc. is pressed. Turn on Filter Keys check box, when selected, makes keyboard ignore rapidly repeated or long pressed key to avoid repeated typing of the same key thereby helping people who type with trembling hand. Text or Visual Alternatives to Sound People with hearing impairment have great use to this feature where they can see the visual feedback or cues on the screen instead of sound. Click on Use text or visual alternatives for sounds in Ease of Access Center. or Turn on visual notifications for sounds (Sound Sentry) option replaces system sounds with visual warnings like flashing caption bar of active window, flashing the active window itself and flash desktop. Select the desired visual warning. Any sound that reads the screen can be turned into text caption that appear on the screen by checking Turn on text captions for spoken dialog checkbox. Make the Computer Easier to See In Ease of Access Center, click Make the computer easier to see link. Choose a High Contrast Theme: Select the option to turn high contrast on/ off by pressing left Shift + left Alt and Print Screen keys. This lets visually challenged people see screen comparatively clearly. Turn on Narrator option allows read aloud of any text on the screen and Turn on Audio Description option lets visually impaired person hear descriptions of what is happening in any videos. Turn on Magnifier option lets user move mouse pointer on the screen and see the objects larger in size. This mode is called lens mode. In docked mode a portion of the screen can be left magnified and rest of the screen remains normal. This portion can be changed as required. Full screen mode magnifies whole screen. Lens size, focus area, zoom level etc. can be set by the user. Set the thickness of the blinking cursor option makes blinking cursor thicker and larger to see them easily. Web Applications and Security 5191

Make the Mouse Easier to Use In Ease of Access Center, click Make the mouse keys easier to use link. Here, you can change the colour and size of mouse pointers. You can turn on mouse keys of numeric keypad. You can set option to activate any window just by bringing mouse pointer over it instead of having to click it. LAB EXERCISE 1. Enable mouse keys on numeric keypad and test them to move mouse pointer on screen. Try opening a file using it. 2. Enable sticky keys then open any word processor and type some text. Now press Ctrl key and leave it. Then press other keys like A, C followed by V, P and observe what happens. These letters must make combination of command shortcuts Ctrl+A, Ctrl+C, Ctrl+V and Ctrl+P. 3. Open notepad and turn on Filter Key option. Now practice typing letters with brief pauses. Observe how the repeated strokes of same letter are ignored. 4. Enable toggle keys option and test them by pressing them on the keyboard. 5. Turn on Sound Sentry and open notepad. In notepad open Print dialog box and then try clicking in the notepad. Observe how it gives visual warning. 6. Try out various settings of Magnifier and see how screen looks like in various modes of magnifier. 7. Try out mouse ease of access options by changing cursor colours and size. Supplement: Accessibility Options in Ubuntu The Universal Access icon of a man in the Top bar refers to the accessibility options in Ubuntu. You can click on System Settings in Side bar to go to Universal Access. Universal Access has 4 tabs namely: ¤ Seeing ¤ Hearing ¤ Typing ¤ Pointing and Clicking 5192 Web Applications and Security

Note: Universal Access can also be opened by pressing Super key to switch to Activities Overview mode and typing Accessibility. Seeing This section provides visual settings such as contrast settings, text size, zoom, enabling screen reader and sounds for keys like Num and Caps lock. User can set the screen contrast and text size as needed. User can enable/disable zoom feature. Hearing This section allows the user to set up a visual indicator on the screen when system produces a sound alert. Users with hearing impairment may not hear the sound so they can see the flashing Title bar of the opened window or entire screen flashes. User can select a suitable option. Typing This section allows the user to enable/disable onscreen keyboard Typing Assistant for touch screens, Sticky keys that allow user to keep the modifier key (Shift, Ctrl etc.) active when it is pressed, Slow keys which allow setting up a time delay between the key press and its acceptance by the computer. This is good for elderly person or anyone who tends to have slower typing speed and Bounce keys that set up the delay between the repeated press of a key to avoid accidental duplicate typing of a key. Pointing and Clicking This section allows user to enable keyboard arrow keys on the numeric keypad to be used as Mouse Keys and move the mouse pointer on the screen. Simulated Secondary Click can be enabled to set a time delay for how long the left mouse button should be pressed to be accepted as right click. Hover Click option can be enabled to trigger mouse click just by bringing the mouse pointer over the desired option or object. The time delay and motion threshold can be set for this. Web Applications and Security 5193

Accessibility feature refers to the assistance in the following impairments: Visual impairments: Poor or low vision and blindness are assisted by the screen readers, contrast settings, changing text size, magnifying or zooming parts of screen and by blinking the cursor while typing. Hearing impairments: These is assisted by visuals on the screen by flashing window Title bar or the whole screen. Mobility impairments: These are assisted by mouse speed, using keyboard as mouse, adjusting click speed, simulating right click with left button, click by hovering, setting slow keys, bounce keys and sticky keys, setting repeated key press delay, on-screen keyboard and navigating applications by keyboard. Orca Screen Reader Brief Overview Orca is a free. open source screen reader utility. Orca provides access to the graphical interface through speech synthesis and braille for visually impaired. Orca can read aloud the user interface. To enable/disable Orca, press Super + Alt + S. To access Orca Preferences, press Insert + Spacebar. Orca preferences window will open up. You can also click on Preferences button on Orca Screen Reader main window. Orca Preferences Orca preferences dialog box can be used to setup Orca utility. In the Speech tab you can enable speech by selecting Speech system as GNOME speech services, Speech synthesizer, Voice settings and Human voices. You can also set the Rate, Pitch and Volume of the speech. To enable/disable speech you can also press Insert + S after Orca is enabled. 5194 Web Applications and Security

If you need to display the Orca preferences for any currently opened application then press Ctrl + Insert + Spacebar. To learn the commands to use Orca, first enable Orca and then enter the learning mode by pressing Insert + H. Now, press keys on the keyboard, Orca will read aloud the key name and the commands associated with that key. To come out of learning mode, press Esc key. You can refer to https://help.gnome.org/users/orca/stable/commands_reading.html.en to explore about Orca reading commands. Go to https://help.gnome.org/users/orca/stable/commands_structural_navigation.html.en to explore Orca navigation commands. LAB EXERCISE Try out the lab activities given in the previous lab exercise for Windows. Explore Ubuntu features to do them. Session-2 Networking Fundamentals Computers, when connected with each other, through a communication medium form a network. The purpose of connecting computers to form a network is to share the data, services and various resources. For instance, one printer connected with a computer can be accessed for printing by other computers on the network or a file can be copied from one computer to another without having the need of sharable media like disks or pen drives. Communication medium can be as simple as cables or advanced as wireless and satellites. On the basis of architecture, networks are of 2 types- Peer-to-peer and Client-Server. In peer-to-peer network, computers are equipped with similar processing power and share information and resources with each other. There is no specifically designated computer that is the source of all data and services. Web Applications and Security 5195

In Client-Server network computers which are dedicated to provided services and access to resources are called Servers and computers that access these services and resources over the network from the servers are called Clients. Depending on the service they provide, servers are called file server, print server, database server, internet access server (proxy server) etc. Servers are equipped with powerful processors and larger storage as compared to clients. In certain networks, clients even rely on server for their data processing. Such clients are called dumb clients. Types of Networks on the Basis of Span On the basis of their span or space occupied, networks are classified into following types: Personal Area Network (PAN) A Personal Area Network (PAN) is a computer network which is used by a single person for communication among the computer devices. For example, printers, fax machines, telephones, or scanners are the computer devices used with a PAN. The reach of a PAN is few metres only. Typically, this is used at home or in a small office. The devices are commonly connected here through wire or Bluetooth, Infrared technology. Local Area Network (LAN) A Local Area Network (LAN) covers a small geographic area, such as home, office or any building. These are used within smaller geographic range and have limited communication lines. LANs have higher data transfer rate. It is useful for sharing resources like files, printers and other applications. A LAN can be set up using wireless media also. Metropolitan Area Network (MAN) Web Applications and Security A Metropolitan Area Network (MAN) is a large network that usually spans a city, but does not extend the boundaries of the immediate town, city, or metropolitan area. Data transfer rate in Metropolitan Area Network is little slower than the Local Area Network. The distance covered by this kind of network enables a large number of users to communicate with each other in the network. For example, network set up by MTNL in Delhi and Mumbai, cable TV network in a city. 5196

Wide Area Network (WAN) A Wide Area Network (WAN) is a geographically independent communication network. It covers a large geographical area. For example, network between two or more countries. A Wide Area Network may be privately owned or rented. It has lower data transmission rate. For example, mobile network set up by BSNL Internet Networks all over the world are globally inter connected to form Internet. This is the reason why internet is called 'network of networks'. Today, almost every computer and handheld communication device is part of Internet. Computers over internet communicate through communication protocols. Internet is one of the most useful technologies of modern times which help us in our daily, personal and professional life. Internet is widely used by students, educational institutes; scientist and professionals to gather information for research and general information. Businesses use the Internet to access complex databases such as financial database. The Internet is the largest encyclopedia for all age groups. The Internet helps in maintaining contact with friends and relatives who live across different locations via Internet chatting systems and email software. Internet is also becoming a major source of entertainment for the general public. The first workable form of internet came in 1969 with the creation of Advanced Research Projects Agency NETwork (ARPANET) in USA under the U.S. Department of Defense. ARPANET allowed multiple computers to communicate on a single network. In 1990s, Internet came in wider use when computer scientist Tim Berners-Lee invented the World Wide Web. World Wide Web World Wide Web (www) is the largest information system on internet. It contains millions of web sites that provide information in the form of text, animations, pictures and videos. Thus, it is like a library that contains millions of books or information in the form of web pages. What is the difference between Internet and World Wide Web? Internet is a massive network of computer networks. It connects millions of computers together globally, forming a network in which any computer can communicate with any other computer (as long as they are connected to the Internet). The World Wide Web, or simply Web, is a way of accessing information over the medium of the Internet. It is an information-sharing model that is built on top of the Internet. The Web uses the HTTP/HTTPS protocol to transmit data. The Web also utilizes browsers, such as Internet Explorer or Mozilla Firefox, to access Web documents called Web pages that are linked to each other via hyperlinks. Advantages of networking Data Sharing: One of the most important uses of networking is to allow the sharing of data. User scan send text files, spread sheets, documents, presentations, audio files, video files, etc. to other users. Hardware Sharing: Hardware components such as printers, scanners, etc. can also be shared. For example, instead of purchasing 10 printers for each user, one printer can be purchased and shared among multiple users thus saving cost. Web Applications and Security 5197

Internet Access Sharing: You can purchase a single Internet connection and share it among other computers in a network instead of purchasing multiple Internet connection for each computer. This is very commonly found in Internet café (browsing centres), schools, colleges, companies, etc. Usage of network based applications such as web browsers, email clients, chat application, audio & video calling, remote desktop applications etc. is another advantage. Getting Access to Internet To connect your computer with internet you need some additional hardware and software. These are as follows: ¤ A telephone or cable line or a dongle (internet data card) for internet connection by the Internet Service Provider. ¤ A modem: Modem is a device that enables a computer to transmit the data through telephone or cable lines. A modem modulates outgoing digital signals from a computer to analog signals for a conventional copper twisted pair telephone line and demodulates the incoming analog signal and converts it to a digital signal for the digital device. Modem can be wired (Digital Subscriber Line, dial-up, cables) or wireless (Wi-Fi, 3G/4G etc.). ¤ Software (browser): A browser/web browser is an application software that is used to access the information (web sites) on the web. Mozilla Firefox, Internet Explorer, Google Chrome, Safari, Opera are some commonly used browsers. Internet Service Provider A company or organisation that provides the access to internet. Access is provided through a cable and a modem that connects your computer with the service providers internet server. Connectivity provides a wide choice of data transfer speed and other services along with internet access. The kind of connectivity available to you depends connectivity facilities in your area. Types of Common Internet Connectivity Connectivity to internet can be broadly categorised into wired and wireless. There are different types of Internet Connectivity available today; it can be widely categorized into wired and wireless access. Following table is a summary of different types of Internet connectivity categorized into wired and wireless: Technology Type of Connectivity Speed Dial-Up Wired 56 Kbps approx. DSL Wired 5-35 Mbps Cable Internet Access Wired 100-300 Mbps 3G/4G/5G Wireless 8/28/280 Mbps approx respectively. WiMAX Wireless Up to 75 Mbps Wi-Fi Wireless 50-2000 Mbps Some of the commonly used Internet connectivity are: Dial-up: Dial-up Internet access is a form of Internet access that uses the facilities of the public switched telephone network (PSTN) to establish a connection to an Internet service provider (ISP) via telephone lines using a device called MODEM. Users dial a particular number provided by the ISP and gain access to the Internet. 5198 Web Applications and Security

Dial-up connections are extremely slow and in most cases, it is replaced by a high speed connection such as DSL or Cable Modem. DSL: Digital subscriber line(DSL) provide Internet access by transmitting digital data over wires of a local telephone network. DSL service is delivered along with wired telephone service on the same telephone line. On the customer premises, a DSL filter removes the high frequency interference, enabling simultaneous use of the telephone and data transmission. For using a DSL connection, you need a DSL modem and a subscription. Cable Internet Access: Cable Internet Access is a form of broadband Internet access that uses the cable television in frastructure. Cable Internet Access is provided through existing cable TV networks; this is similar to DSL that is provided over existing telephone lines. 3G/4G/5G: 3G, short for 3rd Generation is a set of standards used for mobile devices and mobile telecommunication services and networks. High-Speed Downlink Packet Access (HSDPA) is 3G mobile telephony communications protocol that allows higher data transfer speeds and capacity. 3G can transfer multimedia at very high speed rate. Advanced forms of 3G are 4G and 5G. While 3G speed is 8 Mbps, that of 4G is 28 Mbps. 5G promises to be 10 times faster than 4G. If your mobile device supports any of these, you can subscribe for them with your ISP in order to get high speed Internet connection on your phone. WiMAX: WiMAX (Worldwide Interoperability for Microwave Access) is a wireless communications standard designed to provide mobile broadband connectivity across cities and countries through a variety of devices. WiMAX is a long range system, covering many kilometres and is typically used where DSL or Cable Internet Access cannot be used; this could be difficulty in laying out cables for home or offices located in remote locations but need access to the Internet. WI-Fi: Wi-Fi is a popular technology that allows an electronic device such as computers or mobile phones to exchange data wirelessly over a network, including high-speed Internet connections. Wi-Fi devices such as personal computer, smart phones, video game console, etc. can connect to a network resource such as Internet through a device called the Wireless Access Point (WAP). Wi-Fi is used where cables cannot be run (such as old buildings, outdoor areas)to provide network and Internet access. Wi-Fi can also be used where users require mobile connectivity. Wi-Fi connectivity is used in home & offices, hotels, college & school campus typically for Internet Access. Shopping malls, coffee shops, resorts mostly offer free or paid Wi-Fi access to the Internet for their customers. Data transfer on the Internet Let us see how a piece of data, say a Web page, when it is transferred over the Internet: ¤ The data is broken up into bits of same sized pieces called packets. ¤ A header is added to each packet explaining where the data has come from, where it should end up and where it fits in with the rest of the packets. ¤ Each packet is sent from computer to computer until it finds its destination. Each computer on the way decides where next to send the packet. All packets may not take the same route. ¤ At the destination, the packets are examined. If any packets are missing or damaged, a message is sent as king for them to be re-sent. This continues until all packets have been received intact. ¤ The packets are now reassembled into their original form. All this is done in seconds! To access the Internet, you need an active internet connection. You need to gather and understand your requirement carefully before subscribing to an internet connection plan. In this exercise, you will learn how to choose an Internet connection. Web Applications and Security 5199