CGS2545 Exam Review

Terms that were on Exam 1

Relational : A named, two-dimensional table of data.

Degree : Number of participation entity types in relationship.

Ternary : Relationship of degree three.

Identifier/primary key : Uniquely identifies entity instances

Database : Organized collection of related data.

Overlap rule : Entity belongs to two subtypes.

Attribute inheritance : Subtypes gets supertype attributes.

Associative entity: Relation modeled as an entity type (i.e., relationship with attribute(s)).

Back to the Top

Glossary for all TERMS in Chapters 1 - 9

Multiple Choice Exam 1 Questions

1. A student can attend five classes, each with a different professor. Each professor has 30 students. The relationship of students to professors is a _________ relationship.
answer:
True: Many to Many
False: 1 - 1
False: 1 to many
False: Strong

2. In the following diagram, which answer is true? employee can
answer:
True: Each employee can supervise 1 to many employees.
False: Each employee can manage many departments
False: Each employee works in more than one department.

3. The ____ rule specifies that an entity instance of a supertype is allowed not to belong to any subtype.
answer:
True: Partial Specialization.
False: semi-specialization
False: total specialization.
False: disjointedness

4. The________ rules specifies that each entity instance of the supertypes must be a member of some subtype in the relationship.
answer:
True: Total Specialization.
False: semi-specialization
False: partial specialization.
False: total convergence

5. In the figure below, the guitar can be either an electric or a non-electric guitar. Guitar
This is an example of the _________________ rule.
answer:
True: disjoint
False: specialization
False: generalization
False: overlap

6. A rule that states that each foreign key value must match a primary key value in the other relation is called the:
answer:
True: referential integrity constraint
False: key match rule.
False: entity key group rule.
False: foreign/primary match rule.

7. Which of the following are anomalies that can be caused by redundancies in tables?
answer:
True: All of the below
True: insertion
True: deletion
True: modification

8. In the figure below and type of key is depicted by the Manager_ID Attributes? Manager_ID
EMPLOYEE answer:
True: recursive foreign
False: primary
False: recursive primary
False: composite

Back to the Top

Multiple Choice Exam 2 Questions

1. What does the following SQL statement do?
DELETE FROM Customer_T
WHERE state = 'HI';
answer:
True: deletes all records from Customer_T where the state is equal to HI
False: deletes all records from the Customer_T table
False: removes the Customer_T table from the database
False: none of the above.

2. What does the following SQL statement do?
UPDATE Product_T
SET Unit_Price = 775
WHERE Product_ID = 7;
answer:
True: changes the unit price of Product 7 to 775
False: changes the price of a unit called Product_T to 7
False: changes the length of the Unit_Price field to 775
False: updates the Product_T table to have a unit price of 775.

3. In an SQL statement which of the following parts states the conditions of row selection?
answer:
True: WHERE
False: SELECT
False: FROM
False: GROUP BY

4. What does the following SQL statement do?
SELECT *
FROM Customer
WHERE Cust_Type = "Best";
answer:
True: select all fields from the Customer table for each row with a customer labeled "Best".
False: select all fields from the Customer table for each row with a customer labeled "*".
False: select the "*" field from the Customer table for each row with a customer labeled "best".
False: select all fields with a "*" in them from the Customer table.

5. What results will the following SQL statement produce:
SELECT AVG(standard_price) AS average
FROM product_v;
answer:
True: the average standard_price of all products in product_v .
False: the average of all products in product_v.
False: the average price of all products.
False: none of the above.

6. Which of the following questions is answered by the SQL statement?
SELECT COUNT (Product_Description)
FROM Product_T;
answer:
True: How many different product descriptions are in the Product Table?
False: How many products are in the Product Table?
False: How many characters are in the field name "Product_Description"?
False: How many different columns named "product Description are there in table Product_T?

