Skip to content

MIS602 Data Modelling & Database Design

The library management system helps libraries keep a list of documents, credits, investments and member files, sometimes for several physical locations. This type of software can be used by educational libraries and public or private libraries. Excel does not have the ability to manage data for affiliate database programs, such as SQL Server and Microsoft Access. In Excel, data is organized using rows and columns on a sheet of paper. The function of the table makes it easy to insert, organize and move data. There are four parameters for measuring data: name, ordinal, division and measurement. They are methods for dividing different types of data.

IN this above scenario we use a library data in a excel software that make help to easy understand and also easy to understand the visualization. We have use different worksheet in a single excel file. Excel is one of the most widely used desktop applications ever made and everyone is familiar with computers and keyboards. However, most of us do not know what Excel can do, much of its limitations are brought about by the development of its latest capabilities, flexibility and complexity. The fact is that Excel can simplify your work in a number of ways, without counting the number and average in a standard spreadsheet.

Excel business data analysis shows how to solve real-life business problems using your Excel data analysis capabilities. Order keys directly for business users, as opposed to focusing on individual components and functions of Excel. Many chapters begin with a business problem or question and then show you how to create a redesigned spreadsheet that solves common data analysis problems.

Designed especially for advanced Excel users, the workbook does not waste a lot of time on the basics.  

Basically, in this project we use four spreadsheets in a excel for different visualization purpose. In this project we perform different operation like merge data, add data, create data, update, delete data and highlight data.

Basic operation followed in excel files: –

Merge data: it also called Data Consolidation. The data link allows you to collect data from separate pages to form one main page. In other words, the data integration section takes notes from a series of papers or workbooks and combines them into one page that you can easily improve.

Start concatenating tables.

Step 1: Choose a large table.

Step 2: Select the new table.

Step 3: Select the appropriate column.

Step 4: Choose a column that you can upgrade to a large table.

Step 5: Select the column you want to add to the main table.

Step 6: Select advanced connection options.

Data deletion:- Select the cells you want to delete, then press the Dele button. Note that deleting cells in this way erases the data, but not doing it. If you want to remove the shape, use “Delete All” from the clean-up list on the main belt. Another easy way to extract data from a sheet is to delete all columns or rows. We can also delete lines using the tape command. Go to Home tab Tap the Delete key and select Delete line.

Data update:-

Just refresh the selected data ALT + F5, or in the Data tab, in the Queries and Connections tab, click the arrow under the Refresh heading, then click Refresh.

To refresh all data in the CTRL + ALT + F5 workbook, or in the Data tab, in the Query and Connections tab, click Refresh All.

To control how the connection is refreshed and when, in the Data tab, tap the arrow under Total Refresh, and then click on Connection Properties.

In the dialog box, in the Test Management section, select the option you want to use in the tab.

Create attribute:- Create new columns in a excel spreadsheet in called new attribute.

Remove Duplicate Rows:- If you have ever had to combine multiple spreadsheets into one, you will know how difficult it is to remove copied records. Excel can erase them for you, although they should be used with caution, because they erase your data. Excel automatically selects multiple cells. A window appears with a list of columns for verified boxes in front of all names. If you leave three selected columns, Excel will scan copies in all cells This means that all three cells must be the same as all three cells in one row to be considered as copies. That way, if you can run it now, you won’t get twins. You do not have to decide which lines to stay or which lines to go. Excel moves from top to bottom in sequence, marking the first event and deleting any subsequent rows. Excel does not delete complete lines from a spreadsheet when removing twins. This will clear the queue cells from the selected data. Data outside of your site is unaffected. (Therefore, if you have information on E1-E6 cells, they will not be affected by deleting the simulated processes from A1-C6 cells.) If you select cells and when you click Delete Copies, a window will select Continue with this option, which will remove the copied cells from your selection and data. It may leave the room empty.

Working of the spreadsheet:-

As per given scenario we follow the library management with some attributes like book id, author name, book name, rating, language and genres. There is 4 different sheet version present which have different operation.

V1.0 Spreadsheet:-in this given scenario we add ten different book data in spreadsheet we add the ID, author name rating book title and genre of the ten different book data in a V1.0 as show below table.

V1.1  in this Spreadsheet we update last two data and add new five books data in last five Rows as shown below:-

V1.2 in this Spreadsheet we two new attributes named as page number or language and highlighted with red colour as shown below:-

V1.3 in this spreadsheet we drop or remove one of attribute name page number in each row and add five more data in spreadsheet and highlighted with green colour as shown below:-