Applied Programming/Databases

This lesson introduces database access through programming.

Objectives and Skills

Objectives and skills for this lesson include:

• Create, read, and search for elements in databases
• Utilize Structured Query Language(SQL) for database management
• Format header and table columns for database entities

Readings

1. Wikipedia: Database
2. Wikipedia: Relational model
3. Wikipedia: SQL
4. Wikipedia: SQLite

Multimedia

1. YouTube: Database Basics
2. YouTube: Update & Delete
3. YouTube: SQL Commands
4. YouTube: Python SQLite Basics
5. YouTube: Python SQLite Tutorial
6. YouTube: DB Browser for SQLite Tutorial
7. YouTube: Using the Cursor in SQLite
8. YouTube: Using variables in SQLite.
9. YouTube: Node JS SQlite tutorial - How to create a database, table, and insert data

Examples

• JavaScript SQLite
• Python3 MS SQL
• Python3 MySQL
• Python3 SQLite
• Python3 SQLAlchemy
• Python3 PostgreSQL

Activities

Tutorials

1. Complete one or more of the following tutorials:
   • W3Schools: SQL Tutorial
   • SQLZOO: SQL Tutorial
   • SQLBolt: Learn SQL
   • TutorialsPoint: SQL
   • Khan Academy: Intro to SQL

Create a Database

1. Download and install SQLiteBrowser: DB Browser for SQLite. Run the program and create a new database named Northwind.
2. Copy the SQL statements from Northwind SQL. Use Execute SQL in the DB Browser for SQLite to run the script and create the Northwind tables data.

Read a Database

1. Create a program to list the tables in the Northwind database. The following query may be used to select table names:
       SELECT name FROM sqlite_master WHERE type='table';
2. Use a list to save table names. Provide an interface that allows the user to select one of the listed tables and then display all records in the table.
3. Use the cursor.description attribute and a list or dictionary to save field names for the table. Determine the maximum length of each field and display the data in appropriately sized columns. Include column headings and row numbers.
4. For each of the above, use separate functions for each type of processing. Avoid using global variables by passing parameters and returning results. Include appropriate data validation and parameter validation. Add program and function documentation, consistent with the documentation standards for your selected programming language.

Modify a Database

1. Create a program to list the tables in the Northwind database. Use a list to save table names. Provide an interface that allows the user to select one of the listed tables and then display all records in the table with column names and row numbers.
2. Allow users to choose (I)nsert, (U)pdate, or (D)elete. If they choose Insert, ask for field values and insert the new record. If they choose Update, provide an interface that allows the user to select a row and field to update and then update the record. If they choose Delete, allow the user to select the record and then delete the record.
3. For each of the above, use separate functions for each type of processing. Avoid using global variables by passing parameters and returning results. Include appropriate data validation and parameter validation. Add program and function documentation, consistent with the documentation standards for your selected programming language.

Lesson Summary

• A database on the highest level is simply an organized collection of data.
• Existing DBMSs provide various functions that allow management of a database and its data which can be classified into four main functional groups:
  • Data definition – Creation, modification and removal of definitions that define the organization of the data.
  • Update – Insertion, modification, and deletion of the actual data.
  • Retrieval – Providing information in a form directly usable or for further processing by other applications. The retrieved data may be made available in a form basically the same as it is stored in the database or in a new form obtained by altering or combining existing data from the database.
  • Administration – Registering and monitoring users, enforcing data security, monitoring performance, maintaining data integrity, dealing with concurrency control, and recovering information that has been corrupted by some event such as an unexpected system failure.
• A database management system provides three views of the database data:
  • The external level defines how each group of end-users sees the organization of data in the database. A single database can have any number of views at the external level.
  • The conceptual level unifies the various external views into a compatible global view. It provides the synthesis of all the external views. It is out of the scope of the various database end-users, and is rather of interest to database application developers and database administrators.
  • The internal level (or physical level) is the internal organization of data inside a DBMS. It is concerned with cost, performance, scalability and other operational matters. It deals with storage layout of the data, using storage structures such as indexes to enhance performance. Occasionally it stores data of individual views (materialized views), computed from generic data, if performance justification exists for such redundancy. It balances all the external views' performance requirements, possibly conflicting, in an attempt to optimize overall performance across all activities.
