Quiz 2 SQL

If one department chair—a professor—can chair only one department, and one department can have only one department chair. The entities PROFESSOR and DEPARTMENT exhibit a(n) __________________ relationship.

1:1

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

1:1

The ___________ relationship is the "relational model ideal."

1:M

PRODUCT yields all possible pairs of rows from two tables, also known as the__________ product.

Cartesian

With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?

DELETE FROM Persons WHERE FirstName = 'Peter

__________________is the real power behind the relational database, allowing the use of independent tables linked by common attributes.

JOIN

Relationships can be implemented by creating a new entity in 1:M relationships with the original entities.

M:N

The ________ constraint can be placed on a column to ensure that every row in the table has a value for that column.

NOT NULL

Find the maximum enrollment, across all sections, in Autumn 2009

One way of writing this query is as follows: select max(enrollment) from (select count(ID) as enrollment from section natural join takes where semester = 'Autumn' and year = 2009 group by course id, sec id)

___________ are especially useful when you are trying to determine what values in related tables cause referential integrity problems

Outer joins

________ returns only the attributes requested, in the order in which they are requested

PROJECT

To be considered minimally relational, the DBMS must support the key relational operators__________, PROJECT, and JOIN.

SELECT

With SQL, how can you return the number of records in the "Persons" table?

SELECT COUNT(*) FROM Persons

A ________ is any key that uniquely identifies each row.

Superkey

A __________ key can be described as a minimal superkey, a superkey without any unnecessary attributes.

candidate

A ________________ contains at least all of the attribute names and characteristics for each table in the system.

data dictionary

A(n) _______________ provides a detailed description of all tables found within the user/designer-created database

data dictionary

Delete all courses that have never been offered (that is, do not occur in the section relation).

delete from course where course id not in (select course id from section)

The CUSTOMER table's primary key is CUS_CODE. The CUSTOMER primary key column has no null entries, and all entries are unique. This is an example ofintegrity.

entity

A _________ is the primary key of one table that has been placed into another table to create a common attribute.

foreign key

In a database context, the word _________ indicates the use of the same attribute name to label different attributes.

homonym

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

index

A(n) __________ only returns matched records from the tables that are being joined

inner join

Insert every student whose tot cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000.

insert into instructor select ID, name, dept name, 10000 from student where tot cred > 100

Referential __________ dictates that the foreign key must contain values that match the primary key in the related table, or must contain null.

integrity

In a relational model, ___are also used to establish relationships among tables and to ensure the integrity of the data.

keys

A(n) ________ join links tables by selecting only the rows with common values in their common attribute(s).

natural

A ______ key is defined as a key that is used strictly for data retrieval purposes.

secondary

Find all instructors earning the highest salary (there may be more than one with the same salary).

select ID, name from instructor where salary = (select max(salary) from instructor)

Find the highest salary of any instructor.

select max(salary) from instructor

Find the titles of courses in the Comp. Sci. department that have 3 credits

select title from course where dept name = 'Comp. Sci.' and credits = 3

In a database context, a(n) ____________ indicates the use of different names to describe the same attribute.

synonym

When two or more tables share the same number of columns, and when their corresponding columns share the same or compatible domains, they are said to be ____________.

union-compatible

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

unique

Increase the salary of each instructor in the Comp. Sci. department by 10%

update instructor set salary = salary * 1.10 where dept name = 'Comp. Sci.'