Database is a collection of data and Management System is a set of programs
to store and retrieve those data
Dbms=A Database Management System (DBMS) is software designed to store,
retrieve, define, and manage data in a database.
Why DBMS = to store large amount of data
RDBMS=RDBMS stands for Relational DataBase Management Systems.
It is basically a program that allows us to create, delete, and update a
relational database.
Application of DBMS=Telecom
BANKING
ONLINE SHOPPING
EDUCATI0ON SECTOR
ADVANTAGE OF DBMS OVER FILE SYSTEM
• No redundant data= means No data duplication saves storage and
improves access time.
• Data Security= Each user has a different set of access thus data is
secured from the issues such as identity theft, data leaks and misuse of
data.
• Easy access to data with fast response time
• Easy recovery
Disadvantages
• BMS implementation cost is high compared to the file system
• Complexity: Database systems are complex to understand
Metadata=a set of data that describes and gives information about other data.
ARCHITECTURE =SINGLE TIER, 2 TIER,3 TIER
2 TIER= The Database system is present at the server machine and the
DBMS application is present at the client machine, these two machines
are connected with each other through a reliable network.
JDBC, ODBC
3 TIER=client application communicates with server application and
the server application internally communicates with the database
system present at the server.
This architecture has three levels:
1. External level = VIEW LEVEL
2. Conceptual level
3. Internal level
1) It is also called view level. because several users can view their
desired data from this level which is internally fetched from
database
2)It is also called logical level. The whole design of the database such as
relationship among data, schema of data etc. are described in this level.
3) This level is also known as physical level. This level describes how the data
is actually stored in the storage devices
o 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.
Constraints enforce limits to the data or type of data that can be
inserted/updated/deleted from a table.
Types
• NOT NULL=COLOUMN DOES NOT HOLD NULL VALUE
• UNIQUE
• DEFAULT = PROVIDE DEFAULT VALUE
• CHECK
• Key Constraints – PRIMARY KEY, FOREIGN KEY
Data transparency=The ability to easily access and work with data no matter
where they are located or what application created them
KEY= it is ATTRIBUTES
2)it is used for identifying unique rows from table.
Primary Key –set of columns in a table that uniquely identifies tuples
(rows) in that table, MUST CONTAIN UNIQUE VALUES.
Candidate Key – The minimal set of attributes that can uniquely identify a tuple is
known as a candidate key. STUD_NO in STUDENT relation
Foreign Key – Foreign keys are the columns of a table that points to the
primary key of another table, CONDITION=value A refers to column value B,
then column value B must exist.
Super Key – A super key is a set of one of more columns (attributes) to
uniquely identify rows in a table.
Primary key/candidate key
Primary Key is a unique and non-null key which identify a record uniquely in table.
A table can have only one primary key.
Candidate key is also a unique key to identify a record uniquely in a table but a table
can have multiple candidate keys.
Data Model is a logical structure of Database
Object based logical Models – Describe data at the conceptual and view
levels.
1. E-R Model
2. Object oriented Model
Record based logical Models=models specify logical structure of database
with records, fields and attributes
1. Relational Model
2. Hierarchical Model
Physical Data Models – These models describe data at the lowest level of
abstraction.
ER MODEL
An ER model is a design or blueprint of a database that can later be
implemented as a database. The main components of E-R model are: entity
set and relationship set.
THREE COMPONENTS
1. Entity=An entity is an object or component of data. An entity is
represented as rectangle in an ER diagram
Weak entity=entity that cannot be uniquely identified by its own
attributes, only exist when owned by another one eg=a ROOM can only exist in a
BUILDING
2. ATTRIBUTES= An attribute describes the property of an entity. An
attribute is represented as Oval in an ER diagram1. Key attribute
2. Composite =That is a combination of other attributes EG ADDRESS.
3. Multivalued =hold multiple values is known as multivalued attribute
EG PHONE NO., DOUBLE OVAL REPRESNTATION
4. Derived- Person age is a derived attribute as it changes over time,
DASHED OVAL
3. RELATIONSHIP=A relationship is represented by diamond shape in ER
diagram, it shows the relationship among entities
Normalization
is a process of organizing the data in database to avoid data redundancy
ANHOMALIES=There are three types of anomalies that occur when the database
is not normalized. These are – Insertion, update and deletion anomaly.
• First normal form(1NF)
• Second normal form(2NF)
• Third normal form(3NF)
• Boyce & Codd normal form (BCNF)
1ST NF=each attribute of a table must have atomic (single) values”3
2nf=no non-prime attribute is dependent on the proper subset of any
candidate key of the table
3rd nf= in 2nd NF +NO TRANSTITIVE DEPENDENCY, no non prime attribute determine none prime
BCNF= in 3rd NF + LHS must be super key or candidate key
4th NF= BCNF+NO MULTIVALUED DEPENDENCY
5NF= IN 4THNF+ lossless decomposition(no join dependency)
JOINS= Keyword is used in SQL queries for joining two or more tables
used for combining column from two or more tables by using values common to
both tables.
TYPES
Inner Join=used to return all the values that have matching values in both the
tables Equi Join=uses the equivalence condition for fetching the value
Natural
Outer Join=used to return the records in both the tables whether it has matching
records in both the tables or not.
Left-Outer Join
Right-Outer Join
Full-Outer Join
Functional dependency= describe the relationship between attribute
1. Trivial functional dependency
2. Non-Trivial functional dependency
3. Multivalued functional =it consists of at least two attributes that are
dependent on a third attribute
4. Transitive functional dependency
Deadlock=A deadlock is a condition wherein two or more tasks are waiting for
each other in order to be finished but none of the task is willing to give up the
resources that other task needs
Transaction=A transaction is a set of logically related operations.
OPERATION IN TRANSACTION=transaction can have operations like read,
write, insert, update, delete.
Failure in transaction=Commit. Rollback:
