Databases Theory Essay

7343 Words Apr 22nd, 2016 30 Pages
THEORY DATABASES
Views.
A virtual table that does not necessarily exist in the database but is generated by the DBMS from the underlying base tables whenever it’s accessed. These present only a subset of the database that is of particular interest to a user. Views can be customized, for example, field names may change, and they also provide a level of security preventing users from seeing certain data.
Advantages and disadvantages of DBMSs.
Some advantages of the database approach include control of data redundancy, data consistency, sharing of data, and improved security and integrity. Some disadvantages include complexity, cost, reduced performance, and higher impact of a failure.

(a) relation A table with columns and rows.
(b)
…show more content…
Two principal integrity rules for the relational model. Discuss why it is desirable to enforce these rules.
Entity integrity In a base table, no column of a primary key can be null.
Referential integrity If a foreign key exists in a table, either the foreign key value must match a candidate key value of some record in its home table or the foreign key value must be wholly null.
Integrity constraints
Contribute to maintaining a secure database system by preventing data from becoming invalid, and hence giving misleading or incorrect results.

Two major components of SQL and what function do they serve?
A data definition language (DDL) for defining the database structure.
A data manipulation language (DML) for retrieving and updating data.
3.2 Explain the function of each of the clauses in the SELECT statement. What restrictions are imposed on these clauses?
FROM specifies the table or tables to be used;
WHERE filters the rows subject to some condition;
GROUP BY forms groups of rows with the same column value;
HAVING filters the groups subject to some condition;
SELECT specifies which columns are to appear in the output;
ORDER BY specifies the order of the output.
3.3 What restrictions apply to the use of the aggregate functions within the SELECT statement? How do nulls affect the aggregate functions?
An aggregate function can be used only in the SELECT list and in the HAVING clause.

Apart from COUNT(*), each function eliminates nulls first and

Related Documents