Page 89 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

3 Constraints: Primary keys enforce data integrity by preventing duplicate and null values in the primary key
column(s).
4 Indexed: By default, primary key columns are automatically indexed, which can improve query performance.
5 One per Table: Each table can have only one primary key.

6 Used for Joins: Primary keys are often used as reference points for creating relationships with other tables
through foreign keys.
Foreign Key

1 Relationships: A foreign key is a column or a set of columns in one table that establishes a link or relationship
between data in two tables. It references the primary key of another table.
2 Referential Integrity: Foreign keys enforce referential integrity by ensuring that data in the referencing table
(child table) corresponds to data in the referenced table (parent table).
3 Optional: While foreign keys are commonly used to create relationships, they are not required in every table.
Tables can exist without foreign keys.

4 Values Must Match: Values in the foreign key column(s) must match values in the primary key column(s) of
the referenced table.
5 Can Have Multiple: A table can have multiple foreign keys, each referencing a different table and primary key.

6 Used for Joins: Foreign keys are used to join related tables, allowing you to retrieve data from multiple tables
based on their relationships.

Adding Indices

Adding indices to databases is a fundamental part of database optimization. Indices improve query performance
by allowing the database management system (DBMS) to quickly locate rows that meet specific criteria. Here’s
how you can add indices to a database, typically using SQL:
1 Determine Which Columns to Index

Start by identifying the columns that are frequently used in WHERE clauses of your SELECT queries. These
are good candidates for indexing, as they can significantly speed up data retrieval. Consider indexing columns
used in JOIN conditions as well.
2 Choose the Appropriate Index Type
There are different types of indexes, and the choice depends on your specific use case and DBMS:
• Single-Column Index: This indexes a single column.
• Composite Index: Indexes multiple columns together, useful for queries involving multiple criteria.

• Unique Index: Ensures uniqueness on indexed columns.
• Clustered Index (in some DBMS): Determines the physical order of data rows in the table.
• Non-Clustered Index: Contains a copy of the indexed columns and a pointer to the actual data row.
3 Create an Index

To create an index, you use the ‘CREATE INDEX’ statement in SQL. Here’s a basic syntax example for
creating a single-column index:
CREATE INDEX index_name

ON table_name (column_name);
For example, to create an index called ‘idx_last_name’ on the ‘last_name’column of a table named ‘employees’ :-
CREATE INDEX idx_last_name
ON employees (last_name);
If you want to create a composite index on multiple columns, you can specify them within the parentheses:

76

CITS : IT&ITES - Computer software application - Lesson 18 - 36