DBMS: Tutorial Notes | Database Management System | DBMS - All In One Tutorial

DATABASE MANAGEMENT SYSTEM

This is an all in one DBMS tutorial notes for beginners and expert students.

In this Database Management System tutorial(DBMS), we will learn more about dbms notes, database management system notes, dbms tutorial, dbms tutorials, beginners book dbms for beginners students, study tonight dbms, beginners book, dbms tutorial pdf, dbms notes pdf, dbms full notes.


OBJECTIVE TYPE QUESTIONS

Choose the correct or the best alternative in the following:

Q.1
Which of the following relational algebra operations do not require the participating tables to be union-compatible?

(A) Union
(B) Intersection
(C) Difference
(D) Join

Ans: (D)

Q.2
Which of the following is not a property of transactions?
(A) Atomicity
(B) Concurrency
(C) Isolation
(D) Durability
Ans: (B)

Q.3
Relational Algebra does not have
(A) Selection operator.
(B) Projection operator.
(C) Aggregation operators.
(D) Division operator.
Ans: (C )

Q.4
Checkpoints are a part of
(A) Recovery measures.
(B) Security measures.
(C ) Concurrency measures.
(D) Authorization measures.
Ans: (A)

Q.5
Tree structures are used to store data in
(A) Network model.
(B) Relational model.
(C) Hierarchical model.
(D) File based system.
Ans: (C )


Q.6
The language that requires a user to specify the data to be retrieved without specifying exactly how to get it is
(A) Procedural DML.
(B) Non-Procedural DML.
(C) Procedural DDL.
(D) Non-Procedural DDL.
Ans: (B)

Q.7
Precedence graphs help to find a
(A) Serializable schedule.
(B) Recoverable schedule.
(C) Deadlock free schedule.
(D) Cascadeless schedule.
Ans: (A)

Q.8
The rule that a value of a foreign key must appear as a value of some specific table is called a
(A) Referential constraint.
(B) Index.
(C) Integrity constraint.
(D) Functional dependency.
Ans: (A) The rule that a value of a foreign key must appear as a value of some specific table is called a referential constraint. (Referential integrity constraint is concerned with foreign key)

Q.9
The clause in SQL that specifies that the query result should be sorted in ascending or descending order based on the values of one or more columns is
(A) View
(B) Order by
(C) Group by
(D) Having
Ans: (B) The clause in SQL that specifies that the query result should be sorted in ascending or descending order based on the values of one or more columns is ORDER BY. (ORDER BY clause is used to arrange the result of the SELECT statement)

Q.10
What is a disjoint less constraint?
(A) It requires that an entity belongs to no more than one level entity set.
(B) The same entity may belong to more than one level.
(C) The database must contain an unmatched foreign key value.
(D) An entity can be joined with another entity in the same level entity set.
Ans: (A) Disjoint less constraint requires that an entity belongs to no more than one level entity set. (Disjoint less constraint means that an entity can be a member of at most one of the subclasses of the specialization.)

Q.11
According to the levels of abstraction, the schema at the intermediate level is called
(A) Logical schema.
(B) Physical schema.
(C) Subschema.
(D) Super schema.
Ans: According to the levels of abstraction, the schema at the intermediate level is called conceptual schema.
(Note: All the options given in the question are wrong.)

Q.12
It is an abstraction through which relationships are treated as higher-level entities
(A) Generalization.
(B) Specialization.
(C) Aggregation.
(D) Inheritance.
Ans: (C ) It is an abstraction through which relationships are treated as higher-level entities Aggregation. (In ER diagram, aggregation is used to represent a relationship as an entity set.)

Q.13
A relation is in ____________ if an attribute of a composite key is dependent on an attribute of other composite key.
(A) 2NF
(B) 3NF
(C) BCNF
(D) 1NF
Ans: (B) A relation is in 3 NF if an attribute of a composite key is dependent on an attribute of other composite key. (If an attribute of a composite key is dependent on an attribute of other composite key then the relation is not in BCNF, hence it has to be decomposed.)

Q.14
What is data integrity?
(A) It is the data contained in database that is non redundant.
(B) It is the data contained in database that is accurate and consistent.
(C) It is the data contained in database that is secured.
(D) It is the data contained in database that is shared.
Ans: (B) (Data integrity means that the data must be valid according to the given constraints. Therefore, the data is accurate and consistent.)

