Programming Language Generations

Programming Language Generations

• First-generation languages are machine language, instructions the processor directly acts on, represented by strings of 0s and 1s
• Second-generation languages are assembly languages, that use an assembler to convert assembly statements into machine code A typical 2GL instruction: ADD 12,8
• Third-generation languages are high-level languages, like PL/I, C, or Java
• A compiler turns 3GL statements into machine language which may be bytecode that get converted by a virtual machine
• 3GLs require substantial programming knowledge
• Fourth-generation languages are designed to be closer to natural language for accessing databases
• Languages used in Fourth generation languages maybe described as 4GLs, for example: EXTRACT ALL CUSTOMERS WHERE "PREVIOUS PURCHASES" TOTAL MORE THAN $1000
• Fifth-generation languages use a visual or graphical interface, compiled via 3GL or 4GL
• Microsoft, Borland, & IBM create 5GL visual programming products for Java-based applications using object-oriented class hierarchies & drag-and-drop components

Introduction to 4GLs Programming

• Fourth-generation languages (4GLs) are designed to mimic human communication
• Compilers and interpreters convert source code into machine code
• 4GLs typically use English-like words, phrases, graphic icons and on-screen buttons
• Many 4GLs use Structured Query Language (SQL) developed by IBM as the basis for operations
• SQL developed to store information in relational databases
• The American National Standards Institute (ANSI) & International Standards Organization (ISO) adopted to manage structured, factual data
• Database companies commonly offer SQL databases, open databases provide enhanced compatibility with other systems and independence from specific vendors, operating systems, & computer platforms.
• Examples of fourth-generation languages include: -PROLOG is an artificial intelligence (AI) language that uses rules to derive solutions from data -OCCAM and PARLOG are parallel-processing languages
• Newer languages may combine SQL & other high-level language features.
• IBM's Sonnet is being modified to use sound rather than visual interfacing

Databases (DB)

• A database is a long-term collection of data
• Databases are managed through a database management system

Database Management System (DBMS)

• It is sometimes called a 'database system'
• A DBMS is a powerful tool for efficiently creating and managing 10^9 bytes of data and allowing it to persist safely over time
• A DBMS focuses on secondary memory and has a powerful but simple programming interface

The Need for Database Systems

• Database systems solve data management problems
• Analogies can be drawn from personal information space to illustrate their benefit

DBMS vs. File Systems

• DBMS evolved from file systems
• File systems also store significant amounts of data over extended periods in secondary memory file systems
• However, file systems:
  • Lack efficient access
  • Lack direct support for queries
  • Limit organization to directory creation and hierarchical structures
  • Lack sophisticated concurrency support
  • Don't ensure durability

ACID Properties in Databases

• A transaction, a low-level unit of a program, must maintain ACID properties
• ACID stands for Atomicity, Consistency, Isolation, & Durability, ensuring accuracy, completeness, & data integrity

Atomicity

• Atomicity requires treating a transaction as an indivisible unit, either fully executed or not at all
• There must be no partially completed states in the database and states should be defined either before or after a transaction
• It should not be possible to execute half of an operation and either all or none of the effects of a transaction are made permanent

Consistency

• Consistency means the database remains in a consistent state after any transaction
• No transaction should adversely affect database data; maintaining consistency is required if the database was initially consistent
• Examples:
  • There should be no surprises, such as GPA > 4.0, balance

Durability

• Durability ensures the database can hold its latest updates regardless of system failures or restarts
• Updated data will be held in the database if a transaction updates data and commits
• The data will be updated once the system springs back into action should there be system failure before writing data to a disc
• Example considerations during durability: -If power goes out, nothing adverse should occur and saved effects of a transaction are permanent (unless altered by another transaction) -Transaction logs are used

Isolation

• Isolation means that in a system where multiple transactions execute simultaneously, each runs as if it were the only transaction
• No transaction affects another, concurrency control is used in isolation

Database Uses

