DBMS-Keys , Attributes and Constraints.pptx

keys uniquely identify records;Attributes represent data characteristics, classified as simple or composite, with associated data types. Constraints maintain data integrity

1. Keys , Attributes and Constraints

2. Keys Keys play an important role in the relational database. It is used to uniquely identify any record or row of data from the table. It is also used to establish and identify relationships between tables. Types of keys: Primary key It is the first key used to identify one and only one instance of an entity uniquely. An entity can contain multiple keys, as we saw in the PERSON table. The key which is most suitable from those lists becomes a primary key.

3. Super Key Super key is an attribute set that can uniquely identify a tuple. A super key is a superset of a candidate key.

4. Candidate key A candidate key is an attribute or set of attributes that can uniquely identify a tuple. Except for the primary key, the remaining attributes are considered a candidate key. The candidate keys are as strong as the primary key. For example:  In the EMPLOYEE table, id is best suited for the primary key. The rest of the attributes, like SSN, Passport_Number , License_Number , etc., are considered a candidate key.

5. Foreign key Foreign keys are the column of the table used to point to the primary key of another table. Every employee works in a specific department in a company, and employee and department are two different entities. So we can't store the department's information in the employee table. That's why we link these two tables through the primary key of one table. We add the primary key of the DEPARTMENT table, Department_Id , as a new attribute in the EMPLOYEE table. In the EMPLOYEE table, Department_Id is the foreign key, and both the tables are related.

6. 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.

7. Composite key Whenever a primary key consists of more than one attribute, it is known as a composite key. This key is also known as Concatenated Key.

8. Artificial key T he key created using arbitrarily assigned data are known as artificial keys. 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.

9. Atrributes Types of Attributes Simple Attribute Simple attributes are atomic values, which cannot be divided further. Attributes are the  properties which define the entity type. For example, Roll_No , Name, DOB, Age, Address, Mobile_No are the attributes which defines entity type Student. In ER diagram, attribute is represented by an oval. 14

10. Key Attribute The attribute which  uniquely identifies each entity  in the entity set is called key attribute. For example, Roll_No will be unique for each student. In ER diagram, key attribute is represented by an oval with underlying lines. 15

11. Composite Attribute An attribute  composed of many other attribute  is called as composite attribute. For example, Address attribute of student Entity type consists of Street, City, State, and Country. In ER diagram, composite attribute is represented by an oval comprising of ovals. 16

12. Multivalued Attribute An attribute consisting  more than one value  for a given entity. For example, Phone_No (can be more than one for a given student). In ER diagram, multivalued attribute is represented by double oval 17

13. Derived Attribute An attribute which can be  derived from other attributes  of the entity type is known as derived attribute. e.g.; Age (can be derived from DOB). In ER diagram, derived attribute is represented by dashed oval 18

14. Constraints in DBMS Constraints enforce limits to the data or type of data that can be inserted/updated/deleted from a table. The whole purpose of constraints is to maintain the  data integrity  during an update/delete/insert into a table. In this tutorial we will learn several types of constraints that can be created in RDBMS. Types of constraints NOT NULL UNIQUE DEFAULT CHECK Key Constraints – PRIMARY KEY, FOREIGN KEY Domain constraints Mapping constraints

15. NOTNULL NOT NULL constraint makes sure that a column does not hold NULL value. When we don’t provide value for a particular column while inserting a record into a table, it takes NULL value by default. By specifying NULL constraint, we can be sure that a particular column(s) cannot have NULL values.

16. UNIQUE: UNIQUE Constraint enforces a column or set of columns to have unique values. If a column has a unique constraint, it means that particular column cannot have duplicate values in a table. DEFAULT: The DEFAULT constraint provides a default value to a column when there is no value provided while inserting a record into a table. CHECK: This constraint is used for specifying range of values for a particular column of a table. When this constraint is being set on a column, it ensures that the specified column must have the value falling in the specified range.