CSIT seventh semester DBMS old question answer

Q. What are differences in Centralized and Distributed Database Systems? List the relative advantages of data distribution?

A distributed database is a database that is under the control of a central database management system (DBMS) in which storage devices are not all attached to a common CPU. It may be stored in multiple computers located in the same physical location, or may be dispersed over a network of interconnected computers. Collections of data (e.g. in a database) can be distributed across multiple physical locations. A distributed database can reside on network servers on the Internet, on corporate intranets or extranets, or on other company networks. The replication and distribution of databases improves database performance at end-user worksites.

To ensure that the distributive databases are up to date and current, there are two processes: ● Replic Replicati ation on ➢ System maintains multiple copies of data, stored in different sites, for faster retrieval and fault tolerance. ➢ A rela relati tio on or frag fragme ment nt of a rel relati ation is replicated if it is stored red redundantly in two or more sites. ➢ Full replication of a relation is the case where the relation is stored at all sites. ➢ Fully redundant databases are those in which every site contains a copy of the entire database. ● Fragmen Fragmentat tation ion ➢ Relation is partitioned into several fragments stored in distinct sites Divisi sion on of rela relati tion on r into into frag fragme ment ntss r 1, r 2, …, r n which contain contain ➢ Divi sufficient information to reconstruct relation r Major features of a DDB are: ● Data stored at a number number of sites, sites, each site site logically single processor ● Sites are interconnected by a network rather than a multiprocessor multiprocessor configuration ● DDB is logically a single database (although (although each site is a database site) ● DDBMS DDBMS has full full function functionality ality of a DBMS DBMS 1 [email protected]

● To the user, the distributed database database system should appear appear exactly like a non-distributed database system. Advantages of distributed database systems are: ● Data sharing and Distributed Control: The primar primary y advant advantage age to accomp accomplis lishin hing g data data sharin sharing g by means means of data data distribution is that each site is able to retain a degree of control over data stored stored locall locally. y. In a centra centraliz lized ed system system,, the databa database se admini administr strato atorr of the central site controls the database. In a distributed system, there is a global database administrator responsible for the entire system. A part of these responsibilities is delegated to the local database administrator for each site. Depending upon the design of the distributed database system, each local administrator may have a different degree of autonomy which is often a major advantage of distributed databases. ● Reliabilit Reliability y and and Availab Availability ility:: If one site fails in distributed system, the remaining sites may be able to contin continue ue operat operating ing.. In partic particula ular, r, if data data are replic replicate ated d in severa severall sites, sites, transaction needing a particular data item may find it in several sites. Thus, the failure of a site does not necessarily imply the shutdown of the system. Although recovery from failure is more complex in distributed systems than in a centralized system, the ability of most of the systems to continue to oper operat atee desp despit itee fail failur uree of one one site site,, resu result ltss in incr increa ease sed d avai availa labi bili lity ty.. Availability is crucial for database systems used for real-time applications. Loss of access to data, for example, in an airline may result in the loss of potential ticket buyers to competitors. ● Speed Speedup up Query Processin Processing: g: If a query involves involves data at several sites, it may be possible possible to split split the query query into subqueries that can be executed in parallel by several sites. Such parallel computation allows for faster processing of a user’s query. In those cases in which data is replicated, queries may be directed by the system to the least heavily loaded sites. Disadvantages of distributed database systems are: * Complexity (greater potential for bugs in software) * Costly. * Distribution of control (no single database administrator controls the DDB) * Security * Difficult to change * Lack of experience 2 [email protected]

● To the user, the distributed database database system should appear appear exactly like a non-distributed database system. Advantages of distributed database systems are: ● Data sharing and Distributed Control: The primar primary y advant advantage age to accomp accomplis lishin hing g data data sharin sharing g by means means of data data distribution is that each site is able to retain a degree of control over data stored stored locall locally. y. In a centra centraliz lized ed system system,, the databa database se admini administr strato atorr of the central site controls the database. In a distributed system, there is a global database administrator responsible for the entire system. A part of these responsibilities is delegated to the local database administrator for each site. Depending upon the design of the distributed database system, each local administrator may have a different degree of autonomy which is often a major advantage of distributed databases. ● Reliabilit Reliability y and and Availab Availability ility:: If one site fails in distributed system, the remaining sites may be able to contin continue ue operat operating ing.. In partic particula ular, r, if data data are replic replicate ated d in severa severall sites, sites, transaction needing a particular data item may find it in several sites. Thus, the failure of a site does not necessarily imply the shutdown of the system. Although recovery from failure is more complex in distributed systems than in a centralized system, the ability of most of the systems to continue to oper operat atee desp despit itee fail failur uree of one one site site,, resu result ltss in incr increa ease sed d avai availa labi bili lity ty.. Availability is crucial for database systems used for real-time applications. Loss of access to data, for example, in an airline may result in the loss of potential ticket buyers to competitors. ● Speed Speedup up Query Processin Processing: g: If a query involves involves data at several sites, it may be possible possible to split split the query query into subqueries that can be executed in parallel by several sites. Such parallel computation allows for faster processing of a user’s query. In those cases in which data is replicated, queries may be directed by the system to the least heavily loaded sites. Disadvantages of distributed database systems are: * Complexity (greater potential for bugs in software) * Costly. * Distribution of control (no single database administrator controls the DDB) * Security * Difficult to change * Lack of experience 2 [email protected]

What is EER?

● The The Exte Extend nded ed Enti Entity ty-R -Rel elat atio ions nshi hip p (EER (EER)) mode modell is a conc concep eptu tual al (or (or semantic) data model, capable of describing the data requirements for a new information system in a direct and easy to understand graphical notation. ● Data Data requ requir irem emen ents ts for for a data databa base se are are desc descri ribe bed d in term termss of a conc concep eptu tual al schema, using the EER model. ● EER schemata are comparable comparable to UML class class diagrams. diagrams. The EER model introduces the additional concepts of subclasses, super-classes, specializa specialization tion generaliza generalization, tion, and attribute attributess inheritanc inheritance. e. The resulting resulting model is called the enhanced-ER or Extended ER model. It is used to model applications more more comp comple lete tely ly and and accu accura rate tely ly if need needed ed.. It incl includ udes es some some obje object ct-o -ori rien ente ted d concepts, such as inheritance. Why Extend the E-R Model?

E-R is suitable for traditional business applications E-R is not semantically rich enough for advanced applications Applications where E-R is inadequate •

Geographic Geographical al information information systems systems

•

Search Search engine enginess

•

Data Data mini mining ng

•

Mult Multim imed edia ia

•

CAD CAD/CAM /CAM

•

Softwar Softwaree develo developme pment nt

•

Engineerin Engineering g design...a design...and nd others others

ER-to-Relational Mapping Algorithm (steps)

*We use the COMPANY database example to illustrate the mapping procedure. *The COMPANY ER schema is shown in Figure 9.1, and the corresponding COMPANY relational database schema is shown in Figure 9.2 to illustrate the mapping steps. *We assume that the mapping will create tables with simple single-valued attributes. The relational model constraints, which include primary keys, unique 3 [email protected]

keys (if any), and referential integrity constraints on the relations, will also be specified in the mapping results. Step 1: Mapping of Regular Entity Types. For each regular (strong) entity type E in E in the ER schema, create a relation R that incl includ udes es all all the the simp simple le attr attrib ibut utes es of E . Incl Includ udee only only the the simp simple le comp compon onen entt attributes of a composite attribute. Choose one of the key attributes of E of E as as the primary key for R for R.. If the chosen key of E of E is is a composite, then the set of simple attributes that form it will together form the primary key of R. R. If multiple keys were identified for E during E during the conceptual design, the information describing the attributes that form each additional key is kept in order to specify secondary (unique) keys of relation R. R. Knowledge about keys is also kept for indexi indexing ng purpos purposes es and other other types types of analys analyses. es. In our example, example, we create create the relations EMPLOYEE, DEPARTMENT, and PROJECT in Figure 9.2 to correspond to the regular entity types EMPLOYEE, DEPARTMENT, and PROJECT in Figure 9.1. The foreign key and relationship attributes, if any, are not included yet; they will be added during subsequent steps.

4 [email protected]

These include the attributes Super_ssn and Dno of EMPLOYEE, Mgr_ssn and Mgr_start_date of DEPARTMENT, and Dnum of PROJECT. In our example, we choose Ssn, Dnumber, and Pnumber as primary keys for the relations EMPLOYEE, DEPARTMENT, and PROJECT, respectively. Knowledge that Dname of DEPARTMENT and Pname of PROJECT are secondary keys is kept for possible use later in the design. The relations that are created from the mapping of entity types are sometimes called entity relations because each tuple represents an entity instance. The result after this mapping step is shown in Figure 9.3(a). Step 2: Mapping of Weak Entity Types. For each weak entity type W in the ER schema with owner entity type E , create a relation R and include all simple attributes (or simple components of composite attributes) of W as attributes of R. In addition, include as foreign key attributes of 5 [email protected]

R, the primary key attribute(s) of the relation(s) that correspond to the owner entity type(s); this takes care of mapping the identifying relationship type of W . The primary key of R is the combination of the primary key(s) of the owner(s) and the partial key of the weak entity type W , if any. If there is a weak entity type E 2 whose owner is also a weak entity type E 1, then E 1 should be mapped before E 2 to determine its primary key first. In our example, we create the relation DEPENDENT in this step to correspond to the weak entity type DEPENDENT (see Figure 9.3(b)).We include the primary key Ssn of the EMPLOYEE relation—which corresponds to the owner entity type—as a foreign key attribute of DEPENDENT; we rename it Essn, although this is not necessary. The primary key of the DEPENDENT relation is the combination {Essn, Dependent_name}, because Dependent_name (also renamed from Name in Figure 9.1) is the partial key of DEPENDENT. It is common to choose the propagate (CASCADE) option for the referential triggered action (see Section 4.2) on the foreign key in the relation corresponding to the weak entity type, since a weak entity has an existence dependency on its owner entity. This can be used for both ON UPDATE and ON DELETE.

6 [email protected]

Step 3: Mapping of Binary 1:1 Relationship Types. For each binary 1:1 relationship type R in the ER schema, identify the relations S and T that correspond to the entity types participating in R. There are three possible approaches: (1) the foreign key approach, (2) the merged relationship approach, and (3) the crossreference or relationship relation approach. The first approach is the most useful and should be followed unless special conditions exist, as we discuss below. 1. Foreign key approach: Choose one of the relations—S, say—and include as a foreign key in S the primary key of T. It is better to choose an entity type with total participation in R in the role of S. Include all the simple attributes (or simple components of composite attributes) of the 1:1 relationship type R as attributes of S. In our example, we map the 1:1 relationship type MANAGES from Figure 9.1 by choosing the participating entity type DEPARTMENT to serve in the role of S because its participation in the MANAGES relationship type is total (every department has a manager). We include the primary key of the EMPLOYEE relation as foreign key in the DEPARTMENT relation and rename it Mgr_ssn.We also include the simple attribute Start_date of the MANAGES relationship type in the DEPARTMENT relation and rename it Mgr_start_date (see Figure 9.2). Note

7 [email protected]

that it is possible to include the primary key of S as a foreign key in T instead.

In our example, this amounts to having a foreign key attribute, say Department_managed in the EMPLOYEE relation, but it will have a NULL value for employee tuples who do not manage a department. If only 2 percent of employees manage a department, then 98 percent of the foreign keys would be NULL in this case. Another possibility is to have foreign keys in both relations S and T redundantly, but this creates redundancy and incurs a penalty for consistency maintenance. 2. Merged relation approach: An alternative mapping of a 1:1 relationship type is to merge the two entity types and the relationship into a single relation. This is possible when both participations are total, as this would indicate that the two tables will have the exact same number of tuples at all times. 3. Cross-reference or relationship relation approach: The third option is to set up a third relation R for the purpose of cross-referencing the primary keys of the two relations S and T representing the entity types.As we will see, this approach is required for binary M:N relationships. The relation R is 8 [email protected]

called a relationship relation (or sometimes a lookup table), because each tuple in R represents a relationship instance that relates one tuple from S with one tuple from T. The relation R will include the primary key attributes of S and T as foreign keys to S and T. The primary key of R will be one of the two foreign keys, and the other foreign key will be a unique key of R. The drawback is having an extra relation, and requiring an extra join operation when combining related tuples from the tables. Step 4: Mapping of Binary 1:N Relationship Types. For each regular binary 1:N relationship type R, identify the relation S that represents the participating entity type at the N-side of the relationship type. Include as foreign key in S the primary key of the relation T that represents the other entity type participating in R; we do this because each entity instance on the N-side is related to at most one entity instance on the 1-side of the relationship type. Include any simple attributes (or simple components of composite attributes) of the 1:N relationship type as attributes of S. In our example, we now map the 1:N relationship types WORKS_FOR, CONTROLS, and SUPERVISION from Figure 9.1. For WORKS_FOR we include the primary key Dnumber of the DEPARTMENT relation as foreign key in the EMPLOYEE relation and call it Dno. For SUPERVISION we include the primary key of the EMPLOYEE relation as foreign key in the EMPLOYEE relation itself—because the relationship is recursive and call it Super_ssn. The CONTROLS relationship is mapped to the foreign key attribute Dnum of PROJECT, which references the primary key Dnumber of the DEPARTMENT relation. These foreign keys are shown in Figure 9.2. An alternative approach is to use the relationship relation (cross-reference) option as in the third option for binary 1:1 relationships. We create a separate relation R whose attributes are the primary keys of S and T, which will also be foreign keys to S and T. The primary key of R is the same as the primary key of S. This option can be used if few tuples in S participate in the relationship to avoid excessive NULL values in the foreign key. Step 5: Mapping of Binary M:N Relationship Types. For each binary M:N relationship type R, create a new relation S to represent R. Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types; their combination will form the primary key of S.Also include any simple attributes of the M:N relationship type (or simple components of composite attributes) as attributes of S. Notice that we cannot 9 [email protected]

represent an M:N relationship type by a single foreign key attribute in one of the participating relations (as we did for 1:1 or 1:N relationship types) because of the M:N cardinality ratio; we must create a separate relationship relation S. In our example, we map the M:N relationship type WORKS_ON from Figure 9.1 by creating the relation WORKS_ON in Figure 9.2.We include the primary keys of the PROJECT and EMPLOYEE relations as foreign keys in WORKS_ON and rename them Pno and Essn, respectively.We also include an attribute Hours in WORKS_ON to represent the Hours attribute of the relationship type. The primary key of the WORKS_ON relation is the combination of the foreign key attributes {Essn, Pno}. This relationship relation is shown in Figure 9.3(c). The propagate (CASCADE) option for the referential triggered action (see Section 4.2) should be specified on the foreign keys in the relation corresponding to the relationship R, since each relationship instance has an existence dependency on each of the entities it relates. This can be used for both ON UPDATE and ON DELETE. Notice that we can always map 1:1 or 1:N relationships in a manner similar to M:N relationships by using the cross-reference (relationship relation) approach, as we discussed earlier. This alternative is particularly useful when few relationship instances exist, in order to avoid NULL values in foreign keys. In this case, the primary key of the relationship relation will be only one of the foreign keys that reference the participating entity relations. For a 1:N relationship, the primary key of the elationship relation will be the foreign key that references the entity relation on the N-side. For a 1:1 relationship, either foreign key can be used as the primary key of the relationship relation. Step 6: Mapping of Multivalued Attributes. For each multivalued attribute A, create a new relation R. This relation R will include an attribute corresponding to A, plus the primary key attribute K—as a foreign key in R—of the relation that represents the entity type or relationship type that has A as a multivalued attribute. The primary key of R is the combination of A and K. If the multivalued attribute is composite, we include its simple components. In our example, we create a relation DEPT_LOCATIONS (see Figure 9.3(d)). The attribute Dlocation represents the multivalued attribute LOCATIONS of DEPARTMENT, while Dnumber—as foreign key—represents the primary key of the DEPARTMENT relation. The primary key of DEPT_LOCATIONS is the combination of {Dnumber, Dlocation}. A separate tuple will exist in DEPT_LOCATIONS for each location that a department has. The propagate (CASCADE) option for the referential triggered action should be specified on the foreign key in the relation R corresponding to the multivalued attribute for both 10 [email protected]

ON UPDATE and ON DELETE. We should also note that the key of R when mapping a composite, multivalued attribute requires some analysis of the meaning of the component attributes. In some cases, when a multivalued attribute is composite, only some of the component attributes are required to be part of the key of R; these attributes are similar to a partial key of a weak entity type that corresponds to the multivalued attribute. Figure 9.2 shows the COMPANY relational database schema obtained with steps 1 through 6, and Figure 3.6 shows a sample database state. Notice that we did not yet discuss the mapping of n-ary relationship types (n > 2) because none exist in Figure 9.1; these are mapped in a similar way to M:N relationship types by including the following additional step in the mapping algorithm. Step 7: Mapping of N-ary Relationship Types. For each n-ary relationship type R, where n > 2, create a new relation S to represent R. Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types. Also include any simple attributes of the n-ary relationship type (or simple components of composite attributes) as attributes of S. The primary key of S is usually a combination of all the foreign keys that reference the relations representing the participating entity types. However, if the cardinality constraints on any of the entity types E participating in R is 1, then the primary key of S should not include the foreign key attribute that references the relation E_ corresponding to E (see the discussion in Section 7.9.2 concerning constraints on n-ary relationships). For example, consider the relationship type SUPPLY in Figure 7.17. This can be mapped to the relation SUPPLY shown in Figure 9.4, whose primary key is the combination of the three foreign keys {Sname, Part_no, Proj_name}.

069 1.) Explain the following terms Data Mining ECA model Spatial database

Spatial databases provide concepts for databases that keep track of objects in a multi-dimensional space.

11 [email protected]

∙ For example, cartographic databases that store maps include two-dimensional spatial descriptions of their objects—from countries and states to rivers, cities, roads, seas, and so on. These databases are used in many applications, such as environmental, emergency, and battle management. ∙ Other databases, such as meteorological databases for weather information, are three-dimensional, since temperatures and other meteorological information are related to three-dimensional spatial points. ∙ In general, a spatial database stores objects that have spatial characteristics that describe them. The spatial relationships among the objects are important, and they are often needed when querying the database. Although a spatial database can in general refer to an n-dimensional space for any n, we will limit our discussion to two dimensions as an illustration. ∙ The main extensions that are needed for spatial databases are models that can interpret spatial characteristics. In addition, special indexing and storage structures are often needed to improve performance.

Specialization and generalization in an ERR model

The ER Model has the power of expressing database entities in a conceptual hierarchical manner. As the hierarchy goes up, it generalizes the view of entities, and as we go deep in the hierarchy, it gives us the detail of every entity included. Generalization

The process of generalizing entities, where the generalized entities contain the properties of all

the

generalized

entities,

is

called

generalization. In generalization, a number of


entities are brought together into one generalized entity based on their similar characteristics. For example, pigeon, house sparrow, crow and dove can all be generalized as Birds. Specialization

Specialization is the opposite of generalization. In specialization, a group of entities is divided into subgroups based on their characteristics. Take a group ‘Person’ for example. A person has name, date of birth, gender, etc. These properties are common in all persons, human beings. But in a company, persons can be identified as employee, employer, customer, or vendor, based on what role they play in the company.Similarly, in a school database, persons can be specialized as teacher, student, or a staff, based on what role they play in school as entities.

XML and HTML

Key Difference: HTML is a markup language that is used to design web pages. It is written in predefined tag elements. Its primary purpose is to display data with focus on how the data looks. XML is a markup language whose primary purpose is to transport and store data. It is a language that can be used to develop new languages and define other languages. It does not have a predefined set of tags, and allows the developer to customize tags. 13 [email protected]

HyperText Markup Language (HTML) is a well known mark up language used to develop web pages. It has been around for a long time and is commonly used in webpage design. XML or Extensible Markup Language defines a set of rules for encoding documents in a format that can be read by both, human and computer. HTML is written using HTML elements, which consist of tags, primarily an opening tag and a closing tag. The data between these tags is usually the content. The main objective of HTML is to allow web browsers to interpret and display the content written between the tags. The tags are designed to describe the page content. HTML comes with predefined tags. These days, web pages are rarely only designed using HTML. HTML Definition

XML

Markup language

Markup language defines a set of

for displaying web

rules for encoding documents that

pages in a web

can be read by both humans and

browser. Designed

machines. Designed with focus on

to display data with

storing and transporting data.

focus on how the data looks Date when invented 1990

1996

Extended from

SGML

SGML

Type

Static

Dynamic

Usage

Display a web page

Transport data between the application and the database. To develop other mark up languages.

Processing/Rules

No strict rules.

Strict rules must be followed or

Browser will still

processor will terminate processing

generate data to the

the file

best of its ability 14 [email protected]

Language type

Presentation

Neither presentation, nor programming

Tags

Predefined

Custom tags can be created by the author

White Space

Cannot preserve

Preserves white space

white space Limitations

Data does not know

Cannot be used as a subtype of a

itself very well.

sql_variant instance.

Data cannot change

Does not support casting or

in response to

converting to either text or non

environment. Data

text. Does not support the

cannot be easily

following column and table

maintained. Cannot

constraints. XML provides its own

store or call on

encoding. Collations apply to

variables. Lacks the

string types only. Cannot be

capability to define

compared or sorted. Cannot be

new structures by

used in Distributed Partitioned

defining

Views. Not well supported by

relationships

browsers.

between classes. Tags are not useful for exchanging the document between applications.

GIS

Data Mining: Data Mining is the analytic Process. It is designed to explore the data usually big data in search of consistent patterns and/or systematic relationships between 15 [email protected]

variables and then to validate the findings by applying the detected patterns to new subsets of data. Data mining refers to the mining or discovery of new information in terms of patterns or rules from vast amounts of data. To be practically useful, data mining must be carried out efficiently on large files and databases. To date, it is not well integrated with database management systems. ECA Model The model that has been used for specifying active database rules is referred to as the Event-Condition-Action, or ECA model. A rule in the ECA model has three components: 1. 1. The event (or events) that trigger the rule: These events are usually database update operations that are explicitly applied to the database. However, in the general model, they could also be temporal events or other kinds of external events. 2. The condition that determines whether the rule action should be executed: Once the triggering event has occurred, an optional condition may be evaluated. If no condition is specified, the action will be executed once the event occurs. If a condition is specified, it is first evaluated, and only if it evaluates to true will the rule action be executed. 3. The action to be taken: The action is usually a sequence of SQL statements, but it could also be a database transaction or an external program that will be automatically executed. Spatial Database Spatial databases provide concepts for databases that keep track of objects in a multidimensional space. ● For example, cartographic databases that store maps include two-dimensional spatial descriptions of their objects—from countries and states to rivers, cities, roads, seas, and so on. These databases are used in many applications, such as environmental, emergency, and battle management. ● Other databases, such as meteorological databases for weather information, are three-dimensional, since temperatures and other meteorological information are related to three-dimensional spatial points. ● In general, a spatial database stores objects that have spatial characteristics that describe them. The spatial relationships among the objects are important, and they are often needed when querying the database. Although 16 [email protected]

a spatial database can in general refer to an n-dimensional space for any n, we will limit our discussion to two dimensions as an illustration. ● The main extensions that are needed for spatial databases are models that can interpret spatial characteristics. In addition, special indexing and storage structures are often needed to improve performance.

Q 071,6) what are the advantage and disadvantage of OODBMS ? Advantages 1. Improved software-development productivity: Object-oriented programming is modular, as it provides separation of duties in object-based program development. It is also extensible, as objects can be extended to include new attributes and behaviors. Objects can also be reused within an across applications. Because of these three factors – modularity, extensibility, and reusability – object-oriented programming provides improved software-development productivity over traditional procedure-based programming techniques. 2. Improved software maintainability: For the reasons mentioned above, object oriented software is also easier to maintain. Since the design is modular, part of the system can be updated in case of issues without a need to make large-scale changes. 3. Faster development: Reuse enables faster development. Object-oriented programming languages come with rich libraries of objects, and code developed during projects is also reusable in future projects. 4. Lower cost of development: The reuse of software also lowers the cost of development. Typically, more effort is put into the object-oriented analysis and design, which lowers the overall cost of development. 5. Higher-quality software: Faster development of software and lower cost of development allows more time and resources to be used in the verification of the software. Although quality is 17 [email protected]

