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 2 Short Notes

How unit 2 is examined

This unit covers the data models used to describe a database; the marks sit on the relational model (properties of a relation) and the E-R model (constraints, generalization and specialization), with one question on the types of data model.

Data Model and Types of Data Model

<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. A data model is a collection of concepts for describing the data, the relationships among data, the semantics and the constraints of a database.

Key points.

  1. The hierarchical model stores data as a tree of parent-child records, where each child has exactly one parent.
  2. The network model stores records as a graph, so a child may have many parents, linked by pointers (sets).
  3. The relational model stores data as tables (relations) of rows and columns linked by common attribute values.
  4. The E-R model is a high-level conceptual model that describes entities, attributes and relationships as a diagram.
  5. Object-oriented and object-relational models store data as objects with attributes and methods; the associative model stores entities and associations as separate items.

Asked: [7 marks] (Jun 2020) Explain various types of Data models in brief.

Relational Data Model

<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. The relational model, proposed by E. F. Codd, represents data as relations; a relation is a table whose rows are tuples and whose columns are attributes.

Key points.

  1. A relation has a unique name, and every column (attribute) in it has a unique name.
  2. Column homogeneity: all values in a column come from the same domain, for example every value of Age is an integer.
  3. Every value is atomic and single-valued, so a cell cannot hold a list or a repeating group.
  4. Tuples are unique, so a relation has no duplicate rows and a key identifies each tuple.
  5. The order of tuples does not matter, because a relation is a set of tuples.
  6. The order of attributes does not matter, because columns are identified by name and not by position.
  7. The schema $R(A_1, A_2, \dots, A_n)$ gives the relation name and attributes; the number of attributes is its degree and the number of tuples its cardinality.

Example.

RollNo Name Branch
101 Asha CS
102 Ravi IT

<mark>A relation is a table of atomic values with a unique name, uniquely named columns, unique tuples and no significance to row or column order.</mark>

Answer frame. Open with the definition of a relation; draw the small STUDENT table; develop points 1-7 in order; close with the highlighted sentence.

Pitfall: Do not say tuples are ordered; a relation is a set, so row order carries no meaning.

Asked: [7 marks] (Dec 2020, Jun 2020) Describe the properties of a relation.

Hierarchical Model

<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. The hierarchical model organises data as a tree of records in which each child record has exactly one parent.

Key points.

  1. Relationships are one-to-many from parent to child, and access starts at the root and moves down.
  2. It is fast for fixed queries, but many-to-many relationships need duplicated records.
  3. Insertion, deletion and restructuring are rigid, since deleting a parent deletes its children (IBM IMS is an example).

Network Data Model

<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. The network model organises records as a graph in which a child record can have more than one parent, linked through sets of pointers.

Key points.

  1. It supports many-to-many relationships directly without duplicating records.
  2. Records are connected by owner-member sets, and access follows pointers (CODASYL DBTG model).
  3. It is more flexible than the hierarchical model, but the pointer structure is complex to design and change.

Object/Relational Model

<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. The object/relational model (ORDBMS) extends the relational model with object features such as user-defined types, methods, inheritance and nested tables.

Key points.

  1. Data is still stored in tables and queried with SQL, which is extended for objects.
  2. Columns may hold complex types such as arrays or user-defined types.
  3. It keeps relational compatibility while giving some object power (Oracle and PostgreSQL are examples).

Object-Oriented Model

<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. The object-oriented model (OODBMS) stores data as objects that bundle attributes with methods and belong to classes.

Key points.

  1. It supports encapsulation, inheritance, polymorphism and object identity.
  2. Complex data such as images and CAD designs is stored naturally, without joins.
  3. It suits programs in object languages, but it has no single standard query language and a smaller user base.

Entity-Relationship Model

<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. The E-R model describes a database as entities (things), their attributes and the relationships among them, drawn as an E-R diagram.

Key points.

  1. Cardinality constraint gives how many entities relate: one-to-one, one-to-many, many-to-one or many-to-many.
  2. Participation constraint is total if every entity must take part in the relationship, else partial.
  3. Key constraint: a key attribute uniquely identifies each entity in a set.
  4. Domain constraint: each attribute takes values only from its permitted set of values.
  5. Generalization is bottom-up: common features of several lower-level entity sets are combined into a superclass.
  6. Specialization is top-down: a superclass is divided into subclasses with extra attributes; subclasses inherit all attributes of the superclass, shown with an IS-A triangle.

