Skip to content
CampusPrep
CS-504 (C) · Introduction to Database Management Systems/Quick Revision Short Notes

Introduction to Database Management Systems (CS-504 (C)) - Unit 1 Short Notes

How unit 1 is examined

This unit covers what a DBMS is, why it replaces file systems, data independence and schema, data modelling basics, and DBMS versus RDBMS; the marks sit in data independence, the need for a database, its benefits, and DBMS versus RDBMS.

Introduction

<span style="display:inline-block;padding:.16em .6em;border:1.5px solid currentColor;border-radius:999px;font-size:.68em;font-weight:700;letter-spacing:.06em;text-transform:uppercase;opacity:.75">Not asked since 2022</span>

Definition. <mark>A database is an organised collection of related data, and a Database Management System (DBMS) is the software that defines, stores, retrieves and controls access to that data.</mark>

Key points.

  1. Data means raw facts such as a name or a mark, while information is data processed into a meaningful form.
  2. A DBMS sits between users or programs and the stored data, so applications never handle files directly.
  3. Examples of DBMS are MySQL, Oracle, PostgreSQL and MS Access.
  4. A DBMS provides definition, manipulation, security, sharing and recovery of data.

Significance of Database

<span style="display:inline-block;padding:.16em .6em;border:1.5px solid currentColor;border-radius:999px;font-size:.68em;font-weight:700;letter-spacing:.06em;text-transform:uppercase;opacity:.75">Low weight</span>

Definition. <mark>A database system stores data once in a central, controlled place so that many users and programs can share it accurately and securely, which a file system cannot do well.</mark>

Key points.

  1. A file system keeps separate files per application, so the same data is duplicated, becomes inconsistent and wastes space.
  2. File programs depend on the file format, so changing a file layout forces changes in every program.
  3. Files give no built-in security, concurrent access control, integrity checking or crash recovery, and a database supplies all of these.
  4. Elements of a database system are: data (the stored facts), hardware (disks and computers), software (the DBMS and applications), users (DBA, programmers, end users) and procedures (rules for design, backup and use).

Asked: [7 marks] (Dec 2020) Why do we need a database? Write its elements.

Database System Applications

<span style="display:inline-block;padding:.16em .6em;border:1.5px solid currentColor;border-radius:999px;font-size:.68em;font-weight:700;letter-spacing:.06em;text-transform:uppercase;opacity:.75">Not asked since 2022</span>

Definition. <mark>Database applications are systems that keep and query large amounts of shared data for daily operations.</mark>

Key points.

  1. Banking uses databases for accounts and transactions, and airlines for reservations and schedules.
  2. Universities store student records, results and fees, and retail stores track stock and sales.
  3. Telecom keeps call records and billing, and hospitals keep patient and treatment records.
  4. Websites and e-commerce keep users, products and orders in databases.

Data Independence

<span style="display:inline-block;padding:.16em .6em;border:1.5px solid currentColor;border-radius:999px;font-size:.68em;font-weight:700;letter-spacing:.06em;text-transform:uppercase;opacity:.75">Medium weight</span>

Definition. <mark>Data independence is the ability to change the schema at one level of the database without having to change the schema at the next higher level.</mark>

Levels of abstraction (three-level architecture).

