Direct Payment Sales Click for Enquiry +91-8882309876 solvezone@[Link] +91-.

image

Order

Order Synopsis & Project

Download

SOLUTION OF ASSIGNMENT SYNOPSIS & PROJECT DISSERTATION REPORT

VIEW ALL PURCHASEBLE PRODUCT

PRODUCT DETAILS

Title Name Database Management System Solved Assignment for Amity better grades and marks in
Delhi Noida Mumbai Faridabad and Ghaziabad India

University AMITY

Service Type Assignment

Course B.C.A

Semister Semester-III Cource: B.C.A

Short Name or Subject Code Database Management System

Select Assignment Type Without time Running

Commerce line item Type Semester-III Cource: B.C.A

ProductAssignment of B.C.A Semester-III (AMITY)

Price 800.00 INR

SOLVED ASIGNMENT

:-
ANSWER :-

Database Management System

Explain the three-tier architecture of DBMS and illustrate the concept of Data Independence with it.

Ans-

DBMS 3-tier architecture divides the complete system into three inter-related but independent modules
as shown in Figure 1.

Figure 1

Physical Level: At physical level, the information about location of database objects in data store is kept.
Various users are DBMS are unaware about the locations of these objects.

Conceptual Level: At conceptual level, data is represented in the form of various database tables. For
Example, STUDENT database may contain STUDENT and COURSE tables which will be visible to users but
users are unaware about their storage.

External Level: An external level specifies a view of the data in terms of conceptual level tables. Each
external level view is used to cater the needs of a particular category of users. For Example, FACULTY of a
university is interested in looking course details of students, STUDENTS are interested in looking all
details related to academics, accounts, courses and hostel details as well. So, different views can be
generated for different users.
Data Independence

Data independence means change of data at one level should not affect another level. Two types of data
independence are required in this architecture:

Physical Data Independence: Any change in physical location of tables and indexes should not affect
conceptual level or external view of data. This data independence is easy to achieve and implemented by
most of the DBMS.

Conceptual Data Independence: The data at conceptual level schema and external level schema must be
independent. This means, change in conceptual schema should not affect external schema. e.g.; Adding
or deleting attributes of a table should not affect the user’s view of table. But this type of independence
is difficult to achieve as compared to physical data independence because the changes in conceptual
schema are reflected in user’s view.

Phases of database design

Database designing for a real world application starts from capturing the requirements to physical
implementation using DBMS software which consists of following steps shown in Figure 2.

Conceptual Design: The requirements of database are captured using high level conceptual data model.
For Example, ER model is used for conceptual design of database.

Logical Design: Logical Design represents data in the form of relational model. ER diagram produced in
conceptual design phase is used to convert the data into Relational Model.
Physical Design: In physical design, data in relational model is implemented using commercial DBMS like
Oracle, DB2

Advantages of DBMS

DBMS helps in efficient organization of data in database which has following advantages over typical file
system.

Minimized redundancy and data consistency:Data is normalized in DBMS to minimize the redundancy
which helps in keeping data consistent. For Example, student information can be kept at one place in
DBMS and accessed by different users.

Simplified Data Access:A user need only name of the relation not exact location to access data, so the
process is very simple.

Multiple data views:Different views of same data can be created to cater the needs of different users. For
Example, faculty salary information can be hidden from student view of data but shown in admin view.

Data Security:Only authorized users are allowed to access the data in DBMS. Also, data can be encrypted
by DBMS which makes it secure.

Concurrent access to data: Data can be accessed concurrently by different users at same time in DBMS.

Backup and Recovery mechanism:DBMS backup and recovery mechanism helps to avoid data loss and
data inconsistency in case of catastrophic failures.

What is the use of ER diagram? Explain the significance of Specialization and Generalization concept.

Ans-

An entity–relationship model (ER model) describes inter-related things of interest in a specific domain of
knowledge. An ER model is composed of entity types (which classify the things of interest) and specifies
relationships that can exist between instances of those entity types.

An entity–relationship diagram for an MMORPG using Chen´s notation.

In software engineering an ER model is commonly formed to represent things that a business needs to
remember in order to perform business processes. Consequently, the ER model becomes an abstract
data model that defines a data or information structure that can be implemented in a database, typically
a relational database.

Entity–relationship modeling was developed for database design by Peter Chen and published in a 1976
paper. However, variants of the idea existed previously. Some ER modelers show super and subtype
entities connected by generalization-specialization relationships, and an ER model can be used also in
the specification of domain-specific ontologies.

Introduction

An entity–relationship model is usually the result of systematic analysis to define and describe what is
important to processes in an area of a business. It does not define the business processes; it only
presents a business data schema in graphical form. It is usually drawn in a graphical form as boxes
(entities) that are connected by lines (relationships) which express the associations and dependencies
between entities. An ER model can also be expressed in a verbal form, for example: one building may be
divided into zero or more apartments, but one apartment can only be located in one building.

Entities may be characterized not only by relationships, but also by additional properties (attributes),
which include identifiers called "primary keys". Diagrams created to represent attributes as well as
entities and relationships may be called entity-attribute-relationship diagrams, rather than entity-
relationship models.

An ER model is typically implemented as a database. In a simple relational database implementation,

each row of a table represents one instance of an entity type, and each field in a table represents an
attribute type. In a relational database a relationship between entities is implemented by storing the
primary key of one entity as a pointer or "foreign key" in the table of another entity
There is a tradition for ER/data models to be built at two or three levels of abstraction. Note that the
conceptual-logical-physical hierarchy below is used in other kinds of specification, and is different from
the three schema approach to software engineering.

