1103 10 Que Assignment.docx

QUE- 1 Define the terms:- Data, Information, Database, DBMS? ANS - Data, Information And Knowledge Data refers to the facts and statistics collected together for reference or analysis Information refers to the facts provided or learned about something or someone Knowledge refers to awareness or familiarity gained by experience of a fact or situation Database is an organized collection of the related data. Database can be set of related data recorded with pen and paper or in computer, for example the list of students with their marks in major five subjects maintained in a file or excel sheet is known as database. This is the informal definition of the database. A database is defined to have the following implicit properties: A database is a logically coherent collection of data with some inherent meaning. A random assortment of data cannot correctly be referred to as a database. A database is designed, built, and populated with data for a specific purpose. It has an intended group of users and some preconceived applications in which these users are interested. A database represents some aspect of the real world, sometimes called the miniworld. Changes to the miniworld are reflected in the database. Hence, a database has logically related data extracted from source(s) for a specific objective, reflecting real world aspect and users that are actively interested in the contents of the database. In other words, database is a shared collection of logically related data and a description of this data, designed to meet the information need of an organization. Let us take the example of our smart phone; this device has its own maintained database for maintaining information about contacts, messages and other mobile applications related data. Figure 2 represents the mobile database structure and sample data for it. The “contact” file stores data of each contact. To define the database, the data elements stored in each record need to be specified. Figure 2 shows that each “contact” record includes data to represent the contact name, mobile number, ringtone, City, email ID and organization name. Name

Ringtone

City

Email ID

Rahul

Mobile Number 94160321XX

Default

Mumbai

Shelly

80590222XX

Ringtone1

Priya Vishal

93154232XX 95015012XX

Ringtone5 Default

Chandigar h Bangalore Pune

[email protected] m [email protected]

Organization Name Accenture Panjab University IBM Infosys

[email protected] [email protected] m Database management system (DBMS). DBMS is a software system that facilitates the user to create, define and control access to the database. The core element of such a system is a database that is not a redundant collection of disconnected files as in File management system, but an integrated data items with minimum redundancy. DBMS is software designed for maintenance and utilizing large collections of data, and the need for such systems, as well as

their use, is growing rapidly. For example, Microsoft SQL MySQL etc.

Server, IBM’s DB2, Oracle RDBMS,

DATABASE APPROACH VS FILE ORIENTED APPROACH. Traditionally, the file oriented approach advocated collection of files and application programs supported by a conventional operating system. It suffered from many limitations base system as a result the database evolved with DBMS.

Characteristics of File Oriented Approach. Such system stores permanent records in different files, and requires varied application programs for data retrieval and record updation in files. Prior to advent of DBMS, organizations use to store data in systems ruled by file oriented approach. But as summarized in figure 3, file oriented approach suffers from following disadvantages: Duplicate and inconsistent data. Different users/programmers will keep a permanent copy of the same file. Let us take an example of University system where multiple copies of same student appear with the accounts department to record his fee details and academic department to store his marks details. But both are storing his personal details in files too. This redundancy leads to higher storage and access cost. Also, another problem is to ensure consistency. For example if the address of the student changes then it needs to be ensured that the same is updated in all the concerned files. Data Dependence. Application programs are strongly bounded to data representation and storage. Any change in data will require the rewriting of the application programs leading to further overhead. QUE-2 Explain various characteristics of Database approach.? ANS- Characteristics of Data Base Approach. In light of the limitations of file oriented approach, the need of new software aroused that may act as a package or a system for facilitating the creation and maintenance of a computerized database. This software is known as DBMS and approach it is based on has following characteristics as summarised in figure 4: Self-describing nature: A DBMS main element is metadata or the data about the data in the form of catalog storing the description of the database. This facilitates the DBMS software to work with varied databases. Insulation between programs and data: Also termed as program-data independence, allows changing data storage structures and operations without the need of alterring the DBMS access programs. Support of multiple views of the data: Each user may see a different view of the database, which describes only the desired data to that user. Sharing of data and multiuser transaction processing: This allows concurrent users to extract and update the database. Concurrency control within the DBMS guarantees that each transaction is correctly executed or completely aborted.

Self Describing Nature: Database Catalog and metadata Insulation between programs and data and data abstraction

Multiple views of the data is supported

Sharing of Data and Multiuser Transaction Processing

DATABASE APPROACH

QUE-3- Explain various components of data model? ANS- Components of Data model: The specifications provided in data model for designing huge databases can be categorised in to three main components as shown in figure 2; structural information, Functional component and set of integrity rules.

Components of Data Model

Structural Information

It includes set of rules according to which database is created

Set of Integrity Constraints

Functional Component

Also known as manipulating component It defines types of operations allowed for data: updation, retrieval, rules for structure change