7. What results will be produced by the following SQL query?
SELECT SUM(standard_price) AS total_price
FROM product_v
WHERE product_type = 'Wood';
answer:
True: the total price of all products that are of type Wood .
False: the standard_price of any wood product in the table.
False: the total price of all products .
False: the standard_price of the first wood product in the table.

8. ** Which of the following counts ONLY rows that contain a value?
answer:
** True: COUNT
False: COUNT (*)
False: TALLY(*)
False: CHECKNUM

9. ** Which of the following will produce the minimum of all standard prices?
answer:
** True: SELECT MIN(standard_price) FROM product_v.
False: SELECT standard_price FROM product_v Where standard_price= min;
False: SELECT standard_price FROM MIN(product_v);
False: SELECT MIN (standard_price) FROM product_v WHERE standard_price = MIN (standard_price);

10. What will result from the following SQL Select statement?
SELECT MIN(product_description) //assume that product_description has the character data type.
FROM product_v;
answer:
True: The first product_description alphabetically in product_v will be shown.
False: an error message will be generated.
False: The minimum value of product_description will be displayed.
False: none of the above.

11. Which of the following is the wildcard operator in SQL statements?
answer:
True: *
False: €
False: &
False: =

12. ** Which of the following is true about the SQL statement?
SELECT *
FROM Product
WHERE Quantity = 1;
answer:
** True: All fields will be selected from the Product table for the product that have a quantity of 1.
False: All fields will be selected from the Product table for products that have a quantity of only 2.
False: All fields will be selected from the Product table for products that have a quantity of 1 and 2.
False: None of the above.

13. What result set will be the following query return?
SELECT item_no, description
FROM item
WHERE weight > 100 and weight < 200
answer:
True: the item_no and description for all items weighing between 101 and 199.
False: the item_no and description for all items weighing less than 100.
False: the item_no and description for all items weighing between 101 and 199.
False: the item_no for all items weighing more than 200.

14. To eliminate duplicate rows in a query, the ____ qualifier is used in the SQL Select command.
answer:
True: DISTINCT
False: ALTER
False: CHECK
False: SPECIFIC

15. What result set is returned from the following query?
SELECT customer_ name, telephone
FROM customers
WHERE city IN ('Boston', 'New York', 'Denver');
answer:
True: the customer_name and telephone of all customers living in either Boston, New York, or Denver.
False: the customer_name and telephone of all customers.
False: the customer_name and telephone of all customers living in either Boston and New York, and Denver .
False: the customer_name of all customers living in either Boston, New York, or Denver .

Back to the Top

True/False Questions From Exam 1

1. A completeness constraint may specify that each entity of the supertype must be a member of some subtype in the relationship.
TRUE

2. The disjoint rule specifies that if an entity instance of the supertype is a member of one subtype, it MUST simultaneously be a member of any subtype.
FALSE - it only needs to be a member of one subtype.

3. * Information is processes data.
TRUE

4. Data redundancy is a disadvantage of the database approach.
FALSE - there should be NO redundancy.

5. Databases were developed as the first application of computers to data processing.
FALSE -

6. A composite key consists of only one attribute.
FALSE - more than one (two or more)

7. Each column in a table represents an instance of an entity.
FALSE - Each column in a table represents an instance of an Attribute.
The rows in a table represents an instance of an entity.

8. * Data is processed information.
FALSE -Information is processes data.

9. In the relational model, associations between tables are defined through the use of foreign keys.
TRUE

10. When a regular entity type contains a composite attribute, two relations are created.
TRUE

Back to the Top

Review SQL and Relational Algebra commands

Exam 2 Given the following database schemas:
DEPOSIT (BranchName, AccountID, CustomerName, Balance) // Contains customer deposit accounts
CUSTOMER ( CustomerName, Street, CustomerCity)
BORROW (BranchName, LoanID, CustomerName, Amount ) // Contains customer loan accounts
BRANCH (BranchName, Assets, BranchCity )
Write both the Relational Algebra AND SQL Commands to do the following:

