Databases Class Seven: January 16, 2014 

What is a Database? A database is an organized collection of data. It models relevant aspects of reality in a way that supports processes requiring this information. Alternatively, a database is a collection of tables, with related data. Physically, database servers are dedicated computers that hold the actual databases and run only the DBMS and related software. 

What is Data? Data are known facts that can be recorded and have meaning in the user's environment Alternatively, data are values of qualitative or quantitative variables, belonging to a set of items. Sets are important. 

What is a Database Management System? A database management system (DBMS) is a software system designed to allow the definition, creation, querying, update, and administration of databases. Examples: MySQL, PostgreSQL, SQLite, Microsoft SQL Server, MariaDB Because they are so closely related, the term "database" when used casually often refers to both a DBMS and the data it manipulates. 

Why use them? Databases are set up so that one set of software programs provides all users with access to all the data. Databases can inter-operate by using standards such as SQL, ODBC (open database connectivity), or JDBC (Java database connectivity). 

Abridged History and Practice SQL/Relational vs. Post-Relational (NoSQL) We'll be mainly talking today about the relational model. Why? Because it’s a good place to start. At least in my opinion. We will mention NoSQL and how relational databases interact with object oriented programming. 

Relational DBMS Functions Data Definition Defining new data structures for a database, removing data structures from the database, modifying the structure of existing data. Update Inserting, modifying, and deleting data. Retrieval Obtaining information either for end-user queries and reports or for processing by applications. Administration Registering and monitoring users, enforcing data security, monitoring performance, maintaining data integrity, dealing with concurrency control, and recovering information if the system fails. 

RDBMS Terminology Table A table is a matrix with data. A table in a database looks like a simple spreadsheet. Column One column (data element) contains data of one and the same kind. Row A row (= tuple, entry or record) is a group of related data. 

RDBMS Terminology Primary Key A primary key is unique. A key value can not occur twice in one table. With a key you can find at most one row. Foreign Key A foreign key is the linking pin between two tables. Compound Key A compound key (composite key) is a key that consists of multiple columns, because one column is not sufficiently unique. 

DBMS Terminology Index An index in a database resembles an index at the back of a book. Referential Integrity Referential Integrity makes sure that a foreign key value always points to an existing row. 

Simplified Relational Database Structure Databases use a table format. The table is made up of rows and columns. Each piece of information is entered into a row, which then creates a record (tuple). 

Structured Query Language (SQL) Powerful ad hoc query language. SQL is a special-purpose programming language designed for managing data held in a RDBMS. The scope of SQL includes data insert, query, update and delete, schema creation and modification, and data access control. Although SQL is often described as, and to a great extent is, a declarative language (4GL), it also includes procedural elements. 

Structured Query Language (SQL) SQL is designed for a specific purpose: to query data contained in a relational database. SQL is a set-based, declarative query language, not an imperative language like C or BASIC. However, there are extensions to Standard SQL which add procedural programming language functionality, such as control-of-flow constructs. 

SQL CRUD CREATE Insert data into tables RETRIEVE Select data out of tables UPDATE Modify data in a table DELETE Remove data from a table 

SQL Standard SQL was adopted as a standard by the American National Standards Institute (ANSI) in 1986 as SQL-86[33] and the International Organization for Standardization (ISO) in 1987. Nowadays the standard is subject to continuous improvement by the Joint Technical Committee ISO/IEC JTC 1, Information technology, Subcommittee SC 32, Data management and interchange which affiliate to ISO as well as IEC. It is commonly denoted by the pattern: ISO/IEC 9075-n:yyyy Part n: title, or, as a shortcut, ISO/IEC 9075. 

Database “Languages” (SQL) Data definition language (DDL) Defines objects, data types, and the relationships among them Data Manipulation Language (DML) Performs tasks such as inserting, updating, or deleting data occurrences Query Language Allows searching for information and computing derived information 

SQL, DML and DDL The Data Manipulation Language (DML) is the subset of SQL used to add, update and delete data. Some basic items of DML are the SELECT, UPDATE, and DELETE statements. The Data Definition Language (DDL) manages table and index structure. The most basic items of DDL are the CREATE, ALTER, RENAME, DROP and TRUNCATE statements. 

Data Types Data can be stored in a variety of data types. Here are some: Character strings CHARACTER(n) or CHAR(n): fixed-width n-character string, padded with spaces as needed CHARACTER VARYING(n) or VARCHAR(n): variable-width string with a maximum size of n characters NATIONAL CHARACTER(n) or NCHAR(n): fixed width string supporting an international character set NATIONAL CHARACTER VARYING(n) or NVARCHAR(n): variable-width NCHAR string 

Data Types Numbers INTEGER and SMALLINT FLOAT, REAL and DOUBLE PRECISION NUMERIC(precision, scale) or DECIMAL(precision, scale) Bits A two or three way switch Blobs Possibly multimedia, for instance 

Data Types Date and Time DATE: for date values (e.g. 2011-05-03) TIME: for time values (e.g. 15:51:36). The granularity of the time value is usually a tick (100 nanoseconds). TIME WITH TIME ZONE or TIMETZ: the same as TIME, but including details about the time zone in question. TIMESTAMP: This is a DATE and a TIME put together in one variable (e.g. 2011-05-03 15:51:36). TIMESTAMP WITH TIME ZONE or TIMESTAMPTZ: the same as TIMESTAMP, but including details about the time zone in question. 

NoSQL A NoSQL database provides a mechanism for storage and retrieval of data that uses looser consistency models than traditional relational databases. Motivations for this approach include simplicity of design, horizontal scaling and finer control over availability. NoSQL databases are often highly optimized key–value stores intended for simple retrieval and appending operations, with the goal being significant performance benefits in terms of latency and throughput. 

Object Relational Mapping (ORM) A programming technique for converting data between incompatible type systems in object-oriented programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language. The heart of the problem is translating the logical representation of the objects into an atomized form that is capable of being stored on the database, while somehow preserving the properties of the objects and their relationships so that they can be reloaded as an object when needed.