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<br> 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;