Unit 1: Introduction and Conceptual Modeling Flashcards
What is a database?
An organized collection of interrelated data designed for efficient storage, retrieval, and modification.
Example: A university database containing students, courses, and enrollment records.
What is a Database Management System (DBMS)?
Software that manages databases by providing tools to store, retrieve, update, and manage data.
Examples: Oracle, MySQL, SQL Server, PostgreSQL.
What are the advantages of using a DBMS?
- Reduced data redundancy and inconsistency
- Improved data sharing among multiple users
- Enforced data integrity and security
- Better data backup and recovery procedures
What are the main components of a Database System Architecture?
- Storage Manager
- Query Processor
- Transaction Management
What functions does the Storage Manager perform?
- File Manager
- Buffer Manager
- Authorization & Integrity Manager
- Transaction Manager
What is the role of the Query Processor?
- DDL Interpreter
- DML Compiler & Query Optimizer
- Query Evaluation Engine
Define the Relational Model.
Data is stored in tables (relations) with rows (tuples) and columns (attributes).
What does an Entity-Relationship (ER) Model represent?
- Entities
- Attributes
- Relationships
What symbols are used in ER Diagrams?
- Rectangle: Entity set
- Ellipse: Attribute
- Diamond: Relationship
What are some examples of Database Applications?
- Sales
- HR
- Inventory
- Accounting
- Banking
- Airlines
- Telecommunications
- Web services
What does SQL stand for?
Structured Query Language.
List the basic SQL operations.
- Create
- Read
- Update
- Delete
What is Data Definition Language (DDL)?
Commands that define or modify the structure of database objects.
What does the SQL command CREATE do?
Defines new tables/objects.
What does the SQL command ALTER do?
Modifies table structure.
What is the purpose of the DROP command in SQL?
Removes a table (structure and its data).
What does the TRUNCATE command do?
Deletes all rows and frees space without logging individual row deletions.
What is the Data Manipulation Language (DML)?
Commands to modify the data.
What is the primary command in Data Query Language (DQL)?
SELECT.
What is the purpose of Data Control Language (DCL)?
Manage access rights.
What is the function of Transaction Control Language (TCL)?
Manage transactions.
What are SQL constraints?
Enforce rules at the column/table level.
What does the NOT NULL constraint enforce?
No null values allowed.
What is a PRIMARY KEY in SQL?
Combination of NOT NULL and UNIQUE; uniquely identifies rows.
What does Referential Integrity ensure?
Foreign key values must match primary key values in the related table or be null.
What is the purpose of normalization in database design?
Refine the design to reduce redundancy and avoid anomalies.
What are the normal forms in database normalization?
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
- Boyce-Codd Normal Form (BCNF)
What is a Super Key?
A set of attributes that can uniquely identify a row.
What is the principle of least privilege?
Grant minimal privileges to users.
What is the function of a Data Dictionary?
Stores metadata about the database.
What is the role of the Buffer Manager?
Responsible for managing main memory buffers that hold copies of disk blocks.
What are B-Trees used for?
Database indices because they reduce the number of disk accesses.
What does the INSERT command do in SQL?
Adds new rows of data to a table.
What is the purpose of the UPDATE command in SQL?
Modifies existing data in a table.
What is the purpose of the DELETE command in SQL?
Removes rows based on a condition.
What is a view in SQL?
A virtual table created from one or more tables.
What is a transaction in database management?
A sequence of operations performed as a single logical unit of work.
What is the purpose of the COMMIT command?
Saves changes permanently.
What is the purpose of the ROLLBACK command?
Reverses uncommitted changes.
What is a fill-in-the-blank: The __________ is a self-balancing tree data structure that keeps data sorted.
B-Tree.
