Introduction:
Hi Everyone!✋
Here is CS403 Database Management System Mid Term Solved Subjective Question Answers for Sure 100% Success and Exam Preparation. All Questions are very important and repeated every time in exam.
All answers are according to CS403 Handout. Thus all students are request to prepare these Questions for better CGPA and solid Concepts.
If any student prepare these questions sensibly then he no need of any other material and to prepare whole handout.
Benefits of These Notes:
1. Time Consuming:
Mostly students are jobs holder so these are very helpful for them. As I mentioned above all are very important so you can save your time by preparing these notes in a very short time, rather no need to prepare a complete Handout of CS403.
All are Past Exams Questions:
Sometime students confuse about syllabus and pattern about exams questions, so these are very helpful to known about type of questions you will face in exams.
3. Most Repeated:
All are from Past Exams and most repeated. Sometime examiner change 1 or 2 words of question and its meaning remain the same. So prepare all by creating concepts.
4. Completely Theoretical:
Most advantage is that all questions are Theoretical and summary of complete handout. By preparing these notes you will be able to answer the any question you face in exam.
To the point Answers:
All answers given are to the point and according to university and examiner necessities. There is no additional and irrelevant material.
Therefore Prepare them by creating your own concepts and write in your own words in exam. You will not forget these concepts in your future. Then you can taught to others obviously.
Lets Start!
CS403 (Database Management Systems) MID TERM SOLVED QUESTION ANSWERS BY CH ASAD NAEEM BC170200260 Asadnaeem280@gmail.com
Q 1: Write the two types of Completeness
Constrains?
There are
two types of completeness constraints, partial completeness constraints and
total completeness constraints.
Q 2: Define main functionality of
subtype discriminator?
This is a
tool or a technique which provides us a method to determine that which subtype
one example of a super type belongs.
The other
situation where the Subtype discriminator is required the overlapping
constraint; it is the situation where one super type element can be placed in
more than one subtype thing.
Q 3: If we have two entities make it one
to one relationship?
Q 4: What is difference between relation
and relationship?
Relationship is purely creation of the
ER data model. Relationship is any association, linkage, or connection between
the entities of interest to the business; Relation is a structure of the relational
data model.
Q 5: If we map a unary relationship into relationship and ERD
has one to one cardinality then what would be the mapping producer?
Q 6: Make relation b/w two tables with
foreign key? With example
EMP (empId,
empName, qual, depId)
DEPT
(depId, depName, numEmp)
There are
two relations in this given example. 1st is EMP having record of
employees, in 2nd relation DEPT having record of different sections
of an organization. Now in EMP the primary key is empId and depId is the
primary key in DEPT. The depId that is primary key of DEPT is also existing in
EMP so this is a foreign key.
Q 7: What do you know about the
cardinalities of relationship?
The
cardinality of a relationship is the number of entities in which another entity
can map under that relationship.
Q 8: What do you means by “Data type”?
In
Microsoft SQL Server™, each column, local variable, expression, and parameter
has a related data type, some common data types are Integer, Float, Char,
String, etc.
Q 9: How to implement one-to-one
relationship while designing tables?
In this
relationship primary key of one entity included on other as foreign key.
One-to-Many relationships are executed by splitting the data into two tables
with primary key and foreign key relationships.
Q 10: Name the three different kinds of
anomalies which can be eliminated through normalization?
• Insertion
Anomaly • Deletion Anomaly • Updating Anomaly
Q 11: Shortly describe procedural DML (Data
Manipulation Language) and Non-procedural DML with their alternative names.
Here are 2 kinds
of DML.
1st
is practical in which the user states that what data is needed and how to get
it.
2nd
is nonprocedural in which the user only states that what data is needed.
Q 12: What is a functional dependency F also
known as minimal super key?
A minimal
super key is the candidate key, so if a determinant of functional dependency
determines all attributes then it is definitely a super key.
Q 13: What do you know about partial
dependency?
A partial
dependency is a dependency where A is functionally dependent on B (A → B), but some
attribute on A will removed and the dependency will still holds.
Q 14: Define domain of an attribute?
Domain is
the set of possible values that an attribute can have, that is, we identify a
set of values also in the form of a range or some separate values, and then
attribute can have value out of those values.
Q 15: Define relationship type?
A concept
of an association is called “relationship type”. A set of relationships share
common attributes.
Q 16: Describe shortly “the difference
Operation “in relational algebra?
If R and S
are two relations which are union friendly then difference of these two
relations will be set of tuples (Rows of a
table are also called tuples) that appear in R but do not appear in S.
It is denoted by (-).
Q 17: Explain the salient features of
foreign key with help of example?
A foreign
key is a method in a relational database that allows for the tables to have a
relationship with one another. A foreign key have unique features. A foreign
key guarantees that rows in one table correspond to rows in some other table, thus
establish database-wide relationships, or references.
Foreign Key
Example:
EMP (empId,
empName, qual, depId)
DEPT
(depId, depName, numEmp)
There are
two relations in this given example. 1st is EMP having record of employees, in
2nd relation DEPT having record of different sections of an organization. Now
in EMP the primary key is empId and depId is the primary key in DEPT. The depId
that is primary key of DEPT is also existing in EMP so this is a foreign key.
Q 18: What is the basic function of a
DML Compiler?
DML
Compiler translates DML statements in a query language or we say into low-level
instruction. That instruction is comprehensible by Query evaluation engine.
A relation
is in first normal form if and only if every attribute is single valued for
each tuple. Its means that each attribute in each row and cell of the table comprises
only 1 value. No repeating fields or sets are allowable.
Q 20: Write any 5 properties of the
Relational tables or Relation Data Model?
• Each cell
of a table contains single value.
• Each
column has a different name; the name of the attribute it represents.
• The
values of the attributes originate from the similar domain.
• The order
of the columns is trifling.
• Each
row/tuple/record is different; no two rows can be same.
Q 21: Give at least 2 benefits of
Normalization?
Searching,
sorting, and creating indexes is faster, because tables are reduce, and more
rows are fit on a data page.
More tables
allow better use of section to control physical placement of data.
Q 22: What is Data Definition Language
(DDL)?
Data
Definition Language (DDL) describes the portion of SQL (Structured query language) that allows you to create, change, and destroy database
objects.
Q 23: Describe with example Referential
integrity constraints?
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 show in designing
database. Usually these modules are not visibly in data models, they may be accessible
in some of the modern DBMSs (Data Base Management Systems). But in outdated and general model these may not be obtainable.
Q 24: Describe with example function of
Union Operation in relational algebra?
Union Operation is signified by U. If R and S are two relations that’s are union
friendly, and if we take union of these 2 relations then the resultant relation
would be the set of tuples also in R or S or both. By way of it is set so there
are no identical tuples.
The union
operator is commutative its means that R U S = S U R
For
Example:
Let suppose
we have 2 relations “COURSE1” and “COURSE2” specify that the 2 tables storing
the courses offered at different campuses of organization, we will take the
union of two tables to known that exactly which courses are being accessible at
both the campuses.
Q 25: For which purpose do we use
relational data model?
The RDM is
mainly used for designing/defining external and conceptual schemas; but some
extent physical schema is also specified in it.
Q 26: What major problem can occur if we
do not normalize a relation into the first normal form?
It will
make difficult for the designer. Un-normalized relations are more prone to
errors or variation.
Q 27: In which situation does a
recursive relationship exist?
This is the
situation when any attribute of one entity is associated with another attribute
of the same entity.
Q 28: What is DML (Data Manipulation
Language)?
For using a
certain model certain data manipulations are performed using a specific
language also known as data manipulation language.
Q 29: 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 30: Briefly explain super key in
relation with primary key.
We can
identify two different attributes that can individually identify the entity case
of STUDENT and they can be “regNo” and “nIdNumber” both are minimal super keys therefore
both are candidate keys. The one that we choose will be declared as primary
key, other will be the alternate key. Primary key can be any of the candidate
key.
Q 31: What do you know about Insertion
anomaly?
Insertion
anomaly indicates that we cannot insert a fact about one entity until we have
an additional fact about another entity.
Q 32:
Label the following diagram.
1...
2...
Answer: 1... Data Store
2...External entity
Q 33: Why do the relational data model
considered as simple?
There is
just 1 construction and that is a relation or a table. Even this single construction
is very easy to understand, so a user can recognize it easily.
Q 34: What is the intersection operation
in relational algebra?
The
intersection operation also has the requirement that both the relations should
be union friendly, which means they are of same degree and same domains. It is
represented by _.
If “R” and “S”
are 2 relations and if we take intersection of these 2 relations, then the
resulting relation would be the set of tuples just like union intersection is
also “commutative”. R ∩ S = S ∩ R
Bearing in mind the above diagram, which
of the given declarations are true and which are false.
1.
Entity2 is a weak entity. True
2.
Cardinality ratio for Entity1:Entity2 in Rel1 is 1:N. True
3. Attrib6
represents an attribute which is having composite nature. False
4. Attrib3 is
a kind of a derived attributes. True
5. Entity2 is contributing
completely in the Rel1 relationship. True
Q 36: If there is 1
super type and there are 3 subtypes, then how many relations should be created?
If there is 1 super type and there are 3 subtypes, then 4
relations are to be created.
Q 37: Name the two
types of unary operations on relational algebra.
Select and Project
Q 38: “Student CGPA
(StdCGPA) is identified through student name (StdName)”
Signify the above
statement into the FD (functional dependency) representation.
STDUENT (stdName, stdCGPA)
StName à StdCGPA
Q 39: Name the four
basic operations (base operations) in relational algebra.
1: SELECT 2: PROJECT 3: UNION 4: SET DIFFERENCE 5: CARTESIAN
PRODUCT
Q 40: Differentiate
Total and Partial Completeness Constraints.
Total Completeness
constraint:
Total Completeness constraint exists only if we have a supertype
and some subtypes. All the occurrences of the supertype entity should present
in at one of the subtype entities.
Partial Completeness
Constraint:
This type of completeness constraint exists when it is not
necessary for any super type. This type of state exists when we do not identify
all subtype entities associated with a supertype entity,
Normalization is a procedure of professionally organizing data
in a database. There are two goals of the normalization process: eliminate
redundant data (for example: storage of same data in more than one table) and
ensure data dependences make sense (only storing related data in a table).
Q 42: Define primary
key and give one example.
A candidate key chosen by the database designer is act as
primary key. An entity may have more than one candidate keys; in that case the
database designer has to select 1 of them as primary key, meanwhile there is continually
only a single primary key in an entity type. The primary key can also be well-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 43: State the two
conditions which are imposed on candidate key?
There are two conditions for the candidate key,
1. It identifies the entity instances uniquely, as is required
in case of super key.
2. It must be smallest.
Q 44: What is the
importance of determining minimum cardinality in a relationship while designing
database?
It is very significant to regulate the least cardinality when manipulating
a database because it defines the way a database system will be applied.
Q 45: Consider the following two cases:
Student (ID, name, address, phone number, city)
Case
(A)
If Student-ID --> Student name
And Student name --> Student address
Then Student-ID --> Student address
Case
(B)
If Student-ID --> Student name
Then Student-ID, Phone number --> (I forget)
What types of Inference Rules exist in the above two
cases?
SEE Question No 80:
Q 46: Define these terms with examples.
Weak Entity, Entity Type.
Weak Entity:
An entity type whose instances cannot exist without linked with
instances of some other entity type, they cannot occur independently. For
example, in an association we want to keep data about the vehicles owned by the
employees. Now a specific vehicle can exist in this association only if the
owner already exists there as employee. Similarly, if employee leaves the job
and the association agrees to delete the record of the employee then the record
of the vehicle will also be deleted since it cannot exist without linked of
employee.
Entity Type:
The entity type can be described as a name/label allocated to items/objects that exist in a situation and have related properties. For example, electric accessories like a bulb, sports items like Ball & Bat, electronic devices like computers, clothing items like a shirt etc.
Q 47: What are the benefits of using
default values?
“Default values” are the values which are related
with a specific attribute and can benefit us to decrease the probabilities of injecting
incorrect values.
It reduces the
chances of entering incorrect attribute value.
It stops attribute
value to be left empty.
It saves our time by reducing the typing works.
Q 48: Give types of relations.
Unary Relationship: Unary relationships may have one to one,
one-to-many. For example: Roommate where
STUDENT is linked with STUDENT.
Binary relationship: A Binary relationship is the one that
links two entities sets e.g. STUDENT-CLASS.
Ternary Relationship: A “Ternary relationship” is the 1
that includes 3 entities e.g. STUDENT-CLASS-FACULTY.
N-ary Relationship: Maximum relationships in data model are binary or at most ternary but we might describe a relationship set involving any number of entity sets is called n-ary relationship.
Q 49: Give at least 03 types of Anomalies which Normalization handles.
Anomaly
is defined as wrong or dead state of database.
There
are four types of anomalies,
Redundancy
Insertion
Anomaly
Deletion
Anomaly
Updation Anomaly
Q 50: What is the degree of relation?
The
number of attributes it comprises is known as “The Degree of a relation”.
The “Cartesian product” is represented by “X”.
Supposing there is a relation “R” with attributes (A1, A2...An)
and also relation “S” with attributes (B1, B2……Bn) then its “Cartesian product”
will be as follow:
R X S: It will set ordered couples {(A1, B1), (A1, B2)……….}
The resultant relation will be covering all the attributes of R
and all of S.
For Example there are 2 relations “COURSE” and “STUEDNT”. 
Q 52: How to implement one-to-many relationship while designing tables?
If the
primary key in a parent table matches numerous
foreign keys in a child table then the relationship is called by one-to-many.
For example, an application for a sports league might access to a team table
and a player table.
Q 53: In which condition
a relation in first normal form automatically turns into the 2NF (second normal
form)?
If a relation is in FD (functional
dependency) and the key contains a single attribute, the relation would
automatically in 2NF.
Q 54: State one
limitation of Data flow diagrams:
Limitation of DFDs
They do not provide us a way to expressing decision points. DFDs focused on flow of information only.
Q 55: What do you know about the conceptual view of database?
The “conceptual view” is explained by means that an entire description of the data stored in the database. It stores the entire data of the organization that is why it is also identified as the “community view” of the database.
Q 56: Explain and differentiate between Context Level and Level 0 dataflow diagrams.
Context Level:
This is the level of DFD which provides the least amount of details about the working of the system. No System details are exposed in the Contexts DFDs only context is shown.
Level 0 dataflow diagrams:
The level 0 DFD is used to describe the working of the whole system. The level 0 diagram covers all the specious details of the system. It displays the communication between a numbers of processes and may comprise a large number of external entities.
Q 57: Consider the relation R with four
attributes A, B, C and D and the functional dependencies
(A, B) -> (C, D) and
C -> D
The above relation is a normalized relation up to which normal
form?
Answer: 2NF
Q 58: Write a note on
Relational Algebra.
Following
are few major properties of relational algebra:
The
input for relational algebra can be 1 or more relations, and the output would
be another relation, but there would be no change in original participating
relations.
There
are 5 elementary operations in relational algebra:
ü Selection,
ü Projection,
ü Cartesian
product,
ü Union,
ü Set Difference.
Q 59: What is minimum cardinality?
“Minimum cardinality” expresses that whether the link between 2 relations is optional or compulsory.
Q 60: Describe FD functional dependency.
Normalization is founded on the thought of FD (functional
dependency). FD (functional dependency) is a kind of relationship between
attributes.
Q 61: Define Semi Joint with table.
We
take the natural join of 2 relations in semi join then project the attributes
of first table only. Therefore after link and similarity of common attribute of
both relations only attributes of 1st relation are predictable. For
Example if we take the semi join of 2 relations “faculty” and “course” then the
resultant relation would be as under:-
Now
the resulting relation has attributes of first relation after taking the
natural join of both relations.
Q 62: What is inter schema?
Inter Schema Mapping is the tool through which the records or
data at 1 level is connected to the different format of the same data at
another level is known as mapping.
Q 63: Draw and Describe “ER diagram”.
The tags “manager” and “worker” are entitled as “roles”. They agree how employee entities interact via the “works-for” relationship set. Roles are specified in ER diagrams by tagging the lines that join diamonds to rectangles.
Q 64: Define the work of Considerations
and its Implementation in Physical Database Design.
The physical design of the database is one of the most important
phases in the computerization of any organization. Design of the database
should have undergone the following steps,
Normalization of relations
Volume estimate
Definition of each attribute
Explanation of data to be used where and when (with frequencies)
Expectation or requirements of response time and data security
Description of the technologies
We require to check the usage of the data in term of its size and the frequency in physical database design. This dangerous conclusion is to be made to guarantee that correct structures are used and the database is optimized for maximum performance and effectiveness.
Q 65: draw
DFD context diagram.
Level
of DFD which provides the smallest amount of details about the working of the
system. They continuously contain single process and define the single system. System
details are not shown in the Contexts DFDs, only context is shown. Input,
output, process and interactions are shown only with the external entities. For
Example,
No communication is show between external entities themselves.
Q 66: Map
one to one relation of the given scenario.
One-to-One mapping: A mapping “R” from “X” to “Y” is one-to-one if individually entity in X is linked with at most 1 entity in “Y” and vice versa.
Q 67: DFDs diagram of the given scenario.
Data Flow Diagram is a common tool used for designing database
system. It is used to design systems graphically and states different system
detail in different DFD levels.
DFDs display the flow of data between different processes /
specific system.
DFDs are simple and hide difficulties.
DFDs are Sensitive and relatives between processes.
The purpose of the dataflow in a DFD is to express the flow of
information from one entity to another entity in the system. Tool used for
graphical design is Data Flow Diagram (DFD).
“Data flows” are tubes through which packets of information
flow.
Q 68: Name
the types of attributes.
There are different types of Attributes. They may be:
• Simple or Complex • Single valued or multi-valued • Stored or Consequent
Q 69: List two rules of disjoint
constraints?
Rule tells that a certain type of an employee will be either hourly paid employee or salaried employee. This constraint contains the existence of one instance of any supertype entity to exactly subtype entities.
Q 70: For relation model “E-R data model”, what would 3
constructs?
The “E-R data model” supports
following main constructs: • Entity • Attribute • Relationship
Q 71: What is the deference b/w NJ (Natural Join) and EJ (Equi join)?
Equi-join:
Equi-join is the utmost used
type of join. Rows combined on the basis of values of a common attribute
between the 2 relations In Equi–join. It means relations are joined on the
basis of common attributes between them.
Natural Join:
Natural Join is the most common and general form of join. Natural join means a simple join. It is like as Equi join but in natural join the common attribute appears only once.
Q 72: Describe EIC (Entity Integrity Constraint) and RIC (Referential
Integrity Constraint)?
EIC (Entity Integrity Constraint): It represent that in a relation no
attribute of a PK (primary key) can have null value.
RIC (Referential Integrity Constraint): This constraint is functional if foreign keys exists in a relation, also the FK (foreign key) value tie the PK (primary key) value and the foreign key value must be null.
Q 73: Given a "University Environment system" and examine to
express Maximum cardinality.
Maximum cardinality tells us that how many instance of an entity can be placed in the second relation.
Q 74: Two Tables was given COURSE and STUDENT and Equi-join statement
was given need to join both tables in Equi-join.
Rows are joined on the basis of values of a common attribute
between the 2 relations In Equi–join. It means relations are joined on the
basis of common attributes between them.
Q 75: Identify the main difference b/w
intersection and set difference operation.
Intersection:
Both relations should be union compatible for intersection operation, which means they have same degree and same domains.
Set difference operation:
If “R” and “S” are 2 relations which are union friendly then difference of these 2 relations will be set of tuples that appear in “R” but do not appear in “S”. It is denoted by (-).
Q 76: In Your point of view what is the main theme of behind using
cross reference matrix.
To discover entities of the database and their families, it’s very useful. Cross reference matrix is a tool accessible in the data dictionary.
Q 77: Name
4 types of join.
Following are the different types of joins: -
·
Theta Join
·
Equi Join
·
Semi Join
·
Natural Join
· Outer Joins
Q 78: Discuss the main purpose of range control?
Range control implemented over the data. It can be achieved by using any data type. Such as the data types impose the entry of data in the arena according to the restrictions of the data type.
Q 79: Normalization is must to apply on all databases justify this
statement as correct or incorrect?
This statement is correct because the chief objective of normalization is to place the database in highest form of normalization.
Q 80: Consider, we have a relation
Customer with following attributes,
Customer (c-no, name, address, city,
phone-no)
Case A
If c-no --> city, phone-no
Then c-no --> city and c-no -->
phone-no
Case B
If c-no --> phone-no and phone-no,
address --> city
Then c-no, address --> city
Which of the Inference Rules are applied
on the Case A and Case B?
Answer:
Projectivity
or Decomposition Inference will apply In Case A
Projectivity or Decomposition:
In
“Case B” Pseudo transitivity Inference will apply.
Pseudo transitivity:
Anything included/defined in RDM has got an exact meaning since
it is based on mathematics, so there is no confusion.
If something works mathematically it will work with RDM.
Q 82: Major techniques of database modeling?
The procedure of generating the logical structure of the database is known as database modeling. Generally the design of the database is represented graphically because it provides an ease in design and adds flexibility for the understanding the system easily.
Q 83: Consider, we have a relation,
Case A: If Student_id ->stdName then
stdId, stAdr -> stName, stAdr
Case B: If stId -> stName and stName -> stAdr then stId ->stAdr
Which of the Inference Rules are applied
on the Case A and Case B?
Answer:
In “Case A” Augmentation Inference will useful
In “Case B” Transitivity Inference will useful. 
Q 84: What is difference between select
operator and project operator?
The select operation is performed to select certain rows or
tuples of a table, so it performs its action on the table horizontally. Lower
Greek letter sigma 
is used to signify the selection.
Select operator works horizontally on the table and Project
operator functions on a single table vertically, it creates a vertical subset
of the table, take out the values of columns, removing duplicates and insertion
the values in a new table. Projection is signified by a Greek letter
.
Q 85: What are multi-valued attribute?
Some attribute have single value at a time, whereas some others may have multiple values. For example, hobby is attribute of STUDENT or skill is attribute of EMPLOYEE, then a student may have multiple hobbies, likewise an employee may have multiple skills so they are multi-valued attributes. On the other hand, name, father name, designation are generally single valued attributes.
Q 86: Suppose we have 2 entities involved in a relationship, Entities
are A and B. Both Entities A and B are in relationship of one-to-many. Define the
general rule for mapping A and B entity into relation?
These are the rules for mapping entity types:
Each regular ET (entity type) is converted directly into a
relation. Basically entities would be converted into a relation.
PK (Primary key) of the entity is stated as PK of relation and emphasized.
An Entity type with a multi-valued attribute is transformed into 2 relations 1 covers the entity type and other simple attributes.
Q 87: What would be the
constraint on foreign key, if;
MC (Minimum Cardinality) = 0
MC (Minimum Cardinality) = 1
If the minimum cardinality is 0, then the FK is defined as normal and it can have the Null value, on the other hand if it is 1 then we have to state the FK attribute as Not Null.
Q 88: Define PQL (Procedural Query Language)?
The relational algebra is a PQL (procedural query language). A procedures that take 1 or more relations as input and give a new relation.
Q 89: What is Binary Operation?
Binary operations is the operations in which pairs of relations are involved. The input for these operations is two relations and they produce a new relation without changing the original relations. These operations are: Union, Set Difference and Cartesian product.
Q 90: Let suppose we have 2 relations A and B, where:
A=>R.No
B=>GPA
And
A = {01, 02}
B= {3.5, 3.7}
Answer the following questions.
1. Discover A X B
2. Discover B X A
3. Can A X B = B X A
Answer:
A
x B = {01,3.5} {01,3.7} {02,3.5} {02,3.7}
B x A {3.5,01}{3.5,02} {3.7,01}{3.7,01}
No in Cartesian product A x B not equal to B x A
A x B = {01,3.5} {01,3.7} {02,3.5} {02,3.7}
B x A {3.5,01}{3.5,02} {3.7,01}{3.7,01}
Q 91: Write the any four entity of
Examination System of the University.
1.
Student:
2.
Teacher:
3.
Course:
4. Semester:
Q 92: Write the FD (functional dependencies) of the St-Id and Supervisor-Id.
Supervisor-Id
SupName,
SupAdr, SupDept
SupID
PrId PrSal
(Pr means Project)
Q 93: What is
extension and intension?
For a database Intention is nearly permanent because while
designing the database it is guaranteed that no info is left behind, that is essential
enough to the database.
On the bases of a complete intention, Extension of the database will
executed.
Q 94: Define Modification b/w null and zero value.
Null value of an attribute means that the value of attribute is not given or defined yet.
Q 95: How does
Tuple-oriented relational calculus differ from domain-oriented relational calculus?
We are involved typically in finding relation tuples in tuple oriented relational calculus.
Q 96: Briefly explain Boyce - Codd
Normal Form?
A relation is in Boyce-Codd normal form if and only if every
determinant is a candidate key.
A relation R is supposed to be in BCNF if whenever X -> A holds in R, and A is not in X, then X is a candidate key for R.
Q 97: How
End user satisfy requirements from 3 levels Database Architecture.
Not all users have right of entry in database and all the
database levels. The 3 levels construction permits us to distinct the physical
representation of data from the user views data. The 3 levels construction
is useful for hiding the details of internal systems.
Important Concepts:
Concepts from Lecture No. 19 & 21
First Normal Form: In 1NF there is no duplicate value in any tuple and all cells
contain atomic value.
Second Normal Form:
A
relation is in 2NF (second normal form) if and only if it is in 1NF (first normal
form) and all non-key attributes are fully functionally dependent on the key.
Study
the following relation.
CLASS (crId, stId, stName, fId, room, and grade)
In
above relation the key is course ID and student ID. The condition of 2NF (second
normal form) is that all non-key attributes should be fully dependent on the
key. But in given relation student ID is reliant on on student name and also
course ID is somewhat reliant on on faculty ID and room, consequently it is not
in second normal form 2NF.
Third Normal Form: In 3NF all non-key
attributes are functionally dependent only upon the primary key.
PROJECT Ã (projName, projMgr)
EMPLOYEE Ã (empId, empName,
empDept)
These values are in 3NF (third normal form).
Concepts regarding Lecture No. 16
Many-to-Many Relationship:
In this type of relationship one instance of first entity can be
mapped with many instances of second entity. Similarly one instance of second
entity can be mapped with many instances of first entity type. In many to many
relationships a third table is created for the relationship, which is also
called as associative entity type.
For Example, there are 2 entities forms “BOOK” and “STD”
(student). Numerous students can use a book and same as numerous books can be used
by a student, so in this manner there is a many to many relationship. Now there
would be a 3rd relation as well which have its primary key called by
named as TRANS (transaction).
These are the attributes of following relations:
STD (stId, sName,)
BOOK (bkId, bkTitle,)
TRANS (stId,bkId, isDate,)
Now here the third relation TRANS has four attributes first two are the primary keys of two entities whereas the last two are issue date and return date.
Concepts regarding Lecture No. 17
Projection:
While using this operator all the rows of selected attributes of
a relation are part of new relation. For example suppose a relation FACULTY
with 5 attributes and certain number of rows.
If we implement the projection operator on the table then all
the rows of selected attributes will be shown, for example:
Concepts regarding Lecture No. 17 & 18
Cartesian product:
Consider two sets A = {x, y} B = {2, 4, 6}
Cartesian product of these sets (A x B) is a set that consists
of ordered pairs where 1st element of the ordered pair belongs to
set A 2nd element belongs to set B, as shown below:
A X B= {(x, 2), (x, 4), (x, 6), (y, 2), (y, 4), (y, 6)}
A relation is some subset of this Cartesian product, For
example,
• R1= {(x, 2), (y, 2), (x, 6), (x, 4)}
• R2 = {(x, 4), (y, 6), (y, 4)}
Concepts regarding Lecture No. 15
Cardinality and Degree:
The number of columns in relation is degree. For Example
consider the table given below:
We can see in given example the relation STUDENT has 4 columns, therefore
this relation has degree 4.
Concepts regarding Lecture No. 23
De-normalization:
De-normalization
is a technique to move from higher to lower normal forms of database modeling.
De-normalization Situation 1: Merge two Entity
types into one with one to one relationship. Even if one of the entity types is
optional, so joining can lead to wastage of storage,
De-normalization Situation 2: Many-to-many binary
relationships mapped to three relations. Now suppose there are 2 relations
STUDENT and COURSE and there exists a many to many relationship in between
them. Therefore there are 3 relations STUDENT, COURSE and ENROLLED. Now if we
want to check that a student has enrolled in how many courses. So to check this
we will have to join 3 relations, 1st the STUDENT and ENROLLED and
then joining it with COURSE, which is quite exclusive.
Consider the following many to many relationships:-
EMP (empID, eName,
pjId, Sal)
PROJ (pjId, pjName)
WORK (empId.pjId, dtHired, Sal)
This
is a many to many relationship in between EMP and PROJ with a relationship of
WORK.
De-normalization Situation 3: Reference Data:
One-to-many situation. We can understand it by STUDENT and HOBBY relations. 1
student can have 1 interest but 1 interest can be accepted by numerous
students.
Concepts regarding Lecture No. 7
Entities:
Entity is basic
building block of the E-R data model. The term entity is used in 3 different senses
or for 3 different terms and that are:
• Entity type • Entity instance • Entity set
Symbols for
Entity Types
In E R data
model a rectangle is used to represent an entity type. A rectangle with
a single line show strong entity types whereas double lined rectangle is drawn
to signify a weak entity type as is shown below:
Concepts regarding Lecture No. 18
Join type:
Join is a special form of cross product of 2 tables. In
Cartesian product we join a tuple of one table with the tuples of the 2nd
table. For example if we have 2 relation STUDENT and BOOK then it may be obligatory
to see that how much books have been delivered to any specific student.
These are the different types of joins: -
1. Theta Join: 2. Equi Join: 3. Semi Join: 4. Natural Join: 5.
Outer Joins
Theta Join: all the rows of one
relation are mapped/merged with all the rows of second relation,
Left Outer Join: In left outer join all the tuples of left relation remain part
of the output.
Right Outer Join: All the tuples of right relation continue part of the output
relation, while on the left side the tuples, that’s do not match with the right
relation will left as null.
Outer Join: All the tuples of
left and right relations are part of the output in outer join. It means that
all those tuples of left relation which are not matched with right relation are
left as null. Likewise all those tuples of right relation which are not matched
with left relation will left as null.
ALSO READ: CS401 Solved Subjective Questions Answers for Mid Term Exam
0 Comments