image

Solution of Assignment Synopsis & Project Dissertation Report


PRODUCT DETAILS

Online-Typing-and-Filling

Title Name Amity Solved Assignment Introduction to Data Base Management System
University AMITY
Service Type Assignment
Course B.Sc-(IT)
Semister Semester-II Cource: B.Sc-(IT)
Short Name or Subject Code Introduction to Data Base Management System
Commerce line item Type Semester-II Cource: B.Sc-(IT)
Product Assignment of B.Sc-(IT) Semester-II (AMITY)

Solved Assignment


  Questions :-

                                                                                                                                                        Introduction to Data Base Management Systems

 

Assignment A

1 a) Consider the following Relations with their FD’s.

b) Explain the following:

  1. a) What is a Transaction? Explain the ACID Properties of Transaction.

b) Explain two phase locking protocol Solve by www.solvezone.in

  1. Seralizability is used to enforce concurrency control in a database system. Justify this statement with suitable example.

 

  1. Explain architecture of DBMS with suitable diagram. How the architecture of DBMS helps in achieving data independence

 

  1. Differentiate between following with suitable example:

a) Primary Key & Unique Key

b) Candidate Key & Alternate Key

c) Binary Locking & Two Phase Locking

  1. Define relational algebra. Explain the different operations used in relational algebra with suitable examples.
  1. How DBMS is different from traditional file system. Explain with suitable example to justify your answer.

 

  1. Define Entity Relationship Model (ERD)? Draw an ERD for an Inventory Management System. Mention all your assumptions used to draw an ERD.

 

 

 

 

Assignment B

  1. Seralizability is used to enforce concurrency control in a database system. Justify this statement with suitable example.
  1. a) What is a Transaction? Explain the ACID Properties of Transaction.

 

 b) Explain two phase locking protocol.

 

 

 

 

Assignment C

Question No: 1

Overall logical structure of a database can be expressed graphically by

  1. ER diagram
  2. Records
  3. Relations
  4. Hierarchy

 

 

Question No: 2

A table can have how many unique key

  1. 1
  2. any number
  3. 255
  4. None of the above

 

 

Question No: 3

Entity is represented by the symbol.

  1. Double Circle
  2. Ellipse
  3. Rectangle
  4. Square

 

 

Question No: 4

Select the correct statement

  1. ALTER statement is used to modify the structure of Database.
  2. PL/SQL improves the capacity of SQL
  3. SELECT statement is used to retrieve the data from the database
  4. All of the above

 

 

Question No: 5

Which of the following are characteristics of an RDBMS?

  1. Data are organized in a series of two-dimensional tables each of which contains records for one entity.
  2. Queries are possible on individual or groups of tables.
  3. It cannot use SQL.
  4. Tables are linked by common data known as keys

 

 

Question No: 6

Normalization is step by step process of decomposing:

  1. Table
  2. Database
  3. Group Data item
  4. All of the above

 

 

Question No: 7

Which of the following statements contains an error?

  1. Select * from EMP where EMPID = 493945;
  2. Select EMPID from EMP where EMPID = 493945;
  3. Select EMPID from EMP;
  4. Select EMPID where EMPID = 56949 and LASTNAME = ’SMITH’;

 

 

Question No: 8

Which of the following statements are NOT TRUE about ORDER BY clauses?

  1. Ascending or descending order can be defined with the asc or desc keywords.
  2. Only one column can be used to define the sort order in an order by clause.
  3. Multiple columns can be used to define sort order in an order by clause.
  4. Columns can be represented by numbers indicating their listed order in the select

 

 

Question No: 9

The keys that can have NULL values are

  1. Primary Key
  2. Unique Key
  3. Foreign Key
  4. Both b and c

 

 

Question No: 10

The number of columns in a table is known as its

  1. Degree
  2. Cardinality
  3. Domain
  4. Range ANS-(a)

 

 

Question No: 11

Rollback and Commit affect

  1. Only DML statements
  2. Only DDL statements
  3. Both (a) and (b)
  4. All statements executed in SQL*PLUS

 

 

Question No: 12

GRANT and REVOKE are

  1. DDL statements
  2. DML statements
  3. DCL statements
  4. None of these.

 

 

Question No: 13

Oracle 8i can be best described as

  1. Object-based DBMS
  2. Object-oriented DBMS
  3. Object-relational DBMS
  4. Relational DBMS

 

 

Question No: 14

