CC105 - Information Management WEEK 2 TO 8

File Systems

It is used by a manager of any small organization to track necessary data

Disadvantages of a File System

• Program-data dependence

• Duplication of Data

• Limited Data Sharing

• Lengthy Development Times

• Excessive Program Maintenance

Five Major Parts of a Database System

• Hardware

• Software

• People

• Procedures

• Data

Parts of Software in a database system

• Operating Systems Software

• DBMS Software

• Application programs and utility software

Parts of People in a database system

• Systems Administrators

• Database Administrators

• Database Designers Systems Analysts and Programmers

• End users

Database

A shared collection of related data used to support the activities of a particular organization. It can be viewed as a repository of data that is defined once and then accessed by various users

Database properties

• It is a representation of some aspect of the real world or a collection of data elements (facts) representing real-world information

• A database is logical, coherent and internally consistent

• A database is designed, built and populated with data for a specific purpose

• Each data item is stored in a field; A combination of fields makes up a table

Field

Each data item is stored in a field

Table

A combination of fields makes up a table

Database Management System (DBMS)

Stores data in such a way that it becomes easier to retrieve, manipulate, and produce information

DBMS characteristics

• Real-world Entity

• Relation-based Tables

• Isolation Of Data And Application

• Less Redundancy; Consistency

• Query Language

• ACID Properties

• Multiuser And Concurrent Access

• Multiple Views

• Security

Information Management

• The infrastructure used to collect, manage, preserve, store and deliver information

• The guiding principles that allow information to be available to the right people at the right time

• The view that all information, both digital and physical, is an asset that requires proper management

• The organizational and social contexts in which information exists

• Is an umbrella term that encompasses all the systems and processes within an organization for the creation and use of corporate information

Purpose of Information Management

• To Design, develop, manage, and use information with insight and innovation

• To Support decision making and create value for individuals, organizations, communities, and societies

DBMS Architecture

Database Management Systems Architecture will help us understand the components of database system and the relation among them

Types of DBMS Architecture

• Single tier architecture

• Two tier architecture

• Three tier architecture

Data Abstraction

Three levels of abstraction:

• Physical level

• Logical level

• View level

DBMS Three-Level Architecture

This architecture has three levels:

• External level

• Conceptual level

• Internal level

Schema

Design of a database is called the schema. A schema contains schema objects like table, foreign key, primary key, views, columns, data types, stored procedure, etc

Types of Schema

• Physical schema

• logical schema

• view schema

Instance

Data stored in database at a particular moment of time. It contains a snapshot of the database

DBMS instance validity

A DBMS ensures that its every instance (state) is in a valid state, by diligently following all the validations, constraints, and conditions that the database designers have imposed

Data Models

A 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

Data Independence

The capacity to change the schema at one level of a database system without having change the schema at the next higher level

Types of Data Independence

• Logical Data Independence

• Physical Data Independence

DBMS Languages

A DBMS has appropriate languages and interfaces to express database queries and updates. Database languages can be used to read, store and update the data in the database

A DBMS language

• Data Definition Language (DDL)

• Data Manipulation Language (DML)

• Data Control Language (DCL)

Create

It is used to create objects in the database

Alter

It is used to alter the structure of the database

Drop

It is used to delete objects from the database

Truncate

It is used to remove all records from a table

Rename

It is used to rename an object

Comment

It is used to comment on the data dictionary

Select

It is used to retrieve data from a database

Insert

It is used to insert data into a table

Update

It is used to update existing data within a table

Delete

It is used to delete all records from a table

Grant

It is used to give user access privileges to a database

Revoke

It is used to take back permissions from the user

Entity Relational (ER) Model

A high-level conceptual data model diagram. Helps you to analyze data requirements systematically. Represented by Entity-Relationship (ER) Diagram

Entity Relationship (ER) Diagram

Visual Tool to represents ER Model. Displays the relationship of entity sets. Explain logical structure of databases

Components of ER Diagram

• Entities

• Attributes

• Relationship

Rectangles

Represent an entity

Ellipses

Represent an attribute

Diamond

Represent a relationship

Lines

Links between attributes to entity/ Entity to relationship

Underline

Primary key attribute

Entity

• Person

• Place

• Object

• Event

• Concept

Strong Entity

A type of entity that contains sufficient attributes to uniquely identify all its entities. Primary key exists for a strong entity set

Weak Entity

A type of entity which doesn't have its key attribute. Can be identified uniquely by considering the primary key of another entity

Attributes

Characteristic of an entity. All attributes have values

Types of Attributes

• Simple attribute

• Composite attribute

• Derived attribute

• Single-value attribute

• Multi-value attribute

Simple Attribute

Atomic values, which cannot be divided further. For example, a student's mobile number is an atomic value of 11 digits

