Integrity Constraints

Integrity Constraints

• Integrity Constraints are the set of constructs provided by a data model for specifying conditions that must be satisfied by the data.

• An Integrity constraint (IC) is a condition specified on a database schema and restricts the data that can be stored in an instance of the database.

• Integrity constraints can be

ü  Domain Constraints

ü  Key Constraints

ü  Foreign Key Constraints

ü  General Constraints

Domain Constraints

§  Domain refers to a set of possible values for each attribute associated with an entity set.

§  Domain constraints in the schema specify an important condition that the user wants each instance of the relation to satisfy.

 

                Key Constraints

§  A Key Constraint is a statement that a certain minimal subset of the fields of a relation is a unique identifier for a tuple.

§  Super Key :  It is a set of one or more attributes which put together enable us to identify uniquely an entity in the entity set.

§  Candidate Key : A set of fields that uniquely identifies a tuple according to a key constraint is called a candidate key.  A superkey for which no subset is a superkey is called a candidate key.  ( A superkey that is minimal is a candidate key ) Eg.  In students relation, sid is candidate key.

§  Primary Key : It is a candidate key (there may be more than one) chosen by the database designer to identify entities in an entity set.

                Foreign Key Constraints

§  The most common IC involving two relations is a foreign key constraint.

§  Foreign Key : If the primary key of one table is referenced in the other table, then that key is called as foreign key in the another table.  The foreign key in the referencing relation must match the primary key of the referenced relation.

               General Constraints

§  General constraints : Domain, primary key and foreign key constraints are considered to be a fundamental part of the relational data model.  It is also necessary to specify more general constraints.

§  General constraints are

o   Table Constraints – Table constraints are associated with a single table and checked whenever the table is modified.

o   Assertions – Assertions involve several tables and are checked whenever any of these tables is modified.

 

Difference between RDBMS , ORDBMS and OODBMS

RDBMS ,ORDBMS  AND OODBMS

Compare RDBMS with ORDBMS.    

S.No	RDBMS	ORDBMS
1	Relational Database Management Systems	Object – Relational Database Systems
2	Based on Relational Data Model	Based on Object Data Model (ODM)
3	Dominant model	Gaining popularity
4		ORDBMS is an attempt to extend relational database systems to provide a bridge between the relational and object-oriented paradigms.
5	RDBMS support a small, fixed collection of data types ( eg. Integers, dates, strings ) which has proven adequate for traditional application domains such as administrative data processing	ORDBMS is based on Object-Oriented Database systems and Relational Database systems and are aimed at application domains where complex objects play a central role.
6	Supports Structured Query Language ( SQL )	Supports Object Query Language ( OQL ) SQL : 1999 standard extends SQL to incorporate support for the object-relational model of data
7	RDBMS products : IBM’s DB2 Informix Oracle Sybase Microsoft’s Access Fox Base Paradox Tandem Teradata	Object-oriented model products: Objectstore Versant Object-relational model products: Used in DBMS products from IBM Informix Objectstore Oracle Versant Others
8	Supports Standard data types and additional data types	Supports standard data types and new richer data types. The new richer data types supported are User-defined data types that supports image, voice and video footage and these must be stored in the database Inheritance data types to inherit the commonality between different types (eg.  To inherit some features of image objects while defining compressed image objects and low-resolution image objects Object Identity data types like references or pointers to objects (eg video) for giving objects a unique object identity, which can be used to refer or point to them from elsewhere in the data.
9	Case Scenario :	Case Scenario :

9.      Compare the similarities and differences between OODBMS and ORDBMS.  In particular compare OQL and SQL : 1999 and discuss the underlying data model.

·         OODBMS : Object-Oriented Database Management Systems

·         ORDBMS : Object-Relational Database Management Systems

·         Similarities

§  Both supports user-defined ADTs, structured types, object identity and reference types and inheritance.

§  Both supports an extended form of SQL. OODBMS support ODL/OQL. ORDBMS support an extended form of SQL.

§  ORDBMS consciously try to add OODBMS features to an RDBMS and OODBMS in turn have developed query language based on relational query languages.

§  Both provide DBMS functionality such as concurrency control and recovery.

·         Differences

S.No	OODBMS	ORDBMS
1	OODBMSs aim to achieve seamless integration with a programming language such as C++, Java.	Such integration is not an important goal for an ORDBMS.
2	An OODBMS is aimed at applications where an object-centric viewpoint is appropriate.	An ORDBMS is optimized for applications in which large data collections are the focus, even though objects may have a rich structure and be fairly large,
3	The query facilities of OQL are not supported efficiently in most OODBMSs.	The query facilities are the centerpiece of an ORDBMS.

 

File System Vs Database Management System

File Based System Vs  Database System

S.No	Points of distinction	File Systems	DBMS
1.	Main memory storage limit	Limited memory to store and hold data.  Secondary storage devices are used to hold data and relevant parts are to be loaded into main memory for processing as needed.	More memory to store and hold data.  Internal  storage mechanisms need not be revealed to the user. Relevant parts of data are automatically loaded into main memory for processing as needed.
2.	Retrieval complexity	A user has to write special programs to answer each question a user may want to ask about data.	Automatic commands and utilities are available to retrieve data.
3.	Searching complexity	Searching complexity is more because of the large volume of data to be searched.	Searching complexity is less because efficient searching technique is adopted by DBMS itself.
4.	Data Inconsistency	The user must protect data from inconsistent changes made by different users while accessing the data concurrently.	Data Integrity is maintained automatically by DBMS itself.
5.	Crash Recovery	The user must ensure that data is restored to a consistent state if the system crashes while changes are being made.	DBMS protects users from the effects of system failures.
6.	Security Mechanism	Operating systems provide only a password mechanism for security for files.  This is not sufficiently flexible  to enforce security policies in which different users have permission to access different subsets of data.	DBMS provides more security measures by centralizing the administration of data when several users share the data .
7.	Volume of data and no of users use	As the volume of data and number of users grow – hundreds of gigabytes of data and thousands of users  file system is complex and unable to manage	As the volume of data and number of users grow – hundreds of gigabytes of data and thousands of users  DBMS is efficient  to manage Corporate databases.

When the user should  not use a DBMS :

• A DBMS should be used only for answering complex queries or handling many concurrent requests.  It is not suitable for certain specialized applications with tight real-time constraints or applications with just a few well-defined critical operations for which efficient custom code must be written.
• An application sometimes may need to manipulate the data in ways that are not supported by the query language.  In such cases, DBMS is not used.  Eg.  Relational databases do not support flexible analysis of text data.
• If specialized performance or data manipulation requirements are central to an application, the application may choose not to use a DBMS.

DBMS QUIZ SET1

Solved MCQ of Database Management System  Set - 1

1. DBMS is a collection of ………….. that enables user to create and maintain a database.

A) Keys

