Objective category: Databases
Data.Databases (DAT.DB.*)
A tree of computer capabilities might initially branch into these three broad categories:
- number crunching
- data storage
- data exchange
Databases are software systems which provide permanent (also called non-volatile) storage of binary information. Oracle and Microsoft are two major for-profit corporations who have driven the development of database technology over the recent decades. PostgreSQL and MySQL are two open-source alternatives to proprietary database technologies, and will be the technical focus of the modules linked in this document.
Contents
iQuick-links to content modules
iObjective group A: Design fundamentals
iObjective group A: SQL langauge fundamentals
iObjective group C: Software
Database-related content modules
For convenience, links to essential database related content modules are provided in this section. Each one is also linked as apprpriate in sections by objective category.
resources |
Desktop (i.e. single user) DatabasesDatabase design & SQL languageEnterprise-level databases: PostgreSQL |
Objective group A: DB.DESIGN.*
| Objective group | Objective ID | Objective text |
|---|---|---|
| DESIGN: Relational database table design and keying | DB.DESIGN.1 | Correctly insert data into all the tables of a given existing database schema containing at least 3 tables linked with sevearal foreign keys and contain columns of all major data types. |
| DB.DESIGN.2 | Map data storage use cases to optimal technology group with particular emphasis on use cases for a relational database approach among alternatives such as a spreadsheet, a file structure, or a NOSQL (non-relational) database. Defend these mappings using the concepts of normality, latency, and overhead. | |
| DB.DESIGN.3 | Solve the "storage half" of a data management problem by engineering a 4-table relational database schema using the clip-board method: create metadata tables for each table with thoughtful attention to data types and nullability; designate primary and foreign keys; populate data in each table | |
| DB.DESIGN.4 | Solve the "retrieval half" of a data management problem by constructing at least 3 SELECT statements which build accurate result sets from multiple joined tables and meet pre-declared data reporting specifications |
clipboard database project! |
Connected course content
Descriptive statistics is introduced in DAT-102 with this whole-group, multi-step project of exploring frequency distributions.
Objective group B: DB.SQL.*
| Objective group | Objective ID | Objective text |
|---|---|---|
| SQL: The language of databases | DB.SQL.1 | Differentiate between data definition and data manipulation SQL by classifying sample statements and paraphrasing their purpose in human English. Categorize data manipulation statements into their sub-families of SELECT, INSERT, UPDATE, and DELETE statements. |
| DB.SQL.2 | Generate data definition SQL to create the tables designed on the clipboard prototyping tools, specifically use CREATE TABLE commands that include constraint names for primary and foreign keys. | |
| DB.SQL.3 | Write INSERT statements to populate tables created from the clipboard design project which correctly assign type-aligned values to each column in each table, including fields with boolean and date types. | |
| DB.SQL.4 | Manipulate stored data with UPDATE and DELETE statements. Explain the risks associated with willy-nilly SQL scripting and list precautions to minimize risks of data loss such as by always running DML statements inside a transaction block of BEGIN; and COMMIT. | |
| DB.SQL.5 | Design and test SELECT statements to retrieve data from multiple joined tables. Implement: column aliases, WHERE conditions, and row ordering. | |
| DB.SQL.6 | Differentiate uses cases for INNER JOINs versus OUTER JOINS and create SELECT statements which demonstrate the differences between each type and their common use cases. |
resources |
Objective group C: DB.SOFT.*
| Objective group | Objective ID | Objective text |
|---|---|---|
| SOFTWARE: Exploring the database software ecosystem | DB.SOFT.1 | Classify leading database applications (Oracle, SQl Server, PostgreSQL, MySQL, MS Access, LibreOffice Base) by their intended use cases, proprietary status, cost range, and historical development; discuss the pros and cons of an open source software model generally and as applied to database applications. |
| DB.SOFT.2 | Navigate the graphic user interface in Libre Office Base to create a digital version of the clipboard database project; create new tables using the wizard and design view and designate keys; implement keyed relationships between tables; insert data directly into tables; craft queries using the command line and the GUI. | |
| DB.SOFT.3 | Install and configure PostgreSQL server on a computer, create a new database in the public schema, and interact with the system via the psql command line utility to create new tables, insert data, and execute queries. |
resources |
Comprehensive LibreOffice Base moduleThis content module was created for CIT-100 students needing a crash course in creating databases on a desktop app. Work along with Eric to create a database which stores and displays data about your most beloved music. PostgreSQL links |
Page created in 2019 by Eric Xander Darsow. Original content can be freely reproduced without any permission or attribution according to the site's content use agreement. Any content accessed by links to external sites or content with specific rights notices is governed by its respective use agreements.