<figure class="ds-fig" style="margin:1.4rem 0;overflow-x:auto"><svg xmlns="http://www.w3.org/2000/svg" id="dsfig-u1-01" viewBox="0 0 259 415.4" width="259" height="415.4" role="img" aria-label="Three-level architecture: V1, V2 are external views (view level), Conc is the conceptual schema (logical level), Int is the internal schema (physical level), Disk is the stored data"><style>#dsfig-u1-01 .e{stroke:#454C5A;stroke-width:1.4;fill:none}#dsfig-u1-01 .e.hi{stroke:#2340B8;stroke-width:2.6}#dsfig-u1-01 .n{fill:#FFFFFF;stroke:#16181D;stroke-width:1.4}#dsfig-u1-01 .n.hi{fill:#E3E9FC;stroke:#2340B8;stroke-width:2.2}#dsfig-u1-01 .n.rb-b{fill:#16181D;stroke:#16181D}#dsfig-u1-01 .n.rb-r{fill:#BD3227;stroke:#BD3227}#dsfig-u1-01 text{font-family:"JetBrains Mono",ui-monospace,Menlo,Consolas,monospace;font-size:13px}#dsfig-u1-01 .t{fill:#16181D;font-weight:500}#dsfig-u1-01 .t.inv{fill:#FFFFFF;font-weight:700}#dsfig-u1-01 .kd{stroke:#16181D;stroke-width:1.2}#dsfig-u1-01 .dot{fill:#16181D}#dsfig-u1-01 .ann{fill:#2340B8;font-size:11px;font-weight:700}#dsfig-u1-01 .lbl{fill:#6F7787;font-family:system-ui,-apple-system,sans-serif;font-size:12px;font-weight:700}#dsfig-u1-01 .ptr{fill:#2340B8;font-size:12px;font-weight:700}#dsfig-u1-01 .ah{fill:#454C5A}#dsfig-u1-01 .ah.hi{fill:#2340B8}#dsfig-u1-01 .wl rect{fill:#FFFFFF;stroke:#DCE0E7}#dsfig-u1-01 .wl .t{font-size:12px;font-weight:700}#dsfig-u1-01 .wl.hi rect{fill:#2340B8;stroke:#2340B8}#dsfig-u1-01 .wl.hi .t{fill:#FFFFFF}html.dark #dsfig-u1-01 .e{stroke:#B1B7C3}html.dark #dsfig-u1-01 .e.hi{stroke:#8FA3FF}html.dark #dsfig-u1-01 .n{fill:#161920;stroke:#E6E8ED}html.dark #dsfig-u1-01 .n.hi{fill:#1E2748;stroke:#8FA3FF}html.dark #dsfig-u1-01 .n.rb-b{fill:#E6E8ED;stroke:#E6E8ED}html.dark #dsfig-u1-01 .n.rb-r{fill:#FF7E71;stroke:#FF7E71}html.dark #dsfig-u1-01 .t{fill:#E6E8ED}html.dark #dsfig-u1-01 .t.inv{fill:#0F1115}html.dark #dsfig-u1-01 .kd{stroke:#E6E8ED}html.dark #dsfig-u1-01 .dot{fill:#E6E8ED}html.dark #dsfig-u1-01 .ann{fill:#8FA3FF}html.dark #dsfig-u1-01 .lbl{fill:#858D9C}html.dark #dsfig-u1-01 .ptr{fill:#8FA3FF}html.dark #dsfig-u1-01 .ah{fill:#B1B7C3}html.dark #dsfig-u1-01 .ah.hi{fill:#8FA3FF}html.dark #dsfig-u1-01 .wl rect{fill:#161920;stroke:#2A2E37}html.dark #dsfig-u1-01 .wl.hi rect{fill:#8FA3FF;stroke:#8FA3FF}html.dark #dsfig-u1-01 .wl.hi .t{fill:#0F1115}</style><defs><marker id="ah1" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto-start-reverse"><path class="ah" d="M0,1 L9,5 L0,9 z"/></marker><marker id="ahh1" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto-start-reverse"><path class="ah hi" d="M0,1 L9,5 L0,9 z"/></marker></defs><path class="e" d="M52.8,56.6 L108.9,129.6" marker-end="url(#ah1)" marker-start="url(#ah1)"/><path class="e" d="M199.2,56.6 L143.1,129.6" marker-end="url(#ah1)" marker-start="url(#ah1)"/><path class="e" d="M126,179.8 L126,242.6" marker-end="url(#ah1)" marker-start="url(#ah1)"/><path class="e" d="M126,284.6 L126,347.4" marker-end="url(#ah1)" marker-start="url(#ah1)"/><circle class="n" cx="40" cy="40" r="18"/><text class="t" x="40" y="40" dy=".35em" text-anchor="middle">V1</text><circle class="n" cx="212" cy="40" r="18"/><text class="t" x="212" y="40" dy=".35em" text-anchor="middle">V2</text><rect class="n" x="101" y="136.8" width="50" height="30" rx="15"/><text class="t" x="126" y="151.8" dy=".35em" text-anchor="middle">Conc</text><circle class="n" cx="126" cy="263.6" r="18"/><text class="t" x="126" y="263.6" dy=".35em" text-anchor="middle">Int</text><rect class="n" x="101" y="360.4" width="50" height="30" rx="15"/><text class="t" x="126" y="375.4" dy=".35em" text-anchor="middle">Disk</text></svg><figcaption style="font-size:.82em;opacity:.72;margin-top:.45rem">Three-level architecture: V1, V2 are external views (view level), Conc is the conceptual schema (logical level), Int is the internal schema (physical level), Disk is the stored data</figcaption></figure>

