2) Introduction to DBS Flashcards
1
Q
What is a traditional file based system?
A
A method where each program stores its own data in files, usually text files or binary files, without a central database.
2
Q
What are the flaws of traditional file based systems?
A
- Data redundancy
- Data inconsistency
- Poor data sharing
- No security
- Difficult maintenance
- Chaos, inefficiency, errors, high maintenance costs
3
Q
What does data redundancy refer to in traditional file based systems?
A
Same data might be stored in multiple files.
4
Q
What does data inconsistency mean?
A
There exist different versions of the same data.
5
Q
What is meant by poor data sharing?
A
It is hard to access and update data across systems.
6
Q
True or False: Traditional file based systems provide high security.
A
False
7
Q
What are the key challenges addressed by databases?
A
- Structured data representation
- Efficient data access
- Integrity and consistency
- Secure access
- Concurrent usage
8
Q
What is a database (DB)?
A
A collection of related data.
9
Q
What does a database specify?
A
The data types, structures and constraints of data to be stored.
10
Q
What is a Database Management System (DBMS)?
A
A collection of software programs for defining, constructing, and manipulating a database.
11
Q
What are the three main functions of a DBMS?
A
- Defining
- Constructing
- Manipulating
12
Q
Fill in the blank: A DBMS is responsible for _______ what data will be stored and what are the types, constraints, structures.
A
[defining]
13
Q
Fill in the blank: The DBMS is responsible for physically _______ data on disk.
A
[constructing]
14
Q
What does manipulating data in a DBMS involve?
A
- Querying
- Modifying data (insert, update, delete)
15
Q
What is a database system (DBS)?
A
Database (DB) + software (DBMS)
A database system includes both the database and the software that manages it.
16
Q
What does the system catalog (metadata) describe?
A
The structure of the database
Metadata is stored in the system catalog and is part of the DBMS.
17
Q
What are the components included in the system catalog?
A
- Table names
- Column names
- Data types
- Constraints
- Relationships between tables
- Indexes
- Views
- Triggers and stored procedures
Each component plays a crucial role in defining the structure and behavior of the database.
18
Q
What are constraints in a database?
A
- Primary keys
- Foreign keys
- Not null
- Unique
Constraints enforce rules on the data to maintain integrity.
19
Q
What is the purpose of indexes in a database?
A
To optimize searches and make them faster
Indexes provide a way to quickly locate data without scanning the entire database.
20
Q
What are views in a database?
A
Virtual tables created by querying one or more tables
Views do not hold data themselves but present data as if it were a table.
21
Q
What are triggers and stored procedures?
A
Automatic actions based on conditions or pre-written logic
They help manage the database by executing actions automatically or running complex tasks.
22
Q
What does constructing a database refer to?
A
Storing the data itself
This is the foundational step in creating a functional database.
23
Q
What does manipulating the database involve?
A
- Querying to retrieve data
- Updating values
Manipulation allows users to interact with the data stored in the database.
24
Q
What is an example of querying a database?
A
SELECT
The SELECT statement is used to retrieve specific data from a database.
25
What is an example of updating a database?
UPDATE
## Footnote
The UPDATE statement is used to change existing values in the database.
26
What does it mean for a DBMS to be modular and general-purpose?
It can manage various types of databases and is not limited to specific applications
## Footnote
This flexibility allows the DBMS to be used across different data management scenarios.
27
In layman’s terms, what is a DBMS applicable to?
Any database that I create
## Footnote
A DBMS can work with any data as long as it is combined with the software that gathers metadata.
28
Who are end users in the context of databases?
People that query the data via applications or interfaces.
29
What is the role of a DBA (database administrator)?
Manage, secure, and optimize databases.
30
What do database designers do?
Design the logical structure (schema) of the database.
31
What is the function of system analysts in database management?
Talk to users to understand system needs and plan how the system should work.
32
What do application programmers do in relation to databases?
Write code to build applications that interact with the database using DB APIs.
33
What is SQL?
A language used to communicate with a database.
34
True or False: MySQL and PostgreSQL are types of database management system software.
True.
35
Why are databases better than files?
They provide structured, efficient, and scalable data management with built-in support for concurrency, security, and data integrity.
36
What role does a DBS play in data management?
Acts like a middleman that simplifies data handling by managing storage and processing tasks.
37
Fill in the blank: A _______ is a dynamic collection of data that changes frequently.
Database
38
Fill in the blank: A database schema is a _______ description of the database.
Structural
39
What does a database schema define?
Data types, structures, and relationships.
40
True or False: The database schema is dynamic and changes frequently.
False.
41
What type of information is stored separately in the database schema?
Metadata about the structure.
42
What does metadata describe in a database?
Data structures, schemas, and constraints
## Footnote
Metadata is stored in a system catalog managed by DBMS.
43
What is a key advantage of DBMS software?
Modularity
## Footnote
It is generic and not tied to specific applications or data structures.
44
List three advantages of databases over file-based storage.
* Structured access
* Security
* Multi-user support
* Data protection
## Footnote
File-based storage includes text files (CSV, TXT, JSON, XML), spreadsheets, and log files.
45
What are the two common approaches for scaling databases?
* Vertical scaling (scale up)
* Horizontal scaling (scale out)
## Footnote
Vertical scaling adds more CPU/RAM to a single machine, while horizontal scaling distributes data across multiple servers.
46
Name three methods to keep databases fast as they grow.
* Indices
* Query optimization
* Caching mechanisms
## Footnote
Indices speed up queries, query optimization reduces processing time, and caching stores frequent results.
47
What does a data model define?
Data structures, operations, and constraints
## Footnote
It is used for defining the schema (DDL) and accessing and updating the DB (DML).
48
What is the least detailed type of data model?
Conceptual Data Model
## Footnote
It shows entities and relationships.
49
What does the Logical Data Model include?
* Entities
* Relationships (through foreign keys)
* Attributes
* Primary keys
## Footnote
It provides more detail than the conceptual model.
50
What does the Physical Data Model show?
How the data will be stored in a specific database
## Footnote
Includes data types, indexes, and table names from the actual DB.
51
What is data independence?
The capability to change the DB schema at one level without affecting the schema at a higher level
## Footnote
This includes physical and logical data independence.
52
What is physical data independence?
Change in physical schema does not affect logical structure
## Footnote
Examples include changes in indexing methods or storage devices.
53
Define logical data independence.
Change in logical schema does not impact external view
## Footnote
This refers to the table structure not affecting querying results.
54
What is Physical Data Independence?
Schema is still valid when storage structure is changed
## Footnote
It allows for changes in storage systems (e.g., hash or B-Tree) without affecting query results.
55
What is Logical Data Independence?
Application programs should not need to change when the logical structure of the database changes
## Footnote
Example: Adding a column or splitting a table should not affect existing queries if logical data independence is strong.
56
How can you compare physical and logical data independence?
Physical data independence is like reorganizing a restaurant kitchen; logical data independence is like updating the menu
## Footnote
Customers still receive food the same way, and can still order familiar dishes despite changes.
57
What is the 3-Schema Architecture?
A model that helps achieve data independence by separating physical storage, logical structure, and external views of the database
## Footnote
It ensures that changes at one level do not affect other levels.
58
What are the three levels in the 3-Schema Architecture?
* Physical Layer: describes how data is physically stored
* Logical Layer: describes logical structure of the data
* External View Layer: how different users interact with the data
59
What does the Physical Layer in the 3-Schema Architecture describe?
Describes how data is physically stored in the database
## Footnote
Examples include disk storage and file organization.
60
What does the Logical Layer in the 3-Schema Architecture encompass?
Describes logical structure of the data, including organization and relationships
## Footnote
Defines data structure in a way users can understand.
61
What is the External View Layer in the 3-Schema Architecture?
How different users or applications interact with the data
## Footnote
Different teams may see different data based on their needs.
62
What is a conceptual model?
Describes abstract concepts to define how data is organized, focusing on relationships between entities
## Footnote
It does not detail implementation or storage.
63
What is the Entity-Relationship (ER) Model?
A graphical way to represent data’s entities and their relationships
## Footnote
Example: An ER diagram may show Student and Course entities with a relationship called Enrolls In.
64
What does the Logical Data Model (SQL) describe?
Describes the logical structure of the database, including tables, columns, and data types
## Footnote
It uses a specific query language.
65
What is a Physical (Data) Model?
Describes the physical storage and implementation details of how data will be stored
## Footnote
Includes file formats, indexing strategies, and storage structures.
66
What is a database schema?
A blueprint or plan for how the data is organized in a database.
67
What does a database hold?
A collection of data that changes over time, holding real world information.
68
How does a schema differ from a database?
Schema is the structure/design of the database and does not change often.
69
What is a Data Model?
A framework that helps define and work with a database.
70
What are the two main parts of a Data Model?
1. Defining the Schema (DDL)
2. Accessing & Updating the DB (DML)
71
What does DDL stand for?
Data Definition Language.
72
What are three common DDL commands?
* CREATE TABLE
* ALTER TABLE
* DROP TABLE
73
What does DML stand for?
Data Manipulation Language.
74
What are four common DML commands?
* INSERT INTO
* UPDATE
* DELETE
* SELECT
75
What is the primary purpose of DDL?
To design the database structure.
76
What is the primary purpose of DML?
To work with the data inside the database.
77
Is SQL a data model?
No, SQL itself is NOT a data model.
78
What is the most important data model today?
The relational model.
79
How is the schema organized in the relational model?
Using tables (relations).
80
What do columns define in a relational model?
The type of data stored in a table.
81
What do tuples represent in a relational model?
Individual records in a table.
82
What are domains in the context of a relational model?
The allowed values for an attribute (e.g., age should be int).
83
What are keys in a relational model?
Unique identifiers; FK is a key that links to another column’s PK.
84
What is metadata in relation to a database?
Schema is separate from the data.
85
Fill in the blank: A data model is a framework that defines how data is ________.
[structured, stored, and related in a database]
86
What are common data model examples by structure type?
* Relational data model
* Hierarchical model
* Network model
* Document model
87
Which data model is used in NoSQL databases like MongoDB?
Document model.
88
What is SQL?
SQL is the standard language to interact with relational databases.
89
What does SQL allow you to do?
It allows the creation of tables, insertion of data, querying of data, and to update or delete data.
90
Do most people write SQL by hand?
No, most people use applications with graphical user interfaces (GUI) that hide the SQL behind the scenes.
91
What is embedded SQL?
SQL commands written inside a programming language using a special interface.
92
What is an API?
API (application programming interface) is a set of rules that lets the program send SQL commands to the database and get results back.
93
What are ODBC and JDBC?
ODBC and JDBC are used to connect apps with databases using SQL.
94
Name three widely used Database Management Systems (RDBMS).
* Oracle
* PostgreSQL
* MySQL
95
What is Integrity in the context of databases?
Integrity means that the database should never mess up the data or create errors that go against the real-world situation it represents.
96
What is Concurrency Control?
Concurrency Control ensures that multiple users using the database at the same time do not interfere with each other.
97
What is Fail-Safe Operation?
It ensures that if the system crashes, the database remains correct and does not get damaged.
98
What do ACID transactions stand for?
- Atomicity: transaction is all or nothing (everything in transaction happens or nothing)
- Consistency: transaction brings the DB from one valid state to another valid state (prevents DB from ending up in a broken or invalid state after a crash or error)
- Isolation: transactions run independently of each other (errors in one transaction don’t affect others)
- Durability: once a transaction is committed, the changes are permanently saved, even if there’s a crash
99
What is the expected response time for users on websites?
Users expect websites to give answers in under 3 seconds.
100
What is the Hardware Life Cycle?
The Hardware Life Cycle is more or less 3 years.
101
What is the Software Life Cycle?
The Software Life Cycle is more or less 5 years.
102
What is the Data Life Cycle?
The Data Life Cycle is about 30 years.
103
Fill in the blank: The database must handle hundreds of users working at the same time, processing thousands of operations per second, ensuring _______.
[Efficiency]
104
True or False: Data Security is not a critical concern for databases.
False
