Page 1 :
Database Management System, 1.1 DATA, , Data are raw or isolated facts from which the required information is produced., Data are distinct pieces of information, usually formatted in a special way., Examples of data :, In Employer's Mind, ID, , Emp-name, Department, DOB, Qualification, , In Sales Person's View, , Customer name, Customer Account, Address, City, Ph Number, State, , ., , 1.1.1 Three-layer Data Architecture, , Data is organized in the following layered structure :, • Operational data, • Reconciled data, • Derived data, Operational data : Operational data are stored in various operating system throughout the, organization (both internal and external) system., Reconciled data : Reconciled data are stored in the organization data warehouse and in, operational data store. They are detailed and current data, which is intended as the single, authoritative, source for all decision support application., Derived data : Derived data are stored in each of the data mart. Derived data are selected,, formatted and aggregated for end-user decision support application., r----------------, , Enterprise, (Organization), Data Model, , I+---~

Page 2 :
2, , DATABASE MANAGEMENT SYSTEMS, , 1.2 INFORMATION, , Data and information are closely related and are often used interchangeably. Information is, processed, organized or summarised data., It may be defined as collection of related data that when put together, communicate meaningful, and useful message to a recipient who uses it, to make decision or to interpret the data to get the, meaning., Data are processed to create information, which is meaningful to the recipient., For example, from the salesperson's view, we might want to know the current balance of a, customer Mis Waterhouse Ltd. or perhaps we might ask for the average current balance of all the, customers in India. The answers to such questions are information., Thus, information involves the communication and reception of knowledge or intelligence. It, reduces uncertainty reveals additional alternatives or helps in eliminating irrelevant or poor ones,, influences individuals and simulates them into action., 1.3 DATA WAREHOUSE, , Data warehouse is a collection of data designed to support management in the decision making, process. It is a subject oriented, integrated, time-varient, non-up datable collection of data used in, support of management decision-making processes and business intelligence. It contains a wide variety, of data that present a coherent picture of business condition at a single point of time. It is a unique, kind of database which focuses on business intelligence, external data and time-varient data., Data warehousing is the process, where organization extract meaning and information decision, making from their information assets through the use of data warehouses., 1.4 DATA DICTIONARY, , Data dictionary are mini database management systems that manages metadata. It is a repository, of information about a database that documents data elements of a database. The data dictionary is, an integral part of the database management systems and stores metadata or information about the, database, attribute names and definitions for each table in the database., Data dictionary is usually a part of the system catalog that is generated for each database. A, useful data dictionary system usually stores and manages the following types of information :, • Descriptions of the schema of the database., • Detailed information on physical database design, such as storage structures, access paths and, file and record sizes., • Description of the database users, their responsibilities and access rights., • High-level descriptions of the database transactions & applications and of the relationships of, users to translations., • The relationship between database transactions and the data items referenced by them. This, is useful in determining which transactions are affected when certain data definitions are, changed., 1.5 RECORDS, , A record is a collection of logically related fields or data items, with each field processing a fixed, number of bytes and having a fixed data types. A record consists of values for each field. The grouping, of data items can he achieved through different ways to form different records for different purposes., These records are retrieved or updated using programs.

Page 3 :
3, , DATABASE MANAGEMENT SYSTEM, , 1.6 FILES, , A file is a collection of related sequence of records. In many cases, all records in a file are of, the same record type (each record having an identical format). If every record in the file has exactly, the same size in bytes, the file is said to be made up of fixed-length records. If different records in the, file have different sizes, the file is said to be made of variable-length records., 1.7 DATABASE, , A database is defined as a collection of logically related data stored together that is designed to meet, the information needs of an organization., Database can further be defined as, it :, (i) is a collection of interrelated data stored together without harmful or unnecessary, redundancy., (ii) serves mUltiple applications in which each user has his own view of data. This data is, protected from unauthorized access by security mechanism and concurrent access to data is, provided with recovery mechanism., (iii) stores data independent of programs and changes in data storage structure or access strategy, do not require changes in accessing programs or queries., A database consists of the following four components as shown in fig., (i) Data item, (ii) Relationships, (iv) Schema, (ii) Constraints, 1---------------------------------------------------------------------i, Data items, Relationships, Constraints, Schema, , !I, ~, , I I, , I I, , I I, , Ii, , __________________________________ __________________________________ J, , Physical, Database, 1.8 DATABASE MANAGEMENT SYSTEM, , A Database Management system is a collection of interrelated data and a set of programs to, access those data., 1.8.1 Applications of Database System, , These are following applications of the database., • Banking, • Airlines, • University, • Railways, • Finance, • Sales, • Telecommunications, • Pay Roll System, • Manufacturing, • Human Resources

Page 4 :
4, , 1.8.2, , DATABASE MANAGEMENT SYSTEMS, , Functions and Services of DBMS, , A DBMS performs several important functions that guarantee integrity and consistency of data, in the database., (1) Data Storage Management: The DBMS creates the complex structures required for data, storage in the physical database. It provides a mechanism for management of permanent storage of, the data., (2) Transaction Management: A transaction is a series of database operations, carried out by, a application program, which access or changes the contents of the database. Therfore, a DBMS must, provide a mechanism to ensure either that all the updates corresponding to a given transaction are, made or that none of them is made., (3) Integrity Services: Database integrity refers to the correctness and consistency of stored, data and is specially important in transaction oriented database system. Therefore, a DBMS must, provide to ensure that both the data in database and changes to the data follow certain rules. This, minimises data redundancy and maximises data consistency. The data relationship stored in the data, dictionary are used to enforce data integrity. Various types of integrity mechanisms and constraints, may be supported to help ensure that the data values within the database are valid, that the operations, performed on those values are valid and that the database remains in a consistent state., (4) Backup and Recovery Management: The DBMS provides mechanisms for different types, of failures. This prevents the loss of data. The recovery mechanisms of DBMS, make sure that the, database is returned to a consistent state after a transaction fails or aborts due to a system crash, media, failure, hardware or software errors, power failure, and so on., (5) Concurrency Control Services: Since DBMS support sharing of data among multiple users,, they must provide a mechanism for managing concurrent access to the database. DBMS's ensure that, the database is kept in consistent state and that the integrity of the data is preserved. It ensures that, the database is updated correctly when mUltiple users are updating the database concurrently., (6) Data Manipulation Management: DBMS furnishes users with the ability to retrieve,, updata and delete existing data in the database or to add new data to the database. It includes DML, processor component to deal with the data manipulation language (DML)., (7) Data Dictionary/System Catalog Management: The DBMS provides a data dictionary or, ~ystem catalog function in which descriptions of data items are stored and which is accessible to users., System catalog or data dictionary is a system database, which is a repository of information describing, the data in the database. It is the data about the data or metadata. For example, the DBMS will consult, the system catalog to verify that a requested table exists and that the user issuing the request has the, necessary access privileges., (8) Authorisation/Security Management : The DBMS protects the database against, unauthorized access, either intentiomil or accidental. It furnishes mechanism to ensure that only, authorized users can access the database. It creates a security system that enforces user security and, data privacy within the database. Security rules determine which users can access the database, which, data items each user may access and which data operations (add, delete, and modify) the user may, perform., (9) Utility Services: The DBMS provides a set of utility services used by the DBA and the, database designer to create, implement, monitor and maintain the database. These utility services help, the DBA to administer the database effectively., (Hl) Database Access and Application Programming Interfaces : All DBMSs provides

Page 5 :
DATABASE MANAGEMENT SYSTEM, , 5, , interface to enable applications to use DBMS services. They provide data access via structured query, language (SOL). The DBMS query language contains two components:, • A data definition language (DDL), • A data manipulation language (DML), DDL defines the structure in which the data are stored and the DML allows end user to extract, the data from the database., The DBMS also provides data access to application programmers via procedural languages such, as C, C++, Java and others., (11) Data Independence Services: The DBMS must support the independence of programs, from the actual structure of the database., (12) Data Definition Services: The DBMS accepts the data definitions such as external, schema, the conceptual schema, the internal schema, and all the associated mapping in source form., It converts them to the appropriate object form using a DDL processor component for each of the, various data definition languages (DDLs)., 1.8.3 Database Vs. File Systems, A file is a sequence of records., • All records in a file are of the same record type., • File-processing system is supported by a conventional operating system. The system stores, permanent records in various files, and it needs different application program to extract, records from the appropriate files and add record to appropriate files., 1.8.4 Advantage of File Processing System, Although the file-processing system is now largely obsolete, following are the advantages of file, processing system., • It provides a useful historical perspective on. how to handle data., • The characteristics of a file-based system helps in an overall understanding of design, complexity of database system., • Understanding the problems and knowledge of limitation inherent in the file based system, helps avoid these same problems when designing database systems and thereby resulting in, smooth transition., 1.8.5 Disadvantages of File Processing Systems, 1. Excessive programming Effort : A new application program often required an entirely new, set of file definition. Even though an existing file may contain some of the data needed, the application, often required a number of other data items. As a result, the programmer had to recode the definitions, of needed data items from the existing file as well as definitions of all new data items. Thus in, file-oriented systems, there was a heavy interdependence between programs and data., 2. Data Inconsistency: Data Redundancy also leads to data inconsistency, since either the data, formats may be inconsistent or data values may no longer agree or both., 3. Limited data sharing : There is limited data sharing opportunities with the traditional file, oriented system. Each application has its own private file and users have little opportunity to share, data outside their own applications. To obtain data from several incompatible files in separate systems, will require a major programming effort., 4. Poor data control : A file-oriented system being decentralised in nature, there was no, centralised control at the data element (field) level. It could be very common for the data field to have, multiple names defined by the various departments of an organisation and depending on the file it was

