Physical Database Design

Which of the following SQL statements should you use to assign privileges to other users who need access to a relation you created?

GRANT

You create a relation called Employee and give to the user known as 55001 full privileges on the Employee relation. Which of the following SQL statements allows user 55001 the right to give full privileges to other users so they can access the Employee relation?

GRANT ALL PRIVILEGES ON Employee To 55001 WITH GRANT OPTION;

Which of the following statements about using secondary indexes is true?

On each update to a record with a secondary index, the secondary index must also be updated.

In which database design phase would you be concerned with DBMS implementation?

Physical design phase

What is the term for the operations that particular users are authorized to perform?

Privileges

Which of the following statements about relation and attribute names is true?

All relations in a given database must have unique names, but different relations can share attribute names.

Consider the following relation definitions: CREATE TABLE LOCATIONS ( location_num VARCHAR (6) NOT NULL PRIMARY KEY, location_address VARCHAR (75) NOT NULL); CREATE TABLE EMPLOYEES ( emp_num INTEGER NOT NULL PRIMARY KEY, emp_name VARCHAR (35) NOT NULL, location_num VARCHAR (6) NOT NULL, FOREIGN KEY (location_num) REFERENCES Locations (location_num) ON DELETE NO ACTION ON UPDATE CASCADE); What occurs when the location_num attribute for a location gets updated?

The location_num attribute in the Employees relation is automatically updated.

Which of the following determines how the database designer creates relations in a relational database?

The DBMS used to design the database during the physical design phase

Consider the relation shown in the exhibit: CREATE TABLE LOCATIONS ( location_num VARCHAR (6) NOT NULL PRIMARYKEY, location_address VARCHAR (75) NOT NULL CHECK (location_num IN ('BldgA', 'BldgB', 'BldgC', 'BldgD') ) ); What occurs when you insert a new record and specify the location_num as 'BldgE'?

The insert operation will be disallowed.

If the same attribute name is shared among different tables, how would you refer to the attributes in the same SQL statement?

The name of the table precedes the attribute name and is separated by a dot.

Which of the following should you use as the basis on which to build user views?

The original data models based on individual user views of the data

Consider the following example of DDL code: Which statement is true regarding the code?

The table columns will be created in the order in which they are specified.

You create a relation called Employee and grant privileges on the Employee relation using the following SQL statement: GRANT SELECT ON Employee To 55001; Which of the following statements is true?

The user known as 55001 has SELECT privileges on the Employee relation.

Which of the following statements about users who create a view with the CREATE VIEW statement is true?

The user must already have SELECT privileges on any relations that are involved in the view.

What can you use to create an additional key for a relation?

A secondary index

Which of the following is provided by the data dictionary as the database designer begins the process of creating the base relations in the DBMS?

Each attribute's domain and any default values for each attribute

For which of the following can CHECK constraints be used?

Update and insertion operations

Consider the relation shown in the exhibit: CREATE TABLE LOCATIONS ( location_num VARCHAR (6) NOT NULL PRIMARY KEY, location_address VARCHAR (75) NOT NULL CHECK (location_num IN ('BldgA', 'BldgB', 'BldgC', 'BldgD') ) ); In this example, the CHECK constraint was used to:

create a list of acceptable values for an attribute.

In addition to improving performance, the denormalization process can:

increase the maintenance needed to ensure that the database remains in a consistent state.

The physical database design phase:

involves interpreting the logical data model into a physical data model that can be implemented by a particular DBMS.

Due to the capabilities of the DBMS you are using, you may not be able to use the CHECK constraint to:

reference more than one record at the same time.

What is the name for the process of reuniting a set of relations that were created by grouping attributes based on functional dependencies and with a minimum of data redundancy?

Denormalization

What should you consider doing if a set of relations within a database is updated infrequently and is suffering from poor performance?

Denormalize the relations.

Which of the following statements about CHECK constraints is true?

All CHECK constraint names must be unique within a database.

Why can table names contain no blank spaces?

Because blank spaces are used to separate individual words of statements in SQL

For which of the following data types should the size argument always be used?

CHAR and VARCHAR

Which of the following constraints should you use if you want to create a list of acceptable values for an attribute?

CHECK

Which of the following constraints should you use if you want to place restrictions on the data that a relation can receive?

CHECK

Which DDL statement is used to create an empty relation and to define the relation's name?

CREATE TABLE

Consider the following exhibit of DBDL code. Which DDL statement will accurately create the table described in the DBDL code shown in the exhibit?

CREATE TABLE Proj_Mgrs (pm_num INTEGER NOT NULL PRIMARY KEY, pm_first_name VARCHAR (15) NOT NULL, pm_last_name VARCHAR (25) NOT NULL);

Which of the following SQL statements should you use to build a user view?

CREATE VIEW

Consider the relation and the view shown in the exhibit. Which of the following SQL statements will correctly create the view?

CREATE VIEW Emp_Info AS SELECT emp_id, first_name, last_name, salary, hire_date, location FROM Employee WHERE hire_date = '06-01-2004';

Consider the relation and the view shown in the exhibit. Which of the following SQL statements will correctly create the view?

CREATE VIEW Emp_Info AS SELECT emp_id, first_name, last_name, salary, location FROM Employee;

Consider the Employee relation shown in the exhibit. You want to set up a view called Hire_Dates that allows a user to find an employee's first name, last name and hire date. Which SQL statement will accomplish this?

CREATE VIEW Hire_Dates AS SELECT first_name, last_name, hire_date FROM Employee;

How can the denormalization process improve relational database performance?

It reduces the number of join operations that are necessary to obtain the needed data.

Which of the following can be considered a drawback of using secondary indexes?

Secondary indexes incur additional overhead in the performance of the database.

Users can use views to:

access database data via their own portal.