Synetic College Logo
Unit 1 of 4 · M.Sc IT Sem 1

Unit 1: Database and RDBMS fundamentals

Relational Database Management System notes · PTU syllabus (PGCA1904)

3 min read13 topics10 exam questions
On this page
  1. Unit summary
  2. Purpose and applications of database systems
  3. Drawbacks of file systems
  4. View of data
  5. Instances, schemas and data independence
  6. Database languages
  7. Database design, storage and querying
  8. Transaction management and DBMS architecture
  9. Database users and administrators
  10. Structure of relational databases
  11. Database schema and schema diagrams
  12. Keys
  13. Relational query languages
  14. Relational operations
  15. Key terms
  16. Quick revision
  17. Important questions

Unit summary

Relational database systems store most of the world's structured data. This unit covers the purpose and applications of database systems, DBMS fundamentals — views of data, database languages, design, storage and querying, transaction management and architecture — and RDBMS fundamentals: the structure of relational databases, schemas, keys, relational query languages and operations.

After this unit you can

  • Explain the purpose and applications of database systems
  • Describe data abstraction, database languages and DBMS architecture
  • Explain the structure of relational databases, schemas and keys
  • Apply relational query languages and operations

PTU syllabus topics

  • Purpose and applications of database systems
  • DBMS fundamentals (data view, database languages, design, storage and querying, transaction management, architecture)
  • RDBMS fundamentals — structure of relational databases
  • schema
  • keys
  • relational query languages and operations
ClassificationTypes of keys
Database keys

  • Super key

    Any set of attributes that identifies a row

  • Candidate key

    Minimal super key

  • Primary key

    Chosen candidate key

  • Alternate key

    Candidate keys not chosen

  • Foreign key

    Refers to a primary key in another table

1

Topic 1

Purpose and applications of database systems

  • Data: raw facts (e.g. "Ana", 85, "BCA").
  • Information: processed, meaningful data ("Ana scored 85 in BCA").
  • Database: an organised collection of related data stored so it can be accessed easily.
  • DBMS: software that creates, stores, manages and controls access to databases. Examples: MySQL, Oracle, PostgreSQL, SQL Server, MongoDB.

Applications: banking (accounts, transactions), airlines and railways (reservations), universities (students, results), hospitals, e-commerce (orders, inventory), telecom and social media.

2

Topic 2

Drawbacks of file systems

  • File processing system: each application keeps its own files (fees office, library, examination branch), written and read by separate programs.
Key termsProblems of file processing
Data redundancy
Same student address stored in many files
Inconsistency
Address updated in one file but not the others
Difficult access
A new report needs a new program
Data isolation
Data scattered in different formats
Integrity problems
Rules such as marks ≤ 100 buried in program code
Atomicity problems
A crash midway leaves a fee transfer half done
Concurrent access anomalies
Two clerks update the same record at once
Security problems
Hard to give each user only the data they need
3

Topic 3

View of data

  • The ANSI/SPARC architecture separates how users see data from how it is stored, through three levels of schema.
HierarchyThree-level architecture
  1. External level

    Individual user views — what each user or application sees (fees clerk sees fee details only)

  2. Conceptual level

    The whole database for the organisation — all entities, attributes, relationships and constraints, independent of storage

  3. Internal level

    Physical storage — files, records, indexes, access paths, compression

  • Mappings: external–conceptual mapping links each view to the conceptual schema; conceptual–internal mapping links the conceptual schema to stored files.

Example

A college database: the student portal (external) shows a student's marks; the conceptual schema defines STUDENT, COURSE and RESULT tables; the internal level stores RESULT as a B+ tree indexed file on disk.

4

Topic 4

Instances, schemas and data independence

  • Data independence: the ability to change the schema at one level without changing the schema at the next higher level.
ComparisonData independence
Physical data independence
Logical data independence

Meaning

Change the internal schema without changing the conceptual schema

Change the conceptual schema without changing external views or programs

Examples

New index, different file organisation, moving to SSD

Adding a column or a table, splitting a table

Achieved by

Conceptual–internal mapping

External–conceptual mapping

Difficulty

Easier to achieve

Harder, because programs depend on structure

  • Schema vs instance: the schema is the design (changes rarely); the instance is the data at a moment (changes constantly).
5

Topic 5

Database languages

ComparisonDatabase languages
Purpose
Examples

Data definition language (DDL)

Defines schemas, constraints and storage; output stored in the data dictionary

CREATE, ALTER, DROP

Data manipulation language (DML)

Retrieves and modifies data; procedural (how) or declarative (what)

SELECT, INSERT, UPDATE, DELETE

Data control language (DCL)

Grants and revokes access

GRANT, REVOKE

Transaction control (TCL)

Manages transactions

COMMIT, ROLLBACK, SAVEPOINT

6

Topic 6

Database design, storage and querying

ProcessDatabase design phases
  1. 1Requirements analysis

    What data and operations users need

  2. 2Conceptual design

    ER model

  3. 3Logical design

    Map ER to relational schema; normalise

  4. 4Physical design

    Files, indexes, partitions

ComparisonDBMS components
Component
Role

Storage manager

Authorisation and integrity manager, transaction manager, file manager, buffer manager

Interface between stored data and queries; manages data files, data dictionary and indexes

Query processor

DDL interpreter, DML compiler, query evaluation engine

Turns queries into efficient low-level plans

7

Topic 7