Q.15
What are the desirable properties of a decomposition
(A) Partition constraint.
(B) Dependency preservation.
(C) Redundancy.
(D) Security.
Ans: (B) What are the desirable properties of a decomposition – dependency preserving. (Lossless join and dependency preserving are the two goals of the decomposition.)

Q.16
In an E-R diagram double lines indicate
(A) Total participation.
(B) Multiple participation.
(C) Cardinality N.
(D) None of the above.
Ans: (A)

Q.17
The operation which is not considered a basic operation of relational algebra is
(A) Join.
(B) Selection.
(C) Union.
(D) Cross product.
Ans: (A)

Q.18
Fifth Normal form is concerned with
(A) Functional dependency.
(B) Multivalued dependency.
(C) Join dependency.
(D) Domain-key.
Ans:(C)

Q.19
Block-interleaved distributed parity is RAID level
(A) 2.
(B) 3
(C) 4.
(D) 5.
Ans: (D)

Q.20
Immediate database modification technique uses
(A) Both undo and redo.
(B) Undo but no redo.
(C) Redo but no undo.
(D) Neither undo nor redo.
Ans: (A)

Q.21
In SQL the statement select * from R, S is equivalent to
(A) Select * from R natural join S.
(B) Select * from R cross join S.
(C) Select * from R union join S.
(D) Select * from R inner join S.
Ans: (B)

Q.22
Which of the following is not a consequence of concurrent operations?
(A) Lost update problem.
(B) Update anomaly.
(C) Unrepeatable read.
(D) Dirty read.
Ans:(B)

Q.23
As per equivalence rules for query transformation, selection operation distributes over
(A) Union.
(B) Intersection.
(C) Set difference.
(D) All of the above.
Ans: (D)

Q.24
The metadata is created by the
(A) DML compiler
(B) DML pre-processor
(C) DDL interpreter
(D) Query interpreter
Ans: (C)

Q.25
When an E-R diagram is mapped to tables, the representation is redundant for
(A) weak entity sets
(B) weak relationship sets
(C) strong entity sets
(D) strong relationship sets
Ans: (B)

Q.26
Let a relation R(ABCD) with FD={A→B} then the candidate key of R is
(A) A
(B)AB
(C)ACD
(D)None of the above
Ans: (C)

Q.27
In SQL the word ‘natural’ can be used with
(A) inner join
(B) full outer join
(C) right outer join
(D) all of the above
Ans: (A)

Q.28
The default level of consistency in SQL is
(A) repeatable read
(B) read committed
(C) read uncommitted
(D) serializable
Ans: (D)

Q.29
If a transaction T has obtained an exclusive lock on item Q, then T can
(A) read Q
(B) write Q
(C) both read and write Q
(D) write Q but not read Q
Ans: (C)

Q.30
Shadow paging has
(A) no redo
(B) no undo
(C) redo but no undo
(D) neither redo nor undo
Ans: (A)

Q.31
If the closure of an attribute set is the entire relation then the attribute set is a
(A) superkey
(B) candidate key
(C) primary key
(D) not a key
Ans: (A)

Q.32
DROP is a ______________ statement in SQL.
(A) Query
(B) Embedded SQL
(C) DDL
(D) DCL
Ans: (C)

Q.33
If two relations R and S are joined, then the non-matching tuples of both R and S are ignored in
(A) left outer join
(B) right outer join
(C) full outer join
(D) inner join
Ans: (D)

Q.34
The keyword to eliminate duplicate rows from the query result in SQL is
(A) DISTINCT
(B) NO DUPLICATE
(C) UNIQUE
(D) None of the above
Ans: (C)

Q.35
In 2NF
(A) No functional dependencies (FDs) exist.
(B) No multivalued dependencies (MVDs) exist.
(C) No partial FDs exist.
(D) No partial MVDs exist.
Ans: (C)

Q.36
Which one is correct statement?
Logical data independence provides following without changing application programs:
(i) Changes in access methods.
(ii) Adding new entities in database
(iii) Splitting an existing record into two or more records
(iv) Changing storage medium
(A) (i) and (ii) 
(B) (iv) only, 
(C) (i) and (iv) 
(D) (ii) and (iii)
Ans: (D)