• Databases are essential for businesses to track inventory, orders, payroll, accounting, shipping, and transportation 24/7
• Educational institutions use databases for student records from enrollment to transcripts
• Non-profits use databases to track donations and volunteers
• Household and family use databases for birthdays, bills, addresses, and collections
• Retailers store customer data for tailored discounts
• Specialist data is commonly included -The police use databases for criminal records -Schools use databases to record student attendance and sickness records -Hospitals use databases to store patient health histories -Governments use databases to track income tax payments

Database

• A database is a collection of related facts and figures that can be processed to produce information
• Data represents recordable facts which produce information, for example, marks to get average marks

Database Management System

• A database management system (DBMS) optimizes data storage for easy retrieval, manipulation, and information generation

Characteristics of a Modern DBMS

• A modern DBMS is more realistic, using real-world entities with behaviors and attributes in its design like a school database which may use students as an entity and their age as an attribute
• Relation-based tables: DBMS allows entities to form relations into tables, structure is apparent by the table names
• Isolation of data and application: A database system differs from its data; it actively works and organizes data and stores metadata, that is, data about data
• Less redundancy: DBMS follows Normalisation rules, reducing data redundancy mathematically
• Consistency: DBMS remains consistent using methods to detect inconsistency
• Greater consistency is achieved compared to file-processing systems
• Query Language: DBMS has a query language, efficiently retrieving and manipulating data with various filtering options
• ACID Properties: DBMS follows Atomicity, Consistency, Isolation, and Durability for healthy transactions in multi-transactional environments
• Multiuser and Concurrent Access: DBMS supports multiple users manipulating data in parallel; it restricts transactions accessing the same item, users are unaware of this restriction
• Multiple views: A DBMS offers tailored views for users, sales vs. production, so they have a concentrated view
• Security: Multiple views offer security by limiting access to data from other users or departments
• DBMS offers methods to constrain data while entering and retrieving, offering security Features enable tailored views
• Example: sales department cannot see purchase data
• It's difficult for miscreants to break the code because a DBMS isn't saved on disk like a file system

DBMS Users

• A DBMS has different types of users with different permission levels for different purposes like data retrieval and data backup
• Main User Categories: Administrators, Designers, & End Users -Administrators: They maintain the DBMS, administer the database and look after after its usage, applying access profiles and limitations for security; they also handle resources and software/hardware maintenance -Designers: The designers design the database keep a close watch on what data should be kept and in what format; identifying a set of entities, relations, constraints, and views -End Users: Reaping the benefits of the DBMS; simple viewers and sophisticated business analysts are end users

DBMS Architecture

• Database management systems can be centralized, decentralized, or hierarchical
• The architecture can be single-tier or multi-tier
• N-tier architecture divides the system related but independent modules for modification or flexibility
• In a 1-tier architecture, users directly interact with the DBMS.
• It lacks convenient tools for end-users and is preferred by database designers & programmers

2-Tier Architecture

• Requires an application layer for DBMS access
• The application tier are independently of the operation, design and programming of the database

3-Tier Architecture

• Separates tiers based on user complexity and data usage is common in DBMS design
• The Database (Data) Tier resides at this tier which contains query processing languages, data relations and data constraints
• The Application (Middle) Tier contains application servers and programs that access the database, providing an abstracted database view; acting as a mediator
• The User (Presentation) Tier operates with end-users only know what the database offers and generates multiple views reside in application tier

Modifiable and Independent Architecture

• Multiple-tier database architecture is highly modifiable, because all its components are independent
• Data models define the logical structure, introducing abstraction; how data is connected, processed, and stored
• Early data models stored all data in the same plane, were prone to duplication and update anomalies

Database Advantages and Disadvantages

• Advantages:
  • Reduced data redundancy
  • Reduced updating errors and increased consistency
  • Greater data integrity and independence from applications programs
  • Improved data access through host/query languages
  • Improved data security
  • Reduced data entry, storage, and retrieval costs
  • Facilitated development of new applications
• Disadvantages:
  • Complex, difficult, and time-consuming to design
  • Substantial hardware and software start-up costs
  • Damage to affects applications
  • Extensive conversion costs when moving from a file-based system
  • Initial training required for programmers and users

Database Design

