MySQL

There are few keys define in the MySQL, they are:

1. Primary key
2. Foreign key
3. Compound key

Primary key

It is a uniquely identified key in each records in the defined database. It does not contain "Null" values. Almost all table contains the primary key. The creation of primary key should be define while we are creating table into the database, else we need to alter the table as per our definition requirements.

Syntax

columnname datatype(size) NOT NULL PRIMARY KEY

example

 create table studper(pid int(3) not null primary key auto_increment,
 sid int references studinfo(sid),
 fathername varchar(20), 
 mothername varchar(20));
 

In the above code we have define the 'pid' as a Primary key which is 'not null' and can be 'auto_increment' its value.

Foreign key

As the name define foreign, its foreign to the other table, means if a primary key is present or declare in the different table and it can be used in different table as a column so its declare as a foreign key in the used table.

Syntax

columnname datatype references tablename(columnname or Primaykeyname)

In the above code we have define "sid" as a foreign key in the current table(studper) and tablename is studinfo is the references table in which reference column name is sid.

Compound key

It is a key that consists of multiple columns, because one column is not sufficiently unique in the define table. It also called composite key.

It is the process of representing the essential features. there are three forms of schemas or level define they are:

1. Physical
2. Conceptual
3. External

Physical level

In this level of schema it describes details of how data is stored: files, indices, etc. on the other access disk system. It also describes the record layout of files and type of files structure we are using like hash, b-tree,etc. It describe the low level of abstraction structure in detail.

Conceptual level

In this level of schema, it describes what type data are stored in the database, and what relationships exist among those data. It is also refered as the logical level.

External level

In this level of schema it presents data as a set of relations. It specifies a view of the data in terms of the conceptual level. it also referred as the view level.