IST Midterm

○​ = not on study guide; CH1: 1-9, CH2: 10-21, CH3: 22-

Data ○​

stored representations of meaningful objects and events

• structured: numbers, text, dates

• unstructured: images, video, documents

Metadata

data that describes the properties and context of user data

Data & Metadata example

Database

organized collection of logically related data

• data stored in one place can be related to data stored in another place

• data feeds into programs that manipulate and analyze it

  • operational: reflects organization dynamic transactions

  • analytical: reflects the organization's static or historical data

database model ○​

architecture that the DMBS uses to store objects within the database and to relate them to one another

The database approach ○​

• central repository of shared data

• data is managed by a controlling agent

• stored in a standardized, convenient form

  • requires a Database Management System

Database management system (DBMS) ○​

a software system that is used to create, maintain, and provide controlled access to user databases

Advantages of the Database Approach

• Program-data independence

• Planned data redundancy

• Improved data consistency

• Improved data sharing

• Increased application development productivity

• Enforcement of standards

• Improved data quality

• Improved data accessibility and responsiveness

• Reduced program maintenance

• Improved decision support

Costs and Risks of the Database Approach

• New, specialized personnel

• Installation and management cost and complexity

• Conversion costs

• Need for explicit backup and recovery

• Organizational conflict (e.g., conflicts on data definitions, data formats and coding, rights to update shared data)

Business Rules

• Are statements that define or constrain some aspect of the business

• Are derived from policies, procedures, events, functions

• Assert business structure

• Control/influence business behavior

• Are expressed in terms familiar to end users

• Are automated through DBMS software

  • example: A student may register for a section of a course only if he or she has successfully completed the prerequisites for that course.

The Essential Role of Business Rules

• Define how entities interact, what constraints are applied, and how data should be organized and validated.

• Ensure the ERD accurately reflects business requirements, supports data integrity, and provides a solid foundation for the database design.

Entity ○​

a person, a place, an object, an event, or a concept in the user environment about which the organization wishes to maintain data

• categories strong, weak, and associative

Associative Entity ○

• A special type of entity that is used to represent and manage many-to-many relationships between two other entities.

• Helps in normalizing the database schema by avoiding redundant data and ensuring that relationships are managed efficiently.

Attribute ○

property or characteristic of an entity or relationship type

• Required vs. Optional Attributes

• Simple vs. Composite Attribute

• Single-Valued vs. Multivalued Attribute

• Stored vs. Derived Attributes

• Identifier Attributes

Criteria for Identifiers ○

Will not change in value, Will not be null, will not likely change (location); can be simple or composite

Degree of Relationships

Uniary Relationship, Binary Relationship (most common), and Ternary Relationship

Uniary Relationship

One entity related to another of the same entity

Binary Relationship

Two different entities related to each other

Ternary Relationship

Three entities related to each other

One-to-One Relationship Cardinality

Each entity instance in the relationship will have exactly one related entity instance
One-to-Many Relationship Cardinality

Attributes for M:N Relationships ○

A many-to-many relationship often requires an additional attribute to capture additional information, avoid redundancy, ensure referential integrity, and enable complex queries.

Data integrity ○

Mechanisms for implementing business rules that maintain integrity of manipulated data

All relations are in…

1st Normal form

Relation

A _______ is a named, two-dimensional table of data

Consists of rows (records) and columns (attribute or field)

Requirements for a table to qualify as a relation:

• It must have a unique name.

• Every attribute value must be atomic (not multivalued, not composite).

• Every row must be unique (can’t have two rows with exactly the same values for all their fields).

• Attributes (columns) in tables must have unique names.

• The order of the columns must be irrelevant.

• The order of the rows must be irrelevant.

Integrity Constraints

Mechanisms for maintaining the integrity of stored data.

• Domain Constraints

• Entity Integrity

• Referential Integrity

Domain Constraints (Integrity Constraints)

Allowable values for an attribute (includes data types and restrictions on values, e.g., NOT NULL)

Entity Integrity (Integrity Constraints)

No primary key attribute may be null. (All primary key fields MUST contain data values.)

Referential Integrity (Integrity Constraints)

Rules that maintain consistency between the rows of two related tables.

Rule states that any foreign key value (on the relation of the many side)

MUST match a primary key value in the relation of the one side. (Or the foreign key can be null.)

• For example: Delete Rules

• Restrict–don’t allow delete of “parent” side if related rows exist in

“dependent” side

• Cascade–automatically delete “dependent” side rows that correspond with

the “parent” side row to be deleted

• Set-to-Null–set the foreign key in the dependent side to null if deleting from

the parent side (not allowed for weak entities)

Referential Integrity Picture (Integrity Constraints)