Database Management System (DBMS)
– Combined Notes
Two Marks Questions
1. 1. What are the main stages of the database system development lifecycle, and why are
they important?
- Requirements Collection
- Conceptual Design
- Logical Design
- Physical Design
- Implementation
- Testing and Evaluation
- Operation and Maintenance
Each stage ensures correct, efficient, and reliable database design.
2. 2. List any two advantages of database systems.
- Reduces data redundancy
- Ensures data consistency
3. 3. What is a view in SQL?
A view is a virtual table based on the result of an SQL query.
4. 4. Can views be updated in SQL?
Yes, if the view is based on a single table without GROUP BY or aggregate functions.
5. 5. State the different types of integrity constraints used in designing a relational database.
- Domain Integrity
- Entity Integrity
- Referential Integrity
- User-defined Integrity
6. 6. List the applications of DBMS.
- Banking
- Airlines
- Universities
- Telecommunications
7. 7. What are the disadvantages of file processing systems?
- Data redundancy
- Lack of security
- Difficult to access data
- Data isolation
8. 8. What are the different types of joins in SQL?
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
9. 9. Assign the entities and relationships for student course registration
Entities: Student, Course
Relationship: Registers
10. 10. What is a primary key and foreign key?
- Primary Key: Uniquely identifies a record
- Foreign Key: Refers to primary key of another table
11. 11. Why null values might be introduced into the database?
To represent unknown, missing, or not applicable values.
12. 12. Discuss the roles of users in a database environment.
- DBA: Manages the database
- Developers: Design applications
- End-users: Use the applications
Big Questions (Detailed Answers)
13. 1. Describe the process of creating an Entity-Relationship (ER) model and its role in database
design
- Understand user requirements
- Identify entities (e.g., Student)
- Define attributes (e.g., StudentID, Name)
- Determine relationships (e.g., Enrolls)
- Draw ER diagram
- Role: Visualizes structure, improves communication, reduces errors
14. 2. How do requirements collection and analysis impact database design?
- Helps capture user needs accurately
- Avoids rework and miscommunication
- Methods: Interviews, surveys, observations, document analysis
- Guides design decisions and ensures relevance
15. 3. Define domain integrity and its significance
- Ensures column values are valid and consistent
- Enforced using data types, CHECK constraints, NOT NULL
- Prevents incorrect data (e.g., Age < 0)
16. 4. Differences between table and relation
- Table: Physical implementation
- Relation: Theoretical concept
- Table may allow duplicates; relations do not
- Relations follow stricter rules
17. 5. Challenges in database design and how tools help
- Challenges: Redundancy, consistency, complex structure
- Tools like ER/UML diagrams help visualize and plan
- Improves communication with stakeholders
18. 6. Explain referential integrity and how it is maintained
- Ensures foreign keys match primary keys
- Prevents orphan records
- Enforced via FOREIGN KEY constraint
- Example: [Link] references [Link]
Question 7 & 8 (SQL Commands and DB Design)
7. Using DML Commands:
i) Insert sample data into the following tables:
Subjects:
INSERT INTO Subjects VALUES ('OS101', 'Operating Systems');
INSERT INTO Subjects VALUES ('AI102', 'AI');
INSERT INTO Subjects VALUES ('TC103', 'Theory of Computation');
Questions:
INSERT INTO Questions VALUES (1, 'Define OS', 5, 'Easy');
INSERT INTO Questions VALUES (2, 'Explain AI types', 10, 'Medium');
INSERT INTO Questions VALUES (3, 'Define Turing Machine', 8, 'Hard');
INSERT INTO Questions VALUES (4, 'Explain CPU Scheduling', 10, 'Medium');
INSERT INTO Questions VALUES (5, 'Define NP problems', 7, 'Hard');
QuestionPapers:
INSERT INTO QuestionPapers VALUES (1, 'OS101', '2024-01-01', 30);
INSERT INTO QuestionPapers VALUES (2, 'AI102', '2024-02-01', 35);
ii) SQL to get Medium difficulty questions:
SELECT * FROM Questions WHERE DifficultyLevel = 'Medium';
iii) Update Hard questions marks by 10%:
UPDATE Questions SET Marks = Marks + (Marks * 0.10) WHERE DifficultyLevel = 'Hard';
iv) Delete questions with 0 marks:
DELETE FROM Questions WHERE Marks = 0;
8. Create Database and Tables with Constraints:
CREATE TABLE Subjects (
SubjectID INT PRIMARY KEY,
SubjectName VARCHAR(50),
Code VARCHAR(10)
);
CREATE TABLE Questions (
QuestionID INT PRIMARY KEY,
QuestionText TEXT,
Marks INT NOT NULL,
DifficultyLevel VARCHAR(10)
);
CREATE TABLE QuestionPapers (
PaperID INT PRIMARY KEY,
SubjectID INT,
ExamDate DATE,
TotalMarks INT,
FOREIGN KEY (SubjectID) REFERENCES Subjects(SubjectID)
);
iii) Add TopicID with Foreign Key:
ALTER TABLE Questions ADD TopicID INT;
ALTER TABLE Questions ADD CONSTRAINT fk_topic FOREIGN KEY (TopicID) REFERENCES
Topics(TopicID);
iv) Trigger to update total marks:
CREATE TRIGGER UpdateTotalMarks
AFTER INSERT ON Questions
FOR EACH ROW
BEGIN
UPDATE QuestionPapers
SET TotalMarks = TotalMarks + [Link]
WHERE SubjectID = (SELECT SubjectID FROM Subjects WHERE SubjectID = [Link]);
END;