Rows of a relation are called

  1. Tuples
  2. A relation row
  3. A data structure
  4. An entity

 

 

Question No: 15

INITCAP (‘oracle’) returns

  1. oracle
  2. ORACLE
  3. ORACLe
  4. None of these

 

 

Question No: 16

TRUNCATE TABLE statement

  1. Drops a table temporarily
  2. Removes all rows of a table
  3. Removes specified number of rows from a table
  4. Removes all constraints from a table

 

 

Question No: 17

Given an attribute x, another attribute y is dependent on it, if for a given x

  1. There are many y values
  2. There is only one value of y
  3. There is one or more y values
  4. There is none or one y value

 

 

Question No: 18

The name of the only column in the DUAL table is

  1. X
  2. C
  3. DUMMY
  4. None of this.

 

 

Question No: 19

A relation is said to be in 2 NF if

(i)    it is in 1 NF

(ii)     non-key attributes dependent on key attribute

(iii)     non-key attributes are independent of one another

(iv)     if it has a composite key, no non-key attribute should be dependent on

          part of the composite key.

  1. i, ii, iii
  2. i and ii
  3. i, ii, iv
  4. i, iv

 

 

Question No: 20

A relation is said to be in BCNF when

  1. it has overlapping composite keys
  2. it has no composite keys
  3. it has no multivalued dependencies
  4. it has no overlapping composite keys which have related attributes

 

 

Question No: 21

Transaction is defined as

  1. Single logical unit of work
  2. Adding a new column into the table.
  3. Adding new constraints into the table..
  4. Degree of Relation

 

 

Question No: 22

Select the correct statement.

  1. View has no physical existence
  2. Data from the view are retrieved through the Table.
  3. Both (a) and (b)
  4. None of these.

 

 

 

Question No: 23

A relation is said to be in 3 NF if

(i)    it is in 2 NF

(ii)     non-key attributes are independent of one another

(iii)     key attribute is not dependent on part of a composite key

(iv)     has no multi-valued dependency

  1. i and iii
  2. i and iv
  3. i and ii
  4. ii and iv

 

 

Question No: 24

Fourth normal form (4 NF) relations are needed when.

  1. There are multivalued dependencies between attributes in composite key
  2. There are more than one composite key
  3. There are two or more overlapping composite keys
  4. There are multivalued dependency between non-key attributes

 

Question No: 25

Data integrity in a file based system may be lost because

  1. the same variable may have different values in different files
  2. files are duplicated
  3. unnecessary data is stored in files
  4. redundant data is stored in files

 

 

Question No: 26

Data availability is often difficult in file based system

  1. As files are duplicated
  2. As unnecessary data are stored in files
  3. As one has to search different files and these files may be in different e states
  4. Redundant data are stored in files

 

 

Question No: 27

A good database design

(i)    Is expandable with growth and changes in organization

(ii)     Easy to change when software changes

(iii)     Ensures data integrity

(iv)     Allows access to only authorized users

  1. i, ii
  2. ii, iii
  3. i, ii, iii, iv
  4. i, ii, iii

 

 

Question No: 28

Access right to a database is controlled by

  1. Top management
  2. System designer
  3. System analyst
  4. Database administrator

 

 

Question No: 29

When a logical model is mapped into a physical storage such as a disk store the resultant data model is known as

  1. Conceptual data model
  2. External data model
  3. Internal data model
  4. Disk data model

 

 

Question No: 30

Designing physical model of DBMS requires information on

(i)    Data volume

(ii)     Frequency of access to data

(iii)     Programming language used

(iv)     Secondary memory characteristics

  1. i, ii
  2. i, ii, iii
  3. i, ii, iii, iv
  4. i, ii, iv

 

 

Question No: 31

By data security in DBMS we mean

  1. Preventing access to data
  2. Allowing access to data only to authorized users
  3. Preventing changing data
  4. Introducing integrity constraints

 

 

Question No: 32

Data independence allows

  1. Sharing the same database by several applications
  2. Extensive modification of applications
  3. No data sharing between applications
  4. Elimination of several application programs

 

 

Question No: 33

A database should be designed to allow providing

  1. Different views of portions of data requested by an application
  2. Data only to selected applications as decided by an organization
  3. A uniform view of data to all applications
  4. Data to all applications

 

 

Question No: 34

By data redundancy in a file based system we mean that

  1. Unnecessary data is stored
  2. Same data is duplicated in many files
  3. Data is unavailable
  4. Files have redundant data

 

 

