Collection 01

Logical Data Independence

The ability to make changes to the logical layer without disrupting existing users and processes —> Most logical changes involve a physical layer change

Examples:

• Adding a new database object

• Adding a column to an existing database table

The Logical Layer

First of the two layers of abstraction -- existing solely as abstract data structures (normalized, unified interfaces that abstract the complexity of SQL databases, cloud lakes, and APIs)

• Bridge between physical storage and applications —> describing what data is stored and how the data is organized

1. Tables

2. Columns

3. Relationships between tables

Physical Data Independence

The ability to alter the physical file structure of a database without disrupting existing users and processes

Examples:

• Moving or renaming database data files

• Changing how database objects are stored

• Adding new database objects or data files

Entity Relationship Diagrams

A visual blueprint that illustrates the logical structure of a database by mapping out people, objects, or concepts and how they relate to one another

Database Management System (DBMS)

Software that acts like a digital filing cabinet, allowing you to create, manage, store, and retrieve data from a database

• Serves as an interface between you (the user) and the organized collection of data (the database) --> helping you sort information and provide quick access while maintaining its integrity and security

The difference between a database and a spreadsheet

Entity-Relationship Diagrams --> Each table translates into an entity, and columns in each table are listed as attributes in their respective entry

• Not as effective or scalable to compile everything into a single spreadsheet: as more data floods the system, a single table will result in misleading information and errors

Database Normalization

Systematic database design process used to organize data efficiently by dividing large, complex tables into smaller, related tables by attributes; which helps to improve data integrity, prevent data anomalies, and minimize data redundancy

Database Query

A request to retrieve data from a database

Database Schema

A specification of the logical structure of the data stored in the database

Defines how data is organized in a relational database

• Commonly use visual representations to communicate the architecture of the database

• "Blueprint" that describes how data may relate to other tables, but does not contain actual data

• Specifies how the database's data iso rganized and what that organization looks like

The Physical Layer

Contains all the data files that hold all the data in the database, stored in multiple data files that are spread out over multiple physical disk drives for maximum performance

Database

An organized collection of data used for the purpose of modeling some type of organization or organizational process

• The data is organized to model aspects of reality that support processes that NEED this information

• Easily accessed, managed, and updated

• Stores a collection of data

• Defines data as information

Data

What is stored!

• David

• 10026

Information

What is retrieved!

• “David” is the First Name

• “David” is the Last Name

• "David" is the name of the Street in Hazlet, NJ

• 10026" is the Zip Code for Harlem, Manhattan

One user

How many users are spreadsheets originally designed for?

Multiple

How many users are databases designed for?

Online Transaction Processing / OLTP

A type of database based on operating the business, which involves running day-to-day transactions that are used for day-to-day operations using current data

Online Analytical Processing / OLAP

A type of database based on understanding the business, which is used for reporting and analysis, maintaining complex queries and patterns, based on historical data

MySQL

Free and open-source relational database management system that organizes data into data tables

End Users

Clients, customers, and business staff that use applications and interfaces which interact with the data, also involved in planning and testing

Database Administrators

Knowledge of database design, SQL, database theory, and configuring the database

Knowledge Workers

Also programmers, they use SQL to interact with the data and the data model, allowing information to be retrieved, stored, removed, and modified

• Provide End Users with applications, forms, and reports, and only simple interfaces for the data

System Development Life Cycle

The "traditional" development method, as it is good for learning but not best for full implementation, in which it comprises of phases with deliverables

PLANNING

Phase 1/8 of the Systems Development Life Cycle:

• Define goals and define the plan

• Form the project team

REQUIREMENTS GATHERING

Phase 2/8 of the Systems Development Life Cycle:

• Business protocols, collecting and analyzing requirements

• Understanding and analyzing user views

CONCEPTUAL DESIGN

Phase 3/8 of the Systems Development Life Cycle:

• Designing Conceptual Data Model + Entity Relationship Diagrams

• User views, entities, and business rules established

LOGICAL DESIGN

Phase 4/8 of the Systems Development Life Cycle:

• Logical Data Model developed

• Database Normalization

• Specifications written and process modeling documented with logic flow diagrams

PHYSICAL DESIGN

Phase 5/8 of the Systems Development Life Cycle:

• Physical Database Design

  • Physical Data Types imposed

  • Physical Constraints imposed

  • Creation instructions (DDL SQL) are generated

CONSTRUCTION

Phase 6/8 of the Systems Development Life Cycle:

• Build Application Software

• Create Development Environments

• Test any required Data Conversion

• Code and test application of modules

IMPLEMENTATION AND ROLLOUT

Phase 7/8 of the Systems Development Life Cycle:

• Train Users

• Install new applications on live systems

• Rollout is done in phases or the entire system at once

SUPPORT

Phase 8/8 of the Systems Development Life Cycle:

• Performance, unexpected results, bug fixes, and failures all go through a mini SDLC process and are fixed immediately