Key points.

  1. The physical level describes how data is actually stored, using file structures, indexes, and access methods.
  2. The logical level describes what data is stored, as tables, relations, attributes and constraints, and the relationships among them.
  3. The view level shows each user only the part of the database they need, which also gives security.
  4. Physical data independence means the internal schema can change (new index, different file organisation) without changing the conceptual schema or application programs.
  5. Logical data independence means the conceptual schema can change (add or remove an attribute or table) without changing external views or programs; it is harder to achieve.
  6. A schema is the overall design of the database and is fixed, while an instance is the data present at a particular moment, which keeps changing.
  7. A subschema is the part of the schema seen by one user or application.

Example (schema).

CREATE TABLE Student (RollNo INT PRIMARY KEY, Name VARCHAR(30), Branch VARCHAR(10));

Here Student(RollNo, Name, Branch) is the logical schema; the rows stored today are the instance.

Basis Physical level Logical level
Describes How data is stored What data is stored
Contents Files, indexes, access methods Tables, relations, constraints
Abstraction Lowest, most detail Middle, hides storage detail
Used by DBA and system programmers DBA and application developers
Complexity Complex low-level structures Simple, table-based
Change effect Does not affect logical schema May affect views

Answer frame. Open with the definition and the three levels; draw the three-level diagram; develop points 1-5 in order; for the differentiate question give the table; for schema add points 6-7 and the CREATE TABLE example; close with: data independence is the main benefit of the three-level architecture.

Pitfall: Do not mix schema (design, fixed) with instance (data, changing).

Asked: [7 marks] (Dec 2020) Differentiate physical level and logical level of data abstraction. Asked: [7 marks] (Jun 2020, Dec 2020) Write short notes on (any three): three-level architecture of DBMS, functional dependency, transactions, basic structure of SQL query, data independence. Asked: [7 marks] (Jun 2020) Define database schema. Explain it with example.

Data Modeling for a Database

<span style="display:inline-block;padding:.16em .6em;border:1.5px solid currentColor;border-radius:999px;font-size:.68em;font-weight:700;letter-spacing:.06em;text-transform:uppercase;opacity:.75">Not asked since 2022</span>

Definition. <mark>Data modelling is the process of describing the data, its relationships and its constraints in a formal model before building the database.</mark>

Key points.

  1. It moves through conceptual (entities and relationships), logical (tables) and physical (storage) design.
  2. The E-R model is the common conceptual model, and the relational model is the common logical model.
  3. A good model reduces redundancy and makes the database easier to understand and change.

Entities and their Attributes

<span style="display:inline-block;padding:.16em .6em;border:1.5px solid currentColor;border-radius:999px;font-size:.68em;font-weight:700;letter-spacing:.06em;text-transform:uppercase;opacity:.75">Not asked since 2022</span>

Definition. <mark>An entity is a real-world object that can be distinguished from others, and an attribute is a property that describes an entity.</mark>

Key points.

  1. An entity set is a collection of similar entities, such as all students; a weak entity has no key of its own.
  2. Attribute types are simple or composite, single-valued or multivalued, and stored or derived (age from date of birth).
  3. A key attribute uniquely identifies each entity, for example RollNo.
  4. In an E-R diagram entities are rectangles and attributes are ellipses.

Relationships and Relationships Types

<span style="display:inline-block;padding:.16em .6em;border:1.5px solid currentColor;border-radius:999px;font-size:.68em;font-weight:700;letter-spacing:.06em;text-transform:uppercase;opacity:.75">Not asked since 2022</span>

