Unit 4 What is a Database?

A database refers to a collection of logically related information

organized so that it can be easily accessible, managed, and
updated. Databases are generally accessed electronically from a
computer system and are usually controlled by a database
management system (DBMS). The database administrator (DBA)
is the individual responsible for managing the databases,
including database security, access control, backup, and disaster
recovery.
What is Data?

Before we get into the concept of a database, we should first

understand what data is. Put simply, data are pieces of
information or facts related to the object being considered. For
example, examples of data relating to an individual would be the
person’s name, age, height, weight, ethnicity, hair color, and
birthdate. Data is not limited to facts themselves, as pictures,
images, and files are also considered data.

There are a few key terms that would be useful to help one
understand data more, particularly the relation between data and
databases.

Fields: Within a database, a field contains the most detailed

information about events, people, objects, and transactions.

Record: A record is a collection of related fields.

Table: A table is a collection of related records with a unique

table name

Database: A database is a collection of related tables.

What is the Role of Databases in an Enterprise?

Enterprises typically make use of both internal databases and

external databases. Internal databases typically include
operational databases and data warehouses. The former,
operational databases, refer to databases that are actively used
in the operations of the business, such as accounting, sales,
finance, and HR.

Data warehouses contain data collected from several sources,

and the data contained within are generally not used for routine
business activities. Instead, data warehouses are usually used for
business intelligence purposes. External databases refer to
databases external to an organization and are generally accessed
over the Internet and are owned by other organizations. An
example of an external database is the SEC database.

Components of a Database

The five major components of a database are:

1. Hardware

Hardware refers to the physical, electronic devices such as

computers and hard disks that offer the interface between
computers and real-world systems.

3. Software

Software is a set of programs used to manage and control the

database and includes the database software, operating system,
network software used to share the data with other users, and the
applications used to access the data.
3. Data

Data are raw facts and information that need to be organized and
processed to make it more meaningful. Database dictionaries are
used to centralize, document, control, and coordinate the use of
data within an organization. A database is a repository of
information about a database (also called metadata).

4. Procedures

Procedures refer to the instructions used in a database

management system and encompass everything from instructions
to setup and install, login and logout, manage the day-to-day
operations, take backups of data, and generate reports.

5. Database Access Language

Database Access Language is a language used to write

commands to access, update, and delete data stored in a
database. Users can write commands using Database Access
Language before submitting them to the database for execution.
Through utilizing the language, users can create new databases,
tables, insert data, and delete data.
What is a Database Management System (DBMS)?

A Database Management System (DBMS) is a well-known term in

data analysis. It refers to a collection of programs that enable
users to access databases and manipulate, maintain, report, and
relate data. A DBMS is often used to reduce data redundancy,
share data in a controlled way, and reduce data integrity
problems. DBMS is not an information system but is simply
software.

The relational model, which saves data in table formats, is the

most widely used DBMS. The relational DBMS organizes
information into rows, columns, and tables, making it easier to
find relevant information. Relational databases are popular
because they are easy to extend, and new data categories can be
added after the original database is created without large
amounts of modification.
The Structured Query Language (SQL) is considered the standard
user and application program interface for a relational database,
and all relational DBMS software supports SQL. Examples include
FileMaker Pro, Microsoft Access, Microsoft SQL Server, MySQL,
and Oracle.

 Field-It is defined as a unit of meaningful information about an entity like date of

flight, name of passenger, address etc.
 Record– It is a collection of units of information about a particular entity. Passenger
of an airplane, an employee of an organization, or an article sold from a store.
 File-A collection of records involving a set of entities with certain aspects in common
and organized for some particular purpose is called a file. For example collection of
records of all passengers.

SORTING AND INDEXING OF DATABASE FILES

Indexing is a method that is used to improve the data retrieval speed in a table of a
database. An index could be created using a single or more columns in a table and
the index is stored in a separate file. Indices can be created as unique indices or
non-unique indices. Sorting is the process or arranging items in a set in a specific
order. Sorting a table would create a copy of the table in which the rows may have a
different order than the original.

What is Indexing?

