[email protected] +91 9175287066
This website is always under updation with new contents
Unit - IV Relational Database Design
Unit I
Unit II
Unit III
Unit IV
Unit V
Differentiate between functional dependency and multivalued dependency.
Ans: A functional dependency (FD) is a constraint between two sets of attributes in a relation from a database. It is many-to-one relationship between two sets of attributes of a given relation.
Let R be any relation and X and Y are the subsets of the set of attribute of R . The functional dependency can be represented as X -->Y .
This says that the attribute Y is functionally dependent on X.
This FD is said to be hold in R if and only if whenever two tuples of R have the same value for A as well as have the same value for B.
Function dependency can be used for
i) To specify constraint on the set of legal relations.
ii) To test relation to see whether the relation are legal under a given a set of functional dependencies.
The functional dependancy can be defined as follows Let X is the subset of R and y is the subset of R . The functional dependency X->y holds on R if any legal relation r(R) for all pairs of tuples t1,t2 in r such that t1[X] = t2[X] it is also the case that
T1[x]= t2[x] it is also the case that t1[y]= t2[y]
For example, suppose one is designing a system to track vehicles and the capacity of their engines. Each vehicle has a unique vehicle identification number (VIN). One would write VIN → EngineCapacity because it would be inappropriate for a vehicle's engine to have more than one capacity. (Assuming, in this case, that vehicles only have one engine.) However, EngineCapacity → VIN, is incorrect because there could be many vehicles with the same engine capacity.
Multivalued Depedancy:
Let R be a relation and let A, B and C be arbitrary subsets of the set of attribute of R. Then we say that B is multi dependent On A. It is represented as

Because the lecturers attached to the course and the books attached to the course are independent of each other, this database design has a multivalued dependency; if we were to add a new book to the AHA course, we would have to add one record for each of the lecturers on that course, and vice versa.
Put formally, there are two multivalued dependencies in this relation: {course} {book} and equivalently {course} {lecturer}.
Databases with multivalued dependencies thus exhibit redundancy
Explain tree concept hierarchical data model.
Ans: The database schema is represented as a collection of tree structure diagrams. For each such diagram there exists one single instance of a database tree. The root of this tree is a dummy node. The children of that node are instances of the appropriate records types. Each such child instance in turn has several instances of various record types. The following diagram is nothing but a corresponding tree structure diagram which is shown in fig.

