Chapter 2: Online Services PDF

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 applic...

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, http://www.khanacademy.org/, http://www.eagetutor.com/. 1. Khan Academy is an educational website created in 2006 by educator. The website 65 Database Management Applications  hosts video tutorials on different subjects, like, mathematics, history, physics, chemistry, civics, and economics. The learner can view the video lectures at their convenience Figure 2.5(a). Figure 2.5(a): Khan Academy website 2. udacity.org is a site which offers online courses for development of technical skills. It claims to provide projects built by technical leaders like Google and AT & T. The site provides verified certificates for a fee Fig. 2.5(b). Figure 2.5(b): Udacity website 3. coursera.org and edx.org provide high quality online courses for free, in collaboration with various universities across the globe. They aim to provide free online education through its partners world wide. The courses include topics from hunanities, science, and engineering including courses at school level. These sites provide verified certificates for several courses for a fee Figure 2.5(c). 66  Figure 2.5(c): Coursera website 4. w3schools.com offers free tutorials in all web development technologies like HTML, CSS, Javascript, PHP etc. Figure 2.5(d). Figure 2.5(d): w3schools.com - Website 5. GCFLearnFree.org creates and provides quality, innovative online learning opportunities to anyone who wants to improve the technology, literacy and math skills needed to be successful in both work and life. It has 750 different lessons provided absolutely free. GCFLearnFree.org is a worldwide leader in online education. 67 Database Management Applications 6. http://www.ncert.nic.in/index.html is a NCERT portal that provides online learning resources in the form of e-books, journals, question papers, children books, etc. Fig. 2.5(e) Figure 2.5(e): ncert website In addition to learning, there are several sites that allow the students to test their knowledge. These websites hosts tests in different subjects. The learner can take a test and get evaluated. For example, http://www.british-study.com/quizzes/grammar- test.php fig. 2.5(f) is a site that allows a learner to test their English grammar, for free. http://www.pearsonvueindia.com/ lsatindia/overview.html#2 is site for standardized test of reading and verbal reasoning skills designed by the USA-based Law School Admission Council (LSAC) for use by law schools in India. This is a paid site Figure 2.5(g). Figure 2.5(f) 68  The LSAT – India is a test of reasoning and reading skills, not a test to see whether you happened to have memorized the right facts or equations. The theory behind the LSAT – India is democratic and inclusive. It holds that students acquire critical thinking skills over their educational lifetimes, and that these skills are the most important for the study of law. Good critical thinking skills may be acquired in Figure 2.5(g): Some Testing sites 2.6 Project Management - Web Based Application Development Web based application development involves the process of developing web applications. A web application is a program that is stored on a remote server. It is online and is delivered over the Internet through a web browser interface. A web application may be of different kinds, like, a quiz, a game, or a bill calculator. A project is a task that is undertaken to create a unique product, service, or result. Some of the key characteristics of a project are as follows: F A project has a beginning and an end. The extent of the project is defined. It has boundaries. F A project requires finite resources that are required to complete the project. F A project has a specific time frame. It has a definite beginning and end dates. F A project is complete when its end objectives are achieved. The objective is specific and identifiable. Project Management is the process of achieving the objectives of the project through a set of activities defined within the time frame, to produce the end result. It is the application of knowledge, skills, tools, and techniques to project activities to meet the project requirements. 2.6.1 Project Essentials and Tips We have learnt about so many different web applications in the previous sections. In this section, a structured and simplified process is described for the development of web based applications. Let us discuss how the web applications are created. 69 Database Management Applications 2.6.1.1 Phases in a Web Application Project The process of development of web based applications is broadly categorized in four phases. During the development process, one needs to follow the following four main phases of development: 1) Requirements Definition Phase 2) Design Phase 3) Implementation Phase 4) Testing Phase Additionally, for all these phases there is a Planning and Monitoring phase. A project plan lists the activities that are required to be monitored during project execution. For each activity information useful for its monitoring is defined, like, duration, costs, schedule, resources required, and risk involved. Requirement Design Implementation Test Definition For developing web based applications, we discuss the steps to be performed during the four phases of development as follows: 1) Requirement Definition Phase: In this phase we identify the problem statement for which the web application is to be developed. Identify the scope of the problem. The following issues need to be addressed in this phase. (a) Feasibility: Is the project feasible? (i) Check whether the project is technically feasible (is it possible to do it?). (ii) Check whether the project is economically feasible (is it profitable?). (iii) If feasible, proceed further, otherwise there is no need to proceed with the project. (b) Scope: The focus is on "what" the application must do. To define the scope of the application, (i) Compile a detailed list with a clear description of application features. (ii) Establish the goals that the solution must achieve. (iii) Recognizing the limitations that are placed on the project. 2) Design Phase: The focus is on "how" the application is to be designed. This consists of design of: (a) A Map of the Web Application Site: It contains essential information about the structure of the site - the pages and the relationships between them. Thus, we 70  have a map with pages and a path connecting them. We have to simply implement the pages and follow the path to connect them. (b) Database: Design the application database. (i) Identify the database tables that will be required in the application. (ii) Decide the table structures: For each table we need to identity the attributes of the tables, their data types, the size of the columns in the tables, the relationship between tables. (c) Page Structure: Design the structure of page. Identify the main sections of the page like header, main content and sidebar. 3) Implementation Phase: Create backend database, frontend and the connectivity between them. a) Backend Database: Create the database and write SQL code defining tables, attributes and relationships, as per the requirement of the application. b) Frontend: Develop the frontend of the application as per the requirement. Use the Page Structure and the Page Layout you defined in Design phase to implement the frontend code. Identify the inputs that are required to be taken from the user in the Front-End of the application. Decide the kind of buttons you will provide to the user, to minimize the user's typing effort, like, Radio buttons, checkbox, list and combo box. c) Data Connectivity: Establish the data connectivity between the Front-End interface and Back-End Database. 4) Testing Phase: Test the complete application (Front-End and Back-End) with multiple sample sets of data. Find all the application bugs and fix them. Test each page of the application and test each feature of the application. Since fixing an error involves modifying the code, validate the modified code also. After final validation, the application is ready for release. We shall now discuss three case studies that will use the project management steps of the web based application development. 2.6.2 Case Study - Online Game First, let us define the problem statement for the online game. Here, we create an online game called Hangman. It is a simple guessing game where a player has to guess the word which may belong to a category like movie or country. Let us create this game. Problem Statement: Hangman is a popular word game. In this game, a player selects a category like country, capitals, animals, birds and movie names. The game displays some number of blanks to the player. The player starts guessing the letter to fill in the blanks and guess the correct word. This game gives seven chances, for guessing to a user. 1. Requirement Definition: The problem statement is identified. 71 Database Management Applications  a) Feasibility: The students are encouraged to discuss in groups and identify the technical requirements for this project Evaluate whether it is feasible to implement within the scope of resources available in school. The following steps are based on the assumption that the project is feasible. b) Scope : The application must do the following: (i) Ask the player to select a category. (ii) The game selects a word from that category randomly and displays the appropriate number of dashes and spaces to represent the phrase (depending on the number of letters in the word) to the player. (iii) The player makes guesses. The guess is either a hit (successful) or a miss (fail). If it is success, the letter is written in the dash appropriately. The program should allow the user to make a total of seven guesses. 2. Design: a) A Map of the Web Application Site: The students are encouraged to design structure of the sets by identifying the pages for this site. Determination of relationships between these pages will be crucial in design of website, for example, a page will be designed for front end interface containing leads to other pages. Implementation will follow this design structure. b) Database: It is decided to create the following tables in the database, along with the attributes of the tables, their data types, the sizes of the columns in the tables: F Category F Words_in_Category TABLE: Category S. No. Name Type Remarks 1 Category_Id Varchar (10) Unique id for each category 2 Category_Name Varchar (20) Name of the category TABLE: Words_in_Category S. No. Name Type Remarks 1 Category_Id Varchar (10) Unique id for each category 2 Word Varchar (15) Words for the category c) Page Structure: Design the structure of page It is found that the inputs required from the user are as follows: F Selection of Category for the game F Guessing the letters 72  The following front-end interface can be designed. Select Category for the Countries Animals Fruits Vegetables Capitals __ __ __ __ Guess Misses Word A_ __ __ __ __ __ __ Guess S Misses H, T 3. Implementation: Create backend database, frontend, and the connectivity between them. 4. Test: Having implemented the complete application, test the application with random data. Test each feature and functionality of the application. Fix the bugs, if found and retest. 2.6.3 Case Study - Online Quiz Let us now define the problem statement for an online quiz. The pen-paper method of quiz is time consuming, less flexible and involves a lot of human effort in terms of correction, generating results, searching records, result analysis, etc. To solve these problems, a web-based online quiz is to be created. Problem Statement: The online quiz web application allows a number of students to take the quiz at a time, displays the results as the test gets over. The result is generated automatically. A first time user has to register to get a login and password. The user then uses this login and password to take the test and also to see the results. Once logged in, the user can select the category for which to take the quiz. The user then attempts the quiz. When the quiz is complete, a report is generated based on the correct answers. The Administrator has the privilege to create, modify and delete the quiz questions. 1. Requirement Definition: The problem statement is identified. a) Feasibility: The students are encouraged to discuss in groups and identify the 73 Database Management Applications  technical requirements for this project and evaluate whether it is feasible to implement it within the scope of resources available in school. The following steps are based on the assumption that the project is feasible. b) Scope: The application must do the following- (i) Allow a user to register (ii) Provide login and password to the user (iii) Allow user to select a category of quiz (iv) Display the quiz questions for the selected category, to a valid user (v) Generate a report to provide result of the quiz. (vi) Allow administrator to create, modify, and delete questions. 2. Design: a) A Map of the Web Application Site: The students are encouraged to design structure of the sets by identifying the pages for this site. Determination of relationships between these pages will be crucial in design of website, for example, a page will be designed for front end interface containing leads to other pages. Implementation will follow this design structure. b) Database: It is decided to create the following tables in the database, along with the attributes of the tables, their data types, the sizes of the columns in the tables: F Admin_Login F Quiz information (Here, we take two subjects) F User_Info F Result ADMINISTRATOR TABLE: Admin_Login S.No. Name Type Remarks Administrator username for login 1 Username Varchar (10) (Primary Key) 2 Password Varchar (10) Administrator password for login TABLE: Quiz Info S.No. Name Type Remarks 1 Question_No Number (10) The question number Subject name, e.g. English, 2 Subject Varchar (10) Maths 3 Question Varchar (40) The quiz question 74  4 Option 1 Varchar (20) 1st option of quiz answer nd 5 Option 2 Varchar (20) 2 option of quiz answer rd 6 Option 3 Varchar (20) 3 option of quiz answer th 7 Option 4 Varchar (20) 4 option of quiz answer 8 Answer Varchar (10) Correct answer from option1-4 TABLE: User_Info S.No. Name Type Remarks Name of user for login purpose 1 Username Varchar (10) (Primary Key) 2 Password Varchar (10) User password for login 3 Name Varchar (10) User name 4 Email_id Varchar (10) User email_id 5 Gender Varchar (10) User gender 6 Dob Varchar (10) User date of birth TABLE: Result S.No. Name Type Remarks 1 Username Varchar (10) Name of user for login purpose 2 Marks Number (10) Marks scored by user 3 Category Varchar (10) Quiz category which user attempted c) Page Structure: Design the structure of page It is found that the inputs required from the user are as follows: F Username and Password F Selection of Category for which quiz is to be attempted F The quiz is displayed to the user. The user selects the answer for each question. The following front-end interface can be designed. Enter User Name Enter Password 75 Database Management Applications  User Name Category of Quiz Question 1 The question is displayed here The option 1 answer The option 2 answer The option 3 answer The option 4 answer Report Word __ __ __ Name Login Quiz Category Marks 3. Implementation: Create backend database, frontend, and the connectivity between them. 4. Test: Having implemented the complete application, test the application with random data. Test each feature and functionality of the application. Fix the bugs, if found and retest. 2.6.4 Case Study - Online Bill Calculator Let us now define the problem statement for the online bill calculator. The proper record keeping and reporting is need of the hour. The interface should be interactive, and easy to use. To provide user with a facility to calculate the bill online, a Web-based online bill calculator is to be created. Problem Statement: The online bill calculator calculates bill for an individual user. It allows a user to enter the bill details. A first time user has to register to get a login and password. Once the user is logged in, the user can enter details of the bill and the bill is generated. The Administrator has the privilege to add, edit, or delete a user account. 1. Requirement Definition: The problem statement is identified. a) Feasibility: The students are encouraged to discuss in groups and identify the technical requirements for this project evaluate whether it is feasible to implement it within the scope of resources available in school. The following steps are based on the assumption that the project is feasible. b) Scope: The application must do the following: (i) Allow a user to register 76  (ii) Provide login and password to the user (iii) Allow entry of user information (iv) Allow entry of bill information (v) Display the generated bill. (vi) Allow Administrator to add, edit or delete a user. 2. Design: a) A Map of the Web Application Site: The students are encouraged to design structure of the sets by identifying the pages for this site. Determination of relationships between these pages will be crucial in design of website, for example, a page will be designed for front end interface containing leads to other pages. Implementation will follow this design structure. b) Database: It is decided to create the following tables in the database, along with the attributes of the tables, their data types, the sizes of the columns in the tables as shown below: F User_info F Bill_info TABLE: User_Info S.No. Name Type Remarks Name of user for login purpose 1 Username Varchar (10) (Primary Key) 2 Password Varchar (10) password for login 3 Name Varchar (20) Name of user 4 Email_id Varchar (40) Address details of user 5 City Varchar (20) City of user 6 State Varchar (20) State of user 7 Country Varchar (20) Country of user 8 Pin_Code Number (10) Pin code of city 9 Mobile_Number Number (15) Mobile number of user TABLE: Bill_Info S.No. Name Type Remarks Name of user for login purpose 1 Username Varchar (10) (Primary Key) 2 Name Varchar (20) Name of customer 77 Database Management Applications  3 Address Varchar (40) Address details of customer 4 City Varchar (20) City of customer 5 State Varchar (20) State of customer 6 Country Varchar (20) Country of customer 7 Pin_Code Number (10) Pin code of city of customer 8 Month_Dispatch Number (5) Month of dispatch of material 9 Duration of Bill Number (5) Bill duration in days 10 Mobile_Number Number (15) Mobile number of user c) Page Structure: Design the structure of page It is found that the inputs required from the user are as follows: F Username and Password F Interface to select the option of generating or submitting a bill F Interface to add a user F Interface to add Bill information The following front-end interface can be designed. LOGIN PAGE ADD A NEW USER ACCOUNT USERNAME PASSWORD submit reset search Online Bill Calculator GENERAL BILL SUBMIT BILL USER NAME PASSWORD CONFIRM PASSWORD NAME ADDRESS 78  CITY STATE COUNTRY PINCODE PHONE NUMBER submit reset cancel NAME ADDRESS CITY STATE COUNTRY PINCODE PHONE NUMBER MOBILE NUMBER MONTH OF DISPATCH DURATION OF BILL AMOUNT TAX TOTAL AMOUNT LAST DATE WITH FINE FINE 3. Implementation: Create backend database, frontend, and the connectivity between them. 4. Test: Having implemented the complete application, test the application with random data. Test each feature and functionality of the application. Fix the bugs, if found and retest. 79 Database Management Applications Exercise: Q1. Analyse the following scenarios and describe all the steps of the project development cycle: (i) Online tutoring (ii) Online matrimonial bureau (iii) Online ticket booking Q2. Explore the following sites for online shopping: FAmazon.com FHomeshop18.com FEbay.in 80