Database
Collection of Data
Management System
a set of programs to store and retrieve data.
DBMS
(Database Management System)
A
... [Show More] collection of inter-related data that contains programs to store & access that data in an easy and effective manner.
What is the need of DBMS?
Store Data in an optimized and systematic manner
and
Retrieve Data Fast
What are some Examples Applications where we use Database Management Systems are:
Telecom
Industry
Banking
Education sector
Online shopping
Advantages of DBMS over a FILE System
no redundant data
Data consistency and Integrity
Secure
Privacy
Easy access to data
Easy recovery
Flexible
Disadvantages of DBMS over FILE System
Higher implementation Cost
Complexity
Performance for some applications
What must you have to understand the view of data?
Data Abstraction
Instance and Schema
What is data abstraction?
The process of hiding irrelevant details from user
What are the 3 levels of Data abstraction?
Physical
Logical
View
Physical abstraction level
This is the lowest level of data abstraction. It describes how data is actually stored in database. You can get the complex data structure details at this level.
records can be described as blocks of storage (bytes, gigabytes, terabytes etc.) in memory.
Logical abstraction level
- Middle level
- Describes what data is stored in database
- Developers and admins access this level.
- records can be described as fields and attributes along with their data types, their relationship among each other can be logically implemented.
View abstraction level
- Highest level of data abstraction.
- Describes the user interaction with database system.
user just interact with system with the help of GUI and enter the details at the screen, they are not aware of how the data is stored and what data is stored; such details are hidden from them.
Database Instance
The data stored in database at a particular moment of time
Database vs Instance
Database is the set of files where application data (the reason for a database) and meta data is stored.
An instance is the software (and memory) that Oracle uses to manipulate the data in the database.
Database Schema
Design of a database.
Defines the variable declarations in tables that belong to a particular database;
3 Schema levels
Physical
Logical
View
keys in DBMS
Used for identifying unique rows from table.
Establishes relationship among tables.
Types of keys
Primary
Super
Candidate
Alternate
Composite
Foreign
Primary Key
a column or set of columns in a table that uniquely identifies tuples (rows) in that table.
-can not contain duplicate values or nulls
Super Key
a set ofone or more columns (attributes) to uniquely identify rows in a table.
Candidate key
minimal super keys with no redundant attributes.
non-prime attribute
An attribute that is not part of any candidate key
prime attribute
An attribute that is a part of one of the candidate keys is known as prime attribute.
Super Key vs Candidate Key
Candidate keys are selected from the set of super keys.
they do not have any redundant attributes. That's the reason they are also termed as minimal super key.
Foreign Key
The columns of a table that points to the primary key of another table. They act as a cross-reference between tables.
Foreign keys are the columns of a table that points to the candidate key of another table.
Composite Key
A key that consists of more than one attribute to uniquely identify rows in a table is called composite key.
It is also known as compound key.
Alternate key
Out of all candidate keys, only one gets selected as primary key, remaining keys are known as alternative or secondary keys.
Normalization
a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly
Anomalies
problems that can occur in poorly planned, un-normalized databases where all the data is stored in one table
3 types of Anomalies in un-normalized db
Insertion
Update
Delete
Update anomaly Example:
In a table we have two rows for an employee Rick as he belongs to two departments of the company. If we want to update the address of Rick then we have to update the same in two rows or the data will become inconsistent. If somehow, the correct address gets updated in one department but not in other then as per the database, Rick would be having two different addresses, which is not correct and would lead to inconsistent data.
Insert anomaly Example:
Suppose a new employee joins the company, who is under training and currently not assigned to any department then we would not be able to insert the data into the table if emp_dept field doesn't allow nulls.
Delete anomaly Example:
Suppose, if at a point of time the company closes the department D890 then deleting the rows that are having emp_dept as D890 would also delete the information of employee Maggie since she is assigned only to this department.
How do you overcome anomalies?
Normalize Data
Common normal forms
First normal form (1NF)
Second normal form (2NF)
Third normal form(3NF)
Boyce & Codd normal form (BCNF)
First normal form (1NF)
an attribute (column) of a table cannot hold multiple values. It should hold only atomic values.
a single row for each unique value
Second normal form (2NF)
Table is in 1NF (First normal form)
No non-prime attribute is dependent on the proper subset of any candidate key of table.
break column into a new table instead of having to update it twice if there is more then one record. Ie. add details table for age, height, etc.
Third normal form(3NF)
Table must be in 2NF
Transitive functional dependency of non-prime attribute on any super key should be removed.
Example of 3NF
Break emp_zip, emp_state, emp_city and emp_district out of Employee table into Employee_Zip table so that they are not all dependent on employee_id. Then just have emp_zip in employee table
Boyce & Codd normal form (BCNF)
Advanced version of 3NF that's why it is also referred as 3.5NF. BCNF is stricter than 3NF. A table complies with BCNF if it is in 3NF and for every functional dependency X->Y, X should be the super key of the table.
Transitive functional dependency
each functional dependency X-> Y at least one of the following conditions hold:
X is a super key of table
Y is a prime attribute of table
-can only occur in a relation of three of more attributes.
Data Model
A logical structure of Database that describes the design of database to reflect entities, attributes, relationship among data, constrains etc.
Types of Data Models
Object Based
Record Based
Object Based logical models
Describe Data at the conceptual and view levels.
E-R Model
Object Oriented Model
Physical Data Models
describe data at the lowest level of abstraction.
E-R Data Model
(Entity-Relationship model)
a systematic way of describing and defining a business process. An ER model is typically implemented as a database. The main components of E-R model are: entity set and relationship set.
Record based logical Models
describe data at the conceptual and view levels. These models specify logical structure of database with records, fields and attributes.
Relational Model
Hierarchical Model
Network Model
Relational Model
The data and relationships are represented by collection of inter-related tables. Each table is a group of column and rows, where column represents attribute of an entity and rows represents records. [Show Less]