• Database languages are special-purpose languages, which allows one or more of the following tasks, sometimes distinguished as sublanguages:
  • Data control language (DCL) – controls access to data;
  • Data definition language (DDL) – defines data types such as creating, altering, or dropping and the relationships among them;
  • Data manipulation language (DML) – performs tasks such as inserting, updating, or deleting data occurrences;
  • Data query language (DQL) – allows searching for information and computing derived information.
• The DBMS provides various functions that allow entry, storage, and retrieval of large quantities of information and provides ways to manage how that information is organized.
  • In the relational model, data gets organized into tuples or records (the aggregate of which forms a relation or table) and is further subdivided into columns or attributes which describe individual records.
• The relation can be thought of as some entity. The row is an instance of that entity, and its columns describe features attributed to that particular instance.
  • For example, let's consider a 'Person' entity. An instance of that entity is a realized person like 'Joe'. Joe has common features shared by everybody (skin and hair), but specific characterizations that may or may not differ in manifestation from other people (black skin, black hair), fleshing out his unique persona. Each of these idiosyncrasies or traits is a column value in and of itself.
• The purpose of the relational model is to provide a declarative method for specifying data and queries.
• The consistency of a relational database is enforced, not by rules built into the applications that use it, but rather by constraints, declared as part of the logical schema and enforced by the DBMS for all applications.
  • Constraints make it possible to further restrict the domain of an attribute. For instance, a constraint can restrict a given integer attribute to values between 1 and 10.
  • Constraints provide one method of implementing business rules in the database and support subsequent data use within the application layer.
• A relation consists of a heading and a body.
  • A heading is a set of attributes.
  • A body (of an n-ary relation) is a set of n-tuples.
  • The heading of the relation is also the heading of each of its tuples.
• SQL:
  • The most popular database model is the relational model which is often associated with SQL, a query language.
  • Most relational databases use the SQL data definition and query language; these systems implement what can be regarded as an engineering approximation to the relational model.
    • A table in an SQL database schema corresponds to a predicate variable; the contents of a table to a relation; key constraints, other constraints, and SQL queries correspond to predicates.
  • SQL is a domain-specific (limited and narrowly specialized in use) query language for interfacing with databases.
    • With SQL you can retrieve, update, insert, and delete information using standardized commands.
  • SQL offers two main advantages:
    • it introduced the concept of accessing many records with one single command.
    • it eliminates the need to specify how to reach a record, e.g. with or without an index.
  • The SQL language is subdivided into several language elements, including:
    • Clauses, which are constituent components of statements and queries. (In some cases, these are optional.)
    • Expressions, which can produce either scalar values, or tables consisting of columns and rows of data
    • Predicates, which specify conditions that can be evaluated to SQL three-valued logic (3VL) (true/false/unknown) or Boolean truth values and are used to limit the effects of statements and queries, or to change program flow.
    • Queries, which retrieve the data based on specific criteria. This is an important element of SQL.
    • Statements, which may have a persistent effect on schemata and data, or may control transactions, program flow, connections, sessions, or diagnostics.
  • SQL's controversial "null" value is neither true nor false (predicates with terms that return a null value return null rather than true or false). Features such as outer-join depend on null values.
