Code 402 Class 10 MS Office Base

Absolute and relative hyperlinks Hyperlinks connect one document with another. A piece of text or image works as hyperlink, clicking on which fetches the linked document. Imagine a scenario where Mr. Ken has spreadsheets: summary.xls and details.xls. summary.xls is on the following path on his computer: D:\\myprojects\\project20\\reports\\summaries\\summary.xls summary.xls has a text string: Click for details which is a hyperlink to: D:\\myprojects\\project20\\reports\\details\\details.xls This path is called absolute hyperlinking wherein entire path to the linked document is mentioned. Suppose, Mr. Ken sends reports folder to Mr. Ben who copies it on his computer in drive E: so, the path to these files on his computer will be: For summary.xls : E:\\reports For details.xls : E:\\reports\\details Can you see the problem? When Mr. Ben will click on the hyperlink, the application will look for D:\\myprojects\\project20\\reports\\details\\details.xls, which was an absolute (fixed) path used for hyperlinking, will not find it and display an error. To prevent such situations when you need to distribute linked documents to others, relative hyperlinking must be used. Now, let us understand relative hyperlinking. Notice the common path to both the files: D:\\myprojects\\project20\\reports. This is called Base address. So, if the source file (that contains the hyperlink) and the linked file are sharing a common path, then hyperlink can be created by omitting the base (common) path. This is called relative hyperlinking. Therefore, in above example, relative hyperlink address in summary.xls will be details\\details.xls. When user clicks on a relative hyperlink, application looks for the path from the current folder onwards and not from the very beginning (root) of the path. This makes distribution of hyperlinked files easier. In this case, folder details is always going to be under folder reports so, no matter what, if you copy folder reports anywhere on any computer the relative path is not going to change (of course, unless user deliberately changes the paths, rename the folders or files to unnecessarily create problem in accessing linked files). Hyperlink (path, text): Hyperlink function is used to create hyperlink in a spreadsheet. It takes 2 parameters: first, the path to the linked file which could be absolute or relative and, second, the hyperlink text. This example is creating the text SEE DETAILS HERE hyperlink in cell A1 to an absolute path. This example is creating the text SEE DETAILS HERE hyperlink in cell A1 to a relative path. Note: Hyperlinks can also be created using Hyperlink option in Insert tab and selecting the target document in Hyperlink dialog box. Electronic Spreadsheet (Advanced) 100 Information Technology NSQF Level-2

Internal linking using bookmarks You have seen that generally hyperlinks are used to connect with external data i.e. other documents. Many times we need to connect various sections of a document itself. For example, in a spreadsheet named Sheet1, there is a hyperlink Click here for instructions which is linked to some text in Sheet2 in the same workbook. In such case we need to define the linked text by some relevant, unique name and then refer to it in the hyperlink to create the connection. In this case, linked resource is not an external file but another part within the same file. Besides defined name we can link an entire sheet in a workbook, or any of its cell referred to by its address. Defining a name: To give a name to any data value in a cell, select that cell and then, Formulas tab > Defined Names group > Define name drop-down > Define name option. In the dialog box, mention the desired name and click on OK button. In the dialog box, click on either Place in This Document section or Bookmark button. Under the defined Names section, select the name of the bookmark and click OK button. Exercise Ankita has created a sheet for her team to be filled individually. She created the data format in Sheet2 of the workbook and mentioned the instructions in Sheet1. In cell A1 of Sheet1, she needs to create an internal hyperlink to the instructions in Sheet1. Help Ankita. Linking to Registered Data Source Registered data source means any file which contains the data required to be accessed through your document or spreadsheet and your application is aware about it. Aware means, your application knows about its file type (spreadsheet, csv file, database, website etc.) and able to establish connection with it (compatibility). Getting External Data: Data tab > Get External Data drop-down > Select the desired option. For example, if you select From Access, then select the desired database. Next, select the desired table in the database and click OK button. In Import Data dialog box, you can specify to import the table or you can just create the connection to do the import sometime later. You can also choose to create a data summary in the form of Pivot Table or as a Pivot Chart. Finally, click on OK button. Electronic Spreadsheet (Advanced) 101 Information Technology NSQF Level-2

Exercise Get a database StudentDB made by your teacher that contains a table named Student. The table should store First Name, Surname, Date of Birth, Class, Section, House and Percentage Marks. Import this data in a spreadsheet. Session-3 Share and Review Sheets Modern businesses work in a shared environment where information and resources are shared over a network. Concurrency is the main factor while sharing files on the network. It refers to the ability of the software application or operating system to allow multiple users access the same file to facilitate collaborative data sharing. In such scenario, multiple users can view the data of the file and can make edits in the file simultaneously. The worksheet can be shared over local network as well as across the world over internet. For a local network (LAN), the spreadsheet file can be shared by saving it on a shared drive on any computer with rights given to the concerned users by network administrator to access it. Sharing of file has following major advantages: ¤ Faster data entry. ¤ Real-time data updates. ¤ Decreased data redundancy (multiple copies of same data set). ¤ Collaborative work environment. Let us se how it is done in MS Excel. For any worksheet to be shared, first ensure that it is saved on a shared drive where it can be accessed by multiple users then do the following: ¤ Open the desired worksheet. ¤ Go to Review tab. ¤ Click on Share Workbook option in Changes group. Electronic Spreadsheet (Advanced) 102 Information Technology NSQF Level-2

¤ In the Share Workbook dialog box, consider Editing tab. ¤ Check Allow changes by more than one user… checkbox. ¤ Switch to Advanced tab of the dialog box. Here, specify history for tracking changes, when the updates should be saved, how multiuser conflict should be resolved and if users must have access to print and data filter features. Finally, click on OK. To share the worksheet over internet go to File tab > Share option. Here, you can share entire workbook with others over Email or save the work sheet on a global drive like Microsoft OneDrive and invite people to access it. Exercise If your lab has computers networked with each other, ask your teacher to have a worksheet copied on a shared drive which you can access then try editing the sheet at the same time along with some of your classmates. Review changes in spreadsheet Spreadsheets, like other documents, can be reviewed either before sharing them with the intended recipients or after receiving form them to review the changes made by others. We know how to review sheets by the tools such as Spelling and Grammar and Thesaurus. Now, let us see how to review sheets when they are shared among multiple users. First let us, how a sheet can be shared with other users. Sharing a Sheet One way is to save the sheet at a global location such as Cloud drive (Google drive, MS OneDrive etc.) and sharing an access link to it with others. The other way is to send a physical copy of the Electronic Spreadsheet (Advanced) 103 Information Technology NSQF Level-2