Diagram.

<figure class="ds-fig" style="margin:1.4rem 0;overflow-x:auto"><svg xmlns="http://www.w3.org/2000/svg" id="dsfig-u2-01" viewBox="0 0 331 130" width="331" height="130" role="img" aria-label="Person is the superclass; Employee and Customer are subclasses joined by IS-A (generalization upward, specialization downward)"><style>#dsfig-u2-01 .e{stroke:#454C5A;stroke-width:1.4;fill:none}#dsfig-u2-01 .e.hi{stroke:#2340B8;stroke-width:2.6}#dsfig-u2-01 .n{fill:#FFFFFF;stroke:#16181D;stroke-width:1.4}#dsfig-u2-01 .n.hi{fill:#E3E9FC;stroke:#2340B8;stroke-width:2.2}#dsfig-u2-01 .n.rb-b{fill:#16181D;stroke:#16181D}#dsfig-u2-01 .n.rb-r{fill:#BD3227;stroke:#BD3227}#dsfig-u2-01 text{font-family:"JetBrains Mono",ui-monospace,Menlo,Consolas,monospace;font-size:13px}#dsfig-u2-01 .t{fill:#16181D;font-weight:500}#dsfig-u2-01 .t.inv{fill:#FFFFFF;font-weight:700}#dsfig-u2-01 .kd{stroke:#16181D;stroke-width:1.2}#dsfig-u2-01 .dot{fill:#16181D}#dsfig-u2-01 .ann{fill:#2340B8;font-size:11px;font-weight:700}#dsfig-u2-01 .lbl{fill:#6F7787;font-family:system-ui,-apple-system,sans-serif;font-size:12px;font-weight:700}#dsfig-u2-01 .ptr{fill:#2340B8;font-size:12px;font-weight:700}#dsfig-u2-01 .ah{fill:#454C5A}#dsfig-u2-01 .ah.hi{fill:#2340B8}#dsfig-u2-01 .wl rect{fill:#FFFFFF;stroke:#DCE0E7}#dsfig-u2-01 .wl .t{font-size:12px;font-weight:700}#dsfig-u2-01 .wl.hi rect{fill:#2340B8;stroke:#2340B8}#dsfig-u2-01 .wl.hi .t{fill:#FFFFFF}html.dark #dsfig-u2-01 .e{stroke:#B1B7C3}html.dark #dsfig-u2-01 .e.hi{stroke:#8FA3FF}html.dark #dsfig-u2-01 .n{fill:#161920;stroke:#E6E8ED}html.dark #dsfig-u2-01 .n.hi{fill:#1E2748;stroke:#8FA3FF}html.dark #dsfig-u2-01 .n.rb-b{fill:#E6E8ED;stroke:#E6E8ED}html.dark #dsfig-u2-01 .n.rb-r{fill:#FF7E71;stroke:#FF7E71}html.dark #dsfig-u2-01 .t{fill:#E6E8ED}html.dark #dsfig-u2-01 .t.inv{fill:#0F1115}html.dark #dsfig-u2-01 .kd{stroke:#E6E8ED}html.dark #dsfig-u2-01 .dot{fill:#E6E8ED}html.dark #dsfig-u2-01 .ann{fill:#8FA3FF}html.dark #dsfig-u2-01 .lbl{fill:#858D9C}html.dark #dsfig-u2-01 .ptr{fill:#8FA3FF}html.dark #dsfig-u2-01 .ah{fill:#B1B7C3}html.dark #dsfig-u2-01 .ah.hi{fill:#8FA3FF}html.dark #dsfig-u2-01 .wl rect{fill:#161920;stroke:#2A2E37}html.dark #dsfig-u2-01 .wl.hi rect{fill:#8FA3FF;stroke:#8FA3FF}html.dark #dsfig-u2-01 .wl.hi .t{fill:#0F1115}</style><defs><marker id="ah2" 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="ahh2" 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><line class="e" x1="153.5" y1="37" x2="60.5" y2="101"/><line class="e" x1="153.5" y1="37" x2="246.5" y2="101"/><rect class="n" x="120" y="22" width="67" height="30" rx="8"/><text class="t" x="153.5" y="37" dy=".35em" text-anchor="middle">Person</text><rect class="n" x="19" y="86" width="83" height="30" rx="8"/><text class="t" x="60.5" y="101" dy=".35em" text-anchor="middle">Employee</text><rect class="n" x="205" y="86" width="83" height="30" rx="8"/><text class="t" x="246.5" y="101" dy=".35em" text-anchor="middle">Customer</text></svg><figcaption style="font-size:.82em;opacity:.72;margin-top:.45rem">Person is the superclass; Employee and Customer are subclasses joined by IS-A (generalization upward, specialization downward)</figcaption></figure>