It includes set of rules to ensure that the data is accurate

Figure 2: Components of Data Model Classification of Data Models: Data models can be classified based on the types of concepts exploited for describing the database structure in each. High-level or conceptual data models, representational (or implementation) data models and low-level or physical data models.

Types of Data Models

High-level or Conceptual data model 





Provide concepts close to user’s perspective It uses concepts such as entities, attributes, and relationships Eg. Entity relationship model

Representational or Implementation data model (Record-Based) 



Provide concepts that may be understood by end users and are also close to the physical storage of data Eg. Relational, network and hierarchical data models

Low-level or data model 



Physical

Provide concepts that describes the physical storage details of data It describes how data is stored as files in the computer by representing information such as record formats, record orderings, and access paths

Low-level or Physical data model. Physical data model describes how the data is stored in computer, in terms of files, indices and other storage structure like record structures, record ordering, access paths etc. It describes the storage methods and access methods of data on disks. Representational or Implementation (Record-Based) data model. Record-based data model describes the database as collection of fixed format records possibly of different types. Each record is further describes with number of fields and length of each field. Examples are Hierarchical, network and relational models. Hierarchical and network data models were developed almost ten years before relational model, so their connection to traditional file processing technique is obvious. Network Data model. In this model the data is represented as collection of records. The relationships are represented as the sets which become pointers in the implementation. The records in this model are organized as graph structure with records as nodes and relationships as edges as shown in figure 4 for patient database. QUE- 4 Compare and contrast the hierarchical, network and relational data model? 2.6

Ans-COMPARISON OF NETWORK, HIERARCHICAL AND RELATIONAL MODELS. There are various parameters for comparing hierarchical, network and relational models such as Complexity, Storage structure, Basic operations and suitability.  Presentation of the data is represented as records and relationships as sets in hierarchical model. The nodes represent records and edges denote relationships in the framework of a tree structure in hierarchical model. In case of Relational Model, each relation is implemented as a separate tabular form. Network model is usually implemented by using pointers and circularly linked lists (ring files) in a graph structure.  With respect to complexity, relational model is the simplest and network data is the most complex. Hierarchical Model is comparatively less complex than network model

but more complex than relational model. Network Model is highly complex due to the data structure and the associated operations possible on this data structure.  With respect to Basic Operations and anomalies, the database nature depicts the existence of various anomalies in context of the three basic operations (insert, delete, update). For example the patient database example illustrated in figure 4-6 reflects that relational or network model is more appropriate as the patient- hospital relationship may have many-to-many correspondence and not strictly one–to-many correspondence always. For example, Departments and employees depict a genuine hierarchical structure (because each employee belongs to exactly one department). However, basic operations are easier to be done in relational model and there are almost no anomalies. In case of Network Model, only few anomalies are there with respect to basic operations.  With respect to pitfalls, relational model has one disadvantage of the extra storage space as it stores relations in different files. Hierarchical Model is asymmetric leading to the unnecessary complexity. The network model is more symmetric than hierarchical model but its undue complexity is its prime disadvantage. Que-5 What is Database security why it is important for organization ? Ans- Database security is a crucial issue in the database management system as it contain important information which is very valuable and sensitive for a organizations database. Security in database involves both policies and mechanism to protect the data from unauthorized access. Given are some database security threats: - Data tampering. - Eavesdropping and data theft. - Falsifying user’s identities. - Password related threats. To protect database against these types of threats four kind of countermeasures can be implemented : - Access con troll. - Inference control - Flow control. - Encryption A DBMS typically includes a database security and authorization subsystem that is responsible for ensuring the security of portion of a database against unauthorized access. Discretionary security mechanism: they are used to grant privileges to user. Mandatory security mechanism: these are used to enforce multilevel security by classifying the data and user into various classes Discretionary access control based on granting and revoking privileges: There are two levels for assigning privileges to use the database system Account level- DBA specifies the particular privilege that each account hold independently of the relation in database. Relation level- DBA can control the privilege to access each individual relation or view in the database. Revoking privileges In some cases it is desirable to grant the privilege to the user temporarily. For e.g, the owner of a relation may want to grant SELECT privilege to a user for a specific task and then revoke that privilege

once the task is completed. Hence mechanism of revoking the privilege is needed. In SQL a REVOKE command is used to for canceling the privilege.

Mandatory access control and Role based access control The discretionary access control techniques of granting an revoking privileges on relation has traditionally been the main security mechanism for relational database system. A user either has or does not have certain privileges. An additional security policy is needed that classify data and user based on security classes. This approach is known as mandatory control. Role – Based Access Control (RBAC) RBAC emerged rapidity in 1990s as a proven technology for managing and enforcing security in large scale enterprise wide system. Roles are created using CREATE ROLE and DESTROY ROLE Command. RBAC appears to be a viable alternative to traditional discretionary and mandatory access control; it ensures that only authorized user are given access to certain data or resources. RBAC models have several desirable features such as flexibility, policy neutrality, better support for security management and administration, and other aspect that make them better candidate for developing secure web based application

