Database
A database consists of an organized collection of data for one or more multiple uses. One way of classifying databases involves the type of content, for example: bibliographic, full-text, numeric, image. Other classification methods start from examining database models or database architectures: see below. Software organizes the data in a database according to a database model. As of 2010 the relational model occurs most commonly. Other models such as the hierarchical model and the network model use a more explicit representation of relationships.
In computing, databases are sometimes classified according to their organizational approach. The most prevalent approach is the relational database, a tabular database in which data is defined so that it can be reorganized and accessed in a number of different ways. A distributed database is one that can be dispersed or replicated among different points in a network. An object-oriented programming database is one that is congruent with the data defined in object classes and subclasses.
Computer databases typically contain aggregations of data records or files, such as sales transactions, product catalogs and inventories, and customer profiles.
Typically, a database manager provides users the capabilities of controlling read/write access, specifying report generation, and analyzing usage. Databases and database managers are prevalent in large mainframe systems, but are also present in smaller distributed workstation and mid-range systems such as the AS/400 and on personal computers. SQL (Structured Query Language) is a standard language for making interactive queries from and updating a database such as IBM's DB2, Microsoft's Access, and database products from Oracle, Sybase, and Computer Associates.
-----------------------------------------
Uses for a Database
Databases for Businesses
The business world depends on databases 24 hours a day/seven days a week. Inventory, order processing, payroll, accounting, shipping and transportation routing are often tracked within a main database that keeps the company functioning.
Databases for Educational Institutions
From elementary schools to colleges, educational institutions have used databases to keep track of students, grades, transfers, transcripts and other student data. There are even specialized database packages geared toward schools and colleges.
Databases for Non-Profit Organizations
Like businesses and educational institutions, non-profit organizations must have a system to keep track of information. Many charities and other non-profit groups use a database for keeping track of donations, volunteers, hours served in the community, clients helped and other information related to the organization.
Databases for Household and Family Management
The database also has a home in household and family management for many individuals and families. Many individuals/families use a database to keep track of family birthdays, bills and expenses within a home; addresses of friends and relatives; movie/DVD collections; and other lists.
Databases In Use Everyday
Each time you make a purchase and the sales clerk asks for a your address or ZIP code, your information is kept and stored in a customer database. These collections of data are used to send mailings of special offers, discounts and other deals.
-----------------------------------------
Why Use a Database?
So, what possible use could you have for a database on your website? After all, you are just creating a site for yourself, your friends and your family.
As I mentioned earlier, there are a myriad of uses for databases. I'm sure it is easy to imagine all of the uses for a database on a commercial web site like an online store. You could store visitor information for logging in, purchase information, order logs, bookkeeping information, company reports, pricing structures, account information and the list goes on.
But what about personal stuff? Well, imagine this. You started a site for you and your family. You have a few pages for your family, your parents have a few pages and you just added some pages about your friends. Now your site just seems to keep growing and you don't really have the time to keep up with your own stuff let alone all of the things your family and friends keep sending you.
For example, you started a page so that you and your friends can borrow each others DVDs. Your page keeps track of who has what DVD and who is the original owner of the DVD. Each time a DVD changes hands someone sends you an email and you have to change the information on the website.
Another example. Your parents have 7 grandchildren. They love to keep up with their grandchildren's' activities. So, they are constantly sending you each grandchild's schedule of events to post. You get sports schedules, school activities and recitals just to name a few.
You also created a community page for your visitors. On it you have the names, email addresses and telephone numbers of your family and friends. The trouble is your friends and family seem to keep adding their friends and family and you keep getting all of the emails asking you to add more and more people.
Getting tired just thinking about it? Can you see how a database could be your friend?
If you could create a database driven DVD checkout system, calendar of events and community contacts you could save yourself a ton of time by letting your users do the updating instead of you through some simple web forms that you create and then use your database to update your pages automatically.
back to top
-----------------------------------------
What Types of Databases are There?
You wouldn't believe.
DBMSs (Data Base Management Systems) come in many different shapes, sizes and flavors, however, there are two basic categories: Enterprise and Personal.
(In case you are confused, DBMS refers to the software that handles moving the data in, out and around your database while the term database refers to the actual body of data that you are storing.)
Enterprise DBMSs are designed primarily for use with corporations, governments and any other function that would require the database to handle large amounts of data coming in and out. Information Technology folks refer to this as scalability which simply means how well the database handles multiple simultaneous transactions (the handling of data). For example, a high scalability would mean that the database can handle everything from a few transactions an hour to thousands of transactions or more happening at the same time.
Some examples of Enterprise DBMSs that you might have heard of would be Microsoft's SQL (pronounced sequel) Server, Oracle or IBM's DB2.
Personal DBMSs are generally not highly scalable. They are intended to be used as learning tools or in low demand situations like you may run into while working on your personal projects. Depending on the DBMS, you might even find good uses for a personal DBMS in small business. Many Enterprise DBMSs also have personal versions like Oracle that was mentioned above. Other companies
produce separate products like Microsoft's Access which are very similar to their Enterprise product.
-----------------------------------------
Since then, spreadsheets have become much more sophisticated tools. But they still fall far short of databases. Here's why a database is still a better tool for business:
• Easier to share. You can have two or more people editing a database at the same time. But spreadsheet users have to wait until nobody is using a file before it's free for them to use.
• Better security. Along with the ability to better share information is the ability to better secure it. You can protect private information from wandering mice better with a database. You can also protect users from their own mistakes, like adding up the wrong column or forgetting to include sales tax in the price quote.
• More efficient. Databases are relational, allowing you to link related tables to minimize duplication. Here's an example. Say you have a mailing list that includes 20 real estate agents at the same office. All of them have the same company name, address, phone number and fax. In a spreadsheet, you would have to duplicate that information 20 times for each agent. In a relational database manager, you enter that information once, then link or "relate" it to each of the 20 agents.
• Better reporting. With a database, you can format the same data many ways in reports. Here's an example. For monthly sales reports, you want to look at sales by person, by region and by product. In a spreadsheet, you basically have to enter or copy that information three times in three spreadsheets to get the desired reports. In a database, you enter it once and then use the reporting features to compile the data in the three formats with a click of the button.
• Greater capacity. Databases also have capacity to hold much greater numbers of records (into the billions).
• Easier to maintain. What makes spreadsheets so simple to use -- the fact that the programming and the formatting are together on one page with the data -- also makes them hard to maintain. Every place I go where people use spreadsheets for database activities, such as list management and process tracking, I spend time fixing spreadsheets because someone messed up the formatting while changing the data. You don't have that problem with databases.
• Less duplication. There are two kinds of duplication with spreadsheets.
First, there is file duplication. Because you can't share the price-quote spreadsheet, everyone makes a personal copy. Second is duplication of source data. Nine of 10 spreadsheets duplicate information already housed in a database, such as an accounting program.
In short, databases require a greater investment in training. But the return is greater than any spreadsheet.
So when should you use spreadsheets? I like them for two circumstances:
• When compiling information from diverse sources. For example, you want to compare your company's financial ratios with those of industry standards supplied by your accountant.
• When you are doing a one-time analysis. There isn't a faster tool for building a simple snapshot that you might not use again.
Of course, you're free to use spreadsheets however you want. But if you want to debate the merits of spreadsheets vs. databases, I'll see you on Springer.
Wednesday, June 23, 2010
Monday, June 21, 2010
Introduction to Spreadsheet Concepts
What is a Spreadsheet?
A spreadsheet (or spreadsheet program) is software that permits numerical data to be used and to perform automatic calculations on numbers contained in a table. It is also possible to automate complex calculations by using a large number of parameters and by creating tables called worksheets.
In addition, spreadsheets can also easily produce graphic representations of the data entered:
• histograms
• curves
• sector charts
• ...
Therefore, the spreadsheet is a multi-use tool that works as well for secretarial activities that involve organising large quantities of data, as at the strategic and decisional level by creating graphical representation of synthesised information.
The Main Spreadsheets
Numerous spreadsheets have been produced by the main software companies. The main spreadsheets are:
• Microsoft Excel, in the Microsoft Office office suite
• Sun StarOffice Calc, in the StarOffice suite
• OpenCalc, in the OpenOffice suite
• IBM/Lotus 1-2-3 in the SmartSuite suite
• Corel Quattro Pro in the WordPerfect suite
• KSpread in the KOffice free suite from Linux
Examples in the following articles are based on the Microsoft Excel spreadsheet, but the other spreadsheets contain the same functionalities.
The Concept of a Worksheet
Spreadsheets display data and formulas in a table form (lines and columns) called a worksheet.
A worksheet is made of lines (numbered with numbers) and of columns (numbered with letters). The intersection of a line and a column is called a cell. A cell is therefore represented by a number and a letter.
A worksheet can contain up to 65,536 lines and 256 columns, more that 17 million cells.
Each of the cells on a worksheet may contain values. These values are entered directly (numbers, text, date, etc.) or are automatically calculated by the spreadsheet. This is referred to as formulas, meaning expressions that calculate a value in function of one or more values in other cells on the worksheet.
Spreadsheets have a large number of integrated functions for math calculations, statistics, etc.
Here is an example of a worksheet that contains student's grades for which the spreadsheet automatically calculates the averages:
The Concept of a Cell
A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also used).
Coordinates (called reference styles) are generally grouped into two types, depending on the worksheet:
• The mode known as L1C1 (Line 1, Column 1), where the cell is located by the line number preceded by the letter L and the column number preceded by the letter C. L12C34 designates the cell at the intersection of the 12th line and the 34th column.
• The mode known as A1, where line numbers are designated by numbers and columns by letters. Thus AA17 designates the cell at the intersection of the 27th column and the 17th line.
Most spreadsheets have options to allow both modes to be used. With Excel and StarOffice, go into Tools > Options > General and click the box "L1C1 Reference Style".
Cell References
In order to work with data from cells when performing calculations, it is necessary to reference the cells. Many ways of referencing cells exist:
• absolute reference
• relative reference
• mixed reference
• named reference
Absolute References
An absolute reference represents the method of distinguishing a unique cell in a worksheet. Depending on the reference mode (L1C1 or A1), the absolute reference will
be written differently:
• In L1C1 mode: a cell's absolute reference is written by preceding the number by a letter L and the column number by the letter C.
LLineNumberCColumnNumber
For example, L12C24 represents the cell located at the intersection of line 12 and column 24.
• In A1 mode: a cell's absolute reference is written by preceding the line number and the column number by the $ sign.
$ColumnLetter$LineNumber
For example, $AC$34 represents the cell located at the intersection of the indicated column, AC, and line, 34.
Relative References
A cell's relative reference is the expression of its position relative to another cell. Thus, the relative reference gives the difference (in terms of the number of lines and columns) between the cell (called reference) and the target cell (called referenced cell).
By convention, upward difference along the vertical axis and difference towards the left along the horizontal axis are negative.
• In L1C1 mode: the relative difference of a cell is shown as cell coordinates in parenthesis:
L(LineNumber) C(ColumnNumber)
For example, L(3)C(-2) represents a cell located 3 lines lower and 2 columns left of the reference cell:
When there is no difference, a zero is not required in the parenthesis. Thus, L(0)C(12) may be written as LC(12).
• In A1 mode, the expression of the difference between the cells is hidden. In effect, a relative reference in A1 mode is implicit: just click on the coordinates of the target cell (referenced) without writing the $ sign:
Mixed References
A mixed reference is a reference where the horizontal position is expressed in an absolute manner and the vertical position in a relative manner, or vice-versa.
• In L1C1 notation, a mixed reference could look like L2C(3) or L(4)C17.
• In A1 notation, a mixed reference could look like $C5 or F$18.
Named References
A name may be given to a cell or to a group of cells.
To name a cell in Excel, select the cell or the range of cells to be named and go to Insert > Name > Define
When a cell or the range of cells has a name (the term label is sometimes used), it may be referenced by name. This functionality is especially useful when certain cells or cell ranges contain characteristic data because they may be referenced by name even if the cell or the group of cells has been moved.
For example, on an invoice, using a cell name such as total_bt for the cell that gives the total of an order before taxes is a good idea. You can also create a cell called VAT that contains the value of the VAT. Thus, when you need to calculate the total with taxes included, it is as easy as multiplying the cell called total_bt with the cell called VAT.
Comments
Commentary (sometimes called an annotation) may be added to a cell to provide additional information that we do not wish (or that we cannot) show on the worksheet.
To add a comment to a cell in Excel, select the cell or the range of cells to be named and then go to Insert > Comment or click on the cell or group of cells with the right mouse button and choose Insert a comment.
The small red triangle on the upper right of the cell D5 shows that the cell has a comment. This encourages the user to slide the mouse over the cell to read its contents.
Cell Content
A cell of a worksheet can contain a value or be empty. The value of a cell has two essential characteristics:
• a type, which means the intrinsic type of the data. There are generally three types of values:
o numeric values, for example 3.1415927, 58%, or 1984.
o alphanumeric values, for example Table2, Title or Name.
o formulas, which means expressions that represent calculations based on data and performed by the spreadsheet.
• a format that represents the way the spreadsheet displays the data. For example, if it is a number, the spreadsheet can be set to display it as an integer, as a decimal number (as well as the number of decimal places displayed), etc.
The cell may have a style, which means a particular visual appearance (font, size, color, border, etc.), independent of the value it contains.
Numeric Data Entry
When entering numbers in a cell (including monetary signs, percentages, etc.), the spreadsheet interprets the data as being numbers, which makes the use of mathematical formulas on the data possible. By default (without action on your part), the data recognized as being numbers will be right justified in the cell.
Alphanumeric Data Entry
When entering letters in a cell, the spreadsheet interprets the data as being alphanumeric, thus blocking the option of using mathematical tools on the data. By default (without action on your part), the data recognized as being alphanumeric will be left justified in the cell.
Entering a Formula
Formulas are expressions that, when they are interpreted by the spreadsheet, allow calculations to be performed using data present in other cells.
To enter a formula in a cell, select the cell and start entering the formula starting with the "equal" sign (=)!
If the syntax of the formula entered after the equal sign (=) is not correct, the spreadsheet will display an error message preceded with the # character! In case of an invalid cell reference, the spreadsheet will display the #REF! value.
A formula may contain references to other cells, expressions, which means operators that allow simple operations to be performed on the values of other cells, as well as functions.
The integrated functions of the spreadsheet allow elaborated calculations to be performed based on values in the spreadsheet, like averages, rounding, etc.
Defining the Format of a Cell
The format of a cell defines the way its value will be displayed by the spreadsheet. To define the format of a cell, click on the cell, go to the menu Format > Cell and choose the Number tab (or Numbers in StarOffice). The spreadsheet will offer a series of formats divided by category:
Excel StarOffice
http://en.kioskea.net/contents/tableur/tablintro.php3
A spreadsheet (or spreadsheet program) is software that permits numerical data to be used and to perform automatic calculations on numbers contained in a table. It is also possible to automate complex calculations by using a large number of parameters and by creating tables called worksheets.
In addition, spreadsheets can also easily produce graphic representations of the data entered:
• histograms
• curves
• sector charts
• ...
Therefore, the spreadsheet is a multi-use tool that works as well for secretarial activities that involve organising large quantities of data, as at the strategic and decisional level by creating graphical representation of synthesised information.
The Main Spreadsheets
Numerous spreadsheets have been produced by the main software companies. The main spreadsheets are:
• Microsoft Excel, in the Microsoft Office office suite
• Sun StarOffice Calc, in the StarOffice suite
• OpenCalc, in the OpenOffice suite
• IBM/Lotus 1-2-3 in the SmartSuite suite
• Corel Quattro Pro in the WordPerfect suite
• KSpread in the KOffice free suite from Linux
Examples in the following articles are based on the Microsoft Excel spreadsheet, but the other spreadsheets contain the same functionalities.
The Concept of a Worksheet
Spreadsheets display data and formulas in a table form (lines and columns) called a worksheet.
A worksheet is made of lines (numbered with numbers) and of columns (numbered with letters). The intersection of a line and a column is called a cell. A cell is therefore represented by a number and a letter.
A worksheet can contain up to 65,536 lines and 256 columns, more that 17 million cells.
Each of the cells on a worksheet may contain values. These values are entered directly (numbers, text, date, etc.) or are automatically calculated by the spreadsheet. This is referred to as formulas, meaning expressions that calculate a value in function of one or more values in other cells on the worksheet.
Spreadsheets have a large number of integrated functions for math calculations, statistics, etc.
Here is an example of a worksheet that contains student's grades for which the spreadsheet automatically calculates the averages:
The Concept of a Cell
A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also used).
Coordinates (called reference styles) are generally grouped into two types, depending on the worksheet:
• The mode known as L1C1 (Line 1, Column 1), where the cell is located by the line number preceded by the letter L and the column number preceded by the letter C. L12C34 designates the cell at the intersection of the 12th line and the 34th column.
• The mode known as A1, where line numbers are designated by numbers and columns by letters. Thus AA17 designates the cell at the intersection of the 27th column and the 17th line.
Most spreadsheets have options to allow both modes to be used. With Excel and StarOffice, go into Tools > Options > General and click the box "L1C1 Reference Style".
Cell References
In order to work with data from cells when performing calculations, it is necessary to reference the cells. Many ways of referencing cells exist:
• absolute reference
• relative reference
• mixed reference
• named reference
Absolute References
An absolute reference represents the method of distinguishing a unique cell in a worksheet. Depending on the reference mode (L1C1 or A1), the absolute reference will
be written differently:
• In L1C1 mode: a cell's absolute reference is written by preceding the number by a letter L and the column number by the letter C.
LLineNumberCColumnNumber
For example, L12C24 represents the cell located at the intersection of line 12 and column 24.
• In A1 mode: a cell's absolute reference is written by preceding the line number and the column number by the $ sign.
$ColumnLetter$LineNumber
For example, $AC$34 represents the cell located at the intersection of the indicated column, AC, and line, 34.
Relative References
A cell's relative reference is the expression of its position relative to another cell. Thus, the relative reference gives the difference (in terms of the number of lines and columns) between the cell (called reference) and the target cell (called referenced cell).
By convention, upward difference along the vertical axis and difference towards the left along the horizontal axis are negative.
• In L1C1 mode: the relative difference of a cell is shown as cell coordinates in parenthesis:
L(LineNumber) C(ColumnNumber)
For example, L(3)C(-2) represents a cell located 3 lines lower and 2 columns left of the reference cell:
When there is no difference, a zero is not required in the parenthesis. Thus, L(0)C(12) may be written as LC(12).
• In A1 mode, the expression of the difference between the cells is hidden. In effect, a relative reference in A1 mode is implicit: just click on the coordinates of the target cell (referenced) without writing the $ sign:
Mixed References
A mixed reference is a reference where the horizontal position is expressed in an absolute manner and the vertical position in a relative manner, or vice-versa.
• In L1C1 notation, a mixed reference could look like L2C(3) or L(4)C17.
• In A1 notation, a mixed reference could look like $C5 or F$18.
Named References
A name may be given to a cell or to a group of cells.
To name a cell in Excel, select the cell or the range of cells to be named and go to Insert > Name > Define
When a cell or the range of cells has a name (the term label is sometimes used), it may be referenced by name. This functionality is especially useful when certain cells or cell ranges contain characteristic data because they may be referenced by name even if the cell or the group of cells has been moved.
For example, on an invoice, using a cell name such as total_bt for the cell that gives the total of an order before taxes is a good idea. You can also create a cell called VAT that contains the value of the VAT. Thus, when you need to calculate the total with taxes included, it is as easy as multiplying the cell called total_bt with the cell called VAT.
Comments
Commentary (sometimes called an annotation) may be added to a cell to provide additional information that we do not wish (or that we cannot) show on the worksheet.
To add a comment to a cell in Excel, select the cell or the range of cells to be named and then go to Insert > Comment or click on the cell or group of cells with the right mouse button and choose Insert a comment.
The small red triangle on the upper right of the cell D5 shows that the cell has a comment. This encourages the user to slide the mouse over the cell to read its contents.
Cell Content
A cell of a worksheet can contain a value or be empty. The value of a cell has two essential characteristics:
• a type, which means the intrinsic type of the data. There are generally three types of values:
o numeric values, for example 3.1415927, 58%, or 1984.
o alphanumeric values, for example Table2, Title or Name.
o formulas, which means expressions that represent calculations based on data and performed by the spreadsheet.
• a format that represents the way the spreadsheet displays the data. For example, if it is a number, the spreadsheet can be set to display it as an integer, as a decimal number (as well as the number of decimal places displayed), etc.
The cell may have a style, which means a particular visual appearance (font, size, color, border, etc.), independent of the value it contains.
Numeric Data Entry
When entering numbers in a cell (including monetary signs, percentages, etc.), the spreadsheet interprets the data as being numbers, which makes the use of mathematical formulas on the data possible. By default (without action on your part), the data recognized as being numbers will be right justified in the cell.
Alphanumeric Data Entry
When entering letters in a cell, the spreadsheet interprets the data as being alphanumeric, thus blocking the option of using mathematical tools on the data. By default (without action on your part), the data recognized as being alphanumeric will be left justified in the cell.
Entering a Formula
Formulas are expressions that, when they are interpreted by the spreadsheet, allow calculations to be performed using data present in other cells.
To enter a formula in a cell, select the cell and start entering the formula starting with the "equal" sign (=)!
If the syntax of the formula entered after the equal sign (=) is not correct, the spreadsheet will display an error message preceded with the # character! In case of an invalid cell reference, the spreadsheet will display the #REF! value.
A formula may contain references to other cells, expressions, which means operators that allow simple operations to be performed on the values of other cells, as well as functions.
The integrated functions of the spreadsheet allow elaborated calculations to be performed based on values in the spreadsheet, like averages, rounding, etc.
Defining the Format of a Cell
The format of a cell defines the way its value will be displayed by the spreadsheet. To define the format of a cell, click on the cell, go to the menu Format > Cell and choose the Number tab (or Numbers in StarOffice). The spreadsheet will offer a series of formats divided by category:
Excel StarOffice
http://en.kioskea.net/contents/tableur/tablintro.php3
Subscribe to:
Posts (Atom)