Database and SQL Concepts Mid-Term UPDATED

_____, also known as RESTRICT, yields values for all rows found in a table that satisfy a given condition.

SELECT

A(n) ______ table is the implementation of a composite entity used to implement an M:N relationship.

bridge

When you define a table's primary key, the DBMS automatically creates a(n) _____ index on the primary key column(s) you declared.

unique

An index is composed of an index key and pointers. What is the function of the pointers?

Shows the location of the occurrences of a particular index value.

M:N relationship can be changed into two 1:M relationships using a __________.

composite entity

Consider the tables:

table 1

vcode : 7, 8, 9, 11

name: alex, tony, charles, mary

table 2

scode: 341, 213, 312, 712

vcode: 7, 9, 7, 3

How many rows has the join table1 table2?

3

On a table of students the attributes of student code, name, surname, gpa, birth date, and phone number. What data type is more appropriate for the gpa attribute?

Numeric

Consider the following table

attribute 1 attribute 2 attribute 3 attribute 4

1 alex 123 AB

2 roxy 143 CD

2 alex 254 AB

1 tom 309 CD

attribute 3

Proper data _____ design requires carefully defined and controlled data redundancies to function properly.

warehousing

The _________operator yields a vertical subset of a table.

PROJECT

Character data, also known as ___ data, can contain any character or symbol not intended for mathematical manipulation.

string

In a student's table of a large university the name attribute could be used as a _______.

secondary key

In a university, the data of students such as student id, name, and address are recorded in the STUDENT table and the majors, and the data about them, is stored in the table MAJORS. Suppose a student is allowed to take only one major. What kind of relationship exists between students and majors?

Functional dependence.

1:M

Consider the following table:

cod price time

2 19 10

11 8 9

8 22 3

9 19 8

7 19 12

What is the result of πprice (table)?

price

19

8

22

The _____ relationship should be rare in any relational database design.

1:1

In a relational table, an intersection of a row and a column represents ____________.

a single data value

When relating SEMESTER and COURSE to an associative entity such as CLASS where a SEMESTER can have many CLASSes and a COURSE can have many CLASSes, then COURSE would have what type of relationship to CLASS?

one to many

The marketing team is not able to distinguish customers in a specific city because the CUSTOMER table is too compact. The table's attributes must be broken down to make the customers' city a groupable query. Which of the following attributes should be simplified to improve queries for the CUSTOMER table?

ADDRESS_CITY

ADDRESS_STREET

ADDRESS

ADDRESS_STATE

ADDRESS

In a COURSE and CLASS relationship if the CLASS object is given a cardinality of (0,N) then which of the following would be true?

a CLASS is mandatory

a COURSE is mandatory

a COURSE is optional

a CLASS is optional

a CLASS is optional

In an ERD that says DOCTOR writes PRESCRIPTION, a PATIENT receives a PRESCRIPTION, and a DRUG appears in a PRESCRIPTION, it is mostly portraying what type of relationship degree?

unary

recursive

binary

ternary

ternary

When re-evaluating the current ERD design, what is the best possible action a database designer can take to improve the current design in order to account for new operational processes in the company?

Review previous design documentation

If a company generates reports by city or state from its CUSTOMER table, a database designer should be cautious of attributes like ADDRESS because it does not separate the different details of a full address such as a city or state. What type of attributes would a database designer be cautious of and work on decomposing further for better querying?

composite attributes

Using tables like STORE_ADDRESS, CUST_ADDRESS, EMP_ADDRESS, and VEND_ADDRESS, database designers should consider breaking which attribute to make it easier to query, for example, where most products are coming from to improve shipment expenses and logistics?

VEND_ADDRESS

If each CLASS in an ERD can only have one PROFESSOR, the designer would place a cardinality next to the PROFESSOR object as which of the following?

(1,1)

A database designer set the CRS_CODE attribute as the primary key to the CLASS table. If no other changes are made, how many composite identifiers does the table have?

Zero

When designing a database for a car shop, the CAR entity was first conceptualized with a few basic, common characteristics for a car like, model, make, and color. However, a car does not only have one color. If a database designer splits the COLOR attribute in a Crow's Foot model, why would this be preferred over keeping it as is?

The table cannot recognize multivalued attributes.

Although a student's registration form may list information gathered from the COURSE, CLASS, BUILDING, and ROOM table, which of the following would be the most accurate phrase describing these table's relationship with each other in an UML class diagram?

a CLASS uses a ROOM in a BUILDING

COURSEs in a BUILDING have ROOMs

a COURSE generates CLASSes in a BUILDING

