Computer Notes

BANKERS ACADEMY, PALAKKAD

1.  Databases and the DBMS

What data inconsistency is and why it occurs?

Data inconsistency exists when different and conflicting versions of the same data appear in different places. Data inconsistency creates unreliable information, because it will be difficult to determine which version of the information is correct. (It’s difficult to make correct – and timely – decisions if those decisions are based on conflicting information.)

Data inconsistency is likely to occur when there is data redundancy. Data redundancy occurs when the data file/database file contains redundant – unnecessarily duplicated – data. That’s why one major goal of good database design is to eliminate data redundancy.

Database— is shared, integrated computer structure that houses:

                        End user data (raw facts)

                        Metadata (data about data)

1.2.1 The different types of databases.

Databases are classified based on the number of users, database site location, and data use:

• Number of users

Single-user

Multiuser

Workgroup

Enterprise

• Database site location

Centralized

Distributed

• Database use

Transactional (production) database

Data warehouse database

What are the main components of a database system environment?

The system’s components are hardware, software, people, procedures, and data.

What is metadata?

Metadata is data about data. That is, metadata define the data characteristics such as the data type (such as character or numeric) and the relationships that link the data. Relationships are an important component of database design. What makes relationships especially interesting is that they are often defined by their environment.

For instance, the relationship between EMPLOYEE and JOB is likely to depend on the organization’s definition of the work environment. For example, in some organizations an employee can have multiple job assignments, while in other organizations – or even in other divisions within the same organization – an employee can have only one job assignment.

The differences between transaction databases and data warehouse databases.

A transaction (production) database reflects the daily operations of an organization. As the name implies, a transaction database records transactions such as the sale of a product, the enrolment of a student, or the opening of a checking account. Such transactions are time-critical and must be recorded accurately and immediately. Given their critical role in the day-to-day operations – and given the fact that transaction database data are usually the data source for data warehouse databases – much of the focus on the design of transaction databases.

Data warehouse databases focus primarily on the storage of data to be used as the information basis for tactical and strategic decision making. Such decision making typically requires extensive data manipulation to extract information from data. In short, the data warehouse data are the source for decision support systems.

Data warehouse databases are far less time-critical than transaction databases; the former record the historical data record that will be used to formulate pricing decisions, sales forecasts, market positioning, and so on.

Data warehouse databases typically store complex data derived from many sources. To make it easier to store and retrieve such complex data, the data warehouse database structure ends to be quite different from that of the transaction database.

2.  Microwave transmission

Microwave transmission refers to the technology of transmitting information or power by the use of radio waves whose wavelengths are conveniently measured in small numbers of centimeters; these are called microwaves. This part of the radio spectrum ranges across frequencies of roughly 1.0 gigahertz (GHz) to 30 GHz. These correspond to wavelengths from 30 centimeters down to 1.0 cm.

Microwaves are widely used for point-to-point communications because their small wavelength allows conveniently-sized antennas to direct them in narrow beams, which can be pointed directly at the receiving antenna.  This allows nearby microwave equipment to use the same frequencies without interfering with each other, as lower frequency radio waves do. Another advantage is that the high frequency of microwaves gives the microwave band a very large information-carrying capacity; the microwave band has a bandwidth 30 times that of all the rest of the radio spectrum below it. A disadvantage is that microwaves are limited to line of sight propagation; they cannot pass around hills or mountains as lower frequency radio waves can.

 3.  Query types
The Most Recent Member query you created is called a Select query. Access lets you create five different types of query:
Select queries. Used to retrieve data from one or more tables and display the results in a datasheet, which you can save or modify. You can also use Select queries to group records and calculate sums, averages and so on.
Parameter queries. For creating on-the-fly queries which prompt the user for criteria at the time the query is run. For example, you can create a parameter query that answers the question: Which countries have a population greater than X and less than Y? Each time you run the query, it will prompt you for the values of X and Y. Thus you can use the same query repeatedly to discover different information.
Crosstab queries. Used to summarise data from one field and group it in tabular fashion according to two criteria.
Action queries. Queries that make changes to the records in a table. There are four type of action queries: Delete queries remove records from a table; Update queries make global changes to a group of records in a table; Append queries add records from one or more tables to the end or one or more tables; Make-table queries create a new table from all or part of the data in an existing table.
SQL queries. A query created using SQL, which is a highly advanced querying language. SQL queries give you enormous flexibility, but require a high degree of expertise to use effectively.
More about quiries:
Queries can be used to quickly analyze and sort information that is in an Access database. A query allows you to present a question to your database by specifying specific criteria.
Queries allow you to specify:

