SQL: Schema, Constraints - an overview


In this article one would get an overview of the schema evolution commands of SQL. The reader would also gain the basic knowledge regarding DBMS and its relation to SQL as well as the benefits of using SQL over the previously used querying languages.

Structured Query Language widely known by its acronym SQL is the main reason for the rise in the usage of relational databases. It is so because these follow a particular standard and hence are easier to use. Once someone is dissatisfied with the database management system they are using, its easy to change to some other system of their choice. Different systems may have slightly different formats but if a person is well versed with the standard format of SQL, he or she can easily make themselves comfortable with the new system. From this discussion we can imagine the great amount of work reduced due to the mere presence of SQL. Prior to this it was a hectic process to shift data since each system would require a different set of commands to work with. Due to the presence of SQL, data fetching from multiple databases has also been simplified.

Relational Algebra forms the most important part while describing the process in which a particular relation in the database system would be specified but problem arises as these are too technical and are not preferred by the commercial users of the database system. SQL comes to rescue as while using these one does not need to worry about optimizing the relation generated, all one focuses on is the final output they desire. It is so because SQL is a higher-level declarative language interface.

NOTE: Though SQL is not case sensitive, the convention is to always write the command in capital letters.

SQL Data Definition

Technically termed as the Data Definition Language referred to as DDL. Its refers to the creation of a schema, table or domain. As the definition suggests the most important command for this task is CREATE which is used to accomplish any of the above mentioned tasks. It is essential to note that the terms relation, tuple and attribute are the technical terms which have been replaced by the terms table, row and column respectively in order to make the process more user friendly.

Schema

Schema is a concept that was introduced in the later versions of SQL. Prior to this all tables were the part of a single schema but after the introduction of this concept one can efficiently group tables and data under a single type of schema. A schema is recognized by the schema name and the authorization identifier to indicate the particular user who owns the given schema. A schema can be created using the command CREATE SCHEMA. In the same command one can include all the other elements to be defined in the given schema by extending the command line or the elements could be separately added by using their respective CREATE function. The elements include the table, constraints, view and other constructs.

CREATE SCHEMA SCHEMA_NAME AUTHORIZATION Schema_Owner_Name;


Table

Table in database management systems are used to define a relation in a given particular schema, the sole reason that table is also referred to as the relation in technical terms. A table has attributes i.e. columns that are specified during the creation of the table and also the data type of the particular attribute is listed in order to specify its domain. Apart from these one can also add constraints like NOT NULL or declare the attribute as the primary key for the relation, though one can add this using ALTER command as well. Usually the schema for a particular relation is implicitly specified by the environment in which the CREATE TABLE statements are used but its always a better option to declare the schema name as well.

CREATE TABLE SCHEMA_NAME.TABLE_NAME.....

instead of using
CREATE TABLE TABLE_NAME.....


The particular format used to create a relation in schema is given as:
CREATE TABLE TABLE_NAME
( ATTRIBUTE1_NAME DATA_TYPE(SIZE) CONSTRAINT,
ATTRIBUTE2_NAME DATA_TYPE(SIZE) CONSTRAINT);


One could also mention the primary and foreign keys along with this command. The tables created using the CREATE statement are called base tables since they are actually created into the DBMS. These differ from CREATE VIEW because the table hence created may not actually exist in the system. These are known as virtual relations and are derived from previously created tables and give the user information about what type of data is held in those. The attributes are maintained in the same order in the system as they were while being created. This does not apply to the tuples or even the tables generated using VIEW.

Data Types

Numeric

The different numeric data types are specified based on the variations in their sizes into INT or SMALLINT. Floating point numbers can be represented by the use of FLOAT and DOUBLE PRECISION. Formatted numbers are given as DEC(i,j) where i is the total number of decimal digits and j gives the scale i.e. the number of digits after the decimal point. Default value is 0.

Character String

Character strings can be declared as data types having fixed length, given by CHAR(n) where n defines the number of characters or can be a string of variable length i.e. VARCHAR(n) and here n is used to mention the maximum length of the string. Strings are populated by adding the detail inside single quotes. || can be used to concatenate two strings. CLOB can be used to declare attributes which would include large textual data.

Bit String

Tho basic forms used are BIT(n) having a fixed bit size n and BIT VARYING(n), where n gives the maximum possible bit size. BLOB is also available for data having large binary values as in images.

Boolean

As applies to all other programming languages, here also this particular data type has two main values, namely TRUE and FALSE. The third value that has been added in SQL is UNKNOWN.

Date and Time

Date has 10 positions comprising of year, month and day whereas time has 8 positions inclusive of hour, minute and second having the formats YYYY-MM-DD and HH:MM:SS respectively. The TIMESTAMP data type includes both date and time, moreover it has six positions for fractions of seconds. INTERVAL can be used to determine the relative value that can be used to increment or decrement an absolute value of any of the above three.

Constraints in SQL

The names of all the constraints in a particular schema should be unique since they are used to identify a given constraint when it needs to be dropped or replaced in future.

Attribute Constraints and Defaults

NULL is used to define those values which are 0 and in a similar manner NOT NULL is used to refer to those attributes which should never be 0. These are usually attached to attributes which act as the primary key or otherwise those which requires to have non-zero values. The default value can also be set for some given attribute. It is mentioned as DEFAULT and later this value can be used by directly mentioning default for those tuples which don't have any value. Default is taken as NULL for all those attributes which don;t have NON NULL attached to it until and unless specified otherwise. CHECK is another essential command that can be used as and when required either with CREATE statement or any other. Check is a tuple based constraint and is often used to query data that select some particular tuple on the basis of given information.

Keys and Referential Integrity Constraints

PRIMARY KEY forms the most part of this type of constraints since it is used to interact with other tables and can be used to efficiently execute a query. UNIQUE is a statement that can be used to gather all the unique values in a particular attribute. FOREIGN KEY is as important as primary key since the primary key of one table serves as the foreign key for another. It can does be noted that a proper combination of the two keys would produce the required database. These can also be used to avoid the violation of referential integrity constraints. It is done by referential triggered action which would use the previously described constraints as an combination with the key in use to solve the problem.

Schema Change Statements

DROP

This is quite opposite to the create statement as it can be used to drop table, constraints or the entire schema. It has two main behaviors CASCADE and RESTRICT. To completely remove some particular schema along with its tables, constraints etc we use the CASCADE statement as follows:
DROP SCHEMA SCHEMA_NAME CASCADE;


We can replace cascade with RESTRICT when the schema can be dropped once the schema is absolutely empty. In this case a table is dropped if we replace the schema by table in the above code. Drop statement not only deletes all the contents but also removes the element or schema from the catalog.

ALTER

It is used to rename or change the schema elements. It has the ability to drop or add values for base tables. Here also the the drop statement follows as above. Alter can be used in the following manner:
ALTER TABLE SCHEMA_NAME.TABLE_NAME ADD COLUMN ATTRIBUTE_NAME DATA_TYPE(SIZE);


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: