September / October-2007

Database Management System: Paper-205 (DBMS)

Q1. Answer Following:

a. What are prime and non-prime attributes?

Ans. Prime attribute are part of any candidate key. Non-prime attribute are not part of any candidate key.

b. What is multivalued dependency?

Ans. Multivalued dependency is a full constraint between two sets of attributes in a relation.

c. What is DDL?

Ans. Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

o CREATE - to create objects in the databaseo ALTER - alters the structure of the database

o DROP - delete objects from the database

o TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

o COMMENT - add comments to the data dictionary

o RENAME - rename an object

d. Define weak entity set

Ans. An entity set may not have sufficient attributes to form a primary key, and its primary key compromises of its partial key and primary key of its parent entity, then it is said to be Weak Entity set

E. What is transitive functional dependency?

Ans. A functional dependency X->Y is transitive in R, if there exists an attribute Z in R, such that X-> Z, Z-> Y .

F. What is normalization?

Ans. Normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them.

Q-2. a. Explain Structure and architecture of DBMS.

Ans :

Structure Of DBMS :

DBMS (Database Management System) acts as an interface between the user and the database. The user requests the DBMS to perform various operations (insert, delete, update and retrieval) on the database. The components of DBMS perform these requested operations on the database and provide necessary data to the users. The various components of DBMS are shown below: -

1. DDL Compiler - Data Description Language compiler processes schema definitions specified in the DDL. It includes metadata information such as the name of the files, data items, storage details of each file, mapping information and constraints etc.

2. DML Compiler and Query optimizer - The DML commands such as insert, update, delete, retrieve from the application program are sent to the DML compiler for compilation into object code for database access. The object code is then optimized in the best way to execute a query by the query optimizer and then send to the data manager.

3. Data Manager - The Data Manager is the central software component of the DBMS also knows as Database Control System.

Architecture of DBMS :

Data Modeling

A data model is used to describe the structure "logic" and "physics" of a database. Relationships, data

types and constraints are known as the structure or level, dividing into 2 types:

High Level - we call conceptual data model or Entity-Relationship model, its main concept is a projection of the data that gets closest to the vision that the user has data.

Low - known as physical data model, is what provides a detailed view yet of how the data are aramazenados computer.

Schemes

When using the term "description" of the database, as we understand the call of "schema of a database" that is specified for a project database.

Instances

Instances are formed when a data is saved in the database for a certain time they formed these database instances, being changed every time a change in the database is performed. DBMS ensures that all instances satisfying the schema of the database, respecting its structure and its constraints.

In a DBMS architecture has as main objective, separate user applications of physical data that are divided from the diagrams below:

Level domestic or internal schema - uses a data model that shows the physical storage structure of the database, the details of the saved data and access paths.

Level conceptual or conceptual scheme - performs a full description of the structure of the database but did not offer details of the data stored in the database.

Level or external layout view - describes the views of the database to a group of users that shows which users have access to this database.

OR

a. What is data dictionary? How the data dictionary is maintained? Discuss integrity related issues pertaining to Data DictionaryAns: Data dictionary : A relational database system needs to maintain data about the relations, such as the schema of the relations. In general, such “data about data” is referred to as metadata. Relational schemas and other metadata about relations are stored in a structure called the data dictionary or system catalog.

Among the types of information

that the system must store are these:

• Names of the relations.

• Names of the attributes of each relation.

• Domains and lengths of attributes.

• Names of views defined on the database, and definitions of those views.

• Integrity constraints (for example, key constraints).

In addition, many systems keep the following data on users of the system:

• Names of authorized users.

• Authorization and accounting information about users.

theSumit67.blogspot.com

10.7 Data-Dictionary Storage 463

• Passwords or other information used to authenticate users.

Further, the database may store statistical and descriptive data about the relations,

such as:

• Number of tuples in each relation.

• Method of storage for each relation (for example, clustered or nonclustered).

The data dictionary may also note the storage organization (sequential, hash, or

heap) of relations, and the location where each relation is stored:

• If relations are stored in operating system files, the dictionary would note the

names of the file (or files) containing each relation.

