CS403- Database Management Systems midterm Past Papers

CS403- Database Management Systems midterm Past Papers

Q. Write the two types of Completeness Constrains?
Answer:-  There are two types of completeness constraints, partial completeness constraints and total completeness

Q- What is the significance of normalization?
Answer: Normalization is basically; a process of efficiently organizing data in a database. There are two goals of the normalization process: eliminate redundant data (for example, storing the same data in more than one table) and ensure data dependencies make sense (only storing related data in a table).
Q. Define main functionality of subtype discriminator?
Answer:- This is a tool or a technique which provides us a methodology to determine that to which subtype one instance of a supertype belongs.

Q. Differentiate Total and Partial Completeness Constraints.
Total Completeness constraint:
Total Completeness constraint exist only if we have a super type and some subtypes associated with that supertype,
and the following situation exists between the super type and subtype. All the instances of the supertype entity must
be present in at one of the subtype entities, i.e.—there should be not instance of the supertype entity which does not
belong to any of the subtype entity.
Partial Completeness Constraint:

This type of completeness constraint exists when it is not necessary for any
supertype entity to have its entire instance set to be associated with any of the subtype entity. This type of situation
exists when we do not identify all subtype entities associated with a supertype entity, or ignore any subtype entity
due to less importance of least usage in a specific scenario.

Q. what is difference between relation and relationship.
Answer:-  Relationship is purely a construct of the er data model.
Relation is a structure of the relational data model.
Q. Make relation b/w two tables with foreign key? With example.
Answer:- EMP (empId, empName, qual, depId)
DEPT (depId, depName, numEmp)
In this example there are two relations; EMP is having record of employees, whereas DEPT is having record of
different departments of an organization. Now in EMP the primary key is empId, whereas in DEPT the primary
key is depId. The depId which is primary key of DEPT is also present in EMP so this is a foreign key.

Q. What do you know about the cardinalities of relationship? 
Answer:-  The cardinality of a relationship is the number of entities to which another entity can map under that
Q. What is Data type? 
Answer:- In Microsoft SQL Server™, each column, local variable, expression, and parameter has a related data type, which is an attribute that specifies the type of data (integer, character, money, and so on) that the object can
Q. How to implement one-to-one relationship while designing tables? 
Answer:- In this relationship primary key of one entity type has to be included on other as foreign key. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key
Q. Name the three different kinds of anomalies which can be eliminated through normalization? 
• Insertion Anomaly
• Deletion Anomaly
• Updating Anomaly

Q. Shortly describe procedural DML and Non-procedural DML. Also give their alternate names.

Answer:-  There are two types of DML. First is procedural in which: the user specifies what data is needed and how to get it. Second is nonprocedural in which the user only specifies what data is needed.
Q. What is a functional dependency F said to be minimal? 
Answer:-  A minimal super key is the candidate key , so if a determinant of functional dependency determines all attributes of that relation then it is definitely a super key and if there is no other functional dependency.

Q.what do you know about partial dependency? 
Answer:- A partial dependency is a dependency where A is functionally dependant on B (A → B), but there is some attribute on A that can be removed from A and yet the dependacy stills holds.
Q:-Define domain of an attribute?
Answer:- Domain is the set of possible values that an attribute can have, that is, we specify a set of values either in the form of a range or some discrete values, and then attribute can have value out of those values.
Q:-Define relationship type?
Answer:- A relationship type is an abstraction of a relationship i.e. a set of relationships instances sharing common
Q:-Describe shortly “the difference Operation “in relational algebra? 
Answer:-  If R and S are two relations which are union compatible then difference of these two relations will be set of tuples that appear in R but do not appear in S. It is denoted by (-).
Q:-Explain the salient features of foreign key with help of example? 
Answer:- A foreign key is a mechanism in a relational database that allows for the association of those tables, or for the tables to have a relationship with one another. There are unique characteristics that a foreign key must have.
A foreign key guarantees that rows in one table correspond to rows in some other table, thereby establishing
database-wide relationships, or references. The table containing the foreign key is the “child,” and the other
table is the “parent.”

