Relational Database and Transact-SQL Final Study
In T-SQL, varchar(n) can be used to hold a character string. The digit n is for the largest number of bytes the attribute may have. What is the largest value for n? A. 1000 B. 2000 C. 4000 D. 8000 8.2
8000
Select the relational expression that is the same as the following: SupplierProduct <-- Suppliers X Products CurrentSupplierProduct <-- SIGMA <SupplierId = SupplierId> SupplierProduct; A. CurrentSupplierProduct <-- Suppliers UNION Products B. CurrentSupplierProduct <-- Suppliers INTERSECTION Products C. CurrentSupplierProduct <-- Suppliers JOIN Products D. CurrentSupplierProduct <-- Suppliers SET DIFFERENCE Products 7.21
CurrentSupplierProduct <-- Suppliers JOIN Products
Which of the following is the SQL syntax for deleting rows of data with matching conditions? A. DELETE FROM TableName WHERE Condition; B. DELETE ROWS FROM TableName WHERE Condition; C. DELTE FROM TableName; D. DELETE ROWS FROM TableName; 7.26
DELETE FROM TableName WHERE Condition;
If you want to use the SELECT statement to return the result by a group of rows, you will use the ___________ clause. A. GROUP B. WHERE C. GROUP BY D. ORDER BY 5.20
GROUP BY
In SQL, the WHERE clause is used to filter out individual rows while the __________ clause is used to filter out individual groups. A. BOOLEAN B. WHERE GROUP C. LOGICAL GROUP D. HAVING 5.21
HAVING
Suppose dependent is a weak entity with the employee entity as its owner. If EmployeeId is the primary key of the Employee entity and DependentName is the primary key of the Dependent entity, what is the primary key of the mapped relation of the Dependent entity? A. EmployeeId B. DependentName C. EmployeeId, DependentName D. None of the above 5.9
EmployeeId, DependentName
All databases developed using ERD will end up with the same quality. True/False 6.1
False
Denormalization is a must for all database design. True/False 6.24
False
The RENAME operator can only be used to rename relations/tables. True/False 7.11
False
To find out if a column is functionally dependent on another column, you only need a small sample of the database table. True/False 6.16
False
What is the result of the following three-valued logic expression? False AND Null A. True B. False C. Null 6.10
False
With the following employee table, a deletion anomaly may happen when __________. EmployeeId FirstName LastName Department 111 Victoria Gibson Accounting 112 Justin Smith Accounting 113 Melissa Martin Marketing 114 Roy Rocha Sales 115 Jonathan Williams Accounting A. an employee is deleted B. when more than one employee is deleted C. when an employee who is the single person in a department is deleted D. when more than one employee in the same department is deleted. 6.6
when an employee who is the single person in a department is deleted
If two students have the same ID, they must have the same first name. Which of the following is true? A. ID is the primary key B. ID is a determinant C. First name is the primary key D. first name is a determinant 6.15
ID is a determinant
In SQL statement, JOIN usually means _____. A. INNER JOIN B. LEFT JOIN C. OUTER JOIN D. SELF JOIN 6.31
INNER JOIN
What's the difference between outer and inner join? A. INNER JOIN will ignore the non-matching rows while OUTER JOIN will keep non-matching rows. B. INNER JOIN will keep the non-matching rows while OTUER JOIN will ignore the non-matching rows. C. Both INNER JOIN and OTUER JOIN will keep the non-matching rows. However, the OUTER JOIN result will depend on the join type. D. Neither INNER JOIN nor OUTER JOIN will keep the non-matching rows. But, INNER JOIN may keep the non-matching rows depending on the join type. 6.30
INNER JOIN will ignore the non-matching rows while OUTER JOIN will keep non-matching rows.
Which of the following is the SQL syntax for inserting rows of data into a table with data from a different table? A. INSERT INTO TableName (Column1, Column2, ...) VALUES (Value1, Value2,...) B. INSERT INTO TableName (Column1, Column2, ...) VALUES (Row1Value, Row2Value2,...), (Row1Value, Row2Value2,...) C. INSERT INTO TableName (Column1, Column2, ...) SELECT ColumnOne, ColumnTwo,... FROM Table2Name; D. INSERT INTO TableName (Value1, Value2, ...) FOR (Column1, Column2, ...) 7.24
INSERT INTO TableName (Column1, Column2, ...) SELECT ColumnOne, ColumnTwo,... FROM Table2Name;
Which of the following is the SQL syntax for inserting multiple rows into a table (data are not from different tables?) A. INSERT INTO TableName (Column1, Column2, ...) VALUES (Value1, Value2,...) B. INSERT INTO TableName (Column1, Column2, ...) VALUES (Row1Value, Row2Value2,...), (Row1Value, Row2Value2,...) C. INSERT INTO TableName (Column1, Column2, ...) SELECT ColumnOne, ColumnTwo,... FROM Table2Name; D. INSERT INTO TableName (Value1, Value2, ...) FOR (Column1, Column2, ...) 7.23
INSERT INTO TableName (Column1, Column2, ...) VALUES (Row1Value, Row2Value2,...), (Row1Value, Row2Value2,...)
Which of the following is the SQL syntax for inserting a single row into a table (data are not from different tables?) A. INSERT INTO TableName (Column1, Column2, ...) VALUES (Value1, Value2,...) B. INSERT INTO TableName (Column1, Column2, ...) VALUES (Row1Value, Row2Value2,...), (Row1Value, Row2Value2,...) C. INSERT INTO TableName (Column1, Column2, ...) SELECT ColumnOne, ColumnTwo,... FROM Table2Name; D. INSERT INTO TableName (Value1, Value2, ...) FOR (Column1, Column2, ...) 7.22
INSERT INTO TableName (Column1, Column2, ...) VALUES (Value1, Value2,...)
The _______ operation keeps only the common rows between two relations. A. UNION B. INTERSECTION C. SET DIFFERENCE D. CARTESIAN PRODUCT 7.15
INTERSECTION
to map a generalization/specialization relationship to relations, create a relation for each entity, set the primary key of the _____ entity as a foreign key in each _____ entity, and then set the foreign key in the _____ entities as also the primary key. A. sub, super sub B. super, sub, sub C. super, super, sub D. sub, super, super 5.17
super, sub, sub
What does "relational" in "relational database" mean? A. relationship B. database C. table D. column 5.1
table
Which of the following is not a possible situation for null values in a database? A. the database server is down B. the attribute is not applicable for the entity instance C. the data has been not entered in the database table D. the data is not known 6.8
the database server is down
The _________ operation can be expressed as CARRTESIAN PRODUCT followed by the SELECT operations. A. UNION B. INTERSECTION C. SET DIFFERENCE D. JOIN 7.20
JOIN
What is the table name if you see the relation schema below? Movie(MovieId, Title, Duration) A. Movie B. MovieId C. Title D. Duration 5.7
Movie
In T-SQL, which of the following is a Unicode character string data type? A. NVARCHAR B. CHAR C. TEXT D. VARCHAR 8.1
NVARCHAR
In T-SQL, what's the difference between the VARCHAR and NVARCHAR data type? A. VARCHAR is used for foreign languages. B. NVARCHAR is used for foreign languages only. C. VARCHAR can be used for both English and foreign languages. D. NVARCHAR can be used for both English and foreign languages. 8.3
NVARCHAR can be used for both English and foreign languages.
What is the result of the following three-valued logic expression? False OR Null A. True B. False C. Null 6.11
Null
What is the result of the following three-valued logic expression? True AND Null A. True B. False C. Null 6.9
Null
Denormalization is the opposite of normalization. Denormalization combines relations while normalizations break up relations. True/False 6.23
True
The RENAME operation is often used together with the relation assignment operator <--. True/False 7.12
True
Which of the following relational algebra equations is true? A. R INTERSECTION S = ((RUS) - (R-S)) + (S-R) B. R INTERSECTION S = ((RUS) + (R-S)) - (S-R) C. R INTERSECTION S = ((RUS) - (R-S)) - (S-R) D. R INTERSECTION S = ((RUS) - (R+S)) - (S-R) 7.17
R INTERSECTION S = ((RUS) - (R-S)) - (S-R)
What is the result of the following three-valued logic expression? True OR Null A. True B. False C. Null 6.12
True
The ________ operation includes all rows from both relations with duplications removed. A. SELECT B. PROJECT C. RENAME D. UNION 7.13
UNION
Which of the following is a binary relational operation? A. SELECT B. PROJECT C. RENAME D. UNION 7.5
UNION
While JOIN puts the two tables side by side, ______ puts the two tables top and bottom. A. LEFT JOIN B. RIGHT JOIN C. FULL JOIN D. UNION 6.32
UNION
Write an SQL statement to change the price of an OrderDetails table from $2.00 to $2.10 for all quantities under 10. A. UPDATE OrderDetails SET Price FROM 2.0 TO 2.1 WHERE Quantity < 10; B. UPDATE OrderDetails SET Price = 2.1 WHERE Quantity < 10; C. UPDATE OrderDetails CHANGE Price FROM 2.0 TO 2.1 WHERE Quantity < 10; D. UPDATE OrderDetails SET Price = 2.1 WHERE Price = 2.0 AND Quantity < 10; 7.25
UPDATE OrderDetails SET Price = 2.1 WHERE Price = 2.0 AND Quantity < 10;
In SQL statement, which of the following is the correct way to write the WHERE clause? A. WHERE SecondPhone = Null B. WHERE SecondPhone = 'Null C. WHERE SecondPhone IS Null D. WHERE SecondPhone LIKE NULL 6.13
WHERE SecondPhone IS Null
The ________ operation is used to retrieve rows of data that meet certain conditions from a relation. A. SELECT B. PROJECT C. RENAME D. UNION 7.7
SELECT
Which of the following is a unary relational operation? A. JOIN B. UNION C. SELECT D. SET DIFFERENCE 7.3
SELECT
Which SQL statement is equivalent of the following relational algebra expression? OrderByEmployee5 <-- SIGMA <EmployeeID = 5> Orders OrderAfter04012017 <-- SIGMA <OrderDate > '20170401' > Orders Result <-- OrderByEmployee5 U OrderAfter04012017; A. SELECT * FROM Orders B. SELECT OrderId FROM Orders UNION SELECT OrderDate FROM Orders; C. SELECT * FROM Orders WHERE EmployeeId = 5 UNION SELECT * FROM Orders WHERE OrderDate > '20170401'; D. there's no equivalent in SQL. 7.14
SELECT * FROM Orders WHERE EmployeeId = 5 UNION SELECT * FROM Orders WHERE OrderDate > '20170401';
Which SQL statement is equivalent of the following relational algebra expression? SIGMA<supplierId = 3> Products A. SELECT * FROM SupplierId = 3; B. SELECT * FROM Products WHERE SupplierId = 3; C. SELECT SupplierId FROM Products WHERE SupplierId = 3; D. SELECT SupplierId, 3 FROM Products; 7.8
SELECT * FROM Products WHERE SupplierId = 3;
Which SQL statement is equivalent to the following relational algebra expression? PIE<ProductId, DelvieryDate>Deliveries A. SELECT ProductId, DeliveryDate FROM Deliveries; B. SELECT * FROM Deliveries; C. SELECT ProductId FROM Deliveries WHERE DeliveryDate = GETDATE(); D. SELECT * FROM Deliveries WHERE ProductId = @ProductId; 7.10
SELECT ProductId, DeliveryDate FROM Deliveries;
The ______ operation keeps only the rows that appear in the first relation and not in the second relation. A. UNION B. INTERSECTION C. SET DIFFERENCE D. CARTESIAN PRODUCT 7.16
SET DIFFERENCE
Which of the following is a binary relational operation? A. SELECT B. PROJECT C. RENAME D. SET DIFFERENCE 7.4
SET DIFFERENCE
With the following employee table, a modification anomaly may happen when __________. EmployeeId FirstName LastName Department 111 Victoria Gibson Accounting 112 Justin Smith Accounting 113 Melissa Martin Marketing 114 Roy Rocha Sales 115 Jonathan Williams Accounting A. a department changes its name B. an employee changes his or her last name C. an employee changes his or her first name D. an employee changes both his or her first name and last name 6.7
a department changes its name
With the following employee table, an insertion anomaly may happen when __________. EmployeeId FirstName LastName Department 111 Victoria Gibson Accounting 112 Justin Smith Accounting 113 Melissa Martin Marketing 114 Roy Rocha Sales 115 Jonathan Williams Accounting A. a new employee without a department is inserted B. a new employee for a new department is inserted C. a new department is inserted D. a new department without and employees is inserted 6.5
a new department without and employees is inserted
What is a null value in relational database design? A. an empty string value B. a zero numeric values C. both A and B D. an unknown value 5.3
an unknown value
To map a binary many-to-many relationship, you add a new relation to the database. This new relation is mapped to the ________. A. associative entity B. third entity C. additional entity D. backup entity 5.14
associative entity
A table that contains only ______ values in each cell is called a relation and all relations are in the first normal form. A. numeric B. text C. usefil D. atomic 6.17
atomic
In T-SQL, which data type is used to store values of True or False? A. Boolean B. Logic C. T/F D. bit 8.4
bit
What does a good relation do? A. reflect what is in the ERD. B. allow minimum redundancy C. both a and b D. none of the above. 6.2
both a and b
A relation is in second normal form if ________. A. it is in first normal form B. all its values are atomic C. every non primary key attribute is functionally dependent on the whole primary key. D. both a and c 6.20
both a and c
A relation is in the third normal form if ________. A. it is in the second normal form B. every non primary key attribute is functionally dependent on the whole primary key. C. it contains no transitive dependency D. both a and c 6.21
both a and c
Inner join puts two tables side by side, _____. A. but only those rows with matching values will appear in the joined table. B. all the rows from both tables will be in the joined table. C. but the two tables must have the same number of rows. D. but the two tables must have the same number of columns. 6.29
but only those rows with matching values will appear in the joined table.
The associative entity/relation added in a many-to-many relationship mapping contains at least two attributes __________________. A. from one of the participating entities B. from both of the participating entities C. composite of the primary key attribute of the participating entities D. composite of all attribute from both entities 5.15
composite of the primary key attribute of the participating entities
In a relation schema, how do you represent a foreign key? A. put the attribute as the first one in the list. B. solid underline the attribute C. dash underline the attribute D. put the attribute as the last one in the list 5.6
dash underline the attribute
All entities should have a primary key so that each row (tuple to be more accurate) can be uniquely identified by the DBMS. this requirement is called _____. A. entity requirement B. entity integrity C. entity quality D. referential integrity 6.26
entity integrity
A ternary relationship will be mapped into a total of _______________ relation(s). A. one B. two C. three D. four 5.16
four
Say you have two columns of a relation, Column1 and Column2. If two rows in Column1 are equal to each other, then the two corresponding values of Column2 must also be equal to each other. Thus, Column2 is _____________ on Column1 A. functionally dependent B. over dependent C. composite primary key D. equal 6.14
functionally dependent
In T-SQL, varbinary data type is often used to store _________. A. images B. variables C. numbers D. digits 8.7
images
The INTERSECTION operation is not part of the complete set of six operations because ___________. A. it can be expressed in terms of UNION and SET DIFFERENCE B. it is rarely used. C. it is too late to be introduced. D. six is a good number 7.19
it can be expressed in terms of UNION and SET DIFFERENCE
If the data needed is from more than one table, you can __________. A. combine the tables B. join the tables C. use WHERE clause D. use HAVING clause 6.28
join the tables
A rule for denormalization is to _____________. A. always keep relations in at least third normal form. B. always keep relation in at least second normal form. C. make sure data used together stay together. D. make sure at least one denormalization occurs for each database. 6.25
make sure data used together stay together.
When mapping a binary 1:1 relationship to relations, adding the primary key from the optional side as a foreign key to the mandatory side results in ___________. A. more null values B. fewer null value C. more columns D. fewer columns 5.12
more null values
The process of reducing data redundancy in a relation is called ________. A. normalization B. redundancy minimization C. redundancy removal D. redundancy reduction 6.18
normalization
The relationship between entities in ERD are usually reflected by the use of foreign keys. Adding a foreign key achieves __________. A. entity requirement B. entity integrity C. entity quality D. referential integrity 6.27
referential integrity
______ is the theory behind all SQL statements. A. relational algebra B. SQL concept C. SQL theory D. SQL foundation 7.6
relational algebra
_________ uses letters and symbols to represent relations and operations. A. arithmetic B. relational algebra C. relational symbol D. relational operation 7.1
relational algebra
According to the author, for most business problems, a _________ normal form is good enough for a relation. A. first B. second C. third D. fourth 6.22
third
To map a binary many-to-many relationship, you will create a total of _______________ relation(s). A. one B. two C. three D. four 5.13
three
Suppose relation R1 has a1 attributes and r1 rows and another relation, R2, has a2 attributes and r2 rows. The result of using _______ on R1 and R2 is a new relation that has a1+a2 number of attributes and r1Xr2 number of rows. Each row from R1 should be combined with each row from R2 for a result of r1Xr2 number of rows. A. UNION B. INTERSECTION C. SET DIFFERENCE D. CARTESIAN PRODUCT 7.18
CARTESIAN PRODUCT
Which of the following is the correct execution sequence (not syntax sequence) of SQL statements? A. FROM, WHERE, GROUP BY, HAVING, SELECT B. SELECT, FROM, WHERE, GROUP BY, HAVING C. SELECT, FROM, HAVING, GROUP BY, WHERE D. SELECT, FROM, WHERE, HAVING, GROUP BY 5.23
FROM, WHERE, GROUP BY, HAVING, SELECT
Which of the following is the correct execution sequence (not syntax sequence) of SQL statements? A. SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY B. FROM, WHERE, GROUP BY, HAVING, ORDER BY, SELECT C. FROM, WHERE, HAVING, GROUP BY, SELECT, ORDER BY D. FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY 5.24
FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY
Which of the following is the correct execution sequence (not syntax sequence) of SQL statements? A. SELECT, FROM, WHERE B. FROM, WHERE, SELECT C. SELECT, WHERE, FROM D. FROM, SELECT, WHERE 5.22
FROM, WHERE, SELECT
The _____ operation is used to retrieve columns of data from a relation. A. SELECT B. PROJECT C. RENAME D. UNION 7.9
PROJECT
Which of the following is a unary relational operation? A. PROJECT B. UNION C. INTERSECTION D. SET DIFFERENCE 7.2
PROJECT
When mapping an entity with multivalued attributes, _______. A. there is no difference from mapping a strong entity. B. there is no difference from mapping a weak entity. C. add a new attribute for each multivalued attribute. D. add a new relation for each multivalued attribute. 5.18
add a new relation for each multivalued attribute.
A(n) _______________ function performs a calculation on a column and returns a single value. A. mathematic B. aggregate C. normal D. single 5.19
aggregate
What is the "ERD" to relations mapping" ? A. converting entities to relations B. converting relationships to relations C. converting entities and relationships in ERD to relations so that tables can be created. D. converting one ERD to one relation 5.2
converting entities and relationships in ERD to relations so that tables can be created.
Which of the following is note a type of relation anomaly discussed in this chapter? A. insert anomalies B. delete anomalies C. modification anomalies D. create anomalies 6.4
create anomalies
Which of the following is the most serious issue with data redundancy in relations? A. extra storage. B. data inconsistency. C. longer retrieval time. D. confusion for the user. 6.3
data inconsistency.
In T-SQL, if you need both date and time of the day with fractional seconds, which data type is recommended? A. date B. datetime C. datetime2 D. any of the above 8.6
datetime2
The normalization process is based on the functional dependency of the attributes. A relation is broken into smaller relations based on its ________. A size B. determinant C. structure D. normal form 6.19
determinant
To map a binary 1:1 relationship to relations, add the primary key from the ___________ side as a foreign key to the ___________ side. A. optional, mandatory B. mandatory, optional C. optional, optional D. mandatory, mandatory 5.11
mandatory, optional
Which of the following is NOT one of the ERD to relation mapping objectives? A. no data duplication B. all attributes in ERD are kept in relations C. keep relationship constraints from ERD to relations D. minimize the null values in the relations whenever possible. 5.4
no data duplication
In T-SQL, what precision and scale are needed for a number like 22.345? A. numeric(2 , 3) B. numeric( 2, 5) C. numeric(5 , 3) D. numeric(3 , 2) 8.5
numeric(5 , 3)
To map a one-to-many relationship to relations, just add the primary key from the ____________ side as a foreign key on the ____________ side. A. one, one B. one, many C. many, one D. many, many 5.10
one, many
In a relationship schema, how do you represent a primary key? A. put the attribute as the first one in the list. B. solid underline the attribute C. dash underline the attribute. D. put the attribute as the last one in the list. 5.5
solid underline the attribute
In SQL statement, a query can be built on the result of another query. This is called ______. A. query on query B. subquery C. result query D. super query 5.25
subquery
How do you map a regular entity in the ERD to a relation? A. the entity name becomes the relation name and the entity attributes become the relation column headers. There is no change in the primary key B. the relation is given no name, and entity attributes become relation column headers. C. each attribute in the entity becomes a relation. D. add the primary key of the owner entity as a foreign key to the entity and set both the owner primary key and current entity primary as a composite primary key. 5.8
the entity name becomes the relation name and the entity attributes become the relation column headers. There is no change in the primary key
