Database Management System CBMCA6T04

Syllabus topics (RTMNU)

• Data, Information, Metadata, Data dictionary
• Database, Components of a database
• DBMS, Components of DBMS
• Traditional File System Versus Database Systems
• Disadvantages of traditional file system
• Database systems, Advantages and Disadvantages of database systems
• DBMS Users
• Schemas, Subschema and Instances
• Three Level Architecture of Database Systems (DBMS)
• Data independence
• File Organization, Basic Concepts of Files
• Records and record types
• Types of files
• File Organization Techniques

Data, Information, Metadata and Data Dictionary

• Data – raw facts and figures without meaning by themselves, e.g. 101, "Riya", 78.
• Information – processed, organised data that has meaning, e.g. "Roll no. 101 Riya scored 78 marks".
• Metadata – "data about data": describes the data, e.g. field name Marks, type NUMBER, size 3.
• Data dictionary – a central store of metadata in a DBMS, listing all tables, fields, data types, sizes, constraints, relationships and users. It is maintained by the DBMS and used by the DBA.

Database and its Components

A database is an organised collection of logically related data stored so that it can be easily accessed, managed and updated. Example: a college database with student, course and fee tables.

Components of a database system: (1) Data – the actual stored data and metadata; (2) Hardware – computers, storage, network; (3) Software – the DBMS and application programs; (4) Users – people who use the database; (5) Procedures – rules and instructions for using the database.

DBMS and its Components

A Database Management System (DBMS) is software that allows users to create, store, retrieve, update and manage data in a database while controlling access and keeping the data safe and consistent. Examples: Oracle, MySQL, MS SQL Server, PostgreSQL, MS Access.

Components of DBMS:

1. DDL compiler – processes table definitions and stores them in the data dictionary.
2. DML compiler / query processor – translates queries into low-level instructions and optimises them.
3. Storage manager – handles storage, retrieval and updating of data on disk (file manager, buffer manager).
4. Transaction manager – ensures transactions are complete and correct (ACID properties).
5. Concurrency control manager – manages many users working at the same time.
6. Recovery manager – restores data after failure using backups and logs.
7. Authorisation and integrity manager – checks access rights and constraints.
8. Data dictionary.

Traditional File System versus Database System; Disadvantages of File System

In a traditional file system, each department keeps its own separate files and programs (e.g. accounts and exam sections each keep their own student files).

Disadvantages of the file system:

1. Data redundancy – same data repeated in many files.
2. Data inconsistency – copies do not match after an update in only one place.
3. Difficulty in accessing data – a new program is needed for each new query.
4. Data isolation – data scattered in different files and formats.
5. Integrity problems – rules (e.g. marks ≤ 100) hard to enforce.
6. Atomicity problems – a failure can leave half-completed updates.
7. Concurrent access anomalies – two users updating together cause errors.
8. Security problems – hard to restrict access.

Advantages and Disadvantages of Database Systems

Advantages: controlled redundancy; consistency; data sharing; data integrity; security; backup and recovery; concurrency control; data independence; easy querying with SQL; standards enforced.

Disadvantages: high cost of software, hardware and trained staff; complexity; large size; failure of the central database affects all users; performance may be slower for very simple applications.

DBMS Users

1. Database Administrator (DBA) – controls the whole database: designs schema, grants access, backup, recovery, performance.
2. Database designers – decide tables, fields and relationships.
3. Application programmers – write programs that use the database.
4. Sophisticated users – analysts who write their own SQL queries.
5. Naive (end) users – use ready-made applications, e.g. bank clerk, ATM user, railway booking clerk.

Schemas, Subschema and Instances

• Schema – the overall logical design/structure of the database (tables, fields, types, relationships). It changes rarely. E.g. STUDENT(RollNo, Name, Course).
• Subschema – a part of the schema seen by a particular user or application (a user view).
• Instance – the actual data stored in the database at a particular moment; it changes often.