Q.37
In an E-R, Y is the dominant entity and X is a subordinate entity. Then which of the following is incorrect :
(A) Operationally, if Y is deleted, so is X
(B) existence is dependent on Y.
(C) Operationally, if X is deleted, so is Y.
(D) Operationally, if X is deleted, & remains the same.
Ans:(C)

Q.38
Relational Algebra is
(A) Data Definition Language .
(B) Meta Language
(C) Procedural query Language
(D) None of the above
Ans: (C)

Q.39
Which of the following aggregate functions does not ignore nulls in its results?.
(A) COUNT.
(B) COUNT (*)
(C) MAX
(D) MIN
Ans: (B)

Q.40
R (A,B,C,D) is a relation. Which of the following does not have a lossless join dependency preserving BCNF decomposition
(A) A B, B CD
(B) A B, B C, C D
(C) AB C, C AD
(D) A BCD
Ans: (D)

Q.41
Consider the join of relation R with a relation S. If R has m tuples and S has n tuples, then the maximum and minimum size of the join respectively are
(A) m+n and 0
(B) m+n and |m-n|
(C) mn and 0
(D) mn and m+n
Ans: (C)

Q.42
Maximum height of a B+ tree of order m with n key values is
(A) Logm(n)
(B) (m+n)/2
(C) Logm/2(m+n)
(D) None of these
Ans: (D)

Q.43
Which one is true statement :
(A) With finer degree of granularity of locking a high degree of concurrency is possible.
(B) Locking prevents non – serializable schedules.
(C) Locking cannot take place at field level.
(D) An exclusive lock on data item X is granted even if a shared lock is already held on X.
Ans: (A)

Q.44
Which of the following statement on the view concept in SQL is invalid?
(A) All views are not updateable
(B) The views may be referenced in an SQL statement whenever tables are referenced.
(C) The views are instantiated at the time they are referenced and not when they are defined.
(D) The definition of a view should not have GROUP BY clause in it.
Ans:(D)

Q.45
Which of the following concurrency control schemes is not based on the
serializability property?
(A) Two – phase locking
(B) Graph-based locking
(C) Time-stamp based locking
(D) None of these .
Ans: (D)

Q.46
Which of the following is a reason to model data?
(A) Understand each user’s perspective of data
(B) Understand the data itself irrespective of the physical representation
(C) Understand the use of data across application areas
(D) All of the above
Ans: (D)

Q.47
If an entity can belong to only one lower level entity then the constraint is
(A) disjoint
(B) partial
(C) overlapping
(D) single
Ans: (B)

Q.48
The common column is eliminated in
(A) theta join
(B) outer join
(C) natural join
(D) composed join
Ans: (C )

Q.49
In SQL, testing whether a subquery is empty is done using
(A) DISTINCT
(B) UNIQUE
(C) NULL
(D) EXISTS
Ans: (D)

Q.50
Use of UNIQUE while defining an attribute of a table in SQL means that the attribute values are
(A) distinct values
(B) cannot have NULL
(C) both (A)&(B)
(D) same as primary key
Ans: (C)

Q.51
The cost of reading and writing temporary files while evaluating a query can be reduced by
(A) building indices
(B) pipelining
(C) join ordering
(D) none of the above
Ans: (B)

Q.52
A transaction is in __________ state after the final statement has been executed.
(A) partially committed
(B) active
(C) committed
(D) none of the above
Ans: (C)

Q.53
In multiple granularity of locks SIX lock is compatible with
(A) IX
(B) IS
(C) S
(D) SIX
Ans: (B)

Q.54
The statement that is executed automatically by the system as a side effect of the modification of the database is
(A) backup
(B) assertion
(C) recovery
(D) trigger
Ans: (D)

Q.55
The normal form that is not necessarily dependency preserving is
(A) 2NF
(B) 3NF
(C) BCNF
(D) 4NF
Ans: (A)

Q.56
A functional dependency of the form x → y is trivial if
(A) y ⊆ x
(B) y ⊂ x
(C) x ⊆ y
(D) x ⊂ y
Ans: (A)

Q.57
The normalization was first proposed by ______________.
(A) Code
(B) Codd
(C) Boyce Codd
(D) Boyce
Ans: (B)

