1

a)Compare and contrast file system with database system.

File System Database System

Software that manages the data files in databases. Software to create and manage databases.
Backup and Recovery process not efficient because is not possible It has backup and recovery.
to recover data.
Helps to store the collection of raw data files into the disk. Helps to easily store,retrieve and manipulate data In a database.
Has data inconsistency. Provides higher data consistency using Normalization.
There is more redundant data. There is low data.
Provides more security data. Less data security.
Handling is Easy. Handling is Complex.
Ex:-NTFS and Ext. Ex:-MySql,DB2,Oracle,Mssql.

b)List various categories of database users and discuss their interface to DBMS.

application programmer: user who implements specific application programs to access the stored data.

application user: Accesses an existing application program to perform daily tasks.

database administrator (DBA): responsible for authorizing access to the database, monitoring its use and managing all the resources to
support the use of the entire database system.

end user:people whose jobs require access to a database for querying, updating and generating reports.

sophisticated user:those who use other methods, other than the application program, to access the database.

c)Explain the architecture of DBMS with a neat diagram and discuss the functionality of query evaluation engine in detail.
The architecture of a database system is greatly influenced by the underlying computer system on which the database is running:

i. Centralized.

ii. Client-server.

iii. Parallel (multi-processor).

iv. Distributed

Query Processor:

The query processor will accept query from user and solves it by accessing the database.

Parts of Query processor:

DDL interpreter:This will interprets DDL statements and fetch the definitions in the data dictionary.

DML compiler:This will translates DML statements in a query language into low level instructions that the query evaluation engine
understands.

A query can usually be translated into any of a number of alternative evaluation plans for same query result DML compiler will select
best plan for query optimization.

Query evaluation engine:This engine will execute low-level instructions generated by the DML compiler on DBMS.
Storage Manager/Storage Management:

• A storage manager is a program module which acts like interface between the data stored in a database and the application
programs and queries submitted to the system.
• Thus, the storage manager is responsible for storing, retrieving and updating data in the database.

2)
a)Discuss the additional features of ER model.
Additional Features of the ER Model :Er model allows us to the describe some properties of the data .additional features of er model
are as fallows :

• Generalization
• Specialization
• Aggregation
• Inheritance

Generalization:

The process of Generalization is entities where the similar set of entities contains the common properties of all generalized entities.
Ex:-

Specialization:

Specialization is the process of identifying the subsets of an entity set shares common distinguish characters.

Ex:-take a group of people

Aggregation:

Aggregation is an abstraction in which relationship sets are treated has higher level entity

set. Aggregation allows us to indicated that relationship set participants in another relationship set.

Inheritance:
Inheritance is an important feature of generalization and specialization .it allows low-level entities to inherit the attributes of high level

entities .

c)Explain different types of attributes,entities and relationships.

Entities:

The following are the types of entities in DBMS:

Strong Entity:

The strong entity has a primary key. Weak entities are dependent on strong entity. Its existence is not dependent on any other entity.Strong
Entity is represented by a single rectangle:

Weak Entity:

The weak entity in DBMS do not have a primary key and are dependent on the parent entity. It mainly depends on other entities.

Weak Entity is represented by double rectangle:

Attributes:
ATOMIC ATTRIBUTE
An attribute that cannot be divided into smaller independent attribute is known as atomic attribute.

For example, assume Student is an entity and its attributes are Name, Age, Address and Phone no. Here the age (attribute) of student
(entity) cannot further divide. In this example age is atomic attribute.
SINGLE VALUED ATTRIBUTE

An attribute that has only single value for an entity is known as single valued attribute.

For example, assume Student is an entity and its attributes are Name, Age, Address and Phone no. Here the age (attribute) of student
(entity) can have only one value. Here, age is single valued attribute.

MULTI VALUED ATTRIBUTE

An attribute that can have multiple values for an entity is known as multi valued attribute.

For example, assume Student is an entity and its attributes are Name, Age, Address and Phone no. Here the Phone no (attribute) of
student (entity) can have multiple value because a student may have many phone numbers. Here, Phone no is multi valued attribute.

STORED ATTRIBUTE

An attribute that cannot be derived from another attribute is known as stored attribute.

For example, birth date cannot derive from age of student.

DERIVED ATTRIBUTE

An attribute that can be derived from another attribute is known as derived attribute.
For example, age cannot derive from birth date of student.

NULL VALUED ATTRIBUTE

An attribute, which has not any value for an entity is known as null valued attribute.

For example, assume Student is an entity and its attributes are Name, Age, Address and Phone no. There may be chance when a student
has no phone no. In that case, phone no is called null valued attributes.

KEY ATTRIBUTE

An attribute that has unique value of each entity is known as key attribute.

For example, every student has unique roll no. Here roll no is key attribute.

