Module 1 DBMS

✍️Responsibilities of Database Designer and Database

Administrator

🧱 Step 1: Understanding the Role of the Database Designer

Step 1.1: Identifying the data to be stored
The database designer is responsible for deciding what data should
be stored in the database. For example, in a university database,
details like students, courses, marks, and fees must be carefully
identified.
Step 1.2: Choosing appropriate data structures
Once the data is identified, the designer selects how to represent
and store it — using tables, attributes, and relationships. The
structure must be efficient and organized for smooth data access.
Step 1.3: Communicating with users to gather requirements
Designers must interact with all prospective users, such as faculty,
admin staff, or developers, to understand what kind of data they
need and how they will use it. This helps in designing a system that
serves all needs.
Step 1.4: Designing based on requirements
Based on user feedback, the designer creates a database schema that
fulfills the requirements. The design should be logical, efficient, and
easy to work with for different applications.
Step 1.5: Ensuring smooth data access and use
A well-designed database makes data access faster and more
reliable, prevents redundancy, and improves overall performance. It
also helps application programs run smoothly.
 Step 2: Understanding the Role of the Database Administrator
(DBA)
Step 2.1: Authorizing access to the database
The DBA is in charge of deciding who can access the database and
what level of access they have. For example, a user may be allowed
to view student data but not edit it.
Step 2.2: Coordinating and monitoring database use
The DBA must keep track of how the database is used, by
monitoring activities and ensuring that everything runs properly. If
many users are accessing at the same time, the DBA ensures it stays
stable.
Step 2.3: Acquiring software and hardware resources
Whenever the database system needs more resources, like memory,
servers, or updated software, it is the DBA's responsibility to arrange
and install them for better performance.
Step 2.4: Ensuring security of the database system
Security is a major task. The DBA must protect the system from
unauthorized users, data theft, or corruption. Strong passwords,
roles, and backup plans are handled by the DBA.
Step 2.5: Keeping the system functioning efficiently
The DBA must make sure the database is always working properly,
even when many users are online. Tasks like performance tuning,
handling crashes, and recovering lost data fall under the DBA’s role.
Step 2.6: Preventing access by unauthorized users
The DBA sets up permission controls so that only approved users can
access certain parts of the system. This keeps sensitive data safe and
secure.

🔁 Conclusion
The database designer focuses on building the database structure
based on user needs, while the DBA manages, secures, and
maintains the system once it is running. Both are essential for a
successful database system.

🔄 Relationship Between Designer and DBA

 The designer works mostly during the initial phase of the
database — creating the structure and plan.
 The DBA works continuously after the database is deployed —
managing and maintaining it in the long run.
Both roles are very important, and they work together to ensure the
database meets user needs and works efficiently.

2)
Aspect Logical Data Independence Physical Data Independence

Ability to change the conceptual Ability to change the internal schema

Definition schema without changing the without changing the conceptual
external schema or programs schema

Application programs and user Conceptual schema and programs

Impact on
views remain unchanged when remain unchanged when internal
Programs
conceptual schema is changed schema is modified

Changes like adding new fields, Changes like reorganizing file structures,
Changes
removing attributes, or modifying adding indexes, or changing storage
Involved
constraints format

Change is done at the conceptual Change is done at the internal (physical)

Level Affected
level level

Harder to achieve than physical Easier to achieve compared to logical

Complexity
data independence data independence

Moving data to a new disk or adding a B+

Adding a new column to a table
Example tree index without affecting the
without affecting user applications
conceptual schema
1️⃣ Entity
 An entity represents a real-world object or concept, such as an
employee, student, car, or project from the miniworld (real-
world environment).
 Example:
An EMPLOYEE can be an entity in a company database.

2️⃣ Attribute
 An attribute represents a property or characteristic of an
entity.
 It helps to describe the entity in more detail.
 Example:
The EMPLOYEE entity may have attributes like:
o Name
o Salary
o Age
o Designation

3️⃣ Relationship
 A relationship is an association among two or more entities.
 It shows how entities are connected or related to each other.
 Example:
A works-on relationship may exist between:
 o An EMPLOYEE entity and a PROJECT entity
(This shows which employee is working on which project.)

Degree of a Relationship Type

 The degree of a relationship type refers to the number of
participating entity types involved in that relationship.

🔹 Binary Relationship (Degree 2)

 When two entity types are involved in a relationship, the
degree is two, and it's called a binary relationship.
 ✅ Example:
WORKS_FOR is a binary relationship between:
o An EMPLOYEE entity
o A DEPARTMENT entity
This shows which employee works in which department.

🔹 Ternary Relationship (Degree 3)

 If three entity types participate, the relationship is of degree
three, and it's called a ternary relationship.
 ✅ Example:
SUPPLY is a ternary relationship that connects:
o A Supplier (s)
 o A Part (p)
o A Project (j)
It shows: Supplier s supplies Part p to Project j.

1️⃣ Entity and Attribute

 An entity represents a real-world object or concept, like an
employee, car, or project.
 Entities can be:
o Physically existing (e.g., person, car, house)
o Conceptually existing (e.g., course, job, company)
 Each entity has attributes, which are properties that describe it.
o Example: EMPLOYEE entity can have attributes like Name,
Age, Salary, Address, etc.
8. Value Sets / Domains
 A value set is the allowed range of values for an attribute.
o Example: Age attribute may have values from 16 to 70.
 Value sets are like data types (integer, string, etc.).
 Not usually shown in ER diagrams.
 A NULL means no value is given; it’s represented by an empty
set.

🔹 9. Metadata
 Metadata is data about data.
 It includes descriptions of schema, constraints, data types, and
structures.
 Stored in the DBMS catalog.
 Helps the DBMS understand how to manage data internally.

1. Schema
 A schema describes the overall structure or design of the
database.
 It is defined during the database design phase and usually does
not change frequently.
 It includes all entity types, relationships, constraints, and data
definitions.
 📌 The schema is also called the intension (fixed structure of the
database).