Q.58
The division operator divides a dividend A of degree m+n by a divisor relation B of degree n and produces a result of degree
(A) m – 1
(B) m + 1
(C) m * m
(D) m
Ans: (D)

Q.59
Which of the following is not a characteristic of a relational database model?
(A) Table
(B) Tree like structure
(C) Complex logical relationship
(D) Records
Ans: (B)

Q.60
Assume transaction A holds a shared lock R. If transaction B also requests for a shared lock on R.
(A) It will result in a deadlock situation.
(B) It will immediately be rejected.
(C) It will immediately be granted.
(D) It will be granted as soon as it is released by A.
Ans: (C)

Q.61
In E-R Diagram total participation is represented by
(A) double lines
(B) Dashed lines
(C) single line
(D) Triangle
Ans: (A)

Q.62
The FD A → B , DB → C implies
(A) DA → C
(B) A → C
(C) B → A
(D) DB → A
Ans: (A)

Q.63
The graphical representation of a query is ________.
(A) B-Tree
(B) graph
(C) Query Tree
(D) directed graph
Ans: (C)

Q.64
Union operator is a :
(A) Unary Operator
(B) Ternary Operator
(C) Binary Operator
(D) Not an operator
Ans:(C)

Q.65
Relations produced from an E-R model will always be
(A) First normal form.
(B) Second normal form.
(C) Third normal form.
(D) Fourth normal form.
Ans: (A)

Q.66
Manager salary details are hidden from the employee. This is
(A) Conceptual level data hiding.
(B) External level data hiding.
(C) Physical level data hiding.
(D) None of these.
Ans: (A)

Q.67
Which of the following is true for network structure?
(A) It is a physical representation of the data.
(B) It allows many to many relationships.
(C) It is conceptually simple.
(D) It will be the dominant database of the future.
Ans: (A)

Q.68
Which two files are used during operation of the DBMS?
(A) Query languages and utilities
(B) DML and query language
(C) Data dictionary and transaction log
(D) Data dictionary and query language
Ans: (C )

Q.69
A list consists of last names, first names, addresses and pin codes. If all people in the list have the same last name and same pin code a useful key would be
(A) the pin code
(B) the last name
(C) the compound key first name and last name
(D) Tr from next page
Ans: (C )

Q.70
In b-tree the number of keys in each node is ____ than the number of its children.
(A) one less
(B) same
(C) one more
(D) half
Ans: (A)

Q.71
The drawback of shadow paging technique are
(A) Commit overhead
(B) Data fragmentation
(C) Garbage collection
(D) All of these
Ans: (D)

Q.72
Which normal form is considered adequate for normal relational database design?
(A) 2NF
(B) 5NF
(C) 4NF
(D) 3NF
Ans: (D)

Q.73
Which of the following addressing modes permits relocation without any change over in the code?
(A) Indirect addressing
(B) Indexed addressing
(C) PC relative addressing
(D) Base register addressing
Ans: (B)

Q.74
In a multi-user database, if two users wish to update the same record at the same time, they are prevented from doing so by
(A) jamming
(B) password
(C) documentation
(D) record lock
Ans: (D)

Q.75
The values of the attribute describes a particular_____________
(A) Entity set
(B) File
(C) Entity instance
(D) Organization
Ans: (C)

Q.76
Which of the following relational algebraic operations is not from set theory?
(A) Union
(B) Intersection
(C) Cartesian Product
(D) Select
Ans: (D)

Q.77
Which of the following ensures the atomicity of the transaction?
(A) Transaction management component of DBMS
(B) Application Programmer
(C) Concurrency control component of DBMS
(D) Recovery management component of DBMS
Ans: (A)

Q.78
If both the functional dependencies: X→Y and Y→X hold for two attributes X and Y then the relationship between X and Y is
(A) M:N
(B) M:1
(C) 1:1
(D) 1:M
Ans: (C)

Q.79
What will be the number of columns and rows respectively obtained for the operation,
A- B, if A B are Base union compatible and all the rows of a are common to B?
Assume A has 4 columns and 10 rows; and B has 4 columns and 15 rows
(A) 4,0
(B) 0,0
(C) 4,5
(D) 8,5
Ans: (A)

