RBD Flashcards
What year did Univac present the magnetic tape as a means to store data?
1951
Which company introduced the magnetic hard drive in 1956?
IBM
What was the first DBMS developed in 1961?
Integrated Data Store IDS
What data model did the Information Management System (IMS) introduce? 1965 - 70
Hierarchical data model
Who is credited with the relational data model in 1970?
Edgar Codd
What standard was established by CODASYL in 1971?
The standard for the network data model
Who developed the entity-relationship model in 1976?
Peter Chen
What was the first relational database management system developed by IBM in 1973?
System R
Which company marketed the first commercial version of a relational database management system in 1979?
Relational Software (later Oracle)
When was the first standard of SQL established?
1987
What does ODMG 2.0 refer to?
The standard for object-oriented databases established in 1997
List three aspects that have extended databases since the nineties.
- Multi-tier architectures
- Data warehouses
- GIS (Geographical Information Systems)
What is a relation in the context of relational databases?
Any subset of the Cartesian product
Fill in the blank: A relation is defined as a subset of _______.
Cartesian product
What is the term for a table in a database?
Entity
What do attributes describe in a database entity?
Properties of the object which is a name of entity
What is one requirement for attributes in a database?
Attributes should not generate redundancy
What is the First Normal Form condition regarding values?
Values must be atomic
True or False: Each entity in a database should have a unique identifier.
True
What is an example of an operation that can be performed by a database application?
Finding all staff members who participated in project a23
What should attributes not change over time?
Their value
List two examples of entities defined by attributes.
- Customer
- Product
Fill in the blank: In each entity, we have an attribute _______ allowing to identify unique records.
s
What is the attribute that describes a property of the object in a database?
Attribute
What does the term ‘instance of the entity’ refer to in a database?
Record (row in table)
Model of entities
database
Entity
table
Attribute
column
Instance of the entity
record=row in table
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.
Entities are defined by their attributes.
Attributes should describe the entity they are defined for.
Attributes should not change in time.
The intersection of a column and a row must be atomic.
Each entity should have a unique identifier.
In each entity we have an attribute /s allowing to identify unique records
First Normal Form requirement is necessary
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
A correctness condition for the data in a database.
integrity constraint
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
The pseudo-value which means that the data is missing.
Null
The distinguished key which is used to identify objects.
primary key
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
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
A key that is not primary.
unique key
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
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)
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
Manages data and information within an organization
Databases are a core component
Supports business processes
Information System
Stores metadata (data about data)
Contains definitions of tables, columns, constraints, etc.
Essential for database management
Catalog (Data Dictionary)
Used for entity-relationship diagrams
Solid line: Identifying relationship
Dashed line: Non-identifying relationship
Black circle: Detail entity
Hollow diamond: Optional relationship
IDEF1X Notation
Entity-relationship diagram notation
Entity: Rectangle
Attribute: Circle
Relationship: Diamond
Chen’s Notation
Super-entity (general) and sub-entities (specific)
Represents “is-a” relationship (inheritance)
Discriminator attribute indicates subcategory
Subcategories (Entity-Relationship)
Represents data with parent-child relationships
Can use recursive relationships
Dictionary entities for integrity
Hierarchical Data Modeling
Temporal entities store historical data
Tracks changes to attributes or relationships
“From” and “To” attributes indicate time range
Modeling Changes in Time
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
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)
Changes to the logical schema don’t affect applications
Achieved through views
Applications work with views, not base tables
Logical Data Independence
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
Table
The fundamental building block of a relational database.
Organized into rows (records) and columns (attributes).
rows
records
columns
attributes
Key
A set of columns used to uniquely identify rows within a table.
Essential for maintaining data integrity and relationships.
Primary Key
A specific key chosen to be the main identifier for a table.
Must be unique and not null.
Unique Key
A key that ensures uniqueness but is not the primary key.
Can allow null values.
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.
Null
Represents the absence of a value.
Important for handling missing or unknown data.
Integrity Constraint
Rules that ensure the accuracy and consistency of data.
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.
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.
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.
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).
Key Dependency
X includes a key.
Trivial Dependency
A∈X.
Dependency with Key Attribute on Right
A∈K (where K is the union of all keys).
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).
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.
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).
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.
Non-key Dependency
An FD that is neither trivial nor a key dependency.
Partial Dependency
A non-key dependency X→A where X is a subset of a key and A is a non-key attribute.
Transitive Dependency
A non-key dependency X→A where A is a non-key attribute, and it is not a partial dependency.
Cartesian Product
A×B={(x,y):x∈A,y∈B}
Generalized
A1×A2×…×An={(a1,a2,…,an):ai∈Ai}
Table = Relation
In RDB, tables represent relations.
Schema (R)
A set of attributes: R={A 1 ,A 2 ,…,A n }
Domain (Dom(Ai))
The set of possible values for attribute Ai
Domain of Relation (Dom(R))
The union of domains of all attributes: Dom(R)=Dom(A 1 )∪Dom(A 2 )∪…∪Dom(A n )
Tuple (t)
A mapping from attributes to their domains: t:R→Dom(R), where t(A i )∈Dom(A i ).
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.
Redundancy
Data repetition, leading to wasted space and update inconsistency.
Update Anomaly
Difficulty in updating redundant data; requiring multiple changes.
Insert Anomaly
Inability to add new data without also adding unrelated data.
Delete Anomaly
Unintended loss of related data when deleting a record.
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).
Superkey
A set of attributes that uniquely identifies tuples in a relation.
Key
A minimal superkey (no subset is also a superkey).
K (Set of Key Attributes)
The union of all keys.
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.
Trivial Dependency
A functional dependency X → A where A is a subset of X. These always hold.
Key Dependency
A functional dependency X → A where X is a superkey (includes a key).
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.
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).
Non-key Dependency
A functional dependency that is neither trivial nor a key dependency. These are the ones that can violate BCNF.
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).
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.
