IM MIDTERMS (L1,2,3,4)

Database

are used to STORE, MANIPULATE and RETRIEVE data/information in nearly EVERY TYPE of ORGANIZATIONS.

Database

is defined as a collection of interrelated data and information. It is organized to allow easy storage, manipulation, and retrieval of information (Hoffer, Ramesh, & Topi, 2019).

Data

refers to any raw facts or concepts (Coronel, Morris, & Rob, 2011; Hoffer, Ramesh, & Topi, 2019; Sukhani, nd).

Structured Data

Can be displayed in rows, columns and relational databases; Numbers, dates and strings; Requires less storage; Easier to manage and protect with legacy solutions.

Unstructured Data

Cannot be displayed in rows, columns and relational databases; Images, audio, video, word processing files, e-mails, spreadsheets; Requires more storage; More difficult to manage and protect with legacy solutions.

Information

Once data has been processed so that the meaning becomes consistent and is vividly understood by those who use it, they are turned into (Hoffer, Ramesh, & Topi, 2019).

Metadata

Data about data; describes the structure, attributes, or properties of data.

DBMS (Database Management System)

is a software package that allows users to design, create, access, maintain, manipulate, and retrieve data and information (Hoffer, Ramesh, & Topi, 2019).

File System

the earliest attempt at data processing using a computer system; each of the application programs developed addresses the data management requirements of a specific department. In effect, every department in an organization maintains its "own" private files.

Database System or DBMS Approach

emphasizes integrating and sharing data throughout the organization or at least across major segments of the organization.

Characteristics of DBMS Approach

A DBMS is required; Self-describing; Program-data independence; Support multiple data views; Multiuser system.

Database Application

A database application is a computer program whose primary purpose is to interact with a database to retrieve, manipulate, and store data.

Database Application (Coronel & Morris, 2019)

A database application is a program that interacts with the database at some point in its execution. It may collect data from users, retrieve data for display or reports, and update data in response to user input.

Relational Database

Data is stored in tables with rows and columns; SQL-based; Structured data; Supports complex queries.

NoSQL Database

Unstructured or semi-structured data; Flexible schema; Scalable; Variety of data models.

Object-Oriented Database

Data is stored as objects; Object-oriented programming; Complex data relationships; Reusable objects.

Hierarchical Database

Tree-like structure; Parent-child relationships; Fast read access; Predefined schema.

Database Environment

A database environment refers to a group of system components that define and control the collection, storage, management, and data use.

The Database System Components

Hardware, Software, Data, Procedures, and People.

Categories of end-users (Elmasri & Navathe, 2016)

Casual end-user; Naive or Parametric end-user; Sophisticated end users; Stand-alone users.

Importance of Database Design

The importance of database design lies in its foundational role in ensuring that a database system is efficient, accurate, scalable, and easy to maintain.

Key Benefits of Good Database Design

Ensures data integrity and accuracy; Supports efficient queries and reports; Reduces data redundancy; Simplifies database maintenance and future development; Enhances scalability and performance.

Components of Database Design

Conceptual design (ERD models); Logical design (Normalization, schema); Physical design (Storage, indexing).

Data Model

A ____ is a relatively simple representation, usually graphical, of more complex real-world data structures. It serves as a communication tool between the designers, developers, and users of a system (Coronel & Morris, 2019).

Conceptual Data Model

high-level view (e.g., Entity-Relationship Diagram)

Logical Data Model

more detailed, includes keys and attributes

Physical Data Model

implementation-specific, shows how data is stored in the DBMS

Importance of Data Model

A well-structured data model is essential because it helps ensure data consistency, avoids redundancy, improves data quality, and serves as a blueprint for designing databases that meet business needs. (Coronel & Morris, 2019)

Hierarchical Data Model

Structure- Tree-like — each child has only one parent.

Network Data Model

Structure- Many-to-many relationships — children can have multiple parents.

Relational Data Model

Structure- Data is stored in tables (relations) with rows and columns. Uses primary and foreign keys to define relationships.

Object-Oriented Data Model

Structure- Data is represented as objects, similar to object-oriented programming languages.

Entity-Relationship (ER) Model

Structure- A conceptual model that uses entities, attributes, and relationships to visually represent data.

Document Data Model

Structure- Stores data in documents (typically JSON or XML), often schema-less.

Key-Value Data Model

Structure- Data is stored as key-value pairs.

Graph Data Model

Structure- Nodes (entities), Edges (relationships), and Properties to represent and store relationships between data.

Relation

A relation is a named, two-dimensional table of data. A table consists of rows (records) and columns (attributes or fields)

Primary key (PK)

uniquely identifies each record in a table. This ensures that no two rows in the table are the same.

Foreign key (FK)

is used in a related table (typically on the "many" side of a relationship) to reference the primary key of another table (the "one" side), establishing a link between the two tables.

Integrity constraint

____ in DBMS are a set of rules that are applied on the table columns or relationships to ensure that the overall accuracy, validity, integrity, and consistency (i.e., the quality) of the data present in the database table is maintained.

Domain Constraints

Allowable values for an attribute.

Entity Integrity

No primary key attribute may be null.

Referential Integrity

A constraint that requires that a foreign key must have a matching primary key or it must be null.

business rule

Statements that define or constrain business transactions.

entity instance

Person, place, object, event, or concept (row in a table).

entity type

Collection of entities (table).

strong entity

Can exist independently, has its own unique identifier (single rectangle, single underline).

weak entity

Depends on a strong entity, has only a partial identifier, dpes not have unique identifier (double rectangle, double underline).

associative entity ( also called composite/bridge)

The composite entity is used to link the entity types that orginally were created in a many-to-many relationship

attribute

Property or characteristic of an entity or relationship type.

identifier (key)

Attribute that uniquely identifies an entity instance.

relationship type

Category of relationship between entity types.

relationship instance

link between entities (corresponds to primary key–foreign key equivalencies in related tables).

unary relationship

Involves an entity that is related to the same entity type

binary relationship

Composed of two different entity types related to each other

ternary relationship

Composed of three different entity types related to each other

cardinality constraints

Number of instances of one entity that can or must be associated with each instance of another entity.

minimum cardinality

Indicates if relationship is optional (0) or mandatory (1 or more).

maximum cardinality

Maximum number of instances

one-to-one relationship

Each entity has exactly one related entity.

one-to-many relationship

One entity can relate to many others, but the other side relates to only one.

many-to-many relationship

Entities on both sides can have many related entities.

supertype

A generic entity type that has a relationship with one or more subtypes

subtype

A subgrouping of the entities in an entity type that has attributes distinct from those in other subgroupings

attribute inheritance

Subtype entities inherit the values of all attributes of the supertype

generalization

Bottom-up process: The process of defining a more general entity type from a set of more specialized entity types.

specialization

Top-down process: The process of defining one or more subtypes of the supertype and forming supertype/subtype relationships.

total specialization

Every supertype instance must belong to a subtype (double line).

partial specialization

Some supertype instances may not belong to any subtype (single line).

disjoint rule

A supertype instance can belong to only one subtype.

overlap rule

A supertype instance can belong to multiple subtypes.

subtype discriminator

An attribute of the supertype whose values determine the target subtype(s)

overlap rule

a composite attribute whose subparts pertain to different subtypes. Each subpart contains a Boolean value to indicate whether the instance belongs to the associated subtype

erd

Basic diagram showing entities, relationships, and attributes.

eerd

Extended ERD that adds supertypes, subtypes, generalization, specialization, and constraints.

mandatory one

mandatory many

optional one

optional many