Q.80
For correct behavior during recovery, undo and redo operation must be
(A) Commutative
(B) Associative
(C) idempotent
(D) distributive
Ans: (C)

Q.81
Which of the following is not a consequence of non-normalized database?
(A) Update Anomaly
(B) Insertion Anomaly
(C) Redundancy
(D) Lost update problem
Ans: (D)

Q.82
Which of the following is true for relational calculus?
(A)∀x(P(x))≡¬(∃x)(¬P(x))
(B)∀x(P(x))≡¬(∃x)(P(x))
(C)∀x(P(x))≡(∃x)(¬P(x))
(D)∀x(P(x))≡(∃x)(P(x))
Ans: (A)

Q.83
The part of a database management system which ensures that the data remains in a consistent state is
(A) authorization and integrity manager
(B) buffer manager
(C) transaction manager
(D) file manager
Ans: (C)

Q.84
Relationships among relationships can be represented in an-E-R model using
(A) Aggregation
(B) Association
(C) Weak entity sets
(D) Weak relationship sets
Ans: (A)

Q.85
In tuple relational calculus P1 AND P2 is equivalent to
(A) (¬P1OR¬P2).
(B) ¬(P1OR¬P2).
(C) ¬(¬P1OR P2).
(D) ¬(¬P1OR ¬P2).
Ans: (D)

Q.86
If α→β holds then so does
(A) γα→γβ
(B) α→→γβ
(C) both (A) and (B)
(D) None of the above
Ans: (A)

Q.87
Cascading rollback is avoided in all protocol except
(A) strict two-phase locking protocol.
(B) tree locking protocol
(C) two-phase locking protocol
(D) validation based protocol.
Ans: (D)

Q. 88
Wait-for graph is used for
(A) detecting view serializability.
(B) detecting conflict serializability.
(C) deadlock prevention
(D) deadlock detection
Ans: (D)

Q.90
The clause alter table in SQL can be used to
(A) add an attribute
(B) delete an attribute
(C) alter the default values of an attribute
(D) all of the above
Ans: (D)

Q. 91
The data models defined by ANSI/SPARC architecture are
(A) Conceptual, physical and internal
(B) Conceptual, view and external
(C) Logical, physical and internal
(D) Logical, physical and view
Ans: (D)

Q.92
Whenever two independent one-to-many relationships are mixed in the same relation, a _______ arises.
(A) Functional dependency
(B) Multi-valued dependency
(C) Transitive dependency
(D) Partial dependency
Ans:(B)

Q.93
A table can have only one
(A) Secondary key
(B) Alternate key
(C) Unique key
(D) Primary key
Ans: (D)

Q.94
Dependency preservation is not guaranteed in
(A) BCNF
(B) 3NF
(C) PJNF
(D) DKNF
Ans: (A)

Q.95
Which is the best file organization when data is frequently added or deleted from a file?
(A) Sequential
(B) Direct
(C) Index sequential
(D) None of the above
Ans: (B)

Q.96
Which of the following constitutes a basic set of operations for manipulating relational data?
(A) Predicate calculus
(B) Relational calculus
(C) Relational algebra
(D) SQL
Ans: (C)

Q.97
An advantage of views is
(A) Data security
(B) Derived columns
(C) Hiding of complex queries
(D) All of the above
Ans: (A)

Q.98
Which of the following is not a recovery technique?
(A) Deferred update
(B) Immediate update
(C) Two-phase commit
(D) Shadow paging
Ans: (C)

Q.99
Isolation of the transactions is ensured by
(A) Transaction management
(B) Application programmer
(C) Concurrency control
(D) Recovery management
Ans: (C)

Q.100
_______ operator is used to compare a value to a list of literals values that have been specified.
(A) Like
(B) COMPARE
(C) BETWEEN
(D) IN
Ans: (A)






When you write your answer, must draw the picture wherever necessary

Q1. Write the Disadvantage of File Processing System over RDBMS

Data Redundancy: 
Data Redundancy means same information is duplicated in several files. This makes data redundancy.


Data Inconsistency: 
Data Inconsistency means different copies of the same data are not matching. That means different versions of same basic data are existing. This occurs as the result of update operations that are not updating the same data stored at different places.

Example: Address Information of a customer is recorded differently in different files.