dependent upon the experience of the teams, object oriented programming tends to result in higher-quality software. 6. More expressive query language

Navigational access from the object is the most common form of data access in an OODBMS. This is in contrast to the associative access of SQL (that is, declarative statements with selection based on one or more predicates). Navigational access is more suitable for handling parts explosion, recursive queries, and so on. 7. Capable of handling a large variety of data types

Unlike traditional databases (such as hierarchical, network or relational), the object oriented database are capable of storing different types of data, for example, pictures, voice video, including text, numbers and so on. Disadvantages of OODBMSs

There are following disadvantages of OODBMSs: 1. Lack of universal data model: There is no universally agreed data model for an OODBMS, and most models lack a theoretical foundation. This .disadvantage is seen as a significant drawback, and is comparable to pre-relational systems. 2. Lack of experience: In comparison to RDBMSs the use of OODBMS is still relatively limited. This means that we do not yet have the level of experience that we have with traditional systems. OODBMSs are still very much geared towards the programmer, rather than the naïve end-user. Also there is a resistance to the acceptance of the technology. While the OODBMS is limited to a small niche market, this problem will continue to exist


3. Lack of standards: There is a general lack of standards of OODBMSs. We have already mentioned that there is not universally agreed data model. Similarly, there is no standard object-oriented query language. 4. Competition: Perhaps one of the most significant issues that face OODBMS vendors is the competition posed by the RDBMS and the emerging ORDBMS products. These products have an established user base with significant experience available. SQL is an approved standard and the relational data model has a solid theoretical formation and relational products have many supporting tools to help .both end-users and developers. 5. Query optimization compromises encapsulations: Query optimization requires. An understanding of the underlying implementation to access the database