• If the database stores all relations in a single file, the dictionary may note the

blocks containing records of each relation in a data structure such as a linked

list.

All this metadata information constitutes, in effect, a miniature database.

Some database systems store such metadata by using special-purpose data structures

and code. It is generally preferable to store the data about the database as

relations in the database itself. By using database relations to store system metadata,

we simplify the overall structure of the system and harness the full power

of the database for fast access to system data.

The exact choice of how to represent system metadata by relations must be

made by the system designers. One possible representation, with primary keys

underlined, attributes of the relation Index metadata is assumed to contain a list of one or more

attributes, which can be represented by a character string such as “dept name,

building”. The Index metadata relation is thus not in first normal form; it can be

normalized, but the above representation is likely to be more efficient to access.

The data dictionary is often stored in a nonnormalized form to achieve fast access.

Whenever the database system needs to retrieve records from a relation, it

must first consult the Relation metadata relation to find the location and storage

organization of the relation, and then fetch records using this information. However,

the storage organization and location of the Relation metadata relation itself

b. Describe the concept of Generalization, Specialization and Aggregation

Ans : Generalization : The refinement from an initial entity set into successive levels of entity subgroupings represents a top-down design process inwhich distinctions are made explicit. The design process may also proceed in a bottom-up manner, in which multiple entity sets are synthesized into a higher-level entity set on the basis of common features. The database designer may have first identified:

• instructor entity set with attributes instructor id, instructor name, instructor salary, and rank.

• secretary entity set with attributes secretary id, secretary name, secretary salary, and hours per week.

There are similarities between the instructor entity set and the secretary entity set in the sense that they have several attributes that are conceptually the same across the two entity sets: namely, the identifier, name, and salary attributes.

This commonality can be expressed by generalization, which is a containment relationship that exists between a higher-level entity set and one ormore lower-level entity sets. In our example, employee is the higher-level entity set and instructor and secretary are lower-level entity sets. In this case, attributes that are conceptually the same had different names in the two lower-level entity sets. To create a generalization, the attributes must be given a common name and represented with the higher-level entity person. We can use the attribute names ID, name, address.

Specialization :

An entity set may include subgroupings of entities that are distinct in some way from other entities in entity set.

For instance, a subset of entities within an entity set may have attributes that are not shared by all the entities in the entity set. The e-r model provides a means for 6odeled6ting these distinctive entity grouping.

As an example, consider an entity set person, with attributes person_id, name, street and city. A person may be further classified as one of the following:

(a) customer.

(b) employee

Each of these person type is described by a set of attributes that includes all the attributes of entity set person plus possibly additional attributes.

For example,customer entities may be described further by the an attribute credit_rating, whereas employee entities may be describe further by the attribute salary. The process of designating subgroupings within an entity set is called

Specialization…….

In the terms of an E-R diagram, specialization is depicted by a triangle component labeled ISA. The label ISA stands for “is a” and represent, for example that a customer “is a” person. The ISA relationship may also b referred to as a superclass(higher level)-subclass(lower level) relationship.

Aggregation:

Aggregation is an important concept in database design where composite objects can be 6odeled during the design of database applications. Therefore, preserving the aggregation concept in database implementation is essential. In this paper, we propose models for implementation of aggregation in an Object-Relational Database Management System (ORDBMS) through the use of index clusters and nested tables. ORDBMS is a commercial Relational Database Management Systems (RDBMS), like Oracle, which support some object-oriented concepts. We will also show how queries can be performed on index clusters and nested tables.

Q-3. Write note on following: (any three)

a. Object Oriented Data Model

Ans : In the object-oriented data model, information is organized in graphs of objects, where eachobject has a number of attributes. Attributes can be simple values, complex values (partobjects), references to other objects, or methods. Objects are instances of classes, andclasses are (possibly) related to each by means of inheritance. The inheritancemechanism supports generalization and specialization and offers many aspects ofstructured reuse of models. Inheritance also offers the mechanism for qualifiedpolymorphism, since the resulting type system can allow for objects to be recognized asbelonging to several different types, namely the types of all the classes in the inheritancehierarchy which lies on the path from the instantiating class to the root of the hierarchy.A method of an object is a specification (code) of a functionality, typically manipulationsof the other attributes in the same object, but may also invoke methods, associated with

