Mid Exam

Download as pdf or txt
Download as pdf or txt
You are on page 1of 5

Mid Exam

Q No 1 (a) Describe the approach taken to the handling of data in the early file-based systems.
Discuss the disadvantages of this approach.

Answer: -

File-based System A collection of application programs that perform services for the system end-
users such as the production of reports. Each program defines and manages its own data.

File-based systems were application first then data, and data would be stored in a file or files from
the program.

Disadvantages: -

Duplication of data: Owing to the decentralized approach taken by each department, the file-based
approach encouraged, if not necessitated, the uncontrolled duplication of data.

Separation and isolation of data: When data is isolated in separate files, it is more difficult to access
data that should be available.

Incompatible file formats: Because the structure of files is embedded in the application programs,
the structures are dependent on the application programming language. For example, the structure
of a file generated by a COBOL program may be different from the structure of a file generated by a
‘C’ program.

More disadvantages were the difficulty (or impossibility) of cross-referencing data, restricted to one
user at a time, and vast difficulty of recovering data in case of a hardware/software failure.

Q no 1 (b) Describe the main characteristics of the database approach and contrast it with the file-
based approach.

Answer:

The database approach focuses on data first, then application. Structure of data is kept separate
from programs that operate on data- held in the system catalogue or data dictionary. Programs can
now share data and cross reference.

The database is a single, possibly large repository of data that can be used simultaneously by many
departments and users. Instead of disconnected files with redundant data, all data items are
integrated with a minimum amount of duplication. The database is no longer owned by one
department but is a shared corporate resource. The database holds not only the organization’s
operational data but also a description of this data. For this reason, a database is also defined as a
self-describing collection of integrated records.

The main characteristics of the database approach (feature of database approach) And how it
differs from the traditional file system approach :

• Self-describing nature of a database system:


A fundamental characteristics of database approach is that the database system contains not
only the database itself but also a complete definition or description of the database
structure and constraints.
• Support of multiple views of the data:

A database typically has many users, each of whom may require a different perspective or
view of the database. A view may be a subset of the database or it may contain virtual data
that is derived from the database files but is not explicitly stored.

• Sharing of data and multiuser transaction processing:

A multiuser DBMS, as its name implies, must allow multiple users to access the database at
the same time. This is essential if data for multiple applications is to be integrated and
maintained in a single database.
Advantages of DBMSs over traditional file-based system

• Control of data redundancy


• Data consistency
• More information from the same amount of data
• Sharing of data
• Improved data integrity
• Improved security
• Enforcement of standards
• Economy of scale
• Balance conflicting requirements

The Database approach too has some disadvantages. They are:

• Complexity
• Size
• Cost of DBMS
• Cost of conversion
• Performance
• Higher impact of a failure

Q no 2 (a): To address the issue of data independence, the ANSI-SPARC three-level


architecture was proposed. Compare and contrast the three levels of this model.

The Architecture of most of commercial dbms are available today is mostly based on this ANSI-
SPARC database architecture.
ANSI SPARC THREE-TIER architecture has main three levels:

1. Internal Level
2. Conceptual Level
3. External Level

These three levels provide data abstraction; means hide the low-level complexities from end users.
A database system should be efficient in performance and convenient in use.
Using these three levels, it is possible to use complex structures at internal level for efficient
operations and to provide simpler convenient interface at external level.

1. Internal level:

• This is the lowest level of data abstraction.


• It describes How the data are actually stored on storage devices.
• It is also known as physical level.
• It provides internal view of physical storage of data.
• It deals with complex low level data structures, file structures and access methods in detail.
• It also deals with Data Compression and Encryption techniques, if used.

2. Conceptual level:

• This is the next higher level than internal level of data abstraction.
• It describes What data are stored in the database and What relationships exist among those
data.
• It is also known as Logical level.
• It hides low level complexities of physical storage.
• Database administrator and designers work at this level to determine What data to keep in
database.
• Application developers also work on this level.

3. External Level:

• This is the highest level of data abstraction.


• It describes only part of the entire database that a end user concern.
• It is also known as an view level.
• End users need to access only part of the database rather than entire database.
• Different user needs different views of database. And so, there can be many view level
abstractions of the same database
Qn2 (B) Describe the differences between a relation and a relation schema. What is a
relational database schema?

A relation schema can be thought of as the basic information describing a table or relation. It is the
logical definition of a table. Relation schema defines what the name of the table is. This includes a
set of column names, the data types associated with each column.

Relational schema may also refer to as database schema. A database schema is the collection of
relation schemas for a whole database. Relational or Database schema is a collection of meta-
data. Database schema describes the structure and constraints of data representing in a particular
domain. A Relational schema can be described a blueprint of a database that outlines the way data
is organized into tables. This blueprint will not contain any type of data. In a relational schema, each
tuple is divided into fields called Domains.

There are different kinds of database schemas:

• Conceptual schema
• Logical schema
• Physical schema

Q no 2

C. Discuss the differences between the candidate keys and the primary key of a relation. Explain
what is meant by a foreign key. How do foreign keys of relations relate to candidate keys? Give
examples to illustrate your answer.

The primary key is the candidate key that is selected to identify tuples uniquely within a relation. A
foreign key is an attribute or set of attributes within one relation that matches the candidate key of
some (possibly the same) relation. It acts as a cross-reference between tables because it references
the primary key of another table, thereby establishing a link between them.

While a primary key may exist on its own, a foreign key must always reference to a primary key
somewhere. The original table containing the primary key is the parent table (also known as
referenced table). This key can be referenced by multiple foreign keys from other tables, known as
“child” tables.
For any column acting as a foreign key, a corresponding value should exist in the linked table. Special
care must be taken while inserting data and removing data from the foreign key column, as a
careless deletion or insertion might destroy the relationship between the two tables.
If the integrity between the two databases is compromised, errors may ensue.
Qno 3 Write Simple queries for the DreamHome schema for the following
statements.

List full details of all staff.


SELECT * FROM Staff;
Produce a list of salaries for all staff, showing only the staff number, the
first and last names, and the salary details.
SELECT staffNo, fName, lName, salary FROM Staff;
List the property numbers of all properties that have been viewed.
SELECT propertyNo FROM Viewing;
Produce a list of monthly salaries for all staff, showing the staff number,
the first and last names, and the salary details.
SELECT staffNo, fName, lName, salary/12 FROM Staff;
List the addresses of all branch offices in London or Glasgow.
SELECT * FROM Branch WHERE city = ‘London’ OR city = ‘Glasgow’;
List all managers and supervisors.
SELECT staffNo, fName, lName, position FROM Staff WHERE position IN
(‘Manager’, ‘Supervisor’);
Find all owners with the string ‘Glasgow’ in their address.
SELECT ownerNo, fName, lName, address, telNo FROM PrivateOwner WHERE
address LIKE ‘%Glasgow%’;

You might also like