1

I was learning about keys and integrity in database tables. I am having this database : enter image description here

I want to know that Do the tables exhibit entity integrity ? If Yes why and also do thet exhibit referential integrity ?

Also what are the super key and secondary keys for each table. Am confused in this concept.

ms8
  • 417
  • 2
  • 13

1 Answers1

0

They display both entity integrity and referential integrity.

CONCEPT

Super Keys, like a Primary Key (PK) are used to identify each record independently as a unique row. A Super Key is formed by combining multiple columns while still remaining unique, which can include more than the minimum number of columns to create unique distinctions. A Candidate Key is set of columns that uses the minimum columns necessary to distinguish unique keys.

In essence, the Super Keys, Candidate Keys, and Primary Key are used to distinguish records with similar or identical values.

Secondary Keys are the Candidate Keys NOT selected as the Primary Key.

Foreign Keys are used to 'reference' separate tables to prevent the duplication of data.

The database has referential integrity if all the Foreign Keys are valid. For example, if the TRUCK table stored an FK to a BASE_CODE that doesn't exist, then the database would not have referential integrity.

If we were to remove the TRUCK_NUM, TRUCK_MILES, TRUCK_BUY_DATE and TRUCK_SERIAL_NUM from the TRUCK table, the Database would lose entity integrity since not all rows could be distinguish as unique. As long as each record in a table has a unique ID then the database has entity integrity.

SPECIFIC ANWSER

No records have repeated Primary Keys, thus the DB has entity integrity.

  • NO Truck Numbers duplicated
  • NO BASE_CODE duplicated
  • NO TYPE_CODE duplicated

All the Foreign Keys exist and the reference can be dereferenced to an actual record, thus the table has referential integrity.

The Super Keys for the table can be a combination of any of the fields that results in a Unique distinction between records ( i.e. combining truck_num, truck_miles, truck_buy_date and truck_serial_num would make a super key, likewise, combining just truck_numb, truck_serial would also create a valid Super Key). Super Keys vs. Candidate Keys

Informally, a superkey is a set of attributes within a table whose values can be used to uniquely identify a tuple. A candidate key is a minimal set of attributes necessary to identify a tuple; this is also called a minimal superkey. Wikipedia - Superkey

Secondary Keys are the unselected candidate keys for a table. For your given example, the TRUCK_NUM has been selected as the Primary Key. However, other Candidate Keys exist and could have been selected in place of TRUCK_NUM. These include TRUCK_SERIAL, TRUCK_BUY_DATE, and TRUCK_MILES_NUM as none of these fields repeat a value. Since they were not selected as a Primary Key, they are considered Secondary [Candidate] Keys.

Secondary Keys Explained

Community
  • 1
  • 1
Matt
  • 879
  • 9
  • 29
  • I need explanation for entity and referntial integirty parts – ms8 Jul 04 '15 at 08:23
  • Its that last couple of lines. You have to understand the concept to understand the answer. – Matt Jul 04 '15 at 08:38
  • Look at the Values under TRUCK_NUM, see any duplicates? Look under BASE_CODE, see any duplicates? Look under TYPE_CODE, see any duplicates? IF SO, Db is does not have Entity Integrity. – Matt Jul 04 '15 at 08:39
  • Under the TRUCK_NUM table, check to see if all the values for BASE_CODE exist. Do the same for TYPE_CODE. IF you find a code that doesn't exist in one of the tables, then the DB does not have referential integrity. Blanks are OK, it just mean that the specific record may not need to make the reference to another table (this is situational), if values are required, then the Db does not have referential integrity if a blank exists. – Matt Jul 04 '15 at 08:43
  • Are you sure PK are super keys ? I doubt it. Also what are candidate keys ? are they also FKs ? – ms8 Jul 04 '15 at 10:55
  • I got entity and refernetial part. Can you tell candidate key(s) in TRUCK table ? and superkey and secondary key for each table – ms8 Jul 04 '15 at 10:57
  • @ms8 Updated the answer last night, please re-read. Corrected some errors. – Matt Jul 04 '15 at 19:28