Solved question paper for DBMS May-2019 (BBA 4th)

Question paper 1

1. SECTION-A

   (Attempt any FOUR questions)

   1. Explain three Tier architecture of DBMS.

   Answer:

   The 3 tier architecture are:-

   1. The simplest of database architecture are 1tier where the client server and database all reside on the same machine. Any time you install ADB in your system and assess it to practices. SQL requires it is one tier architecture but Such architecture is rarely used in production.

   2. Are two tier architecture is a database architecture where

   1. Presentation layer run on a client (PC mobile tablet etc).

   2. Data is stored on a server.

   An application interface which is called ODBC open database connectivity E and API which allow the client by the program to call the DBMS. This provides add security to the DBMS as it not exposed to the end user directly.

   3.  

   1. Presentation layers your (PC, Tablet, mobile etc).

   2. Application layer (server).

   3. Database server.

   This database act architecture contains an application layer between the user and the DBMS which is responsible for communicating the users request to the DBMS system and send the response from the DBMS to the user.

   This application layer (business logic layer) also process of functional logical consistent and rule before passing data to the user or down to the DBMS.

   Three tier architecture is the most popular DBMS architecture.

   The goal of three tier architecture is:-

   • To separate the user application and physical database.

   • Proposed to support DBMS characteristics

   • Program data independency.

   • Support of multiple views of the data.

2. 2. What are the differences between DDL and DML commands?

   Answer:

   Data definition language (DDL) statement are used to define the database structure or schema.

   • Create- to create object in the database.

   • Alter- alter the structure of database.

   • Drop- delete object from the database.

   • Truncate- remove all records from a Table including all spaces allocated.

   • Comment- add comments to the data dictionary.

   • Rename- rename an object.

   Data manipulation language (DML) statement are used for managing data within schema objects.

   • Select- retrieve data from the database.

   • Insert- insert data into a table.

   • Update- update existing data within a table.

   • Delete- delete all records from a Table the space for the records remaining.

   • Merge-upsert operation( insert or update)

   • Call- call a PL/SQL or java subprogram

   • Explain plan:- explain access path to a data.

   • Lock table:- control concurrency

3. 3. Explain basic operations of Relational Calculus

   Answer:

   Relational calculus is a non-Procedum query language. In the non procedural query language the user is concerned with the detail of how to obtain the end results.

   The relational calculus tells what to do but never explain how to do.

   Type of relational calculus:-

   1. Tuple relational calculus:- That you pal relational calculus is a specified to the selected tuples in a relation. Filtering variable users that people of a relation.

   2. Domain and relational calculus (DRC):- the Second form of a relation is known as a relational calculus. That in the main relation calculus of filtering variable using the domain of attributes.

   Domain relational calculus uses the simple operator as Pupil calculus it uses logical connectivity A and V (or) and 7 (not).

4. 4. Explain difference between Security and Integrity.

   Answer:

   Security	Integrity
   Data security define the prevention of data corruption through the uses of controlled assess mechanism.	Data integrity defines the quality of data which guarantees the data is completed and has whole structure.
   Data security deals with the protection of data.	Data integrated deals with the validity of data.
   Data security is making sure only the people who should have a access to the data are the only ones who can assess the data.	Data integrity is making sure the data is correct and not corrupt.

6. 5. Explain the use of Grant and Revoke command with syntax and example.

   Answer:

   In DCL we have two commands

   • Grant:- used to provide any user across privileges or other privileges for the database.

   • Revoke:- used to take back permissions from any user.

   1. Allow a user to create a session

   Following commands can be used to grant the session creating privileges.

   1. Allow user to create table

   2. Provide user with space on table space to store table.

   After user (username quota unlimited)

   The above command will after the user detail and will provide it assessor to unlimited tablespace on system.

7. 6. Explain features of RDBMS.

   Answer:

   RDBMS stand for relational database management system for stop it organise a data into related rows and columns.

   Features

   • Provide data to be stored in table.

   • Persists data in the form of rows and column.

   • Provides facility primary key to uniquely identify the rows.

   • Create indexes for quicker data relative.

   • Provides a virtual Table creation in which Sanskrit data can be stored and simplified query can be applied.

   • Sharing a common column in two or more table primary key and foreign key

   • Provides multi-user accessibility that can control by individual users.

