The University of Texas

CS 327e

Spring 2002

Test 1

NAME: ______________________________________ LAST 4 DIGITS of SSN: __________________

Date: 2002-02-18

Duration: 60 minutes

Total: 100 points, 50 questions, Each Question is weighted equally, and is worth 2 points. Additionally, there is also a bonus question in the end.

The correct answers are shown in BOLD.

1) The DBMS manages the interaction between the end user and the database.

a) True b) False

2) There are 3 different types of anomalies: modification, insertion, and deletion.

a) True b) False

3) The final outcome of a natural JOIN yields a table that does not include un-matched pairs.

a) True b) False

4) The proper use of foreign keys is crucial to exercising data redundancy control.

a) True b) False

5) Cardinality expresses the specific number of entity occurrences associated with one occurrence of the related entity.

a) True b) False

6) The physical model is both software and hardware independent.

a) True b) False

7) Normalization is a process for changing attributes to entities.

a) True b) False

8) Because a partial dependency can exist only if a table's primary key is composed of several attributes, a table whose primary key consists of only a single attribute must automatically be in 2NF if it is in 1NF.

a) True b) False

9) It is possible for a table in Second Normal Form (2NF) to exhibit transitive dependency, where one or more attributes may be functionally dependent on nonkey attributes.

a) True b) False

10) A table is in Third Normal Form (3NF) if it is in 2NF and it includes no transitive dependencies.

a) True b) False

11) Normalization should be part of the database design process.

a) True b) False

12) Normalization procedures focus on the characteristics of specific entities. It represents a micro view of the entities within the E-R diagram.

a) True b) False

13) The American National Standards Institute prescribes a standard SQL - the most recent version is known as SQL-99.

a) True b) False

14) Any changes made to the table contents are not physically saved on disk until you use the SAVE <table name> command.

a) True b) False

15) You can select partial table contents by naming the desired fields and by placing restrictions on the rows to be included in the output.

a) True b) False

16) SQL allows the use of logical restrictions on its inquiries such as OR, AND, AND NOT.

a) True b) False

17) DBMSs are important because

a.	We need good ways of managing such data.
b.	They contain a query language that makes it possible to produce ad hoc queries.
c.	They help create an environment for end users to have access to more data.
d.	All of the above
e.	None of the above

18) A file system is composed of

a.	Hardware - Software - Procedure
b.	People - Hardware - Data
c.	Procedures - Hardware - Software - Data
d.	Procedures - Hardware - Software - Data - People

19) A primary key

a.	Consists of only one field.
b.	Has the same value for all records.
c.	Must contain a unique value for each record within the table.
d.	Is defined automatically.

20) Of the database system environment, which of the following is not considered part of the people component?

a.	Salesmen
b.	Administrators
c.	Designers
d.	End Users

21) The most important advantages of the hierarchical database model are

a.	Conceptual simplicity, security, integrity, diversity, and security.
b.	Security, efficiency, diversity, simplicity, and integrity.
c.	Integrity, efficiency, diversity, independence, and simplicity.
d.	Conceptual simplicity, security, independence, integrity, and efficiency.

22) Using network terminology, a relationship is called a (n)

a.	Member.
b.	Owner.
c.	Set.
d.	Table.

23) The network database models have

a.	A navigational system that yields simple design.
b.	A simple system that promotes efficiency.
c.	An owner/member relationship that promotes database integrity.
d.	a and b
e.	b and c

24) The entity relationship model

a.	Has unlimited constraint representation.
b.	Has unlimited relationship representation.
c.	Has data manipulation language.
d.	Has visual representation that makes it an effective communications tool.

25) Nulls, if used improperly, can create problems because they can represent:

a.	An unknown attribute value.
b.	A known, but missing, attribute value.
c.	A "not applicable" condition.
d.	All of the above
e.	None of the above

26) The entity integrity rule requires that

a.	All entries are unique.
b.	A part of the key may be null.
c.	Foreign key values do not reference primary key values.
d.	Duplicate object values are allowed.

27) The referential integrity rule requires that

a.	Every null foreign key value must reference an existing primary key value.
b.	It makes it possible for an attribute to have a corresponding value.
c.	Every non-null foreign key value must reference an existing primary key value.
d.	It makes it possible to delete a row in one table whose primary key does not have a matching foreign key value in another table.

28) The key's role is based on a concept known as

a.	Consistency.
b.	Availability.
c.	Determination.
d.	Uniqueness.

29) A primary key

a.	Must be defined in every table.
b.	Is always the first field in each table.
c.	Must be numeric.
d.	Must be unique.

