dbms final exam

data

raw, unprocessed facts stored in a database

information

processed data that support decision making

metadata

data that described the structure of data (“data about data”)

DBMS

software that manages a database and controls access; reduces redundancy and allows multi-user access

data dictionary

stores definition (metadata) of all data in the database

table/relation

organizes data into rows and columns

row/tuple/record

a single entry in a table; represents on instance

column/attribute/field

a characteristic stored for each recordsc

schema

the overall structure/definition of a database d

degree

number of columns (attributes) in a table

cardinality

number of rows in a table

domain

the set of valid values for an attribute

primary key

uniquely identifies each record; must be unique and NOT NULL (entity integrity)

foreign key

references the primary key in another table; enforces referential integrity

candidate key

any attribute that could serve as a primary key; when multiple choices exist, one is selected as the primary key

composite key

a key made of two or more attributes combined; valid only if the collection uniquely identifies an instance and no smaller subset does

secondary key

an attribute likely used for searching; not unique; fields should be indexed for faster retrieval

schema

the blueprint/structure of a database — tables, columns, data types, constraints, and relationships. it does NOT contain actual data

internal schema

how data is physically stored on disk

conceptual schema

the logical structure (tables, keys, relationships); what you design in ER diagramse

external schema/view

what a specific user or app sees (a subset of the full schema)

table/relation

named collection of rows and columns

column/attribute

name + data type

constraint

rule enforced on data: PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK

relationship

defined by foreign key references between tables

PRIMARY KEY

unique + NOT NULL; one per table

FORIEGN KEY

must match an existing primary key in the referenced table (referential integrity)

NOT NULL

column cannot be empty

UNQIUE

all values in column must differ (but CAN be NULL)

CHECK

custom rule

normalization

the process of organizing data to reduce redundancy and prevent anomalies

1NF (First Normal Form)

all attributes must contain atomic (single, indivisible) values; no repeating groups or lists in a cell

2NF (Second Normal Form)

must be in 1NF; every non-key attribute must depend on the entire primary key (no partial dependencies)

3NF (Third Normal Form)

must be in 2NF; no non-key attribute may depend on another non-key attribute (no transitive dependencies)

BCNF (boyce-codd normal form)

stricter version of 3NF; every determinant must be a candidate key

Functional Dependencies

If attribute A determines the values of attribute B, we say A functionally determines B, or B is functionally dependent on A. Written as A—>B; all attributes of an entity are functionally dependent on the primary key

Full Functional Dependency

a non-key attribute depends on the complete composite key (required for 2NF)

Partial Functional Dependency

a non-key attribute depends on only part of a composite key (violates 2NF)

Transitive Functional Dependency

a non-key attribute depends on another non-key attribute, e.g., A —> B —> C (violates 3NF)

entity

a data object for which we store information

attribute

a property of an entity

simple attribute

cannot be divided further

composite attribute

can be broken into smaller parts

derived attribute

calculated from other attributes; shown as a dashed ellipse

multivalued attribute

can hold multiple values for one entity; shown as double ellipse

identifier attribute

uniquely identifies an entity instance

Rectangle

entity

Ellipse/Oval

attribute

Double ellipse

multivalued attribute

dashed ellipse

derived attribute

diamond

relationship

double rectangle

weak entity

strong entity

exists independently; has its own primary key

Weak entity

depends on a parent (strong) entity to exist; has no standalone key

Associative entity

resolves M:N relationships; bridges two entities

Unary

entity related to itself (recursive relationship)

Binary

relationship between 2 entities (most common)

Ternary

relationship among 3 entities

1:1 (One to One)

one entity relates to exactly one other

1:M (One to Many)

one entity relates to many

M:N (Many to Many)

many entities relate to many; resolved with an associative entity

Optional Participation

entity does NOT have to participate in a relationship

Degree of a relationship

the number of entities involved

Supertype

general entity with shared attributes

Subtype

more specific version; inherits all supertype attributes

specialization

top-down: start with general entity —> define specific subtypes

generalization

bottom-up: combine specific entities —> create a general supertype

subtype discriminator

attribute that determines which subtype an entity belongs to

multivalued attribute

an attribute that holds more than one value for a single entity instance; represented by a double ellipse in ER diagrams

Example: an Employee can have multiple phone numbers, degrees, or skills

4NF (Fourth Normal Form)

must be in BCNF; a table must not contain more than one independent multivalued dependency