403 - Final study cards Flashcards
(15 cards)
- What is DBMS? What are the main functions of DBMS (Name minimum of 5 functions)?
A DBMS (Database Management System) is software that stores, manages, and manipulates databases.
Main functions include:
- Data Dictionary Management
- Data Storage Management
- Backup and Recovery
- Data Integrity Management
- Multi-user Access Control
- Define each of the following terms:
a. Data: Raw facts and figures with no specific meaning (e.g., 85, “blue”).
b. Field: A single data item or attribute in a record (e.g., “Student_Name”).
c. Record: A collection of related fields representing one entity (e.g., a student’s full profile).
d. Information: Processed data that is meaningful and useful (e.g., “It is 32°C today”).
- Create “view” for the table created by following SQL statement.
CREATE VIEW Student_View AS
SELECT Student_Name, Student_FamilyName
FROM Student
WHERE Age >= 18;
- What is a database? Explain the difference between a database and a data warehouse.
A database is an organized collection of structured data used by an organization.
Database vs Data Warehouse:
- A database supports real-time transaction processing;
- A data warehouse is optimized for analytical querying and long-term data analysis.
- Define the term ‘Normalization’ in the context of databases.
Normalization is the process of organizing data in a database to reduce redundancy and ensure data integrity by applying a series of rules (1NF to 5NF).
- What is a distributed database?
A distributed database is a database that is stored across multiple physical locations, often on different servers or sites, but managed as a single system.
- What are the differences between a Primary Key and a Foreign Key?
- Primary Key uniquely identifies each record in a table and cannot be NULL.
- Foreign Key is a reference to the primary key in another table and helps maintain referential integrity.
- What is SQL in relation to databases?
SQL (Structured Query Language) is a standard language used to create, manage, and manipulate relational databases.
- Define the differences between DELETE and TRUNCATE in SQL.
- DELETE removes specific rows based on a condition and can be rolled back.
- TRUNCATE removes all rows from a table quickly and cannot be rolled back.
- What is ACID in the context of database transactions?
ACID ensures reliable processing of database transactions:
- Atomicity: All steps complete or none.
- Consistency: Maintains valid state.
- Isolation: Transactions don’t interfere.
- Durability: Changes persist even after failure.
- Explain the purpose of an Index in a database.
An index improves the speed of data retrieval operations on a database table by allowing faster lookups.
- List any two advantages of an index in a database.
- Faster query performance.
- Improved sorting and filtering capabilities.
- Generalization is a process of finding common attributes and relations of a number of entities and defining a common super class for them.
Correct. For example, “Student” and “Employee” may be generalized as “Person” if they share attributes like name and address.
- What is the difference between Referential Integrity and Data Integrity?
- Referential Integrity ensures relationships between tables remain consistent using primary and foreign keys.
- Data Integrity ensures the accuracy, validity, and consistency of all data in the database.
- Specialization is the process of defining a set of subclasses of an entity type where each subclass contains all the attributes and relationships of the parent entity and may have additional attributes and relationships which are specific to itself.
Correct. For instance, from the “Person” entity, you can specialize into “Student” and “Employee” entities with unique attributes.