other objects, and thus change the state of these other objects.

An important aspect of object-oriented data models is the notion of object identity:Objects has an identity (often called OID) which is totally independent of the state ofthe object. That is, we can have two objects with exactly the same state (same values inall attributes), but they will still in the object system be treated as two distinct objects,with separate identities.Object modeling describes systems as built out of objects: programming abstractionsthat have identity, behavior, and state. Objects are an abstraction beyond abstract datatypes (ADTs), where data and variables are merged into a single unifying concept. Assuch object modeling includes many other concepts: abstraction, similarity,

encapsulation, inheritance, modularity, and so on.

IDENTITYObjects have identity. This is the crucial step for describing how objects are differentfrom ADTs. When an object is created it is distinguishable from all other objectswhether their happens to be identical.STATEAn object has a state, defined recursively by the state of its attributes. Objects can gothrough none or many state transitions during its lifetime. Often objects areencapsulated, making their state an abstraction which then is only visible by examiningthe behavior of the object.BEHAVIORObjects provide an abstraction that clients can interact with. The behavior of an objectis the collection of methods (also called operations) and the response to these methodcalls (also sometimes referred to as messages). All interactions with an object must bethrough its interface.ENCAPSULATIONEncapsulation provides an abstraction and prevents external parties from seeing theimplementation details for that abstraction. For objects, clients can interact with thepublic behavior of the object (and by doing so, changing the state of an object) but theycan not see how the behavior (and the state) are implemented.TYPEA type is the specification of an interface that objects may support. An objectimplements a type if it provides the interface described by the type. All object of thesame type can be interacted with through the same interface. An object can implementmultiple types at the same time.ASSOCIATIONSTypes can be associated with other types, which specifies that the objects of one typecan be linked to objects of the other type. Having a link provides the ability to traversefrom one object to the other objects involved in the link.CLASSA class defines what types the objects will implement, how to perform the behaviorrequired for the interface and how to remember state information. Each object will thenonly need to remember its individual state. INHERITANCEInheritance can apply to types or to classes. When applied to types, inheritance specifiesthat object of Type B that inherits from Type A can be used just like an object of Type A.Type B is said to conform to Type A and all objects that are Type Bs are also Type As.When applied to Classes, inheritance specifies that a class uses the implementation ofanother class with possible overriding modification. This frequently implies typeinheritance also but that is not always the case.The object-oriented data model thus deals with both the data and the dynamic andbehavioral aspects of data modeling. It should however be noted, that methods only toa very limited extend supports consistency and business rules (as triggers do).

b. Process of Normalization

Ans : The process of normalization is a formal method that identifies relations based on their primary or candidate keys and the functional dependencies among their attributes.

Normalization is a technique that 1 Decomposes data into two dimensional tables.2 Eliminates any relationships in which table data does fully depend

upon the primary key of a record.3 Eliminates any relationship that contains transitive dependencies.

Forms of normalization :- 1 1NF [First Normal Form]2 2NF [Second Normal Form]3 3NF [third Normal Form]4 4 4NF [Fourth Normal Form]5 BCNF[Boyce codd Normal Form]

1 1NF [First Normal Form]:- Remove horizontal redundancies No two column hold the same information Each row must be unique Use a primary key

o Benefits:-

Easier to query sort the data More scalable Each row can be identified for updating.

o For example:- Customer:-

Cust_idFnameSurname

123AshishPrajapat

456RaviChauhan

789ManishPatel

Customer telephone number:-Cust_id

Telephone number

123555-861-2025

456555-403-1659

456555-776-4100

789555-808-9683

2 2NF [Second Normal Form]:- Table must be in first normal form Remove vertical redundancy

Composite keys:- All columns in a row must refer to both

parts of the key

oBenefits:- Increased storage efficiency Less data repetition

oFor example:-Table: emp_proj:-

FieldKey

Project numberPk

Employee namePk

Table: empfieldKey

Employee numberPk

Employee name -

Rate category-

Hourly rate-

