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(univID, …)
• Foreign key are italicized when typed, dash-underlined when written
  Example:
    STUDENT(univID, collegeID, …)
• Attribute names that would otherwise include a space or an underscore as a separator are to be in camelCase ID exception:  ID is commonly used as an abbreviation for ‘identifier’.  Attributes that include ‘ID’ can be stated as attrID, instead of the strict camelCase of attrId
  Example:
    STUDENT(univID, collegeID, firstName, lastName, expectedGradYear, …)

Implementation (Coding) Standards:

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

SELECT univID, firstName, lastName    
FROM student
WHERE city = 'Rochester' AND state = 'NY';

• Keywords and data types must be in UPPERCASE
  Example:

CREATE TABLE test (
    testID CHAR(9),
    testCount INT,
    testDesc VARCHAR(255),
    CONSTRAINT test_pk PRIMARY KEY(testID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• Table names and attribute names that would otherwise include as space or an underscore as a separator must be in camelCase ID exception:  ID is commonly used as an abbreviation for ‘identifier’.  Attributes that include ‘ID’ can be stated as attrID, instead of the strict camelCase of attrId
  Example:

SELECT univID, collegeID, firstName, lastName

• 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 univID, firstName "First Name", lastName "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:
    stuUnivID    
  stuFirstName    
  stuLastName    
  stuMajID
• Indent each hierarchical level
  Example:

SELECT stuUnivID, stuFirstName, stuLastName, majName
FROM student
JOIN major
    ON stuMajorID = majID
        AND majActive = TRUE
WHERE stuCity = 'Rochester'
    AND stuState = 'NY'
    AND stuYearLvl >= 3;