DBMS Assignment Important Ques and answer

    1.    A well-maintained relational DBMS has a high level of data integrity. What features of a relational DBMS contribute towards this level of integrity?

Ans- Features of DBMS

The following are main features offered by DBMS.

Apart from these features different database management systems may offer different features.

For instance, Oracle is increasing being fine-tuned to be the database for Internet applications.  This may not be found in other database management systems.  These are the general features of database management systems. Each DBMS has its own way of implementing it. A DBMS may have more features the features discussed here and may also enhance these features.

Support for large amount of data Each DBMS is designed to support large amount of data. They provide special ways and means to store and manipulate large amount of data.  Companies are trying to store more and more amount of data. Some of this data will have to be online (available every time).

In most of the cases the amount of data that can be stored is not actually constrained by DBSM and instead constrained by the availability of the hardware.

For example,

Oracle can store terabytes of data. Data sharing, concurrency and locking

DBSM also allows data to be shared by two or more users.  The same data can be accessed by multiple users at the same time – data concurrency. However when same data is being manipulated at the same time by multiple users  certain problems arise. To avoid these problems, DBMS locks data that is being manipulated to avoid two users from modifying the same data at the same time. The locking mechanism is transparent and automatic. Neither we have to inform to DBMS about locking nor we need to know how and when DBMS is locking the data. However, as a programmer, if we can know intricacies of locking mechanism used by DBMS, we will be better programmers. Data Security While DBMS allowing data to be shared, it also ensures that data in only accessed by authorized users. DBMS provides features needed to implement security at the enterprise level. By default, the data of a user cannot be accessed by other users unless the owner gives explicit permissions to other users to do so.                                                           

Data Integrity

Maintaining integrity of the data is an import process. If data loses integrity, it becomes unusable and garbage. DBMS provides means to implement rules to maintain integrity of the data. Once we specify which rules are to be implemented, then DBMS can make sure that these rules are implemented always.

Fault tolerance and recovery

DBMS provides great deal of fault tolerance. They continue to run in spite of errors, if possible, allowing users to rectify the mistake in the mean time. DBSM also allows recovery in the event of failure. For instance, if data on the disk is completely lost due to disk failure then also data can be recovered to the point of failure if proper back up of the data is available.

Entity and Attribute

An entity is any object that is stored in the database.  Each entity is associated with a collection of attributes. For example, if you take a data of a training institute, student is an entity as we store information about each student in the database. Each student is associated with certain values such as roll number, name, course etc., which are

called as attributes of the entity. There will be relationship among entities. The relationship between entities may be one-to-one, one-to-many or many-to-many. If you take entities student, batch and subject, the following are the possible

relationships.

There is one-to-one relationship between batch and subject.  One batch is associated with only one subject. Three is one-to-many relationship between batch and student entities. One batch may contain many students. There is many-to-many relationship between student and subject entities. A single student may take many subjects and a single subject may be taken by multiple students.

Data Models

Data model is a way of storing and retrieving the data.  There are three different data models. Data models differ in the way they allow users to view and manipulate relationships between entities. Each has its own way of storing the data. The following are the three different data models:

 Hierarchical

 Network

 Relational

Hierarchical

In this model, data is stored in the form of a tree.  The data is represented by parent child relationship.  Each tree contains a single root record and one or more subordinate records. For example, each batch is root and students of the batch will be subordinates.

This model supports only one-to-many relationship between entities.

This was used in IBM’s Information management system, IMS.                                       

Network

Data is stored along with pointers, which specify the relationship between entities.

This was used in Honeywell's Integrated Data Store, IDS.

This model is complex. It is difficult to understand both the way data is stored and the way data is manipulated.  It is capable of supporting many-to-many relationship between entities, which hierarchical model doesn’t.

Relational

This stores data in the form of a table. Table is a collection of rows and columns.  

   2.    Explain the various functions of RDBMS? Name the different categories of RDBMS users and how they are different from each other.

Ans- There are several functions that a DBMS performs to ensure data integrity and consistency of data in the database. The ten functions in the DBMS are: data dictionary management, data storage management, data transformation and presentation, security management, multiuser access control, backup and recovery management, data integrity management, database access languages and application programming interfaces, database communication interfaces, and transaction management.

1.Data Dictionary Management:

Data Dictionary is where the DBMS stores definitions of the data elements and their relationships (metadata).  The DBMS uses this function to look up the required data component structures and relationships. When programs access data in a database they are basically going through the DBMS. This function removes structural and data dependency and provides the user with data abstraction. In turn, this makes things a lot easier on the end user. The Data Dictionary is often hidden from the user and is used by Database Administrators and Programmers.
2.Data Storage Management:

                This particular function is used for the storage of data and any related data entry forms or screen definitions, report definitions, data validation rules, procedural code, and structures that can handle video and picture formats. Users do not need to know how data is stored or manipulated. Also involved with this structure is a term called performance tuning that relates to a database’s efficiency in relation to storage and access speed.

