Final Review Flashcards
What is the difference between a database and database management system (DBMS)?
A database is the data stored and a DBMS is the software that manages that data.
What does DML stand for and what is the standard?
DML stands for Data Manipulation Language, and the standard is SQL (structured query language)
Draw the ANSI / SPARC Architecture
Describe the Internal View
The physical representation of the database on the computer, that is, *how* the data is stored.
Describe the Conceptual View.
The logical structure of the database, that is, *what* data is stored and its relationships.
Describe the External View.
The *user’s view* of the database that provides the part of the database relevant to the user.
What are the three levels of the ANSI / SPARC architecture starting with the view closest to the user?
Dexternal, Conceptual, Physical (Internal)
A relation is a _______ with columns and rows.
A relation is a table with columns and rows.
A _______ is a named column of a relation.
An attribute is a named column of a relation.
A _______ is a row of a relation.
A tuple is a row of a relation
A _______ is a set of allowable values for one or more attributes.
domain
The _______ of a relation is the number of tuples it contains.
cardinality
The _______ of a relation is t he number of attributes it contains.
degree
The _______ of a relation is the structure of the relations including its domains.
intension
The _______ of a relation is the set of tuples currently in the relation.
extension
Define:
superkey
A set of attributes that uniquely identifies a tuple in a relation.
Define:
key
A *minimal* set of attributes that uniquely identifies a tuple in a relation
Define:
candidate key
One of the possible *keys* (NOT superkeys) of a relation
Define:
primary key
The candidate key designated as the distinguishing key of a relation
Define:
foreign key
A set of attributes in one relation referring to the primary key of another relation.
True or false:
A key is always a superkey.
True
True or false:
It is possible to have more than one key for a table and the keys may have different numbers of attributes.
True, as a key is a *minimal* number of attributes, though not necessarily a *minimum*. Thus, a student could be identified by student number, or by first & last name.
Define:
domain constraint
Every value for an attribute must be an element of the attribute’s domain or null
Define:
entity integrity constraint
No attribute of a primary key can be null
Define:
referential integrity constraint
If a foreign key exists in a relation, then the foreign key value must match a primary key value in the referenced relation or be null
R is a relation with 10 rows and 5 columns. S is a relation with 8 rows and 3 columns. What is the degree and cardinality of the Cartesian product?
degree = 8
cardinality = 80
Select *one* valid SQL identifier:
a) 23test
b) ‘fred’
c) test_!
d) field_
e) from
d) field_
Because:
a) starts with number
b) & c) have special chars
e) is a reserved word
How can you map from SQL to relational algebra?
True or false:
In SQL queries, it is possible to have two or more identical rows in a relation.
True. In relational algebra it doesn’t work because relations are sets, but relations in SQL are bags, and thus duplicates are possible (in queries)
How can you remove duplicates from an SQL query?
With the DISTINCT clause.
e.g.
SELECT DISTINCT title
FROM emp
When are you guaranteed to have unique rows in an SQL query (without using DISTINCT)?
When you’re querying a primary key
What are the three phases of database design?
Conceptual, logica, and physical
Explain the conceptual step of database design.
This is a high level of data abstraction (e.g. UML); you are organising info without choosing a data model or DBMS.
Explain the logical step of database design.
This is where you organise the information into/with a data model (e.g. SQL).
Explain the physical step of database design.
This is the selection of a DBMS (e.g. MySQL)
In ER modelling, what is the degree of a relationship?
The number of entity types participating in the relationship.
True or False:
A composite key consists of two or more attributes.
True
True or False:
Relationships are generally names with singular verbs.
True
True or False:
The degree of a relationship is the number of attributes it has.
False. The number of entities.
True or False:
Composite attributes are also called atomic attributes.
False. Atomic attributes are simple attributes, that is, attributes that cannot be broken down into smaller parts.
True or False:
A participation constraint is the maximum number of times an entity participates in a relationship.
False. That’s cardinality. Participation is the *minimum* number of times an entity participates in a relationship.
True or False:
Relationships are represented using a directed edge (with arrows)
False. Those are inheritances.
True or False:
An entity is typically named using a singular verb.
False. A singular noun.
True or False:
It is not possible to have a relationship of degree one.
True. For recursive relationships, the degree is two, as the entity participates twice.
True or False:
A diamond is used to represent a relationship of degree larger than 2.
True
True or False:
A derived attribute is an attribute who value is calculated from other attributes and then physically stored in the database.
False. The value is *not* physically stored.
True or False:
Attributes are properties of eith entities or relationships.
True
True or False:
An attribute cannot be multi-valued.
False. For example, the telephone attribute for a person may be multi-valued, as people often have multiple telephone numbers.
True or False:
A composite attribute contains two or more components.
True.
True or False:
It is possible to have two candidate keys with different numbers of attributes.
True
True or False:
In ER modeling, relationship has a primary key.
False. The concept of a primay key does not apply to a relationship in ER modeling, only an entity.
True or False:
A weak entity has its own primary key.
False. Weak entities are dependent upon other entities, and thus only have partial primary keys.
True or False:
A weka entity must be associated with (identified by) a strong entity.
True.
True or False:
A weak entity can have a relationshpi with another entity besides its identifying strong entity.
True
True or False:
Generalization is a bottum-up process.
True.
True or False:
In a UML diagram, the inheritance arrow points towards the superclass.
True.
What are the two possible choices for the participation constraint in superclasses?
Mandatory
Optional
What are the two possible choices for the disjoint constraint in superclasses?
And
Or
True or False:
If the disjoint constraint is AND, a given object can be a member of multiple subclasses.
True.
True or False:
If the participation constraint is MANDATORY, a given object could be an instance of the superclass without being an instance of a subclass.
False. MANDATORY means that a superclass member must be a member of one of its subclasses.
What are the steps when converting from an ER Model to a relationship schema?
- Convert strong entities.
- Convert weak entities.
- Convert 1:1 relationships
- Convert 1:N relationships
- Convert M:N relationships
- Convert multi-valued attributes
- Convert n-ary relationships
- Convert subclasses
True or False:
The M:N relationship mapping rule could be applied to 1:1 and 1:N relationships, as it’s more general.
True.
True or False:
A weak entity will always have primary key attributes from the identifying entity.
True
True or False:
The designer has a choice on which side to put the foreign key when mapping a 1:N relationship.
False. You must select the N-side of the relationship as the relations containing the foreign key and relationship attributes.