Table: proj :-fieldKey

Project numberPk

Project name-

3 3NF [third Normal Form]:- Table must be in second normal form If your table is 2nf,there is a good chance it is 3nf All columns must relate directly to the primary key

oBenefits :- No extraneous data

oFor example:-Table: emp_proj

FieldKey

Project numberPk

Employee numberPk

Table: empFieldKey

Employee numberPk

Employee name-

Rate category-

Table: rateFieldKey

Rate categoryPk

Hourly rate-

Table: projFieldKey

Project numberPk

Project name-

4 BCNF[Boyce codd Normal Form]:- A relational in BCNF, if and only if every determinant is a

candidate key There exits relations that are in 3nf but not in BCNF The goal is to have each relation in BCNF

5 4NF [Fourth Normal Form]:- A relation that is in Boyce codd normal form and contains no

non-trivial multi-valued dependencies. It dictates that no table can contain two or more one to many or

many to many relationships that are not directly related.C. Data Abstractions.

Data Abstraction

For the system to be usable, it must retrieve data efficiently. The need for efficiency has led designers to use complex data structures to represent data in the database. Since many database-systems users are not computer trained, developers hide the complexity from users through several levels of abstraction, to simplify users’ interactions with the system:

Physical Level : The lowest level of abstraction describes how the data are actually stored. The physical level describes complex low-level data structures in detail.

Logical Level : The next-higher level of abstraction describes what data are stored in the database, and what relationships exist among those data. The logical level thus describes the entire database in terms of a small number of relatively simple structures. Although implementation of the simple structures at the logical level may involve complex physical-level structures, the user of the logical level does not need to be aware of this complexity. Database administrators, who must decide what information to keep in the database, use the

logical level of abstraction.

The three levels of data abstraction View Level : The highest level of abstraction describes only part of

the entire database. Even though the logical level uses simpler structures, complexity remains because of the variety of information stored in a large database. Many users of the database system do not need all this information; instead, they need to access only a part of the database. The view level of abstraction exists to simplify their interaction with the system. The system may provide many views for the same database.

D. Loss-less join Decomposition

Ans:Decomposition –• the process of breaking down in parts or elements.• Decomposition in database means breaking tables down into multiple tables• From Database perspective means going to a higher normal form

Lossless means functioning without a loss.In other words, retain everything.

Important for databases to have this feature.

Formal Definition -• Let R be a relation schema.• Let F be a set of functional dependencies on R.• Let and form a decomposition of R.• The decomposition is a lossless-join decomposition of R if at least one of the

following functional dependencies are in F+

1) R1 ∩ R2 R12) R1 ∩ R2 R2

In Simpler Terms…

• R1 ∩ R2 R1• R1 ∩ R2 R2

If R is split into R1 and R2, for the decomposition to be lossless then at least one of the two should hold true.Projecting on R1 and R2, and joining back, results in the relation you started with

Ensures that attributes involved in the natural join (R1 ∩ R2) are a candidate key for at least one of the two relations. This ensures we can never get the situation where false tuples are generated, as for any value on the join attributes there will be a unique tuple in one of the relations.

Ex: R(A B C D E)• FD1 = (A B)• FD2 = (BC E)• FD3 = (ED A)

R1=(AB); R2=(ACDE);

E. Comparison of DBMS and File System

Ans: A Database Management System (DBMS) is a combination of computer software, hardware, and information designed to electronically manipulate data via computer processing. Two types of database management systems are DBMS’s and FMS’s. In simple terms, a File Management System (FMS) is a Database Management System that allows access to single files or tables at a time. FMS’s accommodate flat files that have no relation to other files. The FMS was the predecessor for the Database Management

System (DBMS), which allows access to multiple files or tables at a time

Database Management Systems

AdvantagesDisadvantages

Simpler to useTypically does not support multi-user access

Less expensive·Limited to smaller databases

Fits the needs of many small businesses and home usersLimited functionality (i.e. no support for complicated transactions, recovery, etc.)

Popular FMS’s are packaged along with the operating systems of personal computers (i.e. Microsoft Cardfile and Microsoft Works)Decentralization of data