RELATIONSHIPS:

One-to-One

A row in table A can have only one matching row in table B, and vice versa.

Example of a one-to-one relationship

One-to-Many (or Many-to-One)

This is the most common relationship type. In this type of relationship, a row in table A can have many matching rows in table B, but a
row in table B can have only one matching row in table A.

Example of one-to-many relationship.

Many-to-Many
In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa.

A many-to-many relationship could be thought of as two one-to-many relationships, linked by an intermediary table.
3)

A)Explain the main components of dbms and briefly explain their functionality.

The database management system can be divided into five major components, they are:

1. Hardware
2. Software
3. Data
4. Procedures
5. Database Access Language

DBMS Components: Hardware

When we say Hardware, we mean computer, hard disks, I/O channels for data, and any other physical component involved before any
data is successfully stored into the memory.

When we run Oracle or MySQL on our personal computer, then our computer's Hard Disk, our Keyboard using which we type in all the
commands, our computer's RAM, ROM all become a part of the DBMS hardware

DBMS Components: Software

This is the main component, as this is the program which controls everything. The DBMS software is more like a wrapper around the
physical database, which provides us with an easy-to-use interface to store, access and update data.

The DBMS software is capable of understanding the Database Access Language and intrepret it into actual database commands to execute
them on the DB.

DBMS Components: Data

Data is that resource, for which DBMS was designed. The motive behind the creation of DBMS was to store and utilise data.

In a typical Database, the user saved Data is present and meta data is stored.

Metadata is data about the data. This is information stored by the DBMS to better understand the data stored in it.

For example: When I store my Name in a database, the DBMS will store when the name was stored in the database, what is the size of
the name, is it stored as related data to some other data, or is it independent, all this information is metadata.

DBMS Components: Procedures

Procedures refer to general instructions to use a database management system. This includes procedures to setup and install a DBMS, To
login and logout of DBMS software, to manage databases, to take backups, generating reports etc.

DBMS Components: Database Access Language

Database Access Language is a simple language designed to write commands to access, insert, update and delete data stored in any
database.

A user can write commands in the Database Access Language and submit it to the DBMS for execution, which is then translated and
executed by the DBMS.

User can create new databases, tables, insert data, fetch stored data, update data and delete the data using the access language.
b)Explain data abstraction,view of data,instance,schema in dbms?.

DBMS Instance. Definition of instance: The data stored in database at a particular moment of time is called instance of database.
Database schema defines the variable declarations in tables that belong to a particular database; the value of these variables at a moment
of time is called the instance of that database.
Data abstraction:
This process of hiding irrelevant details from user is called data abstraction. ... You can get the complex data structure details at this
level. Logical level: This is the middle level of 3-level data abstraction architecture. It describes what data is stored in database. View
level: Highest level of data abstraction.

Schema:A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is
organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data. ... It defines
tables, views, and integrity constraints.

View of Data in DBMS. ... The view level provides the “view of data” to the users and hides the irrelevant details such as data
relationship, database schema, constraints, security etc from the user. To fully understand the view of data, you must have a basic
knowledge of data abstraction and instance & schema.

c)Define DBA and explain the functionalities of DBA.

Functions and responsibilities of DBAs

DBA: person in the organization who controls the design and the use of the database refers as DBA.

1. Schema Definition:
• The DBA definition the logical Schema of the database.A Schema refers to the overall logical structure of the database.
• According to this schema, database will be developed to store required data for an organization.

2. Storage Structure and Access Method Definition:

• The DBA decides how the data is to be represented in the stored database.

3. Assisting Application Programmers:

• The DBA provides assistance to application programmers to develop application programs.

4. Physical Organization Modification:

• The DBA modifies the physical organization of the database to reflext the changing needs of the organization or to improve
performance.

5. Approving Data Access:

• The DBA determines which user needs access to which part of the database.
• According to this,various types of authorizations are granted to different users.

6. Monitoring Performance:
• The DBA monitors performance of the system.The DBA ensures that better performance is maintained by making changes in
physical or logical schema if required.

7. Backup and Recovery:

• Database should not be lost or damaged.
• The DBA ensures this periodically backing up the database on magnetic tapes or remote servers.
• In case of failure, such as virus attack database is recovered from this backup.
5)

a)Explain the different types of data models?

Database Models

A database model is a theory or specification describing how a database is structured and used. Several such models have been suggested.
The common models include

 Network Model - Any links supporting quick access.

 Hierarchical Model - Links but no cycles (hierarchy).
 Relational Model - Data Independence.
 Object Oriented Model - Entity Abstractio

hierarchical model: represents data as a hierarchical tree structure

instance: a record within a table

network model: represents data as record types

relation: another term for table

relational model: represents data as relations or tables

