header for databases

Objective category: Databases

Data.Databases (DAT.DB.*)

A tree of computer capabilities might initially branch into these three broad categories:

  1. number crunching
  2. data storage
  3. 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.

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

Connected course content

Descriptive statistics is introduced in DAT-102 with this whole-group, multi-step project of exploring frequency distributions.

arrow_upward back


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.

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 module

This 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

arrow_upward back


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.