RAMKRISHNA MAHATO GOVERNMENT ENGINEERING
COLLEGE
NAME –SUBHOJIT SAHA
ROLL NO – 35000122036
YEAR – 3RD
SEM – 6TH
PAPER NAME – DATABASE MANAGEMENT
SYSTEMS
PAPER CODE – PCC-CS601
Q1. ER diagram of the Company has the following description :
● Company has several departments.
● Each department may have several Locations.
● Departments are identified by a name, d_no, Location.
● A Manager control a particular department.
● Each department is associated with number of projects.
● Employees are identified by name, id, address, job, date_of_joining.
● An employee works in only one department but can work on several project.
● We also keep track of number of hours worked by an employee on a single project.
● Each employee has a dependent ● Dependent has D_name, Gender, and
relationship.
➢ Draw an ER Diagram for the above specification.
ER DIAGRAM OF A COMPANY
Data Abstraction:
Definition: Data abstraction refers to the hiding of details from users at certain levels, for
authentication and security purpose. Any DBMS architecture mainly consists of three levels
1. Physical or Internal level
2. Logical or Conceptual level
3. View or External level
Physical or Internal level
• This is the lowest level of abstraction describes how physical data is stored
• It provides details about the complex data structures that are used for storage of data
Logical or Conceptual level
• It describes the logical structure of the database
• Specifies what type of data can be stored in the database by defining the data type and
data type signs and constraints like primary key, foreign key etc.
View or External level
• The View or External Level is the highest level of data abstraction. There are different views
at this level that define the parts of the overall data of the database. This level is for the
end-user interaction
Data Independence
Data Independence is mainly defined as a property of DBMS that helps you to change the
database schema at one level of a system without requiring to change the schema at the next
level. We have namely two levels of data independence arising from these levels of abstraction:
❖ Physical level data independence
❖ Logical level data independence
Physical data independence
Physical data Independence states that conceptual level is completely unaffected are free from
any changes that are made at the internal level and vice-versa.
ex: Adding a new entity in the internal level should not affect the conceptual level.
Logical data independence
Logical data Independence states that external level is completely unaffected are free from any
changes that are made at the conceptual level and vice-versa.
ex: Adding a new entity in the conceptual level should not affect the external level.
Data Definition Language(DDL)
DDL is a Data Definition Language that is used to define data structures. DDL is used to specify a
database’s structure, which includes its tables, views, indexes, and constraints. For example:
creating a table, and altering a table are instructions in SQL.
DDL commands come in the following types: CREATE, ALTER, DROP, RENAME, and TRUNCATE.
DDL statements only modify the database’s schema; they have no direct effect on the data
within the database. DDL declarations are irreversible and difficult to undo.
Data Manipulation Language(DML)
DML is a Data Manipulation Language that is used to manipulate data itself. Inserting, updating,
removing, and retrieving data from a database are all possible with DML . DML commands
come in the following types: SELECT, INSERT, UPDATE, DELETE, and MERGE. DML statements
have a direct impact on the database’s data. In the event of an error, data can be recovered
thanks to the reversibility of DML statements.
Data Models:
Data Model is the modeling of the data description, data semantics, and consistency constraints
of the data. It provides the conceptual tools for describing the design of a database at each level
of data abstraction.
Entity-relationship model:
A high-level data model diagram is the
entity-relationship modelor ER Model. We
depict the real-world problem in visual form
in this model to make it easier for stakeholders to comprehend. An ER model is the logical
representation of data as objects and relationships among them. These objects are known as
entities, and relationship is an association among these entities. It was widely used in database
designing. A set of attributes describe the entities. For example, student_name, student_id
describes the 'student' entity.
Network Model:
The Network Model was formalized by the Database Task group in the 1960s. This model is the
generalization of the hierarchical model. This model can consist of multiple parent segments
and these segments are grouped as levels but there exists a logical association between the
segments belonging to any level. Mostly, there exists a many-to-many logical association
between any of the two segments.
Relational Data Model:
This type of model designs the data in the form of rows and columns within a table. Thus, a
relational model uses tables for representing data and in-between relationships. Tables are
also called relations. This model was initially described by Edgar F. Codd, in 1969. The
relational data model is the widely used model which is primarily used by commercial data
processing applications.
Object-Oriented Data Model:
In the Object-Oriented Data Model, data and their relationships are contained in a single
structure which is referred to as an object in this data model. In this, real-world problems are
represented as objects with different attributes. All objects have multiple relationships
between them. We can now store audio, video, pictures, and other types of data in databases,
which was previously impossible with the relational approach.
Integrity Constraints:
These are rules that ensure the accuracy and consistency of the data within the database.
Common integrity constraints include:
➢ Primary Key Constraint: Ensures that each record in a table is uniquely identifiable.
➢ Foreign Key Constraint: Ensures that the value in one table matches a value in
another table, maintaining referential integrity.
➢ Unique Constraint: Ensures that all values in a column are unique.
➢ Not Null Constraint: Ensures that a column cannot have a NULL value.
Data Manipulation Operations:
These are the basic operations that can be performed on the data within the database,
including:
➢ Insertion: Adding new records to a table.
➢ Deletion: Removing records from a table.
➢ Update: Modifying existing records in a table.
➢ Selection: Retrieving specific records based on certain conditions.
THANK YOU