<figure class="ds-fig" style="margin:1.4rem 0;overflow-x:auto"><svg xmlns="http://www.w3.org/2000/svg" id="dsfig-u2-02" viewBox="0 0 431 80" width="431" height="80" role="img" aria-label="Employee works for Department, many-to-one, total participation of Employee"><style>#dsfig-u2-02 .e{stroke:#454C5A;stroke-width:1.4;fill:none}#dsfig-u2-02 .e.hi{stroke:#2340B8;stroke-width:2.6}#dsfig-u2-02 .n{fill:#FFFFFF;stroke:#16181D;stroke-width:1.4}#dsfig-u2-02 .n.hi{fill:#E3E9FC;stroke:#2340B8;stroke-width:2.2}#dsfig-u2-02 .n.rb-b{fill:#16181D;stroke:#16181D}#dsfig-u2-02 .n.rb-r{fill:#BD3227;stroke:#BD3227}#dsfig-u2-02 text{font-family:"JetBrains Mono",ui-monospace,Menlo,Consolas,monospace;font-size:13px}#dsfig-u2-02 .t{fill:#16181D;font-weight:500}#dsfig-u2-02 .t.inv{fill:#FFFFFF;font-weight:700}#dsfig-u2-02 .kd{stroke:#16181D;stroke-width:1.2}#dsfig-u2-02 .dot{fill:#16181D}#dsfig-u2-02 .ann{fill:#2340B8;font-size:11px;font-weight:700}#dsfig-u2-02 .lbl{fill:#6F7787;font-family:system-ui,-apple-system,sans-serif;font-size:12px;font-weight:700}#dsfig-u2-02 .ptr{fill:#2340B8;font-size:12px;font-weight:700}#dsfig-u2-02 .ah{fill:#454C5A}#dsfig-u2-02 .ah.hi{fill:#2340B8}#dsfig-u2-02 .wl rect{fill:#FFFFFF;stroke:#DCE0E7}#dsfig-u2-02 .wl .t{font-size:12px;font-weight:700}#dsfig-u2-02 .wl.hi rect{fill:#2340B8;stroke:#2340B8}#dsfig-u2-02 .wl.hi .t{fill:#FFFFFF}html.dark #dsfig-u2-02 .e{stroke:#B1B7C3}html.dark #dsfig-u2-02 .e.hi{stroke:#8FA3FF}html.dark #dsfig-u2-02 .n{fill:#161920;stroke:#E6E8ED}html.dark #dsfig-u2-02 .n.hi{fill:#1E2748;stroke:#8FA3FF}html.dark #dsfig-u2-02 .n.rb-b{fill:#E6E8ED;stroke:#E6E8ED}html.dark #dsfig-u2-02 .n.rb-r{fill:#FF7E71;stroke:#FF7E71}html.dark #dsfig-u2-02 .t{fill:#E6E8ED}html.dark #dsfig-u2-02 .t.inv{fill:#0F1115}html.dark #dsfig-u2-02 .kd{stroke:#E6E8ED}html.dark #dsfig-u2-02 .dot{fill:#E6E8ED}html.dark #dsfig-u2-02 .ann{fill:#8FA3FF}html.dark #dsfig-u2-02 .lbl{fill:#858D9C}html.dark #dsfig-u2-02 .ptr{fill:#8FA3FF}html.dark #dsfig-u2-02 .ah{fill:#B1B7C3}html.dark #dsfig-u2-02 .ah.hi{fill:#8FA3FF}html.dark #dsfig-u2-02 .wl rect{fill:#161920;stroke:#2A2E37}html.dark #dsfig-u2-02 .wl.hi rect{fill:#8FA3FF;stroke:#8FA3FF}html.dark #dsfig-u2-02 .wl.hi .t{fill:#0F1115}</style><defs><marker id="ah3" 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="ahh3" 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="M59,40 L193,40"/><path class="e" d="M231,40 L358,40"/><g class="wl"><rect x="116.4" y="31" width="19.2" height="18" rx="9"/><text class="t" x="126" y="40" dy=".35em" text-anchor="middle">N</text></g><g class="wl"><rect x="288.4" y="31" width="19.2" height="18" rx="9"/><text class="t" x="298" y="40" dy=".35em" text-anchor="middle">1</text></g><circle class="n" cx="40" cy="40" r="18"/><text class="t" x="40" y="40" dy=".35em" text-anchor="middle">Emp</text><circle class="n" cx="212" cy="40" r="18"/><text class="t" x="212" y="40" dy=".35em" text-anchor="middle">WF</text><rect class="n" x="359" y="25" width="50" height="30" rx="15"/><text class="t" x="384" y="40" dy=".35em" text-anchor="middle">Dept</text></svg><figcaption style="font-size:.82em;opacity:.72;margin-top:.45rem">Employee works for Department, many-to-one, total participation of Employee</figcaption></figure>

