Skip to main content

4. Referential integrity

Image
By



§ It is a database concept that ensures that relationships between tables remain consistent. When one table has a foreign key to another table.
§ Theconcept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table.
Example
§ Consider the situation where we have two tables: Employees and Managers.
§ The Employees table has a foreign key attribute entitled ManagedBy which points to the record for that employee’s manager in the Managers table.
Referential integrity enforces the following three rules:
1.      We may not add a record to the Employees table unless the ManagedBy attribute points to a valid record in the Managers table.
2.      If the primary key for a record in the Managers table changes, all corresponding records in the Employees table must be modifiedusing a cascading update.
3.      If a record in the Managers table is deleted, all corresponding records in the Employees table must be deleted using a cascading delete.







<<Prev                                                                                                                                              Next>>




Labels:

Comments

Post a Comment

Popular posts from this blog

5. INDEX

By
§ Indexes are special lookup tables that the database search engine can use to speed up data retrieval. § An index can be created in a table to find data more quickly and efficiently. § An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. § Indexes can be created or dropped with no effect on the data. § Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). Clustered indexes § Uses physical memory. § Easy to retrieve data. § Data is in stored form. Non-clustered indexes § No use of physical memory is needed. § More complicated, data retrieval is not that easy. § We can too createuniqueindex . Duplicate values are not allowed . CREATEINDEX index_name ON table_name ( column_name ) CREATEINDEX index_name ON table_name ( column_name ) CREATEINDEX Pindex Difference between clustered index and no...
Post a Comment
Read more

6. VIEW

By
§ A view is a virtual table. § Database views are created using the CREATE VIEW statement. § Views can be created from a single table, multiple tables, or another view. § When we update a view it will update underline base table. CREATEVIEW view_name AS SELECT column1 , column2 ..... FROM table_name WHERE [condition] Example : CREATEVIEW CUSTOMERS_VIEW AS SELECT name , age FROM   CUSTOMERS SELECT * FROM CUSTOMERS_VIEW                        Advantages § Reduce complexity of database schema. § Mechanism to implement row and column level security. § Represent aggregate data and hide detailed data. Limitations § Cannot pass parameter to view. § Rules and default cannot be associated with the view. § Order by clause is invalid in a view unless TOP or XML is also specified. § Cannot be created on the bases of temporary tables. Indexed View § Create u...
Post a Comment
Read more

15. Aggregate Functions

By
SQL has many built-in functions for performing calculations on data. Aggregate Functions AVG() Function The AVG() function returns the average value of a numeric column. § Syntax SELECT AVG ( column_name ) FROM table_name § Example SQL statement gets the average value of the "Price" column from the "Products" table: SELECT AVG ( Price ) AS PriceAverage FROM Products SQL statement selects the "ProductName" and "Price" records that have an above average price: SELECT ProductName , Price FROM Products WHERE Price >( SELECT AVG ( Price ) FROM Products ); COUNT() Function The COUNT() function returns the number of rows that matches a specified criteria.The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column: § Syntax SELECT COUNT ( column_name ) FROM table_name ; MAX() Function The MAX() function returns the largest value of the s...
Post a Comment
Read more