Internet Fundamentals/Databases



This lesson introduces database concepts.

Objectives and Skills
Objectives and skills for this lesson include:
 * Recognize essential database concepts.

Readings

 * 1)  Database
 * 2)  Relational database
 * 3)  SQL

Multimedia

 * 1) YouTube: Relational Database Concepts
 * 2) YouTube: Intro to SQL
 * 3) YouTube: SQL JOIN Statement
 * 4) YouTube: SQL GROUP BY, HAVING & ORDER BY
 * 5) YouTube: SQL INSERT, UPDATE, and DELETE

Activities

 * 1) Complete one or more of the following tutorials:
 * 2) * W3Schools: SQL Tutorial
 * 3) * SQLZOO: SQL Tutorial
 * 4) * SQLBolt: Learn SQL
 * 5) * TutorialsPoint: SQL
 * 6) * Khan Academy: Intro to SQL
 * 7) Practice writing single-table SELECT statements using W3Schools SQL TryIt Editor.
 * 8) * Select all customers (SELECT *).
 * 9) * Select only customer id and customer name fields (SELECT fields).
 * 10) * Select customer name, address, city, and postal code for all customers from the United Kingdom (WHERE field = 'value').
 * 11) * Select contact name and customer name for all customers, sorted alphabetically by contact name (ORDER BY field).
 * 12) * Count the total number of customers (COUNT(*)).
 * 13) * Count the number of customers from each country (GROUP BY).
 * 14) * Count the number of customers from each country and sort the list in descending order by count and ascending order by country in case of a tie (GROUP BY, ORDER BY).
 * 15) * Count the number of customers from each country and sort the list in descending order by count and ascending order by country in case of a tie, listing only those countries with more than 10 customers (GROUP BY, HAVING, ORDER BY).
 * 16) Practice writing multiple-table SELECT statements using W3Schools SQL TryIt Editor.
 * 17) * Select customer name, order ID, and order date for all customers (INNER JOIN).
 * 18) * Select customer name, order number, and order date for all customers, renaming the OrderID field as Order Number (INNER JOIN, AS).
 * 19) * Select customer name, order number, and order date for all customers, sorted by customer name and order number (INNER JOIN, AS, ORDER BY).
 * 20) * Select order number, order date, product name, and quantity ordered for all customers (INNER JOIN, AS).
 * 21) * Select order number, order date, product name, quantity ordered, and extended price (quantity * price) for all customers (INNER JOIN, AS, calculated field).
 * 22) * Select order number, order date, product name, quantity ordered, and extended price for customer 2 (INNER JOIN, AS, calculated field, WHERE).
 * 23) * Select order number, order date, product name, quantity ordered, and extended price for customer 'Around the Horn' (INNER JOIN, AS, calculated field, WHERE).
 * 24) Practice writing INSERT, UPDATE, and DELETE statements using W3Schools SQL TryIt Editor.
 * 25) * Add a new shipper with ID 4, name 'On Time Delivery', and phone '(503) 555 0123' (INSERT).
 * 26) * Increase prices on all products by 1 (UPDATE).
 * 27) * Reduce prices on all products by 1 (UPDATE).
 * 28) * Change the new shipper's name from 'On Time Delivery' to 'On-Time Delivery' (UPDATE, WHERE).
 * 29) * Delete the new shipper (DELETE, WHERE).

Lesson Summary

 * A database is an organized collection of data.
 * A relational database is a collection of schemas, tables, queries, reports, views, and other elements.
 * A database-management system (DBMS) is a computer-software application that interacts with end-users, other applications, and the database itself to capture and analyze data.
 * A general-purpose DBMS allows the definition, creation, querying, update, and administration of databases.
 * Well-known DBMSs include MySQL, PostgreSQL, EnterpriseDB, MongoDB, MariaDB, Microsoft SQL Server, Oracle Database, Sybase, SAP HANA, MemSQL, SQLite and IBM DB2.
 * Virtually all relational database systems use SQL (Structured Query Language) as the language for querying and maintaining the database.
 * SQL (Structured Query Language) is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS).
 * Data Manipulation Language (DML) commands include SELECT, INSERT, UPDATE, and DELETE.
 * The SQL SELECT statement returns a result set of records from one or more tables.
 * An asterisk ("*") can be used to specify that the query should return all columns of the queried tables.
 * SQL statements are terminated with a semicolon (";").
 * A WHERE clause in SQL specifies that a SQL Data Manipulation Language (DML) statement should only affect rows that meet specified criteria.
 * By default, relational database systems may return data rows in any order, or more specifically, without any order.
 * An ORDER BY clause in SQL specifies that a SQL SELECT statement returns a result set with the rows being sorted by the values of one or more columns.
 * A SQL JOIN clause combines columns from one or more tables in a relational database.
 * An SQL INSERT statement adds one or more records to any single table in a relational database.
 * An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.
 * An SQL DELETE statement removes one or more records from a table. Either all records are removed, or a subset may be chosen using a condition.

Key Terms

 * AJAX (Asynchronous JavaScript And XML)
 * A set of Web development techniques using multiple Web technologies on the client side to create asynchronous Web applications that can send and retrieve data from a server asynchronously (in the background) without interfering with the display and behavior of the existing page.


 * data modeling
 * A process used to define and analyze data requirements needed to support the business processes within the scope of corresponding information systems in organizations.


 * database
 * An organized collection of data.


 * database management system (DBMS)
 * A computer-software application that interacts with end-users, other applications, and the database itself to capture and analyze data.


 * field
 * A set of data values of a particular simple type, one for each row of the table.


 * foreign key
 * A field (or collection of fields) in one table that uniquely identifies a row of another table or the same table.


 * index
 * A data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.


 * many-to-many relationship
 * A type of cardinality that refers to the relationship between two entities A and B in which A may contain a parent instance for which there are many children in B and vice versa.


 * one-to-many relationship
 * A type of cardinality that refers to the relationship between two entities (see also entity–relationship model) A and B in which an element of A may be linked to many elements of B, but a member of B is linked to only one element of A.


 * Open Database Connectivity (ODBC)
 * A standard application programming interface (API) for accessing database management systems (DBMS).


 * primary key
 * A field (or collection of fields) in one table that uniquely identifies a row in the table.


 * query
 * A precise request for information retrieval with database and information systems.


 * record
 * A single, implicitly structured data item in a table.


 * relational database
 * A collection of schemas, tables, queries, reports, views, and other elements.


 * Structured Query Language (SQL)
 * A domain-specific language used in programming and designed for managing data held in a relational database management system


 * table
 * A collection of related data held in a structured format within a database, consisting of columns and rows.

Assessments

 * Flashcards: Quizlet: Information Systems - Databases
 * Quiz: Quizlet: Information Systems - Databases