efficiently.

However,

this

compromises

the

concept

of

encapsulation. 6. Complexity: The increased functionality provided by the OODBMS (such as the illusion of a single-level storage model, pointer sizzling, long-duratipntransactions,

version

management,

and

schema

evolution--makes the system more complex than that of traditional DBMSs. In complexity leads to products that are more expensive and more difficult to use. 7. Larger program size: Object-oriented programs typically involve more lines of code than procedural programs. 8. Lack of support for views: Currently, most OODBMSs do not provide a view mechanism, which, as we have seen previously, provides many advantages such as data independence, security, reduced complexity, and customization. 9. Lack of support for security: Currently, OODBMSs do not provide adequate security mechanisms. The user cannot grant access rights on individual objects or classes. 10. Not suitable for all types of problems: There are problems that lend themselves well to functional-programming style, logic-programming style, or procedure-based programming style, and applying object-oriented programming in those situations will not result in efficient programs. 19 [email protected]

Q 069,8;070,4) Differentiate between object oriented database and relational database.

Relational vs Object-Oriented RDs: ● Mature ● Extensively tested ● Vast amounts of data in this format already ● Programmers know how to optimize for high-speed retrieval OODs: ● New ● here is a general shortage of experienced, quality programmers ● Lack consensus on standards, definitions, etc. ● Performance concerns

