SQL Coding Standards

Any place that you work in the technology industry typically has standards for how code is written, which you are expected to follow.  This course is no different.

We will be requiring (and enforcing) the following standards on homework assignments (HWs), practice exercises (PEs), zyLabs, and examinations.  In all cases, these are not part of the total points, but rather result in additional deductions (1 point for any occurrence per each item, with up to 7 points deducted if none are followed).

Relational Notation Standards:

• Relation (entity) names must be in UPPERCASE; multi-word relations are separated by an underscore.
  Examples:
    STUDENT(…)
  STUDENT_ADVISOR(…)
  
  
• Relation names are singular.
  Example:
    STUDENT(…), not STUDENTS(…)
  
  
• Primary keys are underlined.
  Example:
    STUDENT(univ_id, …)
  
  
• Foreign key are italicized when typed, dash-underlined when written
  Example:
    STUDENT(univ_id, college_id, …)
  
  
• Attribute names that would otherwise include a space as a separator must include an underscore instead.
  Example:
    STUDENT(univ_id, college_id, first_name, last_name, expected_grad_year, …)

Implementation (Coding) Standards:

• One clause per line (SELECT, FROM, WHERE, etc.)
  Example:

SELECT univ_id, first_name, last_name    
FROM student
WHERE city = 'Rochester' AND state = 'NY';

• Keywords and data types must be in UPPERCASE
  Example:

CREATE TABLE test (
    test_id CHAR(9),
    test_count INT,
    test_desc VARCHAR(255),
    CONSTRAINT test_pk PRIMARY KEY(test_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• Table names and attribute names that would otherwise include a space as a separator must include an underscore instead. Don't use camel case to separate words, since capital letters in identifiers cause problems with Postgres, SQLite, and Oracle (even though they're fine in MySQL/MariaDB). E.g., in Postgres, if an identifier contains a capital letter, any time that identifier is referenced, it must be written in double quotes. In Oracle, even if identifiers are created with camel case, the database reports them as all caps.
  Example:

SELECT univ_id, college_id, first_name, last_name

• Single quotes for a string literal. Double quotes do not work for string literals in all DBMSs.
  Example:

WHERE city = 'Rochester' AND state = 'NY'

• Double quotes for any alias that includes a space
  Example:    

SELECT univ_id, first_name "First Name", last_name "Last Name"

• Script submission must include a syntactically correct comment with student’s name and a syntactically correct comment identifying each task number. Comment options include:

-- single line comment (a space MUST be included after --)
# single line comment
/* block comment (can span multiple lines) */

• Script submission must be an executable script file (i.e. only comments and SQL statements). Common violations:
  • Submitting a log file
  • Including the MySQL prompt along with the statement
  • Including an uncommented result set

Suggestions:

• Include whitespace around operators
  Example:
    city = 'Rochester', not city='Rochester'
• Prefix column names with a table identifier
  Example, in table student:
    stu_univ_id    
  stu_first_name    
  stu_last_name    
  stu_maj_id
• Indent each hierarchical level
  Example:

SELECT stu_univ_id, stu_first_name, stu_last_name, stu_maj_id
FROM student
JOIN major
    ON stu_maj_id = maj_id
        AND maj_active = TRUE
WHERE stu_city = 'Rochester'
    AND stu_state = 'NY'
    AND stu_year_lvl >= 3;