Page 6 :
6, , DATABASE MANAGEMENT SYSTEMS, , in. This could lead to different meaning of a data filed in different context, and conversely, same, meaning for different fields. This leads to a poor data control, resulting in a big confusion., S. Inadequate data manipulation capabilities: Since file-oriented system do not provide strong, connections between data in different files and therefore its data manipulation capability is very limited., 6. Data Redundancy (or duplication) : Application are developed independently in file, processing systems leading to unplanned duplicate files. Duplication is wasteful as it requires additional, storage space and changes in one file must be made manually in all files. This also results in loss of, data integrity . It is also possible that the same data item may have different names in different files,, or the same name may be used for different data items in different files., 7. Atomicity problems: Atomicity means either all operations of the transactions are reflected, propertIy in the database or none are, i.e., if everything works correctly without any errors, then, everything gets committed to the database. If anyone part of the transaction fails, the entire transaction, gets rolled back. The funds transfer must be atomic - it must happen in its entire or not at all. It is, difficult to ensure atomicity in a conventional file processing system., 8. Security problems : The problm of security in file processing is unauthrorized person can, retrieve, modify, delete, or insert data in file system. But this is not possible in DBMS., 9. Integrity problems : The data values stored in the database must satisfy certain types of, consistency constraints. Developers enforce these constraints in the system by adding appropriate code, in the various application program. When new constraints are added, it is difficult to change the, program to enforce them. The problem is compounded when constraints involves several data items, for different files., 10. Program Data Dependance : File descriptions (physical structure, storage of the data files, and records) are defined within each application program that accesses a given file., 11. Data isolation : Becuuse data are scattered in various files, and files may be in different, formats, writing new application program to retrieve the appropriate data is difficult., 12. Difficulty in accessing data : The conventional file processing environments do not allow, needed data to be retrieved in a convenient and efficient manner like DBMS. Better data retrieval, system must be developed for general use., 13. Concurrent access anomalies : In order to improve the overall performance of the system, and obtain a faster respone time, many system allow multiple users to update the data simultaneously., In such an environment, interaction of concurrent updates may result in inconsistent data., 1.8.6 Advantages of DBMS, 1. Controlling the data redundancy: The data redundancy, storing the same data multiple times, leads to several problems., First, storage space is wasted when the same data is stored repeatedly., Second, files that represent the same data may become inconsistent. This may happen because, an update is applied to some of the files but not to others., Most DBMS provide a facilities for controlling the data redundancy using normalization and keys, concepts., 2. Restricting unauthorized access: When multiple users access a database therefore some users, will not be authorized to access (!.ll informations in the database., A DBMS should provide a security and authorization subsystem, which the Data Base Administor, (DBA) specify the restrictions. The DBMS should then enforce these restrictions automatically. For, example, the Banking data are often considered confidential, and hence only authorized persons are, allowed to access such data.

Page 7 :
DATABASE MANAGEMENT SYSTEM, , 7, , 3. Providing backup and Recovery : A DBMS must provide facilities for recovering from, hardware or software. The backup and recovery subsystem of DBMS is responsible for recovery., For example, if the computer system fails in the middle of a complex update program, the, recovery subsystem is responsible and makes sure that the database is restored to the state it was in, before the program started executing. Alternatively, the recovery subsystem ensures that the program, is resumed from the point at which it was interrupted so that its full effect is recorded in the database., 4. Providing Multiple user Interfaces : Because many types of users with varying levels of, technical knowledge use a databae, a DBMS should provide a variety of user interfaces. These include, query languages for casual users. Programming language interfaces for application programmers, forms, and command codes for parameteric users and graphical user interfaces for stand alone users., 5. Inforcing Integrity constraints : Data integrity means that the data contained in the database, is both accurate and consistent. Integrity means constraints, which are consistency rules that the, database system should not violate., Most database applications have certain integrity constraints that must hold for the data. A, DBMS should provide capabilities for defming and enforcing these constraints. The simplest type of, integrity constraint specifying a data type for each data item., 6. Efficient data access: DBMS utilizes a variety of sophisticated techniques to store and retrieve, data efficiently. This feature is specially important if the data is stored on external storage devices., 7. Improved the data sharing: Since, database system is a centralised repository of data, belonging to the entire organization, it can be shared by all authorized users. Existing application, program can share the data in the database. Furthermore, new application programs can be developed, on the existing data in the database to share the same data and add only that data that is not currently, stored. Therefore, more users and applications can share more of the data., 8. Improved security: Database security is the protection of database from unauthorized users., The database administrator (DBA) ensures that proper access procedure is followed, including proper, authentication schemes for access to the DBMS and additional checks before permitting access to, sensitive data. A DBA can define user names and passwords to identify people authorized to use the, database., 9. Improved data consistency: If the redundancy is removed or controlled, chances of having, inconsistence data is also removed and controlled. In database system, such inconsistencies are avoided, to some extent by making them know to DBMS. DBMS ensures that any change made to either of the, two entries in the database is automatically applied to the other one as well. This process is known as, propagating updates., 10. Program data Independence: In the database environment, it allows for changes at one level, of the database without affecting other levels. These changes are absorbed by the mapping between, the levels with the database approach, metedata are stored in a central location called reprository. This, property of the data systems allows an organization's data to change without changing the application, programs that process the data., 11. Improved data quality: The database system provides a number of tools and processes to, improve the data quality., 12. Providing persistant storage for program objects and data structures: Database can be used, to provide persistent storage for program objects and data structures. This is one of the main reasons, for object oriented database systems. The persistent storage of program objects and data structures, are an important function of database system., 13. Representing complex relationships among data: A database may include numerous varieties

Page 8 :
8, , DATABASE MANAGEMENT SYSTEMS, , of data that are interrelated in many ways. A DBMS must have the capability to represents a varity of, complex relationships among the data as well as to retrieve and update data easily and efficiently., 14. Permitting inferencing and actions using rules: Some database system provide capabilities, for defining duduction rules for inferencing new information from the stored database facts. Such, systems are called deductive database systems. More powerful functionality is provided by active, database system, which provide active rules that can automatically initiate actions when certain events, and conditions occur., 15. Availability of up-to-date informatin to all users: A DBMS makes the database available to, all users. As soon as one user's update is applied to the database, all other users can immediately see, this update. This availability of up-to-date information is essential for many transaction-processing, applications, such as reservation systems, banking databased and it is made possible by the concurrency, control and recovery subsystems of a DBMS., 16. Flexibility : It may be necessary to change the structure of a database as requirements, changes. Modern DBMSs allows certain types of evolutionary changes to the structure of the database, without affecting the stored data and the existing application programs., 17. Increased concurrency: DBMSs manage concurrent database access and prevents the, problem of loss of information or loss of integrity., 18. Balance of conflicting requirments : The DBA resolves the conflicting requirements of various, users and applications. A DBA can structure the system to provide an overall service that is the best, for the organization. A DBA can choose the best file structure and access methods to get optimal, performance for the response-critical operations, while permitting less critical applications to continue, to use the database., , 1.8.7 Disadvantages of DBMS, There are the following disadvantages of DBMS., 1. Complexity of Backup and Recovery : For a centralised shared database to be accurate and, available all times, a comprehensive procedure is required to be deVeloped and used for providing, backup copies of data and for restoring a database when damage occurs. A modern DBMS normally, automates many more of the backup and recovery tasks than a file oriented system., 2. Increased installation and management cost: The large and complex DBMS software has a, high initial cost. It requires trained manpower to install and operate and also has substantial annual, maintenance and support cost. Additional database software may be needed to provide secur~ty and, to ensure proper concurrent updating of shared data., 3. Additional hardware cost: The cost of DBMS installation varies significantly, depending on, the environment and functionality, size of the hardware and the recurring annual maintenance cost of, hardware and software., 4. Requirement of new and specialized manpower : Because of rapid changes in database, technology and organization's business needs, the organization's need to hire, retrain its manpower on, regular basis to design and implement databases, provide database administration services and manage, a staff of new people. Therefore, an organization needs to maintain specialized skilled manpower., 5. Increased complexity: A multi-user DBMS becomes an extremely complex piece of software, due to expected functionality from it. It becomes necessary for database designers, developers,, database adminstrators and end-users to understand this functionality to full advantage of it., 6. Problems associated with centralization: Centralization means that the data is accessible from, a single source called database.

Page 9 :
9, , DATABASE MANAGEMENT SYSTEM, , 7. Large size of DBMS: The large complexity and wide functionality makes the DBMS an, extremely large piece of softare. It occupies many gigabytes of storage disk space and requires, substaintial amounts of main memory to run efficiently., 1.9 DATA ABSTRACTION, There are three level of data abstraction., (1) Physical Level : The physical level of data abstraction describes how the data are actually, stored., (2) Logical Level : The logical level of the data abstraction describes "what" data are stored in, the database and what relationships exist among those data., Thus logical level describes the entire database., • Database administrators, who must decide what information to keep in the database, use the, logical level of abstraction., , View Level, 1 View 1 I, , 1View 21 -------- 1View n 1, , (3) View level: The view level of data abstraction describes only part of the entire database., The view level of abstraction simplify their interaction with the system. The system may provide, many views for the same database., View of Data : A database system is a collection of integrated files and a set of programs that, allows users to access and modify these fIles., 1.10 INSTANCES AND SCHEMAS, , Instances : The collection of information stored in the database at a particular moment is cal!ed, an instance of the database., Schemas : The overall design of the database is called the database schema., That is, the description of a database is called the database schema which is specified during, database design and is not expected to change frequently., The database schema can be partitioned according to the level of abstraction., (a) Physical or Internal Schema: The physical schema, describes the physical storage structure, of the database., Internal level is concerned with the following activities :, (1) Storage space allocation for data and storage., (2) Record descriptions for storage with stored size for data items., (3) Record Placement., (4) Data compression and data encryption techniques., • This schema uses a physical data model and describes the complete details of data storage, and access path for the database.

Page 10 :
10, , DATABASE MANAGEMENT SYSTEMS, , (b) The conceptual or logical schema : The logical schema describes the structure of the whole, database for a community of users., The conceptual schema describes the entities, data types, relationships, user operations and, constraints and hides the details of physical storage structure., End Users, View level, (defined by user or, application program), , Logical level, , .., , External, , ----------------, , ..., , (defined by DBA), , Internal level, (defined by DBA), , .., , Internal, schema, , The three schema Architecture, The conceptual level is concerned with the following activities :, (i) All entities, their attributes and their relationships., (ii) Constraint on the data., (iii) Semantic information about the data., (iv) Security information., (v) Checks to retain data consistency and integrity., (c) External Schema: Each external schema describes the part of the database that a particular, user group is interested in and hides the rest of the database from that user group., 1.11 DATA INDEPENDENCE, The ability to change thc schema at one level of a database system without having to change the, schema at the next higher level is called "Data Independence"., There are two types of data independence :, (1) Physical Data Independence: Physical data Independence is the ability to change the internal, schema without having to change the conceptual schema., c.g., By creating additional access structure to improve the performance of the retrival or, update., (2) Logical Data Independence : The logical Data Independence is the ability to change the, conceptual schema without having to change application programs (external schema)., c.g., We may change the conceptual schema to expand the database by adding a record types, or data items. OR to reduced the database by removing data item.

Page 11 :
11, , DATABASE MANAGEMENT SYSTEM, I~-----------------------------------------------------------------------~I, , :, I, : External schema, , User View, :, I, External schema, External schema :, ~----------------- ---------------- ---------------- -----------------~, , I, , I, , IPhysical data independence>, , Physical database, , uuu, 1.12 DATA MODELS, ')\ collection of concepts that can be used to describe the structure of a database., The structure of a database means the data types, relationships and constraints that should hold, on the data., The following data models are :, 1.12.1 The Entity-Relationship Model, , The E-R data model is based on a p~rception of a real world that consists of a collection of basic, objects called entities and relationship among these objects., The overall logical structure of a database can be represented graphically by E-R diagram., The E-R diagram is build up from the following components., • Rectangle: which represent entity sets, • Ellipses: which represent attributes, • Diamonds: which represent relationship among entity sets., • Lines: which link attributes to entity sets and entity sets to relationships., Double ellipses : which represent multivalued attributes., Dashed Ellipses : which denote derived attributes., Double Lines : which represent total participates of an entity in a relationship set., Double Rectangle : which represent weak entity sets., (, , )-, , Represent the key attribute

Page 12 :
12, , DATABASE MANAGEMENT SYSTEMS, , Advantages, (1) Straight forward relational representation: Having designed an E-R diagram for a database, application, the relational representation of the database model becomes relatively straight, forward., (2) Easy Conversion for E-R to other data model: Conversion from E-R diagram to a network, or hierarchial data model can easily be accomplished., (3) Graphical representation for better understanding., Disadvantages, (1) No industry standard for notation: There is no industry standard notation for developing, an E-R diagram., (2) Popular for high-level design: The E-R data model is high-level database design., 1.12.2 Relational Model, The relational model uses a collection of tables to represent both data and the relationships, among those data., A table is a collection of rows and columns. Each column has a unique name., Each row in the table represents a collection of related data values. In the relational model, a, row is called a tuple, a column header is called an attribute and the table is called a relation., , II' Relation, Student, , Attributes, name ,///' ,,/', , \<. . . . . . . . . . ., , rr, , \, , RolCNo Name, , ,, , Address Age, , Gr. Noida, Vijay, Santosh, Delhi, Noida, Gopal, Sanjay, Varanasi, , 1, , 2, 3, 4, , .........., , 22-,, 20- -", ::::~=. Tuples, 23 - -"""", 24-', , Advantages of Relational Model, (1) Simplicity: A relational data model is even simpler thau hierarchical and network models., It frees the designers from the actual physical data storage details, thereby allowing them to concentrate, on the logical view of the database., (2) Structural independence : The relational data model does not depend on the navigational, data access system. Changes in the database structure do not affect the data access., (3) Ease of design, implementation, maintenance and uses., (4) Flexible and powerful query capability., Disadvantages, (1) Hardware overheads: The relational data models need more powerful computing hardware, and data storage devices to perform RDBMS-assigned tasks., (2) Easy to design capability leading to bad design., 1.12.3 Object-Oriented Data Model, The object-oriented data model, paradigm.-, , IS, , based on the object-oriented- programming language

Page 13 :
DATABASE MANAGEMENT SYSTEM, , 13, , The object-oriented paradigm is based on the Encapsulation of data and code related to an object, • into a single unit, inheritance and object-identity., e.g.,, class employee, , {, string name;, string address;, int salary;, int annual salary ( );, , };, , Advantages of Object-Oriented Data Model, (1) Improved data access : Object-oriented data model represents relationships explicitly,, supporting both navigational and associative access to information. It improves the data access, performance., (2) Improved productivity: It provide powerful features such as inheritance, polymorphism and, dynamic binding that allow the users to compose objects and provide solutions without writing, object-specific code. These feature increase the productivity of the database application developers, significantly., (3) Combining object-oriented programming with database technology., (4) Capable of handling a large variety of data types., Disadvantages, (1) No precise definition: It is difficult to provide a precise definition of what constitutes on, object-oriented DBMS., (2) Difficult to maintain : The definition of object is required to be changed periodically and, migration of existing databases to conform to the new object definition with change in organizational, information needs., (3) Not suited for all applications: Object-oriented data models are used where there is a need, to manage complex relationships among data objects., (4) Hardware overheads: The relational data models need more powerful computing hardware, and data storage., 1.12.4 The Object-Relational Data Model, The object-relational data model, combines features of the rational and object oriented model., This model provides the rich types system of object-oriented databases, combines with relations, as the basis for storage of data., Object-relational database systems provide a smooth migration path for users of relational, databases who wish to use object-oriented features., 1.12.5 Hierarchical Model, The hierarchical model provides two main data structuring concepts., • Records, • Parent-child Relationships., Records : A record is a collection of field values that provide information on an entity or a, relationship instance., p.drent-child Relationship : A parent child relationship type is a 1 : N relationship between two, record types.

Page 14 :
DATABASE MANAGEMENT SYSTEMS, , 14, , • The record type on the 1-side is called the parent reecprd type and the one on the N-side is, called the child record type of the PCR type, • An instance of PCR type consists of one record of the parent record type and a number of, records (zero or more) of the child record type., For Example,' Department :, Research, , Vijay, , Narayan, , Sandeep, , Mohan, , Advantages of Hierarchical Data Model, , There are following advantages of hierarchical data model., (1) Simplicity: The relationship between various layers is logically simple and design of a, hierarchical database is simple., (2) Data sharing : Because all data are held in a common database, data sharing becomes, practical., ., (3) Data security: It was the first database model that offered the data security that is provided, and inforced by the DBMS., (4) Data integrity: The parent/child relationship, there is always a link between the parent, segment and its child segments under it., (5) Efficiency: This model is very efficient when the database contains a large volume of data in, one-lo-many (1 : m) relationships and when the users require large number of transactions., (6) Data independence: The DBMS creates an environment in which data independence can be, maintained., Disadvantages, (1) Implementation complexity: Although the hierarchical database is conceptually simple, easy, to design and no data-independence problem it is quite complex to implement., (2) Implementation limitation : Many of the common relationships do not confirm to the, one-to-many relationship format required by the hierarchical database model., (3) Inflexibility: A hierarchical database lacks flexibility. The changes in the new relations or, segments often yield very complex system management tasks., (4) Lack of struc!ural independence., (5) Application programming complexity., , 1.12.6 The Network Data Model, Data in the Network model represent the collection of records and relationship among the data, are represented by links, which can be viewed as pointers., The records in the database are organized as collection of arbitrary graphs., For example,'

Page 15 :
DATABASE MANAGEMENT SYSTEM, , 15, , Advantages of Network Data Model, • Simplicity: Similar to hierarchical data model, network model is also simple and easy to, design., • Superior data access : The data access and flexibility is superior to that is found in the, hierarchical data model., • Facilitating more relationship types : The network model facilitates in handling of, one-to-many (1: m) and many-to-many (n : m) relationships, which helps in modeling the real, life situations., • Database integrity: Network model enforces database integrity and does not allow a member, to exist without an owner., • Data independence: The network data model provides sufficient data independence by at least, partially isolating the programs from complex physical storage details., Disadvantages, • System complexity : Since network model provides a navigational access mechanism to the, data in which the data accesses one record at a time. This mechanism makes the system, implementation very complex., • Absence of structural independence: It is difficult to make changes in a network database., • Not a user friendly: The network data model is not a design for user-friendly system and is, a highly skill-oriented system., • The use of pointers leads to a complex structure, which makes mapping of related data very, difficult., 1.13 TYPES OF DATABASE SYSTEMS, The DMBS can be classified according to the number of users, the database site locations., 1. On the basis of the number of users :, • Single-user DBMS, • Multi-user DBMS, 2. On the basis of the site location :, • Centralised DBMS, • Distributed DBMS, • Parallel DBMS, • Client/Server DBMS, 1.13.1 Centralised Database System, The centralised database system consists of a single processor together with its associated data, storage devices and other peripherals. It is physically confined to a single location. The management, of the system and its data are controlled centrally form anyone or central site., A DBMS is centralised if the data is stored at a single computer site. A centralised DBMS can, support multiple users, but the DBMS and the database themselves reside totally at a single computer, site., Advantages of Centralised Database System :, • Most of the functions such as update, backup, query, control access and so on, are easier to, accomplish in a centralised database system., • The size of the database and the computer on which it reside need not have any bearing on, whether the database is centrally located.

Page 16 :
16, , DATABASE MANAGEMENT SYSTEMS, , Disadvantages, , • When the central site computer or database system goes down, then every users is blocked, from using the system until the system comes back., • Communication costs from the terminals to the central site can be expensive., , '[8], Remote User # 2, , Remote User # 1, , Local User # 4, , Local User # 3, Fig., , 1.13.2 Distributed Database System, , A distributed database is a collection of multiple logically interrelated database distributed over, a Computer Network., A distributed database management system is a software system that manages a distributed, database while making the distribution transparent to the user., In distributed database system, data is distributed across a variety of different databases., These are managed by a variety of different DBMS softwares running on a variety of different, computig machines supported by a variety of different operating systems. These machines are, distributed geographically and connected together by a variety of communication networks. In, distributed database system, one application can operate on data that is distributed geographically on, different machines. Thus, in distributed databse system, the data might be distributed on different, , Fig.

Page 17 :
DATABASE MANAGEMENT SYSTEM, , 17, , computers in such a way that data for one portion is stored in one computer and the data for another, portion is stored in another. Each machine can have data and applications of its own. However, the, users on one computer can access to data stored in serveral other computers. Therefore, each machine, will act as a server for some users and a client for others. Further detal on distributed database system, is given in Unit-So, , Advantages of Distributed Database, 1. Management of distributed data with different level of transperancy., 2. Increased Reliability and Availability., 3. Distributed query processing., 4. Improved the performance., 5. Improved scalability, 6. Parallel evalution., 7. Distributed database recovery., 8. Replicated Data management, 9. Security, 10. Network transparency., 11. It provides greater efficiency and better performance., 12. Replication transparency., Disadvantages of Distributed Database, 1. Technical problem of connecting dissimilar machine., 2. Software cost and complexity., 3. Difficulty in data integrity control., 4. Processing overhead., 5. Communication network failures., 6. Recovery from failure is more complex., 1.13.3 Parallel Database System, , Parallel database systems architecture consists of a multiple CPUs and data storage disks in, parallel. Hence, they improve processing and input/output speeds. Parallel database systems are used, in the applications that have to query extremely large databases or that have to process an extremely, large number of transactions per second., Several different architectures can be used for parallel database systems, which are as, follows:, • Shared Memory: All the processors share a common memory., • Shared data storage disk: All the processors share a common set of disks. Shared disk systems, are sometimes called clusters., • Independent Resources : The processors share neither a common memory nor common disk., • Hierarchical: This model is a hybrid of the preceeding three architectures., Fig. illustrates the different architecture of parallel database system. In shared data storage disk,, all the processors share common disk (or set of disks), as shown in Fig. (a). In shared memory, architecture, all the processors share common memory, as shown in Fig. (b). In independent resource, architecture, the processors share neither a common memory nor a common disk. They have their own

Page 18 :
DATABASE MANAGEMENT SYSTEMS, , (a) Shared memory, , (b) Shared storage disk, , Fig. Parallel database, , (c) Independent resource, , (d) Hierarchical

Page 19 :
19, , DATABASE MANAGEMENT SYSTEM, , independent resources as shown in Fig. (c). Hierarchical architecture is hybrid of all the earlier three, architectures, as shown in Fig. (d)., Advantages of Parallel Database System, 1. Parallel database systems are very useful for the applications that have to query extremely, large database or that have to process an extremely large number of transactiens per second., 2. This techniques used to speed-up transaction processing on data-server systems., 3. In a parallel database systems, the through put (that is, the number of tasks that can be, completed in a given time interval) and the response time (that is, the amount of time it takes, to complete a single task from the time it is submitted) are very high., Disadvantages of Parallel Database System, 1. In a parallel database system, there is a startup cost associated with initiating a single process, and the startup-time may overshadow the actual processing time, affecting speedup adversly., 2. Since processes executing in a parallel system often access shared resources, a slowdown may, result from interference of each new process as it competes with existing processes for, commonly held resources, such as shared data storage disks, system bus and so on., 1.13.4 Client/Server Database System, Client/Server architecture of database system has two logical components namely client and, server. Clients are generally personal computer or workstations whereas server is large workstations., The applications and tools of DBMS run on one or more client platforms, while the DBMS software, reside on the sever. The server computer is called backend and the client's computer is called frontend., These server and client computers are connected via a computer network. The applications and tools, act as clients of the DBMS, making requests for its services., The client/server architecture is a part of the open systems architecture in which all computing, hardware, operating systems, network protocols and other software are interconnected as a network, and work in concert to achieve user goals. It is well suited for online transaction processing and, Client 4, (Ap plication and Tools), , ~, t, , Server, (DBMS Software), , ./, , I, , I, , -.,, , ~, A, , "', , ,/, , to.., , ,, , !iii ~, , ~, , ":l-, , ./, , Physical, Data, , ......., , ,..;', , t, ), , Communication Network, , t, , ~, , ":l-, , ~, , Client I, (ApplicallOn and Tools), , t, ~, , '::J.-, , Z, , Client 2, (Application and Tools), , Fig., , t, ~, , '::J.-, , Z, , Client 3, (Application and Tools)

Page 20 :
DATABASE MANAGEMENT SYSTEMS, , 20, , decision support applications, which tend to generate a number of relatively short transactions and, requrie a high degree of concurrency., Advantages of Client/Server Database System, 1. Client/server environment facilitates in more productive work by the users and making better, use of existing data., 2. Client/server database system is more flexible as compared to the centralised system., 3. A single database (on server) can be shared across several distinct client (application), systems., 4. Client/server architecture provide a better DBMS performance., 5. Client/server system has less expensive platforms to support applications that had previously, been running only on mainframe computers., Disadvantages of Client/Server Database System, 1. Labour or programming cost is high in client/server environments, particularly in initial, phases., 2. There is a lack of management tools for performance monitoring and tuning and security, control, for the DBMS, client and operating systems and networking environments., 1.14 DATABASE LANGUAGE, The normal language of the database is SOL. A database system provides following types of, languages:, • Data Definition Language (DOL), • Data Manipulation Language (DML), • Data Control Language (DCL), • Data Ouery Language (DOL), Databa..e Languages, , ~, , ~, , DDL, , I, , ~, , DML, , ~, , Procedural, DML, , I, , DeL, , l, , Non-procedural, DML, , 1.14.1 Data Definition Language, A Data definition language is used to specify the database schema., e.g., The example of DOL is create, alter and drop of the tables. Such as, Create table student, (Roll number (10), Name char ( 15), Sex char (2),, Address varchar (15»;, The execution of above DOL statement creates the student table., It updates a special set of tables called the 'Data Dictionary'., A data dictionary contains the meta data means data about data., The schema (design) of a table is an example of meta data., , l, , DQL

Page 21 :
DATABASE MANAGEMENT SYSTEM, , 21, , For Example :, (i), CREATE: To create objects in the database., (ii) ALTER: Alters the structure of the database., (iii) DROP: Delete the objects from the data., (iv) TRUNCATE: Remove all records from a table, including all spaces allocated for the, records are removed., (v), COMMENT: Add comments to the data dictionary., , 1.14.2 Data-Manipulation Language (DMl), Data Manipulation means :, • The retrieval of data from the database., • The deletion of the data from the database, • The insertion of the new data into the database, • The modification of data in the database., DML is a language that enables users to access or modify the data from the database., That is DML is used to access or manipulate the data from the data base., DML is basically two types:, (i) Procedural DML, (ii) Non procedural DML, Procedural DML : Procedural DMLs require a user to specify what Data are needed and how, to get those data., e.g.,, PL/SOL, Non Procedural DML : Non procedural DMLs require a user to specify what data are needed, without specifying how to get those data., e.g.,, SOL, Example: The example of DML (NDML), (i) Select Roll, Name, Address from Student, Where Roll = 3;, (ii) Select * from student:, For example,, (i), INSERT: Insert data into a table., (ii) UPDATE: Updates existing data within a table., (iii) DELETE: Deletes all records from a table, the space for the records remain., (iv) LOCK TABLE: Control concurrency., 1.14.3 Data Control Language (DCl), It is the components of SOL statements that control access to data and to the database., For example,, (i) COMMIT: Save work done., (ii) ROLL-BACK: Restore database to original since the last commit., (iii) SAVE POINT: Identify a point in a transaction to which you can later roll back., (iv) GRANT/REVOKE: Grant or take back permissions to or from the oracle users., (v) SET TRANSACTION: Change or take back permissions to or from the oracle users.

Page 22 :
22, , DATABASE MANAGEMENT SYSTEMS, , 1.14.4 Data Query Language (DQL), It is the component of SOL statement that allows getting data from the database and imposing, ., ordering upon it., Note: DDL and DML are not two different languages it is the part of SOL., / ' DDL (create the table), SOL, '" DML (manipulate the table), Query : A query is a statement requesting the retrieval of information., 1.15 DBMS INTERFACES, User-friendly interfaces provided by a DBMS may include the following:, • Menu-Based interfaces for Browsing: They are often used in browsing interfaces, which allow, a user to look through the contents of a database in an exploratory and unstructured manner., • Forms-Based Interfaces: A form-based interface displays a form to each user., • Graphical-User Interfaces : A graphical interface displays a schema to the user in, diagrammatic form., • Natural Language Interfaces : It has its own "schema" which is similar to the data base, conceptual schema., • Interface for DBA, • Interface for User, 1.16 DATABASE USERS AND ADMINISTRATORS, The people who work with a database can be categorized as data base administrators and, database users., 1.16.1 Database Administrator, A person who has central control of both the data and the programs that access those data over, the system is called a database administrator., The Functions of DBA, These are the following functions of DBA., (1) Defining the Conceptual Schema: A DBA creates the conceptual schema (using data, definition language) corresponding to the abstract level database design made by data, administrator. The DBA creates the original database schema and structure of the, database., (2) Defining the Physical Schema: A DBA creates the physical schema (using DDL), corresponding to the abstract level database design made by data administrator., (3) Schema and Physical Organization Modification: The DBA carries out the changes or, modification to the description of the database or its relationship to the physical, organisation of the database to reflect the changing needs of the organisation or to alter, the physical organization to improve performance., (4) Granting of Authorization for Data Access : The DBA grants different types of, authorization to use the database to its users. It regulates the usage of specific parts of the, database by various users. The authorization information is kept in a special system, structure that the database system consults whenever someone attempts to access the data, in the system. DBA assists the user with problem definition and its resolution., (5) Storage Structure and Access-Method Definition: DBA decides how the data is to be

Page 23 :
DATABASE MANAGEMENT SYSTEM, , (6), , (7), , (8), , (9), , (10), (11), (13), , 23, , represented in the stored database, the process called physical database design. Database, administrator defines the storage structure of the database using data definition language, and the access method of the data from the database., Routine Maintenance: The DBA maintains periodical backups of the database, either, onto hard disk, compact disks or onto remote servers, to prevent loss of data in case of, disasters. It ensures that enough free storage space is available for normal operations and, upgrading disk space as required. A DBA is also responsible for repairing damage to the, database due to misue or software and hardware failures. DBA define and implement an, appropriate damage control mechanism involving periodic unloading of the database to, backup storage device and reloading the database from the most recent dump whenever, required., Availability, Backup and Recovery: The most important job of the DBA is that of, availability, backup and recovery of data. Bacause of the value placed on electronic data,, the database must be protected from all forms of failure such as hardware, software and, human. A DBA maintains the information on organization needs to be successful., Availability means data must be available to all who need it when they need it. If data is, not available, the business stops functioning. Since not all failure can be predicted, the DBA, needs to implement recovery procedures that will reduce downtime associated with failure., Performance Monitoring and Thning: A DBA must make sure databases are fast and, responsive. A slow response database is usually indicative poor system performancesomething is wrong somewhere. The DBA monitors the state of the database for optional, performance and the error log or event log is also monitored for database errors. Poorly, tuned databases are frustrating to use - they tend to add more stress than value. Monitoring, is essential to access the state of the database and tune accordingly., Database Implementation and Design: A critical duty of the DBA is designing databases, for maximal performance, scalability, flexibility and reliability. A well designed and, implemented database justifies the database investment. The DBA is responsible for, installing new DBMS and upgrading existing DBMS. The DBA must be conversant with, installation and upgrade issues, i.e., problems, requirements etc., Control migration of programs, database changes reference of database changes and menu, changes through the development life cycle., Creates and maintains all databases required for development, testing, and production, usage., DBA enforces and maintains constraints to ensure integrity of the database., , Skills of the DBA, , The DBA should posses the following skills:, (1) A good knowledge of the operating system., (2) A good knowledge of physical database design., (3) Ability to perform both database and also operating system performance monitoring and, the necessary adjustments., (4) Be able to provide a strategic database direction for the organization., (5) Excellent knowledge of database backup and recovery scenarios., (6) Good skill in all database tools., (7) A good knowledge of database security management., (8) A good knowledge of how database acquires and manages resources.

Page 24 :
24, , DATABASE MANAGEMENT SYSTEMS, , (9) Sound knowledge of the applications at your site., (10) Experience and knowledge in migrating code, database changes, data and menu through, the various stages of the development life cycle., (11) A good knowledge of the way database enforces data integrity., (12) A good knowledge of both database and program code performance tuning., (13) A DBA should have good communication skills with management, development teams,, vendors, system administrators and other related service providers., 1.16.2 Database Users, There are four different types of database system users., (1) Naive Users: Naive users are unsophisticated users who intract with the system by invoking, one of the application programs that have been written previously., e.g., A Bank Teller who needs to transfer Rs. 500 from account A to account B invokes a, program called transfer. This program asks teller for amount of money to be transferred., A user of an ATM falls in this category., (2) Application Programmers : Application programmers are computer professionals who write, application programs. Application programmers can choose from many tools to develop user, interfaces., Example: Rapid Application Development (RAD) tools are the tools that enable an application, programmer to construct forms and reports without writing a program., (3) Sophisticated Users: Sophisticated users interact with the system without writing programs., Instead, they form their requests in a database query language., They submit each such query to a query processor, whose function is to break down DML, statements into instructions that the storage manager understands., Analysts who submit queries to explore data in the database fall in this categories., (4) Specialized users: Specialized users are sophisticated users who write specialized database, applications that do not fit into the traditional data processing framework., • Among these applications are Computer Aided design systems, knowledge base and Expert, systems., 1.17 OVERALL DATABASE STRUCTURE, Database system is divided into modules that deal with each of the responsibilities of the overall, system. Some of the function of the database system may be provided by the computer operating, system., The functional components of a database system can be divided into :, • The storage managers, • The query processor, 1.17.1 Storage Manager, A storage manager is a program module that provides the interface between the low level data, stored in the database and the application programs and the queries submitted to the system., • The storage manager is responsible for the interaction with the fill manager., • The storage manager is important because database required a large amount of storage space., These arc following component included in the storage manager., • Authorization and Integrity Manager: It tests for the satisfaction of integrity constraints and, checks the authority of users to access data.

Page 25 :
25, , DATABASE MANAGEMENT SYSTEM, , DBA, , Users, Queries, , IQuel}' Compiler I, r:::::::--:'~···~~···-::=~:==::::::l, , Programmers, Application, , IDML Compiler I, , 1, , i System catalog! i'...----------', , i!, , Data dictional}' I.., (Metadata), i, '~.......................--. ----....---......-----,..;, ~ I---------------~-----------~~------~, ~:, Authorization control, , •, , ee,, >=:, , I r------,, , Q), , I, , ~ : Integrity lfi.-j",.r,::;--=~:",,--___:----, /L--"..lr~~===::-1, ~, , checker, , .J::J, , ee, , ~, , Q, , Q), , S, f=:, >=:, , ~, , ______ L________________________ _, Physical Database, , System catalog!, Data dictionaty, (Metadata), , Fig. Structures of DBMS., , • Transaction Manager: It ensures that the database remains in a consistent state despite system, failures and current transaction executions without conflicting., • File Manager: It manages the allocation of space on disk storage and data structures used to, represent information stored on disk.