• In database design, the organization of data occurs according to a database model
• The designer decides what the data elements must store and how they interrelate; and fit data into the database model
• Database design classifies and identifies relationships
• Ontology is the theoretical data representation

Determining Data Storage

• Database design is separate from data expertise
• Domain leaders determine what to store in the database, determined by Requirement Specification which is part of requirements analysis

Determining Data Relationships

• Database designers must identify data dependencies
• Changing data can have non-visible ramifications
• For example, an address depends on a name, as a name uniquely determines an address

Logical Data Structuring

• The data is arranged once relationships and dependencies are determined into a logical structure to be mapped
• Relational databases use tables; while object-oriented databases correspond directly to objects used by the object-oriented language
• Related data is structured such that dependent objects are placed into tables and relationships can be stored as links

ER Diagram (Entity-Relationship Model)

• It helps to design databases efficiently
• Attributes in ER diagrams are shown as an oval with the name representing the attribute inside; they always contain an object connected
• Entity-Relationship (ER) Model is based on the notion of real-world entities and relationships
• It creating entity sets, etc, best used to conceptualize a database
• ER Model is based on: -Entities and their attributes -Relationships among entities

Entity

• It is a real-world object with properties called attributes whose values are domains
• A student in a school with traits like name, age, or class would be a good example
• Relationship are a logical association to map by cardinality such as one to one etc

Relational Model

• The most popular data model in DBMS is the Relational Model which defines a table as an n-ary relation & it is more scientific a model
• Data is stored in related tables which can be normalized as can values
• Each table contains a unique value derived from the same domain

Database Schema

• The database schema represents the entire database's structure, organizing data, relationships, and data constraints, where its entities and their relations are defined here
• Database designers design schema to help programmers understand the database

Types of Database Schemas

• Physical Database Schema: Which shows the data storage, detailing to storage like files, indices, etc
• Logical Database Schema: This contains constraints in tables, views, and integrity constraints

Database Instance

• Distinguish between the database schema and the database instance individually
• The database schema defines structure, while the database instance contains the data at a moment
• A DBMS maintains the validity of every database instance

Data Independence

• A database contains user data and metadata for easy access
• As a DBMS expands, changes are sometimes needed to user queries and data access
• Metadata follows a layered architecture, and its impact can be tedious and have high complexity

Logical Data Independence

• The data is about the data in a database and is stored by the DBMS for information about how data is managed behind the scenes

Physical Data Independence

• Physical independence enables modification of the disk, but no difference in how a user would query

Codd's 12 Rules

• Edgar F. Codd designed 12 rules about how databases must be designed to be considered to follow relational guidelines

Information Rule

• All data must be stored as table cells

Guaranteed Access Rule

• Every data element should be accessible by table name, primary key, and attribute name.

NULL Values

• Every value must be handled systematically which is important because a NULL value must be a missing value, whether it is or isn't applicable

Active Online Catalog

• The database structure must be in a data dictionary accessed by users through queries.

Data Sub-Language Rule

• Data can only be accessed with a certain standard for language that supports things like data definition
• Some data definitions that apply would be, data manipulation, and transaction management through an application

View Updating Rule

• Anything that is updated must also be updated by the system

High-Level Update, Insert, and Delete Rules

• Database must support high-level insertion, update, and deletion.

Physical Data Independence

• The DB must be independent, and its structure may not impact applications.

Logical Data Independence

• The data must be independent of application, if there is a change it must not impact applications.

Integrity Independence

• A database is independent, and constraints can be modified independent through an interface

Distribution Independence

• End-users must not know the data is different on each location.

Non-Subversion Rule

• Data should not be subverted in terms of constraints

Constraints

• Every relation has rules that must hold and must have Relational Integrity Constraints or, key, domain, & refrential

Key Constraints

• Each relation has a minimal subset of attributes acting as a unique identifier; key, tuple, and possible candidate keys

Attributes

• There must be no indentical values and may not have NULL values

Domain Constraints

• Specificity of attributes must exist and exist in their own range

Referential Integrity Constraints

• Foreign keys are key to relationships

DBMS - File Structure

