Database Keys and Attributes Flashcards
Name the types of keys in DBMS (7).
- Primary key
- Candidate key
- Super key
- Foreign key
- Alternate key
- Composite key
- Artificial key
What is Primary key?
The first unique and non-null key used to identify one and only one instance of an entity. It is selected by the database designer while designing the database.
What is Candidate key (CK)?
A candidate key is an attribute or minimal set of attributes that can uniquely identify a tuple. Some of the candidate key columns can have a null value.
For example: In the EMPLOYEES table, id is best suited for the primary key. The rest of the attributes, like SSN, Passport_Number, and License_Number are considered a candidate key.
What is Super key?
Super key is an attribute set that can uniquely identify a tuple. A super key is a superset of a candidate key.
For example: In the EMPLOYEES table the name of two employees can be the same, but their EMPLOYEE_ID cannot. Hence, this combination can also be a super key.
The super key would be EMPLOYEE-ID (EMPLOYEE_ID, EMPLOYEE-NAME).
What is Foreign key?
Foreign keys are the column of the table used to point to the primary key of another table.
What is Alternate key?
There may be one or more attributes or a combination of attributes that uniquely identify each tuple in a relation. These attributes or combinations of the attributes are called the candidate keys. One key is chosen as the primary key from these candidate keys, and the remaining candidate key, if it exists, is termed the alternate key.
In other words, the total number of the alternate keys is the total number of candidate keys minus the primary key. The alternate key may or may not exist. If there is only one candidate key in a relation, it does not have an alternate key.
What is Composite key?
A primary key, but as a combination of two or more columns in a table that can be used to uniquely identify each row in the table.
Uniqueness is only guaranteed when the columns are combined; when taken individually the columns do not guarantee uniqueness.
What is Artificial key?
The key created using arbitrarily assigned data. These keys are created when a primary key is large and complex and has no relationship with many other relations. The data values of the artificial keys are usually numbered in a serial order.
For example, the primary key, which is composed of Emp_ID, Emp_role, and Proj_ID, is large in employee relations. So it would be better to add a new virtual attribute to identify each tuple in the relation uniquely.
What is an attribute?
A property or characteristics of an entity.
What is entity?
A living or non-living object.
What is a tuple?
One record (one row).
What is Non-prime / Non-key attribute?
A column which can not be used to identify a record uniquely, for example, name or age columns in the customers table.
Name 5 different types of attributes.
Simple, Composite, Single-Valued, Multi-Valued, Derived.
What is a simple attribute (with an example)?
An atomic attribute that cannot be further subdivided into components.
Example: the roll number of a student, the ID of an employee.
What is a single-valued attribute (with an example)?
The attribute that takes up only a single value for each entity instance.
Example: The age of a student.