Question No: 35

Entities are identified from the word statement of a problem by

  1. Picking words which are adjectives
  2. Picking words which are nouns
  3. Picking words which are verbs
  4. Picking words which are pronouns

 

 

Question No: 36

Number of entity set involved in the relationship is called.

  1. Prime Entity
  2. Degree of Relation.
  3. Cardinality of relation
  4. 1: M relation

 

 

Question No: 37

Two pseudo columns present in every Oracle table are .

  1. ROWID & ROWNUM
  2. ROWID & COLUMNID
  3. ROWNUM & COLUMNNUM
  4. None of the above

 

 

Question No: 38

A relationship is

  1. An item in an application
  2. A meaningful dependency between entities
  3. A collection of related entities
  4. Related data

 

 

Question No: 39

Attributes are

  1. Properties of relationship
  2. Attributed to entities
  • Properties of members of an entity set
  1. i
  2. i and ii
  3. i and iii
  4. iii

 

 

Question No: 40

Pick entities from the following:

(i)    vendor

(ii)     student

(iii)     attends

(iv)     km/hour

  1. i, ii, iii
  2. i, ii, iv
  3. i and ii
  4. iii and iv
  Answers :-

                                                                                                                                                        Introduction to Data Base Management Systems

 

Assignment A

1 a) Consider the following Relations with their FD’s.

Ans.

R1 = {A, B, C, D},   {Bà D, (A,B) à C}

R2= {A, B, C, D, E} and {(A, B) àE, (A, B) à C, Cà D} 

 

 

  1. b) Explain the following:

 

  1. i) Why R1 is in INF but not in 2NF.

Sol- Consider the set of FD: AB → CD and B → C. AB is obviously a key for this

relation since AB → CD implies AB → ABCD. It is a primary key since there are

No smaller subsets of keys that hold over R(A,B,C,D). The FD: B → C violates

2NF since:

 

C    B is false; that is, it is not a trivial FD

B is not a super key

C is not part of some key for R

B is a proper subset of the key AB (transitive dependency)

 

  1. ii) Why R1 is in 2NF but not in 3NF

Sol- Consider the set of FD: AB → CD and C → D. AB is obviously a key for this

Relation since AB → CD implies AB → ABCD. It is a primary key since there are

No smaller subsets of keys that hold over R(A,B,C,D). The FD: C → D violates

3NF but not 2NF since:

D   C is false; that is, it is not a trivial FD

C is not a super key

D is not part of some key for R

 

 

  1. a) What is a Transaction? Explain the ACID Properties of Transaction.

Ans.

A transaction can be defined as a group of tasks. A single task is the minimum processing unit which cannot be divided further.

A transaction is a very small unit of a program and it may contain several low-level tasks. A transaction in a database system must maintain Atomicity, Consistency, Isolation, and Durability − commonly known as ACID properties − in order to ensure accuracy, completeness, and data integrity.

Atomicity − this property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none. There must be no state in a database where a transaction is left partially completed. States should be defined either before the execution of the transaction or after the execution/abortion/failure of the transaction.

Consistency − the database must remain in a consistent state after any transaction. No transaction should have any adverse effect on the data residing in the database. If the database was in a consistent state before the execution of a transaction, it must remain consistent after the execution of the transaction as well.

Durability − the database should be durable enough to hold all its latest updates even if the system fails or restarts. If a transaction updates a chunk of data in a database and commits, then the database will hold the modified data. If a transaction commits but the system fails before the data could be written on to the disk, then that data will be updated once the system springs back into action.

Isolation − In a database system where more than one transaction are being executed simultaneously and in parallel, the property of isolation states that all the transactions will be carried out and executed as if it is the only transaction in the system. No transaction will affect the existence of any other transaction.

 

 

  1. b) Explain two phase locking protocol Solve by www.solvezone.in

Ans.

This locking protocol divides the execution phase of a transaction into three parts. In the first part, when the transaction starts executing, it seeks permission for the locks it requires. The second part is where the transaction acquires all the locks. As soon as the transaction releases its first lock, the third phase starts. In this phase, the transaction cannot demand any new locks; it only releases the acquired locks.

Two-phase locking has two phases, one is growing, where all the locks are being acquired by the transaction; and the second phase is shrinking, where the locks held by the transaction are being released.