sheet via email to all the recipients. The only difference is, sharing on cloud drive will have single, same copy accessed by multiple users while in email, separate copies for each participant will be there. If no further changes need to be done by anyone in the same sheet then email is a good option otherwise sharing single copy among multiple users is viable. In Excel, to share a sheet, click on File tab > Share option > Either Invite People or Email. Preparing a sheet before sharing When multiple users are likely to make changes in a sheet, it is a good practice to switch on the tracking of changes. This way, the changes made by anyone in the sheet are visible clearly for review by others. To do so in Excel, click on Highlight Changes option in Track Changes drop-down in Changes group under Review tab. On the Highlight Changes dialog box, click Track changes while editing checkbox and click OK button. Now, sheet is prepared to be shared with others. All the changes made in the sheet by all the users will be recorded and marked distinctly which you can see, analyse and either accept or reject. Reviewing Changes in the Sheet To review the changes, open the sheet. Review tab > Changes group > Track Changes drop-down > Accept/Reject Changes. In the dialog box, click OK. Excel will show the first change made. You can accept/reject current change or accept/reject all changes. Adding and Reviewing Comments Instead of making specific changes, users can also suggest changes by adding popup comments to the cells in the sheet by clicking New Comment option in Comments group under Review tab. Electronic Spreadsheet (Advanced) 104 Information Technology NSQF Level-2

Comments can be edited, deleted and browsed through by using desired option in the Comments group. Exercise Practice adding certain changes and comments in a spreadsheet using Track Changes. Then, exchange spreadsheets with your classmate. Practice accepting/rejecting changes in the sheet. Merge and compare If there are multiple copies of same worksheet modified by multiple users then all of them need to be merged and compared. To merge the worksheets, first open the main, first copy of worksheet. Then click on Compare and Merge command in Quick Access Toolbar In the dialog box, select the other file whose data needs to be merged and click on Open button. The sheet will be merged. To a d d t h e C o m p a r e a n d M e r g e command in Quick Access Toolbar: File tab > Options > Quick Access Toolbar section > Select All Commands from Choose Commands From drop-down > Select Compare and Merge command and click on Add button. Finally, click OK. Note: Compare and merge works only with shared worksheets. Electronic Spreadsheet (Advanced) 105 Information Technology NSQF Level-2

Session-4 Working with Macros Macros are a great way to automate routine tasks in an application like spreadsheet. For example, Mr. Sen heads a team of 200 salesmen in various cities who send him sales data by day end daily. That is 200 records which Mr. Sen needs to sort on the basis of city, sales target and salesman name. Since he needs to do it every day, he can have a macro created for the same to execute this task automatically in a few seconds. Macros are the named set of recorded steps which can be run anytime later as and when required. Macro feature allows the user to record the steps he/she does. The steps are saved in the workbook by a unique name given by the user. User also sets a shortcut key to run the macro later. Macros usually work well with a standard, fixed layout of data. In real life, people perform a lot of routine tasks on a fixed layout of data-set. In such scenarios, macros help in saving a lot of time and effort. Creating a Macro: Options related to macro are found under Developer tab which is usually not seen in Excel interface. To display the Developer tab, go to File > Options > Customize Ribbon section > Check the Developer checkbox under Customize the Ribbon subsection. Then, click OK. Now, Developer tab will be visible. Click on Record Macro option in Code group. In the Record Macro dialog box, specify a Macro name, shortcut key letter to run the macro later, where to store the macro and optionally, the description. Finally, click OK button. Electronic Spreadsheet (Advanced) 106 Information Technology NSQF Level-2

As you press OK button, macro recording will begin. Now, whatever you do will be recorded as a step in the macro. Once all the steps are done, click on Stop Recording option in Code group. Macro will be saved in the workbook. Running a Macro: To run the macro, press the shortcut key set for the macro. You can also run the macro by selecting its name in the name of the macro in the Macro dialog box and clicking Run button. To bring up Macro dialog box, click on Macros option in Code group. Exercise Assume a blank colourful, formatted layout and record the macro to create it. The fields are Serial No., Name, Address, Street, City, Pincode, State, Landline No., Mobile Phone No., WhatsApp No., Email ID, School Name, School Address, School Phone and School Email ID. After recording the macro, test it by the help of the shortcut key. Using Macros as a Function and Pass Arguments Macros can be implemented as built-in functions also. A function is an independent executable unit of code which is identified by its unique name. A function performs a specific task and returns the result of task done. For example, SUM() performs total of the given values. Many functions need input values to process. The values input to a function are called arguments. For example, in =SUM(230,250,100), 230,250 and 100 are arguments. You need to have basic knowledge of Visual Basic for Applications (VBA) programming language to create functions. Here, concept is explained with a simple example. In Excel, macros can be implanted as functions. A function body begins with the keyword Function followed by its name and any arguments and ends with End Function. In this example, function DA is taking 2 arguments – basic_salary and da_perc. The statements that make the task of the function go inside the function body. Here, function da has one statement (first line is just a comment created by single quote) which is calculating dearness allowance and returning it. How the value is returned? It is simply assigned to a variable of same name as the function. All you have to do this and the value will be returned. Creating a Function Press Alt + F11 or Developer tab > Visual Basic option. In the editor, go to Module 1 and type the code in the module editor. Once done, click on Save and close the Visual Basic interface. Electronic Spreadsheet (Advanced) 107 Information Technology NSQF Level-2