In the fig the account record A301 appears twice in the first tree where as customer records John appears twice in the second tree
The above replication has two major drawbacks.
i) Data inconsistency on data duplication occurs.
ii) Waste of space is unavoidable.
The solution is to introduce the concept of virtual record i. a record contain no data value. It does contain a logical pointer to a particular physical record. When a record is to be replicated in several database trees, we keep a single copy of that record in one tree and replace every other record with a virtual record containing a pointer to that physical record.
what is normalization? Explain relation with more than one candidate key.
Ans: The normalization is the process based on the concept of normal forms. If any relation satisfies a certain set of conditions then it is said to be in a particular normal forms.
For example: A relation is said to be in first normal form if and only if it satisfies the condition that it contains scalar value sonly.
The normal are used to minimize various types of anomalies and inconsistencies during database design. All normalized relations are in 1NF some 1NF relations are also in 2NF and some 2NF relations are in 3NF.
A procedure by which a relation in some given normal from say 2NF can be converted into a set of relations in more desirable form, say 3NF. This procedure is called as “Normalization procedure”
Thus normalization procedure is the successive reduction of a given collection of relations to some more desirable from. It is also the reversible process i. e. From the output of the normalization procedure we can obtain the input and no information is lost in the normalization process.
what is IMS system. Explain it.
Ans: IMS (Information Management System) is a database and transaction management system that was first introduced by IBM in 1968. Since then, IMS has gone through many changes in adapting to new programming tools and environments
IMS consists of two major components, the IMS Database Management System (IMS DB) and the IMS Transaction Management System (IMS TM). In IMS DB, the data is organized into a hierarchy. The data in each level is dependent on the data in the next higher level. The data is arranged so that its integrity is ensured, and the storage and retrieval process is optimized. IMS TM controls I/O (input/output) processing, provides formatting, logging, and recovery of messages, maintains communications security, and oversees the scheduling and execution of programs. TM uses a messaging mechanism for queuing requests.
IMS's original programming interface was DL/1 (Data Language/1). Today, IMS applications and databases can be connected to CICS applications and DB2 databases. Java programs can access IMS databases and services.
what is relational schema and relational design? Explain giving suitable example.
Ans: A relation scheme is a plan that indicates the attributes involved in one or more relations. The scheme consists of a set of attributes where attribute is defined on domains.
For ex. consider the relation Schedule. It contains the attributes i. e. prof, course, room, Intake capacity, day and time. Thus relation scheme for the relation schedule says:
Schedule (Prof, course, room, Intake, Capacity, day, time)
Since a relation is an abstraction of some portion of the real world i. e. being modeled in the database & since the real world changes with time, the tuples of relation also vary over time. Thus tuples may be added, deleted or updated over a period of time. However the relation scheme itself does not change.
Relation design: Two approaches generally used in design of a relation database: The decomposition approach and the synthesis approach. The decomposition approach starts with relation and associated set of constraints in the form of functional dependencies and multivalve dependency and join dependencies. The synthesis approach starts with a set of functional dependencies on a set of attribute. It then synthesizes relation relations of the third normal form.
Regardless of the design approach the criteria for the design are the following:
1) The design is content preserving if the original relation can be derived from the relations resulting from the design process. Since the join operations is used in deriving the original relation from its decomposed relations, this criterion is also called as a loss less join decomposition.
2) The relation design is dependency preserving if the original set of constraints can be derived from the dependencies in the output of the design process.
explain network data model.
Ans: The popularity of the network data model coincided with the popularity of the hierarchical data model. Some data were more naturally modeled with more than one parent per child. So, the network model permitted the modeling of many-to-many relationships in data. In 1971, the Conference on Data Systems Languages (CODASYL) formally defined the network model. The basic data modeling construct in the network model is the set construct. A set consists of an owner record type, a set name, and a member record type. A member record type can have that role in more than one set, hence the multiparent concept is supported. An owner record type can also be a member or owner in another set. The data model is a simple network, and link and intersection record types (called junction records by IDMS) may exist, as well as sets between them . Thus, the complete network of relationships is represented by several pairwise sets; in each set some (one) record type is owner (at the tail of the network arrow) and one or more record types are members (at the head of the relationship arrow). Usually, a set defines a 1:M relationship, although 1:1 is permitted. The CODASYL network model is based on mathematical set theory.
The Network Database Model was created for three main purposes. These purposes include representing a complex data relationship more effectively, improving database performance, and imposing a database standard. The network model highly resembles the hierarchal model. Another characteristic is the network database model is composed of at least two record types including an owner, which is a record type equivalent to the parent type in the hierarchal database model, and the member record type which resembles the child type in the hierarchal model.
Explain DBTG data manipulation.
Ans: Following are the data manipulation facilities provided by the DBTG.
1) Run unit it refers to each process or task that is running under the control of DBMS. The process may be a user’s application program containing DMS commands or interactive sessions with a user. The DBMS maintains separate records of the envoronment of each such run unit.
2) Currency indicators: For each record type known to the run unit, the DBMS maintains a marker to indicate the current record position during the execution of the run unit. These record position markers or pointers are called currency indicator. The DBTG maintains a number of currency indicators for each run unit. For each record type known to the run unit there is a currency indicator called the current record of a record type.
3) Database status register: In addition to the currency indicators, the DBMS maintains, for each run unit number of status indicators for the user’s application programme. The term used by the DBTG for these indicators is special registers, however in reality these are hardware register. The registers are DB- Status, DB-Set- Name. DB-Status is a special register to store the appropriate databse status indicator during the execution of any DML command that refer to the database. When an error is detected during the execution of a DML command that refer to the database, the DBMS uses this register to store the name of the set type involved in the command.
4) Following is a list of DML commands of many DBMSs based on DBTG model
Find: Locates the required occurrences of an existing record.
Get: accesses a record occurrences specified by the currency indicator of the record and places it into the template area for the record type in the UWA of the run unit.
Modify: Changes or updates the value of one or more data item in the current record of the run unit.
Explain third normal form with suitable example.
Ans: A table is said to be in the third normal form it is in the second normal form and every non-key attribute is functionally dependent on just the primary key. The primary key here is Ecode. The attribute deot_code is dependent on dept. There is an indirect dependency on the primary key which has to be noticed. It is illustrated in the following form.

Each non key attribute is wholly dependent only on the primary key. Even the third normal form did not satisfy the needs. Hence a new form called the Boyce – Codd Normal form was introduced.
Identify the functional dependency , full functional dependency and partial functional dependency in the following table.

Ans: In the above table K partially depends on J . L is fully functionally depend on J because it depend on J but not any proper subset of J. M is partial depend on J.
what are the objectives of the normalization.
Ans: Objectives of normalization:
• It reduces the redundancy which is the unnecessary repetition of data.
• It is used to structure the data so that any pertinent relationship between entities can be represented.
• To permit simple retrieval of data in response to query and report requests.
• It simplifies the maintenance of the data through updates inventories and deletions.
• It reduces the need to structure or reorganize data when new applications requirements arise.
Explain the network data model with suitable example.
Ans: refer q4d of winter 2005 and for example.
• Consider a database representing a customer-account relationship in a banking system. There are two record types, customer and account.
Explain the architecture of DBTG system.
Ans: The DBTG data model is based on the set construct. The set construct among other things defines the owner record type and the member record types. The record is the basic unit to represent data in the DBTG network database model.
The implementation of one-to- many relationship of asset is represented by linking the members of a given occurrences of a set to the owner record occurrences. The actual method of linking the member record occurrences to the owner is immaterial to the user of the database. We can assume that the set is implemented using a linked list. The list starts at the lower record occurrences and links all the member record occurrences with the pointer in the last member record occurrences leading back to the owner record. Fig shows the implementation of the set occurrences Borrowed where the owner record is Klaf and the member records are the instances Dickens and Hugo. For simplicity we have shown only one of the records fields of each record.