To claim an exclusive (write) lock, a transaction must first acquire a shared (read) lock and then upgrade it to an exclusive lock.

 

 

  1. Seralizability is used to enforce concurrency control in a database system. Justify this statement with suitable example.

Ans.

Schedule compliance with conflict serializability can be tested with the precedence graph (serializability graph, serialization graph, conflict graph) for committed transactions of the schedule. It is the directed graph representing precedence of transactions in the schedule, as reflected by precedence of conflicting operations in the transactions.

In the precedence graph transactions are nodes and precedence relations are directed edges. There exists an edge from a first transaction to a second transaction, if the second transaction is in conflict with the first (see Conflict serializability above), and the conflict is materialized (i.e., if the requested conflicting operation is actually executed: in many cases a requested/issued conflicting operation by a transaction is delayed and even never executed, typically by a lock on the operation´s object, held by another transaction, or when writing to a transaction´s temporary private workspace and materializing, copying to the database itself, upon commit; as long as a requested/issued conflicting operation is not executed upon the database itself, the conflict is non-materialized; non-materialized conflicts are not represented by an edge in the precedence graph).

 

 

  1. Explain architecture of DBMS with suitable diagram. How the architecture of DBMS helps in achieving data independence

Ans.

The design of a DBMS depends on its architecture. It can be centralized or decentralized or hierarchical. The architecture of a DBMS can be seen as either single tier or multi-tier. An n-tier architecture divides the whole system into related but independent n modules, which can be independently modified, altered, changed, or replaced.

In 1-tier architecture, the DBMS is the only entity where the user directly sits on the DBMS and uses it. Any changes done here will directly be done on the DBMS itself. It does not provide handy tools for end-users. Database designers and programmers normally prefer to use single-tier architecture.

If the architecture of DBMS is 2-tier, then it must have an application through which the DBMS can be accessed. Programmers use 2-tier architecture where they access the DBMS by means of an application. Here the application tier is entirely independent of the database in terms of operation, design, and programming.

3-tier Architecture

A 3-tier architecture separates its tiers from each other based on the complexity of the users and how they use the data present in the database. It is the most widely used architecture to design a DBMS.

Database (Data) Tier − At this tier, the database resides along with its query processing languages. We also have the relations that define the data and their constraints at this level.

Application (Middle) Tier − At this tier reside the application server and the programs that access the database. For a user, this application tier presents an abstracted view of the database. End-users are unaware of any existence of the database beyond the application. At the other end, the database tier is not aware of any other user beyond the application tier. Hence, the application layer sits in the middle and acts as a mediator between the end-user and the database.

User (Presentation) Tier − End-users operate on this tier and they know nothing about any existence of the database beyond this layer. At this layer, multiple views of the database can be provided by the application. All views are generated by applications that reside in the application tier.

Multiple-tier database architecture is highly modifiable, as almost all its components are independent and can be changed independently.

Data independence

A database system normally contains a lot of data in addition to users’ data. For example, it stores data about data, known as metadata, to locate and retrieve data easily. It is rather difficult to modify or update a set of metadata once it is stored in the database. But as a DBMS expands, it needs to change over time to satisfy the requirements of the users. If the entire data is dependent, it would become a tedious and highly complex job.

Metadata itself follows a layered architecture, so that when we change data at one layer, it does not affect the data at another level. This data is independent but mapped to each other.

Logical Data Independence

Logical data is data about database, that is, it stores information about how data is managed inside. For example, a table (relation) stored in the database and all its constraints, applied on that relation.

Logical data independence is a kind of mechanism, which liberalizes itself from actual data stored on the disk. If we do some changes on table format, it should not change the data residing on the disk.

Physical Data Independence

All the schemas are logical, and the actual data is stored in bit format on the disk. Physical data independence is the power to change the physical data without impacting the schema or logical data.

For example, in case we want to change or upgrade the storage system itself − suppose we want to replace hard-disks with SSD − it should not have any impact on the logical data or schemas.

 

 

  1. Differentiate between following with suitable example:

Ans.

 

  1. a) Primary Key & Unique Key

PRIMARY KEY

  • Primary Key can´t accept null values.
  • By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.
  • We can have only one Primary key in a table.
  • Primary key can be made foreign key into another table.

UNIQUE KEY

  • Unique key can accept only one null value
  • By default, Unique key is a unique non-clustered index.
  • We can have more than one unique key in a table.
  • In SQL Server, Unique key can be made foreign key into another table.

 

  1. b) Candidate Key & Alternate Key