Que-6 -What is the two-phase locking protocol ? Ans- Two-Phase Locking A transaction is said to follow the two-phase locking protocol if all locking operations (read-lock, write-lock) precede the first unlock operation in the transaction. Such a transaction can be divided into two phases: an expanding or growing (first phase), during which new locks on items can be acquired but none can be released; and a shrinking (second phase), during which existing locks can be released but no new locks can be acquired. Given below, the two transactions T1 and T2 do not follow the two-phase locking protocol. T1 Read-lock (Y); Read-item (Y); Unlock (X); Write-lock (X); Read-item (X); X: = X + Y; Write-item (X); Unlock (X);

T2 Read-lock (X); Read-item (X); Unlock (Y); Write-lock (Y); R read-item (Y); Y: Y + 1; Write-item (Y); Unlock (Y);

.. This is because the write-lock (X) operation follows the unlock (Y) operation in T 1, and similarly the write-lock (Y) operation follows the unlock (X) operation in T 2. If we enforce two-phase locking, the transactions can be rewritten as Tl, and T2, as shown below:

It can be proved that, if every transaction in a schedule follows the two-phase locking protocol, the schedule is guaranteed to be serializable, obviating the need to test for serializability of schedules any more. The locking mechanism by enforcing two-phase locking also enforces serializability. Two phase locking may limit the amount of concurrency that can occur in a schedule. This is because a transaction T may not be able to release an item X after it is through using it. If T must lock an additional item Y later on; or conversely, T must have the additional item Y before it needs it so that it can release X. Hence, X must remain locked by T until all items that the transaction needs to read or write have been locked; only then can X be released by T. Meanwhile, another transaction seeking to access X may be forced to wait, even though T is done with X; conversely, if Y is locked earlier than it is needed, another transaction seeking to access Y is forced to wait even though T is not using Y yet. This is the price for guaranteeing serializability of all schedules without having to check the schedules themselves.

Que-7 Explain Data Types in PL/SQL? Ans- Data Types in PL/SQL Most data types are obviously similar, but each implementation has unique storage and internalprocessing requirements. When writing PL/SQL blocks, you will be declaring variables, which must be valid data types. The following subsections briefly describe the data types available in PL/SQL. In PL/SQL Oracle provides subtypes of data types. For example, the data type NUMBER has a subtype called INTEGER. You can use subtypes in your PL/SQL program to make the data types compatible with data types in other programs, such as a COBOL program, particularly if you are embedding PL/SQL code in another program. Subtypes are simply alternative names for Oracle data types and therefore must follow the rules of their associated data type.

Character String Data Types Character string data types in PL/SQL, as you might expect, are data types generally defined as having alpha-numeric values. Examples of character strings are names, codes, descriptions, and serial numbers that include characters. CHAR stores fixed-length character strings. The maximum length of CHAR is 32,767 bytes, although it is hard to imagine a set of fixed-length values in a table being so long. SYNTAX: CHAR ( max_length ) Subtype: CHARACTER

VARCHAR2 stores variable-length character strings. You would normally user VARCHAR2 instead of CHAR to store variable-length data, such as an individual's name. The maximum length of VARCHAR2 is also 32,767 bytes. SYNTAX: VARCHAR2 ( max_length ) Subtypes: VARCHAR, STRING LONG also stores variable-length character strings, having a maximum length of 32,760 bytes. LONG is typically used to store lengthy text such as remarks, although VARCHAR2 may be used as well. Numeric Data Types NUMBER stores any type of number in an Oracle database. SYNTAX: NUMBER ( max_length ) You may specify a NUMBER's data precision with the following syntax: NUMBER (precision, scale) Subtypes: DEC, DECIMAL, DOUBLE PRECISION, INTEGER, INT, NUMERIC, REAL, SMALLINT, FLOAT PLS_INTEGER defines columns that may contained integers with a sign, such as negative numbers. Binary Data Types= Binary data types store data that is in a binary format, such as graphics or photographs. These data types include RAW and LONGRAW. The DATE Data Type DATE is the valid Oracle data type in which to store dates. When you define a column as a DATE, you do not specify a length, as the length of a DATE field is implied. The format of an Oracle date is, for example, 01-OCT-97. Que-8 Define the terms with examples:- entity, relationship, degree of relationship, attributes. 2.5

Ans- ENTITY RELATIONSHIP MODEL. It is a top down approach to database design that has following two steps: Step 1: To identify data as real world objects, i.e, entity and relationshout iip between such data that need to be modeled in that system.