Difficulty in Accessing Data: It is not easy to retrieve information using a conventional file
processing system. Convenient and efficient information retrieval is almost impossible using conventional file processing system.


Data Isolation: Data are scattered in various files, and the files may be in different format, writing new application program to retrieve data is difficult. 


Integrity Problems: The data values may need to satisfy some integrity constraints. For example, the balance field Value must be greater than 5000. We have to handle this through program code in file processing systems. But in database we can declare the integrity constraints along with definition itself. 


Atomicity Problems: It is difficult to ensure atomicity in file processing system. For example transferring $100 from Account A to account B. If a failure occurs during execution

there could be situation like $100 is deducted from Account A and not credited in Account B.


Concurrent Access Anomalies: if multiple users are updating the same data simultaneously it will result in inconsistent data state. In file processing system it is very difficult to handle this using program code. This results in concurrent access anomalies.

Security Problems: Enforcing Security Constraints in file processing system is very difficult as the application programs are added to the system in an ad-hoc manner.



Q2. What do you mean by HDBMS and NDBMS? What are the disadvantages of these two systems?

Ans. A data model is a collection of conceptual tools for describing data, data relationships,

data semantics, and consistency constraints. The different types of data models are:

1. Flat File based Data Model
2. Hierarchical Data Model
3. Network Data Model

4. Relational Data Model

Database management systems based on the hierarchical data model are known as HDBMS. In the hierarchical model, records are linked in the form of organization chart. The
hierarchical DBMS is used to model one-to-many relationships, presenting data to users in a tree like structure. Within each record, data elements are organized into pieces of records called segments. To the user, each record looks like an organizational chart with one top level segment called the root. An upper segment is connected logically to a lower segment in a parent–child relationship. A parent segment can have more than one child, but a child can have only one parent.


HDBMS and NDBMS
HDBMS and NDBMS


Disadvantage of Hierarchical Model

1) The hierarchical model cannot represent all the relationships that occur in the real world.

2) It cannot demonstrate the overall data model for the enterprise because of the nonavailability of actual data at the time of designing the data model.

3) The hierarchical model is used only when there is a hierarchical character in the concerned database. It cannot represent many to many relationships.



Database management systems based on the network data model are known as NDBMS. Whereas hierarchical structures depict one-to-many relationships, network DBMS depict data logically as many-to-many relationships. In other words, parents can have multiple children, and a child can have more than one parent.


Hierarchical Model in DBMS
Disadvantage of Hierarchical Model in DBMS


Disadvantage of Network Data Model

1) All the records are maintained using pointers and hence the whole database structure becomes very complex. Only database experts use this database successfully as there
are so many links occur between records.

2) Insertion, deletion and updating of any record would require pointer adjustment so it is very difficult to the general public to use this network in real-life application.


Q3. Describe Three-Schema Architecture (or Three level of abstractions) of DBMS and also explain the mappings between levels. Define Physical Data Independence and Logical Data Independence.

Ans. A major purpose of a database system is to provide the users with an abstract view of data. To hide the complexity from users database apply different levels of abstraction. The

following are different levels of abstraction.

 Physical Level/Internal Level
 Logical Level/Conceptual Level

 View Level / External Level


Physical Level / Internal Level

Physical Level is the lowest level of abstraction and it defines the storage structure. The physical level describes complex low level data structures in detail. The database system
hides many of the lowest level storage details from the database programmers. Database Administrators may be aware of certain details of physical organization of data.


Logical Level / Conceptual Level:

This is the next higher level of abstraction which hides the details of physical storage structures and concentrates on describing what data are stored in database, relation between data, types of data, constraints etc. Database programmers, DBA etc. knows the logical structure of data.


View Level / External Level

This the highest level of abstraction. It provides different view to different users. At the view level users see a set of application programs that hide details of data types. The details such as data type etc. are not available at this level. Users don’t have to know how things are implemented underneath-only view or Access is given to a part of data according to the user’s access right


DBMS View Level
DBMS View Level / External Level


Mappings

The DBMS must transform a request specified on an external schema into a request against the conceptual schema (External/Conceptual mapping), and then transform into a request on the internal schema for processing over the stored database (Conceptual/Internal mapping). If the request is database retrieval, the data extracted from the stored database must be reformatted to match the user’s external view. The process of transforming requests and

