Database Systems exam 2 review

Relational data model

Data stored in two-dimensional tables with rows and columns.

Primary key

Uniquely identifies each row in a relation.

Foreign key

Links child table to parent table in relations.

Domain constraints

Define allowable values for attributes in a relation.

Entity integrity

No primary key attribute can be null.

Referential integrity

Foreign key must match primary key or be null.

1NF

No repeating groups; all attributes are atomic.

2NF

In 1NF; non-key attributes fully depend on primary key.

3NF

In 2NF; no transitive dependencies among non-key attributes.

Varchar2

Variable length character data, max length 4000 chars.

Char

Fixed length character data, max length 2000 chars.

CLOB

Character large object, stores up to 4 GB of data.

Number

Positive or negative numbers in range 10^-130 to 10^126.

Date

Stores century, month, day, hour, minute, second.

BLOB

Binary large object, stores up to 4 GB of binary data.

Denormalization

Combines tables for performance, may cause redundancy.

Partitioning

Splits large tables into smaller ones for efficiency.

File organization

Methods include heap, sequential, indexed, and hashed.

Indexing

Speeds up data retrieval by mapping values to rows.

Transaction volume

Estimates database access frequencies based on usage.

Physical database design

Translates logical design into physical structures.

advantages of partitioning

load balancing, efficiency, and improved security by segregated data

objective of choosing data types

efficient use of storage space, fast data retrieval, accurate representation of the data, and minimizing future changes and ensuring compitability with the data that will be stored

factors influencing design

data volume, usage analysis, and regulatory compliance

properties of relations

unique names for each relation and column/row, no multivalued attributes, and order of rows and columns is insignificant