Q 071,3) Distinguish between persistent and transient objects.

Persistent Object

Transient Object

A persistent object is an instance of a

A transient object is an instance of a

class in the domain model that

class in the domain model, which is

represents

created in memory

some

information

extracted from the database.

Persistent objects have permanent

Transient objects are the objects

memory; they remain in memory

which lie in application memory,

until they are explicitly 20 [email protected]

removed.

once application is ended this object also get vanished.

Persistent objects are objects that are

Transient

objects

cannot

be

bound to DB Object.

converted to persistent objects as they have their lifetime defined at the time of their instantiation.

Persistence means, using serializable

Transients

interface,

objects.

storing

objects

are

non

serializable

permanently in the hard disk Data that is not changed and where

Data is stored in the class so the

static binding is applied is called

object of that class having dynamic

persistent base class object.

binding is called transient class object.

Persistent is related to static.

Transient is related to dynamic.

Persistent objects are on heap.

Transient objects are in the transient memory.

Note: Persistent objects when call transient objects, first it is loaded into memory. After process a transient objects can be stored as persistent objects in hard disk. Q 2070 5) discuss some application of active database. How do spatial database differ from regular database ?

An active database system (ADBS) is a DBS that monitors situations of interest and, when they occur, triggers an appropriate response in a timely manner.