Indexing is a method that is used to improve the data retrieval speed in a table of a
database. An index could be created using a single or more columns in a table and
the index is stored in a separate file. This file contains the logical order of rows along
with their physical position in the table. The space required by an index file is
typically less than the space required to store the table. Unique indices will prevent
the table from containing duplicate values of the index. Indexing would make the
data retrieval more efficient. Consider the following SQL statement.

SELECT first_name, last_name FROM people WHERE city = ‘New York’

If the above query was executed in a table that does not have an index created
using the city column, it has to scan the whole table and look at the city column of
each row to find all the entries withcity=”New York”. But if the table had an index, it
will simply follow using a B-tree data structure until the entries with the “New York” is
found. This would make the search more efficient.

What is Sorting?

Sorting is the process or arranging items in a set in a specific order. Sorting a table
would create a copy of the table in which the rows may have a different order than
the original. Storing the new table would require an amount of space similar to that
of the original table. Due to this reason sorting is used less frequently; only used
when a new copy of the sorted table is required. Sorting is allowed using multiple
 fields, such as sorting addresses using the states and then sort using the cities
inside the states.

What is the difference between Indexing and Sorting?

Indexing and sorting are two methods that can be used to create an order in a data
table. Indexing would create an index file that contains only the logical order of rows
along with their physical position in the table whereas with sorting, a copy of the
sorted table has to be stored. Usually, the index file requires lesser space than
storing a sorted table. Furthermore, some operations like running queries and
searching would be faster with a table with indexes. In addition, indexing would not
change the original order in the table, while sorting would change the order of rows.
Also, operation such as linking tables would require having an index.

Create a query, form, or

report
Create a select query
Create a query to focus on specific data.
1. Select Create > Query Wizard .
2. Select Simple Query, and then OK.
3. Select the table that contains the field, add
the Available Fields you want to Selected Fields,
and select Next.
4. Choose whether you want to open the query in
Datasheet view or modify the query in Design view, and
then select Finish.

For more info, see Get started with queries or Create a

simple select query.
 Create a split form
A split form gives you two views of the data at the
same time — a Datasheet view and a Form view. For
example, use the Datasheet view to find a record and
the Form view to edit it.
1. In the Navigation Pane, select a table or query that
contains the data.
2. Select Create > More Forms > Split Form.

For more info, see Create a split form.

DESIGNING A QUERY

What Does Query Mean?

A query is a request for data or information from a database table or
combination of tables. This data may be generated as results returned by
Structured Query Language (SQL) or as pictorials, graphs or complex results,
e.g., trend analyses from data-mining tools.

One of several different query languages may be used to perform a range of

simple to complex database queries. SQL, the most well-known and widely-
used query language, is familiar to most database administrators (DBAs).

A Subquery or Inner query or a Nested query is a query within another SQL query and
embedded within the WHERE clause.
A subquery is used to return data that will be used in the main query as a condition to
further restrict the data to be retrieved.
Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE
statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
Example
Consider the CUSTOMERS table having the following records −
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 35 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

Now, let us check the following subquery with a SELECT statement.

SQL> SELECT *
FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS
WHERE SALARY > 4500) ;

CREATE
CREATE statements is used to define the database structure schema:

Syntax:
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
INSERT:
This is a statement is a SQL query. This command is used to insert data into the row
of a table.

Syntax:
INSERT INTO TABLE_NAME (col1, col2, col3,.... col N)
VALUES (value1, value2, value3, .... valueN);

DELETE:
This command is used to remove one or more rows from a table.

Syntax:
DELETE FROM table_name [WHERE condition];
 SELECT:
This command helps you to select the attribute based on the condition described
by the WHERE clause.

Syntax:
SELECT expressions
FROM TABLES
WHERE conditions;
Create a report
1. Select Create > Report Wizard.
2. Select a table or query, double-click each field
in Available Fields you want to add it to the report,
and select Next.
3. Double-click the field you want to group by, and
select Next.
4. Complete the rest of the wizard screens, and
select Finish.

For more info, see Create basic reports or Create a

simple report.