Using a Function To call a function in the spreadsheet, simply call it like you do other built in functions. In this example, 4000 will be assigned to basic_salary and 120 will be assigned to da_perc. Then, the function will perform calculation on these values and return 4800 (120% of basic salary 4000) Sorting Data using Macro We know how to record a macro and save it by a unique name. To sort data columns, we can record the macro and save it. Then, we can open the macro in program editor. As shown here, a macro named Sort1 is recorded to sort the students’ data on Class and Section. In Macro dialog box, select macro Sort1 and click on Edit button. The VB Code for the macro will open up in the program editor. The program you get will be in detail. However, a minimal working VBA code is given here. Sub Sort1() ActiveWorkbook.Worksheets(\"Sheet1\").Sort.SortFields.Clear ActiveWorkbook.Worksheets(\"Sheet1\").Sort.SortFields.Add Key:=Range(\"A2:A7\"), Order:=xlAscending ActiveWorkbook.Worksheets(\"Sheet1\").Sort.SortFields.Add Key:=Range(\"B2:B7\"), Order:=xlAscending ActiveWorkbook.Worksheets(\"Sheet1\").Sort.SetRange Range(\"A1:C7\") ActiveWorkbook.Worksheets(\"Sheet1\").Sort.Header = xlYes ActiveWorkbook.Worksheets(\"Sheet1\").Sort.Apply End Sub Let us understand this VBA Code. Electronic Spreadsheet (Advanced) 108 Information Technology NSQF Level-2

The key element in this code is function Range(). It takes the cell range to work upon as argument. For example, Range(\"A2:A7\") refers to cells A2 to A7. Range() also accepts any range names defined for the data. For exemple, Range(“Classes”) To access the currently opened worksheet by its name, we refer to function Worksheets() with sheet name passed to it as argument. For example, Worksheets(\"Sheet1\") refers to Sheet1. Worksheets() is defined in the object ActiveWorkbook that represents currently opened workbook. So, to access Sheet1 in curently opened workbook, the statement will be ActiveWorkbook.Worksheets(\"Sheet1\") Sort Object Sorting of data is done using Sort object available with each worksheet. Sort object has following components: 1. Collection SortFields: It contains the details of the fields on which data has to be sorted. Method Add of SortFields is used to load the details of fields. These details are defined as certain named parameters. Here, two parameters are used - Key: and Order:. Key: takes the value as Range of the cells that need to be sorted and Order: takes the defined value xlAscending or xlDescending. For example, ActiveWorkbook.Worksheets(\"Sheet1\").Sort.SortFields.Add Key:=Range(\"A2:A7\"), Order:=xlAscending Before sorting, it is advisable to clear any sorting done earlier. To do so, use method Clear on SortFields collection as ActiveWorkbook.Worksheets(\"Sheet1\").Sort.SortFields.Clear 2. Method SetRange: It sets the range of entire dataset that needs to be sorted by the help of Range(). For example, ActiveWorkbook.Worksheets(\"Sheet1\").Sort.SetRange Range(\"A1:C7\") 3. Method Apply: It finally applies the sorting on the data range as per the settings done. 4. Property Header: It takes defined value xlYes if the data set has headers. Default is xlNo. 5. Property MatchCase: Set it to False (ignore cases in data values) or True as you require. With-End With Shortcut Block If multiple statements are executed consecutively on an object then by using With-End With block, we can shorten the code like this: Sub Sort1() With ActiveWorkbook.Worksheets(\"Sheet1\").Sort .SortFields.Clear .SortFields.Add Key:=Range(\"A2:A7\"), Order:=xlAscending .SortFields.Add Key:=Range(\"B2:B7\"), Order:=xlAscending .SetRange Range(\"A1:C7\") .Header = xlYes .Apply End With End Sub With takes the object reference on which statements are being executed and within With block, the remaining part of the statements is mentioned preceded by a dot. Electronic Spreadsheet (Advanced) 109 Information Technology NSQF Level-2

Property FormulaR1C1 FormulaR1C1 is a useful property of object ActiveCell. ActiveCell object always refers to the currently active cell. Property FormuleR1C1 is used to store functions working on cell ranges. It refers to a cell address in the following ways: Rows: The rows are referred to with respect to the currently active cell. Let us understand with an example. In the data set shown here, the formula =SUM(A1:A3) needs to be set. Below, it is done by using property FormulaR1C1: ActiveCell.FormulaR1C1 = \"=SUM(R[-3]C:R[-1]C)\" The formula is given within double quotes. Argument in SUM() is R[-3]C:R[-1]C. Here, R[-3] refers to third row back(above) from currently active cell (A4) i.e. row 1 and R[-1] refers to one row back (above) i.e. row 3. Columns: C refers to first column i.e. column A. Similarly, C[1] will refer to second column in range and so on. The above macro will display result 60 in cell A1 if cursor is in cell A1. Let us see another example. Following macro is calculating sums in cells A4 and B4 as well as average of all values in range A1:B3 in cell A5. Read the comments. Range(\"A4\").Activate ‘Make cell A4 currently active ActiveCell.FormulaR1C1 = \"=SUM(R[-3]C:R[-1]C)\" ‘Apply SUM() to range A1:A3 Range(\"B4\").Activate ‘Make cell B4 currently active ActiveCell.FormulaR1C1 = \"=SUM(R[-3]C:R[-1]C)\" ‘Apply SUM() to range B1:B3 Range(\"A5\").Activate ‘Make cell A5 currently active ActiveCell.FormulaR1C1 = \"=AVERAGE(R[-4]C:R[-2]C[1])\" ‘Apply AVERAGE() to range A1:B3 In last line, with respect to cell A4, R[-4]C (4 rows back in first column) refers to cell (A1) and R[- 2]C[1] (two rows back in second column) refers cell B3. This marks the whole range A1:B3. Electronic Spreadsheet (Advanced) 110 Information Technology NSQF Level-2

Exercise 1. Write a macro function SortNames() to sort the names of 10 cities in descending order. 2. Consider the given data set. Write macro CalcAvg() to calculate average marks for each subject in correspoinding coloured cells. Assessment 1. Why do we need the data sorted before applying sub-total feature on it? 2. How is Scenario feature different from Goal-seek? Explain with a small example each. 3. Ajit has 3 sets of sales data which he wants to apply on the actual data to see how much earning his company will do in the end of the month. Which “what-if ” analysis feature will be suitable for him and why? 4. Ajit wants to know how much savings are needed to be done in current month to keep his expenses at Rs. 100000 by month end. Which “what-if ” analysis feature will be suitable for him and why? 5. With a small example, explain how Solver is a better tool than Goal Seek in certain scenarios. 6. What is the advantage of data consolidation? 7. Describe the syntax of referring to a cell in another worksheet. 8. What do you mean by base path in hyperlinking? What is the limitation of absolute linking? 9. How is relative linking a better way than absolute linking? 10. Roma is a writer. She has created a document named Index.doc on path D:\\my stories\\fiction\\2020. Index.doc contains relative hyperlinks, one for each of the chapter in the story. All the chapters are in separate files named as Chapter_x.doc, where x is the number of chapter. All the chapters are on path D:\\my stories\\fiction\\2020\\pdf\\draft. She needs to send the story to her publisher by mail. Which folder should she send to the publisher so that all the links work fine on publisher's computer too and why? 11. How will you link a worksheet to a defined name within the same workbook to which this sheet belongs? 12. What are the advantages of sharing a file over a cloud location? 13. Atul has 4 worksheets of data from different branches of his company. All have identical layout. He needs to merge them into one sheet, How will he do that? 14. What is the benefit of macros? How will you create and run a macro in an spreadsheet application? Watch & Learn www.eduitspl.com www.youtube.com/edusoftknowledgeverse Electronic Spreadsheet (Advanced) 111 Information Technology NSQF Level-2

3 Database Management System Session-1 Database Concepts Storing data and retrieving information has been a necessity of all businesses. Data can be anything such as, name of a person, a number, images, sound, etc. For example, Ravi, 4, student, etc. When the data is processed and converted into a meaningful and useful form, it is known as information. For example, Ravi is 4 years old and he is a student. is information. Earlier, the data was stored manually in the form of files, books and ledgers. Storing data and retrieving information from them is a time-consuming task. With computers, this problem has been resolved. Computers have replaced paper, files and ledgers as the principal media for storing important information. Computers help manage and organize the data so that useful information can be accessed easily and efficiently. A database is a collection of related data from which users can efficiently retrieve the desired information. A database can be a simple collection of text data in a simple tabular form to a complex collection of images, audio or video. DBMS is a collection of programs that enables users to create, maintain database and control all the access to the database. The primary goal of the DBMS is to provide an environment that is both convenient and efficient for user to retrieve and store information. The basic concepts behind a DBMS are: a) Specification of data types, structures and constraints to be considered. b) Storing the data and manipulating it as required. c) Querying the database to retrieve desired data. d) Updating the content of the database. Need for Database Management System Database approach came into existence due to the drawbacks and limitations of file processing system. In file processing systems; updating, deleting and maintaining the data is difficult. A number of application programs are written by programmers to maintain the data. In the database Database Management System 112 Information Technology NSQF Level-2