Candidate Key

Candidate keys are those keys which is candidate for primary key of a table. In simple words we can understand that such type of keys which full fill all the requirements of primary key which is not null and have unique records is a candidate for primary key. So thus type of key is known as candidate key. Every table must have at least one candidate key but at the same time can have several.

Alternate Key

If any table have more than one candidate key, then after choosing primary key from those candidate key, rest of candidate keys are known as an alternate key of  that table. Like here we can take a very simple example to understand the concept of alternate key. Suppose we have a table named Employee which has two columns EmpID and EmpMail, both have not null attributes and unique value. So both columns are treated as candidate key. Now we make EmpID as a primary key to that table then EmpMail is known as alternate key.

  1. c) Binary Locking & Two Phase Locking

BINARY LOCKING

Database systems equipped with lock-based protocols use a mechanism by which any transaction cannot read or write data until it acquires an appropriate lock on it. A lock on a data item can be in two states; it is either locked or unlocked.

TWO PHASE LOCKING

This locking protocol divides the execution phase of a transaction into three parts. In the first part, when the transaction starts executing, it seeks permission for the locks it requires. The second part is where the transaction acquires all the locks. As soon as the transaction releases its first lock, the third phase starts. In this phase, the transaction cannot demand any new locks; it only releases the acquired locks. Two-phase locking has two phases, one is growing, where all the locks are being acquired by the transaction; and the second phase is shrinking, where the locks held by the transaction are being released.

 

 

 

  1. Define relational algebra. Explain the different operations used in relational algebra with suitable examples.

Ans.

Relational algebra is a procedural query language, which takes instances of relations as input and yields instances of relations as output. It uses operators to perform queries. An operator can be either unary or binary. They accept relations as their input and yield relations as their output. Relational algebra is performed recursively on a relation and intermediate results are also considered relations.

The fundamental operations of relational algebra are as follows-

  • Select
  • Project
  • Union
  • Set different
  • Cartesian product
  • Rename

Select Operation (σ)

It selects tuples that satisfy the given predicate from a relation.

Notation − σp(r)

Where σ stands for selection predicate and r stands for relation. p is prepositional logic formula which may use connectors like and, or, and not. These terms may use relational operators like − =, ≠, ≥, < ,  >,  ≤.

 

Project Operation (∏)

It projects column(s) that satisfy a given predicate.

Notation − ∏A1, A2, An (r)

Where A1, A2 , An are attribute names of relation r.

Duplicate rows are automatically eliminated, as relation is a set.

 

Union Operation (∪)

It performs binary union between two given relations and is defined as –

r ∪ s = { t | t ∈ r or t ∈ s}

Notion − r U s

Where r and s are either database relations or relation result set (temporary relation).

For a union operation to be valid, the following conditions must hold −

r, and s must have the same number of attributes.

Attribute domains must be compatible.

Duplicate tuples are automatically eliminated.

∏ author (Books) ∪ ∏ author (Articles)

 

Set Difference (−)

The result of set difference operation is tuples, which are present in one relation but are not in the second relation.

Notation − r − s

Finds all the tuples that are present in r but not in s.

∏ author (Books) − ∏ author (Articles)

 

Cartesian Product (Χ)

Combines information of two different relations into one.

Notation − r Χ s

Where r and s are relations and their output will be defined as –

r Χ s = { q t | q ∈ r and t ∈ s}

σauthor = ´tutorialspoint´(Books Χ Articles)

 

Relational Calculus

In contrast to Relational Algebra, Relational Calculus is a non-procedural query language, that is, it tells what to do but never explains how to do it.

Relational calculus exists in two forms −

Tuple Relational Calculus (TRC)

Filtering variable ranges over tuples

Notation − {T | Condition}

Returns all tuples T that satisfies a condition

 

 

 

  1. How DBMS is different from traditional file system. Explain with suitable example to justify your answer.

Ans.

The traditional filing system (TFS) is a method of storing and arranging computer files and the information in the file (data). Basically it organizes these files into a database for the storage, organization, manipulation, and retrieval by the computer´s operating system.

(DBMS) consists of software that operates databases, providing storage, access, security, backup and other facilities. This system can be categorized according to the database model and the type of computer that they support such as a server cluster or a mobile phone, the query language(s) that access the database, such as SQL or XQuery, performance trade-offs, such as maximum scale or maximum speed or others. Some DBMS cover more than one entry in these categories, e.g., supporting multiple query languages.