Good for database solutions for hand held devices such as Palm PilotRedundancy and Integrity issues

File Management Systems

Advantages

Disadvantages

Simpler to use

Typically does not support multi-user access

Less expensive·

Limited to smaller databases

Fits the needs of many small businesses and home users

Limited functionality (i.e. no support for complicated transactions, recovery, etc.)

Popular FMS’s are packaged along with the operating systems of personal computers (i.e. Microsoft Cardfile and Microsoft Works)

Decentralization of data

Good for database solutions for hand held devices such as Palm Pilot

Redundancy and Integrity issues

Q-4. Answer following :( any two) 12

a. Describe anomalies in database during insertion, deletion and updation .

Ans. One goal of schema design is to minimize the storage space

o The previous example not only waste spaces but also cause some anomalies:

Insert Anomalyo Delete Anomalyo Modify Anomaly

Insert Anomaly:o For a new employee, you have to assign NULL to projectso Cannot insert a project unless an employee is assigned to it.

Delete Anomaly:o If we delete from EMP_DEPT an employee tuple that happens to

represent the last employee, the information containing that department is lost from the database

Modify Anomaly:o If we change the value of the manager of department 5, we must update

the tuples of all employees who work in the department

o Design a schema that does not suffer from the insertion, deletion and update anomalies.

o If there are any anomalies present, then note them so that applications can be made to take them into account.

b. What are the important issues related to decomposition?

Ans.

C. Explain various DML, DDL and DCL providing appropriate examples.

Database Systems: Data Definition Language (DDL)

1. Used to specify a database scheme as a set of definitions expressed in a DDL

2. DDL statements are compiled, resulting in a set of tables stored in a special le called a data dictionary or data directory.

3. The data directory contains metadata (data about data)

4. The storage structure and access methods used by the database system are speci ed by a set of definitions in a special type of DDL called a data storage and definition language.

5. basic idea: hide implementation details of the database schemes from the users.

Database Systems: Data Manipulation Language (DML)

1. Data Manipulation is:

retrieval of information from the database insertion of new information into the database

deletion of information in the database

modi cation of information in the database

2. A DML is a language which enables users to access and manipulate data.

The goal is to provide ecient human interaction with the system.

3. There are two types of DML:

procedural: the user speci es what data is needed and how to get it

nonprocedural: the user only speci es what data is needed

Easier for user

May not generate code as ecient as that produced by procedural languages

4. A query language is a portion of a DML involving information retrieval only. The terms DML and query language are often used synonymously.

Database Systems: Database Manager

1. The database manager is a program module which provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system.

2. Databases typically require lots of storage space (gigabytes). This must be stored on disks. Data is moved between disk and main memory (MM) as needed.

3. The goal of the database system is to simplify and facilitate access to data. Performance is important. Views provide simpli cation.

4. So the database manager module is responsible for

Interaction with the file manager: Storing raw data on disk using the le system usually provided by a conventional operating system. The database manager must translate DML statements into low-level le system commands (for storing, retrieving and updating data in the database).

Integrity enforcement: Checking that updates in the database do not violate consistency constraints (e.g. no bank account balance below $25) .

Security enforcement: Ensuring that users only have access to information they are permitted to see.

Backup and recovery: Detecting failures due to power failure, disk crash, software errors, etc., and restoring the database to its state before the failure.

Concurrency control: Preserving data consistency when there are concurrent users.

5. Some small database systems may miss some of these features, resulting in simpler database managers. (For example, no concurrency is required on a PC running MS-DOS.) These features are necessary on larger systems.

Q-5. Consider following relations database and solve the queries: (any three) 9

Bill-Master (Bill_no, Cast_code, Bill_date, Bill_amt, Discount, Final_bill)Cast_Master (Cast_code, Cast_name, Cast_address)

a. Name the customer who visited the shop and purchased material for more than five times.Ans : Select C_name ,From

B Name the customers who have not purchased anything in last two weeks.

Ans :

C. Calculate total Discount given in current month.

Ans :

D. Display Name of Customer and total amount of purchase so far.

Ans:

E. Display Bill detail for that Customer who has purchased items for less than three times.Ans :