Page 26 :
DATABASE MANAGEMENT SYSTEMS, , 26, , • ButTer Manager: It is responsible for fetching data from disk storage into main memory and, deciding what data to fetch in main memory., These are following data structures required as part of physical system implementation., (i) Data File : It store the database itself., (ii) Data Dictionary: It stores metadata about the structure of the database, in particular, the, schema of the database., (iii) Indices: It provides fast access to data items that hold particular values., (iv) Statistical Data : It stores statistical information about the data in the database., 1.17.2 Query Processor, These are following components :, (i) DML Compiler: It translate DML statement into a query language into low level instruction, that query evaluation engine understand., (ii) Embeded DML precompiler : It converts DML statements embedded in an application, program to normal procedure calls in the host language., , • The precompiler interact with DML compiler to generate the appropriate code., (iii) DDL Interpreter : It interprets DDL statements and records the deflnitions in the data, dictionary., (iv) Query Evaluation Engine: which executes low-level instructions generated by the DML, compiler., 1.18 FOURTH-GENERATION LANGUAGE (4GL), The 4GL is a compact, efflcient and non-procedural programming language that is used to, improve the productivity of the DBMS. In 4GL, the user dermes what is to be done and not how it is, to be done. The 4GL depends on higher-level 4GL tools, which are used by the users to deflne, parameters to generate an application program., The 4GL has the following components inbuilt in it :, , •, •, •, •, •, , Query languages, Report generators, Spreadsheets, Database languages, Application generators to deflne operations such as insert, retrieve and update data from the, database to build applications., • High-level languages to generate application program., , Structured Query Language (SQL) and query by example (QBE) are the example of 4GL., 1.19 METADATA, A metadata (also called the data dictionary) is the data about the data. It is also callt'{d system, catalog, which is the self-describing nature of the database that provides program-data independence., The system catalog integrates the matadata., Matadata describes the database structure, constraints, applications, authorization, sizes of data, types and so on. Matadata is available to database administrators, designers, and authorized users as, on-line system documentation.

