Explain following terms with suitable example.

(i) Weak entity set

(ii) Data manipulation language

(iii) Foreign key

(iv) Super key

i. Weak entity set:

• An entity set that does not have a primary key is referred to as a weak entity set.
• The existence of a weak entity set depends on the existence of a identifying entity set:
  • It must relate to the identifying entity set via a total, one-to-many relationship set from the identifying to the weak entity set.
  • Identifying relationship depicted using a double diamond.
• The discriminator (or partial key) of a weak entity set is the set of attributes that distinguishes among all the entities of a weak entity set.
• The primary key of a weak entity set is formed by the primary key of the strong entity set on which the weak entity set is existence dependent, plus the weak entity set’s discriminator.
• We depict a weak entity set by double rectangles. We underline the discriminator of a weak entity set with a dashed line.
• Example:
  • payment-number – discriminator of the payment entity set
  • Primary key for payment – (loan-number, payment-number).
• Note: the primary key of the strong entity set is not explicitly stored with the weak entity set, since it is implicit in the identifying relationship.
• If loan-number were explicitly stored, payment could be made a strong entity, but then the relationship between payment and loan would be duplicated by an implicit relationship defined by the attribute loan-number common to payment and loan.

ii. Data manipulation language:

• Data Manipulation Language (DML) statements are used for managing data in database. DML commands are not auto-committed.
• It means changes made by DML command are not permanent to database, it can be rolled back.

• This manipulation involves inserting data into database tables, retrieving existing data, deleting data from existing tables and modifying existing data.

  • INSERT command:

    • Insert command is used to insert data into a table.

    • Following is its general syntax:

      INSERT into table-name values(data1,data2,…);

  • UPDATE command:

    • Update command is used to update a row of a table.

    • Following is its general syntax:

      UPDATEtable-name set column-name = value

      where condition;

  • DELETE command:

    • Delete command is used to delete data from a table. Delete command can also be used with condition to delete a particular row.

    • Following is its general syntax:

      Delete from table-name;

iii. Foreign Key:

• A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. In simpler words, the foreign key is defined in a second table, but it refers to the primary key in the first table.
• For example, a table called Employee has a primary key called employee_id. Another table called Employee Details has a foreign key which references employee_id in order to uniquely identify the relationship between both the tables.
• A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.

iv. Super key:

• A super key is defined in the relational model of database organization as a set of attributes of a relation variable for which it holds that in all relations assigned to that variable, there are no two distinct tuples (rows) that have the same values for the attributes in this set.
• Equivalently a super key can also be defined as a set of attributes of a relation schema upon which all attributes of the schema are functionally dependent.
• A super key is a set of fields that contains a key.
• Consider the example where the combination of "user_id" and "pet_id" uniquely identifies a tuple if we added "pet_name" (which is not key because we can have multiple pets named "fluffy") it would be a super key. Basically it's like a candidate key without the "minimal subset of fields" constraint.