Information Technology Class XII 2016 CBSE PDF

 DATABASE MANAGEMENT APPLICATIONs class xii student handbook CENTRAL BOARD OF SECONDARY EDUCATION Shiksha Kendra, 2, Community Centre, Preet Vihar, Delhi-110301 Database Manage...

 DATABASE MANAGEMENT APPLICATIONs class xii student handbook CENTRAL BOARD OF SECONDARY EDUCATION Shiksha Kendra, 2, Community Centre, Preet Vihar, Delhi-110301 Database Management Applications Student Handbook, Class-XII Price: ` First Edition: February 2016, CBSE Copies: Paper Used: 80 gsm CBSE Water Mark White Maplitho "This book or part thereof may not be reproduced by any person or agency in any manner." Published By : The Secretary, Central Board of Secondary Education, Shiksha Kendra, 2, Community Centre, Preet Vihar, Delhi-110301 Design & Layout : Multi Graphics, 8A/101, WEA Karol Bagh, New Delhi-110005 Phone: 011-25783846, 47503846 Printed By :  H k k jrd k laf o /k u m í sf ' kd k 1 g e] Hk k jrdsyk sx ] H kk j r d k s,d l Ei w. k Z izH k qR o &l ai U ule kt o k nh iaF k f u ji s{ kyk sdr a=kk R ed x.k jk T;c uk usd sfy ,] rF kk m l dsl eLr u k xfj d ksa d ks% l k e k ftd]v k fF k Zdv k Sjj k tu Sfrd U ; k ; ] f o p k j]v f Hk O; fDr ]f o' ok l] /eZ v kSjm i k l u kdh Lora= k r k ] izfr " B k v kSj voljd h l e rk i zk I r dj kusd sf y, r Fk k mul c esa O; fD rdh x fj e k 2 v kSj j k " V ªdh,drk v kSjv[k aMrk l qf u f 'p r dju sok yh ca/ qr kcsv kSjml d k i fj j { k.k d j s_ ( N) i zk Ñ frd i ; k Zoj. kd h f t l d sv ar x Zro u ] > hy ] u n h ] v k Sj o U;t hog Sa] j{ k k djsvk Sj ml d k lao / Zu dj srFk k i zk.k h ek=kd sizf r n; k H k k o j[ k s_ ( t) o SK k f u d n`f"V dk s.k ] ek u o o kn v k SjK k u kt Zu r Fk k l q/ kj dh Hk k ouk d k fo d k l djs_ ( >) l koZtfu d l ai f Ùk d k sl qjf{ kr j [k sv k Sjf gal k l sn wj j g s_ ( ×k ) O; fD r xr v k Sj l kewf gd x f r fo f/ ;k sa d sl H k h { ks=k k sa e sa m Rd " kZdhv k sj c< +u sdk l r ri z;kl d jsf t ll sj k" Vªf uj arjc 1985. We describe below various types of constraints in Relational model: 1. Domain Constraint: It specifies that the value of every attribute in each tuple must be from the domain of that attribute. For example, the Employee_ID must be a 4-digit number. Hence a value such as “12321” or “A234” violates the domain constraint as the former is not 4-digit long and the latter contains an alphabet. 2. Key Constraint: Before we can explain this constraint, we need to describe the terms superkey, key, candidate key and primary key. (i) Superkey is a set of attributes in a relation, for which no two tuples in a relation state have the same combination of values. Every relation must have at least one superkey which is the combination of all attributes in a relation. Thus for the EMPLOYEE relation, following are some of the superkeys: (a) {Name, Employee_ID, Gender, Salary, Date_of_birth} - default superkey consisting of all attributes. (b) {Name, Employee_ID, Date_of_Birth} (c) {Employee_ID, Gender, Salary} (d) {Name, Employee_ID, Gender} (e) {Employee_ID} 7 Database Management Applications  However {Gender, Salary} is not a superkey because both these attributes have identical values for employees Neha and Himani. (ii) Key is the minimal superkey, which means it is that superkey of a relation from which if any attribute is removed then it no longer remains a superkey. For example the superkey {Name, Employee_ID, Gender}is not a key as we can remove Name and Gender from this combination and then what is left {Employee_ID} is still a Superkey. Now {Employee_ID} is a key as it is a superkey as well as no more removals are possible. A relation may have more than one key. Consider the relation PERSON with the following schema: PERSON (Aadhar_number, PAN, Voter_ID_cardno, Name, Date_of_birth, Address). This relation has three keys namely : {Aadhar_number}, {PAN}, {Voter_ID_no} as every individual in India has a unique Aadhar card number, PAN as well as Voter ID card number. (iii) Candidate key: A key as described above is called candidate key of the relation. For example, the PERSON relation has three candidate keys as discussed above. (iv) Primary Key: One of the candidate keys may be designated as Primary key. Primary key is used to identify tuples in a relation. If a relation has many candidate keys it is preferable to choose that one as primary key which has least number of attributes. Primary key are usually underlined in the schema of the relation. For example in the relation schema: PERSON (Aadhar_number, PAN, Voter_ID_cardno, Name, Date_of_birth, Address), Aadhar_number is the primary key. The relation between superkey, key, candidate key and primary key can be explained with the help of Figure 1.5(c). Primary Key Candidate Keys Keys Superkeys Figure 1.5(c): Superkey, Key, Candidate key and Primary key 3. Null Value Constraint: Sometimes it is required that certain attributes cannot have null values. For example, if every EMPLOYEE must have a valid name then the Name attribute is constrained to be NOT NULL. 4. Entity Integrity Constraint: This constraint specifies that primary key of a relation cannot have null value. The reason behind this constraint is that we know primary key contains no duplicates. However if we allow null values for a primary key then there can be multiple tuples for which primary key is having null values. This would imply that we are allowing duplicate values (NULL) for a primary key which itself violates the definition of primary key. 8  5. Referential Integrity Constraint: This constraint is specified between two relations. Before defining this constraint let us study the concept of foreign keys. Foreign key in a relation R1 is the set of attributes in R1 that refer to primary key in another relation R2 if the domain of foreign key attributes is same as that of primary key attributes and the value of foreign key either occurs as a value of primary key in some tuple of R2 or is NULL. R1 is called the referencing relation and R2 is called referenced relation, and a referential integrity constraint holds from R1 to R2. The main purpose of this constraint is to check that data entered in one relation is consistent with the data entered in another relation. For example, consider two relation schemas: Department (Dept_Name, Dept_ID, No_of_Teachers) Teacher (Teacher_Name, Teacher_ID, Dept_ID, Subject) Following are the primary keys (Underlined above): F Dept_ID is the primary key of Department relation. F Teacher_ID is the primary key of Teacher relation. Now you may notice that Dept_ID- the primary key of relation in Department, is also present in relation Teacher. The reason is that every teacher belongs to a particular department. Now that means Dept_ID of Teacher relation must have a value that exists in Dept_ID attribute of Department relation or it can be NULL in case a teacher has not yet been assigned to a department. We say that Dept_ID of Teacher relation is a foreign key that references primary key of Department relation (Dept_ID). It is important to emphasize it is not necessary to have same name for foreign key as of the corresponding referenced primary key. The above two schemas can also be written as follows: Department (Dept_Name, Dept_ID, No_of_Teachers) Teacher (Teacher_Name, Teacher_ID, Dept_No, Subject) Where Dept_No is the foreign key that references Dept_ID of Department relation. A foreign key may also refer to the same relation. For example suppose we have to create a database of all residents in a colony along with their best neighbors. Consider the following relation: Residents (Name, RID, Block_no, House_no, Floor, Neighbor_RID) The Primary key of this relation is RID (Resident ID). In order to store information about neighbor we have created a foreign key Neighbor_RID that references RID of Residents. Note that the referencing and referenced relation are same in this case. 1.6 Structured Query Language (SQL) SQL is a language that is used to manage data stored in a RDBMS. It comprises of a Data Definition Language (DDL) and a Data Manipulation Language (DML) where DDL is a 9 Database Management Applications language which is used to define structure and constraints of data and DML is used to insert, modify and delete data in a database. SQL commands are used to perform all the operations. In order to study SQL commands, a database system needs to be installed on the Computer. There are various softwares available. We will study the MySQL server. SQL uses the terms table, row and column for the relational model terms relation, tuple and attribute. For studying SQL we will use MySQL Community Server 5.6.20 which is freely downloadable. The most recent versions can be found on the website: http://dev.mysql.com/downloads/ Following are the steps to install and configure MySQL Community Server 5.6.20 for studying SQL commands. 1. Open the URL: http://dev.mysql.com/downloads/mysql/#downloads 2. Download the MySQL Community Server 5.6.20 available on the above webpage. You can select the platform (Windows/Linux) as shown below. MySQL Community Server 5.6.20 Select Platform: Microsoft Windows Recommended Download: All MySQL Products. For All Windows Platforms. In One Package Starting with MySQL 5.6 the MySQL Installer package replaces the server-only MSI packages. Windows (x86, 64-bit), MySQL Installer MSI Other Downloads: Windows (x86, 32-bit), MSI Installer 5.6.20 44.8M (mysql-5.6.20-win32.msi) MD5: 59abb64af27634abd0f65a60204b18ab Signature Windows (x86, 64-bit), MSI Installer 5.6.20 47.8M (mysql-5.6.20-win64.msi) MD5: 503dc2840c6732ae3e5dc80a3022f1a7 Signature Windows (x86, 32-bit), ZIP Archieve 5.6.20 337.6M (mysql-5.6.20-win32.zip) MD5: 28528dd2cecdd79d33deca2e1b7058e Signature Windows (x86, 64-bit), ZIP Archieve 5.6.20 342.9M (mysql-5.6.20-win64.zip) MD5: 08028e89f892534114550c75f57f3453 Signature 3. Once you have downloaded the file mysql-installer-community-5.6.20.0.msi, double click on the downloaded file and then click on the “Run” button. 10 4. MySQL Installer will start installing. Click on the “Install MySQL Products” option. MySQL Installer Welcome The MySQL installer guides you through the installation and configuration of your MySQL products. Run it from the Start Menu to perform maintenance tasks later. Select one of the actions below: Install MySQL Products Guide you through the installation and configuration of your MySQL products. About MySQL Lean more about MySQL products and better understand how you can benefit the most. Resources Get more information on how to install MySQL and configure it to run efficiently on you machine. Copyright © 2014, Oracle and/or its affiliates. All rights reserved, Oracle is a registered trademark of Oracle Corporation and/or its affiliated. Other names may be trademarks of their respective owners. 11 Database Management Applications 5. Check the option “I accept the license terms” and then Click on “Next” button. MySQL Installer License Agreement MySQL. Installer To install MySQL, you must accept the Oracle Software License Terms. GNU GENERAL PUBLIC LICENSE Vesrion2, June 1991 License Information Copyright (c) 1989, 1991 Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA Find latest products Everyone is permitted to copy and distribute verbatim copies of this license document, but changing it is not allowed. Setup Type Preamble ========= Check Requirements The licenses for most software are designed to take away your freedom to share and change it. But contrast, the GNU General Public License is Installation intended to guarantee your freedom to share and change free] software--to make sure the software is free for all its users. This Configuration General Public License applied to most of the Free Software Foundation’s software and to any other program whose authors commit to Complete using it. (Some other Free Software Foundation software is covered by the GNU Library General Public Licence instead.) You can apply it to your programs, too. When we speak of free software, we are referring to freedom, not price. Our General Public Licenses are designed to make sure that you have the freedom to distribute copies of free software (and charge for this service if you wish), that you receive source code or can get it if you want it, that you can change software or use pieces of it in new\ free programs; and that you know you can do these things. I accept the license terms < Back Next > Cancel 6. Then next click on the “Execute” button. MySQL Installer Find latest products MySQL. Installer Before continuing, the Installer will determine if updates are available for the products you are about to install or upgrade. License Information Connect to the Internet Find latest products Fetch product update information Setup Type Check Requirements Installation Configuration Complete Skip the check for updated (not recommended) < Back Execute Cancel 12 7. On successful execution, click on the “Next” button. MySQL. Installer Find latest products MySQL. Installer Before continuing, the Installer will determine if updates are available for the products you are about to install or upgrade. License Information Find latest products Connect to the Internet Fetch product update information Setup Type The operation is complete. Please click 'Next >' to continue. Check Requirements Installation Configuration Complete Skip the check for updates (not recommended) < Back Next > Cancel 8. Select the “Server only” option. Then click on “Next” button. MySQL Installer Choosing a Setup Type MySQL. Installer Please select the Setup Type that suits your use case. Developer Default Setup Type Description Installs all products needed for Installs only the MySQL Server. This type should License Information MySQL development purposes. be used where you want to deploy a MySQL Server, but will not be developing MySQL Find latest products applications. Server only Installs only the MySQL Server Setup Type product. Check Requirements Client only Installation Installs only the MySQL Client products, without a server. Configuration Full Installation Path: Complete Installs all included MySQL products and features. C:\Program Files\MySQL\ Custom Data Path: Manually select the products that C:\ProgramData\MySQL\MySQL Server 5.6\ should be installed on the system. < Back Next > Cancel 13 Database Management Applications 9. Installer will check for the requirements. If any requirements are required, you have to download them first before installing MySQL server. If all the requirements are met, then the following message will be displayed. Click “Next” to continue. MySQL Installer Check Requirements MySQL. Installer There are no additional requirements to be installed. Please click Next to continue with the product installation. License Information Find latest products Setup Type Check Requirements Installation Configuration Complete < Back Next > Cancel 10. Click on “Execute” to install MySQL Server 5.6.20. MySQL Installer Installation Progress MySQL. Installer The following products will be installed or updated. Product Status Progress Notes License Information MySQL Server 5.6.20 To be installed Find latest products Setup Type Check Requirements Installation Configuration Complete Click [Execute] to install or update the following packages < Back Execute > Cancel 14 11. On successful installation, Click on “Next” MySQL Installer Installation Progress MySQL. Installer The following products will be installed or updated. Product Status Progress Notes License Information MySQL Server 5.6.20 Install success Find latest products Setup Type Check Requirements Installation Configuration Complete Show Details > < Back Execute > Cancel 12. Click on “Next” to start initial configuration. MySQL Installer Configuration Overview MySQL. Installer The following products will now be configured. Product Action to be performed Progress License Information MySQL Server 5.6.20 Initial Configuration. Find latest products Setup Type Check Requirements Installation Configuration Complete Show Details > < Back Execute > Cancel 15 Database Management Applications 13. Select the following configurations and then Click “Next”. MySQL Installer MySQL Server Configuration 1/3 MySQL. Installer Server Configuration Type Choose the correct server configuration type for this MySQL Server installation. This setting will define how much system resources are assigned License Information to the MySQL Server instance. Find latest products Config Type: Development Machine Setup Type Enable TCP/IP Networking Check Requirements Enable this to allow TCP/IP networking. Only localhost connections through named pipes are allowed when this option is skipped. Installation Port Number: 3306 Configuration Open Firewall port for network access Complete Advanced Configuration Select the checkbox below to get additional configuration page where you can set advanced options for this server instance. Show Advanced Options < Back Next > Cancel 14. Type the MySQL root password (minimum 4 characters long) and then click “Next” MySQL Installer MySQL Server Configuration 2/3 MySQL. Installer Root Account Password Enter the password for the root account. Please remember to store this password in a secure place. License Information MySQL Root Password: Find latest products Repeat Password: Setup Type Password Strength: Weak Check Requirements Installation MySQL User Accounts Configuration Crate MySQL user accounts for your users and applications. Complete Assign a role to the user that consists of a set of privileges. MySQL Username Host User Role Add User Edit User Delete User < Back Next > Cancel 16 15. Click on “Next” on the following window. MySQL Installer MySQL Server Configuration 3/3 MySQL. Installer Windows Service Details Please specify a Windows Service name to be used for this MySQL License Information Server instance. A unique name is required for each instance. Find latest products Windows Service Name: MySQL56 Setup Type Start the MySQL Server at System Startup Check Requirements Run Windows Service as... Installation The MySQL Server needs to run under a given user account. Based on the security requirements of your system you need to pick one of the options below. Configuration Standard System Account Complete Recommended for most scenarios. Custom User An existing user account can be selected for advanced scenarios. < Back Next > Cancel 16. Installer will configure the server. On successful configuration, click on “Next”. MySQL Installer Configuration Overview MySQL. Installer The following products will now be configured. Product Action to be performed Progress License Information MySQL Server 5.6.20 Configuration Complete. Find latest products Setup Type Check Requirements Installation Configuration Complete Show Details > < Back Execute > Cancel 17 Database Management Applications 17. Then Click on “Finish”. The installation and configuration of MySQL Server 5.6.20 is now complete. You can now start using the server for creating and modifying databases. MySQL Installer Installation Complete MySQL. Installer The installation procedure has been completed. License Information Find latest products Setup Type Check Requirements Installation Configuration Complete Back Finish Cancel We give below SQL commands used to define and modify a database: 1. Create Table Command: This command is used to create a new table or relation. The syntax for this command is : CREATE TABLE ( [constraint] , [constraint], [constraint] ); where []=optional The keyword CREATE TABLE is followed by the name of the table that you want to create. Then within parenthesis, you write the column definition that consists of column name followed by its data types and optional constraints. There can be as many columns as you require. Each column definition is separated with a comma (,). All SQL statements should end with a semicolon (;). Table 1 shows the data types commonly used. 18  Data type Meaning Example CHAR (n) Fixed length character string. CHAR(5):“Ashok” 'n' is the number of characters. “Vijay” VARCHAR(n) Variable length character string. VARCHAR(15): 'n' is the maximum number of “Vijay Kumar” characters in the string. “Ashok Sen” DATE Date in the form of YYYY-MM-DD DATE: '2014-03-20' INTEGER 23 Integer number 56789 Fixed point number m represents the DECIMAL(5,2) : 999.99 DECIMAL (m, d) number of significant digits that are -567.78 stored for values and d represents DECIMAL (5) : 23456 the number of digits that can be 99999 stored following the decimal point. If d is zero or not specified then the value does not contains any decimal part. Table 1: Commonly used data types Suppose we wish to create a database of all the teachers working in a school. This database should include the Teacher relation (schema given below): Teacher (Teacher_ID, First_Name, Last_Name, Gender, Date_of_Birth, Salary, Dept_No) The above schema stores the information about all the teachers working in the school such as their unique ID, first and last name, gender, salary, date of birth and the department to which the teacher belongs. To create the above relations in SQL, following CREATE TABLE command is used: CREATE TABLE Teacher ( Teacher_ID INTEGER, First_Name VARCHAR(20), Last_Name VARCHAR(20), Gender CHAR(1), Salary DECIMAL(10,2), Date_of_Birth DATE, Dept_No INTEGER ); 19 Database Management Applications To create the above table in MySQL, click on MySQL 5.6 Command Line Client in the Start Menu. Following command line window will open: Enter the password as you have entered during the installation of MySQL community server 5.6.20. After entering the password, you can see the MySQL monitor: You can now type the SQL commands on the mysql> prompt. Now the next step is to create a database using the CREATE DATABASE command. CREATE DATABASE School; Once the database is created, you can check it in the list of databases that currently exist on the server by using the SHOW DATABASES command as shown below: In case you want to remove a database, this can be done by using the DROP DATABASE command. 20 Next step is to tell the server which database we will use for further statements. This can be done by using the USE command. Now you can create the Teacher table which would be associated with the School database. To verify you can use SHOW TABLES command which displays all the tables created in the current database. Database Constraints: DBMS can enforce several constraints for smooth operations on databases. These constraints can be specified while creating the table as shown below: 21 Database Management Applications a) NOT NULL: An attribute value may not be permitted to be NULL. For example, the First name of the Teacher cannot be NULL. Hence NOT NULL constraint can be specified in this case. CREATE TABLE TEACHER ( Teacher_ID INTEGER, First_NameVARCHAR(20) NOT NULL, Last_NameVARCHAR(20), Gender CHAR(1), Salary DECIMAL(10,2), Date_of_Birth DATE, Dept_No INTEGER ); b) DEFAULT : If a user has not entered a value for an attribute, then default value specified while creating the table is used. For example, if a teacher's salary has not been entered, then by default the database should store 40000 assuming that the minimum salary given to every teacher is ` 40000. This is illustrated as follows: CREATE TABLE TEACHER ( Teacher_ID INTEGER, First_Name VARCHAR(20) NOT NULL, Last_Name VARCHAR(20), Gender CHAR(1), Salary DECIMAL(10,2) DEFAULT 40000, Date_of_Birth DATE, Dept_No INTEGER ); In MySQL, if you want to look at the structure and description of the tables created, DESC command can be used. The description of the table Teacher created above is as follows: 22 c) CHECK: In order to restrict the values of an attribute within a range, CHECK constraint may be used. For example Dept_No of any teacher must not exceed 110. This can be specified as follows: CREATE TABLE TEACHER ( Teacher_ID INTEGER, First_Name VARCHAR(20) NOT NULL, Last_Name VARCHAR(20), Gender CHAR(1), Salary DECIMAL(10,2) DEFAULT 40000, Date_of_Birth DATE, Dept_No INTEGER CHECK (Dept_No 50000; Result: 33 Database Management Applications  First_Name Last_Name Dept_Name Tarannum Malik Puneet NULL Vinita Ghosh Vansh NULL Logical comparisons equal to (=), less than (), less than or equal to (=), not equal to() can be used in the WHERE clause. (c) Query: To display Teacher_ID,First_Name,Last_Name and Dept_No of teachers who belongs to department number 4 or 7. SELECT Teacher_ID,First_Name,Last_Name, Dept_No FROM Teacher WHERE Dept_No = 4 OR Dept_No = 7; Result: Teacher_ID First_Name Last_Name Dept_No 103 Surbhi Bansal 4 104 Megha Khanna 4 109 Neha Singh 7 Thus Boolean operations AND,OR can also be used in the WHERE clause. (d) Query: To retrieve names of all the teachers and the names and numbers of their respective departments. Note that the above query requires two tables – Teacher and Department. Consider the following query: SELECT First_Name, Last_Name, Dept_ID, Dept_Name FROM Teacher, Department; This query will result in a set in which the number of rows will be the number of rows in Teacher table (14) multiplied with number of rows in Department table (9), i.e. 126 rows. Since we have not specified any WHERE clause, this query will combine each row in Teacher table with each row of Department table resulting in Cartesian product of two tables. This is called as Cross Join. Now if we have to combine each teacher with his/her respective department, there should be a connecting column between the two tables which can be used to join them. Thus Dept_No (Teacher table) and Dept_ID (Department table) can be used to join the two tables. This type of join will result in joining rows from Teacher and Department table 34  based on the equality between Dept_No and Dept_ID columns. The query is as follows: SELECT First_Name, Last_Name, Dept_ID, Dept_Name FROM Teacher, Department WHERE Dept_ID=Dept_No; Here Dept_ID=Dept_No is the join condition. This would produce the following result: First_Name Last_Name Dept_ID Dept_Name Shanaya Batra 1 Chemistry Namit Gupta 1 Chemistry Tarun Mehta 2 Computer Science Vansh NULL 2 Computer Science Alice Walton 3 English Puneet NULL 3 English Surbhi Bansal 4 Hindi Megha Khanna 4 Hindi Tarannum Malik 5 Physics Divya Chaudhary 6 Commerce Neha Singh 7 Biology Saurbh Pant 8 Mathematics Sumita Arora 9 Economics Vinita Ghosh 9 Economics Now suppose we have to retrieve the similar details for the teacher in Chemistry department, the query would be: SELECT First_Name, Last_Name, Dept_ID, Dept_Name FROM Teacher, Department WHERE Dept_ID=Dept_No AND Dept_name=”Chemistry”; First_Name Last_Name Dept_ID Dept_Name Shanaya Batra 1 Chemistry Namit Gupta 1 Chemistry (e) Suppose the teacher and department table both had same names for the department number, say Dept_ID as shown below: CREATE TABLE Department 35 Database Management Applications  ( Dept_ID INTEGER PRIMARY KEY, Dept_Name VARCHAR (30) NOT NULL ); CREATE TABLE Teacher ( Teacher_ID INTEGER, First_Name VARCHAR(20) NOT NULL, Last_Name VARCHAR(20), Gender CHAR(1), Salary DECIMAL(10,2) DEFAULT 40000, Date_of_Birth DATE, Dept_ID INTEGER, CONSTRAINT TEACHER_PK PRIMARY KEY (Teacher_ID), CONSTRAINT TEACHER_FK FOREIGN KEY (Dept_ID) REFERENCES Department (Dept_ID) ); In such case, when the join condition is specified, there will be an ambiguity about which Dept_ID we are talking about. To resolve this problem, we have to prefix the name of the attribute with the relation name followed by a period as shown in the query below: Query: To retrieve names of all the teachers who belong to Hindi department. SELECT First_Name, Last_Name FROM Teacher, Department WHERE Department. Dept_ID=Teacher. Dept_ID AND Dept_Name="Hindi"; First_Name Last_Name Surbhi Bansal Megha Khanna Another method is to create aliases. Aliases are used to resolve ambiguity of the relations. They are created by using the keyword 'AS'. For example the above query can also be written as: SELECT First_Name, Last_Name FROM Teacher AS T, Department AS D WHERE D.Dept_ID = T. Dept_ID AND Dept_Name="Hindi"; Here T is an alias for Teacher table and D is an alias for Department table. We can also create alias to rename a column name as shown below: 36  SELECT First_Name AS Fname, Last_Name AS Lname FROM Teacher AS T, Department AS D WHERE D.Dept_ID = T. Dept_ID AND Dept_Name="Hindi"; Result: Fname Lname Surbhi Bansal Megha Khanna Note that the column names as displayed have been changed. (f) In SQL, duplicate tuples can appear more than once in a table and in the result of a query. However if the requirement is to list distinct values of an attribute then this can be done by using the keyword – 'DISTINCT'. For example, following query will list all the Department numbers corresponding to departments having male teachers. SELECT Dept_No FROM Teacher; WHERE GENDER ='M'; The result of the above query is: Dept_No 2 3 1 8 2 In the above result, 2 is appearing twice which is not required as the query is to find only the department numbers. Hence we can use DISTINCT keyword in the SELECT clause so that there is no repetition in the result. SELECT DISTINCT Dept_No FROM Teacher; WHERE GENDER ='M'; The result of the above query is: Dept_No 1 2 3 8 37 Database Management Applications (g) Sometimes it is required to match part of the string. This is called as string pattern matching. We can use 'LIKE' keyword along with two more reserved characters - % (percent) and _ (underscore) for specifying different number of characters. % replaces zero or more number of random characters and _ replaces a single character. Some examples: F Query: To retrieve names of all the teachers starting from letter 'S'. SELECT First_Name FROM Teacher WHERE First_Name LIKE "S%"; Result: First_Name Shanaya Surbhi Saurabh Sumita F Query: To retrieve names of all the teachers having 6 characters in the first name and starting with 'S'. SELECT First_Name FROM Teacher WHERE First_Name LIKE "S_ _ _ _ _"; Result: First_Name Surbhi Sumita F Query: To retrieve names of all the teachers having at least 6 characters in the first name. SELECT First_Name FROM Teacher WHERE First_Name LIKE "_ _ _ _ _ _%"; Result: First_Name Shanaya Surbhi Tarannum 38  Puneet Saurabh Sumita Vinita (h) Suppose it required to sort the result of a query based on some attributes. This can be achieved by using the clause – ORDER BY followed by the attributes which needs to be sorted. (By default the order is ascending) For ascending order the keyword ASC and for descending order the keyword DESC is used. By default the order is ascending. F Query: To list the names of teachers in alphabetical order. SELECT First_Name, Last_Name FROM Teacher ORDER BY First_Name, Last_Name; Result: First_Name Last_Name Alice Walton Divya Chaudhary Megha Khanna Namit Gupta Neha Singh Puneet NULL Saurabh Pant Shanaya Batra Sumita Arora Surbhi Bansal Tarannum Malik Tarun Mehta Vansh NULL Vinita Ghosh F Query: To list the names of all the Departments in the descending order of their names. SELECT Dept_Name FROM Department ORDER BY Dept_Name DESC; Result: 39 Database Management Applications  Dept_Name Physics Mathematics Hindi English Economics Computer Science Commerce Chemistry Biology F Query: To retrieve the names and department numbers of all the teachers ordered by the Department number and within each department ordered by the names of the teachers in descending order. SELECT First_Name, Last_Name, Dept_No FROM Teacher ORDER BY Dept_No ASC, First_Name DESC, Last_Name DESC; First_Name Last_Name Dept_No Shanaya Batra 1 Namit Gupta 1 Tarun Mehta 2 Vansh NULL 2 Alice Walton 3 Puneet NULL 3 Surbhi Bansal 4 Megha Khanna 4 Tarannum Malik 5 Divya Chaudhary 6 Neha Singh 7 Saurabh Pant 8 Sumita Arora 9 Vinita Ghosh 9 (i) To test whether a value is unavailable or unknown or not applicable in a column (i.e, NULL values), SQL allows us to test this condition using keywords IS NULL and IS NOT 40  NULL. Query: To retrieve all the details of those employees whose last name is not specified. SELECT * FROM Teacher WHERE Last_Name IS NULL; Result: Teacher_ID First_Name Last_Name Gender Salary Date_of_Birth Dept_No 107 Puneet NULL M 52500.00 1976-09-25 3 104 Vansh NULL M 53500.00 1982-05-04 2 (j) We can have another query in the WHERE clause of SQL query if the condition is based on the result of another query as shown below: Query: To retrieve the names of all the departments having female teachers. SELECT DISTINCT Dept_Name FROM Department WHERE Dept_ID IN (Select Dept_No FROM Teacher WHERE Gender = 'F'); Result: Dept_Name Chemistry English Hindi Physics Commerce Biology Economics The above query is a nested query (query within another query). The outer one is called outer query and the inner one is called as inner query. The inner query will return those department ID's which have female teachers and the outer query will retrieve the names of those departments respectively. We could have also written the above query by using JOIN condition as shown below: SELECT DISTINCT Dept_Name 41 Database Management Applications  FROM Department , Teacher WHERE Dept_ID = Dept_No AND Gender='F'; (k) Sometimes it is required to apply certain mathematical functions on group of values in a database. Such functions are called Aggregate Functions. For example retrieving the total number of teachers in all the Departments. Following are the commonly used built-in aggregate functions: F COUNT- It counts the numbers of tuples in the result of the query. F SUM – It finds the sum of all the values for a selected attribute which has numeric data type. F MAX –It finds the maximum value out of all the values for a selected attribute which has numeric data type. F MIN - It finds the minimum value out of all the values for a selected attribute which has numeric data type. F AVG – It finds the average value of all the values for a selected attribute which has numeric data type. Examples: F Query: To find total salary of all the teachers. SELECT SUM(Salary) AS Total_Salary FROM Teacher; Result: Total_Salary 203250.00 The above query finds the sum of all the values in the Salary column and the column is renamed via an alias as Total_Salary which is optional as it only enhances readability. F Query: To find the maximum and minimum salary. SELECT MAX(Salary) AS Max_Salary, MIN(Salary) AS Min_Salary FROM Teacher; Result: Max_Salary Min_Salary 54000.00 34000.00 42  F Query: To count the number of teachers earning more than Rs 40000. SELECT COUNT(Salary) FROM Teacher WHERE Salary > 40000; Result: COUNT (Salary) 9 The above query can also be written as follows: SELECT COUNT(*) FROM Teacher WHERE Salary >40000; The difference is in the use of asterisk (*). Asterisk symbol is used to count the number of rows in the result of the query. F Query: To retrieve the number of teachers in “Computer Science” Department. SELECT COUNT(*) AS No_of_Computer_Science_Teachers FROM Department, Teacher WHERE Dept_Name = "Computer Science"AND Dept_No=Dept_ID; Result: No_of_Computer_Science_Teachers 2 (l) We can also use arithmetic operators in the SELECT clause. For example, if we want to display Teacher name, current salary and a 10% increase in the salary for those teachers who belongs to Department number 4, the SELECT statement can be written as shown below: SELECT First_Name, Last_Name, Salary, Salary*1.1 AS New_Salary FROM Teacher WHERE Dept_No = 4; Result: First_Name Last_Name Salary New_Salary Surbhi Bansal 34000.00 37400.00 Megha Khana 38000.00 41800.00 43 Database Management Applications (m) Grouping based on an attribute can be done in SQL. For such grouping, GROUP BY clause is added in the SQL query. For example, we have to find the number of teachers teaching in each Department. Thus we have to group the result based on the Departments and for each Department we have to count number of teachers who teach in that Department. This query is written by using GROUP BY clause and aggregate function as shown below: SELECT Dept_No, COUNT(*) AS No_of_Teachers FROM Teacher GROUP BY Dept_No; Result: Dept_No No_of_Teachers 1 2 2 2 3 2 4 2 5 1 6 1 7 1 8 1 9 2 The above result can be enhanced if we display the name of Departments also as shown below: SELECT Dept_No, Dept_Name, COUNT(*) AS No_of_Teachers FROM Teacher, Department WHERE Dept_ID = Dept_No GROUP BY Dept_No; Result: Dept_No Dept_Name No_of_Teachers 1 Chemistry 2 2 Computer Science 2 3 English 2 4 Hindi 2 5 Physics 1 44  6 Commerce 1 7 Biology 1 8 Mathematics 1 9 Economics 2 Also it is important to note that the attribute used for grouping (Dept _No in the above query) must also be present in the SELECT clause. It is also possible to apply some condition on the group. This condition will not come under the WHERE clause, but in a new clause HAVING. For example, we have to find those departments which have more than one teacher. SELECT Dept_No, Dept_Name, COUNT(*) AS No_of_Teachers FROM Teacher, Department WHERE Dept_No=Dept_ID GROUP BY Dept_No HAVING COUNT(*) > 1; Result: Dept_No Dept_Name No_of_Teachers 1 Chemistry 2 2 Computer Science 2 3 English 2 4 Hindi 2 9 Economics 2 (n) A Select command can also result in an empty set. For example, retrieve the name of Teacher with ID=115. Since there is no such teacher in the Teacher table, following query results in an empty set. SELECT * FROM Teacher WHERE Teacher_ID = 115; Result: Empty set 45 Database Management Applications Exercise: Q1. Consider the following Employee table: Table Name: Employee Employee_ID Employee_Name Job_Title Salary Bonus Age Manager_ID 1201 Divya President 50000 NULL 29 NULL 1205 Amyra Manager 30000 2500 26 1201 1211 Rahul Analyst 20000 1500 23 1205 1213 Manish Salesman 15000 NULL 22 1205 1216 Megha Analyst 22000 1300 25 1201 1217 Mohit Salesman 16000 NULL 22 1205 The primary key of this table is Employee_ID and Manager_ID is a foreign key that references Employee_ID. Write SQL commands for the following: (a) Create the above table. (b) Insert values as shown above. (c) Delete the Employee having Employee_ID 1217. (d) Update the salary of “Amyra” to 40000. (e) Alter the table Employee so that NULL values are not allowed for Age column. (f) Write a query to display names and salaries of those employees whose salary are greater than 20000. (g) Write a query to display details of employees who are not getting any bonus. (h) Write a query to display the names of employees whose name contains “a” as the last alphabet. (i) Write a query to display the name and Job title of those employees whose Manager_ID is 1201. (j) Write a query to display the name and Job title of those employees whose Manager is “Amyra”. (k) Write a query to display the name and Job title of those employees aged between 26 years and 30 years (both inclusive) Q2. A Railway company uses machines to sell tickets. The machine details and daily sales information are recorded in two tables: 46  Table Name: Machine Field Data Type Machine_ID CHAR (3) Station CHAR (30) Table Name: Sales Field Data Type Machine_ID CHAR (3) Date DATE Tickets_Sold INTEGER Income DECIMAL (8, 2) The primary key of the table Machine is Machine_ID. Records in the table Sales are uniquely identified by the fields Machine_ID and Date. (a) Create the tables Machine and Sales. (b) Write a query to find the number of ticket machines in each station. (c) Write a query to find the total ticket income of the station “New Delhi” of each day. (d) Write a query to find the total number of tickets sold by the machine (Machine_ID = 122) till date. 47 Database Management Applications  Unit - 2: Operating Web 2.1 Operating Web-based Applications An application that can be accessed via a web browser, over a network connection is called a web-based application. A web-based application provides the convenience of availability 24x7 across the world. Nowadays, web-based applications are used for reservation of tickets and bookings, e-governance, online shopping with the provision of making payments online using credit/debit cards, and learning using online tutorials, tests etc. They allow access to the facilities via the Internet from the comfort of home, office, car, etc. The web-based online applications can be broadly categorized into applications that require financial online transactions and applications that provide information and allow interaction via filling forms, posting query, viewing information, sending email or chatting. The online reservation system, online shopping, and bill payments fall in the first category and e- governance and online tutorial and tests fall in the second category. In this chapter, we discuss both kinds of applications. 2.2 Online Reservation Systems An online reservation system allows booking /cancellation of tickets through use of Internet. A user can retrieve information and perform transaction related to reservation of ticket. Some examples of online reservation systems are : F Airline ticket F Railway ticket F Bus ticket F Movie ticket F Hotel booking F Car rental F Tour and travel booking Such reservation systems store information and allow the users to retrieve the information, for example, the information about all the flights from Delhi to Mumbai like, timings, prices, air flight provider (Air India, Spice jet etc.) and availability of seats. A user who wishes to fly from Delhi to Mumbai can check the information about the flight timings, rates, seat availability, etc., and book a ticket. 2.2.1 Advantages of Online Reservation System Online reservation systems are advantageous both for the company providing reservation services and for the consumer who uses the online reservation system for booking. We discuss the advantages of the online reservation system : Advantages for consumers F Convenient: The user/consumer can book tickets anytime anywhere - from home, 48  office, while travelling, etc. All the user needs is just a computer, Internet access, and a card (credit, debit, etc.) for payment. The user saves time (going physically to a booking office in the defined timings, finding parking for car, etc., standing in a queue in booking office) and energy. Making changes to bookings like, cancellation, upgradation is also easy. It is very convenient for the users with special needs. F Price Comparison: In this competitive world, a service may be provided by several providers - cinema tickets on Wave cinema, PVR, Big Cinema etc.; online booking allows the customer to check the prices, compare them and get the best deal. Several websites which provide the comparisons across similar service providers exist. For example, www.makemytrip.com provides comparative pricing of the air ticket providers from a particular source to a destination Figure 2.2(a). This is in contrast to manual system where a user is made to book his/her ticket for the airline that the travel agent recommends. Figure 2.2(a): makemytrip.com showing ticket options for different airlines F Security: Most online reservation sites require the user to create their profile and provide them with a username and password. The information thus remains secure until their password is secure. F Confirmation of Reservation: When a booking is done, the confirmation of the booking just takes a few seconds. It is fast and secure. The confirmed booking of your booked ticket can be viewed on your mobile, iPad, iPhone, laptop, etc., and can also be printed. F Making Choices: In online booking, the user can see the arrangement of seats, select a particular seat, etc. In railway booking, selection of lower berth/upper berth, 49 Database Management Applications  type of food required and booking for beddings is possible. You can read reviews posted by people when making choices. F Time Saving: Online reservation consumes very less time in contrast to long queues at the counter. F Discount Scheme: The users can avail the special discount schemes provided by the service provider like, season discount, advance booking discount and frequent user discount. However, for using the online reservation system, it is important that the user must have the understanding of using the computer for reservation. Advantages for Providers: The organization or the company providing the online reservation facility to their users also has several advantages : F Requires Less Staffing: Less man-power is required as every task is done through computers. F No Spatial Restrictions: The organization which is providing online reservation may be operating from the basement of their house, a garage, an office space in a small building, an office in a large commercial mall, or an office from their own building. The physical location of the office does not really matter to a customer who is reserving tickets online. So, the organization, big or small, can choose their office space depending on their needs. Online reservation also reduces the bills for office rent, electricity, etc. although initial costs need to be incurred on setting up the website and maintaining it. F Global Access: Online reservation is available to anyone irrespective of their physical location. The scope of access to the online reservation becomes large as anyone who has access to the Internet can search for the reservation site and book the tickets. 2.2.2 Precautions while Performing Online Transactions Internet transactions are susceptible to frauds. The precautions to be taken are as follows: The combination of username and password is the most common method of user identification and authentication. There are several security issues with the use of password. An unauthorized access to an account through stealing/guessing a password can get access to the system and a simple password can be easily cracked. Some actions that can be taken to make the passwords safer are as follows: F Make a password complex, like mix case, use numbers and special characters. This decreases ability of automated attacks by trying different character combinations. F Be cautious not to leave passwords around and do not share them with friends. F Never use names of near and dear ones as passwords. 50 2.2.3 Using Online Reservation Systems For using any online reservation system, we need to do the following: F Open the online reservation website: On your machine having an Internet connection, open a web browser like Internet Explorer, Google's chrome to open a website. 2 If you know the address of the website then type the address in the address bar, or 2 If you do not know the address, open a search engine like google.com or bing.com and search for an online reservation website. Get the address of the required site. For example, on the Google Search, you can search for the Air India ticket reservation site Figure 2.2(b) Figure 2.2(b): Google search results for air India ticket booking F Open the website F Browse and search for the required information F Use the website for reservation. 2.2.4 Case Study: Book Rail Ticket Here, we will learn online booking of train tickets using the Indian Railway Catering and Tourism Corporation (IRCTC) online portal. 51 Database Management Applications F Check you have a web browser and an Internet connection on your computer. F Open the browser and type the website address of IRCTC i.e. https://www.irctc. co.in/ in the address bar. Press. F A web page opens, as shown in Figure 2.2 (c) Figure 2.2(c): Home page of IRCTC website F For booking a ticket, you need to have an account on the IRCTC website. If you already have an account in IRCTC, then type your username and password and login. If you are not an existing user, you need to create your account. You need to sign up on the IRCTC website, to perform any transaction. F Create New Account 2 Locate and click link. An individual registration page opens, as shown in Figure 2.2(d). 2 Read the instructions and fill the registration form. The fields marked with a star (*) are mandatory. 2 After filling the form, click the button. 2 Once your account has been created, now you can perform transactions from your account. 52  Figure 2.2(d): Individual Registration Form 2 Once you have created your account you can login to the site using your username and password. 2 After you login, you can see your page with your name displayed on the right side of the web page Figure 2.2(e). 2 Once your account has been created, now you can perform transactions from your account. F Book a Ticket 2 On the Plan My Travel box on the left side of the page, enter the details, i.e. the start station, end station, date on which you want to travel, ticket type (e-ticket) and your quota. Next, click on. 2 A list of available trains with information about availability of tickets is displayed Figure 2.2( f). Figure 2.2(e): User's page 53 Database Management Applications  Figure 2.2(f): Trains available from source to destination on selected dates 2 You can see the train number, train name, departure time, arrival time at the destination, category of the seats allowed in the train, like, 1A, 2A, 3A, SL (Sleeper) etc. A cross (x) mark depicts non availability of that category in the specific train. 2 Select the category of the seat for a train. A screen appears which shows the availability of seats in the selected train and selected category, for the date requested by you and a few days ahead Figure 2.2 (g). It also shows the ticket fare. 2 After you have checked the fares and you are ready to book a ticket, click on. 2 A page as shown in Figure 2.2(h) a form appears. Enter the detail of the persons for whom you are booking the tickets, like, name, age, sex, berth/seat preference (lower, middle, upper), and check the box, if you are a senior citizen. 2 Once you have filled in the details press , the page showing your booking details is displayed Figure 2.2 (i). 2 Now, you are required to make payment for the ticket Figure 2.2(i). You can make payment using credit card, debit card, etc. Once payment is done, you can view your ticket. 2 An electronic copy of your paid ticket is shown on the screen. Also, your ticket will be e-mailed to you at the email address you have specified in the registration form. 54 Figure 2.2(g): Ticket availability for selected train and category Figure 2.2(h): Form to fill the passenger details Figure 2.2(i): Ticket details with passenger details 55 Database Management Applications  Figure 2.2(j): Make payment for the reserved ticket 2.3 E-Governance E-Governance or electronic governance is the use of electronic i.e. information and communication technology (ICT) tools for governance by the government for the citizens. The ICT tools include the Internet, Local Area Network (LAN), mobiles, etc. The use of ICT facilitates providing access to information for the citizens by the government. The government has set up ICT enabled services, like, registration of birth/death certificate, booking of railway tickets, RTI application submission, etc. E-governance empowers the citizens socially and economically, and enhances their lives by providing them with quality information and better services. It enables citizens to access information directly, without paying any money to a middleman or a tout. It ushers transparency in the system. The services of the e-governance portal can be broadly classified into two broad categories : to provide information and to provide online services. 2.3.1 Initiative In India, the main thrust for e-Governance was provided by the launching of NICNET in 1987 - the national satellite-based computer network. This was followed by the launch of the District Information System of the National Informatics Centre (DISNIC) program to computerize all district offices in the country for which free hardware and software was offered to the State Governments. NICNET was extended via the State capitals to all district headquarters by 1990. In the ensuing years, computerization, tele-connectivity and internet connectivity led to establishment of a large number of e-Governance initiatives, both at the union and state levels. The formulation of National e-Governance Plan (NeGP) by the Department of Electronics and Information Technology (DEITY) and Department of Administrative Reforms and Public Grievances (DAR&PG) in 2006 has boosted the e-Governance process. 2.3.2 E-Governance Sites Some of the Central initiatives for e-governance include: F National e-Governance Plan (NeGP) 56 F National e-Governance Division (NeGD) F e-Governance Infrastructure F Mission Mode Projects F Citizens Services F Business Services F Government Services F Projects and Initiatives F R&D in e-Governance Several state governments have also taken e-governance initiatives in the area of agriculture, commercial taxes, district, employment exchange, land records, municipalities, gram panchayats, police, road transport, treasuries, etc. Some of the key e-governance sites of India are as follows: 1. india.gov.in (The National Portal of India) : This is the National Portal of India, which provides a single window access to information and services being provided by the various Indian Government entities Fig. 2.3 (a), (b). The content in this portal is the result of a collaborative effort of various Indian government ministries and departments, at the central/state/district level. This portal is Mission Mode Project under the National E-Governance Plan, designed and maintained by National Informatics Centre (NIC), DeitY, MoCIT, Government of India. Figure 2.3(a): india.gov.in website 57 Database Management Applications 2. goidirectory.nic.in (Government of India Web Directory) : This portal is a one- point source to access all Indian Government websites at all levels and from all sectors. It includes sites of different states and union territories of India, sites of government departments like judiciary and legislature. It also provides information about the various sectors like education and agriculture Figure 2.3 (c), (d). Figure 2.3(b): india.gov.in website Figure 2.3(c): goidirectory.nic.in website 58  Figure 2.3(d): Sectors included in goidirectory.nic.in The e-governance initiatives in India are discussed at http://india.gov.in/e-governance. 2.4 Online Shopping and Bill payments Online shopping is an e-commerce application where the customer can purchase goods over the Internet. There are several websites where the customer can go for online shopping. The customer can purchase all kinds of items, like, books, TV, mobile phones, dresses, shoes, cosmetics, jewellery and greeting cards. The customer can view the goods that are displayed along with the details of the goods. The customer can select the goods to be purchased and store them in their online shopping basket. The payment for the goods is to be made using debit or credit card. The goods are then delivered at the address specified by the customer. 2.4.1 Benefits of Online Shopping Online shopping is useful in many situations. Here, we discuss some of the situations : F The customer does not have enough time to visit a store and purchase goods. F The store where you will get what you need may be very far off. F The money spent in travelling to the store, parking the car, etc., is much more than overhead if any in online shopping. F The product you may require is not available at your market. F You want to send a product to your friend; you can do online shopping and provide the receiver's address. You save your time and energy for delivery of the product to your friend's house located in a different city. F You do not have time during the shopping hours. You can do online shopping from your office, home, a friend's place, anywhere, any time. Thus there are no fixed shopping hours. 59 Database Management Applications  F Online shopping also has bargain offers for the customer delight. F Some online stores allow customers to rate their services and the items. This can be used to know the quality of service an online store is providing you. You can also know about the popularity of the item you are purchasing. Since online shopping involves use of credit card or debit card for payments, and there is a need to create a user account. The precautions to be taken when doing online shopping are same as those required during online reservation as discussed in the earlier sections. 2.4.2 How it Works The general outline of the working of an online shopping site is presented here. This may vary for different sites and can be customized for different users. The steps for online shopping are as follows: F Open the shopping site, from where you want to shop online. F If you just want to see the products and not buy any, you can simply browse the site F If you wish to buy goods, then you may need to create your user account. It requires filling in a registration form with your details along with a user name and password. For a site, the user account needs to be created only once. Next time, you can simply login with your username and password. Some shopping sites may allow you to make a purchase without creating an account, or allow you to logon using your facebook or google account. F To buy products, login with your user name and password. F Just like you have a shopping basket when you physically go to a shop, online shopping stores also provide you with a shopping cart or basket. You can select the products that you wish to buy and add them to your shopping cart. F Having selected your products, you can view what you have selected and the total amount to be paid by you. F You may delete any product from the shopping cart, or proceed for payment. F Read the terms and conditions before making the payment. F Some examples of online shopping stores : 2 Flipkart for buying products in categories like movies, music, games, mobiles, cameras, computers, healthcare and personal products, home appliances and electronics, stationery, perfumes, toys, apparels, shoes, books, cosmetics and mobiles. 2 Ebay for buying and selling products in categories like, movies, music, games, mobiles, cameras, computers, healthcare and personal products, home appliances and electronics, stationery, perfumes, toys, apparels, shoes, books, cosmetics and mobiles. 60  2 Some Other Sites are: yebhi.com, myntra.com, ibibo.com, snapdeal.com and infibeam.com Figure 2.4(a). Figure 2.4(a): Some online shopping sites 2.4.3 Bill Payments Online shoppers generally make payments using their debit card or credit card. However, different websites enable the user to make payments via alternative methods like net banking, e-gift voucher, cash on delivery and wire transfer. Always read the terms and conditions while making the payments. Bill payments can also be made for several government services like electricity bill, water bill, property tax and income tax payments. 2.4.4 Case Study: Online Shopping Using an Online Shopping Website In this section, we shop at flipkart.com to demonstrate how online shopping can be done on a typical shopping site. For demonstration purpose the flipkart site is chosen but there is no preference for the same. The steps to be performed are as follows : F Check you have a web browser and an Internet connection on your computer. F Open the browser and type the website address of Flipkart i.e. https://www.flipkart. com/in the address bar. Press. Alternatively search for the website using a search engine like google and click on the link. F A web page opens, as shown in Figure 2.4 (b). 61 Database Management Applications F For doing any kind of transaction, there is a need to open a user account. F Click on the Sign up link. Window opens Figure 2.4(c). F Provide your email Address which will be used as your login name. Enter a password. Make sure you enter a strong password. Re-enter the password. F Click Sign Up Now. F If you have been successfully signed up, a new window opens Figure 2.4(d). F On clicking on your login name, you can see the folders available to you, like, My account, My orders and My wallet Figure 2.4(e). Figure 2.4(b): Flipkart: Home page Figure 2.4(c): Flipkart: Signup page 62  Figure 2.4(d): Flipkart: User logged in Figure 2.4(e): Flipkart: Options for logged in user F The products in Flipkart are organized in categories like, Clothing, Footwear, Books & e-books, Mobiles and Tablets, Cameras. 63 Database Management Applications F If you want to buy a mobile, bring your cursor on the Mobiles & Tablets, a window pops up Figure 2.4(f). You can choose your preference, like model, pricing and type. F You can select different models of the product and compare them. For this, select the model and click on. Once you have selected, click on. You can now view the comparison of the selected models Figure 2.4(g). F The catalog can be browsed to purchase the product. Once you have decided on the item to be purchased, click on Buy Now. The product gets included in your shopping cart Figure 2.4(h). F You can proceed to select more products before making the payment. Having added all the products you want to purchase, review your shopping cart. Then click on. Figure 2.4 (f): Flipkart: Electronics Figure 2.4(g): Flipkart: Compare Products 64  Figure 2.4(h): Flipkart: Shopping Cart F For making payments, Flipkart allows different modes of payment - credit card, debit card, Net Banking, Cash on Delivery or e-gift voucher. Click on the method using which you want to make payment. Enter the details as required in various fields. Click to make the payments. To complete the transaction, appropriate windows will be displayed based on the procedure to be followed for the payment. 2.5 Online Courses, Tutorials and Tests Online courses and tutorials help the user in learning like using software, video game, or a lesson. In online tutoring, the students and the teachers are at physically different locations, connected to each other via Internet. Online learning has many benefits. It provides freedom to the learner to learn at their convenient location, convenient time and at their own pace of learning. Also, there is no discrimination of age, caste, gender, background, or the qualification of the learner. The tutorials may be enriched with audio and video information, which makes learning more enjoyable and attractive. Also, tutorials and tests are available for almost all topics, sometimes in regional languages also. The tutorials may require different kinds of interaction with the user, for example, F Video/slide tutorials where the video or slides of lectures are available. The learner can visit the site and view the tutorials. F Interactive tutorials require the user to follow the instructions, perform the desired action and get a feedback. F Webinars are real time lectures, where users participate in the lecture, may ask questions, and get their queries answered. 2.5.1 Online Educational Sites There exist several sites for online learning like,