Page 27 :
27, , DATABASE MANAGEMENT SYSTEM, , 1.19.1 Types of Metadata, • Operation Metadata : It describes the data in the various, operation systems that feed the enterprise data warehouse., Operational metadata typically exists in a number of, different formats and unfortunately are often of poor, quality., • Enterprise data warehouse (EDW Metadata) : These types, of metadata are derived from the enterprise data model., EDW meta data describe the reconciled data layer as well, as the rules for transforming operational data to reconciled, data., • Data mart Metadata : They describe the derived data layer, and the rules for transforming reconciled data to derived, data., , Metadata Layer, , r------------------------------, , I, I, I, , Operational Metadata, , I, I, I, I, I, I, , I, I, I, I, , EDW Metadata, , I, I, I, I, I, , I, I, I, I, I, , Datamart Metadata, , I, I, I, I, , _____________________________ J I, , 1.20 ER-MODEL CONCEPTS, An E-R diagram can express the overall logical structure of a database graphically., These are following elements that consist E-R model., (i) Entity set, (ii) Relationship sets, (iii) Attributes, 1.20.1 Entity, An entity is a "thing" or object in the real world that is distinguishable from other objects., e.g., Each person is an entity and bank accounts can be considered as entitites., Entity Sets : An entity set is a set of entities of the same type that share the same properties or, attributes., e.g., The set" of all persons who are customers at a given bank., That is customer is a entity set., 1.20.2 Attributes, Each entity is represented by a set of properties called attributes., Key Attributes : An entity type usually has an attribute whose values are distinct for each, individual entity in the collection. Such an attribute is called a key attribute and its values can be used, to identify each entity uniquely., Types of Attributes, Simple Attributes : Attributes that can not be divided into sub parts are called simple attributes., e.g., Roll number is example of simple attribute., • Composite Attributes : Attributes that can be divided into supports are called composite, attribute. e.g., Date of Birth is example of composite attribute, because it may be divided into, "Birth day", "Birth month" and "Birth year"., OR Name is also an example of composite attribute., Name, , j, First name, , Middle name, , Last name

