1.
Data Models :
There are three types of Data Models in dbms,
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
E-R Model:
● 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.
Semistructured Data Model:
● This type of data model is different from the other three data models
(explained above).
● The semistructured data model allows the data specifications at
places where the individual data items of the same type may have
different attributes sets.
● The Extensible Markup Language, also known as XML, is widely
used for representing the semistructured data.
Object-based Data Model:
● An extension of the ER model with notions of functions,
encapsulation, and object identity, as well.
● This model supports a rich type system that includes
structured and collection types.
2.Data abstraction :
In DBMS, there are three levels of data abstraction, which are as
follows:
Physical or Internal Level:
● The physical or internal layer is the lowest level of data
abstraction in the database management system.
● It is the layer that defines how data is actually stored in
the database.
● It defines methods to access the data in the database. It
defines complex data structures in detail, so it is very
complex to understand, which is why it is kept hidden from
the end user.
Logical or Conceptual Level:
● The logical or conceptual level is the intermediate or next
level of data abstraction.
● It explains what data is going to be stored in the database
and what the relationship is between them.
● It describes the structure of the entire data in the form of
tables. The logical level or conceptual level is less complex
than the physical level.
● With the help of the logical level, Data Administrators (DBA)
abstract data from raw data present at the physical level.
View or External Level:
● 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; at this level, end
users can access the data based on their queries.
3.Data independence :
Data independence can be explained using the three-schema
architecture.
Data independence refers characteristic of being able to modify
the schema at one level of the database system without altering
the schema at the next higher level.
Logical Data Independence
● Logical data independence refers characteristic of being
able to change the conceptual schema without having to
change the external schema.
● Logical data independence is used to separate the external
level from the conceptual view.
● If we do any changes in the conceptual view of the data,
then the user view of the data would not be affected.
● Logical data independence occurs at the user interface
level.
Physical Data IIndependence
● Physical data independence can be defined as the capacity
to change the internal schema without having to change the
conceptual schema.
● If we do any changes in the storage size of the database
system server, then the Conceptual structure of the
database will not be affected.
● Physical data independence is used to separate conceptual
levels from the internal levels.
● Physical data independence occurs at the logical interface
level.
3.Different types of constraints :
Constraints available in SQL are:
● NOT NULL
● UNIQUE
● PRIMARY KEY
● FOREIGN KEY
● CHECK
● DEFAULT
● CREATE INDEX
NOT NULL :
● NULL means empty, i.e., the value is not available.
● Whenever a table's column is declared as NOT NULL, then
the value for that column cannot be empty for any of the
table's records.
● There must exist a value in the column to which the NOT
NULL constraint is applied.
UNIQUE:
● Duplicate values are not allowed in the columns to which
the UNIQUE constraint is applied.
● The column with the unique constraint will always contain a
unique value.
● This constraint can be applied to one or more than one
column of a table, which means more than one unique
constraint can exist on a single table.
● Using the UNIQUE constraint, you can also modify the
already created tables.
PRIMARY KEY
● PRIMARY KEY Constraint is a combination of NOT NULL and
Unique constraints.
● NOT NULL constraint and a UNIQUE constraint together
forms a PRIMARY constraint.
● The column to which we have applied the primary
constraint will always contain a unique value and will not
allow null values
FOREIGN KEY
● A foreign key is used for referential integrity.
● When we have two tables, and one table takes reference
from another table, i.e., the same column is present in
both the tables and that column acts as a primary key in
one table. That particular column will act as a foreign key
in another table.
CHECK
● Whenever a check constraint is applied to the table's
column, and the user wants to insert the value in it, then
the value will first be checked for certain conditions before
inserting the value into that column.
● For example: if we have an age column in a table, then the
user will insert any value of his choice. The user will also
enter even a negative value or any other invalid value. But,
if the user has applied check constraint on the age column
with the condition age greater than 18. Then in such cases,
even if a user tries to insert an invalid value such as zero or
any other value less than 18, then the age column will not
accept that value and will not allow the user to insert it due
to the application of check constraint on the age column.
CREATE INDEX
● CREATE INDEX constraint is used to create an index on the
table. Indexes are not visible to the user, but they help the
user to speed up the searching speed or retrieval of data
from the database.
4.Relation algebra operations
5.DBA roles and responsibilities
6.Relational DB using ER diagram
7.DBMS architeure
8.SQL DDL and DML queries
9.DBMS goals
10.Instances and schemas
11.Two and Three tier architecture
12.File system VS DBMS
13.Different types of DB users
