Slide 5.16: SQL (Structured Query Language)

Slide 5.15: Relational databases and SQL
Slide 5.17: SQL (cont.)
Home

SQL (Structured Query Language)

SQL Naming Conventions
A naming convention is a set of unwritten rules that you should use if you want to increase the readability of the whole data model:

All SQL commands should be written in uppercase letters.
Use lower letters when naming database objects.
Use the singular for the table or column name such as employee.
If the name includes more than one word, use underscore to connect them like first_name.
It would be the best to simply name a primary key column “id.”
Foreign key columns store values from the range of primary key of the referenced table, and you should use that table name and “id,” e.g., customer_id.
The name or variable uses the italic font style or symbol font if it is used in text.

CREATE TABLE

CREATE TABLE table-name (
  column-name-1 type [NOT NULL | UNIQUE] [, column-name-2 ... ]
  ...
  [, UNIQUE ( list-of-column-names ) [, UNIQUE ... ] ]
  [, PRIMARY KEY ( list-of-column-names ) ]
  [, FOREIGN KEY ( list-of-column-names )
      REFERENCES  table-name-2 [ ( list-of-column-names ) ]
  [, FOREIGN KEY ... ] ]
  [, CHECK ( condition ) [, CHECK ... ] ] )

Key (PRIMARY KEY) means an attribute or attribute combination whose values uniquely identify the tuples of any relation.

Foreign key (FOREIGN KEY) defined for a schema R, however, describes an attribute or attribute combination which is a key in another schema S (REFERENCES); the meaning of a foreign key relationship between R and S via attribute X is that the X part of R is a subset of the X part of S.

SQL> DROP TABLE  student;
SQL> CREATE TABLE  student (
  2    student_id   CHAR(4) PRIMARY KEY,
  3    last_name    VARCHAR(32),
  4    first_name   VARCHAR(32),
  5    class        INTEGER,
  6    phone        VARCHAR(16) );

Slide 5.15: Relational databases and SQL
Slide 5.17: SQL (cont.)
Home

My wife accused me of being immature.
I told her to get out of my fort.