Page 28 :
28, , DATABASE MANAGEMENT SYSTEMS, , Single-Valued Attributes : An attribute which can assume one and only one value is called, "Single-valued attribute"., For example: ''Age of a person is an example of single valued Attribution., Multi-valued Attribute: An attribute which may assume a set of values is called "Multi-valued, Attribute"., e.g., An Employee entity set with the attribute phone-number is an example of multivalue, attributes because an employee may have zero, one or many phone numbers., • Null Attributes: A null value is used when entity does not have value for an attribute., , e.g., A college degrees attribute applies only to persons with college degrees., • Derived Attributes : The value of this type of attribute can be derived from other related, attributes or entity., e.g., In a payroll system the HRA and PE are derived from salary attributes., , 1.21, , RELATIONSHIPS AND RELATIONSHIP SETS, • A relationship is an association among two or more entities., • A relationship set is a set of relationship of the same type of the same value., • A mathematically relation on n ~ 2 entity set. If E10 E 2, ... En are entity sets then a relationship, set are a subset of {(eh e2, ... , en)le 1 E Eh e2 E E 2, ... , en E En}., where (eh e2, e3, ... , en) is a set of relationship., , Employees, , The Work-In Relationship Set, , 1.22 CONSTRAINTS, An E-R enterprise scheme may define certain constraints to which the contents of a database, must confirm., /, , Mapping cardinalities, , Constraints, " Participation constraints, , 1.22.1 Mapping Cardinalities, Mapping cardinalities or cardinality ratios, express the number of entitities to which another, entity can be associated via a relationship st:1l: mapping cardinalities are most useful in describing binary, relationship sets., For a binary relationship set R between entity set A and B the mapping cardinality must be one, of the following :

