Data Dictionary And The System Catalog

Data Dictionary




 In a database management system (DBMS), a data dictionary can be defined

as a component that stores a collection of names, definitions, and attributes
for data elements used in the database.

 The database stores metadata, that is, information about the database. These
data elements are then used as part of a database, research project, or
information system.

 It stores all information about relationships or tables, from the schema and
constraints used.

 All metadata is preserved. In general, metadata refers to information about

data. Thus, storing the connection scheme and other metadata in a single
structure called a data dictionary or system directory.

 A data dictionary in a DBMS helps users manage data in an orderly and

orderly manner, thereby preventing data redundancy.

 Below is a data dictionary that describes the table that contains employee
details.
Field Size for
Field Name Data Type Description Example
Display

Unique ID of each
EmployeeID Integer 8 100025
employee

Full name of the Emily

FullName Text 30
employee Johnson

Date of birth of
DOB Date/Time 10 1990-05-15
employee

PhoneNumber Integer 10 Phone number of 555-123-

 Field Size for
Field Name Data Type Description Example
Display

employee 4567

Some of the advantages of using a data dictionary are:

 In DBMS, the data model provides very little information about the database, so
the data dictionary is very important to get the right knowledge about the
entities, relationships, and attributes that exist in the data model.
 The data dictionary provides consistency by reducing data redundancy in data
collection and use among different team members.
 Data dictionaries provide structured analysis and design tools by implementing
data standards. Data standards are sets of rules that govern the collection,
recording, and presentation of data.
 Using a data dictionary helps define the naming convention used in the model.
Types of Data Dictionary in DBMS

Types of data dictionary

There are basically two types of data dictionaries in a database management

system:
 Integrated Data Dictionary
 Stand Alone Data Dictionary
Integrated Data Dictionary
Every relational database has an Integrated Data Dictionary available in the
DBMS.

This integrated data dictionary acts as a system directory that is accessed and
updated by the relational database.

An Integrated Data Dictionary in a DBMS can link metadata.

Stand Alone Data Dictionary
This type of database in the DBMS is very adaptive because it grants the
administrator in charge of the confidential information complete autonomy to define
and manage all crucial data.

Disadvantages of Data Dictionary

 Data dictionaries have little functional detail.
 Non-technical users may experience difficulties when using the data dictionary
for the first time.
 Data dictionary relational diagrams are often not visible.

System catalog
System catalogs, also known as system tables or system views, play a crucial role
in the internal organization and management of the database and serve as the
backbone of YugabyteDB's architecture.

YugabyteDB builds upon the system catalog of PostgreSQL. These catalogs form
a centralized repository that stores metadata about the database itself, such as
tables, indexes, columns, constraints, functions, users, privileges, extensions, query
statistics, and more.

All the system catalog tables and views are organized under
the pg_catalog schema.

To list the tables in the system catalog, you can execute the following command:

SELECT tablename FROM pg_catalog.pg_tables WHERE

schemaname='pg_catalog';

To list all the views in the system catalog, you can execute the following
command:
 SELECT viewname FROM pg_catalog.pg_views WHERE

schemaname='pg_catalog';

To get the details of the names and type information of columns in a table, you can
run the following command:

\d+ <table-name>

Codd's Rules in DBMS





Codd's rules are proposed by a computer scientist named Dr. Edgar F. Codd and
he also invent the relational model for database management.

These rules are made to ensure data integrity, consistency, and usability.

This set of rules basically signifies the characteristics and requirements of a

relational database management system (RDBMS).

Codd's Rules in DBMS

Rule 1: The Information Rule
All information, whether it is user information or metadata, that is stored in a
database must be entered as a value in a cell of a table. It is said that everything
within the database is organized in a table layout.

Rule 2: The Guaranteed Access Rule

Each data element is guaranteed to be accessible logically with a combination of
the table name, primary key (row value), and attribute name (column value).

Rule 3: Systematic Treatment of NULL Values

Every Null value in a database must be given a systematic and uniform treatment.
Rule 4: Active Online Catalog Rule
The database catalog, which contains metadata about the database, must be
stored and accessed using the same relational database management system.

Rule 5: The Comprehensive Data Sublanguage Rule

A crucial component of any efficient database system is its ability to offer an easily
understandable data manipulation language (DML) that facilitates defining,
querying, and modifying information within the database.

Rule 6: The View Updating Rule

All views that are theoretically updatable must also be updatable by the system.

Rule 7: High-level Insert, Update, and Delete

A successful database system must possess the feature of facilitating high-level
insertions, updates, and deletions that can grant users the ability to conduct these
operations with ease through a single query.

Rule 8: Physical Data Independence

Application programs and activities should remain unaffected when changes are
made to the physical storage structures or methods.

Rule 9: Logical Data Independence

Application programs and activities should remain unaffected when changes are
made to the logical structure of the data, such as adding or modifying tables.

Rule 10: Integrity Independence

Integrity constraints should be specified separately from application programs and
stored in the catalog. They should be automatically enforced by the database
system.

Rule 11: Distribution Independence

The distribution of data across multiple locations should be invisible to users, and
the database system should handle the distribution transparently.
Rule 12: Non-Subversion Rule
If the interface of the system is providing access to low-level records, then the
interface must not be able to damage the system and bypass security and integrity
constraints.

