Database Usage

Using Databases

Database Concepts

Databases are used everywhere in today's connected world. Every time you make an online purchase with an e-commerce retailer, many databases are accessed by a variety of different applications in order to facilitate your purchase.

A 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.

Database Usage

A database is used both to store information securely and to report on the information it contains. Consequently, database usage involves the following processes and tools:

Creation—this step involves defining what information the database will store, where it will be hosted, and how it will be accessed by clients.

Database Usage

• Import/input—once the database has been created, it must be populated with data records. Records can either be input and updated manually, usually using some type of form, or data might be imported from another source, or both.

• Storage (data persistence)—databases are often used with applications. While an application processes variables and other temporary data internally, this information is lost when the application is terminated. A database represents a way for an application to store data persistently and securely.

Database Usage

• Queries—it is possible in theory to read the information in each table manually, but in order to view information efficiently, a query is used to extract it. A query allows the user to specify criteria to match values in one or more fields and choose which fields to display in the results so that only information of interest is selected.

• Reports—a query might return a large number of rows and be just as difficult to read as a table. A report is a means of formatting and summarizing the records returned by a query so that the information is easy to read and interpret.

Flat File Systems

One common question asked when users are considering a database is, "Why can't I just use a spreadsheet such as Excel?" It's a fair question because 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.

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.

While flat file systems are easy to create they have many drawbacks compared to database systems.

Databases Vs. Flat File Systems

A flat file system might be useful for tasks such as simple order or sales databases used by a single person or small workgroup. A flat file is also a good way of exporting and importing information between systems. Dedicated database software has many advantages over flat files though

While flat file systems are easy to create they have many drawbacks compared to database systems.

Databases Vs. Flat File Systems

• Databases can enforce data types for each column and validate information entered as fields and records. Spreadsheets can mimic some of this functionality but not as robustly. Databases consequently support a wider variety of data formats.

• Databases can manage multiple tables and link the fields in different tables to create complex schemas. In a flat file, all the information is stored within a single table.

• Databases can support tens, hundreds or thousands, or even millions of users concurrently. A single file-based data storage solution does not offer high enough speed for the volumes of transactions (adding and updating records) on enterprise-level systems.

Databases Vs. Flat File Systems

• Databases are also more scalable. Scalability means being able to expand usage without increasing costs at the same rate. For example, in a non-scalable system, doubling the number of users would also double the costs of the system. Database architecture means that extra capacity can be added later with much less investment.

• Databases provide access controls to protect information from unauthorized disclosure and backup/replication tools to ensure that data can be recovered within seconds of it being committed.

Relational Databases

• A relational database is the 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.

Multiple Choice

Why would a flat file system not be the optimum choice for data storage for an online shopping cart application?

• A flat file system is a more complicated file structure than a database

• A flat file system doesn't support multiple concurrent users

• Flat file systems are not used for permanent and long-term data storage.

• Flat file systems are no longer supported by most programming languages.

Primary Key and Foreign Key

Attempting to store a complex set of data within a single table is impractical. For example, if you want to record who has borrowed books from a library, if you have a single LibraryLoans table, you have to duplicate information about the customer each time a loan record is created. It is likely that mistakes could be made inputting this information and the customer's details may change, resulting in inconsistencies in your data records.

Primary Key and Foreign Key

In a relational database, you can have multiple linked tables. If you design the database schema with one Customer table and one Loan table, you can link a single customer record to multiple loan records. If a customer record has to be updated, you can do that once in the Customer table rather than editing lots of records in a monolithic LibraryLoans table.

Primary Key and Foreign 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.

Primary Key and Foreign Key

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.

The 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 Example

To give you an idea about a relational database, think again about a database supporting a lending library. Your database is designed to store and allow retrieval of data concerned with the management of book lending. This database might include a table that stored customer information, another that contained book title information, and finally a table that stored information about the actual lending.

Relational Database Example

Relational Database Example

