Database Management Systems

Organising Data in a File system

• Uses
– Bit,byte,
– Field
– record (row,tuples)
– file
– Entity (person, place, event etc)and
– Attribute( characteristics of the entity)
THE DATA HIERARCHY
 Problems of file system
• Data redundancy and inconsistency
• Program data dependence
• Lack of flexibility
• Poor security
• Lack of data sharing and
• Unavailability of data at right time
 DBMS
• It facilitates centralisation of data, manage it
efficiently and provide access to stored data
for required applications.
HUMAN RESOURCES DATABASE WITH MULTIPLE VIEWS
 Capabilities of Database Management Systems (DBMSs)

• Relational DBMS
– Represent data as two-dimensional tables
– Each table contains data on entity and attributes
• Table: grid of columns and rows
– Rows (tuples): Records for different entities
– Fields (columns): Represents attribute for entity
– Key field: Field used to uniquely identify each record
– Primary key: Field in table used for key fields
– Foreign key: Primary key used in second table as look-up field to
identify records from original table
 DBMS and RDBMS
• Data is organised
• Normalisation ( avoid redundancy and data inconsistencies)
– Different forms(1NF,2NF,3NF etc.)
• Primary key(unique field in that table)
– Emp_Id,Customer_Id etc.
• Foreign Keys(look up field in other table where this is
primary key)

• Relationships (one to many etc.)

• Entity relationship diagrams
• Referential integrity
 suppliers
Supplier no suppliernam Supplier city state Zip_code
e street

8259 Sujal ind Patel nagar rajkot GJ 360002

8261 Gujjar Comp. Street 5 ahmedabad GJ 382445
8263 Savera ind. Ind. layout mysore KA 560082
8444 BC Raj & Co annasalai chennai TN 600002
 Parts
Part _no Part name Unit price Supplier
number
137 Door latch 22.00 8259
145 Side mirror 15.00 8444
150 compressor 154.00 8261
152 Door Lock 50.00 8259
Relational Database Tables
 Capabilities of Database Management Systems (DBMSs)

• Operations of a Relational DBMS

– Three basic operations used to develop useful
sets of data
• SELECT: Creates subset of data of all records that
meet stated criteria
• JOIN: Combines relational tables to provide user
with more information than available in individual
tables
• PROJECT: Creates subset of columns in table,
creating tables with only the information specified
THE THREE BASIC OPERATIONS OF A RELATIONAL DBMS
 Capabilities of Database Management Systems (DBMSs)

• Capabilities of database management systems

– Data definition capability: Specifies structure of database
content, used to create tables and define characteristics of
fields
– Data dictionary: Automated or manual file storing definitions
of data elements and their characteristics
– Data manipulation language: Used to add, change, delete,
retrieve data from database
• Structured Query Language (SQL)
• Microsoft Access user tools for generating SQL
– Many DBMS have report generation capabilities for creating
polished reports
 Database to improve Business
performance and decision making
• Data warehouses:
– It is a database that stores current and historical
data of potential interest to decision makers
throughout the company
• Customer data
• Suppliers data
• Offer letters of employees
• Discipline/legal documents etc.
Note: data can not be altered.
 Data Warehouse
• A collection of non-volatile data of different
business subjects and objects, which is time
variant and integrated down various sources
and applications and stored in a manner to
make a quick analysis of business situation
 Data Warehouse
Legacy database

Conversion Process
Operational DB

Middleware
Data Warehouse

Select, filter, validate, transform,

External DB
compute, consolidate and move to DW
Components of a Data Warehouse
 Data Marts
• Datamart: It is subset of datawarehouse.
Organisations create smaller de-centralised
warehouses called datamarts
– Ex: Finance regarding company P&L, wage bills
etc.
– HR: Medical insurance claims, legal cases, attrition
history etc.
– Sales: customer details, competitors’ details etc.
 Data Marts
• Smaller and de-centralised warehouses with in
the company are called data marts
• It is a subset of data warehouse
• It is highly focused towards a particular line of
business or department
– Top five customers data
– Customers with more than 20 % of business
 Data Mining
• It is discovery driven.
• To bring out hidden trends/patterns from the
data
– Associations
– Sequential patterns
– Clustering or grouping etc.
Data Warehouse, Data mart, Data mine
Business Data Warehouse Large volumes of business data ready
to use, view and analyse(enterprise
focus)

Data Mart Departmental or functional data

including external data for limited use
by department or function(
department focus)
Data Mine Data set of probable associations or
relations extracted and organised to
evaluate the influencing factors for the
patterns and find solutions to improve
business ( business focus)
 Tools for Business Intelligence
• Multi dimensional data analysis and data
mining
– Compare the performance among sections
– Compare performance of 2020 batch with present
batch in certain subjects
– Schedule compliance wrt planned ( conduct of
sessions) 2015 to 2019
• Online analytical processing enables this
 Text mining and web mining
• Derives from unstructured data like text in the
form of mails, proposals, legal documents,
surveys etc.
• Both from internal sources(internal
communication and processes) and external
sources( web based)
 Managing data resources
• Establishing information policy
• Proper data administration
• Data governance
– Policies and processes for managing the
availability, usability, integrity and security of the
enterprise data.
• Ensure data quality through data quality audit
and data cleansing.
 Normal forms