approach, the data is stored at a central location and is shared among multiple users. Thus, the main advantage of DBMS is centralization of data. Advantages of Database Management System a) The ability to update and retrieve data: This is the fundamental feature of a DBMS. Without the ability to view or manipulate data, there would be no point to using a database system. Updating data in a database includes adding new records, deleting existing records and changing information within a record. b) Support Concurrent Updates: Concurrent updates occur when multiple users make updates to the database simultaneously. Supporting concurrent updates is also crucial to database management as this ensures that updates are made correctly. Otherwise it would lead to the loss of important data and/or inaccurate data stored. c) Recovery of Data: DBMS provides ways to backup and recover database. There are times computers may crash, a fire or other natural disaster may occur, or a user may enter incorrect information invalidating or making records inconsistent. d) Security: To prevent unauthorized access to the database, DBMS uses features like encryption, authentication, authorization and views to provide security to the database. e) Data Integrity: Data integrity is a set of rules that DBMS provides to see that data incorrect or inconsistent data is not stored. f) Controlled data redundancy: During database design, various files are integrated and each logical data item is stored at central location. This removes replicating the data item in different files, and ensures consistency and saves the storage space. g) Data sharing: The data stored in the database can be shared among multiple users or application programs. Due to shared data, it is possible to integrate new applications without having to create any additional data or with minimal modification. h) Ease of application development: The application programmer needs to develop the application programs according to the users' needs. The other issues like concurrent access, security, data integrity, etc., are handled by the DBMS itself. This makes the application development an easier task. Application Areas of Database System Database systems support businesses of almost every nature now a days. Some of the most common database applications are listed here. Travel Industry: Airlines, railways, hotels and cab industry use online databases for reservation, room bookings and for displaying the schedule information. Banking & Finance: Databases are used for storing information such as sales, purchases of stocks and bonds or data useful for online trading, customers, accounts, loans, and other transactions. Education: Schools and colleges use databases for course registration, result, and other information. E-commerce: Integration of heterogeneous information sources (for example, catalogues) for business activity such as online shopping, booking of holiday package, consulting a doctor, etc. Health Care Industry: Databases are used for maintaining the patient health care details. Digital libraries & publishing: Databases manage bulk text & multimedia data. Human resources: Organizations use databases for storing information about their employees, salaries, benefits, taxes, and for generating salary cheques. Database Management System 113 Information Technology NSQF Level-2

Components of Database Management System User: Any person who can access the data by using a DBMS is called database user. Database Administrator: A database administrator is a person who directs or performs all activities related to maintaining a successful database environment. Responsibilities include designing, implementing and maintaining the database system, and providing security & training to employees in database management system. Application Program: An application program is any program designed to perform a specific function directly for the user or, in some cases, for another application program or operating system. Relational Data Model (RDM) The relational model is powerful yet simple, both flexible and natural. Data is stored in conceptual tables in which rows are instances and column and fields. In such a database the data and relations between them are organized in tables. Properties of relational data model: a) A relational database appears as a collection of tables (relations) to user. b) There is a set of operators for partitioning and combining. c) The connections are made on the basis of data. d) The users do not need to know how the data is stored physically. e) There is full data independence. Types of Database Databases are essential for record keeping. You can set up databases with database software packages, or even spreadsheet applications. Either way, both include options for database tables. Establishing database tables are a big part of database design, and the number of database tables is the main contrast between flat file and relational databases. 1. Flat File Database: Flat file is a database with one database table. One single database table can include all the database's fields. A single table flat file database can be okay if the database is only a small one with a few fields. However, some require much larger and more extensive databases with lots of fields. With lots of multiple fields, some record details can then become duplicated within a single database table. As such, flat file databases are not ideal for larger database models. 2. Relational Database: Relational databases are more widespread than the flat file database alternatives. Relational databases break up the single table of flat file database model. Therefore relational databases are split up into multiple database tables with methods for the tables to work together. These tables then need to be linked in some way by the database designer. Links are established with primary and foreign keys which are unique identifiers for the database tables. These links between the database tables are known as relationships and be one-to-one, many-to-one or one-to-many. We shall learn about primary and foreign keys in coming sessions. Database Management System 114 Information Technology NSQF Level-2

Database Servers Database servers are dedicated computers that hold the actual databases and run onlythe DBMS and related software. Typically databases available on the database servers are accessed through command line or graphic user interface tools referred to as Frontends; database servers are referred to as Back-ends. Such type of data access is referred to as a client-server model. Exercise 1. Your school library runs on the database of books. Try to find out how your librarian uses that database. 2. Design the structure of a table BOOK with fields namely: Book_No, Book_Title, Author. 3. D e s i g n a n o t h e r t a b l e B O O K _ I S S U E w i t h t h e f i e l d s n a m e l y : B o o k _ N o , Student_Registration_No, Issue_Date, Return_Date. 4. Decide the primary key and foreign key in the above tables and mention the reasons. Why? Session-2 Data Storage Data in a relational database management system (RDBMS) is organized in the form of tables. You will now quickly recap what you learnt in the last session and assimilate more concepts. Table A Table is a collection of data related to a single topic and a database is a collection of tables. A table organizes the information about a single topic into rows and columns. Parts of a Table a. Columns: The columns of a table are also called attributes. The column is the vertical part of a table. For example, table STUDENT given below has 6 columns. b. Rows: This is the horizontal part of the table. One row represents one record of the table. The row of a table is also called tuple. For example, table STUDENT given below has 8 tuples or rows. c. Cell: Cell is a small rectangular box that contains a value in the table. It is an intersection point of row and column. d. Degree: The number of attributes (columns) in a table is called the degree of the table. For example, table STUDENT given below has degree 6 (number of columns) columns. Database Management System 115 Information Technology NSQF Level-2

e. Cardinality: The number of rows in the table is called its cardinality. For example, table STUDENT given below has cardinality 8 (number of rows). Query The purpose of a database is not only to store and accumulate information, but also to ensure information retrieval according to specific criteria. A query can be used for selection of information in one or several tables. The result of a query can be displayed in data table, form and report views; it is possible to specify the table fields to be included. Form In databases, it is possible to use forms for the creation, display and modification of records. Data entry in a form is visually easier to understand than in a table, and is simpler for the user. Forms may contain fields for data entry, field descriptions (names), action buttons, menus, pictures, and design elements. Report Reports are usually used to print out information with a particular design. Reports are created using query or table data. Creating database reports is ideal when you want to: ¤ Reports help in analysing data and take decisions for future plans. ¤ Provide hard-copy printouts for review at a meeting. ¤ Include data from your site in a printed publication. ¤ Mark up the information in your database that needs updating. Database Management System 116 Information Technology NSQF Level-2

