ANSWER KEY OF DBMS Old Question paper:-
Unit - 1
Q1 a):- Three level architecture of DBMS with diagram.
How this leads to data independence explained below:-
The three-schema architecture can be used to further explain the concept
of data independence, which can be defined as the capacity to change the
schema at one level of a database
system without having to change the schema at the next higher level. We can define
two types of data independence:
Logical data independence is the
capacity to change the conceptual schema
without having to change external schemas or application programs. We may
change the conceptual schema to expand the database (by adding a record type or
data item), to change constraints, or to reduce the database (by removing a
record type or data item). In the last case, external schemas that refer only
to the remaining data should not be affected. For example, the external schema
of Figure 1.5(a) should not be affected by changing the GRADE_REPORT file (or record type) shown in Figure 1.2 into the one shown in Figure 1.6(a). Only the view definition and the mappings need
to be changed in a DBMS that supports logical data independence. After the
conceptual schema undergoes a logical reorganization, application pro-grams
that reference the external schema constructs must work as before.
Changes to constraints can be applied to the conceptual schema without
affecting the external schemas or application programs.
Physical data independence is the
capacity to change the internal schema without
having to change the conceptual schema. Hence, the external schemas need not be
changed as well. Changes to the internal schema may be needed because some physical
files were reorganized—for example, by creating additional access
structures—to improve the performance of retrieval or update. If the same data
as before remains in the database, we should not have to change the conceptual
schema. For example, providing an access path to improve retrieval speed of
section records (Figure 1.2) by semester and year should not require a query
such as list all sections offered in fall
2008 to be changed, although the query would be executed more efficiently
by the DBMS by utilizing the new access path.
Generally, physical data independence exists in most databases and file
environments where physical details such as the exact location of data on
disk, and hard-ware details of storage encoding, placement, compression, splitting,
merging of records, and so on are hidden from the user. Applications remain
unaware of these details. On the other hand, logical data independence is
harder to achieve because it allows structural and constraint changes without
affecting application programs—a much stricter requirement.
Whenever we have a multiple-level DBMS, its catalog must be expanded to
include information on how to map requests and data among the various levels.
The DBMS uses additional software to accomplish these mappings by referring to
the mapping information in the catalog. Data independence occurs because when
the schema is changed at some level, the schema at the next higher level
remains unchanged; only the mapping
between the two levels is changed. Hence, application programs refer-ring to
the higher-level schema need not be changed.
The three-schema architecture can make it easier to achieve true data
independence, both physical and logical. However, the two levels of mappings
create an overhead during compilation or execution of a query or program,
leading to inefficiencies in the DBMS. Because of this, few DBMSs have
implemented the full three-schema architecture.
Q b):- Hierarchical DB model
Advantages
- Have many different structures and forms.
- Structures data in an upside-down tree. (Simplifies data overview)
- Manages large amounts of data.
- Express the relationships between information.
- Many children per parent.
- Distribute data in terms of relationships.
- Improve data sharing.
Disadvantages
- One parent per child.
- Complex (users require physical representation of database)
- Navigation system is complex.
- Data must be organized in a hierarchical way without compromising the information.
- Lack structural independence.
- Many too many relationships not supported.
- Data independence.
Network Model
ADVANTAGES OF NETWORK MODEL-
The major advantage of network model are-
1.) Conceptual simplicity-Just like the hierarchical model,the network model is also conceptually simple and easy to design.
2.) Capability to handle more relationship types-The
network model can handle the one to many and many to many relationships
which is real help in modeling the real life situations.
3.) Ease of data access-The data access is easier and flexible than the hierarchical model.
4.) Data integrity- The network model does not allow a member to exist without an owner.
5.) Data independence- The network model is better than the hierarchical model in isolating the programs from the complex physical storage details.
6.) Database standards
DIS-ADVANTAGE OF NETWORK MODEL-
1.) System complexity- All the records are maintained using pointers and hence the whole database structure becomes very complex.
2.) Operational Anomalies- The insertion,deletion and updating operations of any record require large number of pointers adjustments.
3.) Absence of structural independence-structural changes to the database is very difficult.
Q2 a):- E-R diagram Coming Soon
Unit - 2
Q3 a):- Relational Data Model
RELATIONAL DATA MODEL
The relational data model was introduced in 1970 by Edgar F. Codd. He
worked for IBM. All data is represented as simple tabular data
structures which the user can access through a high-level non-procedural
language. In 1974 IBM proposed a new high-level non-procedural language
– SEQUEL (renamed into SQL in 1990).
Advantages
- Structured independence is promoted.
- Users do not have to know the physical representation of the database.
- Use of SQL language to access data.
- Easier database design.
- Tabular view improves simplicity.
- Support large amounts of data.
- Data independence.
- Multi-level relationships between data sets
- No need to predefined data relationships.
Disadvantages
- Data anomalies.
- People need training if they want to use the system effectively and efficiently.
Q3 b):-
- Index - Sequential Files
- Serial File :-
A serial file is a type of file containing
information, which is recorded in the file in the order it occurred. It
can also contain files, in which case the files information would be
listed in the order they were saved in the serial file. The information
or files are essentially recorded in chronological order. New
information is appended, or recorded, at the end of the serial file
only. There are also no subsets or directories of information in the
file.
Serial files are usually stored in the computer's internal memory,
but they could be stored on a disk as well. When a computer reads a
serial file, it starts at the beginning and reads the information
sequentially. Serial files are beneficial due to the speed and ease at
which a computer can read them.
- Direct File
- Sequential File system
b) :- Data integrityReferential integrity
Referential integrity refers to the series of processes that make
sure data is stored and used uniformly. Rules embedded into the
database’s structure about how foreign keys are used ensure that only
appropriate changes, additions, or deletions of data occur. Rules may
include constraints that eliminate the entry of duplicate data,
guarantee that data is accurate, and/or disallow the entry of data that
doesn’t apply.
Referential Integrity
Referential integrity ensures that a value in one table references an
existing value in another table. The rule of referential integrity
states that the value of a foreign key must be within the domain of its related primary key, or it must be null. A domain is the set of valid values for any column.
Foreign keys—table columns that establish links from one table (the
master, or referenced table) to another (the detail, or referencing
table)—are the implementation of a one-to-many (1:M) relationship
between two tables. A foreign key (a column in the referencing table)
must always have a corresponding primary key, which is a column having
the same data type and length on the referenced side of the
relationship. The domain of a foreign key can't fall outside the domain
of its corresponding primary key. The domain must be the same, or the
foreign key can be null.
A foreign-key value of null signifies an independent, or non-identifying, 1:M relationship. (See "The Foreign Key,"
August 1999, for an explanation of dependency in 1:M relationships.) In
an independent 1:M relationship, a row in the referencing table can
exist without a related row in the referenced table. Therefore, you
don't need a foreign key value.
In a dependent (identifying) relationship, every row in the referencing
table must have a related row in the referenced table. You must enforce
a dependent 1:M relationship in the database. You enforce this rule by
declaring a foreign-key reference at the time of table creation (in the
CREATE TABLE or ALTER TABLE command) or through triggers. Declaring a
reference at the time of table creation is known as declarative
referential integrity (DRI). Although DRI code executes faster than
trigger code, both schemes monitor and maintain referential data
integrity. You can't insert into the referencing table a foreign-key
value that doesn't already exist in the referenced table. Listing 2 is
an example of DRI: The pub_id column references the primary key column
of a table called Publishers.
Domain integrity
Domain integrity is the collection of processes that ensure the
accuracy of each piece of data in a domain. In this context, a domain is
a set of acceptable values that a column is allowed to contain. It can
include constraints and other measures that limit the format, type, and
amount of data entered.
Domain integrity ensures that all the data items in a column fall within
a defined set of valid values. Each column in a table has a defined set
of values, such as the set of all numbers for Pubs..zip (five-digit),
the set of all character strings for Pubs..au_lname, and the set of all
dates for Sales..ord_date. When you limit the value assigned to an
instance of that column (an attribute), you are enforcing domain
integrity. Domain integrity enforcement can be as simple as choosing the
correct data type and length for a column. In the Pubs database's
titles table, the column PubDate has a datetime data type and is not
nullable. The creator of the titles table chose a non-null condition and
a datetime data type for this column. This choice ensures that a value
is always present for the PubDate column, and that the value is a valid
date, such as 11/22/99. In this way, the creator put in place some
controls to maintain domain integrity.
Q4 b):- Functional Dependencies are constraints of set of legal relation in a database
Unit - 3