Meanwhile database management systems are usually accessed in a client-server manner, where the database client and the server are located on different machines (in a local area network).

File-based systems were an early attempt to computerize the manual filing system. File-based system is a collection of application programs that perform services for the end-users, such as updating, insertion, deletion adding new files to database etc. Each program defines and manages its data.

When a computer user wants to store data electronically they must do so by placing data in files. Files are stored in specific locations on the hard disk (directories). The user can create new files to place data in, delete a file that contains data, rename the file, etc which is known as file management; a function provided by the Operating System (OS).

 

 

  1. Define Entity Relationship Model (ERD)? Draw an ERD for an Inventory Management System. Mention all your assumptions used to draw an ERD.

Ans.

An entity–relationship model (ER model) is a data model for describing the data or information aspects of a business domain or its process requirements, in an abstract way that lends itself to ultimately being implemented in a database such as a relational database.

.

An ER diagram representing the information system for a company´s sales department might start with graphical representations of entities such as the sales representative, the customer, the customer´s address, the customer´s order, the product and the warehouse. (See diagram) Then lines or other symbols can be used to represent the relationship between entities, and text can be used to label the relationships.

 

 

 

Assignment B

  1. Seralizability is used to enforce concurrency control in a database system. Justify this statement with suitable example.

Ans.

Schedule compliance with conflict serializability can be tested with the precedence graph (serializability graph, serialization graph, conflict graph) for committed transactions of the schedule. It is the directed graph representing precedence of transactions in the schedule, as reflected by precedence of conflicting operations in the transactions.

In the precedence graph transactions are nodes and precedence relations are directed edges. There exists an edge from a first transaction to a second transaction, if the second transaction is in conflict with the first (see Conflict serializability above), and the conflict is materialized (i.e., if the requested conflicting operation is actually executed: in many cases a requested/issued conflicting operation by a transaction is delayed and even never executed, typically by a lock on the operation´s object, held by another transaction, or when writing to a transaction´s temporary private workspace and materializing, copying to the database itself, upon commit; as long as a requested/issued conflicting operation is not executed upon the database itself, the conflict is non-materialized; non-materialized conflicts are not represented by an edge in the precedence graph).

 

 

  1. a) What is a Transaction? Explain the ACID Properties of Transaction.

Ans.

A transaction can be defined as a group of tasks. A single task is the minimum processing unit which cannot be divided further.

A transaction is a very small unit of a program and it may contain several low-level tasks. A transaction in a database system must maintain Atomicity, Consistency, Isolation, and Durability − commonly known as ACID properties − in order to ensure accuracy, completeness, and data integrity.

Atomicity − this property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none. There must be no state in a database where a transaction is left partially completed. States should be defined either before the execution of the transaction or after the execution/abortion/failure of the transaction.

Consistency − the database must remain in a consistent state after any transaction. No transaction should have any adverse effect on the data residing in the database. If the database was in a consistent state before the execution of a transaction, it must remain consistent after the execution of the transaction as well.

Durability − the database should be durable enough to hold all its latest updates even if the system fails or restarts. If a transaction updates a chunk of data in a database and commits, then the database will hold the modified data. If a transaction commits but the system fails before the data could be written on to the disk, then that data will be updated once the system springs back into action.

Isolation − In a database system where more than one transaction are being executed simultaneously and in parallel, the property of isolation states that all the transactions will be carried out and executed as if it is the only transaction in the system. No transaction will affect the existence of any other transaction.

 

 

 b) Explain two phase locking protocol.

Ans.

This locking protocol divides the execution phase of a transaction into three parts. In the first part, when the transaction starts executing, it seeks permission for the locks it requires. The second part is where the transaction acquires all the locks. As soon as the transaction releases its first lock, the third phase starts. In this phase, the transaction cannot demand any new locks; it only releases the acquired locks.

Two-phase locking has two phases, one is growing, where all the locks are being acquired by the transaction; and the second phase is shrinking, where the locks held by the transaction are being released.

To claim an exclusive (write) lock, a transaction must first acquire a shared (read) lock and then upgrade it to an exclusive lock.

 

 

 

Assignment C

 

Question No: 1

Overall logical structure of a database can be expressed graphically by

  1. ER diagram
  2. Records
  3. Relations
  4. Hierarchy

 

 

 

Question No: 2