<mark>Generalization combines entity sets bottom-up into a superclass, and specialization splits a superclass top-down into subclasses that inherit its attributes.</mark>

Answer frame. For generalization: open with both definitions, draw the Person tree, contrast direction and use, close with inheritance. For constraints: open with entity, relationship and attribute, draw the works-for diagram, develop points 1-4, close with the diagram.

Asked: [7 marks] (Dec 2020) Define generalization and specialization with the help of suitable diagram. Asked: [7 marks] (Jun 2020) Explain about various constraints used in E-R model.

Modeling using E-R Diagrams

<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. E-R modeling is designing a database by identifying entities, attributes and relationships and drawing them as a diagram.

Key points.

  1. First list the entity sets, then their attributes and key attributes.
  2. Next add relationships with their cardinality and participation.
  3. Finally convert the diagram to tables: entity to table, many-to-many relationship to its own table.

Notation used in E-R Model

<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. Chen notation draws each E-R element with a fixed symbol.

Key points.

  1. Rectangle is an entity set; double rectangle is a weak entity.
  2. Ellipse is an attribute; underlined ellipse is a key; double ellipse is multivalued; dashed ellipse is derived.
  3. Diamond is a relationship; lines join the parts and carry 1, N or M.

Associative Database Model

<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. The associative model stores data as two kinds of items: entities and associations that link them.

Key points.

  1. Each association is a triple of source, verb and target, for example (Asha, studies, DBMS).
  2. It avoids null values and separates schema from data.
  3. It is rarely used, and Sentences and Lazy DB are its examples.

Last-minute revision

  • A data model describes data, relationships, semantics and constraints.
  • Hierarchical is a tree (one parent); network is a graph (many parents).
  • A relation is a table: rows are tuples, columns are attributes.
  • Relation properties: unique names, one domain per column, atomic values, unique tuples, no order.
  • Degree is the number of attributes; cardinality is the number of tuples.
  • E-R constraints: cardinality, participation, key, domain.
  • Generalization is bottom-up; specialization is top-down.
  • Subclasses inherit superclass attributes through IS-A.
  • ORDBMS extends tables with objects; OODBMS stores objects.
  • Chen notation: rectangle, ellipse, diamond.

Memory hooks

  • HNROA: Hierarchical, Network, Relational, Object, Associative.
  • GEN goes up (bottom-up), SPEC goes down (top-down).
  • Relation properties: "A-U-U-N": Atomic, Unique tuples, Unique names, No order.
  • Chen: Rectangle = thing, Ellipse = detail, Diamond = link.

Coverage checklist

  • Data Model and Types of Data Model: Q Jun 2020 types of data models.
  • Relational Data Model: Q Dec 2020, Jun 2020 properties of a relation.
  • Hierarchical Model: no past questions.
  • Network Data Model: no past questions.
  • Object/Relational Model: no past questions.
  • Object-Oriented Model: no past questions.
  • Entity-Relationship Model: Q Dec 2020 generalization and specialization; Q Jun 2020 E-R constraints.
  • Modeling using E-R Diagrams: no past questions.
  • Notation used in E-R Model: no past questions.
  • Associative Database Model: no past questions.
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