Q-What do you know about the cardinalities of relationship?
Answer:-  The cardinality of a relationship is the number of entities to which another entity can map under that relationship.
Q-What is the “data type”?
Answer:-  Data type is defined as a set of values along with the operations that can be performed on those values. Some common data types are Integer, Float, Varchar, Char, String, etc.
Q-What is the basic function of a DML Compiler?
Answer:-  DML Compiler translates DML statements in a query language or we say into low-level instruction. That
instruction is understandable by Query evaluation engine.
Q-Define the first normal form.
Answer:- A relation is in first normal form if and only if every attribute is single valued for each tuple. This means that each attribute in each row, or each cell of the table, contains only one value. No repeating fields or groups are
Q-Write any 5 properties of the Relational tables?
• Each cell of a table contains atomic/single value
• Each column has a distinct name; the name of the attribute it represents
• The values of the attributes come from the same domain
• The order of the columns is immaterial
• Each row/tuple/record is distinct, no two rows can be same

Q. Give at least 02 benefits of Normalization 

 Searching, sorting, and creating indexes is faster, since tables are narrower, and more rows are fit on a
data page.
 More tables allow better use of segments to control physical placement of data.
Q. What is Data Definition Language (DDL)? 
Answer:-  Data Definition Language (DDL) describes the portion of SQL that allows you to create, alter, and destroy database objects.

Q. Describe with example Referential integrity constraints 
Answer:- These are the rules which ensure the correctness of data in the database and maintain the database in usable state so that correct information is portrayed in designing the database. Generally these components are not explicitly defined in data models, they may be available in some of the modern DBMSs but in traditional and general model, these may not be available.
Q.For which purpose do we use relational data model?
Answer:- The RDM is mainly used for designing/defining external and conceptual schemas; however to some extent physical schema is also specified in it.
Q. In which situation do a recursive relationship exist?
Answer:-  This is the situation when any attribute of one entity is associated with another attribute of the same entity.

Q. What is DML (Data Manipulation Language)?
Answer:- For using a certain model certain data manipulations are performed using a specific language. This specific language is called data manipulation language.
Q.What are the three important objectives for using data types while specifying attributes?
 Minimized usage of storage space
 Represent all possible values
 Improve data integrity
Q.Briefly explain super key in relation with primary key.
Answer:- we can identify two different attributes that can individually identify the entity instances of STUDENT and they are regNo and nIdNumber, both are minimal super keys so both are candidate keys. Now in this situation we have got two candidate keys. The one that we choose will be declared as primary key, other will be the alternate key. Any of the candidate keys can be selected as primary key, it mainly depends on the database designer which choice he/she makes. There are certain things that are generally considered while making this decision, like the candidate key that is shorter, easier to remember, to type and is more meaningful is selected as primary key.

Q. Why do the relational data model considered as simple?
Answer:- There is just one structure and that is a relation or a table. Even this single structure is very easy to understand, so a user of even of a moderate genius can understand it easily.

Q. What is the intersection operation in relational algebra?
Answer:- The intersection operation also has the requirement that both the relations should be union compatible, which means they are of same degree and same domains. It is represented by _. If R and S are two relations and we
take intersection of these two relations then the resulting relation would be the set of tuples, which are in both R
and S. Just like union intersection is also commutative.
R ∩ S = S ∩ R

Q- Define primary key and give one example.
A candidate key chosen by the database designer to act as key is the primary key. An entity type may have more
than one candidate keys, in that case the database designer has to designate one of them as primary key, since there
is always only a single primary key in an entity type. The primary key can also be defined as the successful
candidate key
For example:
Roll_No can be used as a primary key of the entity as it would always be unique.
Q- What do you know about Insertion anomaly?
Answer:Suppose we want to insert a course in the table, but this course has not been registered to any student. But we cannot enter the student ID, because no student has registered this course yet. So we can also not insert this course. This is called as insertion anomaly which is wrong state of database

Q. State the two conditions which are imposed on candidate key?
There are two conditions for the candidate key
It identifies the entity instances uniquely, as is required in case of super key.
It should be minimum, that is, no proper subset of candidate key is a key.