A table can have how many unique key

  1. 1
  2. any number
  3. 255
  4. None of the above

 

 

 

Question No: 3

Entity is represented by the symbol.

  1. Double Circle
  2. Ellipse
  3. Rectangle
  4. Square

 

 

 

Question No: 4

Select the correct statement

  1. ALTER statement is used to modify the structure of Database.
  2. PL/SQL improves the capacity of SQL
  3. SELECT statement is used to retrieve the data from the database
  4. All of the above

 

 

 

Question No: 5

Which of the following are characteristics of an RDBMS?

  1. Data are organized in a series of two-dimensional tables each of which contains records for one entity.
  2. Queries are possible on individual or groups of tables.
  3. It cannot use SQL.
  4. Tables are linked by common data known as keys

 

 

 

Question No: 6

Normalization is step by step process of decomposing:

  1. Table
  2. Database
  3. Group Data item
  4. All of the above

 

 

 

Question No: 7

Which of the following statements contains an error?

  1. Select * from EMP where EMPID = 493945;
  2. Select EMPID from EMP where EMPID = 493945;
  3. Select EMPID from EMP;
  4. Select EMPID where EMPID = 56949 and LASTNAME = ’SMITH’;

 

 

 

Question No: 8

Which of the following statements are NOT TRUE about ORDER BY clauses?

  1. Ascending or descending order can be defined with the asc or desc keywords.
  2. Only one column can be used to define the sort order in an order by clause.
  3. Multiple columns can be used to define sort order in an order by clause.
  4. Columns can be represented by numbers indicating their listed order in the select

 

 

 

Question No: 9

The keys that can have NULL values are

  1. Primary Key
  2. Unique Key
  3. Foreign Key
  4. Both b and c

 

 

 

Question No: 10

The number of columns in a table is known as its

  1. Degree
  2. Cardinality
  3. Domain
  4. Range ANS-(a)

 

 

 

Question No: 11

Rollback and Commit affect

  1. Only DML statements
  2. Only DDL statements
  3. Both (a) and (b)
  4. All statements executed in SQL*PLUS

 

 

 

Question No: 12

GRANT and REVOKE are

  1. DDL statements
  2. DML statements
  3. DCL statements
  4. None of these.

 

 

 

Question No: 13

Oracle 8i can be best described as

  1. Object-based DBMS
  2. Object-oriented DBMS
  3. Object-relational DBMS
  4. Relational DBMS

 

 

 

Question No: 14

Rows of a relation are called

  1. Tuples
  2. A relation row
  3. A data structure
  4. An entity

 

 

 

Question No: 15

INITCAP (‘oracle’) returns

  1. oracle
  2. ORACLE
  3. ORACLe
  4. None of these

 

 

 

Question No: 16

TRUNCATE TABLE statement

  1. Drops a table temporarily
  2. Removes all rows of a table
  3. Removes specified number of rows from a table
  4. Removes all constraints from a table

 

 

 

Question No: 17

Given an attribute x, another attribute y is dependent on it, if for a given x

  1. There are many y values
  2. There is only one value of y
  3. There is one or more y values
  4. There is none or one y value

 

 

 

Question No: 18

The name of the only column in the DUAL table is

  1. X
  2. C
  3. DUMMY
  4. None of this.

 

 

 

Question No: 19

A relation is said to be in 2 NF if

(i)    it is in 1 NF

(ii)     non-key attributes dependent on key attribute

(iii)     non-key attributes are independent of one another

(iv)     if it has a composite key, no non-key attribute should be dependent on

          part of the composite key.

  1. i, ii, iii
  2. i and ii
  3. i, ii, iv
  4. i, iv

 

 

 

Question No: 20

A relation is said to be in BCNF when

  1. it has overlapping composite keys
  2. it has no composite keys
  3. it has no multivalued dependencies
  4. it has no overlapping composite keys which have related attributes

 

 

 

Question No: 21

Transaction is defined as

  1. Single logical unit of work
  2. Adding a new column into the table.
  3. Adding new constraints into the table..
  4. Degree of Relation

 

 

 

Question No: 22

Select the correct statement.

  1. View has no physical existence
  2. Data from the view are retrieved through the Table.
  3. Both (a) and (b)
  4. None of these.

 

 

 

 

Question No: 23

A relation is said to be in 3 NF if

(i)    it is in 2 NF

(ii)     non-key attributes are independent of one another