Conceptual data model

This is the highest level ER model in that it contains the least granular detail but establishes the overall
scope of what is to be included within the model set. The conceptual ER model normally defines master
reference data entities that are commonly used by the organization. Developing an enterprise-wide
conceptual ER model is useful to support documenting the data architecture for an organization.

A conceptual ER model may be used as the foundation for one or more logical data models (see below).
The purpose of the conceptual ER model is then to establish structural metadata commonality for the
master data entities between the set of logical ER models. The conceptual data model may be used to
form commonality relationships between ER models as a basis for data model integration.

Logical data model

A logical ER model does not require a conceptual ER model, especially if the scope of the logical ER
model includes only the development of a distinct information system. The logical ER model contains
more detail than the conceptual ER model. In addition to master data entities, operational and
transactional data entities are now defined. The details of each data entity are developed and the
relationships between these data entities are established. The logical ER model is however developed
independently of the specific database management system into which it can be implemented.

Physical data model

One or more physical ER models may be developed from each logical ER model. The physical ER model is
normally developed to be instantiated as a database. Therefore, each physical ER model must contain
enough detail to produce a database and each physical ER model is technology dependent since each
database management system is somewhat different.
The physical model is normally instantiated in the structural metadata of a database management
system as relational database objects such as database tables, database indexes such as unique key
indexes, and database constraints such as a foreign key constraint or a commonality constraint. The ER
model is also normally used to design modifications to the relational database objects and to maintain
the structural metadata of the database.

The first stage of information system design uses these models during the requirements analysis to
describe information needs or the type of information that is to be stored in a database. The data
modeling technique can be used to describe any ontology (i.e. an overview and classifications of used
terms and their relationships) for a certain area of interest. In the case of the design of an information
system that is based on a database, the conceptual data model is, at a later stage (usually called logical
design), mapped to a logical data model, such as the relational model; this in turn is mapped to a
physical model during physical design. Note that sometimes, both of these phases are referred to as
"physical design."

Entity–relationship modeling

Two related entities

An entity with an attribute

A relationship with an attribute

Primary key

An entity may be defined as a thing capable of an independent existence that can be uniquely identified.
An entity is an abstraction from the complexities of a domain. When we speak of an entity, we normally
speak of some aspect of the real world that can be distinguished from other aspects of the real world.

An entity is a thing that exists either physically or logically. An entity may be a physical object such as a
house or a car (they exist physically), an event such as a house sale or a car service, or a concept such as
a customer transaction or order (they exist logically—as a concept). Although the term entity is the one
most commonly used, following Chen we should really distinguish between an entity and an entity-type.
An entity-type is a category. An entity, strictly speaking, is an instance of a given entity-type. There are
usually many instances of an entity-type. Because the term entity-type is somewhat cumbersome, most
people tend to use the term entity as a synonym for this term.

Entities can be thought of as nouns. Examples: a computer, an employee, a song, a mathematical

theorem.

A relationship captures how entities are related to one another. Relationships can be thought of as verbs,
linking two or more nouns. Examples: an owns relationship between a company and a computer, a
supervises relationship between an employee and a department, a performs relationship between an
artist and a song, a proves relationship between a mathematician and a conjecture.

The model´s linguistic aspect described above is utilized in the declarative database query language
ERROL, which mimics natural language constructs. ERROL´s semantics and implementation are based on
reshaped relational algebra (RRA), a relational algebra that is adapted to the entity–relationship model
and captures its linguistic aspect.

Entities and relationships can both have attributes. Examples: an employee entity might have a Social
Security Number (SSN) attribute; the proved relationship may have a date attribute.

Every entity (unless it is a weak entity) must have a minimal set of uniquely identifying attributes, which
is called the entity´s primary key.

Entity–relationship diagrams don´t show single entities or single instances of relations. Rather, they show
entity sets (all entities of the same entity type) and relationship sets (all relationships of the same
relationship type). Example: a particular song is an entity. The collection of all songs in a database is an
entity set. The eaten relationship between a child and her lunch is a single relationship. The set of all
such child-lunch relationships in a database is a relationship set. In other words, a relationship set
corresponds to a relation in mathematics, while a relationship corresponds to a member of the relation.
Certain cardinality constraints on relationship sets may be indicated as well.

What are the two Intergrity Constraints in relational databse model? Explain the difference between
them.

Ans-

The first, implicit constraint is on data type. Every column has a type and the database management
system rejects attempts to insert or update rows with invalid data in any column. Your CREATE TABLE
statements establish these constraints. These can include NOT NULL and UNIQUE constraints to ensure
that data is not only of the correct type, but that it´s present and that there are no duplicates of any
value within a given column. PRIMARY KEYS are implicitly UNIQUE and NOT NULL. (Note it is also
possible to have "composite" keys or impose composite UNIQUE constraints; this simply means that the
constraint is imposed on multiple columns. For example a UNIQUE constraint on (first_name, last_name,
birth_date) would mean that not two entries could have the same combination of first+lastname and
birth date -- but many entries could have the same birthday, the same first names, or even the same first
and last names, etc).

The second class of constraints related to "referential interity." These have to do with the relations ... or
references among tables. For example if I have a table of authors, a table of publications, and a junction
table relating publications to authors (a classic multi: association) ... then I would normally declare
constraints on both columns in the junction table "REFERENCES [Link] for one and REFERENCES
[Link] for the other. This arrangement would ensure that not entry in the junction table referred
to a non-existent author nor to a non-existent publication.