B) Translators

C) Program

D) Language Activity

2. In a relational schema, each tuple is divided into fields called

A) Relations

B) Domains

C) Queries

D) All of the above

3. In an ER model,  ……………. is described in the database by storing its data.

A) Entity

B) Attribute

C) Relationship

D) Notation

4. DFD stands for

A) Data Flow Document

B) Data File Diagram

C) Data Flow Diagram

D) Non of the above

5. A top-to-bottom relationship among the items in a database is established by a

A) Hierarchical schema

B) Network schema

C) Relational Schema

D) All of the above

6. ……………… table store information about database or about the system.

A) SQL

B) Nested

C) System

D) None of these

7. …………..defines the structure of a relation which consists of a fixed set of attribute-domain pairs.

A) Instance

B) Schema

c) Program

D) Super Key

8. ……………… clause is an additional filter that is applied to the result.

A) Select

B) Group-by

C) Having

D) Order by

9. A logical schema

A) is the entire database

B) is a standard way of organizing information into accessible parts.

C) Describes how data is actually stored on disk.

D) All of the above

10. ………………… is a full form of SQL.

A) Standard query language

B) Sequential query language

C) Structured query language

D) Server side query language

11. Processed data is called ………………..

A) Raw data

B) Information

C) Useful data

D) Source

12. …………….. is a utility to capture a continuous record of server activity and provide  auditing capability.

A) SQL server Profile

B) SQL server service manager

C) SQL server setup

D) SQL server wizard.

13. Data items grouped together for storage purposes are called a

A) record

B) title

C) list

D) string

14. …………. contains data assisting day to day activities of the organization.

A) Control database

B) Operational database

C) Strategic database

D) Sequential database

15. ………………… approach reduces time and effort required for design and lesser risk in database management.

A) Single global database

B) Top-down approach

C) Multiple databases

D) None of the above

16. HSAM stands for ……….

A) Hierarchic Sequential Access Method

B) Hierarchic Standard Access Method

C) Hierarchic Sequential and Method

D) Hierarchic Standard and Method

17. SQL server stores index information in the ………… system table

A) systindexes

B) systemindexes

C) sysind

D) sysindexes

18. The one guideline to be followed while designing the database is

A) A database design may be ambiguous.

B) Unrelated data should be in the same table so that updating the data will be easy.

C) It should avoid/reduce the redundancy.

D) An entity should not have attributes.

19. Which of the following is not a logical database structure?

A) Chain

B) Network

C) Tree

D) Relational

20. ……………. is a preferred method for enforcing data integrity

A) Constraints

B) Stored procedure

C) Triggers

D) Cursors

Answers:

1.       1C) Program 2.      2 B) Domains 3.      3 A) Entity 4.       C) Data Flow Diagram 5.       A) Hierarchical schema 6.       C) System 7.       B) Schema 8.       C) Having 9.       B) is a standard …... accessible parts. 10.   A) Standard query language

11.  11 B) Information 12.   12B) SQL server service manager 13.   13A) record 14.   B) Operational database 15.   C) Multiple databases 16.   A) Hierarchic Sequential Access Method 17.   D) sysindexes 18.   C) It should avoid/reduce … redundancy. 19.   A) Chain 20.   A) Constraints