Step 2: to identify information about entity (known as attributes) and any constraints on the relationship and attributes. 2.5.1

Entities. A group of objects in the real world with the same properties but having independent evidence. For example, entities in bank database are customer, staff etc. The entity is the basic object that the E-R model represents in the real world with an independent existence. An entity may be an object with a physical existence like customer, car or with conceptual existence like course, treatment etc.

2.5.1

Attributes. The properties of entity that are of significance to be recorded in the database. These properties or attributes enables the entity to be distinguish from other entity in the database. For example, attributes of entity customer in bank database are customer’s account number, acccount type, account balance etc

2.5.2

Relationship. Set of meaningful associations among entity types is known as relationships.

Branch_ ID

Name HAS

Gender

Location IFSC Code

Designation

Entity-Relationship (E-R) Diagrams. E-R diagram is the diagrammatic notation associated with the E-R model, which is a popular high level conceptual data model. The E-R model describes data as entities, relationships, and attributes. Figure 8 displays the COLLEGE E-R database schema as an E-R diagram. let us review the full E-R diagram notation. Entity types such as STUDENT and COURSE are shown in rectangular boxes. Relationship types such as STUDIES and PARTICIPATES are shown in diamondshaped boxes attached to the participating entity types with straight lines. Attributes are shown in ovals, and each attribute is attached by a straight line to its entity type or relationship type. Component attributes of a composite attribute are attached to the oval representing the composite attribute, as illustrated by the Name attribute of STUDENT. Key attributes have their names underlined. In Figure 8 the cardinality ratio of each binary relationship type is specified by attaching a 1, M, or N on each participating edge. The cardinality ratio of STUDENT: COURSE in STUDIES is N:1 as many students are enrolled in one course, whereas it is M:N for STUDENT: EVENTS in PARTICIPATES as many students may participates in many co-curricular events.

Que-9 List advantages and disadvantages of DBMS. 1.4.1

Ans- ADVANTAGES AND LIMITATIONS OF DBMS. It is clear from limitations of file oriented approach and characteristics of Database approach that DBMS is the specialised software offering a spectrum of benefits to majority of database related applications. Still there exist some limitations of the usage of DBMS and the tradeoff is summarised in figure 5. The main pitfalls or limitation is the huge initial investment in terms of hardware and software cost. No doubt the features like security and concurrency comes as a package with DBMS but at the cost of overhead that may restrict some real time applications of data extraction or updation. USAGE OF DBMS TRADEOFF

Limitations of DBMS 1. High Cost involved 2. Need of additional hardware 3. Overhead for ensuring concurrency control, security and integrity 4. Overhead may act as obstacle for achieving real time requirements 5. Complex operations require skilled staff

Advantages of DBMS 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13.

Controls redundancy Restricts unauthorised access Provides multiple user interface Provides storage structures for efficient query processing Provides backup and recovery Provides persistent storage for program objects Enforces integrity constraints Permits inference and actions using rules Represents complex relationships among users Provides concurrency control for multiuser transaction processing Enforces standard and flexibility Reduces application development time Availability of up –to-date information

Que-10 List the steps for designing the relational database for a given application.

Ans- CONCEPTUAL DESIGN OF RELATIONAL DATABASE

E-R diagrams do the designing of database. For constructing an E-R diagram, the entity sets are identified. Then their relationship sets are also identified. Finally, they are represented using the diagrammatic notation of E-R diagrams. If we are given the need for a system requiring the use of a relational database, then we must how should the database be designed? We must try to know what the database logical schema should be i.e. what should the tables be and what attributes should they have? There is a typical life cycle for solving this problem-

1. Requirements collection and analysis Requirements are typically given in sentences, which give the descriptions of what the system should do or represent.

2. Conceptual Design (leading to conceptual schema via E-R Model) Database conceptual modeling attempts to represent requirements in a more rigorous, clear and operational way. It is also known as semantic modeling. E-R Modeling is a wellestablished and accepted form of semantic modeling.

3. Logical Design (leading to database logical schema) The conceptual design created using E-R diagram is then converted to logical relational schema. There are some rules for the conversion:

a) One-to-One relationships: Merge the two entities into a single RDBMS table keeping all the attributes into it. Identify a primary key in the relation. If there is no primary key, add a new dummy attribute to serve as primary key.

b) One-to-Many relationships: Create two RDBMS tables one for each entity. Add the primary key from the entity on one side to the entity on many side and identify it as foreign key.

c) Many-to-Many relationships: Create a new (bridge) table and post the primary keys from both entities as attributes in the new table. The posted attributes are foreign keys.

4. Physical Design This refers to the final Structured Query Language (SQL) commands, which will be used to create RDMS tables. It is system specific as different RDBMS might be used on different systems.