Three-Level Architecture (ANSI/SPARC) and Data Independence

1. External level (view level) – how individual users see the data; many external views (subschemas).
2. Conceptual level (logical level) – the whole database for the community of users: entities, attributes, relationships, constraints; hides storage details.
3. Internal level (physical level) – how data is actually stored on disk: files, indexes, record formats.

Data independence – the ability to change the schema at one level without changing the next higher level.

• Physical data independence – change in internal level (new index, storage device) does not affect the conceptual schema.
• Logical data independence – change in conceptual schema (adding a new field or table) does not affect external views/application programs. It is harder to achieve.

File Organization: Files, Records, Record Types and Types of Files

A field is a single data item (Name); a record is a group of related fields (one student's details); a file is a collection of related records.

Record types: fixed-length records (every record same size – simple, may waste space) and variable-length records (size varies – saves space, harder to manage).

Types of files: master file (permanent data, e.g. customer master), transaction file (day-to-day transactions), reference/table file (tax rates, price lists), backup file, archive file, report file.

File Organization Techniques

1. Sequential (serial) file – records stored one after another in key order; good for batch processing (payroll); slow for searching one record.
2. Heap (pile) file – records stored in the order they arrive; fast insertion, slow search.
3. Indexed sequential (ISAM) – records in key order plus an index for direct access; supports both sequential and random access.
4. Direct/random (hashed) file – a hash function on the key gives the address of the record; very fast access (e.g. ATM account lookup).
5. Clustered file – related records from different tables stored close together.

Likely exam questions

1. (Long, 8/16 marks) What is a DBMS? Explain its components and compare a traditional file system with a database system.
2. (Long, 8/16 marks) Explain the three-level architecture of DBMS and data independence.
3. (Short, 4 marks) Define: data, information, metadata and data dictionary.
4. (Short, 4 marks) Explain schema, subschema and instance.
5. (Short, 4 marks) Explain any three file organization techniques.

Syllabus topics (RTMNU)

• Data Model: Types of data models
• Codd's rules
• Comparison of various data models
• Types of Database Systems
• Comparison between Client/Server and Distributed Database System
• Comparison between DBMS and RDBMS
• E-R Models, Basic concepts
• Types of Attributes
• Relationship Sets, Degree of relationship sets
• Mapping cardinalities
• Keys, Types of keys
• Entity-Relationship Diagram
• Advantages and Limitation of E-R model
• Types of Entity Sets
• Enhanced Entity-Relationship (EER): Superclass and subclass entity types
• Specialization, Generalization, Attribute inheritance
• Aggregation

Data Models and their Types

A data model is a collection of concepts used to describe the structure of a database – the data, relationships, meaning and constraints.

1. Hierarchical model – data arranged as a tree; each child has one parent (one-to-many). E.g. IBM's IMS. Fast for fixed paths but rigid.
2. Network model – data as records linked in a graph; a child can have many parents (many-to-many). E.g. IDMS. Flexible but complex.
3. Relational model – data stored in tables (relations) of rows (tuples) and columns (attributes); proposed by E.F. Codd in 1970. E.g. Oracle, MySQL.
4. Entity-Relationship model – conceptual model using entities and relationships (Peter Chen, 1976).
5. Object-oriented model – data stored as objects with attributes and methods.

Codd's Rules

E.F. Codd gave 12 rules (numbered 1–12, plus a foundation Rule 0) that a database must satisfy to be called fully relational:

1. Information rule – all data stored as values in tables.
2. Guaranteed access – every value reachable by table name + primary key + column name.
3. Systematic treatment of NULLs.
4. Active online catalogue – the data dictionary itself is stored as tables.
5. Comprehensive data sub-language – one language (SQL) for definition, manipulation and control.
6. View updating rule.
7. High-level insert, update, delete – on sets of rows.
8. Physical data independence.
9. Logical data independence.
10. Integrity independence – constraints stored in the catalogue, not in programs.
11. Distribution independence.
12. Non-subversion rule – low-level access cannot bypass integrity rules.

Rule 0: the system must manage databases entirely through its relational capabilities.

Types of Database Systems; Client/Server vs Distributed

Types: centralised (one computer), personal/desktop (MS Access), client/server, distributed, parallel, cloud databases, NoSQL databases.

DBMS vs RDBMS

E-R Model: Basic Concepts and Types of Attributes

The Entity-Relationship (E-R) model describes data as entities, their attributes and relationships. Entity – a real-world object (a student); entity set – a group of similar entities (all students); attribute – a property (RollNo, Name).

Types of attributes: simple (Age) vs composite (Name = First + Last); single-valued (DOB) vs multi-valued (Phone numbers); stored (DOB) vs derived (Age from DOB); key attribute (RollNo); null-valued.

Relationship Sets, Degree and Mapping Cardinalities

A relationship is an association among entities (Student enrols in Course); a relationship set is a set of similar relationships.

Degree: unary (recursive – Employee manages Employee), binary (two entity sets), ternary (three).

Mapping cardinalities: one-to-one (Person–Aadhaar), one-to-many (Department–Employees), many-to-one (Employees–Department), many-to-many (Students–Courses).

Keys and Types of Keys

• Super key – any set of attributes that uniquely identifies a row ({RollNo}, {RollNo, Name}).
• Candidate key – a minimal super key (RollNo, Aadhaar No).
• Primary key – the candidate key chosen; unique and not null.
• Alternate key – candidate keys not chosen as primary.
• Foreign key – attribute in one table referring to the primary key of another.
• Composite key – primary key of two or more attributes.

E-R Diagram; Advantages and Limitations

Advantages: simple and easy to understand; good communication between users and designers; easily converted to tables; shows the whole design clearly.

Limitations: no standard notation; no data manipulation; loses some information when converted; can become cluttered for large systems; cannot show some constraints.

Types of Entity Sets

Strong entity set – has its own primary key (Employee). Weak entity set – has no key of its own and depends on a strong (owner) entity; identified by a partial key plus the owner's key (Dependent of an Employee). Shown by a double rectangle and double diamond.

Enhanced E-R (EER): Superclass, Subclass, Specialization, Generalization, Attribute Inheritance, Aggregation

• Superclass – a general entity type (EMPLOYEE); subclass – a sub-group with extra attributes (MANAGER, CLERK).
• Specialization – top-down: dividing a superclass into subclasses based on distinguishing features (Account → Savings, Current).
• Generalization – bottom-up: combining entity types with common features into a superclass (Car, Truck → Vehicle).
• Attribute inheritance – a subclass inherits all attributes and relationships of its superclass (Savings Account inherits AccNo, Balance).
• Aggregation – treating a relationship (with its entities) as a higher-level entity so that it can take part in another relationship. E.g. (Employee works-on Project) is monitored by Manager.

Likely exam questions

1. (Long, 8/16 marks) What is a data model? Explain the hierarchical, network and relational models and compare them.
2. (Long, 8/16 marks) What is an E-R model? Explain its basic concepts, types of attributes, keys and mapping cardinalities with an E-R diagram.
3. (Short, 4 marks) Explain any six of Codd's rules.
4. (Short, 4 marks) Distinguish between DBMS and RDBMS.
5. (Short, 4 marks) Explain specialization, generalization and aggregation.

Syllabus topics (RTMNU)

• Normalization
• Benefits of normalization
• First, Second and Third normal form
• Denormalization, advantages and disadvantages of Denormalization
• Parallel databases: Parallel Database Architectures
• Advantages and Disadvantages of Parallel Databases
• Distributed Database: Basic Concepts
• Distributed Database Management System (DDBMS)
• Advantages and Disadvantages of Distributed Databases
• DDBMS Architectures
• Comparison of DBMS and DDBMS
• Comparison of Parallel and Distributed Databases

Normalization and its Benefits

Normalization is the process of organising data in a relational database by dividing large tables into smaller related tables, so as to reduce redundancy and remove insertion, update and deletion anomalies.

Anomalies in an un-normalised table: insertion anomaly (cannot add a course until a student joins it), update anomaly (changing a teacher's name in many rows), deletion anomaly (deleting the last student also deletes course data).

Benefits: less redundancy and storage; consistency; removes anomalies; better data integrity; easier maintenance; clearer design.

Functional dependency (FD): A → B means the value of A determines the value of B (RollNo → Name).

First, Second and Third Normal Form (worked example)

Un-normalised table:

1NF – every cell has a single (atomic) value; no repeating groups. Split multi-valued cells into separate rows: (1, Riya, C1, Tally, Mr. Rao, 98xxx11) and (1, Riya, C2, Excel, Ms. Joshi, 98xxx22). Key = (RollNo, CourseID).

2NF – in 1NF and no partial dependency (no non-key attribute depends on only part of a composite key). Name depends only on RollNo; CourseName, Teacher depend only on CourseID. So split:

• STUDENT(RollNo, Name)
• COURSE(CourseID, CourseName, Teacher, TeacherPhone)
• ENROLMENT(RollNo, CourseID)

3NF – in 2NF and no transitive dependency (non-key attribute depending on another non-key attribute). In COURSE, CourseID → Teacher → TeacherPhone. So split:

• COURSE(CourseID, CourseName, Teacher)
• TEACHER(Teacher, TeacherPhone)

Denormalization: Advantages and Disadvantages

Denormalization is the deliberate addition of some redundancy (combining tables or storing derived values) to improve read/query performance.

Advantages: faster queries (fewer joins); simpler queries; good for reports and data warehouses.

Disadvantages: redundancy and more storage; risk of inconsistency; slower inserts and updates; more complex maintenance.

Parallel Databases: Architectures, Advantages and Disadvantages

A parallel database uses many processors and disks working at the same time on one database to speed up queries and handle large loads.

Architectures:

1. Shared memory – all processors share common memory and disks; fast communication, limited scalability.
2. Shared disk – each processor has its own memory but all share disks; good fault tolerance.
3. Shared nothing – each processor has its own memory and disk; communicate over network; highly scalable.
4. Hierarchical (hybrid) – combination of the above.

Advantages: high speed (speed-up), handles more users and data (scale-up), high availability, efficient for big queries. Disadvantages: costly, complex design, start-up and coordination overheads, load-balancing problems (skew).

Distributed Databases and DDBMS: Concepts, Advantages and Disadvantages

A distributed database is a collection of logically related data spread over several computers (sites) connected by a network. A DDBMS is the software that manages it and makes the distribution transparent to users – the user feels it is one database.

Concepts: fragmentation (horizontal – rows split by branch; vertical – columns split), replication (copies at several sites), allocation (which fragment is stored where), transparency (location, fragmentation, replication).

Advantages: reflects organisational structure (branches); local autonomy; reliability and availability (one site failing does not stop all); faster local access; easy expansion.

Disadvantages: complex; costly; security harder; maintaining consistency of copies; network dependence; difficult design and query processing.

DDBMS Architectures

1. Client–server architecture – clients send queries to one or more servers holding data.
2. Peer-to-peer (collaborating servers) – every site acts as both client and server.
3. Multi-database (federated) system – independent, possibly different databases linked through a common layer.

Distributed databases may be homogeneous (same DBMS at all sites) or heterogeneous (different DBMSs).

Comparison of DBMS and DDBMS; Parallel vs Distributed Databases

Likely exam questions

1. (Long, 8/16 marks) What is normalization? Explain 1NF, 2NF and 3NF with a suitable example.
2. (Long, 8/16 marks) What is a distributed database? Explain its advantages, disadvantages and DDBMS architectures.
3. (Short, 4 marks) What is denormalization? State its advantages and disadvantages.
4. (Short, 4 marks) Explain the architectures of parallel databases.
5. (Short, 4 marks) Distinguish between parallel and distributed databases.

Syllabus topics (RTMNU)

• Structured Query Language
• Characteristics of SQL
• Advantages of SQL
• Components of SQL Language: DDL, DML, DCL, DQL
• Basic data types
• Table fundamentals: creating and modifying table
• Data Constraints, Types of data constraints
• Operators in SQL
• ORACLE functions: Text, Numeric, Date Functions

SQL: Meaning, Characteristics and Advantages

SQL (Structured Query Language) is the standard language used to create, manage and query relational databases. It was developed at IBM in the 1970s and is standardised by ANSI and ISO. Oracle, MySQL and SQL Server all use SQL.

Characteristics: English-like, easy to learn; non-procedural (you say what you want, not how); works on sets of rows; not case-sensitive for keywords; portable across RDBMSs; one language for definition, manipulation and control; can be embedded in other languages.

Advantages: fast retrieval of large data; no heavy coding; standard and portable; interactive; supports multiple views; security through GRANT/REVOKE; widely used, so skills are in demand.

Components of SQL: DDL, DML, DCL, DQL

Basic Data Types (Oracle)

• CHAR(n) – fixed-length text up to n characters (padded with spaces).
• VARCHAR2(n) – variable-length text up to n characters (saves space).
• NUMBER(p, s) – numbers; p = total digits, s = digits after decimal. NUMBER(8,2) stores 123456.78.
• DATE – date and time (default display like 15-MAR-26).
• LONG – long variable text; RAW/LONG RAW – binary data; CLOB/BLOB – large text/binary objects.

Table Fundamentals: Creating and Modifying Tables

Modifying structure with ALTER:

DELETE vs TRUNCATE vs DROP: DELETE (DML) removes selected rows and can be rolled back; TRUNCATE (DDL) removes all rows quickly, cannot be rolled back; DROP removes the table structure and data.

Data Constraints and their Types

Constraints are rules applied to columns to keep data accurate and reliable (integrity).

1. NOT NULL – column cannot be empty.
2. UNIQUE – no duplicate values (NULLs allowed).
3. PRIMARY KEY – unique + not null; identifies each row.
4. FOREIGN KEY (REFERENCES) – value must match the primary key of another table (referential integrity).
5. CHECK – value must satisfy a condition (marks between 0 and 100).
6. DEFAULT – value used when none is given.

Constraints may be defined at column level (with the column) or table level (after all columns, needed for composite keys).

Operators in SQL

• Arithmetic: + − * / (SELECT salary*12 FROM emp).
• Comparison: =, <> or !=, >, <, >=, <=.
• Logical: AND, OR, NOT.
• Range and set: BETWEEN … AND …, IN (list), NOT IN.
• Pattern matching: LIKE with % (any characters) and _ (one character): WHERE name LIKE 'R%'.
• NULL test: IS NULL, IS NOT NULL.
• Concatenation: || joins text ('Mr. ' || name).
• Set operators: UNION, INTERSECT, MINUS.

Oracle Functions: Text, Numeric and Date

Oracle functions are tested on the dummy table DUAL: SELECT UPPER('padhai') FROM dual; → PADHAI.

Likely exam questions

1. (Long, 8/16 marks) What is SQL? Explain its characteristics, advantages and components (DDL, DML, DCL, DQL) with examples.
2. (Long, 8/16 marks) What are data constraints? Explain the types of constraints with suitable SQL examples.
3. (Short, 4 marks) Explain any four text functions in Oracle with examples.
4. (Short, 4 marks) Explain any four date functions in Oracle.
5. (Short, 4 marks) Explain the LIKE, BETWEEN and IN operators in SQL.