When you create a table using REFERENCES then these referential integrity constraints are implicitly
enforced by the RDBMS.
In addition to these implicit constraints it´s possible, in most database management systems, add your
own custom constraints ... which can require values in a certain range, require that values in one column
NOT EXIST in some other column (UNIQUE only ensure that the value doesn´t exists somewhere within
the same column/table), and so on.

These custom constraints could generally be categorized as being on data type/value or relating to
referential integrity. (Off hand I can´t think of a single constraint would be in both categories; but I can´t
assert with any confidence that such a thing is not possible). Certainly I might have multiple constraints
on a single column. For example I might have one for mapping drivers licenses to persons which
REFERENCES some sort of [Link] while also having another which requires that person. Age for
example.

In general the purpose of RDBMS integrity constraints is to ensure that bad data doesn´t get into the
system; by having the database system provide as much centralized enforcement (referential integrity
and business rules) as is possible/practical.

The core assumption of an RDBMS is that the corpus of data stored there will be used by different
applications concurrently and over time. The intention is to be robust even in the face of possible
application level defects so that the data and the relationships among the data retain their value.

(This assumption is often in dynamic tension with how many applications´ programmers view "the
database." To many applications developers "the database" is simply a fancy filesystem or data
persistence engine, and one which imposes constraints on their code. That view is not necessarily
incorrect. Many databases are created and maintained only for use by a single application or tightly
couple suite of applications. Also that view naturally leads to the Object-relational impedance mismatch
and to the forces which gave rise to NoSQL

What do you mean by Normalization? Why do we need it? Explain 2NF and 3NF with suitable examples.

Ans-
Database normalization, or simply normalization, is the process of organizing the columns (attributes)
and tables (relations) of a relational database to reduce data redundancy and improve data integrity.
Normalization is also the process of simplifying the design of a database so that it achieves the optimal
structure composed of atomic elements. It was first proposed by Edgar F. Codd, as an integral part of a
relational model.

Normalization involves arranging attributes in relations based on dependencies between attributes,

ensuring that the dependencies are properly enforced by database integrity constraints. Normalization is
accomplished by applying some formal rules either by a process of synthesis or decomposition. Synthesis
creates a normalized database design based on a known set of dependencies. Decomposition takes an
existing (insufficiently normalized) database design and improves it based on the known set of
dependencies.

Edgar F. Codd, the inventor of the relational model (RM), introduced the concept of normalization and
what is now known as the first normal form (1NF) in 1970. Codd went on to define the second normal
form (2NF) and third normal form (3NF) in 1971, and Codd and Raymond F. Boyce defined the Boyce-
Codd normal form (BCNF) in 1974. Informally, a relational database relation is often described as
"normalized" if it meets third normal form. Most 3NF relations are free of insertion, update, and deletion
anomalies.

Objectives

A basic objective of the first normal form defined by Codd in 1970 was to permit data to be queried and
manipulated using a "universal data sub-language" grounded in first-order logic. (SQL is an example of
such a data sub-language, albeit one that Codd regarded as seriously flawed.)

The objectives of normalization beyond 1NF (first normal form) were stated as follows by Codd:

To free the collection of relations from undesirable insertion, update and deletion dependencies;
To reduce the need for restructuring the collection of relations, as new types of data are introduced, and
thus increase the life span of application programs;

To make the relational model more informative to users;

To make the collection of relations neutral to the query statistics, where these statistics are liable to
change as time goes by.

An update anomaly. Employee 519 is shown as having different addresses on different records.

An insertion anomaly. Until the new faculty member, Dr. Newsome, is assigned to teach at least one
course, his details cannot be recorded.

A deletion anomaly. All information about Dr. Giddens is lost if he temporarily ceases to be assigned to
any courses.

When an attempt is made to modify (update, insert into, or delete from) a relation, the following
undesirable side-effects may arise in relations that have not been sufficiently normalized:

Update anomaly. The same information can be expressed on multiple rows; therefore updates to the
relation may result in logical inconsistencies. For example, each record in an "Employees´ Skills" relation
might contain an Employee ID, Employee Address, and Skill; thus a change of address for a particular
employee may need to be applied to multiple records (one for each skill). If the update is only partially
successful – the employee´s address is updated on some records but not others – then the relation is left
in an inconsistent state. Specifically, the relation provides conflicting answers to the question of what this
particular employee´s address is. This phenomenon is known as an update anomaly.
Insertion anomaly. There are circumstances in which certain facts cannot be recorded at all. For example,
each record in a "Faculty and Their Courses" relation might contain a Faculty ID, Faculty Name, Faculty
Hire Date, and Course Code. Therefore we can record the details of any faculty member who teaches at
least one course, but we cannot record a newly hired faculty member who has not yet been assigned to
teach any courses, except by setting the Course Code to null. This phenomenon is known as an insertion
anomaly.

Deletion anomaly. Under certain circumstances, deletion of data representing certain facts necessitates
deletion of data representing completely different facts. The "Faculty and Their Courses" relation
described in the previous example suffers from this type of anomaly, for if a faculty member temporarily
ceases to be assigned to any courses, we must delete the last of the records on which that faculty
member appears, effectively also deleting the faculty member, unless we set the Course Code to null.
This phenomenon is known as a deletion anomaly.

2NF (Second Normal Form) Rules

Rule 1- Be in 1NF

Rule 2- Single Column Primary Key

It is clear that we can´t move forward to make our simple database in 2nd Normalization form unless we
partition the table above.

Table 1

Table 2

We have divided our 1NF table into two table’s viz. Table 1 and Table2. Table 1 contains member
information. Table 2 contains information on movies rented.

We have introduced a new column called Membership_id which is the primary key for table 1. Records
can be uniquely identified in Table 1 using membership id
3NF (Third Normal Form) Rules

Rule 1- Be in 2NF

Rule 2- Has no transitive functional dependencies

To move our 2NF table into 3NF, we again need to again divide our table.

3NF Example

TABLE 1

Table 2

Table 3

We have again divided our tables and created a new table which stores Salutations.

There are no transitive functional dependencies, and hence our table is in 3NF

In Table 3 Salutation ID is primary key, and in Table 1 Salutation ID is foreign to primary key in Table 3

Now our little example is at a level that cannot further be decomposed to attain higher form of
normalization. In fact, it is already in higher normalization forms. Separate efforts for moving into next
levels of normalizing data are normally needed in complex databases. However, we will be discussing
next levels of normalizations in brief in the following.

5 What is a View in SQL? Consider the CUSTOMERS table having the following records −

| ID | NAME | AGE | ADDRESS | SALARY |

+----+----------+-----+-----------+----------+

| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |

| 2 | Khilan | 25 | Delhi | 1500.00 |

| 3 | kaushik | 23 | Kota | 2000.00 |

| 4 | Chaitali | 25 | Mumbai | 6500.00 |

| 5 | Hardik | 27 | Bhopal | 8500.00 |

| 6 | Komal | 22 | MP | 4500.00 |

| 7 | Muffy | 24 | Indore | 10000.00 |

+----+----------+-----+-----------+----------+ Write an SQL query to create a view of CUSTOMERS table with

only Name and Address attributes and draw the output of the same.

Ans-

A VIEW is a virtual table, through which a selective portion of the data from one or more tables can be
seen. Views do not contain data of their own. They are used to restrict access to the database or to hide
data complexity. A view is stored as a SELECT statement in the database. DML operations on a view like
INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based.
The Syntax to create a sql view is

CREATE VIEW view_name

AS

SELECT column_list

FROM table_name [WHERE condition];

view_nameis the name of the VIEW.

The SELECT statement is used to define the columns and rows that you want to display in the view.

For Example: to create a view on the product table the sql query would be like

CREATE VIEW view_product

AS

SELECT product_id, product_name

FROM product;

Create a table to store information about weather observation stations:

-- No duplicate ID fields allowed

CREATE TABLE STATION

(ID INTEGER PRIMARY KEY,

CITY CHAR (20),

STATE CHAR (2),

LAT_N REAL,

LONG_W REAL);

Populate the table STATION with a few rows:

INSERT INTO STATION VALUES (13, ´Phoenix´, ´AZ´, 33, 112);

INSERT INTO STATION VALUES (44, ´Denver´, ´CO´, 40, 105);

INSERT INTO STATION VALUES (66, ´Caribou´, ´ME´, 47, 68);

Query to look at table STATION in undefined order:

SELECT * FROM STATION;

ID

CITY

STATE

LAT_N

LONG_W

13
Phoenix

AZ

33

112

44

Denver

CO

40

105

66

Caribou

ME

47
68

Query to select Northern stations (Northern latitude > 39.7):

-- selecting only certain rows is called a "restriction".

SELECT * FROM STATION

WHERE LAT_N > 39.7;

ID

CITY

STATE

LAT_N

LONG_W

44

Denver

CO
40

105

66

Caribou

ME

47

68

Query to select only ID, CITY, and STATE columns:

-- selecting only certain columns is called a "projection".

SELECT ID, CITY, STATE FROM STATION;

ID

CITY

STATE
13

Phoenix

AZ

44

Denver

CO

66

Caribou

ME

Query to both "restrict" and "project":

SELECT ID, CITY, STATE FROM STATION

WHERE LAT_N > 39.7;

ID
CITY

STATE

44

Denver

CO

66

Caribou

ME

Create another table to store normalized temperature and precipitation data:

-- ID field must match some STATION table ID

(so name and location will be known).

-- Allowable ranges will be enforced for other values.

-- No duplicate ID and MONTH combinations.

-- Temperature is in degrees Fahrenheit.

-- Rainfall is in inches.

CREATE TABLE STATS

(ID INTEGER REFERENCES STATION (ID),

MONTH INTEGER CHECK (MONTH BETWEEN 1 AND 12),

TEMP_F REAL CHECK (TEMP_F BETWEEN -80 AND 150),

RAIN_I REAL CHECK (RAIN_I BETWEEN 0 AND 100),

PRIMARY KEY (ID, MONTH));

Populate the table STATS with some statistics for January and July:

INSERT INTO STATS VALUES (13, 1, 57.4, 0.31);

INSERT INTO STATS VALUES (13, 7, 91.7, 5.15);

INSERT INTO STATS VALUES (44, 1, 27.3, 0.18);

INSERT INTO STATS VALUES (44, 7, 74.8, 2.11);

INSERT INTO STATS VALUES (66, 1, 6.7, 2.10);

INSERT INTO STATS VALUES (66, 7, 65.8, 4.52);

Query to look at table STATS in undefined order:

SELECT * FROM STATS;

ID

MONTH

TEMP_F
RAIN_I

13

57.4

.31

13

91.7

5.15

44

27.3

.18
44

74.8

2.11

66

6.7

2.1

66

65.8

4.52
Query to look at table STATS, picking up location information by joining with table STATION on the ID
column:

-- matching two tables on a common column is called a "join".

-- The column names often match, but this is not required.

-- Only the column values are required to match.

What are Exceptions? Explain the keywords used in exception handling in SQL with an example.

Ans-

When an error occurs within a method, the method creates an object and hands it off to the runtime
system. The object, called an exception object, contains information about the error, including its type
and the state of the program when the error occurred. Creating an exception object and handing it to
the runtime system is called throwing an exception.

After a method throws an exception, the runtime system attempts to find something to handle it. The set
of possible "somethings" to handle the exception is the ordered list of methods that had been called to
get to the method where the error occurred. The list of methods is known as the call stack (see the next
figure).

The call stack showing three method calls, where the first method called has the exception handler.

The call stack.

The runtime system searches the call stack for a method that contains a block of code that can handle
the exception. This block of code is called an exception handler. The search begins with the method in
which the error occurred and proceeds through the call stack in the reverse order in which the methods
were called. When an appropriate handler is found, the runtime system passes the exception to the
handler. An exception handler is considered appropriate if the type of the exception object thrown
matches the type that can be handled by the handler.

The exception handler chosen is said to catch the exception. If the runtime system exhaustively searches
all the methods on the call stack without finding an appropriate exception handler, as shown in the next
figure, the runtime system (and, consequently, the program) terminates.

Versions of SQL Server previous to SQL Server 2005 required error handling code after every Transact-
SQL statement that might produce an error. (You can handle errors using the @@error global variable.)
Starting with SQL Server 2005, you can capture and handle exceptions using two statements, TRY and
CATCH. This section first explains what “exception” means and then discusses how these two statements
work.

An exception is a problem (usually an error) that prevents the continuation of a program. With such a
problem, you cannot continue processing because there is not enough information needed to handle the
problem. For this reason, the existing problem will be relegated to another part of the program, which
will handle the exception.

The role of the TRY statement is to capture the exception. (Because this process usually comprises
several statements, the term “TRY block” typically is used instead of “TRY statement.”) If an exception
occurs within the TRY block, the part of the system called the exception handler delivers the exception to
the other part of the program, which will handle the exception. This program part is denoted by the
keyword CATCH and is therefore called the CATCH block.

NOTE

Exception handling using the TRY and CATCH statements is the common way that modern programming
languages like C# and Java treat errors.
Exception handling with the TRY and CATCH blocks gives a programmer a lot of benefits, such as:

Exceptions provide a clean way to check for errors without cluttering code

Exceptions provide a mechanism to signal errors directly rather than using some side effects

Exceptions can be seen by the programmer and checked during the compilation process

SQL Server 2012 introduces the third statement in relation to handling errors: THROW. This statement
allows you to throw an exception caught in the exception handling block. Simply stated, the THROW
statement is another return mechanism, which behaves similarly to the already described RAISEERROR
statement.

Example 1 shows how exception handling with the TRY/CATCH/THROW works. It shows how you can use
exception handling to insert all statements in a batch or to roll back the entire statement group if an
error occurs. The example is based on the referential integrity between the department and employee
tables. For this reason, you have to create both tables using the PRIMARY KEY and FOREIGN KEY
constraints.

Illustrate the differences between Mandatory and Discretionary Security Issues. Explain the applicability
of both in different situations.

Ans-

The authority for discretionary spending stems from annual appropriation acts, which are under the
control of the House and Senate Appropriations Committees. Most defense, education, and
transportation programs, for example, are funded that way, as are a variety of other federal programs
and activities. Those appropriations are subject to a set of budget enforcement rules and processes that
differ from those that apply to mandatory spending. As the Congress considers appropriation acts, CBO
tallies the budget authority those acts would provide and estimates the outlays that would result.

Mandatory—or direct—spending includes spending for entitlement programs and certain other
payments to people, businesses, and state and local governments. Mandatory spending is generally
governed by statutory criteria; it is not normally set by annual appropriation acts. Outlays for the
nation’s three largest entitlement programs (Social Security, Medicare, and Medicaid) and for many
smaller programs (unemployment compensation, retirement programs for federal employees, student
loans, and deposit insurance, for example) are mandatory spending. Social Security and some other
mandatory spending programs are in effect indefinitely, but some (for example, some agriculture
programs) expire at the end of a given period. Roughly 60 percent of federal spending in 2012 (other
than for the government’s net interest costs) was mandatory. Legislation that changed direct spending
would, by itself, affect the budget deficit because no further legislative action would be required for the
change in spending to occur.

One of the attributes by which an access control policy is classified is whether it is a Mandatory Access
Control (MAC) policy, or a Discretionary Access Control (DAC) policy. As the name implies, a MAC policy is
obligatory – that is, it dictates whether an operation should be permitted or denied without letting a
user override the policy. Most mail servers, for example, enforce a policy that disallows messages larger
than a predetermined size to be sent through them. Many mail servers also reject any incoming
messages that are suspected of containing a computer virus. Both policies are MAC because they cannot
be overridden by a decision of an end-user – neither the sender nor the recipient of a message can ask
the mail server to disregard the policy for a specific message. The term “access control” is not in
common use with respect to mail servers, but effectively, what the policy controls is access to the
mailboxes managed by the mail server. A DAC policy, on the other hand, leaves final decision in the
hands of the end-user. The most common example is a computer file system. The owner of a file can
grant or deny access rights at his/her discretion. For example, a company may decide on a policy that
prohibits employees from disclosing expense reports to each other, and requires that every manager be
able to inspect the expense reports of his/her employees. However, as long as the file system policy is
discretionary, so is the adherence to the company policy, and it is up to every employee to adhere to it or
violate it by granting or restricting access to his/her files. An access control policy does not have to be
strictly mandatory or strictly discretionary. Let us revisit the mail server example – it is often possible for
the mail account owner to specify a rule that rejects messages from a specific origin. Combined with a
mandatory size-limit and a mandatory virus-rejection policy, such a mail server employs a combined
mandatory/dis scretionary access control policy – some decisions are mandatory, but others are left at
the discretion of the end user. Naturally, for mandatory rules to actually be mandatory, they must take
precedence over discretionary rules in a combined mandatory/discretionary policy. Strict security
standards such as the Trusted Computer System Evaluation Criteria (TCSEC) employee d in military
environments, require MAC policies to be in effect. In the business world, MAC policies are usually not
used – not because they are not useful, but rather because they are practically impossible to implement
using the operating system’s standard tools. The reader should be aware that both MAC and DAC stand
for many other things. DAC also stands for Digital/Analog Converter. MAC is also used as an acronym for
Message Authentication Code in secure transport systems, Medium or Media Access Control protocols
determine rules for fairly sharing wireless bandwidth. With respect to security policies, however, they
stand for Mandatory and Discretionary access control.

8 What is a Transaction? Explain ACID properties of a transaction in detail.

Ans-

In computer programming, a transaction usually means a sequence of information exchange and related
work (such as database updating) that is treated as a unit for the purposes of satisfying a request and for
ensuring database integrity. For a transaction to be completed and database changes to made
permanent, a transaction has to be completed in its entirety. A typical transaction is a catalog
merchandise order phoned in by a customer and entered into a computer by a customer representative.
The order transaction involves checking an inventory database, confirming that the item is available,
placing the order, and confirming that the order has been placed and the expected time of shipment. If
we view this as a single transaction, then all of the steps must be completed before the transaction is
successful and the database is actually changed to reflect the new order. If something happens before
the transaction is successfully completed, any changes to the database must be kept track of so that they
can be undone.

DOWNLOAD THIS FREE GUIDE

What should be in a CIO’s IT strategic plan?

This complimentary document comprehensively details the elements of a strategic IT plan that are
common across the board – from identifying technology gaps and risks to allocating IT resources and
capabilities. The [Link] team has compiled its most effective, most objective, most valued
feedback into this single document that’s guaranteed to help you better select, manage, and track IT
projects for superior service delivery.

Corporate E-mail Address:

Corporate email address

Download Now

By submitting your personal information, you agree that TechTarget and its partners may contact you
regarding relevant content, products and special offers.

You also agree that your personal information may be transferred and processed in the United States,
and that you have read and agree to the Terms of Use and the Privacy Policy.

A program that manages or oversees the sequence of events that are part of a transaction is sometimes
called a transaction monitor. Transactions are supported by Structured Query Language, the standard
database user and programming interface. When a transaction completes successfully, database changes
are said to be committed; when a transaction does not complete, changes are rolled back. In IBM´s
Customer Information Control System product, a transaction is a unit of application data processing that
results from a particular type of transaction request. In CICS, an instance of a particular transaction
request by a computer operator or user is called a task.

Less frequently and in other computer contexts, a transaction may have a different meaning. For
example, in IBM mainframe operating system batch processing, a transaction is a job or a job step.

SECTION B

Case study
A relational database is to be designed for a medium sized Company dealing with industrial applications
of computers. The Company delivers various products to its customers ranging from a single application
program through to complete installation of hardware with customized software. The Company employs
various experts, consultants and supporting staff. All personnel are employed on long-term basis, i.e.
there is no short-term or temporary staff. Although the Company is somehow structured for
administrative purposes (that is, it is divided into departments headed by department managers) all
projects are carried out in an inter-disciplinary way. For each project a project team is selected, grouping
employees from different departments, and a Project Manager (also an employee of the Company) is
appointed who is entirely and exclusively responsible for the control of the project, quite independently
of the Company´s hierarchy. The following is a brief statement of some facts and policies adopted by the
Company:

Each employee works in some department.

An employee may possess a number of skills

Every manager (including the MD) is an employee

A department may participate in none/one/many projects.

At least one department participates in a project.

An employee may be engaged in none/one/many projects

Project teams consist of at least one member.

[Link] 1: Analyze the data required. Identify all entities and attributes.

Ans-

Data analysis is concerned with the NATURE and USE of data. It involves the identification of the data
elements which are needed to support the data processing system of the organization, the placing of
these elements into logical groups and the definition of the relationships between the resulting groups.

Other approaches, e.g. [Link] and Flowcharts, have been concerned with the flow of data-dataflow
methodologies. Data analysis is one of several data structure based methodologies Jackson SP/D is
another.
Systems analysts often, in practice, go directly from fact finding to implementation dependent data
analysis. Their assumptions about the usage of properties of and relationships between data elements
are embodied directly in record and file designs and computer procedure specifications. The
introduction of Database Management Systems (DBMS) has encouraged a higher level of analysis, where
the data elements are defined by a logical model or `schema´ (conceptual schema). When discussing the
schema in the context of a DBMS, the effects of alternative designs on the efficiency or ease of
implementation is considered, i.e. the analysis is still somewhat implementation dependent. If we
consider the data relationships, usages and properties that are important to the business without regard
to their representation in a particular computerised system using particular software, we have what we
are concerned with, implementation independent data analysis.

It is fair to ask why data analysis should be done if it is possible, in practice to go straight to a
computerised system design. Data analysis is time consuming; it throws up a lot of questions.
Implementation may be slowed down while the answers are sought. It is more expedient to have an
experienced analyst `get on with the job´ and come up with a design straight away. The main difference
is that data analysis is more likely to result in a design which meets both present and future
requirements, being more easily adapted to changes in the business or in the computing equipment. It
can also be argued that it tends to ensure that policy questions concerning the organisations´ data are
answered by the managers of the organisation, not by the systems analysts. Data analysis may be
thought of as the `slow and careful´ approach, whereas omitting this step is `quick and dirty´.

From another viewpoint, data analysis provides useful insights for general design principals which will
benefit the trainee analyst even if he finally settles for a `quick and dirty´ solution.

The development of techniques of data analysis have helped to understand the structure and meaning
of data in organisations. Data analysis techniques can be used as the first step of extrapolating the
complexities of the real world into a model that can be held on a computer and be accessed by many
users. The data can be gathered by conventional methods such as interviewing people in the
organisation and studying documents. The facts can be represented as objects of interest. There are a
number of documentation tools available for data analysis, such as entityrelationship diagrams. These
are useful aids to communication, help to ensure that the work is carried out in a thorough manner, and
ease the mapping processes that follow data analysis. Some of the documents can be used as source
documents for the data dictionary.

In data analysis we analyse the data and build a systems representation in the form of a data model
(conceptual). A conceptual data model specifies the structure of the data and the processes which use
that data.

Data Analysis = establishing the nature of data.

Functional Analysis = establishing the use of data.

However, since Data and Functional Analysis are so intermixed, we shall use the term Data Analysis to
cover both.

[Link] 2: Draw the Relational Database Schema for the Company database.

Ans-

Semantic Modeling: Employee has ID, salary and reports to a manager who is also an employee.

The conceptual graph (CG) is

Employee --has--> ID --has--> Salary --report-to-> Manager Manager --is-a-> Employee

ER Diagram

ID, Salary and Manager are attributes of the entity Employee.

3. Dependency graph

ID -> Salary, ID -> Manager

4. Database schema

EMP (ID, SALARY, and MANAGER)

Note: MANAGER is a foreign key of EMP and its domain is subset of the domain of ID.

DESIGN OF OTHER DATABASE MODELS

After the ER diagram is obtained, instead of the relational database model, we can also map the ER
diagram into the network model, the hierarchical model or the object-oriented database model.

Given a conceptual model such as the ER diagram, we want to MAP the conceptual design to:

Relational

Hierarchical

Network

Object-Oriented

Can we do this for any ER diagram?

Suppose this can be done, what are the implications?

ER schema diagram for the COMPANY database:

ER-TO-RELATIONAL MAPPING

STEP 1: For regular entity type E in ER schema, create a relation R that includes all the simple attributes,
and component attributes of composite attributes. Select the primary key.

STEP 2: For weak entity type W in ER schema, with owner entity type E, create a relation R, include all
simple attributes (or component attributes of composite attributes) of W as attributes of R. In addition,
include the primary key attributes of the relation Q for the owner entity type E. Primary key is the
combination of primary key of Q and partial key of R.

STEP 3: For 1:1 relationship X, suppose S and T are the relations for the entity types participating in it.
Include primary key of T as foreign key of S. Include other attributes of the relationship X as attributes of
S.

ER-TO-RELATIONAL MAPPING (CONTINUED)

STEP 4: For 1:N relationship Y, suppose S relation corresponds to the entity type at the N-side, and T
relation corresponds to the entity type at the other side. Include primary key of T as foreign key of S.

STEP 5: For M:N relationship Z, create a new relation R to represent Z. Include simple attributes of Z in R.
Include the primary keys of S and T as foreign keys of R, their combination forms the primary key of R.

STEP 6: For multivalued attribute A, create a new relation R that includes an attribute corresponding to
A. Include primary key of the relation of the entity type having A as an attribute. Primary key is their
combination.

ER-TO-RELATIONAL MAPPING (CONTINUED)

STEP 7: For n-ary relationship type X, and n>2, create a new relation R. Include primary key of each
participating entity type´s relation as foreign key of R. Include attributes of X as simple attributes of R.
SUMMARY

Entity Type -> Relations

Relationship Type

1:1 or 1:N -> absorbed in relations for entity types

M:N or N-ary -> Relations

[Link] 3: Draw the ER diagram. Mention proper cardinality.

Ans-

A Customer has a one-to-many relationship with a Purchase Order because a customer can place many
orders, but a given purchase order can be placed by only one customer. The relationship is optional
because zero customers might place a given order (it might be placed by someone not previously
defined as a customer).

A Purchase Order has a many-to-many relationship with a Stock Item because a purchase order can refer
to many stock items, and a stock item can be referred to by many purchase orders. However, you do not
know which purchase orders refer to which stock items.

Therefore, you introduce the notion of a Line Item. A Purchase Order has a one-to-many relationship
with a Line Item because a purchase order can list many line items, but a given line item can be listed by
only one purchase order.

A LineItem has a many-to-one relationship with a StockItem because a line item can refer to only one
stock item, but a given stock item can be referred to by many line items. The relationship is optional
because zero line items might refer to a given stock item.
SECTION C

A relational database consists of a collection of:

(A): Tables

(B): Attributes

(C): Tuples

(D): Keys

A ________ in a table represents a relationship among a set of values.

(A): Column

(B): Key

(C): Row
(D): Entry

The term _______ is used to refer to a row.

(A): Attribute

(B): Tuple

(C): Table

(D): Instance

For each attribute of a relation, there is a set of permitted values, called the ________ of that attribute.

(A): Domain

(B): Relation

(C): Set

(D): Schema

Course (course_id,sec_id,semester). Here the course_id,sec_id and semester are __________ and course
is a _________ .

(A): Relations, Attribute

(B): Attributes, Relation

(C): Tuple, Relation

(D): Tuple, Attributes

A domain is atomic if elements of the domain are considered to be ____________ units.

(A): Different

(B): Indivisbile

(C): Constant

(D): Divisible

The tuples of the relations can be of ________ order.

(A): Any

(B): Same

(C): Sorted
(D): Constant

A _____ is a property of the entire relation, rather than of the individual tuples in which each tuple is
unique.

(A): Rows

(B): Key

(C): Attribute

(D): Fields

Which one of the following attribute can be taken as a primary key?

(A): Name

(B): Street

(C): Id

(D): Department

Which one of the following cannot be taken as a primary key?

(A): Id
(B): Register number

(C): Dept_id

(D): Street

A _________ consists of a sequence of query and/or update statements.

(A): Transaction

(B): Commit

(C): Rollback

(D): Flashback

Which of the following makes the transaction permanent in the database?

(A): View

(B): Commit

(C): Rollback
(D): Flashback

Which one of the following is used to define the structure of the relation, deleting relations and relating
schemas?

(A): DML

(B): DDL

(C): Query

(D): Relational Schema

Select * from employee What type of statement is this?

(A): DDL

(B): Integrity constraint

(C): DML

(D): View

The basic data type char (n) is a _____ length character string and varchar(n) is _____ length character.

(A): Fixed, equal

(B): Equal, variable

(C): Fixed, variable

(D): Variable, equal

To remove a relation from an SQL database, we use the ______ command.

(A): Delete

(B): Purge

(C): Remove

(D): Drop

Updates that violate __________ are disallowed.

(A): Integrity constraints

(B): Transaction control

(C): Authorization
(D): DDL constraints

Database __________, which is the logical design of the database, and the database _______, which is a
snapshot of the data in the database at a given instant in time.

(A): Instance, Schema

(B): Relation, Schema

(C): Relation, Domain

(D): Schema, Instance

The union operation is represented by

(A): U

(B): n

(C): -

(D): *

If we want to retain all duplicates, we must write ________ in place of union.

(A): Union all

(B): Union some

(C): Intersect all

(D): Intersect some

For like predicate which of the following is true. i) % matches zero of more characters. ii) _ matches
exactly one character.

(A): i-only

(B): ii-only

(C): Both

(D): None

The number of attributes in relation is called as its

(A): Cardinality

(B): Degree

(C): Tuples
(D): Entity

_________ joins are SQL server default

(A): Outer

(B): Inner

(C): Equi

(D): None

Entity is a ____________

(A): Object of relation

(B): Present working model

(C): Thing in real world

(D): Model of relation

The attribute AGE is calculated from DATE_OF_BIRTH. The attribute AGE is

(A): Single valued

(B): Multi valued

(C): Composite

(D): Derived

Which of the following is a single valued attribute. Solve by [Link] contact for more details
at 8882309876

(A): Register_number

(B): Address

(C): SUBJECT_TAKEN

(D): Reference

Update instructor _____ salary= salary * 1.05; Fill in with correct keyword to update the instructor
relation.

(A): Where

(B): Set

(C): In
(D): Select

A stored procedure in SQL is a___________.

(A): Block of functions

(B): Group of Transact-SQL statements compiled into a single execution plan.

(C): Group of distinct SQL statements.

(D): None of mentioned

The____condition allows a general predicate over the relations being joined.

(A): On

(B): using

(C): set

(D): where

Which of the join operations do not preserve non matched tuples.

(A): Left outer join

(B): Right outer join

(C): Inner Join

(D): Natural join

In case of any shut down during transaction before commit which of the following statement is done
automatically?

(A): Commit

(B): Rollback

(C): Flashback

(D): View

A __________ is a special kind of a store procedure that executes in response to certain action on the
table like insertion, deletion or updation of data.

(A): Procedures

(B): Triggers
(C): Functions

(D): None of these

The default extension for an Oracle SQL*Plus file is:

(A): .txt

(B): .pls

(C): .ora

(D): .sql

The intersection operator is used to get the _____ tuples.

(A): Different

(B): Common

(C): All

(D): Repeating

The _____________ is essentially used to search for patterns in target string.

(A): Like Predicate

(B): Null Predicate

(C): In Predicate

(D): Out Predicate

He ______ clause allows us to select only those rows in the result relation of the ____ clause that satisfy
a specified predicate.

(A): Where, from

(B): From, select

(C): Select, from

(D): From, where

The ________ clause is used to list the attributes desired in the result of a query.

(A): Where

(B): Select
(C): From

(D): Distinct

Insert into employee _____ (1002,Joey,2000); In the given query which of the keyword has to be
inserted ?

(A): Table..

(B): Values

(C): Relation

(D): Field

The database administrator who authorizes all the new users, modifies database and takes grants
privilege is

(A): Administrator

(B): Super User

(C): Operator of OS

(D): All of the mentioned

The union operation automatically __________, unlike the select clause.

(A): Adds tuples

(B): Eliminates unique tuples

(C): Adds common tuples

(D): Eliminates duplicate

Back to top