• At every intersection of the row and the
column there can be only one value(first)
• When every non-key attribute is fully and
functionally dependent on primary key for the
unique identification.(second normal form)
• There is no transitive dependency between
two attributes(third normal form) .
• Split the data into tables with different but
related primary keys.
Normalization of Database
Database Normalisation is a technique of organizing the data
in the database.
Normalization is a systematic approach of decomposing
tables to eliminate data redundancy and undesirable
characteristics like Insertion, Update and Deletion
Anomalies.
It is a multi-step process that puts data into tabular form by
removing duplicated data from the relation tables.
Normalization is used for mainly two purposes,
----Eliminating redundant(useless) data.
----Ensuring data dependencies make sense i.e data is
logically stored.
 S_id S_Name S_Address Subject_opted
401 Adam Noida Bio
402 Alex Panipat Maths
403 Stuart Jammu Maths
404 Adam Noida Physics

Updation Anomaly : To update address of a student who occurs twice or more than twice
in a table, we will have to update S_Address column in all the rows, else data will become
inconsistent.
Insertion Anomaly : Suppose for a new admission, we have a Student id(S_id), name and
address of a student but if student has not opted for any subjects yet then we have to
insert NULL there, leading to Insertion Anamoly.
Deletion Anomaly : If (S_id) 402 has only one subject and temporarily he drops it, when we
delete that row, entire student record will be deleted along with it.
As per First Normal Form, no two Rows of data must contain repeating group of
information i.e each set of column must have a unique value, such that multiple columns
cannot be used to fetch the same row. Each table should be organized into rows, and
each row should have a primary key that distinguishes it as unique.

The Primary key is usually a single column, but sometimes more than one column can
be combined to create a single primary key. For example consider a table which is not in
First normal form

Student Table :
In First Normal Form, any row must not have a column in which more than one value is
saved, like separated with commas. Rather than that, we must separate such data into
multiple rows.

Student Age Subject

Adam 15 Biology, Maths
Alex 14 Maths
Stuart 17 Maths
 Student Age Subject
Adam 15 Biology
Adam 15 Maths
Alex 14 Maths
Stuart 17 Maths

Student Table following 1NF will be :

Using the First Normal Form, data redundancy increases, as there will be
many columns with same data in multiple rows but each row as a whole will be
unique.
Second Normal Form (2NF)
As per the Second Normal Form there must not be any partial
dependency of any column on primary key. It means that for a
table that has concatenated primary key, each column in the
table that is not part of the primary key must depend upon the
entire concatenated key for its existence. If any column depends
only on one part of the concatenated key, then the table fails
Second normal form.
In example of First Normal Form there are two rows for Adam,
to include multiple subjects that he has opted for. While this is
searchable, and follows First normal form, it is an inefficient use
of space. Also in the above Table in First Normal Form, while the
candidate key is {Student, Subject}, Age of Student only
depends on Student column, which is incorrect as per Second
Normal Form. To achieve second normal form, it would be
helpful to split out the subjects into an independent table, and
match them up using the student names as foreign keys.
Third Normal Form (3NF)
applies that every non-prime attribute of table must be
Third Normal form

dependent on primary key, or we can say that, there should not be

the case that a non-prime attribute is determined by another non-
prime attribute. So this transitive functional dependency should be
removed from the table and also the table must be in Second
Normal form. For example, consider a table with following fields.
Student_Details Table :
In this table Student_id is Primary key, but street, city and state
depends upon Zip. The dependency between zip and other fields
is called transitive dependency. Hence to apply 3NF, we need to
move the street, city and state to new table, with Zip as primary
key.

Student_id Student_name DOB Street city State Zip

 MS Access
• Tables
• Queries
• Forms and
• Reports
MS Access

N Siva Prasad
 Topics
• Creating a database file(open,name,save,re-
open)
• Creating ( within that database)
– a table
– A form
– A query
– A report
Note: each can be given different names and all these
will appear in the left side column in the order as
decided( objectwise or table related or date created
etc.)
 Views
• Each of these applications have different views
– Tables
• Design view
• Database view
• Pivot table view
• Pivot Chart view
– Forms
• Form View
• Layout view
• Design View
 Views Cntd…
• Each of these applications have different views
– Queries
• Design view
• Database view
• Pivot table view
• Pivot Chart view
• SQL view
– Reports
• Layout view
• Design View
• Report view
• Print view
 Design view
• Allows designing the application(table, form,
query or report)
• Need to save after modifications( will be
prompted)
• Data entries are saved automatically.
• Other views are used as applicable for each of
the above
 Table
• Select “Create a table” and go to design view
• Primary key is indicated by a small key icon appearing to
the left side to the field name
• Primary key will be a field named ID by default
• The name can be changed
• Entries in other fields are allowed only after entering
value in Primary Key field
• Primary key field can be changed
• Each field to be named logically
• Some names are reserved and can not be used(Ex:
name). Access will prompt accordingly
• Each field has option of different data types
• Each data type has different options
 Data Types
• Decide on primary key and accordingly set data
type for primary key( by default “ID”)
• Each field has different options( through
dropdown menu)
• One of them can be selected( Ex: text or number
or auto number etc.)
• Each option has default settings( field properties)
shown as a list under “General”. These settings
can be changed as per requirement
 Creating the tables
• Decide on entities and corresponding
attributes with their field properties (
including relationship) and create tables
accordingly for each entity( give suitable
names)
• To build relationship between two tables the
primary key of one table has to be a field in
second table( called “foreign key”)
• The data type of these keys have to be same
 Entering Data
• Use datasheet view to enter the data in the fields
created.( each row is called a record)
• Primary key indication will not be there in data sheet
view
• Fill at least five records through datasheet view
• A new field can be added in datasheet view also
• Try adding a field through data sheet view( default text
type)
• “Caption” is used to give meaningful name in Forms for
entering record values( default is field name)