Definition. <mark>A relationship is an association among two or more entities, and its type is given by the cardinality ratio.</mark>

Key points.

  1. One-to-one (1:1): each entity of A relates to at most one of B, such as person and passport.
  2. One-to-many (1:N): one entity of A relates to many of B, such as department and employees.
  3. Many-to-one (N:1) is the reverse of one-to-many.
  4. Many-to-many (M:N): many of A relate to many of B, such as students and courses.
  5. The degree of a relationship is the number of entity sets involved: unary, binary or ternary.

Advantages and Disadvantages of Database Management System

<span style="display:inline-block;padding:.16em .6em;border:1.5px solid currentColor;border-radius:999px;font-size:.68em;font-weight:700;letter-spacing:.06em;text-transform:uppercase;opacity:.75">Low weight</span>

Definition. <mark>A DBMS controls redundancy and keeps data consistent, shared, secure and recoverable, at the cost of extra money and complexity.</mark>

Key points.

  1. Advantages: reduced redundancy and better consistency, because data is stored once, and data sharing among many users.
  2. Integrity constraints keep data valid, and security through authorisation controls who may see or change it.
  3. Data independence, backup and recovery after failure, and controlled concurrent access are also provided.
  4. Disadvantages: high cost of software, hardware and trained staff, and complexity of design and use.
  5. A central database is a single point of failure, and small applications may run slower than with simple files.

Asked: [7 marks] (Dec 2020) What are the major benefits of database systems?

DBMS Vs RDBMS

<span style="display:inline-block;padding:.16em .6em;border:1.5px solid currentColor;border-radius:999px;font-size:.68em;font-weight:700;letter-spacing:.06em;text-transform:uppercase;opacity:.75">Low weight</span>

Definition. <mark>A DBMS manages data in any structure, while an RDBMS is a DBMS that stores data as related tables of rows and columns and enforces relational rules.</mark>

Basis DBMS RDBMS
Storage Files, hierarchical or network Tables with rows and columns
Relationships Not necessarily kept Kept through keys
Normalisation Not supported Supported
Users Usually single user Multiple users
Constraints Few or none Primary and foreign key support
Examples XML store, file system MySQL, Oracle, SQL Server

Key points.

  1. A DBMS has characteristics such as storage, retrieval, security and backup; an example is a hierarchical system.
  2. An RDBMS follows Codd's rules, uses SQL and keeps ACID transactions.
  3. Applications: a DBMS suits small standalone data, and an RDBMS suits banking, enterprise and web systems.

Answer frame. Open with both definitions; give examples; draw the table; close with: every RDBMS is a DBMS, but not every DBMS is relational.

Asked: [7 marks] (Jun 2020) What is DBMS and RDBMS? Explain it.

Last-minute revision

  • Database is organised related data; DBMS is the software managing it.
  • Elements of a database system: data, hardware, software, users, procedures.
  • Three levels: physical, logical (conceptual), view.
  • Physical independence: change storage without changing the logical schema.
  • Logical independence: change the logical schema without changing views.
  • Schema is the design; instance is the current data.
  • Relationship types: 1:1, 1:N, N:1, M:N.
  • RDBMS stores data as related tables.

Memory hooks

  • Physical = how, Logical = what, View = who.
  • Elements: "D-H-S-U-P".
  • Schema is the blueprint, instance is the snapshot.

Coverage checklist

  • Introduction: covered.
  • Significance of Database: Dec 2020 need and elements.
  • Database System Applications: covered.
  • Data Independence: Dec 2020 differentiate, Jun 2020 and Dec 2020 short notes, Jun 2020 schema.
  • Data Modeling for a Database: covered.
  • Entities and their Attributes: covered.
  • Relationships and Relationships Types: covered.
  • Advantages and Disadvantages of Database Management System: Dec 2020 benefits.
  • DBMS Vs RDBMS: Jun 2020 DBMS and RDBMS.
Go to where you left off?

Quick Add to Notes

Save questions, your own notes and screenshots into notes filed by unit. It takes a free account.

Create free account

Have an account? Log in