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}