Course Code : CS-06
Course Title : Database Management Systems
Assignment Number : BCA(4)/TMA/09
Maximum Marks : 25
Last Date of Submission : 30th April, 2009/30th October, 2009

There are four questions in this Assignment. Answer all the questions. You may use illustrations and diagrams to enhance explanation.

Q. 1  Explain architecture of Distributed Database systems? Also, explain the reasons for building distributed database systems?


Ans :

Distributed Database Architecture :

A distributed database system allows applications to access data from local and remote databases. In a homogenous distributed database system, each database is an Oracle Database. In a heterogeneous distributed database system, at least one of the databases is not an Oracle Database. Distributed databases use a client/server architecture to process information requests.

    *Homogenous Distributed Database Systems

    *Heterogeneous Distributed Database Systems

    *Client/Server Database Architecture

Homogenous Distributed Database Systems

A homogenous distributed database system is a network of two or more Oracle Databases hat reside on one or more machines. Following Figure illustrates a distributed system that connects three databases: hq, mfg, and sales. An application can simultaneously access or modify the data in several databases in a single distributed environment. For example, a single query from a Manufacturing client on local database mfg can retrieve joined data from the products table on the local database and the dept table on the remote hq database.

For a client application, the location and platform of the databases are transparent. You can also create synonyms for remote objects in the distributed system so that users can access them with the same syntax as local objects. For example, if you are connected to database mfg but want to access data on database hq, creating a synonym on mfg for the remote dept table enables you to issue this query:

SELECT * FROM dept;

In this way, a distributed system gives the appearance of native data access. Users on mfg do not have to know that the data they access resides on remote databases.

Figure Homogeneous Distributed Database

Heterogeneous Distributed Database Systems

In a heterogeneous distributed database system, at least one of the databases is a non-Oracle Database system. To the application, the heterogeneous distributed database system appears as a single, local, Oracle Database. The local Oracle Database server hides the distribution and heterogeneity of the data.

The Oracle Database server accesses the non-Oracle Database system using Oracle Heterogeneous Services in conjunction with an agent. If you access the non-Oracle Database data store using an Oracle Transparent Gateway, then the agent is a system-specific application. For example, if you include a Sybase database in an Oracle Database distributed system, then you need to obtain a Sybase-specific transparent gateway so that the Oracle Database in the system can communicate with it.

Client/Server Database Architecture

A database server is the Oracle software managing a database, and a client is an application that requests information from a server. Each computer in a network is a node that can host one or more databases. Each node in a distributed database system can act as a client, a server, or both, depending on the situation.

In Following Figure, the host for the hq database is acting as a database server when a statement is issued against its local data (for example, the second statement in each transaction issues a statement against the local dept table), but is acting as a client when it issues a statement against remote data (for example, the first statement in each transaction is issued against the remote table emp in the sales database).

Figure An Oracle Database Distributed Database System

Benefits of Distributed database system.

There are a number of benefits of using a distributed DBMS. These include the following:

    *Capacity and incremental growth
    *Reliability and availability
    *Efficiency and flexibility
    *Sharing

Capacity and growth
An advantage of distributed databases is that as the organisation grows, new sites can be added with little or no upheaval to the DBMS. Compare this to the situation in a centralised system, where growth entails upgrading with changes in hardware and software that effect the entire database.

Reliability and availability
An advantage of distributed databases is that even when a portion of a system (i.e. a local site) is down, the overall system remains available. With replicated data, the failure of one site still allows access to the replicated copy of the data from another site. The remaining sites continue to function. The greater accessibility enhances the reliability of the system.

Efficiency and flexibility
An advantage of distributed databases is that data is physically stored close to the anticipated point of use. Hence if usage patterns change then data can be dynamically moved or replicated to where it is most needed.

Distributed database sharing
An advantage of distributed databases is that users at a given site are able to access data stored at other sites and at the same time retain control over the data at their own site.

Q.2

New City Studio Records company has decided to store information about musicians who perform on its albums (as well as other company data) in a database. The company has wisely chosen to hire you as a database designer.

                • Each musician that records at the company has an SSN, a name, an address, and a phone number. Poorly paid musicians often share the same address, and no address has more than one phone.

                • Each instrument used in songs recorded at the studio has a unique identification number, a name(e.g., guitar, synthesizer, flute) and a musical key (e.g., C, B-flat,E-flat).

                • Each album recorded on the Studio has a title, a copyright date, a format (e.g., CD or MC), and an album identifier.

                • Each song recorded at the Studio has a title and an author.

                • Each musician may play several instruments, and a given instrument may be played by several musicians.

                • Each album has a number of songs on it, but no song may appear on more than one album.

                • Each song is performed by one or more musicians, and a musician may perform a number of songs.

                • Each album has exactly one musician who acts as its producer. A musician may produce several albums, of course.

Draw an ER diagram for the above said schema.