Page 29 :
29, , DATABASE MANAGEMENT SYSTEM, , (1) One to One: An entity inA is associated with exactly one entity in B. And one entity in B is, associated with exactly one entity in A., , A, , B, , (2) One to many: An entity in A is associated with any number of entities in B. An entity in B, can be associated with at most one entity in A., , A, , B, , (3) Many to One: An entity in A is associated with at most one entity in B. An entity in B, can, be associated with any number of entity in A., , A, B, (4) Many to Many: An entity in A is associated with any number of entities in B, and an entity, in B is associated with any number of entities in A., , 1.22.2 Participation Constraints, , Participation constraints specifies whether the existence of an entity set depends on its being, related to another entity., Total, Participation constraints ( ', ....... Partial, (i) Total participate : The participation of an entity set E in a relationship set R is said to be, total if every entity in E participates in at least one relationship in R.

Page 30 :
30, , DATABASE MANAGEMENT SYSTEMS, , For Example : We expect every loan entity to be related to at least one customer through the, borrower relationship. Therefore, the participation of loan in the relationship set borrower is total., , Cust-Name, , Customer, Total participation, (ii) P-drtial Participate : If only some entities in E participate in relationships in R. The, , participation of entity set E in relationship R is said to be partial participation., For Example: An individual can be a bank customer whether or not he has a loan with the bank., Therefore the participation of customer in the borrower relationship set in partial., 1.23, , EXISTENCE DEPENDENCY, , If the existence of an entity x depends on the existence of another entity y, then x is said to be, existence dependent on y., • Operationally y is deleted then entity y is said to be dominated entity and x is said to be, subordinate entity., , ~--------~------~~~======~==========~, Weak Entity type : The entity types that do not have key attributes of their own are called weak, entity., • A weak entity type always has a total participation constraint with respect to its identifying, relationship because a weak entity cannot be identified without an owner entity, • We can represent a weak entity set by double rectangle, • We underline the discriminator of a weak entity set with a dashed line., Strong entity type : The entity type that do have a key attributes are called strong entity type., e.g.,

Page 31 :
31, , DATABASE MANAGEMENT SYSTEM, , 1.24 KEYS, Key is a field that uniquely identifies the records, tables or data., In the relational data model there are many keys. Some of these keys are :, • Primary key, • Foreign key, • Unique key, • Super key, • Candidate key, Primary Key : A primary key is one or more column(s) in a table used to uniquely identify each, row in the table., Ex., Student, , Roll No, , Name, , Address, , 1, , Vijay, , Noida, , 2, , Sandeep, , G. Noida, , 3, , Ajay, , Noida, , Roll No. is a primary key, Note: Primary key cannot contain Null value., Unique Key: Unique key is one or more column(s) in a table used to uniquely identify each row, in the table. It can contain NULL value., Student, RoltNo, , Name, , Address, , 1, , Vijay, , Noida, , -, , Sandeep, , G. Noida, , 3, , Ajay, , Noida, , Roll-No. is unique key., DitT b/w primary key and Unique key, • Unique key may contain NULL value but primary key can never contain NULL value., Super Key : A super key is a set of one or more attributes that, taken collectively, allow us to, identify uniquely a tuple in the relation., e.g., {Roll-No}, {Roll-No, Name}, {Roll-No, Address}, {Roll-No, Name, Address} all these sets, are super key., Candidate Key: If a relational schema has more than one key, each is called a candidate key., • All the keys which satisfy the condition of primary key can be candidate key., e.g.,, Student, 'Roll_No, , Name, , Phone No, , City, , Vijay, , 578910, , Noida, , 113, , Gopal, , 558012, , Noida, , 114, , Sanjay, , 656383, , G. Noida, , 115, , Santosh, , 656373, , G. Noida, , 112, , ,

Page 32 :
32, , DATABASE MANAGEMENT SYSTEMS, , {Roll-No} and {Phone-No} are two candidate key. Because we can consider anyone of these as, a primary key., Foreign Key : Foreign keys represent relationships between tables., • A foreign key is a column whose values are derived from the primary key of some other table., OR, • A foreign key is a key which is the primary key in the one table and used to relate with some, attributes in other table with same data entry., , e.g.,, Student:, Roll No, , Name, , Subject, , 1, , Computer, Math, Physics, , f--, , 2, , f--, , 3, , Vijay, Gopal, Sanjay, , Roll No, , Sem, , Marks, , '--, , 1, , III, , 80, , '---, , 2, , V, VII, , 75, 70, , .--, , Marks :, , -, , 3, , Here Roll_No of marks table is foreign key., , 1.25 ASSOCIATION, Association connect two or more independent entity types., For example : Teachesrreachers by are a basic binary associative relationship connecting the two, entity types teachers and courses., While writes/written by is a ternary relationship that connects student, course and Reports., 1.26 SPECIALIZATION, Specialization is the result of taking a subset of a higher-level entity set to form a lower-level, entity set., We can say: A lower-level entity set inherits all the attributes and relationship participation of, the higher-level entity set to which it is linked., • Specialization follows "Top-down" approach.

Page 33 :
33, , DATABASE MANAGEMENT SYSTEM, , 1.27 GENERALIZATION, , Generalization is the result of taking the union of two or more disjoint (lower-level) entity sets, to produce a higher-level entity set., The attributes of higher-level entity sets are inherited by lower-level entity sets., • Generalization follow "Bottom-up" approach., • Person is superclass (higher-level entity) and customer and employee are lower level entity., 1.28 AGGREGATION, , An aggregation is an abstraction through which relationships are treated as higher level entities., • Aggregation allows us to indicate that a relationship set participates in another relationship, set., , Employee t---c, , ~--i, , Branch, , e.g., The relationship set work-on, relating the entity sets employees, branch and job as a higher, level entity set called work-on., 1.29 RELATIONSHIPS OF HIGHER DEGREE, , • The degree of a relationship type is the number of parkcipating entity types., • A relationship type of degree two is called binary. One of the degree three is called ternary., e.g.,, Employee, , Works-For, , The works-for relationship is of degree two., Supplier, , Supply, , Project

Page 34 :
34, , DATABASE MANAGEMENT SYSTEMS, , The supply relationship is of degree three or a ternary relationship is supply., 1.30 REDUCTION OF AN E-R DIAGRAM TO TABLES, • A database that conforms to an E-R database schema can be represented by a collection of, tables., • For each entity set and for each relationship set in the database, there is a unique table that, is assigned the name of the corresponding entity set or relationship set., • Each table has multiple columns, each of which has a unique name. We can represent in E-R, schema by table., 1.30.1 Tabular Representation of Strong Entity Set, Let E be a strong entity set with descriptive attributes ab a2, a3, ... , an. We represents this entity, by a tables called E with n distinct columns, each of which corresponds to one of the attribute of E., Each row in this table corresponds to one entity of the entity set E., , Customer, SSN, --, , Name, , DOB, , Address, , 1.30.2 Tabular Representation of Weak Entity Set, Let A be a weak entity set with attributes aI, a2, ... , am., Lct B be the strong entity set on which A is dependent., Let the primary key of B consist of attributes b l , b 2, ... , b n . We represent the entiy set A by a, table called A with one column for each attribute of the set, {aI' a2, ... , an} U {bl> b 2, ... , b n }, , 'Strong entity, , J se~_l Mtl, I:, , Roll-no, , ------, , Mt2, , --, , i, , /, Primary key, , Ii, , ,, , I, , Final

Page 35 :
35, , DATABASE MANAGEMENT SYSTEM, , 1.30.3 Tabular representation of Relationship Sets, , Let R be a relationship set, let at> a2, ... , am be the set of attributes formed by the union of the, primary keys of each of the entity set participating in R and let the descriptive attributes of R be, bl, b z, ... , bn . We represent this relationship set by a table called R with one column for each attribute, of the set., , e.g.,, , SSN Name DOB, , Address, , SSN Loan, , Loan-no, , Amount, , Date, , Solved Problems, Q.1. Constrnct an E-R model for a car insurance company whose customer own one or more cars, (UPTU 2003, 06), each. Each car has associated with it zero to any number of recorded accidents., Ans.

Page 36 :
36, , DATABASE MANAGEMENT SYSTEMS, , Q.2. Explain the following keys, , • Alternatively, • Secondary key, ADs., • Alternatively: If many candidate keys exists, one of them is used as the primary key, then all, other candidate keys known as alternative key., • Secondary key: A secondary key is an attribute or combination of attributes that may not be, a candidate key but they classify the entity set on a particular characteristics., Q.3. Discuss Extended E-R model:, ADs. Extended E-R Model: Since the late 1970s, new applications of database technology have, come up, these are mainly data base for engineering design and manufacturing, telecommunication,, images and graphics, multimffdia, data mining, data warehousing etc. These types of database have, more complex requirements than traditional applications. To represent these requirements as, accurately as possible, designer of database application must use additional semantic data modelling, concepts., The ER models can be enhanced to include these concepts, leading to the Enhanced ER model., Using the concept of class/subclass relationship and type inheritance into the E-R Models can, do this. The enhance E-R models include all the modeling concepts of the E-R model and the concept, of subclass, super-class, specialization and generalization., Q.4. Constrnct an E-R diagram for a hospital with a set of patients and a set of medical doctor., Associate with each patient, a log of the various tests and examinations conducted., (UPTU 2005-06), ADs., , Q.5. Draw E-R diagram for an operator who can work on many machines and each machine has, , many operators. Each machine belongs to one department but a department can have many machines., (UPTU 2005-06)

Page 37 :
37, , DATABASE MANAGEMENT SYSTEM, , Ans., MachIne-nanl!.!, , O-M, , M, , Q.6. Explain the following E-R diagrams., Payment date, , Ans. Explanation of E-R diagram:, , (i) A doubly outlined box (payment) indicates a weak entity set. A doubly outline diamond (loan, payment relationship) indicates the corresponding identifying relationship., (ii) Payment is a weak entity which depends on loan, a strong entity, via the relationship set loan, payment., (iii) Double lines i!ldicate total participation., (iv) Arrow from loan-payment to loan indicate that each payment is for a single loan., (v) Loan-No is primary key of entity loan. While payment-No is partial key of entity payment., Q.7. Explain the following symbols of E-R diagram., (UPTU 2004), (i), (iii), , (v), , -0-<8>-E, , (ii), , -0-, , (iv), , ~, , (vi), , <6:)

Page 38 :
38, , DATABASE MANAGEMENT SYSTEMS, , Ans., , (ii), , -0-- :, -0- :, , (iii), , ~, , : one-to-one relationship., , (iv), , ~, , : total participation of entity set in relationship., , (i), , many-to-many relationship., many-to-one relationship., , (v), , : weak entity set., , (vi), , : multivalued attribute., , Q.S. Draw a E-R diagram for a customer and account with an attribute attached to a relationship, , set., , Ans., Access-date, , Q.9. Draw the E-R diagram of the registration process of the student in a particular course., (UPTU 2005-06), , Ans. An E-R diagram of the registration process of a student in a particular course.

Page 39 :
39, , DATABASE MANAGEMENT SYSTEM, , Q.I0. Difference between Database system and file system., (UPTU 2004), , Ans., File System, , Database System, , (1) Data redundancy : Duplication of data in Data redundancy: Centralized control of data, , different files. Various copies of same data may avoids unnecessary duplication of data using, have different information., functional dependency and normalization., (2) Data isolation : Since data is scattered in Sharing data : A database allows the sharing, various files and files may be in different of data under its control by any number of, formats, it is difficult to write new application application programs or user., programs to retrieve the appropriate data., , (3) Difficult to access data : Data retrieval is Easy to access data : Because of data sharing, complex as every time there is a new request, and data independence, it is easy to access data., the programmer has to write the necessary, code/application., (4) Unsatisfactory data security: Every user of the Data security : In database system proper, system should be allowed to view only that part access procedures are followed, including, of the data which is relevant to his department. proper authentication schemes and additional, checks before permitting access of data., (5) Risk of data integrity: Data values must satisfy Data integrity: Centralized control ensures that, certain integrity constraints. But this task adequate checks are incorporated 10 the, becomes complex when constraints involve database to provide integrity., several data items from different files., , (6) Concurrent access: Many systems allows users Data independence: Data independence allows, to manipulate the data simultaneously. But such for changes at one level of database without, manipulations may result in consistent data as affecting other levels. Hence consistency of data", data are at different locations and different is maintained., formats., Q. 11. Explain Codd's Rules for RDBMS., (UPTU 200L.02), Ans. Codd's Rules: These are following rules., (1) The Information Rule: All data should be presented in table form., (2) The Rule for Sure Access : All data should be accessible without ambiguity. This can be, accomplished through a combination of the table name, primary key, and column name., (3) Systematic 'freatment of Null Values : A field should be allowed to remain empty. This, involves the support of a null value, which is distinct from an empty string or a number with a value, of zero., (4) Dynamic On-Line Catalog based on the Relational Model : A relational database must, provide access to its structure through the same tools that are used to access the data., (5) Data Sublanguage Rule: The database must support at least one clearly defined language that, includes functionality for data definition, data manipulation, data integrity and database transaction control., (6) View Updating Rule: Data can be presented in different logical combinations called views., Each view should support the same full range of data manipUlation that has direct access to a table, available., (7) High-Level Insert, Update and Delete: Data can be retrieved from a relational database in

Page 40 :
40, , DATABASE MANAGEMENT SYSTEMS, , set constructed of data from multiple rows and/or multiple tables. This rule states that insert, update, and delete operations should be supported for any retrievable set., (8) Physical Data Independence : The access to the database must remain consistent whenever, change so strong or accesses to data are changed., (9) Logical Data Independence : How data is viewed should not be changed when the logical, structure of the database change. This rule is particularly difficult to satisfy., (10) Integrity Independence: The database language (like SOL) should support constraints on, user input that m ...intain database integrity. At a minimum, all database do preserve two constraints, through SOL., • No component of a primary key can have a null value., • If a foreign key is defined in one table, and value in it must exist as a primary key in another, table., (11) Distribution Independent: A user should be totally unaware of whether or not the database, is distributed., (12) Non Subversion Rule: There should be no way to modify the database structure other than, through the multiple row database language (like SOL). Most databases today support administrative, tools that allow some direct manipulation of the data structure., Q.12. What is difference between DBMS and RDBMS., Ans., DBMS, , RDBMS, , (1) In DBMS relationship between two tables or In RDBMS relationship between two tables or, files can be specified at the time of table, files are mail.ltained programmatically., creation., (2) DBMS, , does, architecture, , not, , support, , client/server Most of the RDBMS supports client/server, architecture., , (3) DBMS does not support distributed database Most of the RDBMS supports distributed, database., (4) Each table is given an extension in DBMS., , Many tables are grouped in one database in, RDBMS., , (5) DBMS allows only one person to access the RDBMS allows multiple users simultaneous, database at any given time like MS-Access, access to the database like Oracle and, SOL-Server., FoxPro., (6) In DBMS there is lack of security of data., , In RDBMS there are multiple levels of security., (i) Logging in at O/S level., (ii) Command level., (iii) Object level., , (7) DBMS may satisfy less than 7 to 8 rules of RDBMS satisfy more than 7 to 8 rules of codd., codd., (8) Examples of DBMS are MS-Access, FoxPro etc. Examples of RDBMS are Oracle, SOL-Server., Q. 13. What is the advantages of Object Relational Database Management System (ORDBMS) ?, Ans. The Advantages of ORDBMS : These are following:, (1) The objects as such can be stored in the database., (2) The language of the DBMS ean be integrated with an object-oriented programming language.

Page 41 :
DATABASE MANAGEMENT SYSTEM, , 41, , The language may even be exactly the same as that used in the application, which does not, force the programmer to have two representations of his objects., Q. 14. What is the disadvantages of Hierarchical DBMS ?, Ans. Disadvantages of HDBMS, (i) The link in hierarchical model are unidirectional, that is, we can move from parent to the, child only., (ii) The Hierarchical model does not support many-to-many relationship., (iii) In this model if :we need any information from lower level then we have to follow complete, hierarchy of the system., Q. 15. What is Data?, Ans. Data is a collection of raw information., Q. 16. What is Information?, Ans. Information is a collection of processed data, Q. 17. What is Database?, Ans. Database is a collection of inter-related data items that can be processed by one or more, application systems., Q. 18. What is DBMS?, Ans. Database Management System is a collection of interrelated data and set of programs to, access those data. The DBMS is a general purpose software system that facilitates the process of, defining constructing and manipulating databases for various applications., Q. 19. What are the disadvantages of file Oriented System?, Ans. The typical file-oriented system is supported by a conventional operating system. Permanent, records are stored in various files and a number of different application programs are written to extract, records from and add records to the appropriate files., The following are the disadvantages of file-oriented system :, (i) Data redundancy and Inconsistency : Since files and application programs are created by, different programmers over a long period of time, the files are likely to have different formats and the, programs may be written in several programming languages. Moreover, the same piece of information, may be duplicated in several places. This redundancy leads to higher storage and access cost. In, addition, it may lead to data inconsistency, i.e., the various copies of same data may no longer agree., (ii) Difficulty in accessing data : The conventional file processing environments do not allow, needed data to be retrieved in a convenient and efficient manner. Better data retrieveal system must, be developed for general use., (iii) Data isolation : Since data is scattered in various files, and files may be in different formats,, it is difficult to write new application programs to retrieve the appropriate data., (iv) Concurrent access anomalies : In order to improve the overall performance of the system, and obtain a faster response time, many systems allow multiple users to update the data simultaneously., In such an environment, interaction of concurrent updates may result in inconsistent data., (v) Security problems : Not every user of the database system should be able to access all the, data. For example, in banking system, payroll personnel need only that part of the database that has, information about various bank employees. They do not need access to information about customer, accounts. It is difficult to enforce such security constraints., (vi) Integrity problems : The data values stored in the database must satisfy certain types of, consistency constraints. For example, the balance of a bank account may never fall below a prescribed, amount. These constraints are enforced in the system by adding appropriate code in the various

Page 42 :
42, , DATABASE MANAGEMENT SYSTEMS, , application programs. When new constraints are added, it is difficult to change the programs to enforce, them. The problem is compounded when constraints involve several data items for different files., (vii) Atomicity problem : A computer system like any other mechanical or electrical device is, subject to failure. In many applications, it is crucial to ensure that once a failure has occurred and has, been detected, the data are restored to the consistent state existed prior to the failure., Q. 20. What are the advantages of DBMS over File Oriented System?, Ans. The following are the advantages of DBMS over file oriented system., I. Data Redundancy: A major difficulty was that many applications used their own special files, of data. Thus, some data items were common to several applications. In a bank, for example, the same, customer name might appear in a checking account file, a savings account file and an installment loan, file. Moreover, even though it was always the customer name, the related field often had a different, name in the various account files. Thus, CNAME in the checking account file became SNAME in the, savings account file and INAME in the installment loan file. The same field also has a different length, in the various files. For example, CNAME could be up to 20 characters, but SNAME and INAME, might be limited to 15 characters. This redundancy increased the overhead costs of maintenance and, storage. Data redundancy also increased the risk of inconsistency among the various versions of, common data., Suppose a customer's name was changed. The name field might be immediately updated in the, checking account file, updated next week in the savings account file and updated incorrectly in the, installment loan file. Over time, such discrepancies can cause serious degradation in the quality of, information contained in the data files., Database systems can eliminate data redundancy, since all applications share a common pool of, data. Essential information such as customer name will appear just once in the database. Thus, we can, enter a name or change once and know that applications will be accessing consistent data., II. Poor Data Control : In the file system, there was no centralized control at the data element, level. It was very common for the same data element to have multiple names, depending on the file it, has., At a more fundamental level, there is always the chance that the various departments of a, company will be inconsistent in their terminology., III. Inadequate Data Manipulation Capabilities : Indexed seqential files allow the applications, to access a particular record by a key such as product 10. For example, if we knew the ProductID for, the table, it is easy to access a record in the table. Suppose we want a set of records. It is not possible, to obtain a set of records using file system because they are unable to provide strong connections, between data in different files. Database systems were specifically developed to make the interrelating, of data in different files., IV: Excessive Programming Effort : A new application program often required an entirely new, set of file definitions. Even though an existing file may contain some of the data needed, the application, often required a number of other data items. As a result, the programmer had to recode the definitions, of needed data items from the existing file as well as definitions of all new data items. Thus, in, file-oritented systems, there was a heavy interdependence between programs and data., Database provides a separation between programs and data, so that programs can be somewhat, independent of the de'tails of data definition. By providing access to a pool of shared data and by, supporting powerful data manipulating languages, database systems eliminate a large amount initial, and maintenance programming.

Page 43 :
DATABASE MANAGEMENT SYSTEM, , 43, , Q. 21. What is Instance and Schema?, Ans. Instance : The collection of informahon stored in the database at a particular moment is, called an}nstance of the database., Schema : The overall design of the database is called the database schema., Q. 22. What is Data Independence?, Ans. Data Independence: The ability to modify a schema defInition in one level without effecting, a schema defInition in the next level is called Data Independence., There are two levels of data independence :, (i) Physical Data Independence : The ability to modify the physical schema without causing, application programs to be rewritten., (ii) Logical Data Independence: The ability to modify the conceptual schema without causing, application programs to be rewritten., Logical Data Independence is more diffIcult to achieve than physical data independence since, application programs are heavily dependent on the logical structure of the data they access., Q. 23. What is a data model?, Ans. Data Model : A conceptual method of structuring data is called Data Model., The development of systems based on three principal data models. These three models are the, Hierarchical, the Network and the Relational., Q. 24. Explain the components of Database System., Ans. A complete database system in an organization consists of four components., (i) Hardware: The hardware is the set of physical devices on which a database resides. It consists, of one or more computers, disk drives, CRT terminals, printers, tape drivers, connecting cables, etc., The computers used for processing the data in the database may be mainframe, mini computers, or personal computer. Mainframe and mini computers have traditionally been used on a stand-alone, basis to support multiple users accessing a common database. Personal computers are often used with, stand-alone databases controlled and accessed by a single user., Disk drivers are the main storage mechanism for databases. Desktop computers, CRT terminals, and printers are used for entering and retrieving information from the database., The success of the database system has been heavily dependent on advances in hardware, technology. A very large amount of main memory and disk storage is required to maintain and control, the huge quantity of data stored in a database., (ii) Software : A database system includes two types of software :, (a) General purpose database management software usually called the database management, system (DBMS)., (b) Application software that uses DBMS facilities to manipulate the database to achieve a, specific business functions., Application software is generally written by programmers to solve a specific company problem., It may be written in languages like COBOL or C or it may be written in a language supplied by DBMS, like SQL. Application software uses the facilities of the DBMS to access and manipulate data in the, database providing reports or documents needed for the information and processing needs of the, company., The DBMS is system software similar to an operating system. It provides a number of services, to end users and programmers., DBMS typically provides most of the following services., 1. A central data definition and data control facility known as a data dictionary/directory or, catalog.

