IT Fundamentals/Database Use

From Wikiversity
Jump to navigation Jump to search
Database use
MySQL
MariaDB
MongoDB
Oracle Database
PostgreSQL

This lesson introduces database use.

Objectives and Skills[edit]

Objectives and skills for the database use portion of IT Fundamentals certification include:[1]

  • Summarize methods used to interface with databases.
    • Relational methods
      • Data manipulation
        • Select
        • Insert
        • Delete
        • Update
      • Data definition
        • Create
        • Alter
        • Drop
        • Permissions
    • Database access methods
      • Direct/manual access
      • Programmatic access
      • User interface/utility access
      • Query/report builders
    • Export/import
      • Database dump
      • Backup

Readings[edit]

  1. Wikipedia: SQL

Multimedia[edit]

  1. YouTube: Interfacing with Databases
  2. YouTube: Querying a Database
  3. YouTube: Table Design

Activities[edit]

  1. Complete one or more of the following tutorials:
  2. Practice writing single-table SELECT statements using W3Schools SQL TryIt Editor.
    • Select all customers (SELECT *).
    • Select only customer id and customer name fields (SELECT fields).
    • Select customer name, address, city, and postal code for all customers from the United Kingdom (WHERE field = 'value').
    • Select contact name and customer name for all customers, sorted alphabetically by contact name (ORDER BY field).
    • Count the total number of customers (COUNT(*)).
    • Count the number of customers from each country (GROUP BY).
    • 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).
    • 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).
  3. Practice writing multiple-table SELECT statements using W3Schools SQL TryIt Editor.
    • Select customer name, order ID, and order date for all customers (INNER JOIN).
    • Select customer name, order number, and order date for all customers, renaming the OrderID field as Order Number (INNER JOIN, AS).
    • Select customer name, order number, and order date for all customers, sorted by customer name and order number (INNER JOIN, AS, ORDER BY).
    • Select order number, order date, product name, and quantity ordered for all customers (INNER JOIN, AS).
    • Select order number, order date, product name, quantity ordered, and extended price (quantity * price) for all customers (INNER JOIN, AS, calculated field).
    • Select order number, order date, product name, quantity ordered, and extended price for customer 2 (INNER JOIN, AS, calculated field, WHERE).
    • Select order number, order date, product name, quantity ordered, and extended price for customer 'Around the Horn' (INNER JOIN, AS, calculated field, WHERE).
  4. Practice writing INSERT, UPDATE, and DELETE statements using W3Schools SQL TryIt Editor.
    • Add a new shipper with ID 4, name 'On Time Delivery', and phone '(503) 555 0123' (INSERT).
    • Increase prices on all products by 1 (UPDATE).
    • Reduce prices on all products by 1 (UPDATE).
    • Change the new shipper's name from 'On Time Delivery' to 'On-Time Delivery' (UPDATE, WHERE).
    • Delete the new shipper (DELETE, WHERE).
  5. Research SQL statements.
    • Research CREATE, ALTER, and DROP. Note how they differ from INSERT, UPDATE, and DELETE.
    • Research GRANT and REVOKE. Note how they may be applied broadly to tables and queries (views) or specifically to individual fields in a table or query (view).

Lesson Summary[edit]

Relational Databases[edit]

  • A relational database is a digital database where all data is represented in terms of tuples (rows or records), grouped into relations (tables).[2]
  • Each row in a table has its own unique key.[3]
  • A primary key is a specific choice of a minimal set of attributes (columns) that uniquely specify a tuple (row) in a relation (table).[4]
  • A foreign key is a set of attributes (columns) whose values exist in another relation (table), and uniquely identify a tuple (row) in the other table[5]
  • Constraints make it possible to restrict the domain (possible values) of an attribute (column). Constraint options include:[6]
    • Primary key
    • Foreign key
    • Stored procedure (data validation)
    • Index
  • Most relational databases use the SQL data definition and query language.[7]

Data Definition Language[edit]

  • Data Definition Language (DDL) is a syntax similar to a computer programming language for defining data structures, especially database schemas.[8]
  • A database schema is its structure described in a formal language supported by the database management system (DBMS).[9]
  • DDL statements create and modify database objects such as tables, indexes, and users.[10]
  • Common DDL statements are CREATE, ALTER, and DROP.[11]
  • The CREATE command is used to establish a new database, table, index, or stored procedure.[12]
  • The ALTER statement modifies an existing database object.[13]
  • The DROP statement destroys an existing database, table, index, or view.[14]

Data Query Language[edit]

  • Data Query Language (DQL) is used for performing queries on the data within schema objects.[15]
  • The SELECT statement returns a result set of records from one or more tables.[16]
  • The SELECT statement is often considered part of Data Manipulation Language (DML) rather than a separate subset.[17]
  • The basic SELECT statement syntax is SELECT <column> [AS <alias>, ...] FROM <table> [AS <alias>, ...].[18][19]
  • An asterisk ("*") can be used to specify that the query should return all columns of the queried tables.[20]
  • A WHERE clause in SQL specifies that a SQL Data Manipulation Language (DML) statement should only affect rows that meet specified criteria.[21]
  • The basic WHERE clause syntax is <SQL-DML-Statement> FROM <table> WHERE <predicate>.[22]
  • By default, relational database systems may return data rows in any order, or more specifically, without any order.[23]
  • 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.[24]
  • The basic ORDER BY clause syntax is SELECT <column(s)> FROM <table(s)> ORDER BY <column> [DESC] [NULLS FIRST], ....[25]
  • The DESC keyword will sort a given column in descending order.[26]
  • A JOIN clause combines columns from one or more tables in a relational database.[27]