• Data stored in file formats, sequences of records that may get turned into disk blocks mapped to certain records

File Organization

• It is defined data being mapped for file data to be accessed which can be sequential or indexed

Heap Organization

• The file has allocated blocks for it in accounting and can be managed without anything else.

Sequential Organization

• Where file records contain data field like a key, in order and practically too store

Hash Organization

• Computation on some fields are applied

Clustered Organization

• It is not as well related; it may use multiple searches

File Operations

• Update and Retrieval
• There is insertion, deletion, and filtering

Open

• Files can be read and written if there is access and alterations to data

Locate

• A file pointer will find to write or read data

Read

• All data will point to a location

Write

• Can be done dynamically

Close

• Some operations may be removed, and data may be altered or stored

Data Sorting

• The location of files vary in the manner they are clustered or how they're set up

Sequential File

• Accessing in specific orders by their sorted keys

Direct Files

• Randomized but can be accessible for storing

Serial Files

• Used in tape for storage

Indexed-Sequential Method

• Locating records with the computer to store
• Can be magnetic

States of Transactions

• They stay active, which is ongoing
• The partially is about the operations finalizing
• Failed transactions may not happen if checked by the system
• Aborted ones could be managed to revert where they were previously
• Where the operation successfully finished

Concurrency

• It is important to control transactions with protocols for isolation or serialization based between locks from what time is stamped

Based Protocols

• They don't write data until there is an appropraite data on lock

Binary Locks

• Can be locked or unlocked

Shared/Exlusive

• The locking will allow or lead to inconsistencies

Structured Query Language

• Can communicate with Oracle databases and is easy to use with different capabilities
• Its Data language includes options of Select functions, Insert, Update/Delete , Merge

Definitions

• Different definitions on how tables get created , altered , or dropped
• There should be control for privileges that involve different rules and syntax

Creating Tables for Table Users

• When you have to create table statements you must provide the following qualities: table rules with lengths and a different arrangement for different tables, columns and data types
• Different rules may involve a way to charter to have 1-30 carters with different strings
• Different columns must involve different decimal types such as a character,integer , or floating point
• You should not alter a long type colum in these tables

Coding by SQL Types

• Some types of code, system to be applied in coding could be structured by connectives or table users
• One must add different tables into the code that are arranged in types graded with values
• After applying the coding one may DESCRIBE a list or its structure

Writing SQL Statements

• It is recommended that there be readable key words that may be capitalized
• Must choose to select columns with a report and specify which ones will have access to a report that is displayed

Alais

• Is used to define a column feild for statements in a payment
• Arithmetic expressions must involve descriptions where the operator can follow mathematical rules

Rows, statement usage and clause

• A duplicate must be separated, clause are required that can follow the set value to what conditions should occur

Logical Conditions

• They should be true or what may not apply
• Order of clause exists for those that may have a ascending or descending effect
• Update commands allow for sets to be applied for employee id
• This also applies vice versa
• Remove or delete and keep records only

Commands to be applied in the statement example or process

• There rollback option for transactions used in language processing for data mainpulation
• While also there is COMMIT that insures records permanency that is implemented into data manipulation
• Different subqueries can be used and removed to table structures of coding and syntax

Customize

• Customize single row tables functions to show trim
• Lower function examples that could be applied
• Different numbers for roundness can be coded for functions
• This applies when working with dates with different characters

Reporting

• For reporting different avg counts min and mix, those have variance
• Coding has certain limits in language that must be applied for tables to follow different key strokes
• Different tables have rules
• Foreign keys apply constraints too different tables
• The department can run to be a master role in the coding
• Tables may get altered to add a certain part of the coding for an id

Objects used for coding

• Table- Basic unit of storage
• View- Logically represents subsets of data from one or more tables
• Sequence- Generate numeric values
• Index- Improves the performance of some queries
• Synonyms- Gives alternative names to objects

View

• The view of a logical table will be based on other views and tables
• Their advantage is to restrict different accesses through independence

Creating and modifying a table

• Views can be done by either replacing or creating them views can also remove them
• Different tables may be data dictionaries with structures in language