When writing and submitting your assignments follow these requirements:
Name your Eclipse project with your Network login, an underscore, 'a' (for
'assignment', then the assignment number: login_en.zip.
For example, if the user barn4520 submits Assignment 1, the
name should be: barn4520_a01. Give your .zip file the same
name when exporting your project. The project above would be exported in a
.zip file named barn4520_a01.zip. Use only Eclipse's built-in
archive capability to create these .zip files. No other format will be
accepted.
Some assignments consist of multiple Eclipse projects where one project
references another. Use the same naming scheme for all your projects.
Thus, a project named data_structures should be barn4520_data_structures.
Test your programs thoroughly - show us how it works. Copy the outputs
from your testing to a file in your Eclipse/Pydev project named testing.txt.
Make sure the tests are well labelled so that the markers know which
program the results represent.
There should be no errors or warnings in your code. Watch out particularly for bad or unused imports - these generate warnings and must be removed.
If you are asked to provide discussion put this discussion in your
Eclipse/Pydev project in a file named discussion.txt.
Put all your programs, testing, and discussion files into one Eclipse/Pydev project.
Zip the entire project using Eclipse and submit the resulting .zip file through MyLearningSpace.
Marks will be deducted from any questions where these requirements are not met.
Follow the assignment instructions to the letter (e.g. name the function
module functions.py), as this assignment is auto-graded. If
your Eclipse project is not defined correctly, the auto-grading fails, and
your assignment will be given a mark of 0.
Pages of use for this assignment:
These questions use the DCRIS database.
These functions return lists of tuples containing the results of the
various database calls. How you display these results is up to you.
Do not call print from within the functions. Do not
close the database from within the functions. It is the responsibility
of whatever opens the database to close the database.
We suggest you use Pythons triple quotes to make long SQL statements more readable:
sql = """SELECT ...
FROM ...
JOIN ...
ON ...
WHERE ...
ORDER BY ..."""
These functions use the information_schema to extract metadata
from the DCRIS database. All table references must use information_schema
as a prefix. For example, to query the TABLES table, you must
refer to it as information_schema.TABLES.
Further, we want only DCRIS information, so all queries must use only the
'dcris' TABLE_SCHEMA or CONSTRAINT_SCHEMA as
parameters to the functions.
Write and test the following function:
def get_table_info(cursor, tableSchema, tableName=None):
"""
-------------------------------------------------------
Queries information_schema.TABLES for metadata.
Use: rows = get_table_info(cursor, tableSchema)
Use: rows = get_table_info(cursor, tableSchema, tableName=v1)
-------------------------------------------------------
Parameters:
cursor - a database cursor (cursor)
tableSchema - the database table schema (str)
tableName - a table name (str)
Returns:
rows - (list of the TABLE_NAME, TABLE_TYPE, TABLE_ROWS,
and TABLE_COMMENT fields data)
if tableName is not None:
rows containing tableName
else:
all TABLES rows
Sorted by TABLE_NAME, TABLE_TYPE
-------------------------------------------------------
"""
Add this function to the PyDev module functions.py. Test it
from t01.py.
Examples:
| TABLE_NAME | TABLE_TYPE | TABLE_ROWS | TABLE_COMMENT |
|---|---|---|---|
| broad | BASE TABLE | 19 | Contains broad categories of security expertise. |
(Do not return the headers.)
Write and test the following function:
def get_column_info(cursor, tableSchema, tableName=None):
"""
-------------------------------------------------------
Queries information_schema.COLUMNS for metadata.
Use: rows = get_column_info(cursor, tableSchema)
Use: rows = get_column_info(cursor, tableSchema, tableName=v1)
-------------------------------------------------------
Parameters:
cursor - a database cursor (cursor)
tableSchema - the database table schema (str)
tableName - a table name (str)
Returns:
rows - (list of the TABLE_NAME, COLUMN_NAME, IS_NULLABLE,
and DATA_TYPE fields data)
if tableName is not None:
rows containing tableName
else:
all COLUMNS rows
Sorted by TABLE_NAME, COLUMN_NAME
-------------------------------------------------------
"""
Add this function to the PyDev module functions.py. Test it
from t02.py.
Examples:
| TABLE_NAME | COLUMN_NAME | IS_NULLABLE | DATA_TYPE |
|---|---|---|---|
| member | memberTitle | YES | varchar |
(Do not return the headers.)
Write and test the following function:
def get_constraint_info(cursor, tableSchema, constraintType=None):
"""
-------------------------------------------------------
Queries information_schema.TABLE_CONSTRAINTS for metadata.
Use: rows = get_constraint_info(cursor, tableSchema)
Use: rows = get_constraint_info(cursor, tableSchema, constraintType=v1)
-------------------------------------------------------
Parameters:
cursor - a database cursor (cursor)
tableSchema - the database table schema (str)
constraintType - a database constraint type (str)
Returns:
rows - (list of the CONSTRAINT_NAME, TABLE_NAME,
and CONSTRAINT_TYPE fields data)
if constraintType is not None:
rows containing constraintType
else:
all TABLE_CONSTRAINTS rows
Sorted by CONSTRAINT_NAME, TABLE_NAME
-------------------------------------------------------
"""
Add this function to the PyDev module functions.py. Test it
from t03.py.
Examples:
| CONSTRAINT_NAME | TABLE_NAME | CONSTRAINT_TYPE |
|---|---|---|
| fkMemberBroadBroad | memberBroad | FOREIGN KEY |
(Do not return the headers.)
Write and test the following function:
def get_foreign_key_info(cursor, constraintSchema, tableName=None, refTableName=None):
"""
-------------------------------------------------------
Queries information_schema.REFERENTIAL_CONSTRAINTS for metadata.
Use: rows = get_foreign_key_info(cursor, constraintSchema)
Use: rows = get_foreign_key_info(cursor, constraintSchema, tableName=v1)
Use: rows = get_foreign_key_info(cursor, constraintSchema, refTableName=v2)
Use: rows = get_foreign_key_info(cursor, constraintSchema, tableName=v1, refTableName=v2)
-------------------------------------------------------
Parameters:
cursor - a database cursor (cursor)
constraintSchema - the database constraint schema (str)
tableName - a table name (str)
refTableName - a table name (str)
Returns:
rows - (list of the CONSTRAINT_NAME, UPDATE_RULE, DELETE_RULE,
TABLE_NAME, and REFERENCED_TABLE_NAME fields data)
if tableName and/or refTableName are not None:
rows containing tableName and/or refTableName
else:
all REFERENTIAL_CONSTRAINTS rows
Sorted by CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
-------------------------------------------------------
"""
Add this function to the PyDev module functions.py. Test it
from t04.py.
Examples:
| CONSTRAINT_NAME | UPDATE_RULE | DELETE_RULE | TABLE_NAME | REFERENCED_TABLE_NAME |
|---|---|---|---|---|
| fkPubMember | CASCADE | CASCADE | pub | member |
(Do not return the headers.)
Write and test the following function:
def get_key_info(cursor, constraintSchema, tableName=None, refTableName=None):
"""
-------------------------------------------------------
Queries information_schema.KEY_COLUMN_USAGE for metadata.
Use: rows = get_key_info(cursor, constraintSchema)
Use: rows = get_key_info(cursor, constraintSchema, tableName=v1)
Use: rows = get_key_info(cursor, constraintSchema, refTableName=v2)
Use: rows = get_key_info(cursor, constraintSchema, tableName=v1, refTableName=v2)
-------------------------------------------------------
Parameters:
cursor - a database cursor (cursor)
constraintSchema - the database constraint schema (str)
tableName - a table name (str)
refTableName - a table name (str)
Returns:
rows - (list of the CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME,
REFERENCED_TABLE_NAME, and REFERENCED_COLUMN_NAME fields data)
if tableName and/or refTableName are not None:
rows containing tableName and/or refTableName
else:
all KEY_COLUMN_USAGE rows
Sorted by TABLE_NAME, COLUMN_NAME
-------------------------------------------------------
"""
Add this function to the PyDev module functions.py. Test it
from t05.py.
Examples:
| CONSTRAINT_NAME | TABLE_NAME | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
|---|---|---|---|---|
| fkPubType | pub | pubPubType | pubType | pubType |
(Do not return the headers.)