CIST1220-Structured Query Language SQLfall 14
#
13.On an ER diagram, which symbol identifies an attribute as part of a unique identifier
All employees must belong to at least one department
14.Which of the following is an example of a structural business rule?
Defining Instances of Entities
15.A Matrix Diagram will help you with all of the following except:
Name, optionality, cardinality
16.What are the three properties that every relationship should have?
False
2.A well-structured ERD will show only some parts of the finished data model. You should never try to model the entire system in one diagram, no matter how small the diagram might be. True or False? entity relationship model
True
3.The purpose of an ERD is to document the proposed system and facilitate discussion and understanding of the requirements captured by the developer. entity relationship model
False
A table alias is assigned to a table in the WHERE clause
False
By default, a column alias is displayed in lower case characters in the results.
True
9.The demand for Information Technology professionals in today's market is increasing.
False
No formal rules exist for drawing ERD's. The most important thing is to make sure that all entities, attributes, and relationships are documented on the diagram. The Layout is insignificant. True or False?
False
The COUNT function can only be used for numeric columns.
False
The UPPER function can be used to display upper-case characters in lower-case.
True
The number of joining conditions required to join tables is always one less than the number of tables being joined.
False
The outer join operator is used to combine the results of multiple SELECT statements
True
The percent sign (%) and underscore (_) symbols can be used with the LIKE comparison operator to create a search pattern.
selection
The process of retrieving only certain rows based upon a specified condition is known as ___
Desc and the Describe
The structure of the table can be displayed with the _________ command:
Tables, Columns
The transformation from an ER diagram to a physical design involves changing terminology. Entities in the ER diagram become __________ , and attributes become ____________.
False
To calculate the difference between two date columns, you must use the TO_DATE function.
Many to Many Mandatory
What uncommon relationship is described by the statements: "Each LINE must consist of many POINTS and each POINT must be a part of many LINES"
True
When a function is used in a SELECT statement, it only affects how the data are displayed in the results.
False, correct answer would be *"comma"* to separate the names
When listing more than one column name in the SELECT clause of a SELECT statement, the column names must be separated by semicolons.
Mandatory
When resolving a M:M relationship, the new relationships will always be __________ on the many side.
DISTINCT
Which of the following keywords can be included in a SELECT statement to suppress duplicate data
AS
Which of the following keywords can be used to indicate that a column alias should be included in the results?
ColB IS NOT NULL
Which of the following search conditions can be used to identify records that have data stored in a column named ColB?
SELECT title, pubdate, name FROM publisher JOIN books USING (pubid) WHERE category = 'BUSINESS';
Which of the following will display the title, publication date, and publisher name of each book in the BUSINESS category?
Countries may change their names and/or borders over a period of time.
Which of the following would be a logical constraint when modeling time for a country entity?
It allows you to create your data model and then check it for accuracy
Why is it important to identify and document business rules?
The price of gold fluctuates and, to determine the current price, you need to know the time of purchase.
Why would you want to model a time component when designing a system that lets people buy bars of gold?
single quotation marks (' ')
A string literal must be enclosed in ____.
False
Data Modeling is the last step in the database development process. True or False?
True
Equality, non-equality, and self-joins are all categorized as inner joins
Barring the relationships to the original entities.
If an intersection entity is formed that contains no attributes of its own, its uniqueness may be modeled by
False
One-to-One relationships are transformed into Foreign Keys in the tables created at either end of that relationship. True or False?
Artificial
People are not born with "numbers", but a lot of systems assign student numbers, customer IDs, etc. A shoe has a color, a size, a style, but may not have a descriptive "number". To be able to uniquely and efficiently identify one instance of the entity SHOE, a/an ______________ UID can be created.
False
The JOIN...USING keywords are used to join two tables that do not have a commonly named and defined column
True
The LPAD or RPAD function can be used to add a character to extend the current contents of a column to a specified width.
False
The SORT BY clause is used to present query results in a specific order.
all of the above
Which of the following is a valid comparison operator? a)!= b)IN c)^= d)all of the above
True
1.The overall mission of the Oracle Corporation is to use the internet and fast processing servers to build its own network.
Physical Model
10.A customized hat sitting on your friend's head is an example of which model--the conceptual model or the physical model?
True
11. Attributes are written inside the entity to which they belong. True or False?
False
12.When reading a relationship between 2 entities, the relationship is only read from left to right. True or False?
2 entities (or one entity and itself)
17.Relationships always exist between
A, B, and D
4.Which of the following are examples of data vs. information:
All of the above
5.Which of the following are reasons we create conceptual models?
True
7.Oracle was one of the first relational database systems available commercially. True or False?
Distinguish one instance of an entity from all other instances of that entity
8.. Unique Identifiers:
False
A cross join between two tables, containing four rows each, will display eight rows in its output
False
A foreign key always refers to a primary key in the same table.
Diamond
A non-transferable relationship is represented by which of the following symbols?
False
A table alias can be assigned in the FROM clause, even when tables are being joined using the NATURAL JOIN keywords.
True
A table does not have to have a primary key. True or False?
False
An entity can be on 2nd Normal Form even if it has repeated values. True or False?
True
Any of the single-row functions covered in this chapter can be used with the DUAL table
True
As a database designer, you have a responsibility to store data in only one place and in the best possible place. True or False?
SELECT COUNT(DISTINCT pubid) FROM books;
Based upon the contents of the BOOKS tables, which of the following SQL statements will return the number of different publishers represented in the table?
SELECT order#, shipdate-orderdate FROM orders WHERE order# = 1007;
Based upon the contents of the ORDERS table in the accompanying figure, which of the following SQL statements will display how long it took to ship order # 1007 (based upon when the order was originally placed)?
none of the above
Based upon the contents of the PUBLISHER table, which of the following SELECT statements will display the publisher's name first in the results? Based upon the contents of the PUBLISHER table, which of the following SELECT statements will display the publisher's name first in the results?
SELECT * FROM publisher;
Based upon the contents of the PUBLISHER table, which of the following is a valid SQL statement?
TEACHER NAME AND ADDRESS
Examine the following entity and decide which attribute breaks the 2nd Normal Form rule: ENTITY: CLASS ATTRIBUTES: CLASS ID DURATION SUBJECT TEACHER NAME AND ADDRESS
It depends on the functional needs of the system.
How do you know when to use the different types of time in your design?
True
If a Cartesian join is used to link table A which contains two rows to table B which contains eight rows, there will be sixteen rows in the results.
WHERE
If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed first.
False
If a date column is being used for comparison against the search condition, the search condition is enclosed in double quotation marks
Transferable
If a relationship can be moved between instances of the entities it connects, it is said to be:
ALL
If the DISTINCT keyword is not included in the VARIANCE function, the ____ keyword will be assumed.
HAVING
If the output is to be restricted based upon a group function, a(n) ____ clause must be included in the SQL statement.
Redundant
If the same relationship is represented twice in an Entity Relationship Model, it is said to be:
NULL
If the two values compared by the NULLIF function are equal, what value does the function return? The first value in the comparison
True
In a payroll system, it is desirable to have an entity called DAY with a holiday attribute when you want to track special holidays. True or False
Table
In a physical data model, an entity becomes a/an _____________.
True
In an ERD, it is a good idea to group your entities according to the expected volumes. By grouping high volume entities together, the diagrams could become easier to read. True or False? True
Another entity
Many to many relationships between entities usually hide what?
1st Normal Form
Many-to-Many relationships are perfectly acceptable in a finished ERD. There is no need to do any more work on them. True or False?
Providing valuable information via reports to management
Modeling historical data produces efficient ways for a business to operate such as:
False
No database in the world is ever truly on 3rd Normal Form. Everyone always stops after 2nd Normal Form. True or False?
False
Non-transferable relationships can only be mandatory, not optional. True or False?
SELECT order#, MONTHS_BETWEEN(shipdate-orderdate, '99') FROM orders WHERE shipdate IS NOT NULL;
Note: Column names are truncated as follows: SH = SHIPSTATE and SHIPZ = SHIPZIP Based upon the contents of the ORDERS table, which of the following SQL statements will display the number of days between the date an order was placed and the date it was shipped?
False
Relationships on an ERD can only be transformed into UIDs in the physical model? True or False?
True
The SUBSTR function is used to return a portion of a character string.
False
The TOTAL function is used to calculate the total value stored in a numeric field for a group of records.
Column integrity
The explanation below is an example of which constraint type? A column must contain only values consistent with the defined data format of the column
Entity integrity
The explanation below is an example of which constraint type? A primary key must be unique, and no part of the primary key can be null.
User-defined integrity
The explanation below is an example of which constraint type? If the value in the balance column of the ACCOUNTS table is below 100, we must send a letter to the account owner which will require extra programming to enforce.
False
The following entity is in 1st normal form: True or False? ENTITY: VEHICLE ATTRIBUTES: REGISTRATION MAKE MODEL COLOR DRIVER PASSENGER 1 PASSENGER 2 PASSENGER 3
True
The majority of operations performed on a typical database table utilize the SELECT statement
False
The table called DUMMY consists of a blank holding area that can be referenced by a user.
All non-UID attributes must be dependent upon the entire UID.
What is the rule of Second Normal Form?
True
When mapping supertypes, relationships at the supertype level transform as usual. Relationships at the subtype level are implemented as foreign keys, but the foreign key columns all become optional. True or False?
True
When using the "LIKE" operator, the % and _ symbols can be used to do a pattern-matching, wild card search. True or False?
A relationship to each entity from the original M:M.
When you resolve a M:M by creating an intersection entity, this new entity will always inherit:
SELECT title FROM d_songs WHERE type_code = 88;
Which example would limit the number of rows returned?
NULLIF
Which function compares two expressions?
SELECT title, cost, contact, phone FROM publisher JOIN books USING (pubid);
Which of the following SQL statements will display the title and cost of each book stored in the BOOKS table, as well as the name of the contact person and the phone number to call to reorder the book?
WHERE, GROUP BY, HAVING
Which of the following indicates the processing order for the indicated clauses?
An asterisk can be used as the argument for the COUNT function to include NULL values in the results.
Which of the following is a correct statement? An asterisk can be used as the argument for the COUNT function to include NULL values in the results. The NULL keyword can be included in the argument of the COUNT function to include NULL values in the results. The ALL keyword can be included in the argument of the COUNT function to include NULL values in the results. The INCLUDE NULLS keywords can be included in the argument for the COUNT function to include NULL values in the results.
TRUNC
Which of the following is not considered a group function?
False
You must make sure all entities of a proposed system can fit onto one diagram. It is not allowed to break up a data model into more than one diagram. True or False?