संगणकशास्त्र Computer Science

थोडक्यात सारांश

Object-Oriented Programming (OOP) is a way of writing programs around objects, which combine data and the functions that work on that data. It models real-world things. This topic covers the difference between procedural and object-oriented programming, and the main OOP concepts: class, object, encapsulation, abstraction, inheritance, polymorphism, dynamic binding and message passing, plus the benefits of OOP and an introduction to C++.

महत्त्वाचे मुद्दे

• Procedure-oriented programming (POP) like C focuses on functions; data moves freely between functions and is less secure. It follows a top-down approach.
• OOP like C++, Java and Python focuses on data (objects); data is hidden and accessed only through member functions. It follows a bottom-up approach.
• Class: a user-defined blueprint that describes data members and member functions. Example: class Student.
• Object: an instance of a class; a real entity in memory. Example: Student s1;
• Encapsulation: wrapping data and functions into a single unit (class). It supports data hiding using private access.
• Abstraction: showing only essential features and hiding background details (we drive a car without knowing the engine design).
• Inheritance: a new class (derived) gets properties of an existing class (base). It gives reusability.
• Polymorphism ("many forms"): the same name behaves differently in different situations. Compile-time (function and operator overloading) and run-time (virtual functions).
• Dynamic binding: the code to run for a function call is decided at run time.
• Message passing: objects communicate by calling each other's functions.
• Benefits: reusability, data security, easier maintenance, modelling of real-world problems, scalability.
• C++ was developed by Bjarne Stroustrup at Bell Labs in the early 1980s, first called "C with Classes".
• C++ I/O uses cout with the insertion operator and cin with the extraction operator, from the iostream header.

महत्त्वाच्या संज्ञा

• Object (वस्तू) – instance of a class with its own state and behaviour.
• Data hiding – preventing direct access to data from outside the class.
• Reusability – using existing code again through inheritance.
• Overloading – same function name or operator with different meanings by argument type.
• Access specifiers – private, protected, public; control visibility of members.

सूत्रे

A first C++ program:

५ महत्त्वाचे प्रश्न

Q1. Differentiate between a class and an object.

A class is a blueprint or template that defines data and functions; it uses no memory for data until objects are made. An object is an instance of a class that occupies memory. Example: Car is a class; myCar is an object.

Q2. What is encapsulation? How does it give data hiding?

Encapsulation binds data and functions into a class. By declaring data members private, they can be reached only through public member functions, so outside code cannot change them directly. This is data hiding.

Q3. Explain the basic concepts of OOP.

• Class and object.
• Encapsulation and data hiding.
• Abstraction.
• Inheritance (reusability).
• Polymorphism (overloading, virtual functions).
• Dynamic binding and message passing.
• Give a one-line example for each (e.g. BankAccount class with deposit()).

Q4. Compare procedure-oriented and object-oriented programming.

• POP divides the program into functions; OOP into objects.
• POP uses top-down design; OOP bottom-up.
• In POP data is mostly global; OOP hides data inside classes.
• OOP supports inheritance, polymorphism and overloading; POP does not.
• Examples: C (POP); C++, Java (OOP).

Q5. What are the benefits and applications of OOP?