• SQLite:
  • SQLite stores the entire database (definitions, tables, indices, and the data itself) as a single cross-platform file on a host machine.
    • It implements this simple design by locking the entire database file during writing. SQLite read operations can be multitasked, though writes can only be performed sequentially.
  • The design goals of SQLite were to allow the program to be operated without installing a database management system or requiring a database administrator.
  • SQLite uses an unusual type system for an SQL-compatible DBMS; instead of assigning a type to a column as in most SQL database systems, types are assigned to individual values; in language terms it is dynamically typed.
  • Unlike client–server database management systems, the SQLite engine has no standalone processes with which the application program communicates. Instead, the SQLite library is linked in and thus becomes an integral part of the application program, making it a popular choice among widespread browsers, operating systems, and mobile phones. The library can also be called dynamically.
    • SQLite is included by default in Windows 10, Google's verison of Android, FreeBSD, and Samsung's Tizen.
  • SQLite has bindings to many programming languages such as Python, Java, JavaScript, C++, C#, Objective-C, Ruby, Swift as well as a variety of other languages.
    • Unlike some other RBDMSs, SQLite only allows one concurrent user for writes, so it is better for small to medium size projects.
  • In SQLite, parameterized queries are used to improve performance and increase security by preventing injection attacks.
    • Instead of directly writing the values (SQL literals) into the statements, parameterized queries include placeholders.
    • The sqlite3 module supports two kinds of placeholders: question marks (qmark style) and named placeholders (named style).

Key Terms

ACID

Atomicity, consistency, isolation, durability, four properties expected from typical database interactions.

atomicity

A component property of ACID transactions; a database operation will never be executed partway or halfheartedly—either the whole operation gets performed or it's cancelled and nothing occurs.

attribute, column, property, field

A particular feature which describes and is characteristic of a record.

body

A set of n-tuples.

COMMIT

A COMMIT statement in SQL ends a transaction within a relational database management system (RDBMS) and makes all changes visible to other users.

connection

This read-only attribute provides the SQLite database Connection used by the Cursor object. A Cursor object created by calling con.cursor() will have a connection attribute that refers to con.

consistency

A component property of ACID transactions; data will always be held to standards established by the schema.

cursor

A database cursor is an object that enables traversal over the rows of a result set. It allows you to process individual row returned by a query.

database

An organized collection of data.

Database Management System / DBMS

There are multiple types of DBMS, (Relational, Object Oriented, etc), but each variant allows for reading, inserting, updating, deleting of a database along with general administrative/security tools.

DELETE

Removes one or more records from a table.

durability

A component property of ACID transactions; once a commit has been submitted and is reported, it's guaranteed to be reflected in the database—even in the case of an immediate system failure.

foreign key

A set of one or more columns in a table which provide a link or reference to the primary key in another table maintaining a relationship between the tables.

heading

A set of attributes.

INSERT

adds one or more records to a single table.

isolation

A component property of ACID transactions; concurrent requests for data should only be permitted once in-progress transactions working on that data have been completed in their entirety.

PRAGMA

In SQLite the PRAGMA command is a special command to be used to control various environmental variables and state flags within the SQLite environment.

primary key

A specific choice of one or more columns (attributes) whose data uniquely identifies each row (tuple) in a table (relation).

query

Retrieves/creates data based on specific criteria. Queries must follow SQL rules and syntax.

Relational model

Is an approach to managing data using a structure and language consistent with first-order predicate logic, first described in 1969 by English computer scientist Edgar F. Codd,[1][2] where all data is represented in terms of tuples, grouped into relations. A database organized in terms of the relational model is a relational database.

Retrieval

Providing information in a form directly usable or for further processing by other applications.

RDBMS

Relational database management system, a program or application that maintains a relational database.

record, row, tuple

An instance of an entity in a database table.

relation, table

A series of records gathered together under an entity type.

schema

A set of integrity constraints that structures and establishes the rules for data in a database.

SELECT

A common DQL command used to return a result set of records, either from a single or from multiple tables.

SQL

Structured Query Language, a programmatic standard for querying data from a relational database.

SQLite

A relational database management system (abbreviated RDBMS) embedded directly into a program.

transaction

A single transaction consists of one or more independent units of work, each reading and/or writing information to a database or other data store. When the system processes a COMMIT statement, the transaction ends with successful completion.

UPDATE

Changes specified data of one or more records in a table.

view

Is the set of rows from a database of a stored query on the data, which the database users can query just as they would in a persistent database collection object.

See Also

• Databases
• Internet Fundamentals/Databases
• Codecademy SQL Tutorial
• SQLite3 Python Documentation
• Sorting with Python
• Wiki: Relational Database
• SQL Fiddle
• SQLite Tutorial - Delete
• SQLite Cheat Sheet
• Pragma

References

Applied Programming