Relational Algebra Flashcards
What is an information system?
An information system is a set of interrelated components that collect, manipulate, store and disseminate data and information and provide a feedback mechanism to meet an objective.
Explain the five components of an information system.
Collect - gather and capture raw data
Manipulate - covert or transform data into useful information.
Store - Keep data and information available for future use.
Disseminate - Produce useful information
Feedback - Is information from the system that is used to make changes to the collection or manipulation of data.
Distinguish between information and data?
Data consists of raw facts.
Information is a collection of facts organised so that they have additional value.
What do the following stand for?
IS
DB
DBMS
Information Systems
Database
Database Management System
What is a DB?
A DB is a collection of logically related data of interest to the IS.
What is DBMS?
Is a software package used to define, create, use and maintain a database.
What is a database system?
The combination of a DBMS and a DB.
What should a DBMS be able to manage (3) and how should it do it (2)?
Large datasets
Shared datasets (multiple users)
Persistent datasets (data lasts beyond program execution)
Reliably
With privacy
With Efficiency (using the appropriate amount of resources) & Effectively (supporting the productivity of its users)
How is inconsistency in data avoided in relation to DBMS?
Integration of dataset and sharing allows a reduction of redundancy and the consequent possibility of inconsistency.
What is the principle of concurrency in relation to DBMS?
Concurrency involves facilitating multiple accesses that can be simultaneous to data which is suitably organised for this purpose.
What is a Database approach?
A database approach is a method of implementing DBMS, which has an integrated resource for multiple departments, avoids inconsistency and allows concurrency.
What is a file-based approach?
Every application program/department stores its data in its dedicated files. Duplicated or redundant information is stored. Inconsistency between departments, lack of concurrency.
Why is database approach superior to file-based approach?
Database approach has efficiency, consistency and concurrency. Loose coupling between applications and data makes DBMS much easier to maintain. Data format can be changed without having to massively alter application program and vica-versa.
List the four elements of a database system.
Data model
Database schemas versus instances
Database languages
Database users
What is a data model?
A data model is a set of concepts and constructs used to describe and organise data items, their characteristics and relationships. There are two types conceptual and logical model.
Distinguish between conceptual and logical model.
Conceptual:
- Early stages
- Abstract description of data items, characteristics and relationships.
- Independent of implementation environment.
- Entity-Relationship Model (ER)
Logical:
- Late stages
- Translates concept into specific implementation environment.
- Specific to implementation environment but abstracts from physical structures.
- Relational Model (R)
Give an example of feedback in an information system.
A payroll system will trigger an error if an employee in a grocery store is being paid 200 euro an hour, so the model can be changed as this is obviously incorrect.
Explain the difference between database schema and database instance.
Database Schema: Description of the data including data items, characteristics, relationships, constraints. Invariant in time.
Database Instance: Made up of actual data. Changes with time.
Define the two types of database languages.
Data definition language (DDL): used to define database schemas and access authorisations.
Data manipulation language (DML): used to retrieve and update database instances.
Name five types of database users.
-Information Architects
-Database Designers
-Database Administrators
-Application Developers
-Business Users
What is the relational model?
A logical model where a DB is represented as a collection of relations.
What is the set of attributes of a relation called?
Schema
What is a relation?
A relation is a set of tuples, each of which represents a real-world entity.
What are tuples? What is true of all tuples in a relation.
A tuple is an ordered list of attribute values describing aspects of the entity. All tuples in a relation are homogeneous (same type).
In a relation represented as a table what do the table, rows, datatype, columns and set of attributes correspond to in the relational model.
Table = Relation
Row = tuple
Datatype = domain
Column = Attribute (Name)
Set of Attributes = Database Schema
What is a database schema?
Contains multiple schemas from its relations (tables)
Differentiate between the degree and the cardinality of a relation.
The degree of a relation is the number of attributes (columns) of that relation.
The cardinality of a relation is the number of tuples (rows) contained in the relation.
There is a relation R with attributes A1, A2 … Ak define the schema and a generic tuple. Find the value of Attribute Ai for tuple t.
Schema = R(A1, A1 , … , Ak)
t = (v1, v2, …, vk)
To find value t[Ai]
What is null value?
A special value which denotes an absence of information
What is the key of a relation?
Is the set of attributes that uniquely identifies tuples of the relation.
Define key of a relation more formally.
A set X of attributes of a relation R is a key of R if
- for each instance of R, no pair of distinct tuples t’ and t’’ exist in R such that t’ and t’’ have same value for all attributes in X.
- no proper subset of X satisfies property.
List the two key conditions.
A key cannot have null values
A relation may have more than one key.
What is a primary key?
The selected key used to identify tuples in the relation. Can only be one.
What is a foreign key?
Links tuples of two different relations and provides a mechanism to model associations between entities in the relations.
R = internal relation
S = external relation
X = set of attributes of R
Y = set of attributes that is key of S
Y is the foreign key of R on S if Y is a subset of X.
Which way does a foreign key always point.
To the external relation’s key. Has to be key.
Give another name for external relation.
Referenced.
What is relational algebra?
Is a formal procedural language that provides the theoretical foundation of relational databases.
What is a procedural language?
Data retrieval functions are specified by describing the procedure that must be followed to obtain the result.
Explain the closure property in relational algebra.
Application of an operator always produces a relation as the result.
Define union-compatibility.
P and Q have the same degree.
Corresponding attributes of P and Q are based on the same domain.
Which attribute name is taken by convention?
The first relation’s name is always taken first by convention.
T or F. The degree of R U Q is the same as the degree of R and the degree of Q.
True.
T or F. The degree of P / Q is the same as the degree of P and the degree of Q
True
T or F, For P X Q, P and Q must be union compatible.
False
What is the degree of P x Q?
d(P) + d(Q)
What condition applies to cartesian product?
For P x Q, if P and Q have attributes with the same name, it is necessary to rename those attributes in one of the two relations.
What is projection? And informally? And another name?
Relation of tuples who only have specified attributes.
Getting rid of some columns
Vertical decomposition
What is selection? And informally? And another name?
Relation containing all tuples in P that satisfy some predicate
Getting rid of some rows
Horizontal decomposition
Name one condition of the renaming operator.
New attribute names must be unique
T or F. Union, difference and intersection all require union compatible relations.
True.
What is the join operator?
The join operator allows combining tuples of two relations P and Q base d on specific conditions. The result of a join is a combined effect of a Cartesian product followed by a selection.
Distinguish between natural join and standard join.
In standard join, the related columns are joined together but are included twice with different names. In natural join, two identically named attributes are shown as one column in the resulting table so all values must match.
T or F. Relational Algebra is a declarative language.
False.
T or F. It is NOT possible to apply the relational algebra operation intersection between two relations that have different cardinality.
False
T or F. The key of a relation can be composed of more than one attribute.
True
T or F. The degree of a relation is the number of attributes in the relation.
True
What is the referential integrity constraint in relation to foreign keys?
Referential integrity constraint: foreign key guarantees that values in internal relation refer to actual values in internal relation. Cannot insert rows in table where foreign key is not defined with the given value.
Distinguish between Relational Algebra and Sql?
RA:
- Formal
- Procedural
- DML
SQL:
- practical, implemented, commercial
- declarative (primarily)
- DDL + DML
Syntax when projecting two attributes.
pi x , y ()
When undertaking a natural join where names are not identical for the columns being overwritten what must be specified beneath the natural join symbol?
column_name_a = column_name_b
