Exam 3

13. Discuss the 7 advantages and 3 disadvantages of views.

Advantages: 1. Data independence: A view can present a consistent, unchanging picture of the structure of the database, even if the underlying source tables are changed 2. Currency: a change in the tables is updated immediately in the view 3. Improved security: views can restrict a user's access to DB 4. Reduced complexity: views can simplify queries by drawing data from several tables into a single table 5. Convenience: users are presented only with the part of DB they need to see 6. Customization: appearance of DB is simplified or customized; different users can see them in different ways 7. Data integrity: ensures that any row that fails to satisfy the WHERE clause is not added to any of the base tables through the view Disadvantages: 1. Update restriction: views cannot be updated 2. Structure restriction: structure of view cannot be changed 3. Performance: there is a performance penalty to be paid when using views, some views may take a long time to process.

2. Name 5 advantages and 5 disadvantages of SQL.

Advantages: 1. SQL command structure and statement syntax are easy to understand. 2. SQL commands are portable as it works when moving from one DBMS to another. 3. It is a flexible and universal language. 4. It only allows users with permission to access DB to do so. 5. Using standard SQL makes it easier to manage DB without writing huge code. Disadvantages: 1. The license cost is high. 2. SQL requires detailed knowledge of DB structure. 3. It contains defined and manipulative commands and does not have flow of control commands, so it lacks computational completeness. 4. Sometimes it may lead to incorrect results. 5. Not recommended for use in multi-user DB as it lacks self-documentation.

1. What are the two main components of SQL? what function do they serve and give an example of each.

DDL (Data Definition Language): allows users to define the structure of DB, specifies data types, and constraints on the data stored in DB. Ex: CREATE DML (Data Manipulation Language): provides set of operations that allows for manipulation of data store in DB; allows user to insert, delete, modify, update and retrieve data from DB. Ex: SELECT, INSERT, DELETE, UPDATE

11. Name the 8 base data types in SQL.

a. Boolean, character, bit, exact numeric, approximate numeric, datetime, interval, large objects

4. Explain the function of the WHERE clause in the SELECT statement and give an example.

a. Filters rows subject to some condition i. WHERE salary>1000

5. Explain the function of the GROUP BY clause and give an example.

a. Forms groups of rows with the same column value i. GROUP BY branchNo

15. What is the materialized view and what are the advantages of maintaining it rather than view resolution?

a. Materialized view is a temporary table stored in DB to represent a view, which is maintained as the base table is updated b. Advantages: i. may be faster than view resolution ii. useful for integrity checking and query optimization

9. Explain how the GROUP BY clause works. Difference between the WHERE and HAVING?

a. SQL first applies the WHERE clause, then conceptually arranges table based on grouping columns, then does the HAVING clause, then orders results according to the ORDER BY b. HAVING filters groups subject to some condition, WHERE does the same with rows

7. Explain the function of the ORDER BY clause and give an example

a. Specifies the order of the output i. ORDER BY salary DESC

6. Explain the function of the SELECT clause and give an example.

a. Specifies which columns are to appear in the output i. SELECT staffNo

3. Explain the function of the FROM clause in the SELECT statement and give an example.

a. Specifies which tables are to be used i. FROM Staff

8. What restrictions apply to the use of the aggregate functions within the SELECT statement? How do nulls affect them?

a. They can be used only with the SELECT and HAVING clause statement b. apart from COUNT, each function eliminates nulls first and then operates only in the non-null values. COUNT counts all the rows, even ones with nulls.

10. What is the difference between subquery and a join? Under what circumstances would you not be able to use a subquery?

a. With a subquery the columns specified in the SELECT list are restricted to one table. Join can access multiple tables. b. Subqueries cannot be used when the results obtained use information from more than one table.

14. Describe how the process of view resolution works.

a. any operations on view are automatically translated into operations on relations from which it is derived 1. The view column names in the SELECT list are translated into their corresponding column names in the defining query 2. View names in the FROM clause are replaced with the corresponding FROM lists of the defining query 3. The WHERE clause from the user query is combined with the WHERE clause of the defining query using the logical operator AND 4. The GROUP BY and HAVING clauses are copied from the defining query. In this example, we have only a GROUP BY clause 5. Finally, the ORDER BY clause is copied from the user query with the view column name translated into the defining query column name

12. Discuss each of the clauses of the create table statement.

a. column definition: defines the attribute of tuple (specifies data type) b. PRIMARY KEY: specifies the columns that form the primary key for the table; not null, one per table, unique value c. FOREIGN KEY: specifies foreign key in the child table, implements referential integrity constraints d. Check constraints: allow for additional constraints to be defined; constraints are checked after every SQL is executed; references the column being defined.