midterm157
GRANT and REVOKE are: DCL DML DQL DDL
DCL
To specify a list of values ............. Operator is used. IS BETWEEN LIKE IN
IN
In order to insert a row directly in the table, which command is used? INSERT UNDER INSERT IN INSERT INSIDE INSERT INTO
INSERT INTO
DML COMMANDS
INSERT UPDATE DELETE
For a table R(B, O, I, S, Q, D), if S --> D, I --> B, IS --> Q, and B --> O, then what are the keys for R? BO IS SD IB
IS
What does the following statement in SQL do? ALTER TABLE STUDENT ADD ADDRESS VARCHAR(20); -Adds a column called ADDRESS in the table student. -Invalid Syntax -Checks if a column called ADDRESS is present in the table student. -none
adds a column called address in the table student
Which of the following is/are valid aggregate function(s) in SQL? avg sum max count
all
second normal form
all elements must depend on primary key or be in its own table
join may include how many tables one two three all of the above
all of the above
INNER JOIN
allows rows from either table to appear in the result if and only if both tables meet the conditions specified in the on clause
NATURAL JOIN
perform the same task as an inner or left join, in which ON or USING clause refers to all columns that the tables to be joined have in common
third normal form
primary key defines all non key data
normalization is not a good option for ___________
OLAP online analytical processing
Which SQL function is used to count the number of rows in a SQL query? COUNT(*) COUNT() MAX() SUM()
COUNT(*)
DDL COMMANDS
CREATE ALTER DROP
DCL COMMANDS
GRANT REVOKE
inference rules - armstrongs axioms
how new functional dependencies can be inferred from given ones
partial functional dependency
if there is some attribute that can be removed from a and yet the dependency still holds
database data models usually have a way to describe _______ on what the data can be
imitations
external level
includes only those entities attributes and relationships in the real world that the user is interested in
advantages of normalization
increases data consistency helps in grouping data under same schema improves searching faster as indexes can be created
Which of the following statements is/are correct regarding these three keys (Primary Key, Super Key, and Candidate Key) in a database? -Only one candidate key can be a primary key -Candidate key is always same as super key -Minimal super key is a candidate key -A super key can be a candidate key
-Minimal super key is a candidate key -A super key can be a candidate key
first normal form
-each column can only have one value, each row must be unique and repeating data should have its own column
Two relations have 4 and 8 tuples respectively. If they are multiplied according to the rules of a Cartesian product how many tuples will be there in end result? 8 32 12 4
32
If A --> B, B --> C, and C --> D, then which of the following is/are true? B-->D A-->D A-->C
B--D A--D A--C
Assume that (RegNo, Name), (Edu, Gen), (Phone, RegNo), and (RegNo) are the super keys for a relation.Which of the following is the candidate key for this relation? RegNo Gen Phone SName
RegNo
DATA QUERY LANGUAGE
SELECT
Consider a table "weather" with columns: city, humidity, and temperature Find all cities whose humidity is 100. SELECT humidity = 100 FROM weather; SELECT city FROM weather; SELECT city FROM weather WHERE humidity = 100; SELECT city WHERE humidity = 100;
SELECT city FROM weather WHERE humidity = 100;
Consider a table "weather" with columns: city and temperature Which of this query will be used to get all cities with temperature in decreasing order? SELECT city, temperature FROM weather ORDER BY temperature DESC; SELECT city, temperature FROM weather; SELECT city FROM weather ORDER BY temperature; SELECT city, temperature FROM weather ORDER BY city;
SELECT city, temperature FROM weather ORDER BY temperature DESC;
______ is such a join that specifies that all records be fetched from the table on the left side of the join statement right join left join straight join none
left
database instance
The data in the database at any particular point in time
How can you change "Thomas" into "Michel" in the "LastName" column in the Users table? -MODIFY Users SET LastName = 'Michel' WHERE LastName = 'Thomas' -MODIFY Users SET LastName = 'Thomas' INTO LastName = 'Michel' -UPDATE Users SET LastName = 'Michel' WHERE LastName = 'Thomas' -UPDATE User SET LastName = 'Thomas' INTO LastName = 'Michel'
UPDATE Users SET LastName = 'Michel' WHERE LastName = 'Thomas'
if a->b has a trivial functional dependency then which of the following statement is true a is a subset of b b is a subset of a' a is a subset of b' b is a subset of a
b is a subset of a
conceptual level
community view of database; describes what data is stored in the database and the relationships among the data; contains logical structure and is complete view of the data requirements of the organization that is independent of any storage considerations
normalization
compose relations into two or more relations when that will remove anomalies
what is the equivalent of the cartesian product
cross join
If X --> YZ then X --> Y and X --> Z is reflexivity rule composition rule union rule decomposition rule
decomposition rule
A multivalued attribute in ER digram is represented by double oval double rec double circle dotted rec dotted circle
double oval
external schema is related to the conceptual schema by the _____________ mapping
external/conceptual; it enables the dbms to map names in the user's view to the relevant part of the conceptual schema
In a functional dependency X -> Y, if Y is functionally dependent on X but not on the proper subset of X then this functional dependency is called full functional dependency none multivalued functional dependency partial functional dependency
full functional dependency
STRAIGHT JOIN
scans and combines matching rows which are stored in associated tables otherwise it behaves like an inner join or join without any condition
what is internal level concerned with?
storage space allocation for data and indexes, record descriptions for storage, record placement, data compression
physical data independence
the immunity of the conceptual schema to changes in the internal schema; only effect that may be noticed is a change in performance
logical data independence
the immunity of the external schemas to changes in the conceptual schema
internal level
the physical representation of the database on the computer; describes how the data is stored in the database
decomposition of relations
the replacement of one relation by several, whose sets of attributes together include all the attributes of the original
Subqueries can be nested multiple times. true false
true
The conceptual Level of data abstraction is a complete view of the data requirements of the organization that is independent of any storage considerations. true or false
true
with the union clause, each query involved must output the same number of columns and be union compatible true false
true
_______________________ form has a table that contains one or more repeating groups. Unormalized 3nf 1nf 2nf
unormalized
data independence
upper levels are unaffected by changes to lower levels two kinds: logical and physical
Consider a relational schema S=(U,V,W,X,Y,Z) on which the following dependencies hold: {U -> V, VW -> X, Y-> W, X -> U} Which of the following is/are candidate keys for the relational schema? vy uyz vyz xy xyz
uyz vyz xyz
Let us consider a relation with attributes A, B, C, D, E, and F. Suppose that this relation has the FD'sA B -> C, B C -> AD, D ->E, and CF -> B. What is the closure of {C,F}, that is, {C,F}+. {A,B,C,F} {A,B,C,D,F} {A,B,C,D,E,F} {B,C,F}
{A,B,C,D,E,F}
Let R(A,B,C,D) be a relation schema and F = {A -> BC, AB -> D, B->C } be the set of functional dependencies over R. Which of the following {B,C} {A,C,D} {A,B,C} {B}
{B,C}