Introduction to Relational Database Concepts

• ·         A constraint which ensures that the column contains no null values and uniquely identifies each row of the table. –Primary Key
• ·         A column or set of columns that refer to a primary key in the same table or another table. –Foreign key
• ·         An entry in a table, consisting of values for each appropriate column -row
• ·         An implementation of an attribute or relationship in a table -column
• ·         An integrity constraint that requires every value in a column or set of columns be unique. - Unique key
• ·         Collections of objects or relations, set of operators to act on those relations and data integrity for accuracy and consistency. –relational database
• ·         More than one column or combination of columns that could serve as the table’s primary key –candidate key

1. Identify the integrity rules below.

• ·         A column must contain only values consistent with the defined data format of the column –column integrity
• ·         Define the relationally correct state for a database; they ensure that users can perform only those operations that leave the database in a correct, consistent state –data integrity
• ·         A primary key must be unique and no part of the primary key can be null –entity integrity
• ·         A foreign key must match an existing primary key value (or else be null) –referential integrity
• ·         The data stored in a database must comply with the rules of the database –user defined integrity

2. Data Integrity in the DJs Database

Examine the sample data for the tables in the DJs on Demand database (next page). Check for entity, referential, and column integrity. Identify any data-integrity violations.

Assume that all date columns should have a date format and all amount (cost) columns should have a number format.

Below mentioned all are data integrity (entity, referential, column, user-defined) violations.

1.      Problem designer removed 77 from id column of client table. This created Entity Integrity issue in clients table and referential integrity issue in events table.

2.      Same in theme _code of events table for row with id 15 - and referential integrity issue

3.      Problem designer instructs all amount columns to be number format and gives cost in row with id 17 as 2gs (events table) to demonstrate column integrity violation.

4.      Same in date of row with id 25 in events table - column integrity violation

5.      To identify user-defined integrity we some need business rules here which must be followed – insufficient information provided to identify this kind of violation.