results between levels are called mappings.


Physical Data Independence

The changes in Physical Level does not affect or visible at the logical level. This is called physical data independence. E.g. the location of the database, if changed from C drive to D drive will not affect the conceptual view or external view.


Logical Data Independence

The changes in the logical level do not affect the view level. This is called logical data independence. E.g. the name field in conceptual view is stored as first name, middle name

and last name whereas in external view, it remains to be as a single name field.




Q4. Describe the role of DBA.

Ans. One of the main reasons for using DBMS is to have a central control of both data and the programs accessing those data. A person who has such control over the system is called a Database Administrator (DBA). The following are the functions of a Database administrator

  • Schema Definition
  • Storage structure and access method definition
  • Schema and physical organization modification.
  • Granting authorization for data access.
  • Routine Maintenance



Schema Definition

The Database Administrator creates the database schema by executing DDL statements. Schema includes the logical structure of database table (Relation) like data types of attributes, length of attributes, integrity constraints etc.



Storage Structure and Access Method Definition


Database tables or indexes are stored in the following ways: Flat files, Heaps, B+ Tree etc.


Schema and physical organization modification


The DBA carries out changes to the existing schema and physical organization.


Granting authorization for data access

The DBA provides different access rights to the users according to their level. Ordinary users might have highly restricted access to data, while you go up in the hierarchy to the administrator, you will get more access rights.



Routine Maintenance


Some of the routine maintenance activities of a DBA is given below.
Taking backup of database periodically
 Ensuring enough disk space is available all the time.

 Monitoring jobs running on the database.
 Ensure that performance is not degraded by some expensive task submitted by some users.

 Performance Tuning



Q5. Define Schema.

Ans. Actually Schemas

(i) is a plan that describes records and relationships between them
(ii) maps entities at one level onto another


That means the overall design of the database is called the schema.

Physical Schema: Describes database design at physical level
Logical Schema: Describes database design at logical level

Subschema: Describes different view of the database.



Q6. What do you mean by Instances in the database?

Ans. The collection of information stored in the database at a particular moment is called an instance of the database. This is also called database state or snapshot or extensions of the

database.

Q7. Why ER model is needed for designing a database?

Ans. ER model means Entity-Relationship model. Conceptual schema is usually built with the ER model. We take a brief example – suppose we are building a banking solution. One of the first thing we have to know is that in a bank there should be an Account, there should be Customers, there should be of course monitory transactions, ledger account books etc. All of these represents some kinds of data elements. These are nothing but Entities. Using ER model
we describe all the entity types, relationships among entities and constraints for ease of understanding to communicate with the non-technical users. We need not think about any
implementation details. This is only for human understanding. The database designer after getting this ER model convert it to the machine understandable internal schema.



Q8. What is the significance of ‘R’ in RDBMS – Explain?

Ans. In RDBMS „R‟ stands for Relational. In RDBMS the attributes are grouped to form a relation, i.e. every attributes are linked with at least one other attribute. This is similar to

a mathematical relation.


Q9. Define Cardinality and Degree (arity) in RDBMS.

Ans. Cardinality: The number of tuples a table contains or number of elements in a relational set contains.


Degree: The number of attributes a table or relation contains.



Q10. Define the following:

(i) Simple and Composite attribute

(ii) Single Valued and Multi Valued attribute
(iii) Stored and Derived attribute
(iv) Complex attribute
(v) NULL attribute


Ans.
Simple attribute: Attributes that cannot be divided into sub parts are called simple or atomic attribute. Example: Eno,age.

Composite attribute: Attributes that are made by one or more simple or composite attributes.

Example:


Simple attribute
Simple attribute


Composite attribute
Composite attribute



Single Valued Attribute: A single valued attribute can have only a single value. For example a person can have only one “date of birth”, “age” etc.

Single valued attribute can be simple (eg. age) or composite (eg. date of birth).

Multi valued attributes: It can have multiple values. Example: Color_of_a_ peacock, Phone no.

Stored attribute: An attribute that that supplies a value to the related attribute is called stored attribute. Example: date_of_birth.

Derived attribute: An attribute whose value can be inferred from the value of other attribute is called derived attribute. Example: Age.

