Quiz 2.1 Flashcards
give the formal name for the following practical components of the relational model - Table
Relation
give the formal name for the following practical components of the relational model Column
Attribute
give the formal name for the following practical components of the relational model row
Tuple
Includes a name, data type, format, and in some special cases a unit of measurement is referred to as the logical definition of this.
Domain
For questions 5 & 6 please refer to the following denotation of the relation schema of R…
R(A1, A2, …, An)
What is “R” referred to as?
Relation name (Relation Schema)
For questions 5 & 6 please refer to the following denotation of the relation schema of R…
R(A1, A2, …, An)
What is “A1, A2, …, An” referred to as?
Ordered list of attributes
What is the degree (or arity) of the following relation schema for STUDENT?
STUDENT(Ssn, Name, DateOfBirth)
3
Which of the following describes the relation schema in general; Relatively Static or Constantly Changing?
Relatively Static
For questions 9 & 10 please refer to the following denotation of the relation state r of R…
r(R) = { t1, t2, …, tm }
t = < v1, v2, …, vn >
What is “{t1, t2, …, tm}” referred to as?
List of tuples
For questions 9 & 10 please refer to the following denotation of the relation state r of R…
r(R) = { t1, t2, …, tm }
t = < v1, v2, …, vn >
What must be true about the value v1 in terms of the dom(A1)?
It must be in the domain of A1.
Which of the following describes the relation state in general; Relatively Static or Constantly Changing?
Constantly changing
Give one downside of NULL values and the relational model regarding arithmetic aggregations and comparisons.
If we perform a query that compares the values of 2 tuples that are both null, then weird things happen: ie null is not necessarily = to null
State the key difference between the entity relationship and relational models in terms of how entities and relationships are represented in each (explain your answer).
In entity relationship model (entities, relationships), these are just relations in the relational model.
What assumption states that only true facts about the universe are present within the extension (state) of the relation(s)?
The closed world assumption.
Also known as model-based constraints, and are characterized by being inherent in the data model?
Implicit constraints.
Also known as explicit constraints, and can be directly expressed in the schemas of the data model, typically by specifying them in the data definition language (DDL)?
Schema-based
Also known as semantic constraints, and cannot be directly expressed in the schemas of the data model, hence they must be expressed and enforced by the application programs or in some other way?
Business rules
This type of schema-based constraint specifies that within each tuple t, the value v of each attribute A must be an atomic value from domain dom(A).
Domain constraint
This type of constraint can also be classified as what type of constraint (hint: It’s not schema-based)?
Model based
A subset of attributes S such that for tuples t1 and t2 the following invariant holds: t1[S] ≠ t2[S] is referred to as what?
Superkey
A subset of attributes S such that for tuples t1 and t2 the following invariant holds: t1[S] ≠ t2[S] is referred to as what?
This must always be true for which subset of attributes (according to the relational model)?
All the attributes
True or false, the set of attributes constituting a key is an attribute of the relation state r(R)?
False, of the schema.
When a relation schema has more than one key, one must be chosen to uniquely identify tuples. This is referred to as what special kind of key?
Primary key
A relational database schema S is a set of relation schemas S = { R1, R2, …, Rm } and what else?
Set of integrity constraints IC.
The constraint stating that no primary key values can have NULL values is referred to as what?
Entity integrity
These kinds of constraints typically arise from the relationships among entities represented by the relation schemas.
Referential Integrity Constraints
REVIEW PAGE 4 of CHPT 5 Questions
What does SQL stand for?
Structured Query Language
What type(s) of DBMS language(s) would SQL be considered (from Chapter 2, pages 39 – 40 in the textbook)?
DML and DDL
What is the advantage of using a standardized language such as SQL to define and manipulate our database(s) within the context of a relational DBMS?
It’s portable i.e., the base implementation must be implemented by the RDBMS vendor.
What is a schema?
Collection of tables, and other things (types, domains, constraints, authorization grants, views, etc…) generally 1:1 with database applications i.e., your API’s.
What are the two required components for creating a schema in SQL?
CREATE SCHEMA X AUTHORIZATION Y – DDL (Schema name, authorization identifier)
What is the default schema, and what does it stand for?
dbo (Database owner)
What is a collection of schemas referred to as?
Catalog, referred to as the database.
What is the name of the special schema that includes information on all the schemas of the database and all the element descriptors of those schemas?
INFORMATION_SCHEMA
CREATE TABLE UNIVERSITY.STUDENT
(
A. B. C. D.
);
- CONSTRAINT PK_STUDENT_SSN
C
CREATE TABLE UNIVERSITY.STUDENT
(
A. B. C. D.
);
10. CHAR(9)
B
CREATE TABLE UNIVERSITY.STUDENT
(
A. B. C. D.
);
PRIMARY KEY
D
CREATE TABLE UNIVERSITY.STUDENT
(
A. B. C. D.
);
Ssn
A
CREATE TABLE UNIVERSITY.STUDENT
(
A. B. C. D.
);
What types of constraints can be specified directly within the CREATE table statement from above?
All the constraints within the schema-based constraints
Modify the DDL statement from questions 9 – 12 to create the STUDENT table explicitly within the UNIVERSITY schema instead of the implicitly the default defined for the current database user.
I think the UNIVERSITY. does this already
The STUDENT table would be considered one of these types, which along with its attributes is always created and stored as a file by the DBMS?
Base tables (relations)
Describe the main difference between the two main character string datatypes, CHAR and VARCHAR.
CHAR – padded with blank spaces.
VARCHAR – not padded with blank space.
CHAR(10), VARCHAR(10)
‘NATE ’ VS. ‘NATE’
DECIMAL( i, j )
- What is i (i.e., give its name, and describe what it means)?
Precision – total number of digits
DECIMAL( i, j )
What is j (i.e., give its name, and describe what it means)?
Scale – number of digits after the decimal
- What is the smallest “i” we can specify to represent the value 1.1?
2
- What is the smallest “j” we can specify to represent the value 1.1?
1
- True or false, the default value is included in any new tuple if an explicit value is not provided for that attribute?
True*
- The UNIQUE keyword specifies alternate unique keys known as what?
Candidate keys
- What is the default referential triggered action when referential integrity is violated (when tuples are updated, or deleted)?
RESTRICT (CASCADE, SET NULL, SET DEFAULT) – qualified with ON DELETE or ON UPDATE
- What are the other referential triggered action(s), in general when would or wouldn’t you use each one?
I got this
SELECT <attribute>
FROM <table list>
WHERE <condition>;</condition></attribute>
- Compared to the comparison operators in Java, those in SQL which are used in the <condition> after the WHERE keyword, are the same except for which operator(s) (give the operator(s) in Java and SQL)?</condition>
<> is != (= or ==)
Use BETWEEN instead of > X and <= Y
- Describe the difference between a selection condition and a join condition (give an example).
Selection condition attributename = ‘’, join condition attributename = someotherattributename;
- True or false, in SQL the same name can be used for two (or more) attributes if the attributes are in different tables?
True
- Renaming tables in the FROM clause to avoid repeated typing of long tables is referred to as what?
Aliasing
- To retrieve all the attribute values of the selected tuples, we do not have to list the attribute names explicitly in SQL; we just specify this, which stands for all the attributes?
, relationname.
- This optional keyword can be added after the SELECT keyword and preserves the function of the query.
SELECT ALL = SELECT
- What’s the difference between “%” and “_” in terms of use in the WHERE <condition>?</condition>
% multiple chars, _ single char
- What is the default ordering when using the ORDER BY clause without specifying explicitly what the ordering should be?
ASC Other is DESC
- Explain two of the different ways we can INSERT tuples into a relation.
Insert INTO UNIVERSITY.COURSE VALUES (‘X’, 242, 3, ‘Y’)
OR
Insert INTO UNIVERSITY.COURSE (Crn, Credits) VALUES (‘X’, 3)
- True or false, zero, one or many tuples may be deleted by a single DELETE command?
TRUE
DELETE FROM UNIVERSITY.COURSE
WHERE 1 = 1;
- True or false, I can update multiple relations with a single UPDATE command?
FALSE