Data types Data types are used to identify which type of data (value) we are going to store in the database. Fields themselves can be of different types depending on the data they contain. Data types in Open Office base is broadly classified into five categories listed below. • Numeric Types. • Alphanumeric Types. • Binary Types. • Date time. • Other Variable Types. Numeric Types Numeric data types are used for describing numeric values for the field used in the table of a database. Numeric data types in a database can be using for storing information such as mobile number, roll number, door number, year of school admission, true or false statements, statistical values, etc. The different types of numeric data types available are listed here. ¤ BOOLEAN (yes / no): Values as 0 or 1. Example: True or False, Yes or No. ¤ TINYINT (tiny integer): Integer range between 0 and 255 ¤ SMALLINT (small integer): Integer range between -215 to +215-1 ¤ INTEGER: Integer range between -231 and + 231-1 ¤ BIGINT (big integer): Range between -263 to + 263-1 ¤ NUMERIC: Maximum precision of e(+/-)231 ¤ DECIMAL: Maximum precision of e(+/-)231 ¤ REAL: 2-1074 to (2-2-52)* 21023 ¤ FLOAT: 2-1074 to (2-2-52)* 21023 ¤ DOUBLE: 2-1074 to (2-2-52)* 21023 Alphanumeric Types ¤ LONGVARCHAR or memo: Stores up to the max length or number indicated by user. It accepts any UTF 8 character. ¤ CHAR text (fix): Stores exactly the length specified by user. Pads with trailing spaces for shorter strings. Accepts any UTF 8 Character. ¤ VARCHAR (text): Stores up to the specified length. No padding (Same as long var char) ¤ VARCHAR_IGNORECASE (text): Stores up the specified length. Comparisons are not case sensitive but stores capitals as you type them. Binary Types Binary data types are used for storing data in binary formats. Binary data types in a database can be using for storing photos, music files, etc. In general, files of any format can be stored using the binary data type. The different types of binary data types available are listed here. ¤ LONGVARBINARY (image): Stores any array of bytes (images, sounds, etc.). No validation required. ¤ BINARY (fix): Stores any array of bytes. No validation required. ¤ VARBINARY: Stores any array of bytes. No validation required. Database Management System 117 Information Technology NSQF Level-2

Date time Date time data types are used for describing date and time values for the field used in the table of a database. Date time data types in a database can be using for storing information such as date of birth, date of admission, date of product sale, etc. The different types of date time data types available are listed here. ¤ Date stores month, day and year information 1/1/99 to 1/1/9999. ¤ Time stores hour, minute and second info Seconds since 1/1/1970. ¤ Timestamp stores date and time information. Database Keys Each table has a set of attributes and values of each attribute are different from each other in the form of data types. Each table has a number of rows that represent to a single record. So how do databases keep all of these records straight? It's through the use of keys. Primary Key Primary key uniquely identifies each record in the table. Primary key consists of a single attribute or multiple attributes in a combination. Properties of Primary Key a) It must have a non-null value for each instance of the entity. b) The value must be unique for each instance of the entity. c) The value must not change or become null during the life of each entity. Foreign Key The foreign key refers to a referencing key present in child table to a matching value in a column in another table (parent). It cannot contain the values other than that column. It can contain the duplicate values also. Candidate Key In some instances, an entity will have more than one attribute that can serve as a primary key. Any key or minimum set of keys that could be a primary key is called a candidate key. Once candidate keys are identified, choose one and only one primary key for each entity. Let's assume that for each employee in an organization there are three candidate keys: Employee's ID, Social Security Number and Insurance Policy No. So you can choose only one column as a primary key. Properties of CK are same as of PK. Database Management System 118 Information Technology NSQF Level-2

Alternate Key The Candidate Key, which is not chosen as a Primary Key is known as Alternate Key. Let's assume that for each employee in an organization there are three candidate keys: “Employee's ID”, “Social Security Number” and “Insurance Policy No. that serve as unique identifiers in the given table. If for example “Emp_Id” was chosen as Primary Key then “Social_Security_No” and “Insurance_Policy_No” would become the alternate key. Combination of “Emp_Id”, “Social_Security_No” and “Insurance_Policy_No” is known as Candidate Key. Composite primary key There are certain cases where a single field may not qualify to become a primary key then unique combination of more than one key can be considered as a primary key. Such fields which together function as primary key are called composite primary key fields. Exercise 1. Go to a grocery shop in your neighbourhood and analyse that if you create a table to store the details of all the items in that shop then what will be the data types of each field in the table. Justify your choice of data type. 2. Which type of data should be suitable for the fields Phone_Number and Pin_Code? Justify your answer. 3. Why does database have a variety of data types? 4. How is a form different from a report? Give example. 5. Why table is the most important object of a database? 6. List 2 basic differences between primary key and foreign key. Session-3 Manipulating Data The data stored on a database can be manipulated using some commands. There are two types of languages for this task. These are: • Data Definition Language (DDL). • Data Manipulation Language (DML). Data Definition Language (DDL) Data definition language or data description language (DDL) includes database commands that define, modify and remove the structure of various objects like tables, indexes, users, etc. in a database. Common DDLstatements are CREATE, ALTER, and DROP. Data Manipulation Language (DML) Data manipulation language (DML) includes the commands that allows to access and manipulate data in a database in the following ways: ¤ Retrieval of information from the database- SELECT statement. ¤ Insertion of new information into the database - INSERT statementDeletion of information in the database - DELETE statement. Database Management System 119 Information Technology NSQF Level-2

¤ Modification of information in the database - UPDATE statement. Structured Query Language A query language is a part of DML involving information retrieval only. The terms DML and query language are often used synonymously. A popular data manipulation language is Structured Query Language (SQL). This is used to retrieve and manipulate data in a relational database. There are two types of DML: ¤ Procedural: the user specifies what data is needed and how to get it. ¤ Nonprocedural: the user only specifies what data is needed. This is easier for the user but may not generate code as efficient as that produced by procedural languages. Session-4 Creating a Database Object There are several popular RDBMS applications like Microsoft Access, OpenOffice Base, IBM DB2 etc. Here, we shall use OO Base open source database application. Opening Open Office 1. Start > All Programs > OpenOffice.org > Open Office. OR Click on the desktop shortcut of Open Office. Creating a Database 1. Start > All Programs > OpenOffice.org > Open Office. OR Click on the desktop shortcut of Open Office. 2. In the pop-up window, click on Database. 3. In the Database Wizard dialog box, select Create a new database option. Database Management System 120 Information Technology NSQF Level-2

4. Click Next. 5. Keep the option to register the database with OO and Open the database for editing selected. 6. Click Finish. 7. In the Save As dialog box, select the desired location for the database and enter the name of the database file. Note that, Open Office database file has the extension ODF. 8. Click on Save button. Database Management System 121 Information Technology NSQF Level-2

