All object names must be unique or implicitly or explicitly qualified by the next higher object and can not be SQL reserved words.

The vendors’ specifications vary from the rules specified by the SQL2003 standard.

 

Object naming standards fall into two categories: 1. naming conventions and 2. identifier rules.SQL standards say little about naming conventions, but certain conventions have been adopted by programmers with many years of experience:

Choose names that are descriptive and meaningful with underscores. FB127 is meaningless. YEAR_TO_DATE_SALES or YTD_SALES is easily understood by everyone. Others may be using your tables and the meaning is clear to everyone. Each vendor has naming limits, but their size is generally large enough to be meaningful. Names like YEARTODATESALES are harder to read than YEAR_TO_DATE_SALES.

Use abbreviations consistently. Do not use YTD in some places and YEAR_TO_DATE in other places.

Use the same case consistently. Use all upper case or all lower case names. Postgre SQL converts unquoted object names to lower case, DB2 requires all upper case names, and other vendors are case sensitive. Using mixed-case names can cause problems.

Do not use double quoted names. Names bracketed by the double quote character (” “) are case sensitive and can be difficult to use.

Do not use manufacturer and brand names. Company and product names change.

A more structured 3NF is called the Boyce-Codd Normal Form (BCNF). A table is in BCNF if every determinate column is a candidate key. A determinate column is one on which some of the columns are fully functional. These are tables that have composite candidate keys, multiple candidate keys, or candidate keys that overlap.A table in fourth normal form (4NF) is in BCNF and has no multi-valued dependencies. A multi-valued dependency (MVD) is when one column has values in multiple rows that match a single value in another column of the same table. Suppose an employee works at multiple stores and is a customer representative for multiple customers. If you place all of this data into a single table, you must use all three attributes as the key because only all three attributes as keys can identify a unique row. The MVD relationship between employee and store says where each employee works. The MVD relationship between employee and customer says how many customers buy products from this employee. To change a table with MVDs to 4NF, move each MVD pair to a new table.

A table in fifth normal form (5NF) is in 4NF and has no join dependencies. 5NF table’s objectives are to have tables that can not be split into smaller tables. Most tables are already in 5NF and no further effort is necessary. Most people only have to normalize to 1NF, 2NF, 3NF, and sometimes 4NF.

A table in third normal form (3NF) must be in second normal form and have no transitive dependencies.
A transitive dependency is when a non-key column’s value determines another non-key column’s value.
A table is in third normal form when non-key columns are mutually independent and dependent on only
primary key column(s).

A domain-key normal form (DKNF) is a constraint of the domain (or range of permitted values) and the keys.
The sale determines the customer. Customer depends on sale and sale is a key.
The sale determines the item. Item depends on sale and sale is a key.
The item determines the price. Price depends on item and item is a key.
One table uses sale as a key and the other table uses item as a key.