Database Review

Exam 2, Spring 2004

Three Schema architecture

• Internal schema

• Conceptual schema

• External schemas

• Purpose of the three schemas is to produce data independence

Components of the Relational Model

• Data structure

• Data integrity

• Data manipulations or orperators

• The actual implementation of any DBMS is a superset of a subset of the relational model.

Integrity constraints over relations

• A database is only as good as the information stored in it

• DBMS must help prevent the entry of incorrect information

• An integrity constraint is a condition specified on the schema that restricts the data that can be stored

• Integrity constraints are specified when you create tables in using SQL’s Data Definition Language (DDL)

Summary of constraints on a Relational Database

• Domain Constraint

– The value of each attribute A must be an atomic value from the domain for that attribute.

• Key Constraint

– A primary key must be unique and minimal.

• Entity Integrity Constraint

– No primary key value can be NULL

• Referential Integrity Constraint

– The value of a foreign key must match a value of the primary key in the relation to which it refers

Data Definition Commands

• Create table

• Drop table

• Alter table

– alter table emp add constraint fk_deptno foreign key(dno)references dept(dnumber) ;

Specifying constraints

• Data types

• Key and referential integrity constrains

• Not null

• Check constraint

• Default values

Syntactic forms of constraints

• Column constraint

• Table constraint

• Constraints can be named

– Naming constraints clarifies error messages and allows you to refer to the constraint when you need to change it.

Data Manipulation Language (DML)

• Basic query : select ..from..where

• Renaming tables or attributes

• Wildcards for string operations % and _

• Ordering of displays (default is ascending)

• Set operations: union, except

• Aggregate functions: avg, min, max, sum, count.

• Group by and having

Find the average balance for each customer who lives in Harrison and has at least three accounts.

• select D.c_name, avg(balance)
from depositor as D, account as A, customer as C
where D.account_number = A.account_number
and D.c_name = C.c_name
and city = "Harrison”
group by D.c_name
having count (distinct D.account_number >= 3;)

SQL options

• Use join to put tables together in the from clause

• Outer joins include tuples with nulls

• Nested queries

– In, exists, not exists

Modifying the data in the DB

• Insert

• Delete

• Update

• When updating, the DBSM will check the integrity constraints

Views

• Create view view_name as

• Storing the view

– Usually DBMSs have a certain amount of memory allocated for these stored views…as that space is used up, the last view to be queried is kicked out

• Querying the view

• Updating views

• Keeping the views up to date

ER Diagrams

• An ER diagram is a high level model used to gather data and organize it into the relational model

• You start by looking at the nuouns, and organizing them into entities and attributes

• Then relations are identified from attributes that refer to other entities

– Constraints are entered as cardinality constraints and participation constraints

• The ER diagram can be directly mapped to the relational model

Common sense database designing

• Design a relation schema so that it has attributes of only one entity.

• Design the base tables so no insertion, deletion, or modification anomalies occur in the relations.

• Avoid placing attributes in a base table whose values may be NULL, as far a possible

• Avoid decomposing relations so that spurious tuples are formed by then are joined

Functional dependencies

• Type of functional dependencies

– Partial dependency

– Transitive dependency

– Full functional dependency

Inferred FDs

• A DB designer specifies the obvious FD

• There are always many more that can be inferred.

– The complete set of FD that can be inferred is called the closure

– Many of these may be trivial

• There are usually several sets of FD that are equivalent

Summary of Normal forms

• A relation is in 1NF the domains of attributes must include only atomic values

• A relation is in 2NF if every nonkey attribute is fully functionally dependent on a candidate key.

• A relation is in 3NF if every nonkey attribute is in 2NF and nontransitively dependent on a candidate key

• A relation is in BCNF if and only if every determinate is a candidate key