Exercise Create a database in OO Base by the name Library. Session-5 Creating and Working with Tables Table is the primary object of a database, which is used to store data in tabular format. While creating a table you need to decide the field or column names you need and their data types. Master Table What is a master table? A master table contains the main and primary data about any object in any enterprise or any business. Such table is not modified after every transaction. For example, a table containing details like codes, names, description, type and unit prices of items. Master table ideally contains a primary key field. Transaction Table A transaction table contains the details of every transaction that occurs in any enterprise or business. For example, daily sales details like Sale Date and quantity sold for each item that is sold. Transaction table usually has a field common with the primary key field of Master table. Such field logically relates transaction table to the master table. Such field in transaction table is called Foreign Key. Master table : Item ITEM_CODE ITEM_NAME ITEM_SIZE ITEM_TYPE PRICE J001 J0J0E1ANS 28 STRETCHABLE 700 J002 J0J0E2ANS J003 J0J0E3ANS 28 PARALLEL 870 J004 J004 J005 J0J0E5ANS 32 STRETCHABLE 1000 J006 J0J0A6CKET 32 PARALLEL 980 JACKET 42 DENIM 4000 XL DENIM 3000 Transaction Table : Sales ITEM_CODE ITEM_DATE QTY_SOLD J003 J1000/11/2019 12 J005 J1000/21/2019 20 J005 J1010/31/2019 25 J001 J004 23 J003 J1010/51/2019 10 J1020/61/2019 Working with Tables After creating the database, you will see the database interface which lists the four database object types – Tables, Queries, Forms and Reports in the Database pane. Click on the category Tables. The Tasks pane will list the choices to create a new table. Table Design View Design View provides full control to the user to create or design a database object by defining every small detail. For example, Table Design View allows you to mention each field you need to keep in the table and define the type of data it should store along with various constraints on the fields which Database Management System 122 Information Technology NSQF Level-2

ensures valid data storage in the field. You can specify which field should be primary key field. Click on Create Table in Design View… option in the Tasks pane. As you chose to create a table using Design View, the Design View window opens up. Under Field Name, specify the names of the fields that make the table. For each field, define the data type under Field Type. Description is optional and is for documentation or reference purpose. Before designing a table, the basic requirements regarding that table should be determined. Let us create table Item considering following structure requirements: Field Name Field Type Maximum Decimal Other ITEM_CODE Size or Places Requirements ITEM_NAME Text of variable Length ITEM_SIZE number of None Primary Key ITEM_TYPE characters 4 PRICE None Text of variable 100 number of None characters 5 None Text of variable 100 number of 2 characters 6 Text of variable number of characters Text of variable number of characters As shown here, one by one, type the name of the field, select the field type. For currently selected field, properties of that field are displayed in the Field Properties pane at the bottom. Some common field properties are as below: Database Management System 123 Information Technology NSQF Level-2

Entry required: If Yes, it means that data entry in this field is mandatory. Such fields cannot be left blank. Like, primary key fields cannot be left blank so their Entry Required property need to be Yes. For example, in the Item table above, all fields are mandatory for data entry. Length: Refers to maximum number of letters allowed in the field. Default value: A value can be defined in this property which will be taken if user does not enter any value. For example, in a shop, it is very rare that any item is sold for free. So a default value of price can be set in it. The field property settings for each field are given below: Field Name Property Settings ITEM_CODE ITEM_NAME ITEM_SIZE ITEM_TYPE PRICE 124 Information Technology NSQF Level-2 Database Management System

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

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

5. Specify a relevant, unique name to the table. Click Finish button. Edit, delete, sort data in table For editing the table, open it in Data view and insert data as shown here. To sort the data on the basis of any field, select the field and click on Sort Ascending or Sort Descending buttons on Table Data toolbar. Database Management System 127 Information Technology NSQF Level-2

Note that data is sorted in ascending order of BeginDate field values. To delete a record, simply select it and select Delete Record option in Edit menu. Exercise A. Open database Library created earlier and create table Book with following fields: Database Management System 128 Information Technology NSQF Level-2

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

Steps to Set up Relationship Between the Tables 1. In Tools menu, click Relationships option. 2. In the Add Tables popup, select the tables and click Add button one- by-one to add desired tables to Relationship Design View. Here, tables Stu_tab and Books_Issue_Return tables are added. 3. In the table structures, drag desired matching field from one table and drop it to the matching field of the other. The relationship is established and denoted by a thread. Database Management System 130 Information Technology NSQF Level-2

4. Right click on the relationship thread and select Delete to remove the relationship or Edit to make further changes in the relationship. 5. In Relations popup, specify desired update and delete options and click OK. Here, Update cascade and Delete cascade are selected. Let us check the relationship. Below is master table Stu_tab. Database Management System 131 Information Technology NSQF Level-2

Below, in the table Book_Issue_Return, StudentID of student Kashif is changed from 3 to 5. There is no student in the parent table Stu_tab with ID 5 so an error is displayed that StudentID 5 is not found in parent table. Exercise Create a master table Furniture to store FurnitureID, Furniture Name and Furniture Price. Create another table Sales table to store FurnitureID, SaleDate and SaleQuantity. Relate the two tables so that for any update and delete in table Furniture, update cascade and Delete cascade occur in Sales table. Test the relationship by entering sample data and performing updates and deletions. Session-7 Create and Manage Queries Basically, queries are used to access conditional data from tables. It is very difficult to extract data from the table without a query. Using query, we can request for data of our choice. It is also used to answer questions to perform calculations, combine data from different tables, or to add, change or delete table data. Create query using Query Wizard To create query using Query Wizard, following are the steps by example. Let us create a simple query on the table Book_Issue_Return. The table data is as below: Database Management System 132 Information Technology NSQF Level-2

1. Select Query object and click on Use Wizard to Create Query option in Tasks pane. 2. Select the desired table in first step of Query Wizard and add desired fields in the query. Click Next button. 3. Select the field on which you may want to get the data sorted. Click Next button. Database Management System 133 Information Technology NSQF Level-2

4. Specify the search conditions. E.g. records having actual date of return later than 18th April. Multiple conditions can be combined with AND by selecting Match all of the following option. Click Next button. 5. Specify if you need to see all records filtered out by query or only summary of data. Click Next button. Database Management System 134 Information Technology NSQF Level-2