(iii)     key attribute is not dependent on part of a composite key

(iv)     has no multi-valued dependency

  1. i and iii
  2. i and iv
  3. i and ii
  4. ii and iv

 

 

 

Question No: 24

Fourth normal form (4 NF) relations are needed when.

  1. There are multivalued dependencies between attributes in composite key
  2. There are more than one composite key
  3. There are two or more overlapping composite keys
  4. There are multivalued dependency between non-key attributes


 

Question No: 25

Data integrity in a file based system may be lost because

  1. the same variable may have different values in different files
  2. files are duplicated
  3. unnecessary data is stored in files
  4. redundant data is stored in files

 

 

 

Question No: 26

Data availability is often difficult in file based system

  1. As files are duplicated
  2. As unnecessary data are stored in files
  3. As one has to search different files and these files may be in different e states
  4. Redundant data are stored in files

 

 

 

 

Question No: 27

A good database design

(i)    Is expandable with growth and changes in organization

(ii)     Easy to change when software changes

(iii)     Ensures data integrity

(iv)     Allows access to only authorized users

  1. i, ii
  2. ii, iii
  3. i, ii, iii, iv
  4. i, ii, iii

 

 

 

 

Question No: 28

Access right to a database is controlled by

  1. Top management
  2. System designer
  3. System analyst
  4. Database administrator

 

 

 

Question No: 29

When a logical model is mapped into a physical storage such as a disk store the resultant data model is known as

  1. Conceptual data model
  2. External data model
  3. Internal data model
  4. Disk data model

 

 

 

Question No: 30

Designing physical model of DBMS requires information on

(i)    Data volume

(ii)     Frequency of access to data

(iii)     Programming language used

(iv)     Secondary memory characteristics

  1. i, ii
  2. i, ii, iii
  3. i, ii, iii, iv
  4. i, ii, iv

 

 

 

Question No: 31

By data security in DBMS we mean

  1. Preventing access to data
  2. Allowing access to data only to authorized users
  3. Preventing changing data
  4. Introducing integrity constraints

 

 

 

Question No: 32

Data independence allows

  1. Sharing the same database by several applications
  2. Extensive modification of applications
  3. No data sharing between applications
  4. Elimination of several application programs

 

 

 

Question No: 33

A database should be designed to allow providing

  1. Different views of portions of data requested by an application
  2. Data only to selected applications as decided by an organization
  3. A uniform view of data to all applications
  4. Data to all applications

 

 

 

Question No: 34

By data redundancy in a file based system we mean that

  1. Unnecessary data is stored
  2. Same data is duplicated in many files
  3. Data is unavailable
  4. Files have redundant data

 

 

 

Question No: 35

Entities are identified from the word statement of a problem by

  1. Picking words which are adjectives
  2. Picking words which are nouns
  3. Picking words which are verbs
  4. Picking words which are pronouns

 

 

 

Question No: 36

Number of entity set involved in the relationship is called.

  1. Prime Entity
  2. Degree of Relation.
  3. Cardinality of relation
  4. 1: M relation

 

 

 

Question No: 37

Two pseudo columns present in every Oracle table are .

  1. ROWID & ROWNUM
  2. ROWID & COLUMNID
  3. ROWNUM & COLUMNNUM
  4. None of the above

 

 

 

Question No: 38

A relationship is

  1. An item in an application
  2. A meaningful dependency between entities
  3. A collection of related entities
  4. Related data

 

 

 

Question No: 39

Attributes are

  1. Properties of relationship
  2. Attributed to entities
  • Properties of members of an entity set
  1. i
  2. i and ii
  3. i and iii
  4. iii

 

 

 

Question No: 40

Pick entities from the following:

(i)    vendor

(ii)     student

(iii)     attends

(iv)     km/hour

  1. i, ii, iii
  2. i, ii, iv
  3. i and ii
  4. iii and iv

Review

Average user rating

4.8 / 5

Rating breakdown

5
80% Complete (danger)
1
4
80% Complete (danger)
1
3
80% Complete (danger)
0
2
80% Complete (danger)
0
1
80% Complete (danger)
0

January 29, 2015
This was nice in buy
Assignment from solve zone is probably one of the first preference of students.

October 09, 2016
This was nice in buy
I recommend a website that was really helpful throughout your session.

March 19, 2017
Some day ago
This was nice in buy
This was good in buy . I found all the answer correct and meaningful and had scored good marks
Back to top