3.Data Transformation and Presentation:
This function exists to transform any data entered into required data structures. By using the data transformation and presentation function the DBMS can determine the difference between logical and physical data formats.

4.       Security Management:

 This is one of the most important functions in the DBMS. Security management sets rules that determine specific users that are allowed to access the database. Users are given a username and password or sometimes through biometric authentication (such as a fingerprint or retina scan) but these types of authentication tend to be more costly. This function also sets restraints on what specific data any user can see or manage.

5.Multiuser Access Control:

   Data integrity and data consistency are the basis of this function. Multiuser access control is a very useful tool in a DBMS, it enables multiple users to access the database simultaneously without affecting the integrity of the database.

6.Backup and Recovery Management:

Backup and recovery is brought to mind whenever there is potential outside threats to a database. For example if there is a power outage, recovery management is how long it takes to recover the database after the outage. Backup management refers to the data safety and integrity; for example backing up all your mp3 files on a disk.

7.Data Integrity Management:

 The DBMS enforces these rules to reduce things such as data redundancy, which is when data is stored in more than one place unnecessarily, and maximizing data consistency, making sure database is returning correct/same answer each time for same question asked.

8.Database Access Languages and Application Programming Interfaces:

  A query language is a nonprocedural language. An example of this is SQL (structured query language). SQL is the most common query language supported by the majority of DBMS vendors. The use of this language makes it easy for user to specify what they want done without the headache of explaining how to specifically do it.

9.Database Communication Interfaces:  

 This refers to how a DBMS can accept different end user requests through different network environments. An example of this can be easily related to the internet.  A DBMS can provide access to the database using the Internet through Web Browsers (Mozilla Firefox, Internet Explorer, Netscape).
10.Transaction Management: 

 This refers to how a DBMS must supply a method that will guarantee that all the updates in a given transaction are made or not made.All transactions must follow what is called the ACID properties.

A – Atomicity: states a transaction is an indivisible unit that is either performed as a whole and not by its parts, or not performed at all.It is the responsibility of recovery management to make sure this takes place.

C – Consistency:A transaction must alter the database from one constant state to another constant state. I – Isolation:Transactions must be executed independently of one another.Part of a transaction in progress should not be able to be seen by another transaction. D – Durability:A successfully completed transaction is recorded permanently in the database and must not be lost due to failures.

 Alternate key - An alternate key is any candidate key which is not selected to be the primary key
* Candidate key - A candidate key is a field or combination of fields that can act as a primary key field for that table to uniquely identify each record in that table.
For Eg:
The table:
Emloyee(Name,Address,Ssn,Employee_Idprimary_key,Phone_ext)
In the above example Ssn no. and employee identity are ccandidate keys.

* Compound key - compound key (also called a composite key or concatenated key) is a key that consists of 2 or more attributes.
* Primary key - a primary key is a value that can be used to identify a unique row in a table. Attributes are associated with it. Examples of primary keys are Social Security numbers (associated to a specific person) or ISBNs (associated to a specific book).
In the relational model of data, a primary key is a candidate key chosen as the main method of uniquely identifying a tuple in a relation.
For Eg:
Emloyee(Name,Address,Ssn,Employee_Idprimary_key,Phone_ext)

* Superkey - A superkey is defined in the relational model as a set of attributes of a relation variable (relvar) for which it holds that in all relations assigned to that variable there are no two distinct tuples (rows) that have the same values for the attributes in this set. Equivalently a superkey can also be defined as a set of attributes of a relvar upon which all attributes of the relvar are functionally dependent.
For Eg:
Emloyee(Name,Address,Ssn,Employee_Idprimary_key,Phone_ext)



All the above are super keys.

* Foreign key - a foreign key (FK) is a field or group of fields in a database record that points to a key field or group of fields forming a key of another database record in some (usually different) table. Usually a foreign key in one table refers to the primary key (PK) of another table. This way references can be made to link information together and it is an essential part of database normalization.
For Eg:
For a Student....
School(Name,Address,Phone,School_Reg_noprimary_key

   (b) RDBMS has become most popular Database System world wide? Justify your answer with suitable example.

 3.   Explain different integrity constrains supported by RDBMS and ORDBMS with suitable examples

 Ans- 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-

i.  Domain Constraints

ii. Key Constraints

iii.Foreign Key Constraints

iv. 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- Table Constraints – Table constraints are associated with a single table and checked whenever the table is modified.
- Assertions – Assertions involve several tables and are checked whenever any of these tables is modified.


DBMS stands for Database Management System which is a general term for a set of software dedicated to controlling the storage of data.
RDMBS stand for Relational DataBase Management System.

DBMS includes the theritical part that how datas are stored in a table.It does not relates tables with another. While RDBMS is the procedural way that includes SQL syntaxes for relating tables with another and handling datas stored in tables. 

In DBMS all the tables are treated as different entities. There is no relation established among these entities.

ORDBMS = ODBMS + RDBMS = (O + R) * DB * MS. On a logical level, an ORDBMS is the MS process methods applied for DB data structure, which is complied with O object and R relational concepts. 


 4. Write SQL statements to create a table from existing table. What are the advantages of creating a table from existing table?

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names )
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