6. Enter the names of the fields which should be displayed in your query. Here, field names are taken as alias names. Click Next button. 7. Specify a relevant name to the query. Click Finish button. Below is the output of the query (one record satisfies the given condition ActualReturnDate > 04/18/20. Database Management System 135 Information Technology NSQF Level-2

Exercise Create a master table Furniture to store FurnitureID, Furniture Name and Furniture Price. Create another table Sales table to store FurnitureID, SaleDate and SaleQuantity. Create a query to display the names of those furniture items whose quantity more than 100 is sold before January 1. Enter some sample data that matches the criteria. Create query using Query Design View Design View gives better control over designing a query. 1. Click on Table object and select Create Query un Design View option in Tasks pane and add desired table. 2. From the table structure (field list), drag-drop desired fields which you need to show in the query output into the Field section. Alias is display name of the field which can be changed optionally. Specify sort order in the Sort section exactly below the desired field. In Visible section, uncheck the boxes for the fields you do not need to show on the query result. In Criterion section, specify the record filter criteria. Once done, close query design and save the query with a relevant name. Database Management System 136 Information Technology NSQF Level-2

Criteria on multiple fields For multiple criteria, mention the desired criteria in Criterion section below desired field. To specify if all the conditions should be true then keep the criteria in the Criterion section. For having any of the conditions to be true, put them in Or sections as shown here. BETWEEN Operator Between operator is used to specify a range of numeric or date values. For example, for a field named age the range of age from 25 to 60 can be specified as Between 25 and 60. Exercise Create a master table Furniture to store FurnitureID, Furniture Name and Furniture Price. Create another table Sales table to store FurnitureID, SaleDate and SaleQuantity. Create a query to display the names and SaleDate of those furniture items whose quantity between 10 and 50 is sold on Feb 5. Enter some sample data that matches the criteria. Wildcards Wildcards are a shortcut way to specify value patterns that are shared by multiple values in a table. For example, you may need to display the records of all the students whose names begin with letter 'R' or those having letters 'sh' in their names. Or, you may need to list records of those student whose names have exactly 5 letters. Database Management System 137 Information Technology NSQF Level-2

To specify wildcard patterns, LIKE operator is used. There are 2 wildcards: * and ?. * : This symbol means any number of occurrences of any letter. ? : This symbol means exactly one letter. In the following example, LIKE 'R*' means values in field FirstName beginning with letter 'R'. In the following example, In the following example, LIKE '?????' means values in field FirstName having exactly 5 letters. In the following example, LIKE '*sh*' means values in field FirstName may have letters 'sh' anywhere. Exercise Create a master table Furniture to store FurnitureID, Furniture Name and Furniture Price. Create another table Sales table to store FurnitureID, SaleDate and SaleQuantity. Create a query to display the following: i. All the details of furniture items who have exactly 5 letters in their name. ii. All the details of furniture items whose names contain letter ‘A’. iii. All the details of furniture items whose names begin with letter ‘T’ and letter ‘E’. iv. All the details of furniture items whose names either contain letter ‘H’ or ‘P’. Database Management System 138 Information Technology NSQF Level-2

Perform calculations Queries can store formulas based on the fields to perform calculations. When query executes, the formula is executed and displayed as an additional field but it is not saved in the table. Such fields are called calculated fields. To perform calculations in a query, in Design View, these are the steps: In the Field section, specify the formula. Enter an Alias name for the formula. For example, if there is a field named PRICE and discount of 15% needs to be calculated then formula “PRICE” * 0.15 should go in Field section. In the following example, Penalty amount on late return of the book is calculated as number of days delayed multiplied by Rs. 10. Number of days delayed are calculated as days between Actual date on which book was returned (ActualReturnDate) and the date on which book should have been returned (ReturnDate). DateDiff() function is used here to take the difference in terms of 'day'. DateDiff() takes 3 arguments: First argument is a defined value for taking difference on the basis of year, months or days. Here, day denotes number of days between the two dates. Smaller or earlier date. Larger or later date. The output of the query shows the penalty amount @ Rs. 10 per day. Penalty is 0 if book is returned on time. Exercise Create a master table Furniture to store FurnitureID, Furniture Name and Furniture Price. Create another table Sales table to store FurnitureID, SaleDate and SaleQuantity. Create a query to calculate and display Sale Amount by multiplying SaleQuantity with Furniture Price. [Hint: Precede field name with table name such as Furniture.Price * Sales.SaleQuantity] Grouping data Many times it is required that calculations are done on the basis of a data group. For example, calculating average marks of students in class 9 and 10 separately, or calculating average price of Database Management System 139 Information Technology NSQF Level-2

all washing machines, or calculating total of sale made in each region, or counting how may employees are there in each department. To do so, in query design, we need to specify function Group in the Function section of the field and the calculation to be done in the Function section of the field on which calculation needs to be done. Such calculations are called aggregate calculations. COUNT, SUM, AVERAGE etc. are aggregate functions because they perform calculation on a group of multiple values. In the following example, students are grouped on the basis of Class and Count function is applied on the field ID which contains unique IDs of the students. Table Stu_tab has currently 4 records of which 3 students are in class 10 and 1 is in class 9. Exercise Create a master table Furniture to store FurnitureID, Furniture Name, Furniture Material and Furniture Price. Furniture Material can be Wood, Plastic or Metal. Create another table Sales table to store FurnitureID, SaleDate and SaleQuantity. I. Create a query to display total furniture price Furniture-Material-wise. ii. Create a query to display total sale amount (price * sale quantity) Furniture-Material-wise. Editing query To edit the query, right click on it and select Edit option. Database Management System 140 Information Technology NSQF Level-2

Session-8 Structured Query Language Some RDBMS like MS Access, OO Base etc. provide graphical interface to design a query while others do not. Besides design view, there is another powerful way of querying for data from the database and that is called SQL (pronounce “sequel”) – Structured Query Language. An SQL statement is a simple English like set of commands and clauses which help in querying desired data from the database. SQL Command: It is a keyword that denotes the action to be performed on the desired table like CREATE (to create database objects like tables), SELECT (to query data), UPDATE (for data modification) and DELETE (to remove data) SQL Clause: It is a keyword that is used along with SQL command to define the scope of the SQL Command and to determine in which order the data should be displayed in output. Like, while (to filter records on the basis of condition(s), order by (to sort the output) etc. Example of SELECT command without any clause: SELECT ITEM_CODE, ITEM_NAME, PRICE from Item; Here, item code, name and price of all the items will be displayed. SELECT *from Item; Here, all the details (fields) of all the records will be displayed. Creating Query in SQL View Using SELECT Note: The structural syntax of SELECT command is as follows: An SQL statement can be written without Select<comma separated fields list> any line breaks. Here, line breaks are from<comma separated table names> given easier clarity of the concept and where<condition> make the SQL statement easily readable. order by<field name on which output to be sorted><ASC/ DESC> where and order by clauses are optional unless you need them. 1. In Database pane, click on Queries category. Then, in Tasks pane, select Create Query in SQL View… option. 2. SQL query text editor will open up. In it, type: SELECT *from Item; 3. Press F5 or Run SQL command directly button in SQL toolbar. The interface with the SQL statement and with the output will look like this: Note: SQL is not case sensitive in typing the commands but field names should be typed in exact the case they have been created in the table. Database Management System 141 Information Technology NSQF Level-2

Let us try some more SQL statements: Display selective fields from the table This statement displays, item code, name and price of all the records in the table. Select ITEM_CODE, ITEM_NAME, PRICE from Item; Display selective fields on the basis of a criteria (condition) Let us display item names, size, type and price of those items whose price is more than 800. Select ITEM_NAME, ITEM_SIZE, ITEM_TYPE, PRICE from Item where PRICE > 800; Let us display the size and type of all the jeans in our store. Select ITEM_NAME, ITEM_SIZE, ITEM_TYPE from Item where ITEM_NAME='JEANS' Note: 1. Text values in condition should be enclosed in single quote, not double quotes. 2. Data value are compared with case sensitivity. Here, JEANS will not match with 'jeans' or 'Jeans' etc. Sorting Output Output of the query result can be sorted in ascending or descending order,on the basis of one or more fields. Database Management System 142 Information Technology NSQF Level-2

Let us display the output of above SQL statement in the descending order of price. Select ITEM_NAME, ITEM_SIZE, ITEM_TYPE, PRICE from Item where PRICE > 800; order by PRICE Desc Note: For ascending order, use the keyword Asc. Inserting and Modifying Data in the Table Using SQL, you can insert new records in the table, delete and update (modify) the existing records in the table. To modify data in the table, you need SQL Statement Execution tool. To open it, select SQL option in Tools menu. Execute SQL Statement dialog box will popup. Type the statement in Command to Execute box and click on Execute button. Inserting New Records SQL syntax for inserting new records in the table is: Insert into “<table name>” (comma separated list of fields) Values (comma separated list of values) Note: 1. The table name should be in double quotes. 2. The sequence of fields and that of values should be same. 3. Non numeric values need to be enclosed in single quotes. Let usadd details of a new leather jacket in the table Item. Insert into \"Item\" (ITEM_CODE, ITEM_NAME, ITEM_SIZE, ITEM_TYPE, PRICE) values('J011','JACKET','28','LEATHER',7600); Deleting Records Deleting records is a very sensitive issue and should be dealt with care. Always consider before hitting Execute button if you really need to delete the intended records. Delete statement should generally have some condition associated with it otherwise all the records in the table will be deleted. Database Management System 143 Information Technology NSQF Level-2

The syntax of Delete statement is: Delete from <table name> Where <criteria> Let us delete all the items which have price less than 700. Delete from “Item” where PRICE < 700; Updating Records Updating records is a also very sensitive issue and should be dealt with care. Always consider before hitting Execute button if you really need to update the intended records. Update statement should generally have some condition associated with it otherwise all the records in the table will be updated with the given same value. Update is used to make changes in the data values of existing records. The syntax for update statement is: Update “<table name>” Set “<field_name>” = '<value>' Where <criteria> Let us change the value STRETCHEABLE to STRECTH in all the records. update \"Item\" set \"ITEM_TYPE\" = 'STRETCH' where \"ITEM_TYPE\"='STRETCHABLE'; Database Management System 144 Information Technology NSQF Level-2

Creating a New Table using SQL Statement SQL statement Create is used to create table by mentioning table keyword. It is used to define the table name, field names along with their types and other constraints. The syntax of Create Table statement is as below: CREATE TABLE <table name> CREATE TABLE \"Sales\" (( <field name> <data type> <constraint>, \"ITEM_CODE\" Varchar(4), <field name> <data type> <constraint>, \"SALE_DATE\" Date, <field name> <data type> <constraint>, \"QTY_SOLD\" Numeric(5) . ); . <field name> <data type> <constraint> ); Exercise A. Table Book is given here. BookID Title Author Publisher Price Copies --------------------------------------------------------------------------------------------------------------------------------------------- 100 Eye of the Needle Ken Follet Rupa 545 5 101 The Firm John Grisham DigiMedia 399 2 102 The Third Twin Ken Follet Rupa 400 2 103 Clear and Present Danger Tom Clancy Rupa 760 1 104 The Innocent Man John Grisham DigiMedia 324 3 105 The Hunt for Red October Tom Clancy DigiMedia 575 2 To work on the table Book issue SQL commands to find out the following: 1. List all the details of all the books. 2. Display title, publisher and price of all the books. 3. Display title, author and price of all the books whose price is less than 400. 4. Who are the authors whom 'Rupa' publication has published? 5. What are the prices of the books written by ‘Tom Clancy’? 6. List details of all the books in descending order of Price. 7. Display the names of the authors with book titles in ascending order of Price. 8. Refer to question 4 and display the output sorted on Book Title. 9. Which books and their authors are published by 'DigiMedia'? 10. What are the titles and prices of books whose more than 2 copies are there in library. 11. Who has written which books cheaper than 300. 12. Here, BookID is primary key. Why not any other field is considered as primary key? Explain briefly. B. Change the price of Book number 5 to 430. C. Delete the all the book which are priced higher than 400. D. Deleted the books which have just 1 copy in the library. Database Management System 145 Information Technology NSQF Level-2

E. Write the SQL Statement to create the table Tea as described below. Tea_Code A number to identify each tea flavour Tea_Flavour Stores name of the flavour such as Ginger, Lemon, Honey etc. Size Size is Large, Medium, Small Sugar_Type Sugar, Sugar free Price A value with two decimal places In the above table, which field should be the primary key and why? F. Write the SQL Statement to create the table Flight as described below. Sector For example, KOL-DEL, DEL-BAN, CHN-DEL etc. Date Date of flight Day1 3 letter weekday name Day2 3 letter weekday name Day3 3 letter weekday name Capacity number of seats In the above table, which field should be the primary key? Why?/Why not? Suggest a remedy also if why not. Session-9 Building Forms A Form is a database object that can be used to create a user interface for a database application. With the help of 'forms', you can enter, view and change data in an easy manner. A form is an interface between the user and the underlying table whose layout is defined by the user for working conveniently with the data stored on the table. A Form is a collection of buttons, text boxes and labels. These are also known as controls. Let us create a Form for the table Item. Creating Form with Form Wizard A form can be created in two ways - Using Form Design View and Using Form Wizard. Here, we shall learn to use Form Wizard. 1. Click on the category Forms in Database pane and then select Use Wizard to Create Form… option in the Tasks pane. Database Management System 146 Information Technology NSQF Level-2

2. In the Form Wizard dialog box, do the following: a. i.) Select the desired table from the Table or queries drop-down. Using Shift key, select all the fields in the table that you need to add to the form. a. ii.) Then click on > button. You can click also on >> button to add all the fields at once in the form. Then, click Next. b) Next step allows you to insert an already existing form in the form being created. We do not need this step here, so simply click Next. c) Select the arrangement of the controls in the Form and click Next. d) Select the data entry mode either to enter only new records and not to display the records already entered in the table or to display all the records. Here, you can also select to disallow modification and deletion of existing data and entry of new data. Let us select to display all data with every action allowed. Then, click Next. Database Management System 147 Information Technology NSQF Level-2

