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