What are the different anomalies if the table is not in 3NF?
Ans: If the table is in 2NF but not in the third normal from then it may be possible that every non key attribute is not depend on just primary key. The relation may have the transitive dependency.Transitive functional dependencies arise:
• when one non-key attribute is functionally dependent on another non-key attribute
• FD: non-key attribute -> non-key attribute
• and when there is redundancy in the database
By definition transitive functional dependency can only occur if there is more than one non-key field, so we can say that a relation in 2NF with zero or one non-key field must automatically be in 3NF.

• address depends on the value in the manager column
• every time B Black is listed in the manager column, the address column has the value `32 High Street'. From this the relation and functional dependency can be implied as:
Project(project_no, manager, address)
manager -> address
• in this case address is transitively dependent on manager. Manager is the determinant - it determines the value of address. It is transitive functional dependency only if all attributes on the left of the “->” are not in the key but are all in the relation, and all attributes to the right of the “->” are not in the key with at least one actually being in the relation.
• Data redundancy arises from this
• we duplicate address if a manager is in charge of more than one project
• causes problems if we had to change the address- have to change several entries, and this could lead to errors.
• The solution is to eliminate transitive functional dependency by splitting the table
• create two relations - one with the transitive dependency in it, and another for all of the remaining attributes.
• split Project into Project and Manager.
• the determinant attribute becomes the primary key in the new relation
• manager becomes the primary key to the Manager relation
• the original key is the primary key to the remaining non-transitive attributes
• in this case, project_no remains the key to the new Projects table.

• Now we need to store the address only once
• If we need to know a manager's address we can look it up in the Manager relation
• The manager attribute is the link between the two tables, and in the Projects table it is now a foreign key.
• These relations are now in third normal form.
Explain function dependence in detail.
Ans: function dependence (FD) : It is an association between two attributes of the same table. One of the attribute is called as the determinant and the other attribute is called as the determined.
If A is the determinant and b is the determined then we say that A functionally determines B and is graphically represented by A B. We can also say that B is functionally determined by A.
For ex. Consider the table below:
Here A does not functionally determines B. This is because for A=1 there is associated more than one value of B i.e. 70 as well as 100.
So FD is defined as follows- “ An attribute in a relational model is said to be functionally dependent on another attribute in the table if it can take only one value for a given value of the attribute upon which it is functionally dependent.”

State major advantage and disadvantages of network model and hierarchical model.
Advantages of network model
• Provide very efficient "High-speed" retrieval
• Simplicity
The network model is conceptually simple and easy to design.
• Ability to handle more relationship types
The network model can handle the one-to-many and many-to-many relationships.
• Ease of data access
In the network database terminology, a relationship is a set. Each set comprises of two types of records.- an owner record and a member record, In a network model an application can access an owner record and all the member records within a set.
• Data Integrity
In a network model, no member can exist without an owner. A user must therefore first define the owner record and then the member record. This ensures the integrity.
• Data Independence
The network model draws a clear line of demarcation between programs and the complex physical storage details. The application programs work independently of the data. Any changes made in the data characteristics do not affect the application program.
Disadvantages of network model
• System complexity
In a network model, data are accessed one record at a time. This makes it essential for the database designers, administrators, and programmers to be familiar with the internal data structures to gain access to the data. Therefore, a user friendly database management system cannot be created using the network model
• Lack of Structural independence.
Making structural modifications to the database is very difficult in the network database model as the data access method is navigational. Any changes made to the database structure require the application programs to be modified before they can access data. Though the network model achieves data independence, it still fails to achieve structural independence.
Hierarchical data model :
Advantages - simplicity
- data security
- data integrity
- efficiency
- ease to add and delete record
Disadvantages - implement complexity
- database management problem
- lack of structural independence
- programming complexity
- implementation limitation
- procedural access language
Faculty-profile (faculty_name,Designation, Highest degree, specialization, Years of exp, number of articles) The following functional dependencies are exist in the following table
Faculty_name --> Designation
Faculty_name --> Highest_degree
Faculty_name --> Specilzation
Faculty_name --> Yearsofexp
Faculty_name --> number_of_art
Faculty_name --> number_of_ artcles
Highest_degree --> Specilization
Is it the above table in 2NF? Why? Normalize it to 3NF.
Ans: The above table is not in the second normal from because every attribute in the above table depend on the primary key attribute i. e. faculty_name but it may be possible that there is redundancy in the above table since many faculties has the same designation and highest degree as well as specialization.
The above relation is in third normal form:
To represent the above relation in third from it may decompose in the following relation
Fac_qualification(Faculty_name,highest_degree,specialization, numbr_of _articles, year_of_exp)
In the fac_qualifiaction there is a possibility of redundancy because it may possible that many faculties has the same degree.
For accessing computer programs go to TECHNOLOGY