Complex attribute: Attribute that is both composite and multi valued is called complex attribute.

NULL Attribute: Attributes that do not have any applicable value is called NULL attribute. Actually they are missing or unknown values.



Q11. How multi valued attribute differs from composite attribute?

Ans. In composite attribute such as Street, City, State, Zip all of these need not be the same
type but in multi valued attribute all the different values we take are of same type.



SOME IMPORTANT NOTES ON ER DIAGRAM

1. Constraints on Relationship Types

Constraints limit the set of possible combination of entities that can participate in the relationship type.

Two main kinds of constraints-

  • Cardinality Constraints
  • Participation Constraints

Cardinality Constraints


Cardinality Constraints
Cardinality Constraints


Employee works for the department represents a cardinality constraint that while a department may have any number (N) of employees, each employee entity should be associated with only on department instance. 

In some cases one employee could be work for more than one department. In that case there is no constraint of employee → department.


Participation Constraints

Participation Constraints
Participation Constraints

If every project has to be associated with a department, then the “existence” of an instance of Project entity type depends on the existence of an instance of the Handles relation type. A department may handle several different projects. Suppose there is a restriction (or constraint) that a project has no existence unless it is associated with the department. This constraint is called participation constraint. Above example shows total participation. Total participation is shown with a double line in the ER diagram.


2. Identifying Relationships

Identifying Relationships
Identifying Relationships

Insurance Record is a weak entity type with no key. It has no existence without its association with entity type Employee.

An employee is uniquely identified by PAN number (if he is a Tax payer) Let the company prepare an insurance records for each employee. However Insurance record has no existence or no meaning unless it is associated with some employee. Here the relationship Insurance Details is an “Identifying

relationship.



EER model (Enhanced ER Model)

Subclasses or Inheritance

Subclasses means there is a “is-a” relationships
 An entity class B is said to be a subclass of another entity class A, if it shares an “is-a” relationship with A.
 Example-1: Car “is-a” Vehicle.
 Example-2: Manager “is-an” Employee.
 An entity of class B is said to be a specialization of entities of class A. Conversely entities of class A are called generalization of class B entities.
 If we notice it we can see that “is-a” relationship identifies a specialization of some particular entity type. For example, A car is a vehicle but not all vehicle is a car. So
vehicle is more general class of car.
 Each member of the subclass has the same attributes as the super class entities, and participates in the same relationship types.
 Any key attribute which is uniquely identifies entities of a super class can also uniquely identify entities of the sub (special) classes.


Example:

ER Model
EER model (Enhanced ER Model)

PAN is the key of an Employee as well as a Manager.

Special classes may have more attributes in addition to the key attributes of the super class. For example, a manager has one more identification i.e. in which department he

is a manager.



Specialization and Generalization

The process of creating subclasses out of a given entity type is called Specialization.

The reverse process of taking two or more entity types and clubbing them under a common super class is called Generalization.

In ER diagram it can be shown by the following two ways:

ER diagram
ER diagram

or


ER diagram
ER diagram



Predicate-Defined Subclasses

Predicate Defined Subclasses
Predicate Defined Subclasses

Subclass entity types whose members can be defined based on the value of an attribute are called “Predicate-Defined” subclasses.
In the example above job-type is called the defining predicate. So, this is the case how entities of one specialized class distinguish with the other specialized class.



Disjoint and Overlapping Subclasses

Disjoint
Disjoint and Overlapping Subclasses

The “D” in the circle represents that the subclasses are disjoint.
An entity of type Employee may belong to at most one of the subclasses. 

Here disjoint means they are mutually exclusive, i.e. no secretory is a professor and no professor is a secretory.

All secretory have to have job-type as admin and all professor have to have job-type as academic. So set of all secretaries are disjoint from set of all professor.

er model
ER Model

The “O” in the circle represents that the subclasses are overlapping.
Subclasses that are not disjoint are said to overlap.
Disjoint or overlap may be either partial or total.


Aggregation

One limitations of the ER model is that it cannot express relationships among relationships. The solution is Aggregation.


Aggregation basically aggregates a particular ER schema and makes it into an Entity.

er model
ER Model


Here [Center-Course] is an aggregated entity which contains offers relationships. It is also called containment relationship.





Post a Comment

0 Comments