8. SECTION-B

   (Attempt any TWO questions)

   7. (a) Difference between Database and File Oriented System.

   Answer:

   Difference between database and file oriented system.

   	File system	Database
   1.	Software that manage the data files in a computer system.	Software to create and manage database.
   2.	Helps to store a collection of raw data file into the hard disk.	Helps to easily store retrieve and manipulate data in a database
   3.	Tasks such as storing retrieving and searching a done manually so it is difficult to manage data.	Operation such as updating searching selecting data is easier since it allows using SQL Query.
   4.	There is a more redundant data.	There is a low data.
   5.	Has a data inconsistency.	Provide higher data consistency using normalization
   6.	Provides more security to data.	Comparatively less data security
   7.	Backup and recovery process is not efficient because it is not possible to Re corner the lost data.	Has a sophistical backup and recovery
   8.	Ex- NTFS and Ext	Ex-My SQL, MSSQL, Oracle, DB2

9. (b) What are the various advantages and disadvantages of DBMS ?

   Answer:

   Advantages and disadvantages

   Advantages:-

   1. Improved data sharing

   • In DBMS helps to create an environment in which end users have better access to more and better managed data.

   • Such assess makes it possible for end users to respond quickly to changes in their environment.

   2. Improved data security

   • The more users assess the data the greater the risk of data security breaches Corporation invest considerable amount of time effort and money to ensure that corporate data are used properly.

   Disadvantages:-

   1. Increased cost

   • Database system require sophisticated hardware and software and highly skilled personnel.

   2. Management complexity

   • Database system interfaces with many different Technologies and have no significant impact on a company's resources and culture.

   3. Maintaining currency

   • Maximize the efficiency of the database system you must keep your system current.

   • Because database Technology advanced rapidly personal training cost tend to be significant Of vendor dependency.

    