I need to change the primary key of a table to an identity column, and there's already a number of rows in table.

I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine on my test database.

 5. What do you mean by ‘view’? What are its different types? Differentiate between updatable and non updatable ‘view’ with suitable examples. Also give proper syntax for creating ‘view’.

Ans- A View is a "Virtual Table". It is not like a simple table, but is a virtual table which contains columns and data from different tables (may be one or more tables). A View does not contain any data, it is a set of queries that are applied to one or more tables that is stored within the database as an object. After creating a view from some table(s), it used as a reference of those tables and when executed, it shows only those data which are already mentioned in the query during the creation of the View.
Different types of Views

There are two different types of Views:

    System Views
        Information Schema View
        Catalog View
        Dynamic Management View (DMV)
    User Defined Views
        Simple View
        Complex View

Now we will take a look at the different types of Views in SQL Server 2005.
System Views

In SQL Server, there are a few system databases like Master, Temp, msdb, and tempdb. Each and every database has its own responsibility, like Master data is one of the template databases for all the databases which are created in SQL Server 2005. Similarly, System Views are predefined Microsoft created Views that already exist in the Master database. These are also used as template Views for all new databases. These system Views will be automatically inserted into any user created database. There are around 230 system Views available.
Information View

These are one of the most important system grouped Views. There are twenty different schema Views in this group. These are used for displaying most physical information of a database, such as table and columns. The naming convention of this type of Views is INFORMATION_SCHEMA.[View Name]. From the system View image, we can get the names of a few Information Schema Views.
Catalog View

These types of Views were introduced in SQL Server 2005. Catalog Views are categorized in to different groups also. These are used to show database self describing information.

For example:

select * from sys.tables

The following notes apply to updatable views:

An updatable view is one you can use to insert, update, or delete base table rows. You can create a view to be inherently updatable, or you can create an INSTEAD OF trigger on any view to make it updatable.

To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view. The information displayed by this view is meaningful only for inherently updatable views. For a view to be inherently updatable, the following conditions must be met:

    Each column in the view must map to a column of a single table. For example, if a view column maps to the output of a TABLE clause (an unnested collection), then the view is not inherently updatable.

    The view must not contain any of the following constructs:

A set operator

A DISTINCT operator

An aggregate or analytic function

A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause

A collection expression in a SELECT list

A subquery in a SELECT list

A subquery designated WITH READ ONLY

Joins, with some exceptions,

CREATE VIEW emp_view AS

   SELECT last_name, salary*12 annual_salary

   FROM employees

   WHERE department_id = 20;

    Explain the difference between the Drop & Delete, Commit & Rollback and Alter & Update with suitable examples.

Ans- The DELETE Statement is used to delete rows from a table.

The Syntax of a SQL DELETE statement is:

DELETE FROM table_name [WHERE condition];

·         table_name -- the table name which has to be updated.

NOTE:The WHERE clause in the sql delete command is optional and it identifies the rows in the column that gets deleted. If you do not include the WHERE clause all the rows in the table is deleted, so be careful while writing a DELETE query without WHERE clause.

For Example: To delete an employee with id 100 from the employee table, the sql delete query would be like,

DELETE FROM employee WHERE id = 100;

To delete all the rows from the employee table, the query would be like,

DELETE FROM employee;

The SQL DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back, so be careful while using RENAME command. When a table is dropped all the references to the table will not be valid.

Syntax to drop a sql table structure:

DROP TABLE table_name;

For Example: To drop the table employee, the query would be like

DROP TABLE employee;

COMMIT command makes the transaction permanent.

ROLLBACK command undo the transaction.

in sql commit is used for saving the changes made in database and roll back is to roll back them , changes will not get saved in the database

Update and Alter are two SQL (Structured Query Language) commands used for modifying databases. Update statement is used to update existing records in a database. Update is a Data Manipulation Language (DML) statement. Alter SQL command is used to modify, delete or add a column to an existing table in a database. Alter is a Data Definition Language (DDL) statement. Commands that are used to define the structure of a database (database schema) are called DDL statements.

Update is a SQL command that is used to update existing records in a database, while alter is a SQL command that is used to modify, delete or add a column to an existing table in a database.

Update is a DML statement whereas alter is a DDL statement. Alter command modifies the database schema, while update statement only modifies records in a database without modifying its structure