3.7+Database

=3.7 Databases=

Introduction
Databases lie at the heart of most IT systems whether in businesses, organizations or other institutions. Databases enable organizations to maintain accurate and comprehensive records. In order to appreciate the role that databases play, the ITGS student must have an understanding of how they work, which can only be gained from the design and creation of basic relational databases as well as by examining how databases are used in specified scenarios (for instance, schools, retail stores, online shopping, online reservations). The increasing use of databases raises a range of social impacts and ethical issues such as the rights of individuals with respect to the storage and potential sale of their personal data or the ease of data mining and data matching. Students are expected to discuss these issues and, where appropriate, evaluate possible solutions.(IBO)

Database organization
• Table A table is a set records (rows) that have a series of fields (cloumns) and is an organised way to display data.

• Field, data types, key field/primary key, secondary key __Fields__ Fields are the columns in a table

__Data Types__ For each field there are different data types available. These data types allow the user to have a variety of forms to choose from and there fore being able to choose the most suitable one. These vary from Boolean, Number, Date and Text among others.

__Key Field/Primary Key__ A key field is a field that has records that are unique throughout the whole field, meaning values in the records of the that field can never repeat themselves.

__Secondary Key__ This is simply the same as a primary key, and is used when there is more than one field that can be considered a key field.

• Record Records are the rows in a table

• Flat-file database, relational database, normalization __Flat file Database__ This is a database that usually contains only one table and has no relationship in the database.

__Relational Database__ These databases contain more than one table that are connected to each other and have a "relationship"

• Database management system A Database management system is a set of computer programs that control the creation, maintenance and the use of a database. • Specialized databases: for example, web databases, online encyclopedias

Functions
• Data validation: data types, range check, check digit, field size, input mask, drop-down list

• Queries: for example, searching, sorting, filtering, use of Boolean operators (AND, NOT, OR) A Query is a special way to retrieve information from a database. It is basically a "question" that you ask the database. When you create a query you can choose what fields you want to use from whatever tables you want, and it allows you to sort and filter certain information. For example, if you had a table which had physical characteristics of a group of people, in a query you could specify in the "hair" field that you only want to see people with blonde hair. The database would then produce a table which only contained people from the original group with blonde hair.

• Data entry form This is basically a structured page where users can input their data in an organised form. It can allow individual users to input information that would be needed for a table be produced

• Report generation Databases have a function that allows that user to produce a printable, presentable form of displaying tables and queries in the database. Reports are mainly used for when people want to produce hard copies data in a database as it has various design options that allow it to be more visually user friendly and more organised than simply tables with no visually captivating design.

• Macros A Macro is a rule or pattern that specifies how a certain input sequence should be mapped to an output sequence according to a defined procedure.The idea behind a Macro is to make available to the programmer a sequence of computing instructions as a single program statement, making the programming easier and with less chance of error.

• Transfer of data between databases and other applications Database applications such as Microsoft Access allow data from the database to be exported and imported to other programs such as excel or word. This is extremely useful as it allows data to be displayed and dealt with by all types of applications. For example, if there is data in the database that a user wants to perform calculations with, they can export this data from Access to Excel and from there use the functions of excel to perform the desired calculations on the data. It can also work the other way as for example if someone wants to make certain queries to some data that they have on Microsoft Word, Access can simply import the data and then use the query function to produce the users requirement.

Data storage and access
• Data integrity, reliability, redundancy __Data Integrity__ This refers to the accuracy and completedness of data. Data loses its integrity when for example it is accidentally changed or purposely tempered with.

__Data Reliability__ This refers to the accuracy of data and how it compares to the real world. Data can be unreliable if it is incorrectly entered or if it becomes outdated

__Data Redundancy__ This occurs when a database has a field that is repeated in two or more tables. This should be avoided as it can lead to data anomolies and corruption

• Data matching, data mining __Data matching__ This is when you compare records in two different databases to find matches that answer a specific question.

__Data Mining__ This describes the act of looking through and analyzing a large amount of data, and attempting to find patterns and certain relationships within the data. Data mining is used in things such as marketing and surveillance.

• Database security Database security is the process by which one protects a database from unintended activity. This could be authenticated misuse, malicious attacks or simply mistakes made by the creators or users. Five main examples of information security are:
 * [|Access Control]
 * [|Auditing]
 * [|Authentication]
 * [|Encryption]
 * [|Integrity controls]

Practical database techniques
• Table

• Field, data types, key field/primary key, secondary key

• Record

• Linking tables to create a relational database A user can link tables to create a relational database. These tables must have a column that other tables in the database can key on to gather information from that table.


 * < LName ||< FName ||< City ||< Age ||< Salary ||
 * < Smith ||< John ||< 3 ||< 35 ||< $280 ||
 * < Doe ||< Jane ||< 1 ||< 28 ||< $325 ||
 * < Brown ||< <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">Scott ||< <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">3 ||< <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">41 ||< <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">$265 ||
 * < <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">Howard ||< <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">Shemp ||< <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">4 ||< <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">48 ||< <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">$359 ||
 * < <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">Taylor ||< <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">Tom ||< <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">2 ||< <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">22 ||< <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">$250 ||

The table below matches the numbers of the cities in the above table with a specific city

<span style="display: block; font-family: Arial,Helvetica,sans-serif; font-size: 13px; line-height: normal; text-align: left;"> By storing this in a seperate table, the database can create a single small table with the locations that can be used for other purposes by other tables in the database.
 * < <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">City # ||< <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">City Name ||
 * < <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">1 ||< <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">Boston ||
 * < <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">2 ||< <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">London ||
 * < <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">3 ||< <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">New York ||
 * < <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">4 ||< <span style="font-family: Arial,Helvetica,sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">Los Angeles ||

• Data maintenance: changing, editing, deleting records Data maintenance is an activity that is designed to keep a database runing smoothly. The most common methods of maintaining a database are adding, deleting, changing and updating records.

• Queries: for example, searching, sorting, filtering, use of Boolean operators (AND, NOT, OR)

• Data entry form • Report generation • Creating and editing simple macros • Mail merge

rss url="http://groups.diigo.com/group/mjb_itgs/rss/tag/3.7_databases/84183/096cd7e10b07daee190543bb00b250ca" link="true" number="10"