11. 8. (a) Explain Client Server Architecture in detail.

    Answer:

    Client server architecture is a computer model in which the server hosted Delivers and merge most of the resources and services to be consumed by the client this type of architecture has one or more client computer connected to a central sir service over a network on internet resources. Client server is also known as a networking computer model or client server network because all the request is and services are delivered over a network.

    CSA is an architecture of a computer network in which many client (remote processor) request and receive service from the centralized server (host computer). Client computer provide an interface to allow computer used to request a service for the server and to display the result the server Returns. Server wait for request to arrive from client then respond to them a. Idli a server provide a standard as a transparent interface to to client so that client need to be aware of the specified of the system (i.e. hardware and software that is providing the service.

12. (b) What are the various operations that can be done on Relational Algebra ?

    Answer:

    Every database management system must define a query language to allow user to assess the data stored in the database relation algebra is a a producer query language used to query the database table to assess the data and different ways.

    In a relational algebra input a relational (table from which data has to be assessed) and output is also a realization( a temporary table holding the data are called for by the user).

    The primary operations are:-

    1. Select:- This is used to rows (tuples) from table (relation) which satisfy a given condition one can use unary and binary operators like =,< ,>. ,etc to specify the condition.

    2. Project:- project operation is used to project on the certain set of attributes of a relation for stop in simple word if you want to see only the name of all the student in the student table then you can a project operation.

    Syntax:-A₁,A₂

    Where is A₁,A₂ etc are attribute name (column names)

    3. Union

    4. Set different

    5. Cartesian product

    6. Rename

13. 9. Explain Hierarchical, Network and Relational model in detail

    Answer:

    (a) Hierarchical model

    This model presented data to users in a hierarchical of data element that can be represented in a sort of investor tree. In a sales order processing system of customer may have many invoices raised to him and each invoice may have different data elements. Does the root level of data is customer and second level is invoice and the last level is line items such as invoice number date product and quality etc.

    The structure is quite natural when seen from the event point of view. However the lower level are owned by higher level data element and elements at the same level have no linkage to all. As a result the query such as what product are produced by when customer in the above example shall be difficult to carry out the hierarchical structure.

    (b) Network model

    In the work model of database there are no level and recover can have any number of on as also can have ownership of service records. T h u s the problem raised above in the sales order processing will not arise in the network model.

    As if there is not definite path defined for iterative of data the number of link is very large and the network data are complex slow and difficult in implementation network model is used only when all other option are closed.

    The typical example of a network database may be employees and the department he she has worked or can work with in feature.

    (c) Relational model

    The most recent and popular model of database design is the relational database model first of this model was developed to overcome the problem of complexity and in flexibility of the earlier to model in handling database with many to many relationship between entities.

    This model are not only simple but also powerful in the relational database each file is received as a flat file a two dimension table. Consistently of many line record each record having ki and non ki deta atoms. The key item(s) is the data element that identifies the record.

    Customer file customer ID name address contact amount due

    Invoice file invoice no customer id date payment terms delivery item

    Find item file invoice no serial no product code quantity

    Product file product code description unit of measurement price per unit stock on head

    
     

14. 10. (a) Explain difference between Relational Calculus and Relational Algebra.

    Answer:

    Difference between relational algebra and relational calculus.

    	Relational algebra	Relational calculus
    1.	It is a Procedural language	While relational calculus is declarative language
    2.	Relational algebra means how to obtain the result	While RC means what result we have to obtain
    3.	This order is a specified in which the operation have to be performed	The order is not specified
    4.	Independents on domain	While RC can be a domain depend
    5.	RA is nearer to the programming language	While RC is not near to programming language

16. (b) What are the basic operations of Relational Calculus ?

    Answer:

    Relational calculus

    It is a formal query language. It also known as declarative language. In relational calculus the order is not specified in which the operation have to be performed.

    Relational calculus has two basic operation:-

    1. Tuple relational calculus (TRC)

    In this work on filtering tuples based on the given conditions.

    Syntax:{T/condition }

    In this form of a relational calculus we define a type variable specified the table name in which the people is to be search for along with a condition. We can also specified common name using. {.} Dot operator with the type of variable to only get a certain attribute in a result.

    2. Domain relational calculus (DRC)

    In this filtering is done based on domain of the attribute and not based on the tuple values

    Syntax:-={C₁,C₂,C₃………….C₂/F C₁,C₂/C₃}

    Where C₁,C₂, ………….etc represent domain of attributes column and F defines the formula including the condition for the switching off the data.

    For example:-

    {name, age>/E student A Age > 17 }

    Again the above query will return the names and age of the students in the table student who are older than 17.

17. SECTION-C

    (Attempt any TWO questions)

    11. What is Normalization ? Explain first second and third normal form in detail.

    Answer:

    Database normalization is the process of structuring a relational database in accordance with a series of so called normal form in order to reduce data redundancy and improve Data integrity. It was first proposed by Edgar part of his relational model.

    Another word it is a database design technique that organize a table in a manner that reduce data for stop normalization divides a large table into small table and link them using a relationship the purpose normalization is to eliminate redundant davit data and ensure data is stored logically.

    Database normal form:-

    The theory of Tata normalization in SQL is still being developed further. For example there are discussion even on 6th Normal Form. However in most practical application normalisation achieve its best in third Normal Form. The evaluation of normalisation theories is illustrated below

    1st Normal Form >2nd Normal Form >3rd normal form > Boyce Codd NF> 4th normal form > 5th Normal Form> 6th normal form

    1st First Normal Form

    Rules:-

    • Each table shall should contain single value.

    • Each record need to be unique.

    The above table in 1NF:-

    Examples:-

    Full name	physical address	movie ranted	Salutation
    Janet jones	First Street Plot Number 4	Pirates of the Caribbean	Ms.
    Janet jones	Plot Number 4	Clash of the titans	Ms.
    Robert phil	3rd street 34	Forgetting search marshal	Mr.

     

    2NF (second normal form)

    Rules:-

    Rule 1 :- be in First Normal Form

    Rule 2 :- single column primary key.

    It is clear that we can't move forward to make our sample database Second normalisation form in unless we partition the table above.

    Membership ID	Full name	physical address	Salutation
    1.	Janet jones	First Street Plot Number 4	Ms.
    2.	Janet jones	Plot Number 4	Ms.
    3.	Robert phil	3rd street 34	Mr.

    
     

    3NF third normal form

    Rule 1:- be in a second normal form

    Rule 2:- has no transitive functional dependency

    Table 1

    Table2

    Table 3

    There are not transitive functional dependency and hence our table is in 3NF.

18. 12. (a) What are the various threats to the database in an organisation ?

    Answer:

    Various threats to database are

    1. Executive privileges:- When worker are granted default database privileges that exceed the requirement of their job function these privileges can be used sad “for example a bank employee whose job require ability to change on the account holder contact information may not take advantages of excessive database privileges.

    2. Ligament privilege abuse:- user Mein abuse ligament database privilege for unauthorized purpose.

    3. Malware:- A perennial threat Malware is used to steal sensitive data via ligament user using inflected virus.

    4. Storage media exposure:- Backup storage media is often completely and protector from attack. As a result numerous security branches have involved the theft of database backup disks and tapes.

    5. The human factor:- The root cause for 30% of data breaches incidence in human negligence it according to phenomena Institute cost of data breach study “oftrn this is due to the lack of expertise required to implement security controls in force policies or conduct incident response process” Gerhardt said.

19. (b) Discuss Database Failures and Recovery techniques in detail.

    Answer:

    Database techniques are:-

    There are both automatic and non automatic ways of both backing up of data and recovery form any failure situation first of the technique used to recover the lost data due to the system crash transaction error of viruses incorrect commands execution etc are database recovery techniques. So to prevent data loss recovery technique based on deferred update and immediate update for backing up data can be used.

    Failures are:-

    • Application software errors

    • Network failure

    • Media failure

    • Natural physical disasters

    In case of system crash the system hang up and need to be rebooted first of these failure occurred due to hardware malfunction for a bug in the database software or the operating system itself. It causes the loss of content of storage and bring transaction processing to a a heart full stop the content of non-volatile storage does not affected with the desert type of failure. The assumption that Hardware errors and bug bring the system ta a half but do not corrupt the non-volatile storage contents is known as the fail-stop assumptions.

21. 13. (a) What is Database Integrity ? Explain various integrity rules.

    Answer:

    Data integrity is normally in forced in a database system by a series of integrity consistent or rules. Entity integrity is an integrity rule which state that every table must have a primary key and that column chosen to be the primary key should be unique and not null.

    1. Domain integrity

    Domain restricts the value of attribute in the relational and is a consistent of the relational model. However there are real word semantics for data that cannot be specified if used on the with the domain Consultant for stock we need more specific weight to state what a data value are aur not allowed and which format is suitable for an attribute. For example the employees ID must be unique for the employee birthdates is in the range {Jan 1st 1950}. Such information is provided in logical statement called integrity consistent.

    2. Entity integrity

    To ensure your entity integrity it is required that every table have a primary key. Neither the pk nor any part of it can contain null values this is because null value for the primary key means we cannot identify some rows full stop for example in the EMPLOYEE table phone cannot be a primary key sensor some people may not have a telephone.

22. (b) Discuss various data types that are used in Oracle with example.

    Answer:

    Introduction of Oracle data type:-

    Each column value and consistent in a SQL statement has a data type which is associated with a specific storage format consistent and a valid range of value for stop when you create a table you must specify a data type for each of it column.

    Char data type

    The CHAR data type store fixed length characters string. When you create a table with a CHAR column you must specify a string length in white or characters between 1 and 2000 bytes for the CHAR column width. The default is One byte Oracle then guarantees that

    when you insert or update a row in the table the value for the c h a r column has the fixed length.

    VARCHAR 2 and VARCHAR data type:-

    The VARCHAR 2 data type store variable length character strings. When you create a table with a VARCHAR 2 column you specify a maximum string length in a bite or tractor between 1 and 400 bytes for the VARCHAR 2 column. For each row Oracle database store each value in the column as a variable length field unless a value exceed two columns maximum length in which case Oracle database written and oral. Using VARCHAR 2 and VARCHAR saves on space used by table.