Composite Attribute

Made of more than one simple attribute. For example, a student's complete name may have first_name and last_name

Derived Attribute

Attributes that do not exist in the physical database, but their values are derived from other attributes present in the database. For another example, age can be derived from date_of_birth

Single valued Attribute

Contain single value. For example, a Social_Security_Number

Multi valued Attribute

May contain more than one values. For example, a person can have more than one phone number, email_address, etc

Relationship

It describes the association among entities

Types of Relationship

• One-to-one

• One-to-many

• Many-to-one

• Many-to-many

One-to-one

A type of relationship. Example: One student can register for numerous courses. However, all those courses have a single line back to that one student

One-to-many

A type of relationship. Example: one class is consisting of multiple students

Many-to-one

A type of relationship. Example: many students belong to the same class

Many-to-many

A type of relationship. Example: Students as a group are associated with multiple faculty members, and faculty members can be associated with multiple students

Cardinality Constraints

It specifies the maximum number of entity instance which associates with instances of another entity

• Mandatory one

• Mandatory many

• Optional one

• Optional many

Mandatory

One or many

Optional

Zero, one or many

Type of Relationship Notations

Crow’s Foot Notation

• One to one

• One to many

• Many to one

• Many to many

Steps to Create ER Diagram

Step 1: Entity Identification → Identify the entities.

Step 2: Relationship Identification → Identify the relationships between entities.

Step 3: Cardinality Identification → Identify the cardinality of the relationships.

Step 4: Identify Attributes → Identify the attributes of the entities.

Step 5: Create the ERD → Create the Entity Relationship Diagram

Dr. E.F. Codd

A British scientist who worked for IBM. Invented the relational model for database management

Relational Model

Represents the database as a collection of relations, with relations pertaining to tables with rows and columns

Attributes

The properties which define a relation

Tables

Has two properties rows and columns. Rows represent records and columns represent attributes

Tuple

Single row of a table, which contains a single record

Degree

The total number of attributes which in the relation

Cardinality

Total number of rows present in the Table

Column

Represents the set of values for a specific attribute

Relation Instance

A finite set of tuples in the RDBMS system

Relation Key

Every row has one, two or multiple attributes

Attribute Domain

Every attribute has some pre-defined value and scope

Relational Model Constraints

Referred to conditions which must be present for a valid relation

Three Main Categories of Relational Model Constraints

• Key Constraints

• Domain Constraints

• Referential Integrity Constraints

Key Constraints

All the values of primary key must be unique. The value of primary key must not be null

Domain Constraints

Domain constraint defines the domain or set of values for an attribute. It specifies that the value taken by the attribute must be the atomic value from its domain

Referential Integrity Constraints

This constraint is enforced when a foreign key references the primary key of a relation. It specifies that all the values taken by the foreign key must either be available in the relation of the primary key or be null

Key

A set of attributes that can identify each tuple uniquely in the given relation

Super Key

A set of attributes that can identify each tuple uniquely in the given relation. May consist of any number of attributes

Candidate Key

A super key with no repeated attribute. A minimal super key. Set of minimal attribute(s) that can identify each tuple uniquely in the given relation

Primary Key

A candidate key that the database designer selects while designing the database. Column or group of columns in a table which helps us to uniquely identifies every row in a relation

Foreign Key

A column which is added to create a relationship with another table. It help us to maintain data integrity and also allows navigation between two different instances of an entity

Operations in Relational Model

• Insert

• Delete

• Modify

• Select

• Insert Operation

• Update Operation

• Delete Operation

• Select Operation

Insert

Used to insert data into the relation

Delete

Used to delete tuples from the table

Modify

Allows you to change the values of some attributes in existing tuples

Select

Allows you to choose a specific range of data

Converting ER Diagrams to Tables

Rule-01 → For Strong Entity Set With Only Simple Attributes.

Rule-02 → For Strong Entity Set With Composite Attributes.

Rule-03 → For Strong Entity Set With Multi Valued Attributes.

Rule-04 → Translating Relationship Set into a Table

Normalization

A technique used to perform logical database design, and for producing set of relations that possess a certain set of properties. Process of organizing data in a database, which includes creating tables and establishing relationship between tables to eliminate redundancy and inconsistent dependency

Normal Forms

An algorithm you use to test the structure of a table

Goals of Normalization

Eliminate redundant data. Eliminate insert, delete, and update anomalies

Insertion Anomaly

Refers to a situation wherein a new tuple (row) cannot be inserted in a relation because of an artificial dependency on another relation

Updation Anomaly

Refers to a situation in which an update of a single data value requires multiple tuple (rows) of data to be updated

Deletion Anomaly

Refers to the situation wherein deletion of data about one particular entity causes unintentional loss of data that represents another entity