Schema and catalog concepts in SQL

 

SQL uses the terms table, row and column for the formal relational model terms relation, tuple, and attribute, respectively.

The key concept to start is the SQL scheme. This is a grouping of table and other database objects which logically belong together. An SQL scheme is identified by a schema name and it includes an authorization identifier to indicate the user or accounts who own the schema. They can perform any actions they want within the context of the scheme. A scheme is defined in the context of a business such as the order, the purchase order … Schema elements include tables, constraints, views, domains, and other constructs that describe the schema.

 

Ex : CREATE SCHEMA PURCHASE AUTHORIZATION ‘Ysaline’;

In general, not all users are authorized to create schemas and schema elements. The privilege to create schemas, tables, and other constructs must be explicitly granted to the relevant user accounts by the system administrator or DBA.

In addition to the concept of a schema, SQL uses the concept of a catalog, a named collection of schemas in an SQL environment.

The create table command in SQL

Once we have defined the schema we can start implementing SQL table. SQL tables implements a relation from the relational model. It specifies a new relation by giving it a name and specifies its attributes and initial constraints. It has multiple columns, one per attribute type and multiple rows, one for each tuple. An SQL table can be created by using the create table statements followed by the name of the table. It is recommended to explicitly assign a new table to an already existing scheme to avoid any confusions are inconsistencies.

 

We can explicitly attach the schema name to the relation name, separated by a period. Ex: CREATE TABLE COMPANY.EMPLOYEE

                          

An SQL table will have various colums, one per attribute table. Eache of these colums will have a corresponding data type to represent the format and the range of possible datavalues. Some examples are given in the table.These data types might be implemented differently in various different DBMSs.

 Domains in SQL

It is also possible to define a user-defined databtype or domain in SQL. This can be handy when the domain can be reused multiple times in a table or in a scheme. Changes to the domain definitions then only have to occur once. It improves the maintainability.

Ex : CREATE DOMAIN SSN_TYPE AS CHAR(9);

 Specifiying constraints in SQL

SQL column definitions can be further defined by imposing colums constraints.

  • The primary key constraints define the primarey key of a table. It should have unique values and null values should be not allowed. Ex : Dnumber INT PRIMARY KEY.
  •  A foreign key constraint defines the foreign key which typically refers to the primary key of another, restricting the number of possible values. 
  • The unique constraints defines an alternative key of the table. It can also be specified directly for a secondary key if the secondary key is a single attribute.
  •  The not null constraint prohibits null values for a column. As SQL allows NULLs as attribute values, a constraint NOT NULL may be specified if NULL is not permitted for a particular attribute. This is always implicitly specified for the attributes that are part of the primay key of each relation.
  • The default constraint can be used to set a default value for a column. The default value is included in any new tuple if an explicit value is not provided for that attribute.if no default clause is specified, the default “default value” is NULL for attributes that do not have the not null constraint.
  •  The check constraint can be used to define the constraint of a column value and restrict attribute or domain values. Ex : suppose that department numbers are resrricted to integer numbers between 1 and 20; then, we can cahnge the attribute declaration of Dnumber in the DEPARTMENT table. Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber <21);

 All the constraint shall be set in a close collaboration between the database developer and the business user.

 

Since many database objects are connected, we must specify how to manage the changes. This can be done by using referential integrity constraints.

  •   The on update cascade option says that an update should be cascaded to all referring objects.
  • The under lead cascade options says the removal should be cascaded to all referring objects. If the option is set to restrict, the update a removal will be holed in case referring objects exist.
  •  Set null implies that all objects will be set null.
  •   Set default allows setting a default value.

The author statement can also be used to modify table columns communications. Common actions are: adding or dropping a column.