set type: a limited type of one to many relationship

b)Explain the fallowing

1)data independence 2)physical independence 3)logical independence?

Data Independence Types. ... It means we change the physical storage/level without affecting the conceptual or external view of the
data. The new changes are absorbed by mapping techniques. Logical data independence is the ability to modify the logical schema
without causing application program to be rewritten.

Physical independence: It means we change the physical storage/level without affecting the conceptual or external view of the data. The
new changes are absorbed by mapping techniques. Logical data independence is the ability to modify the logical schema without
causing application program to be re writte.

Logical data independence is the one which has power (Ability) to change logical schema without changing external schema or
application. Logical data independence in any database applications relates to views where we can only view the data rather than
modifying data.

c)Explain database language in detail?

Data Definition Language (DDL)

It is a language that allows the users to define data and their relationship to other types of data. It is mainly used to create files, databases,
data dictionary and tables within databases.

It is also used to specify the structure of each table, set of associated values with each attribute, integrity constraints, security and
authorization information for each table and physical storage structure of each table on disk.

The following table gives an overview about usage of DDL statements in SQL
Data Manipulation Language (DML)
It is a language that provides a set of operations to support the basic data manipulation operations on the data held in the databases. It
allows users to insert, update, delete and retrieve data from the database. The part of DML that involves data retrieval is called a query
language.

The following table gives an overview about the usage of DML statements in SQL:

Data Control Language (DCL)

DCL statements control access to data and the database using statements such as GRANT and REVOKE. A privilege can either be granted
to a User with the help of GRANT statement. The privileges assigned can be SELECT, ALTER, DELETE, EXECUTE, INSERT, INDEX
etc. In addition to granting of privileges, you can also revoke (taken back) it by using REVOKE command.

The following table gives an overview about the usage of DCL statements in SQL:

In practice, the data definition and data manipulation languages are not two separate languages. Instead they simply form parts of a single
database language such as Structured Query Language (SQL). SQL represents combination of DDL and DML, as well as statements for
constraints specification and schema evaluation.

4)

a)Explain the concept of hierarchical with relevant ER diagrams?

Class Hierarchies(Specialization and Generalization)Class hierarchies can be viewed in one of two ways:
People-
-Students
-Graduate
-Undergraduate-Part time-Full time—Employees
-Academic-Non-academic
-Permanent-Contract1.
People can be specialized into Students and Employees (Specialization-Top DownApproach).
Specialization is the process of identifying the subsets of a superclass (entity set) that share some special attributes.
2.Student and Employees can be generalized into People. (Generalization-Bottom Up Approach).
Generalization is the process of identifying common characteristics from a collection of entity sets and creating a new set that
possesses these characteristics.
Overlapping vs. Disjoint:Disjoint:
There is no entity common to the subclassesOverlapping–There is at least one entity common to the subclasses.
Full vs. Partial:Full :
All the entities of the super class belong to at least one of the subclasses. Also known as covering.Partial–At least one of the entities of the
super class doesn’t belong to either of the subclasses.
The two main reasons for identifying subclasses:
1.To add descriptive attributes that makessense only for the entities in the subclasses.
2.To identify the set of entities that participates in some relationship.
b)Explain the phases to database design ?
We can say that the consistency of a data is achieved when the database is designed in such a way so it can store only useful and
often most required data.
In this article, we will explain you the main phases that create database design and their roles in the design.

1. Conceptual design

When every data requirement is stored and analyzed, the next thing that we need to do is creating a conceptual database plan. Here, a
highly leveled conceptual data model is used. This phase is called conceptual design.

When the conceptual design phase is in progress, the basic data modeling operations can be deployed to define the high-level user
operations that are noted during analysis of the functions.

2. Logical Design

The logical phase of database design is also called the data modeling mapping phase. This phase gives us a result of relation schemas.
The basis for these schemas is the ER or the Class Diagram.

To create the relation schemas is mainly mechanical operation. There are rules for transferring the ER model or class diagram to relation
schema.

3. Normalization

Normalization is, in fact, the last piece of the logical design puzzle. The main purpose of normalization is to remove superfluity and every
other potential anomaly during the update.

Normalization in database design is a way to change the relation schema to reduce any superfluity. With every normalization phase, a new
table is added to the database.

4. Physical Design

The last phase of database design is the physical design phase. In this phase, we implement the database design. Here, a DBMS (Database
Management System) must be chosen to use.

For instance, different DBM systems have different names for every datatype and they have different data types.

SQL clauses are written to help in creating the database. Also, the indexes and the integrity constraints (rules) are defined in this phase.
And finally the data is added and the database can finally be tested.

c)draw the ER diagram for HOSPITAL

c)draw the ER diagram for ONLINE SHOPPING WEBSITES.(lab reference).