The

desired

behavior

is

expressed in production rules (also called event-condition-action

rules),

which are defined and stored in the DBS. This has the benefits that the rules can be shared by many application programs, and the DBS can optimize their implementation. Applications

• Production control, e.g., power plants, ... • Maintenance tasks, e.g., inventory control, ... • Financial applications, e.g., stock & bond trading, ... • Network management • Air traffic control • Program trading • Computer Integrated Manufacturing (CIM)

Q 071 ,4) Discuss how time is represented in temporal database and compare the different time dimensions.

For temporal databases, time is considered to be an ordered sequence of points in some granularity that is determined by the application. For example, suppose that some temporal application never requires time units that are less than one second. Then, each time point represents one second in time using this granularity. In reality, each second is a (short) time duration, not a point, since it may be further divided into milliseconds, microseconds, and so on.


Because there is no known beginning or ending of time, one needs a reference point from which to measure specific time points. Various calendars are used by various cultures . In SQL2, the temporal data types include DATE (specifying Year, Month, and Day as

YYYY-MM-DD), TIME (specifying Hour, Minute, and Second as

HH:MM:SS), TIMESTAMP (specifying a Date/Time combination, with options for including sub-second divisions if they are needed), INTERVAL (a relative time duration, such as 10 days or 250 minutes), and PERIOD (an anchored time duration with a fixed starting point, such as the 10-day period from January 1, 1999 to January 10, 1999, inclusive). Time Dimensions

The most natural interpretation is that the associated time is the time that the event occurred, or the period during which the fact was considered to be true in the real world. If this interpretation is used, the associated time is often referred to as the valid time. A temporal database using this interpretation is called a valid time

database. However, a different interpretation can be used, where the associated time refers to the time when the information was actually stored in the database; that is, it is the value of the system time clock when the information is valid in the system. In this case, the associated time is called the transaction time. A temporal database using this interpretation is called a transaction time database. Other interpretations can also be intended, but these two are considered to be the most common ones, and they are referred to as time dimensions. In some applications, only one of the dimensions is needed and in other cases both time dimensions are required, in which case the temporal database is called a bitemporal database. If other interpretations are intended for time, the user can