• Benefits: code reuse, data security, easy upgrades, modular design, fewer errors in big projects.
• Real-world modelling makes design natural.
• Applications: GUI applications, games, simulation, CAD/CAM, real-time systems, object databases.
• Many modern languages (C++, Java, Python, C#) are object-oriented.

परीक्षेसाठी टीप

The POP vs OOP table and "explain OOP concepts" are near-certain questions. Give a small real-life example for each concept to score full marks.

थोडक्यात सारांश

In C++, a class groups data members and member functions into one unit, and objects are variables of that class. This topic covers defining a class, access specifiers, defining member functions inside and outside the class, creating objects, constructors and destructors, static members, friend functions, and inline functions.

महत्त्वाचे मुद्दे

• Syntax: class Name { private: data; public: functions; }; — note the semicolon after the closing brace.
• Access specifiers: private (default in a class; accessible only inside the class), public (accessible everywhere), protected (accessible in the class and its derived classes).
• Members are accessed through an object with the dot operator: s1.getdata();
• Member functions can be defined inside the class (they become inline automatically) or outside using the scope resolution operator ::, e.g. void Student::show() { … }
• Constructor: a special member function with the same name as the class and no return type. It is called automatically when an object is created. Used to initialise data.
• Types of constructor: default (no arguments), parameterised, copy constructor (initialises an object from another object of the same class).
• Constructor overloading: a class can have several constructors with different parameter lists.
• Destructor: same name as the class with a tilde (~), no arguments and no return type; called automatically when the object goes out of scope. Only one destructor per class.
• Static data member: one copy shared by all objects (e.g. a counter of objects). It must also be defined outside the class.
• Static member function can access only static members and is called with the class name: Student::count();
• Friend function: not a member, but declared with the keyword friend inside the class, so it can access private data.
• Inline function: the compiler may replace the call with the function body, saving call overhead for small functions.
• The this pointer inside a member function points to the object that called it.

महत्त्वाच्या संज्ञा

• Data member – variable declared inside a class.
• Member function (सदस्य फलन) – function declared inside a class.
• Scope resolution operator (::) – links a function defined outside to its class.
• Constructor – auto-called initialiser with the class name.
• Destructor – auto-called clean-up function, ~ClassName().

सूत्रे

Example class:

५ महत्त्वाचे प्रश्न

Q1. What is a constructor? List its characteristics.

A constructor is a special member function that initialises an object automatically when it is created. It has the same name as the class, has no return type (not even void), is usually declared public, can be overloaded, and cannot be virtual.

Q2. Differentiate constructor and destructor.

A constructor initialises an object when it is created; it can take arguments and can be overloaded. A destructor releases resources when the object is destroyed; its name starts with ~, it takes no arguments and cannot be overloaded.

Q3. Find the output and explain:

• c is static, so all objects share one copy.
• Each object creation calls the constructor, which increments c.
• Three objects a, b, d → c = 3.
• Output: 3

Q4. Write a C++ class Student with roll number and marks, with functions to read and display data (one defined outside the class).

• Data members are private by default; functions are public.
• show() is defined outside using ::.

Q5. What is a friend function? Write its features with an example.

• A non-member function given access to private members by declaring it with friend inside the class.
• It is called like a normal function, not with an object and dot.
• It takes objects as arguments, e.g. friend float average(Student s);
• It can be a friend of more than one class (useful to work on two classes' data).
• Drawback: it weakens data hiding, so use it only when needed.

परीक्षेसाठी टीप

Learn the types of constructors with one small example each — "explain constructors with types" is one of the most frequent long questions.

थोडक्यात सारांश

Inheritance lets a new class (derived class) acquire the properties of an existing class (base class), giving code reuse. Polymorphism lets one name take many forms. This topic covers types of inheritance, visibility modes, constructors in derived classes, the diamond problem and virtual base classes, function and operator overloading, function overriding, virtual functions and abstract classes.

महत्त्वाचे मुद्दे

• Syntax: class Derived : public Base { … };
• Visibility modes: public, protected, private. Private members of the base are never directly accessible in the derived class.
• With public inheritance: public stays public, protected stays protected. With protected inheritance: public and protected become protected. With private inheritance: both become private.
• Types of inheritance: single (A → B), multiple (A, B → C), multilevel (A → B → C), hierarchical (A → B, A → C), hybrid (combination).
• Diamond problem: in hybrid inheritance, class D gets two copies of A's members through B and C. Solved by making A a virtual base class: class B : virtual public A.
• Constructors of the base class run first, then the derived class; destructors run in reverse order.
• Compile-time (static) polymorphism: function overloading (same name, different parameters) and operator overloading (giving a new meaning to +, ==, etc. for user types).
• Run-time (dynamic) polymorphism: achieved using virtual functions and a base class pointer or reference. The function that runs depends on the type of object pointed to, not the type of pointer.
• Function overriding: a derived class redefines a base class function with the same signature.
• Pure virtual function: virtual void draw() = 0; A class with at least one pure virtual function is an abstract class; it cannot be instantiated.
• Some operators cannot be overloaded: scope resolution (::), member access (.), pointer-to-member (.*), conditional (?:) and sizeof.

महत्त्वाच्या संज्ञा

• Base class (मूळ वर्ग) – class whose features are inherited.
• Derived class – class that inherits from a base class.
• Virtual function – member function declared virtual so that calls are resolved at run time.
• Abstract class – class with a pure virtual function; used only as a base.
• Early binding / late binding – function call fixed at compile time / at run time.

सूत्रे

Run-time polymorphism example:

५ महत्त्वाचे प्रश्न

Q1. What is a virtual base class?

When a class is inherited by two classes which are both inherited by a fourth class (diamond shape), the fourth gets two copies of the first class. Declaring the inheritance with the keyword virtual (class B : virtual public A) keeps only one shared copy.

Q2. Differentiate function overloading and function overriding.

Overloading: same function name with different parameter lists in the same scope; decided at compile time. Overriding: derived class redefines a base function with the same name and parameters; with virtual functions it is decided at run time.

Q3. Find the output (no virtual keyword):

• show() is not virtual, so binding is early (compile time).
• The call is decided by the pointer type A*.
• Output: A
• If show() in A were declared virtual, output would be B.

Q4. Explain the types of inheritance with diagrams in words.

• Single: one base, one derived (Person → Student).
• Multiple: two bases, one derived (Teacher, Researcher → Professor).
• Multilevel: chain (Person → Employee → Manager).
• Hierarchical: one base, many derived (Shape → Circle, Square).
• Hybrid: mix, e.g. multilevel + multiple, may need a virtual base class.

Q5. What is operator overloading? Overload + to add two complex numbers.

• c3 = c1 + c2; is read as c1.operator+(c2).
• Rules: at least one operand must be a user-defined type; precedence and arity cannot change; some operators cannot be overloaded.

परीक्षेसाठी टीप

Always show the effect of the virtual keyword with a base pointer example — it is the clearest way to explain run-time polymorphism.

थोडक्यात सारांश

Templates let one piece of C++ code work with many data types, which is called generic programming. Exception handling lets a program catch run-time errors and respond to them instead of crashing. This topic covers function templates, class templates, templates with more than one type, an introduction to the STL, and exception handling with try, throw and catch, including multiple catch blocks and the catch-all handler.

महत्त्वाचे मुद्दे

• A template is a blueprint; the compiler creates the real function or class for each type used. This is compile-time polymorphism.
• Function template: template <class T> T maxOf(T a, T b) { return (a > b) ? a : b; } works for int, double, char …
• Class template: template <class T> class Box { T v; … }; used as Box<int> b1(10); or Box<string> b2("Hi");.
• typename and class mean the same in a template parameter list.
• Multiple parameters: template <class T1, class T2>.
• A template function can be overloaded by a normal function; the exact normal match is preferred.
• STL (Standard Template Library) is built from templates: containers (vector, list, stack, queue, map), algorithms (sort, find) and iterators.
• An exception is an unusual run-time event such as division by zero, array index out of range or memory not available.
• try block holds code that may fail; throw raises the exception; catch handles it.
• When an exception is thrown, the rest of the try block is skipped and control jumps to the matching catch.
• Several catch blocks can follow one try; the first one whose type matches is run.
• catch(...) catches any exception; write it last.
• An exception can be rethrown with throw; inside a catch block.
• Standard exceptions come from std::exception, e.g. bad_alloc (new fails), out_of_range.

महत्त्वाच्या संज्ञा

• Generic programming – writing code independent of data type.
• Template instantiation – compiler creating a real version for a given type.
• Exception – run-time error condition.
• Handler – a catch block.
• Stack unwinding – destroying local objects while control moves to the handler.

सूत्रे

• Function template syntax: template <class T> return-type name(T arg, …) { … }
• Class template object: ClassName<type> object(args);
• Exception syntax: try { … throw value; … } catch (type var) { … }

५ महत्त्वाचे प्रश्न

Q1. What is a template? Why is it called generic programming?

A template is a model for a function or class in which the data type is a parameter. The same code serves int, float, char or user types, and the compiler generates the needed version. Because the code is written once without fixing the type, it is called generic programming.

Q2. What does catch(...) do? Where should it be placed?

catch(...) (three dots) is the catch-all handler; it catches an exception of any type. It must be the last catch block, otherwise the specific handlers after it would never run.

Q3. Find the output:

• maxOf(3, 7) uses T = int → 7
• maxOf(2.5, 1.5) uses T = double → 2.5
• maxOf('a', 'z') uses T = char; 'z' (122) > 'a' (97) → z
• Output: 7 2.5 z

Q4. Write a program to handle division by zero using exception handling. Show the output.

• Output: 5, then Error: Division by zero
• "after" is never printed, because after the throw the rest of the try block is skipped.

Q5. Explain class templates with an example, and list the advantages of templates.

Outline: syntax template <class T> class Box { T v; public: Box(T x) { v = x; } T get() { return v; } }; → objects Box<int> b1(10); Box<string> b2("Hi"); print 10 and Hi → a class template with two types (Pair<T1, T2>) → advantages: code reuse, type safety, less code, base of the STL → disadvantage: larger compiled code and harder error messages.

परीक्षेसाठी टीप

In output questions, write which type T becomes for every call. For exceptions, show clearly which statements are skipped after throw.

थोडक्यात सारांश

C++ handles files through stream classes declared in <fstream>. An ofstream writes to a file, an ifstream reads from it, and an fstream can do both. This topic covers the stream class hierarchy, opening and closing files, file modes, reading and writing text with <<, >> and getline(), binary files with write() and read(), file pointers and random access, and error checking.

महत्त्वाचे मुद्दे

• A stream is a flow of bytes. cin and cout are console streams; file streams connect to disk files.
• Classes: ifstream (input file), ofstream (output file), fstream (both). They come from istream, ostream and iostream, whose common base is ios.
• Open by constructor: ofstream out("marks.txt"); or by function: out.open("marks.txt");. Close with out.close();.
• File modes (combine with |): ios::in read, ios::out write, ios::app append at end, ios::ate go to end on opening, ios::trunc erase old contents, ios::binary binary mode, ios::nocreate/ios::noreplace in old compilers.
• Text I/O uses << and >> just like cout/cin; getline(in, line) reads a full line with spaces.
• Character I/O: get(ch) and put(ch).
• Binary I/O: out.write((char*) &obj, sizeof(obj)); and in.read((char*) &obj, sizeof(obj)); — used to store whole objects.
• Check success with if (!in) or in.is_open(). eof() is true at end of file; fail() when an operation fails.
• File pointers: get pointer (reading) and put pointer (writing). seekg()/seekp() move them; tellg()/tellp() give their position.
• Seek origin: ios::beg, ios::cur, ios::end.
• A file is closed automatically when the stream object goes out of scope (destructor), but closing explicitly is good practice.

महत्त्वाच्या संज्ञा

• ifstream / ofstream / fstream – stream classes for input, output and both.
• Get pointer – position of the next read.
• Put pointer – position of the next write.
• Binary file – stores the memory image of data.
• Persistent object – object saved in a file so it survives after the program ends.

सूत्रे

• Open with modes: fstream f("data.dat", ios::in | ios::out | ios::binary);
• Position of object k (k = 0, 1, …) = k × sizeof(object) → f.seekg(k * sizeof(obj), ios::beg);
• Number of objects in file = file size ÷ sizeof(object), file size from seekg(0, ios::end); tellg();

५ महत्त्वाचे प्रश्न

Q1. Differentiate ios::app and ios::ate.

ios::app opens the file so that every write is added at the end; existing data cannot be overwritten. ios::ate only moves the pointer to the end when the file is opened; after that the pointer can be moved with seekp and data can be written anywhere.

Q2. What are seekg() and tellg()?

seekg(offset, origin) moves the get (read) pointer, e.g. in.seekg(0, ios::end) jumps to the end. tellg() returns the current position of the get pointer in bytes. Together they give the file size: in.seekg(0, ios::end); long size = in.tellg();.

Q3. A file s.txt has the lines "Asha 85" and "Ravi 72". Write a program to read it and print the number of students, total and average marks.

• Output: 2 157 78.5 (85 + 72 = 157; 157 ÷ 2 = 78.5)

Q4. Write a program to store objects of class Student in a binary file and display them.

Outline: class Student with roll, name (char array), marks and functions getdata()/show() → ofstream out("stu.dat", ios::binary); → loop: getdata, out.write((char*) &s, sizeof(s)); → close → ifstream in("stu.dat", ios::binary); → while (in.read((char*) &s, sizeof(s))) s.show(); → close. Note: use char arrays, not string, inside objects written in binary.

Q5. Explain the file stream class hierarchy and the file opening modes.

Outline: draw ios at top → istream and ostream → iostream; file classes ifstream (from istream), ofstream (from ostream), fstream (from iostream), plus filebuf for buffering → list each mode with its meaning → show combining modes with | → explain default modes (ifstream = in, ofstream = out | trunc).

परीक्षेसाठी टीप

Always check whether the file opened (if (!in)) and close it at the end. In binary programs, write the (char*) &obj cast and sizeof(obj) exactly.

थोडक्यात सारांश

A Database Management System (DBMS) is software used to create, store, manage and retrieve data in a database. It removes many problems of the old file-processing system. This topic covers advantages of DBMS, three-level architecture and data independence, data models, the ER model, the relational model with keys, and normalisation (1NF, 2NF, 3NF).

महत्त्वाचे मुद्दे

• Database: an organised collection of related data. DBMS: software to manage it (MySQL, Oracle, PostgreSQL, MS Access).
• Problems of file systems: data redundancy, inconsistency, difficulty in access, poor security, no concurrent access control.
• Advantages of DBMS: less redundancy, consistency, data sharing, security, integrity constraints, backup and recovery, concurrency control.
• Three-level (ANSI/SPARC) architecture: external level (user views), conceptual level (whole logical structure), internal level (physical storage).
• Data independence: physical (change storage without changing the logical schema) and logical (change the conceptual schema without changing user views).
• Data models: hierarchical (tree), network (graph), relational (tables), object-oriented.
• ER model (Peter Chen, 1976): entity (rectangle), attribute (ellipse), relationship (diamond). Key attributes are underlined; multivalued attributes have double ellipses; weak entities have double rectangles.
• Cardinality: one-to-one, one-to-many, many-to-one, many-to-many.
• Relational model (E. F. Codd, 1970): data in tables called relations. Row = tuple, column = attribute, number of rows = cardinality, number of columns = degree.
• Keys: super key, candidate key, primary key (unique, not null), alternate key, foreign key (refers to a primary key of another table), composite key.
• Normalisation reduces redundancy and update anomalies:
• 1NF – every attribute has atomic (single) values.
• 2NF – in 1NF and no partial dependency (non-key attribute depending on part of a composite key).
• 3NF – in 2NF and no transitive dependency (non-key depending on another non-key).
• ACID properties of transactions: Atomicity, Consistency, Isolation, Durability.

महत्त्वाच्या संज्ञा

• Schema – logical design of the database. Instance – data at a given moment.
• Tuple (पंक्ती) – a row of a table.
• Primary key – attribute(s) that uniquely identify each row.
• Foreign key – attribute that links to the primary key of another table.
• Functional dependency – X → Y means the value of X decides the value of Y.
• DBA – database administrator who controls the database.

सूत्रे

• Degree = number of attributes (columns); Cardinality = number of tuples (rows)
• Functional dependency notation: RollNo → Name
• Partial dependency: (RollNo, CourseID) → StudentName depends only on RollNo
• Transitive dependency: RollNo → PinCode and PinCode → City, so RollNo → City through PinCode

५ महत्त्वाचे प्रश्न

Q1. Define primary key and foreign key with an example.

A primary key uniquely identifies each row and cannot be NULL, e.g. RollNo in Student. A foreign key is a column in one table that refers to the primary key of another, e.g. RollNo in the Marks table referring to Student(RollNo).

Q2. What is data independence? Name its types.

It is the ability to change the schema at one level without changing the schema at the next higher level. Physical data independence and logical data independence.

Q3. Draw an ER diagram (in words) for a college with Student and Course.

• Entity Student (rectangle): attributes RollNo (underlined key), Name, Phone (double ellipse if many).
• Entity Course (rectangle): CourseID (key), Title, Credits.
• Relationship "Enrolls" (diamond) between them.
• Cardinality many-to-many (M:N): a student takes many courses; a course has many students.
• Attribute of relationship: Grade (ellipse attached to the diamond).

Q4. Normalise up to 3NF: Result(RollNo, Name, CourseID, CourseName, Marks), key (RollNo, CourseID).

• 1NF: values are atomic → already 1NF.
• Partial dependencies: RollNo → Name; CourseID → CourseName.
• 2NF: Student(RollNo, Name), Course(CourseID, CourseName), Result(RollNo, CourseID, Marks).
• No transitive dependency remains → all three tables are in 3NF.

Q5. Explain the three-level architecture of DBMS.

• External level: individual user views; each user sees only needed data.
• Conceptual level: complete logical structure — entities, relationships, constraints.
• Internal level: how data is physically stored — files, indexes.
• Mappings between levels give data independence.
• Diagram: three stacked layers with users at top and the stored database at bottom.

परीक्षेसाठी टीप

Normalisation examples carry big marks. Always state the key first, list dependencies, then split tables step by step.

थोडक्यात सारांश

The relational model, proposed by E. F. Codd in 1970, stores data in tables called relations. This topic covers relational terms (relation, tuple, attribute, domain, degree, cardinality), the types of keys, integrity constraints, Codd's rules in brief, and relational algebra — the set of operations (select, project, union, set difference, Cartesian product, rename, join, intersection, division) used to query relations, with worked examples.

महत्त्वाचे मुद्दे

• A relation is a table; a tuple is a row; an attribute is a column; a domain is the set of allowed values of an attribute.
• Degree = number of attributes (columns); cardinality = number of tuples (rows).
• Rows have no fixed order, and no two rows are exactly the same.
• Keys: super key (any set of attributes that identifies a row uniquely), candidate key (minimal super key), primary key (the candidate key chosen), alternate key (other candidate keys), foreign key (attribute that refers to the primary key of another table), composite key (key with two or more attributes).
• Integrity constraints: domain constraint (value must be from its domain), entity integrity (primary key cannot be NULL), referential integrity (a foreign key value must exist in the referenced table or be NULL).
• Relational algebra is procedural: it says what operations to do and in which order. Each operation takes relations and returns a relation.
• Basic operations: select σ (picks rows), project π (picks columns, removes duplicates), union ∪, set difference −, Cartesian product ×, rename ρ.
• Derived operations: intersection ∩, join ⋈ (theta join, equi-join, natural join), division ÷.
• Union, difference and intersection need union-compatible relations: same number of attributes with matching domains.
• Relational calculus (tuple and domain) is non-procedural: it says only what is wanted. SQL is based on both.

महत्त्वाच्या संज्ञा

• Relation schema – name and list of attributes, e.g. Student(roll, name, city, dept_id).
• Relation instance – the data in the table at a given time.
• NULL – unknown or missing value.
• Natural join – join on common attributes, keeping one copy of them.
• Union compatible – having the same structure for set operations.

सूत्रे

Tables used: Student(roll, name, city, dept_id) = (1, Asha, Nagpur, 10), (2, Ravi, Pune, 20), (3, Neha, Nagpur, 10), (4, Amit, Bhandara, NULL); Dept(dept_id, dname) = (10, Computer), (20, Physics), (30, Maths).

• Degree of R × S = degree of R + degree of S; cardinality = |R| × |S|
• R ∩ S = R − (R − S)

५ महत्त्वाचे प्रश्न

Q1. Define degree and cardinality. Find them for the Student table above.

Degree is the number of attributes; cardinality is the number of tuples. Student has 4 attributes (roll, name, city, dept_id) and 4 rows, so degree = 4 and cardinality = 4.

Q2. Differentiate primary key and candidate key.

A candidate key is any minimal set of attributes that identifies each row uniquely; a table may have several (e.g. roll and Aadhaar number). The primary key is the one candidate key chosen by the designer; it cannot be NULL. The remaining candidate keys become alternate keys.

Q3. Write relational algebra and the result for: (a) names of students from Nagpur; (b) Student × Dept size; (c) names with their department names.

• (a) π name (σ city = 'Nagpur' (Student)) → Asha, Neha
• (b) Student × Dept has degree 4 + 2 = 6 and cardinality 4 × 3 = 12
• (c) π name, dname (Student ⋈ Dept) → (Asha, Computer), (Ravi, Physics), (Neha, Computer). Amit has NULL dept_id and Maths has no student, so neither appears.

Q4. Explain the set operations with an example.

Let A = π name (σ city = 'Nagpur' (Student)) = {Asha, Neha} and C = π name (σ roll ≤ 2 (Student)) = {Asha, Ravi}. Then A ∪ C = {Asha, Neha, Ravi}; A ∩ C = {Asha}; A − C = {Neha}; C − A = {Ravi}. State the union-compatibility condition and that duplicates are removed.

Q5. Explain the types of keys and integrity constraints with examples.

Outline: define each key using Student and Dept (roll = primary key, dept_id in Student = foreign key, {roll, name} = super key) → entity integrity (roll cannot be NULL) → referential integrity (Student cannot have dept_id 50 because Dept has no 50; deleting Dept 10 must be blocked or cascaded) → domain constraint (marks between 0 and 100) → why constraints keep data correct.

परीक्षेसाठी टीप

Write the relational algebra expression from inside out: first σ for rows, then ⋈ if two tables are needed, and π last for columns. Show the result table for full marks.

थोडक्यात सारांश

SQL (Structured Query Language) is the standard language for working with relational databases. It is used to create tables, insert and change data, and ask questions (queries). This topic covers SQL command categories, data types, constraints, CREATE, INSERT, UPDATE, DELETE, SELECT with WHERE, ORDER BY, GROUP BY, HAVING, aggregate functions, and simple joins.

महत्त्वाचे मुद्दे

• SQL is not case-sensitive for keywords; statements usually end with a semicolon.
• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE, RENAME — change structure.
• DML (Data Manipulation Language): INSERT, UPDATE, DELETE (and SELECT, sometimes called DQL).
• DCL (Data Control Language): GRANT, REVOKE. TCL (Transaction Control): COMMIT, ROLLBACK, SAVEPOINT.
• Common data types: INT, DECIMAL(p, s), CHAR(n) (fixed length), VARCHAR(n) (variable length), DATE.
• Constraints: PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK, DEFAULT.
• SELECT fetches data; WHERE filters rows; ORDER BY sorts (ASC default, DESC); DISTINCT removes duplicate rows.
• Operators in WHERE: =, <>, >, <, >=, <=, AND, OR, NOT, BETWEEN, IN, LIKE (with % for any characters and _ for one character), IS NULL.
• Aggregate functions: COUNT, SUM, AVG, MAX, MIN.
• GROUP BY groups rows with the same value; HAVING filters groups (WHERE cannot use aggregates).
• DELETE removes rows (can use WHERE, can be rolled back); TRUNCATE removes all rows quickly; DROP removes the whole table structure.
• JOIN combines rows of two tables using a common column. INNER JOIN returns only matching rows; LEFT JOIN returns all rows of the left table.

महत्त्वाच्या संज्ञा

• Query (प्रश्न / क्वेरी) – a request for data from a database.
• Constraint – rule applied to a column to keep data valid.
• Aggregate function – function that returns one value from many rows.
• View – virtual table based on a SELECT query.
• NULL – unknown or missing value (not zero, not blank).

सूत्रे

Sample statements for a table Student:

• Order of clauses: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY

५ महत्त्वाचे प्रश्न

Q1. Differentiate DELETE, TRUNCATE and DROP.

DELETE removes selected rows using WHERE and is DML. TRUNCATE removes all rows but keeps the table structure; it is DDL and faster. DROP removes the entire table, including its structure.

Q2. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates like COUNT or AVG.

Q3. Write queries on Student(RollNo, Name, City, Marks): (a) names of students with marks between 60 and 80; (b) number of students in each city; (c) cities with average marks above 70.

• (a) SELECT Name FROM Student WHERE Marks BETWEEN 60 AND 80;
• (b) SELECT City, COUNT(*) FROM Student GROUP BY City;
• (c) SELECT City, AVG(Marks) FROM Student GROUP BY City HAVING AVG(Marks) > 70;
• BETWEEN includes both 60 and 80.

Q4. Write queries: (a) students whose names start with 'S'; (b) highest marks; (c) list students in alphabetical order.

• (a) SELECT * FROM Student WHERE Name LIKE 'S%';
• (b) SELECT MAX(Marks) FROM Student;
• (c) SELECT * FROM Student ORDER BY Name ASC;
• Names with 'a' as second letter: WHERE Name LIKE '_a%'

Q5. Explain INNER JOIN with an example using Student(RollNo, Name) and Result(RollNo, Subject, Marks).

• Matches rows where RollNo is equal in both tables.
• Students without any result row are not shown.
• LEFT JOIN would show all students, with NULL for missing results.
• RollNo in Result is a foreign key referring to Student.

परीक्षेसाठी टीप

Write SQL keywords in capitals and end every query with a semicolon. Practise GROUP BY with HAVING — it is the most common query question.

थोडक्यात सारांश

Real questions often need data from more than one table. SQL joins combine rows of two tables using a related column, subqueries put one query inside another, and views save a query as a virtual table. This topic covers inner, left, right, full outer, cross and self joins, aggregate functions with GROUP BY and HAVING, single-row and multi-row subqueries (IN, EXISTS, ANY, ALL), and creating, using and dropping views, with outputs.

महत्त्वाचे मुद्दे

• A join combines rows from two tables based on a condition, usually primary key = foreign key.
• INNER JOIN: only rows that match in both tables.
• LEFT (OUTER) JOIN: all rows of the left table; unmatched right-side columns become NULL. RIGHT JOIN is the mirror image.
• FULL OUTER JOIN: all rows of both tables, NULL where no match.
• CROSS JOIN: Cartesian product, every row with every row (m × n rows).
• SELF JOIN: a table joined with itself using aliases, e.g. employee and manager in one table.
• Aggregate functions: COUNT, SUM, AVG, MIN, MAX. GROUP BY forms groups; HAVING filters groups (WHERE filters rows before grouping).
• Subquery: a query inside another, written in brackets. The inner query usually runs first.
• Single-row subquery uses =, <, >; multi-row subquery uses IN, ANY, ALL; EXISTS checks whether the inner query returns any row.
• Correlated subquery: the inner query uses a column of the outer query and runs once for each outer row.
• View: a stored SELECT that behaves like a virtual table; it stores no data of its own. CREATE VIEW … AS SELECT …, DROP VIEW ….
• Views give security (show only some columns), simplicity (hide complex joins) and logical data independence.

महत्त्वाच्या संज्ञा

• Alias – short temporary name for a table or column (Student s).
• Outer join – join that also keeps unmatched rows.
• Nested query – another name for a subquery.
• Virtual table – a view; its rows come from base tables when queried.
• Base table – a table that actually stores data.

सूत्रे

Tables: Student(roll, name, city, dept_id) = (1, Asha, Nagpur, 10), (2, Ravi, Pune, 20), (3, Neha, Nagpur, 10), (4, Amit, Bhandara, NULL). Dept(dept_id, dname) = (10, Computer), (20, Physics), (30, Maths). Marks(roll, marks) = (1, 82), (2, 67), (3, 91), (4, 58).

• Order of clauses: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …

५ महत्त्वाचे प्रश्न

Q1. Differentiate WHERE and HAVING.

WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates, e.g. HAVING COUNT(*) > 1.

Q2. What is a view? Give two advantages.

A view is a virtual table defined by a SELECT query; it stores no data and shows current data from the base tables each time it is used. Advantages: it hides sensitive columns (security) and saves a complex query so users can write a simple SELECT * FROM view.

Q3. Write queries and outputs: (a) each student's name with department name (inner join); (b) all students even without a department.

• Output: (Asha, Computer), (Ravi, Physics), (Neha, Computer)

• Output: the same three rows plus (Amit, NULL)

Q4. Find the names of students who scored above the class average, and the departments with more than one student.

• Average = (82 + 67 + 91 + 58) ÷ 4 = 298 ÷ 4 = 74.5. Rolls above it: 1 (82) and 3 (91). Output: Asha, Neha.

• Output: (Computer, 2)

Q5. Explain views with syntax and an example; can a view be updated?

• SELECT * FROM TopStudents; gives (Asha, 82), (Neha, 91).
• DROP VIEW TopStudents; removes the view, not the data.
• A simple view on one table without GROUP BY, DISTINCT or aggregates is usually updatable; views with joins or aggregates are generally read-only.

परीक्षेसाठी टीप

Use table aliases and always write the join condition with ON. For subqueries, solve the innermost query first and write its value beside it.

थोडक्यात सारांश

A transaction is a group of database operations that must be done completely or not at all, like transferring money between two accounts. This topic covers the ACID properties, transaction states, problems caused by running transactions at the same time (lost update, dirty read, unrepeatable read), schedules and serializability, lock-based concurrency control with two-phase locking, deadlocks, and basic recovery using logs, COMMIT and ROLLBACK.

महत्त्वाचे मुद्दे

• A transaction is a logical unit of work: it reads and/or writes data items, e.g. read(A), A = A − 500, write(A), read(B), B = B + 500, write(B).
• ACID properties:
• Atomicity – all or nothing; a half-done transfer is undone.
• Consistency – the database moves from one valid state to another (A + B stays the same in a transfer).
• Isolation – concurrent transactions do not see each other's half-done work.
• Durability – once committed, changes survive crashes.
• Transaction states: active → partially committed → committed; or active → failed → aborted (rolled back), then restart or kill.
• SQL commands: COMMIT saves changes, ROLLBACK undoes them, SAVEPOINT marks a point to roll back to.
• Concurrency problems: lost update (one write overwrites another), dirty read (reading uncommitted data), unrepeatable read (same item read twice gives different values), phantom rows.
• A schedule is the order in which operations of several transactions run. A serial schedule runs one transaction completely, then the next.
• A schedule is conflict serializable if it can be turned into a serial schedule by swapping non-conflicting operations. Test: draw a precedence graph; if it has no cycle, the schedule is serializable.
• Two operations conflict if they belong to different transactions, use the same item, and at least one is a write.
• Locks: shared lock (S) for reading — many can hold it; exclusive lock (X) for writing — only one can hold it.
• Two-Phase Locking (2PL): growing phase (only acquire locks) then shrinking phase (only release). It guarantees conflict serializability but can cause deadlock. Strict 2PL holds X locks until commit.
• Deadlock: T1 waits for T2's lock and T2 waits for T1's. Handled by wait-for graph detection and aborting a victim, or by timeouts / wait-die and wound-wait schemes.
• Recovery: the DBMS keeps a log of every change (old and new values). After a crash it redoes committed transactions and undoes uncommitted ones. A checkpoint limits how much log must be read.

महत्त्वाच्या संज्ञा

• Commit – making a transaction's changes permanent.
• Rollback / abort – undoing a transaction's changes.
• Schedule – sequence of operations from concurrent transactions.
• Precedence graph – graph with an edge Ti → Tj when an operation of Ti conflicts with and comes before one of Tj.
• Lock – permission to access a data item.

सूत्रे

• Conflicting pairs: R–W, W–R, W–W on the same item by different transactions (R–R never conflicts)
• Number of serial schedules of n transactions = n!

५ महत्त्वाचे प्रश्न

Q1. State the ACID properties with the example of a bank transfer.

Transfer ₹500 from A to B. Atomicity: if the system fails after debiting A, the debit is undone. Consistency: A + B is the same before and after. Isolation: another transaction cannot see A debited but B not yet credited. Durability: after "transfer successful", the change stays even if power fails.

Q2. Explain the lost update problem with an example.

Balance A = 100. T1 withdraws 50, T2 deposits 20. Interleaving: T1 reads 100, T2 reads 100, T1 writes 50, T2 writes 120. Final A = 120, but the correct result is 100 − 50 + 20 = 70. T1's update is lost because T2 overwrote it using an old value.

Q3. Check whether the schedule S: R1(A), R2(A), W1(A), W2(A) is conflict serializable.

• R1(A) before W2(A): conflict → edge T1 → T2
• R2(A) before W1(A): conflict → edge T2 → T1
• W1(A) before W2(A): conflict → edge T1 → T2
• The graph has a cycle T1 → T2 → T1, so S is not conflict serializable. (This is the lost update pattern.)

Q4. Is S′: R1(A), W1(A), R2(A), W2(A), R1(B), W1(B), R2(B), W2(B) conflict serializable?

• On A: every operation of T1 comes before T2's → T1 → T2
• On B: again T1's operations come first → T1 → T2
• Only edge T1 → T2, no cycle → serializable, equivalent to the serial schedule T1 then T2.

Q5. Explain two-phase locking and how it may lead to deadlock.

Outline: shared and exclusive locks with the compatibility table → growing and shrinking phases, lock point → 2PL ensures conflict serializability → strict and rigorous 2PL avoid cascading rollback → deadlock example: T1 holds X(A) and asks for X(B); T2 holds X(B) and asks for X(A); both wait forever → detection with a wait-for graph and aborting one transaction, or prevention by wait-die/wound-wait.

परीक्षेसाठी टीप

For serializability questions, list every conflicting pair, draw the precedence graph, then state clearly "cycle → not serializable" or "no cycle → equivalent to serial order …".