30) A Relational operator that yields all possible pairs of rows from two tables is known as a

a.	Union.
b.	Intersect.
c.	Difference.
d.	Product.
e.	Project.

31) A Relational operator that yields all rows in one table that are not found in the other table is a

a.	Union.
b.	Intersect.
c.	Difference.
d.	Product.
e.	Project.

32) In the context of a database table, the statement "A determines B" indicates that

a.	Knowing the value of attribute A you can not look up the value of attribute B.
b.	You do not need to know the value of attribute A in order to look up the value of attribute B.
c.	Knowing the value of attribute B you can look up the value of attribute A.
d.	Knowing the value of attribute A you can look up the value of attribute B.

33) In sophisticated application development software, nulls can create problems when using function(s) such as

a.	Count
b.	Average
c.	Sum
d.	a & c
e.	a & b & c

34) When designing a new database, it is a good idea to

a.	Avoid data redundancy.
b.	Include redundant fields.
c.	Include a common field in all tables.
d.	Use composite keys.

35) A super key that does not contain a subset of attributes that is itself a super key is called a

a.	Candidate key.
b.	Primary key.
c.	Super key.
d.	Secondary key.
e.	Foreign key.

36) A Relational operator that combines all rows from two tables is considered to be a

a.	Union.
b.	Intersect.
c.	Difference.
d.	Product.
e.	Project.

37) Database designers employ data models as communications tools to facilitate the interaction among

a.	The designers.
b.	The applications programmers.
c.	The users.
d.	All of the above.

38) The conflicts between design efficiency, information requirements, and processing speed are often resolved through

a.	Conversion from 1NF to 2NF.
b.	Conversion from 2NF to 3NF.
c.	Compromises that include denormalization.
d.	Conversion from 3NF to 4NF.

39) Domains can be used when:

a.	The set of possible values is relatively small.
b.	The attribute characteristic is not sufficiently important to justify the creation of a new entity.
c.	The objective is to standardize the data characteristic for an attribute appearing in multiple tables
d.	The attribute characteristic is not sufficiently important to justify the creation of a new one.
e.	all of the above.

40) The SQL command that lets you save your work to disk, is

a.	Insert.
b.	Select.
c.	Commit.
d.	Update.
e.	Rollback.

41) The SQL command that lets you list the table contents is

a.	Insert.
b.	Select.
c.	Commit.
d.	Update.
e.	Rollback.

42) The SQL command that enables you to make changes in the data is

a.	Insert.
b.	Select.
c.	Commit.
d.	Update.
e.	Rollback.

43) To list all the contents of a PRODUCT table you would use

a.	LIST * FROM PRODUCT;
b.	SELECT * FROM PRODUCT;
c.	DISPLAY * FROM PRODUCT;
d.	SELECT ALL FROM PRODUCT;

44) To remove the value ‘2238/QPD’ from the product table you must use the following command

a.	DELETE FROM PRODUCT WHERE P_CODE = '2238/QPD';
b.	REMOVE FROM PRODUCT WHERE P_CODE = '2238/QPD';
c.	ERASE FROM PRODUCT WHERE P_CODE = '2238/QPD';
d.	ROLLBACK FROM PRODUCT WHERE P_CODE = '2238/QPD';

45) To select partial table contents you must use the command

a.	SELECT <column(s)> FROM <Table name> WHERE <item>;
b.	LIST <column(s)> FROM <Table name> WHERE <Conditions>;
c.	SELECT <column(s)> FROM <Table name> WHERE <Conditions>;
d.	LIST<column(s)> FROM <Table name> WHERE <Item>;

46) To create a query using a mathematical symbol to find all codes but code (21344) from the product table you must write it as

a.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344;
b.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <= 21344;
c.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344;
d.	SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE => 21344;

47) ANSI-standard SQL allows the use of special operator in conjunction with WHERE clause. Such special operator used to define a range limit is

a.	Between.
b.	Null.
c.	Like.
d.	In.
e.	Exists.

48) The basic SQL aggregate function that gives the number or rows containing not null values for the given column is

a.	COUNT
b.	MIN
c.	MAX
d.	SUM
e.	AVG

49) The basic SQL aggregate function that gives the total of all values for a selected attribute in a given column is

a.	COUNT
b.	MIN
c.	MAX
d.	SUM
e.	AVG

50) A HAVING clause can be used to replace a WHERE clause.

a) TRUE b) FALSE

51) Bonus Question: (2 points)

IBM bought another database company in 2001. The DBMS that they got as part of the acquisition is

a) DB2 b) UDB c) SQL Anywhere d) Informix e) Teradata