LIS2780 A2 review

A database user manual notes that, "The file contains two hundred records, each record containing nine fields." Use appropriate relational database terminology to "translate" that statement.

"the table or entity set contains two hundred rows or, two hundred tuples, or entity occurrences. Each of these rows contains nine attributes."

What is the difference between a database and a table, and what is metadata?

- A table, a logical structure that represents an entity set -The database is a structure that houses one or more tables and metadata. -A table is perceived as a two dimensional structure composed of rows and columns. - The metadata (contained w/in the database) are data about data

What does it mean to say that a database displays both entity integrity and referential integrity?

-Entity integrity describes a condition in which all tuples (i.e., rows or records) within a table are uniquely identified by their primary key. The unique value requirement prohibits a null primary key value, because nulls are not unique. -Referential integrity describes a condition in which a foreign key value has a match in the corresponding table, or in which the foreign key value may be null (depending upon the business rules).

Why are entity integrity and referential integrity important in a database?

-Referential integrity means that, if the foreign key contains a value, that value refers to an existing valid tuple (row) in another relation. -Entity integrity ensures that each row is uniquely identified by the primary key.

Describe the type(s) of relationship(s) between STORE and REGION.

Because REGION_CODE values occur more than once in STORE, we may conclude that each REGION can contain many stores. But since each STORE is located in only one REGION, the relationship between STORE and REGION is M:1. (It is, of course, equally true that the relationship between REGION and STORE is 1:M.)

What are the requirements that two relations must satisfy in order to be considered union-compatible?

Both must have the same number of attributes (columns) and corresponding attributes (columns) must have the same (or compatible) domains (data types).

Using the tables in Figure Q3.11, create the table that results from MACHINE DIFFERENCE BOOTH.

Chips 1.25 Chocolate Bar 1

Create the table that results from applying a UNION relational operator to the tables shown in Fig Q3.11.

Chips 1.5 Cola 1.25 Energy Drink 2 Chips 1.25 Chocolate Bar 1

Create the table that results from applying an INTERSECT relational operator to the tables shown in Fig Q3.11.

Energy Drink 2

Add two records to the orders table. (either implicit or explicit insert into, feel free to use your own data records )

INSERT INTO orders (order_number, order_date, customer_number) VALUES (12505,'2002-09-06',124), (12506,'2002-09-07',256); OR INSERT INTO orders (order_number, order_date, customer_number) VALUES (12505,'2002-09-06',124), (12506,'2002-09-07',256);

The data dictionary is sometimes called "the database designer's database." Which one of the following is NOT a correct description about the data dictionary?

It should only include basic information for each table in the system without additional information like table and attribute names

Which relational algebra operators can be applied to a pair of tables that are not union-compatible?

Product, Join, and Divide

Use the database shown in Figure P3.1 to answer Problems 1-5. Ch3P1: For each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None in the space provided

Store_code Region_code, emp_code none

Do the tables exhibit referential integrity? Answer yes or no and then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key.

Yes Yes N/A

Do the tables exhibit entity integrity? Answer yes or no and then explain your answer.

Yes Yes Yes

Remove order number 12491:

delete from orders where order_number=12491;

Find the customer number for every customer whose last name is Adams.

select customer_number from customer where last = 'Adams';

List the customer number, last name, first name, and balance of every customer, sort by balance in descending order, and last name in ascending order.

select customer_number,last,first,balance from customer order by balance desc, last asc;

List the last and first name of customer number 124.

select last, first from customer where customer_number = 124;

Update the units on hand to 50, warehouse number to 2, and unit price to $329.00 for part number CB03.

update part set units_on_hand=50, warehouse_number=2, unit_price=329.00 where part_number='CB03';