define the semantics and program the applications appropriately, and it is called a user-defined time.


070 10) Enumerate the limitation of conventional database compared to multimedia database

The incorporation of multimedia database systems will improve the quantity and quality of information manipulated by computer users in all fields, computer aided design, and information retrieval. The area of intelligent multimedia content analysis and retrieval techniques is an emerging discipline. Techniques for representing and extracting semantic information from media such as speech, images, and video are required. When a multimedia application lacks a database, the data structure is buried in the script, where all of its value is lost. This omission also makes the script more complicated and less flexible. Using a multimedia database makes the data structure logic available to other multimedia applications and simplifies the script so that many scripts can share the same multimedia metadata. In addition, when a multimedia or abstract data database is organized and annotated for one application, other applications can use those annotations without going through the same time-consuming process. This capability adds great value to the data through reuse and controlled redundancy. When multimedia application content is controlled by the multimedia database, multimedia content can be added, deleted, or modified without modifying the application script. For example, interactive kiosks that display, describe, and demonstrate products can be updated automatically without reprogramming the application script. Furthermore, a multimedia application such as a multimedia textbook can actually control the operation of book topics that have the same look and feel. This control lets the script perform as a template: An entire series of math textbooks (algebra, calculus, trigonometry, and geometry), including text and video, can use the same multimedia application because all data is physically separate. Search and retrieval operations are critical in interactive multimedia applications; they must be equally efficient and powerful. Search and retrieval of multimedia and abstract data is challenging, but multimedia databases make it feasible through internal storage format flexibility and efficient operation. The DBMS should have 24 [email protected]

significant knowledge about the data and its structure to enable powerful semantic optimizations and intelligent searches. Search and retrieval operations also give the application access to media components so that they can be dynamically and seamlessly processed when necessary.

Q model 069,9;070,8) What is data warehouse? list the characteristic of data warehouse? how it is differ from databases.

A data warehouse is a central repository for all or significant parts of the data that an enterprise's various business systems collect. Data warehousing emphasizes the capture of data from diverse sources for useful analysis and access, but does not generally start from the point-of-view of the end user who may need access to specialized, sometimes local databases. The latter idea is known as the data mart. Typically, a data warehouse is housed on an enterprise mainframe server or increasingly, in the cloud. Data from various online transaction processing (OLTP) applications and other sources is selectively extracted for use by analytical applications and user queries. characteristic of data warehouse

● Subject Oriented - A data warehouse is subject oriented because it provides information around a subject rather than the organization's ongoing operations. These subjects can be product, customers, suppliers, sales, revenue, etc. A data warehouse does not focus on the ongoing operations, rather it focuses on modelling and analysis of data for decision making.


● Integrated - A data warehouse is constructed by integrating data from heterogeneous sources such as relational databases, flat files, etc. This integration enhances the effective analysis of data. ● Time Variant - The data collected in a data warehouse is identified with a particular time period. The data in a data warehouse provides information from the historical point of view. ● Non-volatile - Non-volatile means the previous data is not erased when new data is added to it. A data warehouse is kept separate from the operational database and therefore frequent changes in operational database is not reflected in the data warehouse. Data warehouse VS Database

Sr.N

Data Warehouse (OLAP)

Operational Database(OLTP)

It involves historical processing

It involves day-to-day processing.

o.

1

of information.

2

OLAP systems are used by

OLTP systems are used by clerks,

knowledge

DBAs, or database professionals.

executives,

workers

such

managers,

as and

analysts.


3

It is used to analyze the business.

It is used to run the business.

4

It focuses on Information out.

It focuses on Data in.

5

It is based on Star Schema,

It is based on Entity Relationship

Snowflake Schema, and Fact

Model.

Constellation Schema.

6

It focuses on Information out.

It is application oriented.

7

It contains historical data.

It contains current data.

8

It

provides

summarized

and

consolidated data.

9

10

11

It

provides

summarized

It provides primitive and highly detailed data.

and

It provides detailed and flat

multidimensional view of data.

relational view of data.

The number of users is in

The number of users is in

hundreds.

thousands.

The number of records accessed

The number of records accessed

is in millions.

is in tens.


12

13

The database size is from 100GB

The database size is from 100

to 100 TB.

MB to 100 GB.

These are highly flexible.

It provides high performance.

069 10 )Explain mobile computing architecture with suitable diagram

The general architecture of a mobile platform is illustrated in Figure 29.1. It is a distributed architecture where a number of computers, generally referred to as Fixed Hosts and Base Stations, are interconnected through a high-speed wired network. Fixed hosts are general purpose computers that are not typically equipped to manage mobile units but can be configured to do so. Base stations function as gateways to the fixed network for the Mobile Units. They are equipped with wireless interfaces and offer network access services of which mobile units are clients. Wireless Communications. The wireless medium on which mobile units and base stations communicate have bandwidths significantly lower than those of a wired network. The current generation of wireless technology has data rates that range from the tens to hundreds of kilobits per second (2G cellular telephony) to tens of megabits per second (wireless Ethernet, popularly known as WiFi). Modem (wired) Ethernet, by comparison, provides data rates on the order of hundreds of megabits per second. Besides data rates, other characteristics also distinguish wireless connectivity options. Some of these characteristics include range, interference, locality of access, and support for packet switching. Some wireless access options allow seamless roaming throughout a geographical region (e.g., cellular networks), whereas WiFi networks are localized around a base station. Some wireless networks, such as WiFi and Bluetooth, use unlicensed areas of the frequency 28 [email protected]

spectrum, which may cause interference with other appliances, such as cordless telephones. Finally, modem wireless networks can transfer data in units called packets, that are commonly used in wired networks in order to conserve bandwidth. Wireless applications must consider these characteristics when choosing a communication option. For example, physical objects block infrared frequencies. While inconvenient for some applications, such blockage allows for secure

wireless

communications within a closed room.

Client/Network Relationships.

Mobile

units can move freely in a geographic

mobility

domain, an area that is circumscribed by wireless network

coverage.

To

manage the mobility of units,

the

geographic

entire mobility

