Compound key

A compound key is any database key that consists of two or more columns.

Examples:
If a product is uniquely identified by its model number and revision code, the combination of the model number and revision code is both a compound key and a candidate key for that table. The database designer may also select this candidate key to be the primary key for the table.

Candiate key

A candidate key is a combination of attributes that can be uniquely used to identify a database record without any extraneous data. Each table may have one or more candidate keys. One of these candidate keys is selected as the table primary key.


Primary keys

Every database table should have one or more columns designated as the primary key. The value this key holds should be unique for each record in the database.
For example, assume we have a table called Employees that contains personnel information for every employee in our firm. We’d need to select an appropriate primary key that would uniquely identify each employee.

Once you decide upon a primary key and set it up in the database, the database management system will enforce the uniqueness of the key. If you try to insert a record into a table with a primary key that duplicates an existing record, the insert will fail.

Foreign keys

A foreign key is a field in a relational table that matches the primary key column of another table. The foreign key can be used to cross-reference tables.

The other type of key that we’ll discuss in this course is the foreign key. These keys are used to create relationships between tables.

Database relationshps

A relationship exists between two database tables when one table has a foreign key that references the primary key of another table.

Default values
Default vlaues allow you to specify a value that the database will use to populate fields that are left blank in the input source. They’re a replacement for the use of NULL values that provide a great way to predefine common data elements.

Many to many relationships

Many-to-many relationships occur when each record in TableA may have many linked records in TableB and vice-versa.

One to many relationships
One-to-many relationships occur when each record in TableA may have many linked records in TableB but each record in TableB may have only one corresponding record in TableA.

One to one relationships
One-to-one relationships occur when there is exactly one record in the first table that corresponds to exactly one record in the related table.

What is database

Databases are designed to offer an organized mechanism for storing, managing and retrieving information. They do so through the use of tables.

Just like Excel tables, database tables consist of columns and rows. Each column contains a different type of attribute and each row corresponds to a single record.


Stored Procedure

Stored procedures are precompiled database queries that improve the security, efficiency and usability of database client/server applications. Developers specify a stored procedure in terms of input and output variables. They then compile the code on the database platform and make it available to aplication developers for use in other environments, such as web applications. All of the major database platforms, including Oracle, SQL Server and MySQL support stored procedures. The major benefits of this technology are the substantial performance gains from precompiled execution, the reduction of client/server traffic, development efficiency gains from code reuse and abstraction and the security controls inherent in granting users permissions on specific stored procedures instead of the underlying database tables.