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. 1^st is EMP having record of employees, in 2^nd 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.

1^st is practical in which the user states that what data is needed and how to get it.

2^nd 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.

Q 19: Define the first normal form.

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

Q 35: The following diagram describes a part of an ER diagram.

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,

Q 41: What is the significance of normalization?

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”.

Q 51: Describe the Cartesian product operation algebra and its Result?

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 1^st 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:

Q 81: Write 2 main features of RDMs

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 3^rd 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 1^st element of the ordered pair belongs to set A 2^nd 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, 1^st 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 2^nd 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.