TYPES OF ATTRIBUTES IN DBMS

TYPES OF ATTRIBUTES AND KEYS  IN DBMS

Types of Attributes

• Simple attribute − Simple attributes are atomic values, which cannot be divided further. For example, a student's phone number is an atomic value of 10 digits.
• 
  
• Composite attribute − Composite attributes are made of more than one simple attribute. For example, a student's complete name may have first_name and last_name.
• 
  
• Derived attribute − Derived attributes are the attributes that do not exist in the physical database, but their values are derived from other attributes present in the database. For example, average_salary in a department should not be saved directly in the database, instead it can be derived. For another example, age can be derived from data_of_birth.
• 
  
• Single-value attribute − Single-value attributes contain single value. For example − Social_Security_Number.
• 
  
• Multi-value attribute − Multi-value attributes may contain more than one values. For example, a person can have more than one phone number, email_address, etc.
• 
  
• TYPES OF KEYS
• The different types of keys in DBMS are:
  1. Candidate Key - The candidate keys in a table are defined as the set of keys that is minimal and can uniquely identify any data row in the table.A minimal super key is called a candidate key. An entity set may have more than one candidate key
  2. Primary Key - The primary key is selected from one of the candidate keys and becomes the identifying key of a table. It can uniquely identify any data row of the table.A primary key is one of the candidate keys chosen by the database designer to uniquely identify the entity
  3. Super Key - Super Key is the superset of primary key.A set of attributes (one or more) that collectively identifies an entity in an entity set. The super key contains a set of attributes, including the primary key, which can uniquely identify any data row in the table.
  4. Composite Key - If any single attribute of a table is not capable of being the key i.e it cannot identify a row uniquely, then we combine two or more attributes to form a key. This is known as a composite key.
  5. Secondary Key - Only one of the candidate keys is selected as the primary key. The rest of them are known as secondary keys.
  6. Foreign Key - A foreign key is an attribute value in a table that acts as the primary key in another another. Hence, the foreign key is useful in linking together two tables. Data should be entered in the foreign key column with great care, as wrongly entered data can invalidate the relationship between the two tables 

courtesy;tutorialpt

Question on DBMS

 Information in a bank is about customers and their accounts. Customer has a name, address, which
 consists of house number, area, city and one phone number. Account has numbers, type, and balance. 

We need to record customers who own an account. Account can be held individually or jointly. Account cannot exist without a customer. Arrive at an E-R diagram, clearly indicate: entities, attributes, relationships.

MERITS AND DEMERTS OF INDEXNG

MERITS

1)Speed up SELECT query

2)Helps to make a row unique or without duplicates(primary,unique) 

3)If index is set to fill-text index, then we can search against large string values. for example to find a word from a sentence etc.

4)They result  in better queries performance.

5)They make it possible to quickly retrieve (fetch) data.

6)They can be used for sorting..

7)Unique indexes guarantee uniquely identifiable records in the database.

DEMERTS

1)Indexes take additional disk space.

2)indexes slow down INSERT,UPDATE and DELETE, but will speed up UPDATE if the WHERE condition has an indexed field.  INSERT, UPDATE and DELETE becomes slower because on each operation the indexes must also be updated.

3)Some databases will monocase(It means that filenames are case-insensitive) values in fields that are indexed

‎


courtesy..bayt.com