e) Select any display style for the Form and click Next. 3. Specify a name for the form. In our example, it can be named as Item_Form. You can decide to use the form as it is created or to format the look of the form in the form editor. Let us select Work with the form option and click Finish. Once form is opened in the working mode, you can use the backward and forward arrow buttons on the Form Navigation toolbar. If it is not visible, select it from Toolbars submenu under View menu. Database Management System 148 Information Technology NSQF Level-2

To skip to a blank record for entering new data values, click on New Record button on this toolbar. Enter the following new record in the table through this form. Close the form. Closing it will automatically save the changes and newly added records in the underlying table. Open the table Item and you will find the newly added record in it. Exercise A. For table Book created in previous session create: 1. A form named View_Books only to view the records. 2. A form named Add_Books only to add records of new books in the table. Delete and update should not be allowed through this form. 3. A form named Modify_Books only to modify existing records of books in the table. New entry and delete should not be allowed through this form. B. Open these three forms and test if they are working as desired. Delete records in Forms To delete the current record, click on Delete Record button on the Navigation bar of the form. Editing Form Once a form is created using wizard, to edit it, right click on it and select Edit. The form will open in Form Design View. Using design view, a new from can also be created from scratch. Form contains 3 types of controls: Data Controls: These controls are bound with a field in the concerned table and they display the data value of that field. Calculated Controls: These controls store formulas for calculations. They are not bound with any field in the table. Database Management System 149 Information Technology NSQF Level-2