RBD Flashcards
1
Q
What year did Univac present the magnetic tape as a means to store data?
A
1951
2
Q
Which company introduced the magnetic hard drive in 1956?
A
IBM
3
Q
What was the first DBMS developed in 1961?
A
Integrated Data Store IDS
4
Q
What data model did the Information Management System (IMS) introduce? 1965 - 70
A
Hierarchical data model
5
Q
Who is credited with the relational data model in 1970?
A
Edgar Codd
6
Q
What standard was established by CODASYL in 1971?
A
The standard for the network data model
7
Q
Who developed the entity-relationship model in 1976?
A
Peter Chen
8
Q
What was the first relational database management system developed by IBM in 1973?
A
System R
9
Q
Which company marketed the first commercial version of a relational database management system in 1979?
A
Relational Software (later Oracle)
10
Q
When was the first standard of SQL established?
A
1987
11
Q
What does ODMG 2.0 refer to?
A
The standard for object-oriented databases established in 1997
12
Q
List three aspects that have extended databases since the nineties.
A
- Multi-tier architectures
- Data warehouses
- GIS (Geographical Information Systems)
13
Q
What is a relation in the context of relational databases?
A
Any subset of the Cartesian product
14
Q
Fill in the blank: A relation is defined as a subset of _______.
A
Cartesian product
15
Q
What is the term for a table in a database?
A
Entity
16
Q
What do attributes describe in a database entity?
A
Properties of the object which is a name of entity
17
Q
What is one requirement for attributes in a database?
A
Attributes should not generate redundancy
18
Q
What is the First Normal Form condition regarding values?
A
Values must be atomic
19
Q
True or False: Each entity in a database should have a unique identifier.
A
True
20
Q
What is an example of an operation that can be performed by a database application?
A
Finding all staff members who participated in project a23
21
Q
What should attributes not change over time?
A
Their value
22
Q
List two examples of entities defined by attributes.
A
- Customer
- Product
23
Q
Fill in the blank: In each entity, we have an attribute _______ allowing to identify unique records.
A
s
24
Q
What is the attribute that describes a property of the object in a database?
A
Attribute
25
What does the term 'instance of the entity' refer to in a database?
Record (row in table)
26
Model of entities
database
27
Entity
table
28
Attribute
column
29
Instance of the entity
record=row in table
30
database functionalities
what a computer application can do, illustrated with examples like finding doctors or changing book categories. It also clarifies what database functionalities are NOT, such as business rules, user duties, data content, entities, or attributes.
31
Entities are defined by their attributes.
32
Attributes should describe the entity they are defined for.
33
Attributes should not change in time.
34
The intersection of a column and a row must be atomic.
35
Each entity should have a unique identifier.
In each entity we have an attribute /s allowing to identify unique records
36
First Normal Form requirement is necessary
37
A set of one or more columns whose values are also values of the primary key or a unique key of the related table (it can be the same table). The values of these columns are interpreted as pointers to the rows of the related table.
foreign key
38
A correctness condition for the data in a database.
integrity constraint
39
A subset (may be a singleton) of the columns of a table such that the values in these columns uniquely identify the row of this table.
key
40
The pseudo-value which means that the data is missing.
Null
41
The distinguished key which is used to identify objects.
primary key
42
An integrity constraint which states that each value of a foreign key is either Null or occurs also in the apropriate column of the associated primary (or unique) key.
referential integrity constraint
43
A two-dimensional structure which consists of rows and columns. At the intersection of a row and a column there is only one atomic data item. A row stores a record of data on an object (e.g. a person or a company) or a relationship between objects. Each column contains a set of atomic data items which describe one of the attributes of an object (e.g. the name of the company or the last name of a person).
table
44
A key that is not primary.
unique key
45
A virtual table created for users. It is defined on the logical level and used on the user level. If it is physically saved in the form of a relational table, we call it a materialized view.
view
46
Client (user interface) sends requests to the server
Server (DBMS) processes requests and manages data
Improves performance, security, and data sharing
Client-Server Architecture (Databases)
47
Data is valuable to a company, like employees or equipment
Needs to be managed and protected
Information is data with meaning
Data as an Asset
48
Manages data and information within an organization
Databases are a core component
Supports business processes
Information System
49
Stores metadata (data about data)
Contains definitions of tables, columns, constraints, etc.
Essential for database management
Catalog (Data Dictionary)
50
Used for entity-relationship diagrams
Solid line: Identifying relationship
Dashed line: Non-identifying relationship
Black circle: Detail entity
Hollow diamond: Optional relationship
IDEF1X Notation
51
Entity-relationship diagram notation
Entity: Rectangle
Attribute: Circle
Relationship: Diamond
Chen's Notation
52
Super-entity (general) and sub-entities (specific)
Represents "is-a" relationship (inheritance)
Discriminator attribute indicates subcategory
Subcategories (Entity-Relationship)
53
Represents data with parent-child relationships
Can use recursive relationships
Dictionary entities for integrity
Hierarchical Data Modeling
54
Temporal entities store historical data
Tracks changes to attributes or relationships
"From" and "To" attributes indicate time range
Modeling Changes in Time
55
Stores the result of a view as a physical table
Improves performance for frequently used queries
Data may become outdated if base tables change
Materialized View
56
User Level: Views for end-users
Logical Level: Tables, indexes, views (conceptual schema)
Physical Level: Files, storage structures (internal schema)
Levels of Abstraction (Relational Database)
57
Changes to the logical schema don't affect applications
Achieved through views
Applications work with views, not base tables
Logical Data Independence
58
Changes to the physical schema don't affect the logical schema
Achieved through abstraction
Allows for flexibility in storage and performance tuning
Physical Data Independence
59
Table
The fundamental building block of a relational database.
Organized into rows (records) and columns (attributes).
60
rows
records
61
columns
attributes
62
Key
A set of columns used to uniquely identify rows within a table.
Essential for maintaining data integrity and relationships.
63
Primary Key
A specific key chosen to be the main identifier for a table.
Must be unique and not null.
64
Unique Key
A key that ensures uniqueness but is not the primary key.
Can allow null values.
65
Foreign Key
A column (or set of columns) in one table that refers to the primary key (or unique key) in another table (or the same table).
Establishes relationships between tables.
66
Null
Represents the absence of a value.
Important for handling missing or unknown data.
67
Integrity Constraint
Rules that ensure the accuracy and consistency of data.
68
Referential Integrity Constraint
A specific integrity constraint that ensures foreign key values are valid.
Guarantees that a foreign key value either matches a primary key value in the related table or is null.
69
View
A virtual table derived from one or more base tables.
Provides a customized view of the data.
Can simplify complex queries and enhance security.
70
Materialized View
A view that is physically stored. This increases speed of retrieval, at the cost of disk space, and data that can become out of date.
71
Tuple Restriction (t∣X)
t∣X(A j )=t(A j ) if A j ∈X (attribute is in the subset).
t∣X(A j ) is undefined if A j ∈ / X (attribute is not in the subset).
72
Key Dependency
X includes a key.
73
Trivial Dependency
A∈X.
74
Dependency with Key Attribute on Right
A∈K (where K is the union of all keys).
75
Boyce-Codd Normal Form (BCNF)
Definition: A relation is in BCNF if for every FD X→A, either:
A∈X (trivial dependency).
X includes a key (key dependency, meaning X is a superkey).
76
Identifying BCNF
Identify all FDs.
Identify all keys.
For each FD, check if it's trivial or a key dependency.
If all FDs meet these criteria, the relation is in BCNF.
77
Third Normal Form (3NF)
Definition: A relation is in 3NF if for every FD X→A, either:
A∈X (trivial dependency).
X includes a key (key dependency).
A∈K (key attribute on the right).
78
Identifying 3NF
Identify all FDs.
Identify all keys and K (union of all keys).
For each FD, check if it's trivial, a key dependency, or if A∈K.
If all FDs meet these criteria, the relation is in 3NF.
79
Non-key Dependency
An FD that is neither trivial nor a key dependency.
80
Partial Dependency
A non-key dependency X→A where X is a subset of a key and A is a non-key attribute.
81
Transitive Dependency
A non-key dependency X→A where A is a non-key attribute, and it is not a partial dependency.
82
Cartesian Product
A×B={(x,y):x∈A,y∈B}
83
Generalized
A1×A2×...×An={(a1,a2,...,an):ai∈Ai}
84
Table = Relation
In RDB, tables represent relations.
85
Schema (R)
A set of attributes: R={A 1 ,A 2 ,...,A n }
86
Domain (Dom(Ai))
The set of possible values for attribute Ai
87
Domain of Relation (Dom(R))
The union of domains of all attributes: Dom(R)=Dom(A 1 )∪Dom(A 2 )∪...∪Dom(A n )
88
Tuple (t)
A mapping from attributes to their domains: t:R→Dom(R), where t(A i )∈Dom(A i ).
89
Tuple Restriction (t|X)
A tuple restricted to a subset of attributes X. t∣X(A j )=t(A j ) if A j ∈X, otherwise undefined.
90
Redundancy
Data repetition, leading to wasted space and update inconsistency.
91
Update Anomaly
Difficulty in updating redundant data; requiring multiple changes.
92
Insert Anomaly
Inability to add new data without also adding unrelated data.
93
Delete Anomaly
Unintended loss of related data when deleting a record.
94
Types of FDs
Trivial: A∈X (e.g., NAME, SURNAME → NAME).
Key Dependency: X includes a key (e.g., {A,C} includes K1).
Key Attribute on Right: A is part of a key (e.g., D → C, if C is part of a key).
95
Superkey
A set of attributes that uniquely identifies tuples in a relation.
96
Key
A minimal superkey (no subset is also a superkey).
97
K (Set of Key Attributes)
The union of all keys.
98
Functional Dependency (FD)
For a relation schema R, a functional dependency X → A holds if for any two tuples in a relation instance of R, if they have the same values for all attributes in X, then they must also have the same value for attribute A.
99
Trivial Dependency
A functional dependency X → A where A is a subset of X. These always hold.
100
Key Dependency
A functional dependency X → A where X is a superkey (includes a key).
101
Boyce-Codd Normal Form (BCNF):
A relation is in BCNF if for every non-trivial functional dependency X → A, X is a superkey. In simpler terms, the left side of every non-trivial FD must be a superkey.
102
Third Normal Form (3NF):
A relation is in 3NF if for every non-trivial functional dependency X → A, at least one of the following holds:
X is a superkey.
A is a key attribute (belongs to any key).
103
Non-key Dependency
A functional dependency that is neither trivial nor a key dependency. These are the ones that can violate BCNF.
104
Partial Dependency
A non-key dependency X → A where A is a non-key attribute, and X is a proper subset of some key. (This relates to 2NF, which was not the focus of this lecture but is important context).
105
Transitive Dependency
A non-key dependency X → A where A is a non-key attribute, X is not a superkey, and there exists a non-key attribute B such that X → B and B → A (and B does not functionally determine X). These can violate 3NF.
106
SQL (Structured Query Language)
The standard language for accessing and manipulating data in relational databases.
107
Relational Database
A database that organizes data into tables with relationships between them.
108
Tables
Collections of data organized into rows and columns.
109
Views
Virtual tables based on the result of a stored query.
110
SQL in Programming Languages
SQL can be embedded and used within various programming languages (C, C++, Java, etc.).
111
4GL (4th Generation Language)
Programming environments (like Oracle Forms, MS Access) that can automatically generate client applications, sometimes reducing the need for traditional coding.
112
SQL Statement Categories:
DML (Data Manipulation Language): Used for managing data within tables (e.g., SELECT, INSERT, UPDATE, DELETE).
DDL (Data Definition Language): Used for defining the structure of the database (e.g., CREATE TABLE, ALTER TABLE, DROP TABLE).
DCL (Data Control Language): Used for controlling access and permissions within the database (e.g., GRANT, REVOKE).
DQL (Data Query Language): Used for retrieving data from the database (primarily the SELECT statement). This lecture focuses on DQL.
113
DML (Data Manipulation Language)
Used for managing data within tables (e.g., SELECT, INSERT, UPDATE, DELETE).
114
DDL (Data Definition Language)
Used for defining the structure of the database (e.g., CREATE TABLE, ALTER TABLE, DROP TABLE).
115
DCL (Data Control Language)
Used for controlling access and permissions within the database (e.g., GRANT, REVOKE).
116
DQL (Data Query Language)
Used for retrieving data from the database (primarily the SELECT statement). This lecture focuses on DQL.
117
Database Schema
The structure of the database, including the tables, their columns, and relationships.
118
SELECT Statement
The fundamental statement for retrieving data.
SELECT column_name1[, column_name2, ...]
119
FROM
Specifies the table(s) from which to retrieve data.
FROM table_name [alias]
120
WHERE
Filters the rows based on specified conditions.
WHERE condition
121
*: Selects all columns from the specified table(s).
You can use expressions involving columns (e.g., price * 0.2).
||: The concatenation operator (used to combine strings).
AS alias_name: Renames the output column with alias_name.
122
DISTINCT: (Optional)
Returns only unique rows, eliminating duplicates.
SELECT DISTINCT column_name
FROM table_name;
123
ORDER BY: (Optional)
Sorts the result set based on specified columns. It's always the last clause.
ORDER BY column_name1 [ASC|DESC][, column_name2 [ASC|DESC], ...]
124
IS [NOT] NULL
Checks if a value is NULL (missing).
WHERE column_name IS NULL
WHERE column_name IS NOT NULL
125
[NOT] BETWEEN value1 AND value2
Checks if a value is within a specified range (inclusive).
WHERE column_name BETWEEN 2 AND 4
WHERE column_name NOT BETWEEN 1500 AND 1750
126
[NOT] LIKE 'pattern'
Checks if a string matches a specified pattern.
%: Matches any sequence of zero or more characters.
_: Matches any single character.
WHERE surname LIKE 'K%' -- Starts with 'K'
WHERE p_name LIKE '_____' -- Consists of 5 characters
WHERE surname LIKE 'K%s' -- Starts with 'K' and ends with 's'
127
[NOT] IN (value1, value2, ...)
Checks if a value is present in a specified list.
WHERE Job IN ('Manager', 'Director')
WHERE Job NOT IN ('Clerk', 'Analyst')
128
NVL(column_or_expression, default_value): Replaces NULL values in a column or expression with the specified default_value.
SELECT surname, NVL(phone_number, '12345')
FROM Staff;
129