CLASSes in a BUILDING has multiple ROOMs

a CLASS uses a ROOM in a BUILDING

When examining an associative entity with a primary key and two foreign keys, what can a database designer review as well to better understand the overall design?

two parent entities

The entity relationship model (ERM) is dependent on the database type.

False

Although not required to capture, the EMPLOYEE table can have duplicates with the attributes EMPLOYEE_NUM and EMP_SPOUSE when employees marry each other. How would the database be designed better?

Place the EMP_SPOUSE was on a different table.

When using a solid line to show an associative entity in a Crow's Foot notation, if there is a strong relationship describe what the associative entity would have.

one primary key and two foreign keys.

When creating a strong relationship between COURSE and CLASS, which of the following would be true about the CLASS object?

It has two foreign keys in which one is a primary key of COURSE.

Using attributes such as STU_LNAME and STU_FNAME, the entity STUDENT would be represented with a _____ in the Chen model.

rectangle

Consider the following table:

cod price time

2 19 10

11 8 9

8 22 3

9 19 8

7 19 12

What is the result of σprice = 19 (table)?

cod price time

2 19 10

9 19 8

7 19 12

The ____ allows using independent tables linked by common attributes.

JOIN

The _________operator uses one single-column table (e.g., column "a") and one two-column table (e.g., columns "a" and "b")..

DIVIDE

A car wash business has a list of all its clients in a table where each client is uniquely identified by a CLIENT_ID attribute; CLIENT_ID is a primary key. Another table with all the services provided contains a SERVICE_NUM, SERVICE_TYPE, DATE, PRICE, CLIENT_ID. Which of the previous attributes in the table of services should be a foreign key?

CLIENT_ID

A data dictionary is sometimes described as ____________.

"the database designer's database"

A(n) _____ is an orderly arrangement used to logically access rows in a table.

index

If the attribute (B) is functionally dependent on a composite key (A) but not on any subset of that composite key, the attribute (B) is fully functionally dependent on (A).

False

A table of products with a foreign key of vend_code references to the table vendor. Which of the following could be an unintended effect of deleting an entry in the vendor table?

it damages the referential integrity

Consider the tables:

table1

p_code char

19 FX

10 SCI

table2

s_code size color

341 M brown

213 S red

312 L black

How many rows, and columns does table1 table2 have?

6 rows, 5 columns

In a student's table of a large university the name attribute could be used as a _______.

secondary key

A(n) ______ table is the implementation of a composite entity used to implement an M:N relationship.

bridge

Character data, also known as ___ data, can contain any character or symbol not intended for mathematical manipulation.

string

Relational algebra defines the theoretical way of manipulating table contents using _______..

relational operators

_____ logic, used extensively in mathematics, provides a framework in which an assertion (statement of fact) can be verified as either true or false.

Predicate

M:N relationship can be changed into two 1:M relationships using a __________.

composite entity

The marketing team is not able to distinguish customers in a specific city because the CUSTOMER table is too compact. The table's attributes must be broken down to make the customers' city a groupable query. Which of the following attributes should be simplified to improve queries for the CUSTOMER table?

ADDRESS

A user gets an error when entering new information into a table. The error suggests the wrong values are entered in. What can the user review about the table's attributes to ensure the right values are entered in?

The attribute's domain

In a M:N relationship between STUDENT and CLASS at a university, it is possible that a class may start with no students and a student may start with no classes. If a database designer is using the O symbol using the Chen model, what is the designer hoping to establish between these entities?

An optional relationship.

In some cases where an association is maintained within a single entity, a database designer should depict with a line on itself. If a manager is able to manage other employees, how would the EMPLOYEE table's relationship degree be described as?

unary

If a college with a department called Research must also offer courses to its students, just like all other departments that offer courses, then what type of relationship would a COURSE have to a DEPARTMENT in an entity relationship diagram (ERD)?

mandatory

A database design can deviate from normal standards when the database size is less than 100 MB.

False

When a student's grade point average (GPA) attribute has a domain of (0,4), the GPA can hold ____ possible values.

many

In a COURSE and CLASS relationship if the CLASS object is given a cardinality of (0,N) then which of the following would be true?

a CLASS is optional

If processing speed was an important requirement for a company when building out a database, what changes can a designer revise in the next database iteration if the current design has more than 95% of tables with a 1:1 relationship?

Combine some tables together.

When reading the database diagram with entities, management has a hard time understanding the implementation of these tables with just solid lines. Their relationships are still questionable. Using the tables ROOM and BUILDING, what is the most appropriate way a database designer can conceptualize their implementation in the diagram?