Transaction management and DBMS architecture

  • A transaction is a unit of work that must be atomic, consistent, isolated and durable (ACID); the recovery manager ensures atomicity and durability, the concurrency-control manager ensures isolation.
ComparisonDatabase architectures
Structure
Examples

Two-tier

Client application talks directly to the database server

Desktop app with ODBC or JDBC

Three-tier

Client → application server → database server

Web applications

Centralised, parallel and distributed

One machine, many processors, or many sites

Banking core systems, cloud databases

8

Topic 8

Database users and administrators

UserRole
Database Administrator (DBA)Manages the whole database: schema, security, backup, performance
Database designersDecide the structure: tables, keys, relationships
Application programmersWrite programs that use the database
End users (naive and sophisticated)Use the database through forms or queries

Responsibilities of the DBA: defining the schema, granting access, backup and recovery, monitoring performance, and maintaining integrity and security.

9

Topic 9

Structure of relational databases

In the relational model, data is stored in relations (tables). A row is a tuple, a column is an attribute, the set of allowed values is a domain, the number of columns is the degree and the number of rows is the cardinality.

ProcessReducing an ER diagram to tables
  1. 1Each strong entity becomes a table

    Key attribute is the primary key

  2. 2Weak entity

    Table with owner's key + partial key

  3. 31:N relationship

    Put the key of the "1" side as a foreign key in the "N" side

  4. 4M:N relationship

    New table with both keys

  5. 5Multivalued attribute

    Separate table

10

Topic 10

Database schema and schema diagrams

  • Relation schema: the logical design, e.g., instructor(ID, name, dept_name, salary); relation instance: the rows at a moment. A schema diagram shows relations as boxes with attributes, underlines primary keys and draws arrows from foreign keys to referenced keys.
11

Topic 11

Keys

Key termsTypes of keys
Super key
Any set of attributes that uniquely identifies a row
Candidate key
A minimal super key
Primary key
The candidate key chosen to identify rows; unique and not null
Alternate key
Candidate keys not chosen as primary
Foreign key
An attribute referring to the primary key of another table
Composite key
A key made of two or more attributes
Unique key
Must be unique but may allow one null
Surrogate key
An artificial key such as an auto-increment ID

Example

In STUDENT(RollNo, AadhaarNo, Name, Email): {RollNo}, {AadhaarNo} and {Email} are candidate keys; RollNo is chosen as the primary key; AadhaarNo and Email are alternate keys; {RollNo, Name} is a super key but not a candidate key.

12

Topic 12

Relational query languages

ComparisonRelational query languages
Nature
Example

Relational algebra

Procedural — sequence of operations

π name (σ dept = 'IT' (instructor))

Tuple relational calculus

Non-procedural — describe the result

{ t.name such that t ∈ instructor and t.dept = 'IT' }

Domain relational calculus

Non-procedural over attribute values

QBE-style queries

SQL

Declarative commercial language based on algebra and calculus

SELECT name FROM instructor WHERE dept = 'IT'

13

Topic 13

Relational operations

OperationSymbolMeaning
SelectionσChooses rows satisfying a condition: σ age>20 (STUDENT)
ProjectionπChooses columns: π name, course (STUDENT)
Union∪Rows in either relation (union-compatible)
Intersection∩Rows in both relations
Set difference−Rows in the first but not the second
Cartesian product×Every row of one with every row of the other
Join⋈Product followed by a selection on matching columns
Division÷Rows related to all rows of another relation

Example

Names of BCA students: π name (σ course = 'BCA' (STUDENT)).

Key termsAdditional relational operations
Rename (ρ)
Gives a name to a result or attribute
Assignment (←)
Stores an intermediate result
Natural join
Joins on all common attributes and keeps one copy
Outer joins
Keep unmatched tuples padded with nulls
Aggregation (G)
Grouping with SUM, AVG, COUNT

Key terms

Data abstraction
Hiding storage details through physical, logical and view levels
DDL
Language defining database schemas
Storage manager
DBMS component interfacing data files and queries
Relation schema
Logical design of a relation
Relational algebra
Procedural query language on relations

Quick revision

  • Purpose of databases; file-system drawbacks.
  • Physical, logical, view levels; instances and schemas; data independence.
  • DDL, DML, DCL, TCL; design phases; storage manager and query processor.
  • Transactions; two- and three-tier architecture; users and DBA.
  • Tables, tuples, attributes, domains; keys; schema diagrams; σ, π, ∪, −, ×, ⋈, ρ.

Important exam questions

Practice questions written to the PTU exam pattern for this unit's syllabus: short answers (Section A style) and long answers (Sections B and C style).

Short-answer questions

  1. Q1.State three drawbacks of file processing systems.
  2. Q2.Name the three levels of data abstraction.
  3. Q3.Distinguish procedural and declarative DML.
  4. Q4.What are the components of the query processor?
  5. Q5.Distinguish a super key and a candidate key.
  6. Q6.What does the rename operation do?

Long-answer questions

  1. Q1.Explain the purpose of database systems and their applications.
  2. Q2.Explain the architecture of a DBMS with its components.
  3. Q3.Explain the structure of relational databases and keys.
  4. Q4.Explain relational query languages and the fundamental relational operations.

Stuck on this unit?

Message SBS on WhatsApp for help with Relational Database Management System, or to ask about studying M.Sc IT at Synetic.

WhatsApp us