Module 2 / Unit 4 Using Databases

dump

A dump is a copy of the database or table schema along with the records expressed as SQL statements. These SQL statements can be executed on another database to import the information. Most database engines support exporting data in tables to other file formats, such as Comma Separated Values (.CSV) or native MS Excel (.XLS).

Query/Report Builder

A query or report builder provides a GUI for users to select actions to perform on the database and converts those selections to the SQL statements that will be executed.

Direct/Manual Access

Administrators might use an administrative tool, such as phpMyAdmin, to connect and sign in to an RDBMS database. Once they have connected, they can run SQL commands to create new databases on the system and interact with stored data. This can be described as direct or manual access.

non-relational

Document databases and key/value pair databases are non-relational because there are no formal structures to link the different data objects and files. This does not mean that relationships between the data items cannot be found though. Non-relational database systems use searches and queries to summarize and correlate data points.

flat file

Excel enables you to store your information in sheets, which are broadly analogous to tables, with rows and columns of data. This is an example of a flat file data storage and access system rather than a database

primary key.

For this to work, in any given table, each record has to be unique in at least one way. This is usually accomplished by designating one column as a primary key. Each row in the table must have a unique value in the primary key field. This primary key is used to define the relationship between one table and another table in the database. When a primary key in one table is referenced in another table, then in the secondary table, that column is referred to as a foreign key.

Relational Database Management Systems (RDBMS) use Structured Query Language (SQL) to maintain and query data in the database. Examples of RDBMS include:

Microsoft SQL Server. ■ Oracle Database. ■ MySQL. ■ Microsoft Office Access.

Garbage In, Garbage Out (GIGO)

One of the functions of an RDBMS is to address the concept of Garbage In, Garbage Out (GIGO). It is very important that the values entered into fields conform are consistent with what information the field is supposed to store.

Comma Separated Values (CSV)

Spreadsheets are not the only kind of flat file data store. Another example is a plain text file with delimiters for each column. A Comma Separated Values (CSV) file uses commas to identify the end of a column and a line feed for each row.

constraints

When defining the properties of each field, as well as enforcing a data type, you can impose certain constraints on the values that can be input into each field. A primary key is an example of a constraint. The value entered or changed in a primary key field in any given record must not be the same as any other existing record. Other types of constraints might perform validation on the data that you can enter.

structured

When you store your information in a relational database, it is stored in a structured way. This structure enables you to more easily access the stored information and gives you flexibility over exactly what you access.

n-tier

application architecture can be used to mean either a two-tier or three-tier application, but another use is an application with a more complex architecture still.

three-tier

application, the presentation and application layers are also split. The presentation layer provides the client front-end and user interface and runs on the client machine. The application layer runs on a server or server cluster that the client connects to. When the client makes a request, it is checked by the application layer, and if it conforms to whatever access rules have been set up, the application layer executes the query on the data layer which resides on a third tier and returns the result to the client. The client should have no direct communications with the data tier.

two-tier

client-server application separates the database engine, or back-end or data layer, from the presentation layer and the application layer, or business logic. The application and presentation layers are part of the client application. The database engine will run on one server (or more likely a cluster of servers), while the presentation and application layers run on the client.

Data Manipulation Language (DML)

commands allow you to insert or update records and extract information from records for viewing (a query)

Data Definition Language (DDL)

commands refer to SQL commands that add to or modify the structure of the database.

semi-structured data

data lacks the structure of formal database architecture. But in addition to the raw unstructured data, there is associated information called metadata that helps identify the data.

one-tier or standalone.

database application can be designed for any sort of business function. Customer Relationship Management (CRM) and accounting are typical examples. If the application front-end and processing logic and the database engine are all hosted on the same computer, the application architecture can be described as one-tier or standalone.

document database

example of a semi-structured database. Rather than define tables and fields, the database grows by adding documents to it. The documents can use the same structure or be of different types. The database's query engine must be designed to parse each document type and extract information from it.

database

is an organized collection of information. The information is stored in a structured manner for easier access. Typically, a database consists of tables of information, organized into columns and rows. Each row represents a separate record in the database, while each column represents a single field within a record.

Backups and Data Export

it is vital to make secure backups of databases. Most RDBMS provide stored procedures that invoke the BACKUP and RESTORE commands at a database or table level.

key/value pair database

means of storing the properties of objects without predetermining the fields used to define an object

Database access methods

processes by which a user might run SQL commands on the database server or update or extract information using a form or application that encapsulates the SQL commands as graphical controls or tools.

Database interfaces

processes used to add/update information to and extract (or view) information from the database. In an RDBMS, the use of Structured Query Language (SQL) relational methods is critical to creating and updating the database. These relational methods can be split into two types; those that define the database structure and those that manipulate information in the database.

Unstructured data

provides no rigid formatting of the data. Images and text files, Word documents and PowerPoint presentations are examples of unstructured data. Unstructured data is typically much easier to create than structured data. Documents can be added to a store simply and the data store can support a much larger variety of data types than a relational database can.

Programmatic Access

software application can interact with the database either using SQL commands or using SQL commands stored as procedures in the database. Most programming languages include libraries to provide default code for connecting to a database and executing queries

schema

structure of the database in terms of the fields defined in each table and the relations between primary and foreign keys is referred to as the schema.

relational database

type we have been describing so far. A relational database is a highly structured type of database. Information is organized in tables (known as relations). A table is defined with a number of fields, represented by the table columns. Each field can be a particular data type. Each row entered into the table represents a data record.