You can see that the customer information and book information that appears in the Lending table is drawn from the Customer and Book tables respectively. Specifically, the Customer ID is drawn from the Customer table and the Title is drawn from the Book table. The advantage of this approach is that if you must update customer information, you only need to do so in one place—the Customer table.

Relational Database Example

A query can be used to reconstruct the information. For example, if you want to check the customer associated with a particular lending record, your query would select the record from the Lending table then use the join between the Lending and Customer tables to show the values of the name and address fields from the related Customer table record.

Constraints

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 are consistent with what information the field is supposed to store.

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.

Semi structured and Unstructred

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. For example, you can access all fields or only certain fields. Each field has a defined data type, meaning that software that understands the database language (SQL), can parse (interpret) the content of a field easily.

Semi structured and Unstructured

Unstructured data, on the other hand, 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.

Multiple Choice

In a database where each employee is assigned to one project, and each project can have multiple employees, which database design option would apply?

• The Project table has a primary key of ProjectID and a foreign key of EmployeeID. The Employee table has a primary key of EmployeeID.

• The Project table has a primary key of ProjectID. The Employee table has a primary key of EmployeeID and a foreign key of ProjectID.

• The Project table has a primary key of ProjectID. The Employee table has a primary key of EmployeeID and a foreign key of EmployeeID

• The Project table has a primary key of ProjectID and a foreign key of EmployeeID. The Employee table has a primary key of ProjectID.

Semi structured and Unstructred

Sitting somewhere between these two is semi-structured data. Strictly speaking, the 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. Email data, as well as markup languages such as XML, are forms of semi-structured data.

Multiple Choice

How do Relational Database Management Systems (RDBMS) handle multiple users accessing the same database concurrently?

• RDBMS allow multiple users to view a table simultaneously, but only allow one user at a time to view and update a row in a table.

• If two users update a row in a table at exactly the same time, the RDBMS will prompt both users to decide which update should be posted to the database.

• Updates are allowed on a first-come-first-serve basis since there wouldn't ever be two updates on the same row of the same table at exactly the same millisecond.

• Multiple users can view the same record at the same time. If more than one user attempts to update the same record at the same time, the RDBMS locks that record so that only one user at a time can update.

Document and Key/Values Pair Databases

A document database is an 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.Documents would very commonly use markup language such as XML (eXtensible Markup Language) to provide structure.

Document and Key/Values Pair Databases

Documents would very commonly use markup language such as XML (eXtensible Markup Language) to provide structure. A key/value pair database is a means of storing the properties of objects without predetermining the fields used to define an object. A key/value pair table looks like the following:

Document and Key/Values Pair Databases

As you can see, not all properties have to be defined for each object. One widely used key/value format is JavaScript Object Notation (JSON). For example, "user01" could be expressed as the following JSON string:

{ "user01_surname" : "Warren",
  "user01_firstname" : "Andy",
  "user01_age" : 27,
  "user01_marketingconsent" : TRUE }

Document and Key/Values Pair Databases

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.

Drag and Drop

A ​

Placeholder

does enforce data types for each field/column in a ​

Placeholder

; therefore, it can validate that the correct type of data is being entered and ​

Placeholder

in each field. When users enter ​

Placeholder

they don't typically know whether the data is being stored in a database or a flat file .Databases and ​

Placeholder

files are both backed up on a regular basis so that there is always a copy of the data that can be restored.

Drag these tiles and drop them in the correct blank above

data

information

files

table

desk

stored

delete

database

flat

Relational Methods

Database interfaces are the 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.

Multiple Choice

When processing large numbers of transactions, how does processing speed compare between databases and flat file systems?

• Database processing speeds are much faster than flat file speeds.

• Flat files’ processing speeds are much faster than database processing speeds.

• The speed of processing transactions is the same whether the data is stored in a flat file or a database.

• Flat file processing speeds are faster than database processing speeds if the computer’s processor is slower than average.

Data Definition Methods

