For each of the following concepts, 1) give a brief but clear explanation, and 2

Question

For each of the following concepts, 1) give a brief but clear explanation, and 2)
provide a real case example of the HR DB on how the integrity constrain is enforced by
the DBMS. (*Hint: The DB’s table schema contains the example information.)
a) Domain IC.
b) Primary key IC.
c) Referential IC.
d) The use of NULL. For each of the following concepts, 1) give a brief but clear explanation, and 2)
provide a real case example of the HR DB on how the integrity constrain is enforced by
the DBMS. (*Hint: The DB’s table schema contains the example information.)
a) Domain IC.
b) Primary key IC.
c) Referential IC.
d) The use of NULL. from the previous pic you can see the schema of the data base and all the attributes

Explanation / Answer

Domain Integrity Constraints can be defined as Set of Values for an Attribute.

In Domain Integrity, we have

1.      Column Integrity: A column MUST contain only values consistent with the defined data format

2.      User Defined Integrity - The data stored in the database MUST comply with the rules of the business.

1. Create specific validation rules for an attribute to limit its domain further.

2. Exclude various unacceptable values for an attribute

3. A business has the following user defined data integrity constraints. For example,

1. The "county code" may only contain the numbers 01 through 15.

2. An employee in the Finance Department cannot have a title of: "Programmer".

Primary key integrity constraint defines a column or combination of columns which uniquely identifies each row in the table.

Syntax to define a Primary key at column level:

          column name datatype [CONSTRAINT constraint_name] PRIMARY KEY

Syntax to define a Primary key at table level:

   [CONSTRAINT constraint_name] PRIMARY KEY   (column_name1,column_name2,..)

For example:

       CREATE TABLE employee
              (Employee_id number (5) PRIMARY KEY,
                First_name char (20),

                Last_name char (20),

                Email varchar (20),

                Phone number number (12),

                Salary number (10),
                Dept_ID char (10),

               Manager_ID (10));

Foreign key or Referential Integrity constraint identifies any column referencing the PRIMARY KEY in another table. It establishes a relationship between two columns in the same table or between different tables. For a column to be defined as a Foreign Key, it should be a defined as a Primary Key in the table which it is referring. One or more columns can be defined as foreign key.
Syntax to define a foreign key at column level:

[CONSTRAINT constraint_name]

                       REFERENCES Referenced_Table_name (column_name)

Syntax to define a Foreign key at table level:

[CONSTRAINT constraint_name] FOREIGN KEY (column_name)

                      REFERENCES referenced_table_name (column_name);

For example:

Assume that job history table and Department table

CREATE TABLE job history

     (Employee_ID number (5) CONSTRAINT E_ID primary key,

      Start_date date,

      End_date date,

      Job_Id number (5),

      Department_ID number (5) CONSTRAINT D_ID REFERENCES

      Department (Department_ID));

NULL values represent missing unknown data.

For example:

       CREATE TABLE employee
              (Employee_id number (5) NOT NULL,

                First_name char (20) NOT NULL,

                Last_name char (20) NOT NULL,

                Email varchar (20),

                Phone number number (12),

                Salary number (10),
                Dept_ID char (10) NOT NULL,

SELECT Employee_id, First_name, Last_name

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.