Databases and Database Connection in Java

Definitions

• A database is an organized collection of logically related data.
• Data refers to stored representations of meaningful objects and events.
• Structured data includes numbers, text, and dates, while unstructured data includes images, video, and documents.
• Information is processed data that increases knowledge in the person using it.
• Metadata is data that describes the properties and context of user data.
• Database Management Systems (DBMS) are used to create, maintain, and access databases.

Database Components

• A database typically consists of tables, fields (columns), and records (rows).
• Tables are collections of related records.
• Fields are single categories of data stored in a database (e.g., name, telephone number).
• Records are collections of related fields in a database (all fields for one customer, for example).

Relational Database

• A relational database ties data from several tables together using a common field.
• A relation is a table with non-duplicated rows.

Integrity Constraints

• Domain constraints specify possible values for an attribute.
• Primary key constraints uniquely identify records in a table and must be unique and unchanged.
• Foreign key constraints identify relationships between tables.

Data Concepts and Characteristics

• Entity relationships describe associations between two or more entities.
• One-to-one (1:1) entity relationships are not common.
• One-to-many (1:M) entity relationships are most common.
• Many-to-many (M:M) entity relationships require a third table to tie tables together.

Data Definition

• Data definition is the process of describing properties of data to be included in a database table.
• Each field is assigned a name, data type, description, and properties (size, format, allowable range, etc.).

The Relational Database Model

• Retrieving information from a database involves querying specific data that matches certain criteria.
• Queries specify which records to retrieve and which fields to display.
• Queries can be saved and reused.

SQL

• SQL (Structured Query Language) is the universal language for accessing relational database systems.
• SQL statements create tables, insert records, update records, query databases, and delete records and tables.
• SQL can be used on various database systems, including MySQL, Oracle, and MS Access.

Creating Database and Tables

• Create a database using the "create database" command (e.g., "create database javabook;").
• Create tables using the "create table" command, specifying fields, data types, and constraints.

Data Types

• char(n) : field consists of exactly n characters
• varchar(n) : variant length string with maximum n characters
• integer : integer number
• date : date
• float : decimal number

Data Manipulation Language (DML)

• DML is part of SQL responsible for working on data in database tables.
• DML includes inserting, updating, deleting, and retrieving data.

Inserting Data into Tables

• Insert data into tables using the "insert into" command, specifying column names and values.

Modifying Tables

• Update data in tables using the "update" command, specifying the table, column, and new value, and optionally a "where" clause.
• If the "where" clause is omitted, the update will apply to all records in the table.