domain is divided into one or more smaller domains, called cells, each of which is supported by at least one base station. The mobile discipline requires that the movement of mobile units be unrestricted throughout the cells of a geographic mobility domain, while maintaining information access contiguity- Le., movement, especially intercell movement, does not negatively affect the data retrieval process. The communication architecture just described is designed to give the mobile unit the impression that it is attached to a fixed network, emulating a traditional client-server architecture. 29 [email protected]

Wireless communications, however, make other architectures possible. One alternative is a mobile ad-hoc network (MANET), illustrated in Figure 29.2. In a MANET, co-located mobile units do not need to communicate via a fixed network, but instead, form their own using cost-effective technologies such as Bluetooth. In a MANET, mobile units are responsible for routing their own data, effectively acting as base stations as well as clients. Moreoever, they must be robust enough to handle changes in the network topology, such as the arrival or departure of other mobile units. MANET applications can be considered as peer-to-peer, meaning that a mobile unit is simultaneously a client and a server. Transaction processing and data consistency control become more difficult since there is no central control in this architecture. Resource discovery and data routing by mobile units make computing in a MANET even more complicated. Sample MANET applications are multi-user games, shared whiteboards, distributed calendars, and battle information sharing. The expectation is that these networks and related applications will become dominant in a few years. Currently MANETs are an active research area in both academia and industry. This research is still in its infancy, so the following discussion will focus on the basic mobile computing architecture described previously.

071 9) Describe the characteristics of mobile computing environment in detail.

As we discussed in the previous section, the characteristics of mobile computing include high communication latency, intermittent wireless connectivity, limited battery life, and, of course, changing client location. Latency is caused by the processes unique to the wireless medium, such as coding data for wireless transfer, and tracking and filtering wireless signals at the receiver. Battery life is directly related to battery size, and indirectly related to the mobile device's capabilities. Intermittent connectivity can be intentional or unintentional. Unintentional disconnections happen in areas wireless signals cannot reach, e.g., elevator shafts or subway tunnels. Intentional disconnections occur by user intent,


e.g., during an airplane takeoff, or when the mobile device is powered down. Finally, clients are expected to move, which alters the network topology and may cause their data requirements to change. All of these characteristics impact data management, and robust mobile applications must consider them in their design. To compensate for high latencies and unreliable connectivity, clients cache replicas of important, frequently accessed data, and work offline, if necessary. Besides increasing data availability and response time, caching can also reduce client power consumption by eliminating the need to make energy-consuming wireless data transmissions for each data access. On the other hand, the server may not be able to reach a client. A client may be unreachable because it is dozing-in an energy-conserving state in which many subsystems are shut down--or because it is out of range of a base station. In either case, neither client nor server can reach the other, and modifications must be made to the architecture in order to compensate for this case. Proxies for unreachable components are added to the architecture. For a client (and symmetrically for a server), the proxy can cache updates intended for the server. When a connection becomes available, the proxy automatically forwards these cached updates to their ultimate destination. As suggested above, mobile computing poses challenges for servers as well as clients. The latency involved in wireless communication makes scalability a problem. Because latency due to wireless communications increases the time to service each client request, the server can handle fewer clients. One way servers relieve this problem is by broadcasting data whenever possible. Broadcast takes advantage of a natural characteristic of radio communications, and is scalable because a single broadcast of a data item can satisfy all outstanding requests for it. For example, instead of sending weather information to all clients in a cell individually, a server can simply broadcast it periodically. Broadcast also reduces the load on the server, as clients do not have to maintain active connections to it. Client mobility also poses many data management challenges. First, servers must keep track of client locations in order to efficiently route messages to them. Second, client data should be stored in the network location that minimizes the 31 [email protected]

traffic necessary to access it. Keeping data in a fixed location increases access latency if the client moves "far away" from it. Finally, as stated above, the act of moving between cells must be transparent to the client. The server must be able to gracefully divert the shipment of data from one base station to another, without the client noticing. Client mobility also allows new applications that are location-based. For example, consider an electronic valet application that can tell a user the location of the nearest restaurant. Clearly, "nearest" is relative to the client's current position, and movement can invalidate any previously cached responses. Upon movement, the client must efficiently invalidate parts of its cache and request updated data from the database.

071 10) Differentiate between XML schema and XML DTD with suitable example.

The critical difference between DTDs and XML Schema is that XML Schema utilize an XML-based syntax, whereas DTDs have a unique syntax held over from SGML DTDs. Although DTDs are often criticized because of this need to learn a new syntax, the syntax itself is quite terse. The opposite is true for XML Schema, which are verbose, but also make use of tags and XML so that authors of XML should find the syntax of XML Schema less intimidating. The goal of DTDs was to retain a level of compatibility with SGML for applications that might want to convert SGML DTDs into XML DTDs. However, in keeping with one of the goals of XML, "terseness in XML markup is of minimal importance," there is no real concern with keeping the syntax brief. LIST1 is an example using DTD and providing a schema definition for the content above, while LIST2 is an example using XML Schema to provide a schema definition (employee.xs). LIST1: Employee Information DTD


LIST2：Employee Information XML Schema （employee.xs） 33 [email protected]

As we see, the syntax is completely different between the two. For the DTD, a unique syntax is written, whereas the XML Schema is written in XML format conforming to XML 1.0 syntax. LIST3 is an example of a valid XML document for the LIST2 XML Schema (employee.xml). For DTD, a DOCTYPE declaration is used to associate with the XML document; but, in the case of XML Schema, the specification does not particularly determine anything with respect to the association of the XML document. Accordingly, the implementation method of the validation tool actually used is followed. However, under the XML Schema specification, there is a defined method for writing a hint to associate with the XML document. The following content is inserted into the root element of the XML document.

071 1) Explain the following terms : a) Data Warehouse

A data warehouse is a central repository for all or significant parts of the data that an enterprise's various business systems collect. Data 34 [email protected]