• The table fields that appear in a query.
• The order of the fields in a query.
• Filter and sort criteria for each field in a query.

Select queries are the most commonly used type of queries in Access. They allow you to choose which fields and records to display in a new datasheet. Other types of queries include cross tab queries and four types of action queries.
Queries have two views: Design view and Datasheet view. In the Design view, you specify which tables you want to see, which tables they come from, and the criteria that records have to meet in order to appear on the resulting database. Criteria are tests that records have to pass. In the Query Datasheet view, you view the records that are found to meet your criteria.
When you run a query, Access pulls data out of tables and puts the data in a database for you to see. The original table and database stay connected, so that if you make changes to the data on your database, the results of the query also change. When you save a query, you save the query design, rather than the results, so that you can ask the same questions again.
A select query can be used to select certain data from a table or tables. It basically filters and sorts the data and can perform simple calculations, such as summing and averaging.
An easy way to create a select query is to use the Simple Query Wizard. It allows you to select the table fields you want to include in a query. This type of query is very useful when you want to eliminate extra fields, but still want to view every record in a database table.
A limitation of the Simple Query Wizard is that it does not allow you to set sorting parameters for records in order to sort by particular criteria, such as less than, greater than, equal to, etc. You must use a create a query from scratch in order to be able to set parameters.
An end user query tool enables employee access to all data across your business allowing for fast, organised and meaningful reporting to occur easily.
"Ad hoc" reporting systems [queries] allow the users themselves to create specific, customized queries. Typically this would be via a user-friendly GUI-based system without the need for the in-depth knowledge of SQL, or database schema that a programmer would have.

4.  Tiered Applications

In a multi-tiered application, the functionality of the application is separated into isolated functional areas, called tiers. Typically, multi-tiered applications have a client tier, a middle tier, and a data tier (often called the enterprise information systems tier). The client tier consists of a client program that makes requests to the middle tier. The middle tier's business functions handle client requests and process application data, storing it in a permanent datastore in the data tier.

5.  What are DDL statements in SQL? Explain the Create, Alter and Drop commands.

In SQL, DDL stands for Data Definition Language. It is the part of SQL programming language that deals with the construction and alteration of database structures like tables, views, and further the entities inside these tables like columns. It may be used to set the properties of columns as well.

The three popular commands used in DDL are:
Create - Used to create tables, views, and also used to create functions, stored procedures, triggers, indexes etc.

Drop - Used to totally eliminate a table, view, index from a database - which means that the records as well as the total structure is eliminated from the database.

Alter - Used to alter or in other words, change the structure of a table, view, index. This is particularly used when there is a scenario wherein the properties of fields inside a table, view, index are supposed to be updated.

6.  DDL/DML/DCL/TCL COMMANDS

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• MERGE - UPSERT operation (insert or update)
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

7.  JavaBeans are reusable software components for Java. Practically, they are classes written in the Java programming language conforming to a particular convention. They are used to encapsulate many objects into a single object (the bean), so that they can be passed around as a single bean object instead of as multiple individual objects. A JavaBean is a Java Object that is serializable, has a nullary constructor, and allows access to properties using getter and setter methods.

8.  Java is an Object Oriented Programming (OOP) language created by James Gosling of Sun Microsystems. Java script is a scripting language that was created by the fine people at Netscape and was originally known as LiveScript. JavaScript is a (very) distant cousin of Java in that it is also an OOP language. Many of their programming structures are similar. However, JavaScript contains a much smaller and simpler set of commands than does Java. It is easier for the average weekend warrior to understand. 

9.  Firewall (computing), a technological barrier designed to prevent unauthorized or unwanted communications between computer networks or hosts.