CP363: Assignment 04 - Fall 2024

Due 10:30 AM, Saturday, November 16, 2024

General Assignment Notes

When writing and submitting your assignments follow these requirements:

• When asked to write a SQL statement you may use any editor you like so long as it stores the statements as plain text. This means you should not use a word processing program such as Microsoft Word. Simple editors such as Notepad or TextEdit, or a Eclipse code editor are fine.

• There should be no errors or warnings in your code. SQL code that doesn't run doesn't get graded.

• Put all your programs, testing, and discussion files into one zip file.

• Use the Validate Assignment link to make sure that your .zip file is named correctly have have the proper contents. Improper assignment submissions are given a grade of zero.

WARNING

Follow the assignment instructions to the letter (e.g. name the various files t01.txt, t02.txt, etc.), as this assignment is auto-graded. If your SQL code files are not named correctly the auto-grading fails, and your assignment will be given a mark of 0.

These tasks use the DCRIS database. You may test your SQL code either through your own DBMS connection, or with the SQL Practice page.

These tasks are written for the most part in English and must be translated into SQL. You may have to look at the DCRIS data to determine which ID numbers to use, if appropriate, and which columns to sort on. Requests to have rows "sorted by x then y", means to write the ORDER BY as ORDER BY X, Y.

Assignment 1 dealt with single tables and views, Assignment 2 dealt with joined tables and aggregation, and Assignment 3 dealt with subqueries. This assignment deals with metadata.

Requirements

• Put nothing but SQL code into each task file. Do not use EXPLAIN.
• Use single quotes ('...') around text. Do not use double quote ("..." at all.
• Do not use semicolons (";") at all.
• Do not use UNION, INTERSECT, or EXCEPT.
• When asked for a particular projection (i.e. set of columns), ask for the columns in the order given in the task description.
• When not asked for a particular projection, select all (*) columns.
• Do not use any views. To select data from multiple tables you must use JOINs.
• Use only the JOIN … ON syntax, not alternatives such as USING.

The Tasks

Some explanations:

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 the 'dcris' TABLE_SCHEMA or CONSTRAINT_SCHEMA selections, i.e.:
… WHERE … TABLE_SCHEMA = 'dcris' …
or
… WHERE … CONSTRAINT_SCHEMA = 'dcris' …

Write a single SQL statement that:

1. selects the TABLE_NAME, TABLE_TYPE, TABLE_ROWS, and TABLE_COMMENT attributes from the TABLES table. Sort the results by the table name. Save the statement as .

   This is an example of a subset of the results:

   TABLE_NAME	TABLE_TYPE	TABLE_ROWS	TABLE_COMMENT
   broad	BASE TABLE	19	Contains broad categories of security expertise.

   ---

2. selects the TABLE_NAME, TABLE_TYPE, TABLE_ROWS, and TABLE_COMMENT attributes from the TABLES table for tables with 100 or more rows. Sort the results by the table name. Save the statement as .

   ---

3. selects the TABLE_NAME, IS_NULLABLE, COLUMN_NAME, and DATA_TYPE attributes from the COLUMNS table. Sort the results by the table name and then column name. Save the statement as .

   ---

4. selects the TABLE_NAME, COLUMN_NAME, and DATA_TYPE attributes from the COLUMNS table for nullable columns. Sort the results by the table name and then column name. Save the statement as .

   ---

5. selects the CONSTRAINT_NAME, TABLE_NAME, and CONSTRAINT_TYPE attributes from the TABLE_CONSTRAINTS table. Sort the results by the constraint name and then table name. Save the statement as .

   ---

6. selects the CONSTRAINT_NAME and TABLE_NAME attributes from the TABLE_CONSTRAINTS table for UNIQUE constraints. Sort the results by the constraint name and then table name. Save the statement as .

   ---

7. selects the CONSTRAINT_NAME, UPDATE_RULE, DELETE_RULE, TABLE_NAME, and REFERENCED_TABLE_NAME attributes from the TABLE_CONSTRAINTS REFERENTIAL_CONSTRAINTS table. Sort the results by the constraint name, then table name, then referenced table name. Save the statement as .

   ---

8. selects the CONSTRAINT_NAME, UPDATE_RULE, TABLE_NAME, and REFERENCED_TABLE_NAME attributes from the TABLE_CONSTRAINTS REFERENTIAL_CONSTRAINTS table for constraints whose delete rule is not CASCADE. Sort the results by the constraint name, then table name, then referenced table name. Save the statement as .

   ---

9. selects the CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, and REFERENCED_COLUMN_NAME attributes from the KEY_COLUMN_USAGE table. Sort the results by the table name and then column name. Save the statement as .

   ---

10. selects the TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, and REFERENCED_COLUMN_NAME attributes from the KEY_COLUMN_USAGE table for primary keys only. Sort the results by the table name and then column name. Save the statement as .

    ---