AIS Exam#3

What are the requirements of a relation?

1. *Unique name*. 2. Atomic. 3. Each *row* is *unique*. 4. Each *attribute* has a *unique name*. 5. Sequence of columns is insignificant. 6. Sequence of rows is insignificant.

What is the well-structured relation?

A relation that has minimal redundancy. Allows users to insert, modify, or delete rows without errors or inconsistency.

An attribute in a relation of a database that serves as the primary key of another relation in the same database is called a: A. Foreign key. B. link key. C. link attribute. D. foreign attribute.

A. foreign key.

The entity integrity rule states that: A. no primary key attribute can be null. B. referential integrity must be maintained across all entities. C. each entity must have a primary key. D. a primary key must have only one attribute.

A. no primary key attribute can be null.

In the figure below, what type of relationship is depicted? A. One-to-one. B. Many-to-many. C. One-to-many. D. Unary

B. Many-to-many.

An attribute (or attributes) that uniquely identifies each row in a relation is called a: A. column. B. primary key. C. foreign field. D. duplicate key.

B. primary key.

A primary key that consists of more than one attribute is called a: A. foreign key. B. multivalued key. C. composite key. D. cardinal key.

C. composite key.

Which of the following are anomalies that can be caused by redundancies in tables? A. Insertion. B. Deletion. C. Modification. D. All of the above.

D. All of the above.

What are the referential integrity constraints?

Foreign key value must match the primary key value in another relation or the foreign key value is null.

Write a Query to list all the customers

SELECT * FROM Customers

Write a query to show the total value of all the orders that customers whose names start with letter "R" have placed.

SELECT SUM(quantity*price) AS "Total value" FROM books, odetails WHERE books.bno = odetails.bno and ono in (SELECT ono FROM orders WHERE cno in (SELECT cno FROM customers WHERE cname in (SELECT cname FROM customers WHERE cname like "R%")))

Write a query to show the book names and prices for the book that are less expensive than the average book price

SELECT bname, price FROM books WHERE price < SELECT (avg (price) FROM books)

Get the names of cities of customers who have placed an order for the book "Accounting 101". Required: you must complete this query with sub select.

SELECT city FROM Zipcodes WHERE zip in(SELECT zip FROM customers WHERE cno in(SELECT cno FROM orders WHERE ono in(SELECT ono FROM odetails WHERE bno in(SELECT bno FROM books WHERE bname = "Accounting 101"))))

Write a query to find total $ value in each order. You shall display "ono" and "Total". Hint: you need to use "Group By".

SELECT ono, sum(quantity*price) AS "Total" FROM books, odetails WHERE books.bno = odetails.bno GROUP BY ono