Entity-Relationship Model
The ER model defines the conceptual view of a database. It works around real-world entities
and the associations among them. At view level, the ER model is considered a good option
for designing databases.
 Entity

An entity can be a real-world object, either animate or inanimate, that can be easily
identifiable. For example, in a school database, students, teachers, classes, and courses
offered can be considered as entities. All these entities have some attributes or properties
that give them their identity.

An entity set is a collection of similar types of entities. An entity set may contain entities
with attribute sharing similar values. For example, a Students set may contain all the
students of a school; likewise a Teachers set may contain all the teachers of a school from
all faculties. Entity sets need not be disjoint.

Attributes
Entities are represented by means of their properties, called attributes. All attributes have
values. For example, a student entity may have name, class, and age as attributes.

There exists a domain or range of values that can be assigned to attributes. For example, a
student's name cannot be a numeric value. It has to be alphabetic. A student's age cannot
be negative, etc.

Types of Attributes
 Simple attribute − Simple attributes are atomic values, which cannot be divided further.
For example, a student's phone number is an atomic value of 10 digits.
 Composite attribute − Composite attributes are made of more than one simple attribute.
For example, a student's complete name may have first_name and last_name.
 Derived attribute − Derived attributes are the attributes that do not exist in the physical
database, but their values are derived from other attributes present in the database. For
example, average_salary in a department should not be saved directly in the database,
instead it can be derived. For another example, age can be derived from data_of_birth.
 Single-value attribute − Single-value attributes contain single value. For example −
Social_Security_Number.
 Multi-value attribute − Multi-value attributes may contain more than one values. For
example, a person can have more than one phone number, email_address, etc.

These attribute types can come together in a way like −

 simple single-valued attributes

 simple multi-valued attributes
 composite single-valued attributes
 composite multi-valued attributes

Entity-Set and Keys

Key is an attribute or collection of attributes that uniquely identifies an entity among entity
set.

For example, the roll_number of a student makes him/her identifiable among students.

 Super Key − A set of attributes (one or more) that collectively identifies an entity in an
entity set.
 Candidate Key − A minimal super key is called a candidate key. An entity set may have
more than one candidate key.
 Primary Key − A primary key is one of the candidate keys chosen by the database
designer to uniquely identify the entity set.

Relationship
The association among entities is called a relationship. For example, an
employee works_at a department, a student enrolls in a course. Here, Works_at and
Enrolls are called relationships.

Relationship Set
A set of relationships of similar type is called a relationship set. Like entities, a relationship
too can have attributes. These attributes are called descriptive attributes.

Degree of Relationship

The number of participating entities in a relationship defines the degree of the relationship.

 Binary = degree 2
 Ternary = degree 3
 n-ary = degree

Mapping Cardinalities
Cardinality defines the number of entities in one entity set, which can be associated
with the number of entities of other set via relationship set.
 One-to-one − One entity from entity set A can be associated with at most one entity
of entity set B and vice versa.

 One-to-many − One entity from entity set A can be associated with more than one
entities of entity set B however an entity from entity set B, can be associated with at most
one entity.
 Many-to-one − More than one entities from entity set A can be associated with at
most one entity of entity set B, however an entity from entity set B can be associated with
more than one entity from entity set A.

 Many-to-many − One entity from A can be associated with more than one entity
from B and vice versa.
ER DIAGRAM

ER Model is represented by means of an ER diagram. Any object, for example, entities,

attributes of an entity, relationship sets, and attributes of relationship sets, can be
represented with the help of an ER diagram.

Entity

Entities are represented by means of rectangles. Rectangles are named with the entity set
they represent.

Attributes

Attributes are the properties of entities. Attributes are represented by means of ellipses.
Every ellipse represents one attribute and is directly connected to its entity (rectangle).

If the attributes are composite, they are further divided in a tree like structure. Every node
is then connected to its attribute. That is, composite attributes are represented by ellipses
that are connected with an ellipse.
Multivalued attributes are depicted by double ellipse.

Derived attributes are depicted by dashed ellipse.

 Relationship

Relationships are represented by diamond-shaped box. Name of the relationship is written

inside the diamond-box. All the entities (rectangles) participating in a relationship, are
connected to it by a line.

Binary Relationship and Cardinality

A relationship where two entities are participating is called a binary relationship.
Cardinality is the number of instance of an entity from a relation that can be associated with
the relation.
 One-to-one − When only one instance of an entity is associated with the relationship, it is
marked as '1:1'. The following image reflects that only one instance of each entity should be
associated with the relationship. It depicts one-to-one relationship.

 One-to-many − When more than one instance of an entity is associated with a relationship,
it is marked as '1:N'. The following image reflects that only one instance of entity on the left
and more than one instance of an entity on the right can be associated with the relationship.
It depicts one-to-many relationship.
 Many-to-one − When more than one instance of entity is associated with the relationship,
it is marked as 'N:1'. The following image reflects that more than one instance of an entity
on the left and only one instance of an entity on the right can be associated with the
relationship. It depicts many-to-one relationship.

 Many-to-many − The following image reflects that more than one instance of an entity on
the left and more than one instance of an entity on the right can be associated with the
relationship. It depicts many-to-many relationship.

Participation Constraints
 Total Participation − Each entity is involved in the relationship. Total participation is
represented by double lines.
 Partial participation − Not all entities are involved in the relationship. Partial participation
is represented by single lines.