17 (a). Find the names of all customers who have deposit balances greater than $700.
Relational Algebra:
Π CustomerName (σ Balance > $700 (DEPOSIT))
SQL:
SELECT DEPOSIT.CustomerName, DEPOSIT.Balance
FROM DEPOSIT
WHERE (DEPOSIT.Balance ) > "$700");

17 (b). Find the names of all customers who have a loan but not an account at the Oviedo Branch.
Relational Algebra:
Π CustomerName [ (Π CustomerName (σ Balance =' Oviedo ' (BORROW))) −
(ΠCustomerName (σ Balance =' Oviedo ' (DEPOSIT))) ]
SQL:
SELECT BORROW.CustomerName, BORROW.BranchName
FROM BORROW
WHERE (((BORROW.BranchName)='Oviedo'));
EXCEPT
SELECT DEPOSIT.CustomerName, DEPOSIT.BranchName
FROM DEPOSIT
WHERE (((DEPOSIT.BranchName)='Oviedo'));

17 (c). Find the name and city of all customers having deposit accounts.
Look at database
Relational Algebra:
Π CustomerName, CustomerCity (DEPOSIT ∞ CUSTOMER )
SQL:
SELECT DEPOSIT.CustomerName, CUSTOMER.CustomerCity
FROM DEPOSIT, CUSTOMER
WHERE DEPOSIT.CustomerName = CUSTOMER.CustomerName;

17 (d). Find the names of all customers with accounts (i.e., loans, deposits, or both) at the Orlando Branch.
Look at database
Relational Algebra:
(Π CustomerName ( σ BranchName = " Orlando " ( BORROW )))
∪ (Π CustomerName ( σ BranchName = " Orlando " ( DEPOSIT )))
SQL:
SELECT BORROW. [CustomerName]
FROM BORROW
WHERE (((BORROW. [BranchName])= " Orlando "));
UNION
SELECT DEPOSIT. [CustomerName]
FROM DEPOSIT
WHERE ((( DEPOSIT. [BranchName])= " Orlando "));

18(a). Raise all deposit accounts of more than $1,000 with a 3% bonus.
Look at database
SQL:
UPDATE DEPOSIT. Balance
SET DEPOSIT. Balance = "DEPOSIT. Balance *1.03"
WHERE ("DEPOSIT. Balance" > " $1000");

18(b). Find the average loan amount at each branch (i.e., sort by branch)
Look at database
SQL:
SELECT AVG(BORROW.Amount) AS Avg_Loan, BORROW. BranchName
FROM BORROW
GROUP BY BORROW. BranchName;

18(c). Find the number of loan holders at each branch.
Look at database
SQL:
SELECT COUNT(BORROW.CustomerName) AS Num_of_Customers, BORROW. BranchName
FROM BORROW
GROUP BY BORROW. BranchName;

18(d). Find the total amount of deposit.
Look at database
SQL:
SELECT SUM (DEPOSIT. Balance) AS Total_Amount
FROM DEPOSIT ;

18(e). Sort the DEPOSIT relation such that Balances are in descending order and AccountIDs are in ascending order. (Use only 1 SQL statement)
Look at database
SQL:
SELECT *
FROM DEPOSIT
ORDER BY DEPOSIT. AccountID, DEPOSIT. Balance DESC;
BONUS Find the branches with the average loan amount is less than $1,200.
Look at database
SQL:
SELECT BORROW.BranchName, AVG (BORROW.Amount) AS Avg_Loan
FROM BORROW
GROUP BY BORROW.BranchName
HAVING (AVG (BORROW.Amount) < "$1200") ;

Example where 3 tables are needed:
Find all employees who live in the same city as the company they work for. (From Homework 4)
SQL:
SELECT WORKS.Name
FROM WORKS, LIVES, LOCATED_IN
WHERE (((WORKS.Name)=[LIVES].[Name]) AND ((LIVES.City)=[LOCATED_IN].[City]));

Back to the Top

Links to Other Pages in this Website:

Back to the Top