5DV119 DB Code, Spring 2015

Introduction to Database Management
Spring 2015
Database Programs

Database Programs to Generate the "Company" Schema and Database of the Textbook:

Generate in the Leap database system: Leap
A no-frills SQL script which works with both PostgreSQL and MySQL: SQL
Generate in any ODBC-compliant system using C: C/ODBC

Table data include (.h) file for the above program: H/ODBC
Tuple data include (.h) file for the above program: H/ODBC

Generate using Python and PyODBC: .py

Table data include file for the above program: .py
Tuple data include file for the above program: .py

Database Programs to Generate the Banking Schema and Database of the Beaulieu book:

Link to a directory containing the script of Alan Beaulieu (MySQL only): HTML
A no-frills SQL script which works with both PostgreSQL and MySQL: SQL

Database Programs to Generate the Airline Schema and Database for the Obligatory Exercises and Slides:

Code to generate the tables of the Airline schema: SQL
Code to generate the tuples for the Airline schema (iso8859-1, for the cs.umu.se systems): SQL
Code to generate the tuples for the Airline schema (utf8, may be useful on other systems): SQL
Code to generate both the tables and the tuples in the Leap database system: Leap

SQL from the Course Slides:

SQL -- Part 1: SQL
SQL -- Part 2: SQL
For the Grading Database Example: SQL
Security and Authorization SQL

Example C programs with ODBC calls:

Connect to an ODBC database: C
Connect to an ODBC database with password: C
Connect to an ODBC database using single command-line agrument (for ident authentication): C
Connect to an ODBC database using command-line agruments for all arguments: C
Execute a simple SQL query: C
Repeatedly execute an SQL query with interactive database name: C
Test for a null value in a retrieved tuple: C

Example Python programs with ODBC calls using PyODBC:

Connect to an ODBC database: .py
Connect to an ODBC database using ident authentication: .py
Connect to an ODBC database with ident authentication plus user name and password: .py
Connect to an ODBC database with ident authentication plus user name and password using command-line arguments: .py
Connect program in which the connect mode may be set parametrically. .py

Connect module for the above program and all programs below .py
Cursor module for the above program and all programs below .py

Execute a simple SQL query: .py
Repeatedly execute an SQL query with interactive input parameters: .py
Test for a null value in a retrieved tuple: .py
Read dates in various formats an SQL query: .py
Execute SQL updates via ODBC and illustration of the need for commit: .py
Execute SQL updates with autocommit via ODBC: .py
Execute SQL insert and detect constraint violation: .py
Write to standard error: .py
Illustrate SQL injection: .py