Page 44 :
44, , DATABASE MANAGEMENT SYSTEMS, , 2., 3., 4., 5., , Data security and integrity mechanisms., Concurrent data access for multiple users., User-oriented data query" manipulation and reporting capabilities., Programmer-oriented application system development capabilities., , DBMS, Data, Dictionary/, Directory, Subsystem, , User, Query and, Reporting, Facilities, , Data, , Security, and, Integrity, Subsystem, , Application, Program, Development, Facilities, , Database, , Fig., , (iii) Data': No database system can exist without data. Data can be collected an entered into the, , database according to the defined structure., (iv) People : Two different types of people concerned with the database., They are:, 1. Users : Executives, Managers, Staff, Clerical personnel., 2. Practitioners : Database Administrators, Programmers., Application Program, Analyst, , Application Program, Application Program, Data Dictionary, Directory, DBMS, , "", , ,-, , "", , User, , Terminals, Computer, , Disks, , Printer, Fig.

Page 45 :
45, , DATABASE MANAGEMENT SYSTEM, , Q. 25. What is Data Dictionary?, A. A data dictionary / directory subsystem keeps track of the defmitions of all the data items in, the database. This includes elementary-level data items (fields), group and record-level data structures, and relational tables. It keeps track of relationships that exist between various data structure. It, maintains the indexes that are used to access data quickly. It also keeps track of screen and report, format definitions that may be used by various application programs., Q. 26. Explain Data Sharing., A. Data without sharing :, , t, , t, , U, , Marketing Files, , Purchasing Files, , U, , Accounting Files, , Fig., , The most significant difference between a file-based system and a database system is that data, are shared., There are three types of data sharing :, (i) Sharing beteen Functional Units: The data sharing suggests that people in different functional, areas use common pool of data, each of their own applications. Without data sharing, the marketing, group may have their data files, the purchasing group theirs, the accounting group theirs and so on., Each group benefits only from its own data. The combined data are more valuable than the sum of, , ~[arkdmg, , FIles, , Purchru.l11g Files, , Accounting Files, , Sharing Data In A Database Enviromnent, Fig.

Page 46 :
46, , DATABASE MANAGEMENT SYSTEMS, , the data in separate files. Not only does each group continue to have access to its own data but, within, reasonable limits of control, they have access to other data as well. The concept of combining data for, common use is called data integration., (ii) Sharing data between Different Levels of Users: Different levels of users need to share data., Three different levels of users are normally distinguished : operations, middle management and, executive. These levels correspond to the three different types of automated business systems that have, evolved during the past three decades :, (a) Electronic Data Processing (EDP) : EDP was first applied to the lower operational levels of, the organization to automate the paperwork. Its basic characteristics include :, • A focus on data, storage, processing and flows at the operational level., • Efficient transaction processing., • Summary reports for management., (b) Management Information System (MIS) : The MIS approach elevated the focus on information, systems activities with additional emphasis on integration and planning of the information systems, function. This includes :, , • An information focus aimed at the middle managers., • An integration of EDP jobs by business function such as productin MIS, marketing MIS,, personnel MIS, etc., • Inquiry and report generation usually with a database., (c) Decision Support System : DSS is focused still higher in the organization with an emphasis on, the following characteristics :, • Decision focused, aimed at top managers and executive decision makers., • Emphasis on flexibility, adaptability and quick response., • Support for the personnel decision-making styles of individual managers., Executive, , Decision, Support System, , (Strategic Reports Queries Analysis), , t, , Middle Management, , Management, Information, System, , (Management Reports by Functional Area), , t, , Operations, , Electronic, Data Processing, System, , (Transactions File Maintenance Processing, Control Reports), , System Corresponding to Different Management Levels, , Fig., , (iii) Sharing data between Different Locations : A company with several locations has important, , data distributed over a wide geographical area. Sharing these data is a significant problem., A centralized database is physically confined to a single location, controlled by a single computer., Most functions for the databases are created and accomplished more easily if the database is, centralized. That is, it is easier to update, back up, query and control access to a database if we know, exactly where it is and y, hat software controls it.

Page 47 :
47, , DATABASE MANAGEMENT SYSTEM, , ~~, , ~, , Computer I - - - - - i Database, , Centralized Database Structure, Fig., , A Distributed database system is made up of several database systems running at local sites, connected by communication lines. A query or update is then no longer a single process controlled by, one software module, but a set of cooperating processes running at several sites controlled by, independent software modules. For a distributed database system to function efficiently, adeqaute, communication technology must be available and the DBMS in the system must be able to, communicate while interacting with the communication~ facilities., , Distributed Database Structure, Fig., , Q. 27. Explain Strategic Database Planning., Ans. Database Planning is a strategic corporate effort to determine information needs for an, extended period. A successful databae planning project will precede operational projects to design and, implement new databases to satisfy the organization's information needs., The Need for Database Planning: Database planning has significant advantages :, • .It expresses management's current understanding of the information resource., • It identifies and justifies resource requirements., • It identifies opportunities for effective resource management including collaboration among, departments or divisions within the organizations., • It specifies action plans for achieving objectives., • It can provide a powerful stimulus and sense of direction to employees at all levels, focusing, their efforts, increasing their productivity and making them feel that they are a genuine part, of the enterprise.

Page 48 :
48, , DATABASE MANAGEMENT SYSTEMS, , Business Plan, , f-+, , Infonnation, ~, Needs, , Database, Plan, , f-+, , Database, Development, Project, , The Database Planning Project : Strategic Database Planning is initiated by senior, management. They allocate resources and identify personnel to participate in the project. With their, commission from management, team members have resources needed to carry out a successful project., The project team should be extensive experience in information systems and other functional, areas of the company. A group of four full-time members, two from information systems and two, acquainted with most other areas of the company. All team members should be skilled and respected, employees, since their work will have a major impact on the organization for· many years. If they are, not skilled in a methodology for carrying out the study, an outside consultant should be employed as, an advisor to train the team in a suitable methodology. The project team leader should be a consultant, but a permanent employee and possibly the head of the database administration., During the project, the team interacts with senior managers from all the primary user areas. The, senior end users identify the principal processes, activities, and entities used in manual or automated, information processing. The project team synthesizes these data into a corporate information model, included as part of the comprehensive database plan., A report covering at least the next five should be delivered to senior management. This report, will include analysis of the following :, • Information needs of the functional areas., • Information needs of different management levels., • Information needs of the geographical locations., • A model of this information needs., • Anticipated data volumes by geographical location projects for the period under study., • A preliminary estimate of costs associated with system upgrades., • Recommendations for detailed development of new or enhanced databases with schedules., Q. 28. Explain the functions of DBA., Ans. Database Administrator is a person with the responsibility of controlling and protecting the, data. The DBA should coordinate the design of the database, guide the development and, implementation of data security procedures, protect the integrity of data values and make sure system, performance is satisfactory., In a small organization, one person carries out all these responsibilities. Often, these function are, assigned to a group of people. This is most likely in a large organization where DBA responsibilities, are divided among several people managed by a chief administrator., The functions of DBA include:, (i) Database Design : Conceptual Database Design consists primarily of defining the data, elements to be included in the database, the relationship that exists between them and the value, constrains apply. A value constraint is a rule defining the permissible values for a specific data items., Physical Database Design determines the physical structure of the database and includes such decisions, as what access methods will be used to retrieve data and what indexes will be built to improve the, performance of the system., (ii) User Training: The DBA is responsible for educating users in the structure of the database, and in its access through the DBMS. This can be done in formal training session by interacting with, users to create database views, through user's manuals and periodic memos and through company, information centres. An information center is an area where users are provided with facilities to do, their own computing.

Page 49 :
DATABASE MANAGEMENT SYSTEM, , 49, , (iii) Database Security and Integrity: The concept of combining an organization's data into one, , common pool accessible to all has both advantages and disadvantages. The obvious advantage of data, sharing is offset by the disadvantage that data can be misused or damaged by the users who do not, have original responsibility and authority over the data. The DBA provides procedures and controls, to prevent the abuse of data., Access of database is ultimately controlled by a password mechanism, whereby a user attempting, access gives a system-validates password. The system allow the validated user only those access rights, recorded in the data dictionary. The DBA is responsible for assinging passwords and controlling, privileges. Data integrity refers to the problems of maintaining the accuracy and consistency of data, values. Security mechanisms such as passwords and data views protect data integrity., (iv) Database System Performance: A database system being simultaneously accessed by many, users may respond very slowly at times because the physical problems associated with users competing, for the same resources are not trivial. Thus, the DBA staff should include technically skilled personnel, who can diagnose and solve system response-time problems. Problem solution may be hardware, acquisition, physical rearrangement of data on disk construction of indexes for rapid access to, high-volume data or the writing of special software to improve access time. The DBA may decide to, maintain redundant copies of data to improve system performance. Such redundancy must be, controlled; however problems of data inconsistency will be avoided., Q. 29. What are the Risks and Costs of databases?, Ans. Database systems have drawbacks., The following are the Risks and Costs of a database :, (i) Organizational Conflicts : Pooling data in a common database may not be politically feasible, in some organiza~ions. Certain user groups may not be willing to relinquish control over their data to, the extent needed to integrate data. Moreover, the risk involved in data sharing for example, that one, group may damage another group's data - and the potential system problems that may limit a group's, access to its own data may be viewed as more troublesome than beneficial. Such people problems could, prevent the effectual implementation of a database system., (ii) Development Project Failure : For a variety of reasons, the project to develop a database, system may fail. Sometimes management was not fully convinced of the value of the database system, in the first place. A database project that seems to be taking too long may be terminated., A project too large in scope may be almost impossible to complete in a reasonable time. Again,, management and users become disenchanted and the project fails., During the course of a project, key personnel may unexpectedly leave the company. If, replacement personnel cannot be found, then the project might not be successfully completed., (iii) System Failure : When the system goes down, all users directly involved in accessing must, wait until the system is functional again. This may require a long wait. Moreover, if the system or, application software fails, there may be permanent damage to the databse. It is very important,, therefore to carefully evaluate all software that will have a direct effect on the database to be certain, that it is as free as errrors as possible. If the organization does not use a database, it is not exposed, to this risk, since the data and its software are distributed., (iv) Overhead Costs: The database approach may require an investment in both hardware and, software. The hardware to run large DBMS must be efficient and will generally require more main, memory and disk storage than simpler file-based system. Tape drives for rapidly backing up the, database are also required. In addition, the DBMS itself may be quite expensive.

Page 50 :
50, , DATABASE MANAGEMENT SYSTEMS, , The DBMS may also need increase operating costs" since it requires more execution time. For, example, an pplication system using a DBMS will usually execute more slowly than a system not using, a DBMS., (v) Need for sophisticated Personnel : The database administration function requires skilled, personnel who are capable of coordinating the needs of different user groups, designing views,, integrating those views into a single schema, establishing data recovery procedures and fine tuning the, physical structure of the database to meet acceptable performance criteria. There is a risk involved in, identification of personnel for the DBA, since if no person having the requisite skills can be found,, the DBA functions may not be properly performed. This could result in significant problems and may, even result in the failure of a database implementation., Q. 30. List and explain the ditTerent stages of DDLC., Ans. DDLC (Database Development Life Cycle) :, It is a process for designing, implementing and maintaining a database system. It consists of six, stages:, 1. Preliminary design, 2. Feasibility design, 3. Requirements definition, 4. Conceptual design, 5. Implementation, 6. Database evaluation and maintenance., r, , A Database Development Life Cycle, , Fig., , Preliminary planning : It is a specific database system that takes place during the strategic, database planning project. After the database implementation project begins, the general information, model produced during database planning is reviewed and enhanced if needed. During this process,, the firm collects information to answer the following questions :, 1. How many application programs are in use, and what functions do they perform?, 2. What files are associated with each of these applications?, 3. What new applications and files are under development?

Page 51 :
DATABASE MANAGEMENT SYSTEM, , 51, , This information can be used to establish relationships between current applications and to, identify uses of application information. It also helps to identify future system requirements and to, assess the economic benefits of a database system., Feasibility Study: A feasibility study involves preparing report on the following issues :, 1. Technological feasibility: Is suitable technology available to support database development?, 2. Operational feasibility : Does the company have personnel, budget and internal expertise to, make a database system successful?, 3. Economic feasibility: Can benefits be identified? Will the desired system be cost-beneficial?, Can costs and benefits be measured?, Requirements Definition: It involves defining the scope of the database identifying management, and functional area information requirements and establishing hardware/software requirements., Information requirements are determined from questionnaire responses, interviews with managers and, clerical users and reports and forms currently being used., Conceptual Design: The conceptual design stage creates the conceptual schema for the database., Specifications are developed to the point where implementation can begin. During this stage, detailed, models of user view are created and integrated into a conceptual data model recording all corporate, data elements to be maintained in the database., Implementation: During database implementation, a DBMS is selected and acquired. Then the, detailed conceptual model is converted to the implementation model of the DBMS, the data dictionary, built, the database populate, application programs developed and users trained., Database Evaluation and Maintenance : Evaluation involves interviewing users to determine if, any data needs are unmet. Changes are made as needed. Over time the system is maintained via the, introduction of enhancements and addition of new programs and data elements as business needs, change and expand., Q. 31. Explain the Three-Level Database Architecture., Ans. It is a standard database structure consisting of Three-Levels., (i) Conceptual Level : It is the level at which conceptual, Select and Acquire DBMS, database design is done. Conceptual database Design involves, analysis of user's information needs and definition of data items, needed to meet them. The result of conceptual design is the, Convert Conceptual Model to, conceptual schema, a single and logical descriptioin of all data, Implementation Model, elements and their relationships., (ii) External Level : It consists of the user views of the, database. Each definable user group will have its own view of, Build Data Dictionary, the database. Each of these views gives a user-oriented, description of the data elements and relationships of which the, Populates Database, view is composed. It can be directly derived from conceptual, schema., (iii) Internal level : The internal level provides the, Develop Application Programs, physical view of the database-the disk drives, physical, addresses, indexes, pointers and so on. This level is the, Trall1 Users, responsibility of physical database designers, who decide which, physical devices will contain data, what access methods will be, TIle Database Impkmentahon Step, used to retrieve and update data and what measures will be, Fig., taken to maintain or improve datahase performance., , I, , I, , I

Page 52 :
52, , DATABASE MANAGEMENT SYSTEMS, , Q 32. Explain the 1\vo-TIer and Three-TIer Architecture of DBMS., Ans. 1. 1\vo-TIer client/server Architecture for DBMS: In a two-tier architecture, the application, is partitioned into a component that resides at the client machines, which evokes database system, functionality at the server machine through query language statements. Application program interface, standards are used for interaction between the client and the server two tier client/server architectures, have two essential components :, 1. A client PC and, 2. A Database server, 2. Tier Considerations, (i) Client program accesses database directly:, • Requires a code change to port to a different database., • High volume of traffic due to data shipping., (ii) Client program executes application logic:, • Limited by processing capa.bility of client workstation (memory, CPU)., • Requires application code to be distributed to each client workstation., • The SQL provides a standard language for RDBMS. This created a logical dividing point, between client and server. Hence, the query and transaction functionality remained on, the server side. In such an architecture, the server often called a query server or, transaction server, because it provides these two, Client Workstation, Chent Program GUI, Presentation Logic, Database Request, , Server Machine, , File Access Routines, Database, Management System, Database File, , Chent Workstation, Client Program GUI, Presentation Logic, Database Request, , File Access Routines, , Fig.

Page 53 :
53, , DATABASE MANAGEMENT SYSTEM, 2-TI~r Archlt~"tur~, , r-----------------------------,, , PhysIcal, Database, , Dataha.,e, Server, , 2-TIer Architedure, Fig., , Advantages of 2-Tier Architecture, , 1., 2., 3., 4., 5., , Simple structurc, Easy to setup and maintain, Adcquate performance for low to medium volume environments., Business logic and database are physically close, which provides higher performance., It is simple and seamless compatibility with existing systems., , Disadvantages :, 1. Complex application rules are difficult to implement in database, server requires more code, , 2., 3., 4., 5., , for the client., Complex application rules difficult to implement in client and have poor performance., Changes to business logic not automatically enforced by a server-hanges require new client, side software to be distributed and installed., Not portable to other database server platforms., Inadequate performance for medium to high volume environments, since database server is, required to perform business logic. This slows down database operations on database server., , 3-Tier client/server Architecture for DBMS, , The three-tier architecture, which adds an intermediate layer between the client and the database, server. This intermediate layer or middle tier is called application server. This server plays an, intermediary role by storing business rules (procedures or constraints) that are used to access data, from the database server. It can also improve database security by checking a client's credentials before, forwarding a request to the database server. Clients contain GUI interfaces and some additional, application specific business rules. The intermediate server accepts requests from the client, processes, the request and sends database commands to the database server, and then acts as a conduit for passing, processed data from the database server to the clients, where it may be processed further and filtered, and presented to users in GUI format. Thus, the user interface, application rules, and data access act, as the three-tiers., 3-tier client server architectures have three essential components :, • A client PC, • An Application Server, • A Database Server

Page 54 :
54, , DATABASE MANAGEMENT SYSTEMS, , Chent, Workstation, , Chent Program, Gl 'I PresentatIon, Request for, Application Function, , +, I, , Connllunieallon, Functions, , I, , I, .-\pphcatlon, Sen er, , ..., , +I, j, , Database, Scryer, , I, , t, , •, I, , Sen'cr Program, ApplIcation, Logic Database, Requests, COIllll1Ulllcation, FlUlclions, , ,I, +I, , I, , I, , t, , .., I, , Database, l'vlanagement, System, Database Files, , I, , +, , Fig., , 3. Tier Architecture Considerations, , 1., , Client program contains presentation logic only :, • Less resources needed for client workstation., • No client modification if database location changes., • Less code to distribute to cleint workstation., 2. One server handles many client requests :, • More resources available for server program., • Reduces data traffic on the network., Advantages :, 1. Complex application rules easy to implement in application server., 2. Business logic off-loaded from database server and client, which improves performance., 3. Changes to business logic automatically enforced by server-changes require only new, application server software to be installed., 4. Application server logic is portable to other database server platforms by virtue of the, application software., 5. Superior performance for medium to high volume environments., 6. Encryption and decryption technology make it safer to transfer sensitive data from server to, client in encrypted form, where it will be decrypted., 7. Various technologies for data compression helping in transferring large amount of data from, servers to clients., Disadvantages, 1. More complex structure., 2. More difficult to setup and maintain., 3. Network security problem., 4. The physical separation of application servers contammg business logic functions and, database servers containing databases may moderately affect performance.