Constraints

-
Constraints protect the database from errors. It puts restrictions on database state.

Types of Constraints in Relational Model

  • Domain Constraint
  • Key Constraint
  • Entity Integrity Constraint
  • Referential Integrity Constraint 
Types of Constraints that cannot be captured in relational model
  • Semantic Constraints (Business Rules)
                       eg : Buy one get one free


Domain Constraint

Value of each attribute in a tuple must be atomic (no repeating values) and consistent with its domain(data type). It could be null if allowed.



Key Constraint


key constraint specifies that the values in the column declared as a primary key must be unique.

Types of keys
  • Candidate Key
           A relation has several keys referred to as candidate keys.but one candidate key is unique. it is               called the primary key.
  • Primary Key
          An attribute that uniquely identified the each row in a table.
  • Composite Key
          Two or more attribute that uniquely identified the each row in a table.
  • Foreign Key
          Set of columns in one table that serves as a primary key in another table.
  • Recursive Foreign Key
          A foreign key in a table that references the primary key values in that same table.
  • Super Key
          Every Key is a super key. But not Vice versa.

          (LicenseNumber , Model , Brand ) - This is a super key. but this is not a key.
          
           LicenseNumber  -  This is a super key and also a key

Properties of a key
  • uniqueness
  • minimality
name   id    amount

  A        1     1000
  B        2     2000
  C        3     1000

name can be a key. id can be a key.
[name , id] can be a key. It is called composite key.
But, it is not minimal. Because you can remove one column from it and still have a key.

amount is not a key. [amount , id] is a key. But it is not minimal.


Entity Integrity Constraint

Entity integrity constraint specifies that the primary key cannot be null.



Referential Integrity Constraint 

Tuples in the referencing relation(relation with a foreign key) must refer to tuples that exist in the referenced relation

Foreign Key and Primary Key are attributes that have the same domain although not necessarily the same attribute name.

        Professor (id , Name , DepID)
        Department (id , DName , Location)

        Professor(DepID) REFERENCES Department(id)

Possible Violations for INSERT and UPDATE
  • Domain Constraint
  • Key Constraint
  • Entity Integrity Constraint
  • Referential Integrity Constraint 
Possible Violations for DELETE
  • Referential Integrity Constraint 







Comments

Post a Comment

Popular posts from this blog

Normalization

-
Image
Normalization is a process for evaluating and correcting table structures to minimize data redundancies, thereby reducing the likelihood of data anomalies. Normalization starts by identifying the dependencies of a given relation and progressively breaking up the relation (table) into a set of new relations (tables) based on the identified dependencies. Normal Forms 0NF            Multi-valued attributes exists 1NF           Any multi-valued attributes (repeating groups) have been removed. 2NF           Any partial functional dependencies have been removed 3NF           Any transitive dependencies have been removed BCNF           Any anomalies resulting from functional dependencies have been removed 4NF           Any multi valued dependencies have been removed 5NF           Any remaining anomalies have been removed The table in 0NF : eliminate the nulls by making sure that each repeating group attribute contains an appropriate data va
Read more

Database Types

-
Types of Databases Databases can be classified according to the number of users, o    Single user database - A single-user database that runs on a personal computer is called a desktop database o    Multi user database ·          Workgroup database - the multiuser database supports a relatively small number of users (usually fewer than 50) or a specific department within an organization ·          Enterprise database Databases can be classified according to the database location(s), o    Centralized database - a database that supports data located at a single Site. o    Distributed database – a database that supports data distributed across several different sites The most popular way of classifying databases today, however, is based on how they will be used and on the time sensitivity of the information gathered from them. o    operational database (sometimes referred to as a transactional or production database) - A database that is designed primarily to suppor
Read more

Entity Relationship Diagram (Pat II)

-
Image
Mapping Binary Relationships One to one Relationship  Partial participation (Partial participation is represented by single lines.)           Not every professor teaches every student. Every student is not learnt by every professor.             Relational Mapping                                                                   Professor ( Name , Phone , Address )                                                                                       ↑FK            Course   ( CourseId  , CourseName , Credits , Name) Total Participation (Total participation is represented by double lines.)             Every professor teaches every student. Every student is learnt by every professor           Relational Mapping                      Professor ( Name , Phone , Address , CourseId , CourseName , Credits) One to many Relationship (Partial participation)           Relational Mapping                                          
Read more