Data Definition Language (DDL) commands refer to SQL commands that add to or modify the structure of the database. Some examples of data definition commands are:

Data Definition Methods

• CREATE—this command can be used to add a new database on the RDBMS server (CREATE DATABASE) or to add a new table within an existing database (CREATE TABLE). The primary key and foreign key can be specified as part of the table definition.

• ALTER TABLE—this allows you to add, remove (drop), and modify table columns (fields), change a primary key and/or foreign key, and configure other constraints. There is also an ALTER DATABASE command, used for modifying properties of the whole database, such as its character set.

Data Definition Methods

• DROP—this is the command used to delete a table (DROP TABLE) or database (DROP DATABASE). Obviously, this also deletes any records and data stored in the object.

• CREATE INDEX—specifying that a column (or combination of columns) is indexed speeds up queries on that column. The tradeoff is that updates are slowed down slightly (if the column is not suitable for indexing, updates may be slowed down quite a lot.) The DROP INDEX command can be used to remove an index.

There are also SQL commands allowing permissions (access controls) to be configured. These are discussed in the upcoming pages.

Fill in the Blanks

Microsoft SQL Server is an example of what?

Data Manipulation Methods

Data Manipulation Language (DML) commands allow you to insert or update records and extract information from records for viewing (a query):

Data Manipulation Methods

• INSERT INTO TableName—adds a new row in a table in the database.

• UPDATE TableName—changes the value of one or more table columns. This can be used with a WHERE statement to filter the records that will be updated. If no WHERE statement is specified, the command applies to all the records in the table.
  

Data Manipulation Methods

• DELETE FROM TableName—deletes records from the table. As with UPDATE, this will delete all records unless a WHERE statement is specified.

• SELECT—enables you to define a query to retrieve data from a database.

Permissions

SQL supports a secure access control system where specific user accounts can be granted rights over different objects in the database (tables, columns, and views for instance) and the database itself. When an account creates an object, it becomes the owner of that object, with complete control over it. The owner cannot be denied permission over the object. The owner can be changed however, using the ALTER AUTHORIZATION statement.

Database Access Methods

Database access methods are the 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 Access Methods

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.

Multiple Choice

How many students would be represented in a “Student” table containing 150 rows, 17 columns, 1 primary key, and 1 foreign key?

• 150

• 17

• 2,550

• 1

Drag and Drop

The ​

Placeholder

key must be able to uniquely identify each customer from all other customers. In past decades, social security numbers were used for that purpose. Now, a good ​

Placeholder

designer would create a CustomerNumber field to use as the primary ​

Placeholder

and generate unique numbers for each ​

Placeholder

.

Drag these tiles and drop them in the correct blank above

primary

secondary

unique

database

flat

key

customer

Query/Report Builder

There are many users who may need to interact closely with the database but do not want to learn SQL syntax. 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.

Programmatic Access

A 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.

Backups and Data Export

As with any type of data, 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.

Backups and Data Export

It may also be necessary to export data from the database for use in another database or in another type of program, such as a spreadsheet. 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).

Dropdown

A ​

Select answer

can always be used to filter out any columns that a user doesn't want/need to see.

Application Architecture Models

A 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.

Multiple Choice

What is the relationship between queries and reports?

• A report filters the data values to provide only the needed data and a query retrieves it.

• “Report” and “query” are really two terms for the same thing

• A query selects and retrieves the correct data, whereas a report formats and summarizes the output.

• A query produces output on the screen and a report produces output on the printer.

Application Architecture Models

A 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.

Application Architecture Models

In a 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.

Application Architecture Models

An 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. For example, the application may use separate access control or monitoring services.

Multiple Choice

How is a relational table structured?

• A table is made up of fields, where all fields are placed into random rows.

• A table is made up of columns, where each column has its own unique collection of rows.

• A table is made up of rows, where each row has its own unique collection of columns.

• A table is made up of rows and columns, where all of the rows include the same columns.