Mid Exam
Mid Exam
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 :
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.
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
• Complexity
• Size
• Cost of DBMS
• Cost of conversion
• Performance
• Higher impact of a failure
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:
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:
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.
• 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.