warehousing emphasizes the capture of data from diverse sources for useful analysis and access, but does not generally start from the point-of-view of the end user who may need access to specialized, sometimes local databases. The latter idea is known as the data mart. Typically, a data warehouse is housed on an enterprise mainframe server or increasingly, in the cloud. Data from various online transaction processing (OLTP) applications and other sources is selectively extracted for use by analytical applications and user queries. b) Distribution Transparency Distribution transparency allows the user to perceive the database as a single, logical entity. If a DDBMS exhibits distribution transparency, then the user does not need· to know the data is fragrances (fragmentation transparency) or the location of data items (Local transparency). Distribution transparency can be classified into: Fragmentation transparency Fragmentation is the highest level of distribution transparency. If fragmentation transparency is provided by the DDBMS, then the user does not need to know that the data is fragmented, As a result, database accesses are based on the global schema,. so the user does not need to specify fragment names or data locations. Location transparency Location is the middle level of distribution transparency. With location transparency, the user must know how the data has been fragmented but still does not have to know the location of the data. Replication transparency Closely related to location transparency is replication transparency, which means that the user is unaware of the replication of fragments. Replication transparency is implied' by location transparency. Local mapping transparency


This is the lowest level of distribution transparency. With local mapping transparency, user needs to specify both fragment names and the location of data items, taking into consideration any replication that may exists. Clearly, this is a more complex and time-consuming query for the user to enter than the first. It is unlikely that a system that provides only this level of transparency would be acceptable to end-users. Naming transparency As a corollary to the above distribution transparencies, we have naming transparency. As in a centralized database, each item in a distributed database must. have a unique name. Therefore, the DDBMS must ensure that no two sites create a database object with the same name. One solution to this problem is to create a central name server, which has the responsibility to ensure uniqueness of all names in the system. However, this approach results in: • Loss of some local autonomy; • Performance problems, if the central site becomes a bottleneck; • Low availability; .if the central site fails the remaining sites cannot create any .new database objects. An alternatively solution is to prefix an object , with the identifier of the site that created it For example, the relation branch created at site S1 might be named S1.Branch. Similarly, we need to be able to identify each fragment and each of its copies. ·Thus, copy 2 of fragment 3 of the Branch relation created at site S1 might be referred to as S1.Branch.F3.C2. However, this results in loss of distribution transparency. An approach that resolves the problems with both these solution uses aliases (sometimes called synonyms) for each database object. Thus, S 1.Branch.F3.C2 might be known as Local Branch by the user at site S1. The DDBMS has the task of mapping an alias to the appropriate database object.

c) X Query

XQuery is a query and functional programming language that queries and transforms collections of structured and unstructured data, usually in the form of XML, text and with vendor-specific extensions for


other data formats (JSON, binary, etc.). The language is developed by the XML Query working group of the W3C. d) Distribution transaction

A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database A distributed transaction is composed of several sub-transactions, each running on a different site. Each database manager ( DM ) can decide to abort (the veto property). An Atomic Commitment Protocol (ACP) is run by each of the DMs to ensure that all the subtransactions are consistently committed or aborted.

e) Knowledge base

In general, a knowledge base is a centralized repository for information: a public library, a database of related information about a particular subject, and whatis.In general, a knowledge base is a centralized repository for information: a public library, a database of related information about a particular subject, and whatis.com could all be considered to be examples of knowledge bases. In relation to information technology (IT), a knowledge base is a machine-readable resource for the dissemination of information, generally online or with the capacity to be put online. An integral component of knowledge management systems, a knowledge base is used to optimize information collection, organization, and retrieval for an organization, or for the general public. f) Classification and clustering CLASSIFICATION

We have a Training set containing data that have been previously categorized


Based on this training set, the algorithms finds the category that the new data points belong to Since a Training set exists, we describe this technique as Supervised learning Example:We use training dataset which categorized customers that have churned. Now based on this training set, we can classify whether a customer will churn or not. Clustering

We do not know the characteristics of similarity of data in advance Using statistical concepts, we split the datasets into sub-datasets such that the Sub-datasets have “Similar” data Since Training set is not used, we describe this technique as Unsupervised learning Example:We use a dataset of customers and split them into sub-datasets of customers with “similar” characteristics. Now this information can be used to market a product to a specific segment of customers that has been identified by clustering algorithm 2.) Distinguish multiple inheritance and selective inheritance in OO concepts.

Multiple inheritances in a type hierarchy occurs when a certain subtype T is a subtype of two (or more) types and hence inherits the functions (attributes and methods) of both super types. For example, we may create a subtype ENGINEERING_MANAGER that is a subtype of both MANAGERand ENGINEER. This leads to the creation of a type lattice rather than a type hierarchy.

070 1.) Explain the following terms : 38 [email protected]

• Extent

• Temporal database

A temporal database is a database that has certain features that support time-sensitive status for entries. Where some databases are considered current databases and only support factual data considered valid at the time of use, a temporal database can establish at what times certain entries are accurate. For temporal databases, time is considered to be an ordered sequence of points in some granularity that is determined by the application. For example, suppose that some temporal application never requires time units that are less than one second. Then, each time point represents one second in time using this granularity. In reality, each second is a (short) time duration, not a point, since it may be further divided into milliseconds, microseconds, and so on. Because there is no known beginning or ending of time, one needs a reference point from which to measure specific time points. Various calendars are used by various cultures . • Degree of homogeneity of DBMS

• X Path ➢ XPath is a syntax for defining parts of

an XML document 39 [email protected]

➢ XPath uses path expressions to navigate in XML documents ➢ XPath contains a library of standard functions ➢ XPath is a major element in XSLT ➢ XPath is a W3C recommendation

• OLAP

OLAP (online analytical processing) enables a user to easily and selectively extract and view data from different points-of-view.OLAP (online analytical processing) is computer processing that enables a user to easily and selectively extract and view data from different points of view. For example, a user can request that data be analyzed to display a spreadsheet showing all of a company's beach ball products sold in Florida in the month of July, compare revenue figures with those for the same products in September, and then see a comparison of other product sales in Florida in the same time period. To facilitate this kind of analysis, OLAP data is stored in a multidimensional database. Whereas a relational database can be thought of as two-dimensional, a multidimensional database considers each data attribute (such as product, geographic sales region, and time period) as a separate "dimension." OLAP software can locate the intersection of dimensions (all products sold in the Eastern region above a certain price during a certain time period) and display them. Attributes such as time periods can be broken down into sub attributes. 2.) Draw an ER Diagram for a hospital with a set of patients and set of doctors associated with each patient a log of various tests and examinations conducted.


(Add appropriate attributes for the entities yourself or see below)

6.) Write a schema that provides tags for a person’s first name, last name, weight, and shoe size. Weight and shoe size tags should have attributes to designate measuring systems.


CSIT seventh semester DBMS old question answer

Recommend Documents