Data Manipulation Language[edit]

  • Data Manipulation Language (DML) is a computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database.[28]
  • The INSERT statement adds one or more records to any single table in a relational database.[29]
  • The basic INSERT syntax is INSERT INTO <table>(<column(s)>) VALUES(<value(s)>)[30]
  • The 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.[31]
  • The basic UPDATE syntax is UPDATE <table> SET <column> = <value>, ... [WHERE <condition>][32]
  • The DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.[33]
  • The basic DELETE syntax is DELETE FROM <table> [WHERE <condition>][34]

Data Control Language[edit]

  • Data Control Language (DCL) is a syntax similar to a computer programming language used to control access (authorization) to data stored in a database.[35]
  • GRANT allows specified users to perform specified tasks.[36]
  • REVOKE removes user accessibility to a database object.[37]
  • The operations for which privileges may be granted to or revoked from a user or role apply to both Data Definition Language (DDL) and Data Manipulation Language (DML).[38]

Database Access Methods[edit]

  • The functionality provided by a DBMS can vary greatly, and often include:[39]
    • Data storage, retrieval and update
    • User accessible catalog or data dictionary describing the metadata
    • Support for transactions and concurrency
    • Facilities for recovering the database should it become damaged
    • Support for authorization of access and update of data
    • Access support from remote locations
    • Enforcing constraints to ensure data in the database abides by certain rules
  • It is generally expected the DBMS will provide a set of command line or graphical utilities for direct access to the database for administration.[40]
  • A programmer will code interactions to the database (sometimes referred to as a datasource) via an application program interface (API) or via a database language.[41]
  • External interaction with the database will be via an application program that can range from a database tool that allows users to execute SQL queries textually or graphically to a web site that uses a database to store and search information.[42]
  • Query and reporting applications often support different user interface options such as:[43]
    • Tables to hold or access stored data
    • Queries to select and modify table data for forms and reports
    • Forms to display and modify data on screen
    • Reports to display data on paper or in digital documents.

Export/Import[edit]

  • A comma-separated values (CSV) file is a delimited text file that uses a comma to separate values. Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator is the source of the name for this file format.[44]
  • A CSV file typically stores tabular data (numbers and text) in plain text, in which case each line will have the same number of fields. CSV files may be used to export data from and import data to a database.[45]
  • A database backup operation is performed occasionally or continuously in case it becomes necessary to restore a database back to a previous state.[46]
  • A database dump contains a record of the table structure and/or the data from a database and is usually in the form of a list of SQL statements. A database dump is most often used for backing up a database so that its contents can be restored in the event of data loss.[47]

Key Terms[edit]

CRUD (Create, Read, Update, Delete)
The four basic functions of persistent storage.[48]
DDL (Data Definition Language)
A syntax similar to a computer programming language for defining data structures, such as tables and indexes.[49]
DML (Data Manipulation Language)
A computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database.[50]
RDBMS (Relational Database Management System)
A software system used to maintain relational databases.[51]
SQL (Structured Query Language)
A domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS).[52]

Assessments[edit]

See Also[edit]

References[edit]

  1. CompTIA: IT Fundamentals (ITF+) Exam Objectives FC0-U61
  2. Wikipedia: Relational model
  3. Wikipedia: Relational database
  4. Wikipedia: Primary key
  5. Wikipedia: Foreign key
  6. Wikipedia: Relational database
  7. Wikipedia: Relational model
  8. Wikipedia: Data definition language
  9. Wikipedia: Database schema
  10. Wikipedia: Data definition language
  11. Wikipedia: Data definition language
  12. Wikipedia: Data definition language
  13. Wikipedia: Data definition language
  14. Wikipedia: Data definition language
  15. Wikipedia: Data query language
  16. Wikipedia: Select (SQL)
  17. Wikipedia: Data query language
  18. Wikipedia: SQL
  19. Wikipedia: Select (SQL)
  20. Wikipedia: SQL
  21. Wikipedia: Where (SQL)
  22. Wikipedia: Where (SQL)
  23. Wikipedia: Order by
  24. Wikipedia: Order by
  25. Wikipedia: Order by
  26. Wikipedia: Order by
  27. Wikipedia: Join (SQL)
  28. Wikipedia: Data manipulation language
  29. Wikipedia: Insert (SQL)
  30. Wikipedia: Insert (SQL)
  31. Wikipedia: Update (SQL)
  32. Wikipedia: Update (SQL)
  33. Wikipedia: Delete (SQL)
  34. Wikipedia: Delete (SQL)
  35. Wikipedia: Data control language
  36. Wikipedia: Data control language
  37. Wikipedia: Data control language
  38. Wikipedia: Data control language
  39. Wikipedia: Database
  40. Wikipedia: Database
  41. Wikipedia: Database
  42. Wikipedia: Database
  43. Go Free: LibreOffice Base Tutorial
  44. Wikipedia: Comma-separated files
  45. Wikipedia: Comma-separated files
  46. Wikipedia: Database
  47. Wikipedia: Database dump
  48. Wikipedia: Create, read, update and delete
  49. Wikipedia: Data definition language
  50. Wikipedia: Data manipulation language
  51. Wikipedia: Relational database
  52. Wikipedia: SQL