Insert the phrase "located in" in between.

A database designer set the CRS_CODE attribute as the primary key to the CLASS table. If no other changes are made, how many composite identifiers does the table have?

Zero

When re-evaluating the current ERD design, what is the best possible action a database designer can take to improve the current design in order to account for new operational processes in the company?

Review previous design documentation.

Recognizing the entities CLASS and STUDENT in a college ERD, what bridged entity can a database designer add to help ensure each student is assigned their classes for specific courses?

ENROLL

In Chen notation, there is no way to represent cardinality.

False

When examining an associative entity with a primary key and two foreign keys, what can a database designer review as well to better understand the overall design?

two parent entities

High processing speeds are often a high priority in database design when generating large numbers of transactions.

False

Using tables like STORE_ADDRESS, CUST_ADDRESS, EMP_ADDRESS, and VEND_ADDRESS, database designers should consider breaking which attribute to make it easier to query, for example, where most products are coming from to improve shipment expenses and logistics?

VEND_ADDRESS

The "_____" characteristic of a primary key states that the primary key must uniquely identify each entity instance, must be able to guarantee unique values, and must not contain nulls.

nonintelligent

_____ is the bottom-up process of identifying a higher-level, more generic entity supertype from lower-level entity subtypes.

Generalization

Composite primary keys are particularly useful as identifiers of composite entities, where each primary key combination is allowed _____ in the M:N relationship.

only once

An entity cluster is considered "virtual" or "_____" in the sense that it is not actually an entity in the final ERD.

abstract

The relationships depicted within the specialization hierarchy are sometimes described in terms of "is-a" relationships.

False

What type of subtypes are subtypes that contain nonunique subsets of the supertype entity set?

Overlapping

The result of a grouping operation on simple entities is called a(n) ______.

entity cluster

What type of keys are useful as identifiers of weak entities, where the weak entity has a strong identifier relationship with the parent entity?

composite key

Implementing overlapping subtypes requires the use of one discriminator attribute for each subtype.

False

A subtype contains attributes that are common to all of its supertypes.

False

An entity cluster is a "virtual" entity type used to represent multiple entities and relationships in the ERD.

False

Without the ______, the primary key inheritance rule changes.

key attributes

The surrogate key has __ meaning in the user's environment.

No meaning

What type of identifier relationship ensures that the dependent entity can exist only when it is related to the parent entity?

strong

____ relationships occur when there are multiple relationship paths between related entities.

Redundant

Which of the following is a specialization hierarchy overlapping constraint scenario in case of partial completeness?

Supertype has optional subtypes

What is the general rule that is used when using entity clusters?

to avoid the display of attributes

What do you add to the supertype table for a disjoint condition?

subtype discriminator

Composite primary keys are particularly useful as identifiers of composite entities, where each primary key combination is allowed only once in the _____ relationship.

M:N

When using a surrogate key, one must ensure that the candidate key of the entity in question performs properly through the use of what constraints?

unique index

Your boss needs you to show a specialization hierarchy that can reflect the relation between EMPLOYEES and its subtypes. What type of relationship do you need to show your boss?

1:1

Which of the following is an example of a foreign key?

Its value can be deleted from the child table.

When there is a discriminator of a weak entity how is the set specified?

using a dashed line

What is the top-down process of identifying lower-level, more specific entity subtypes from a higher-level entity supertype?

specialization

Let's assume that college football has many divisions. With this, each division has many players and each division has many teams. Given these "incomplete" business rules. What type of relationship is division in with the players and team?

division is in a 1:m relationship with team and players.

When should you use a composite primary key?

As identifiers of composite entities, in which each primary key combination is allowed only once in the M:N relationship

A _____ is a primary key created by a database designer to simplify the identification of entity instances.

surrogate key

What is the best way to manage unique values, since the database can use internal routines to implement a counter-style attribute that automatically increments values with the addition of each new row?

numeric

The purpose of an entity _____ is to simplify an entity-relationship diagram (ERD) and thus enhance its readability.

cluster

Within a specialization hierarchy, a supertype can exist only within the context of a subtype.

False

An entity cluster is formed by combining multiple interrelated entities into _____.

a single abstract entity object

What is the benefit of the entity clustering?

Avoid the display of attributes to eliminate complications that result when the inheritance rules change.

Which of the following is an entity cluster?

location

Within a specialization hierarchy, every subtype can have _____ supertype(s) to which it is directly related.

only one

One important inheritance characteristic is that all entity subtypes inherit their _____ key attribute from their supertype.

primary