query languages and database design

query

command for a database that typically inserts new data, retrieves data, updates data, or deletes data from a database

• the four common queries are sometimes referred to as CRUD operations, an acronym for create, read update, and delete data

query language

a computer programming language for writing database queries

NoSQL

refers to a new generation of non relational databases. NoSQL originally meant ‘does not support SQL’ however, many NoSQL databases have added support for SQL, and it has come to mean ‘not only SQL’

SQL statement

a database command, such as a query that inserts, selects, updates, or deletes data

database design

a specification of database objects such as tables, columns, data types, and indexs. Database design also refers to the process used to develop the specification

3 phases of database design

1. analysis

2. logical design

3. physical design

analysis phase aka conceptual design and entity relationship modeling

specifies database requirements without regard to a specific database system. Requirements are represented as entities, relationships, and attributes.

ER diagrams

entities, relationships, and attributes are depicted in ER diagrams

• rectangles represent entitiies

• lines between rectangles represent relationships

• texts inside rectangles and below entity names represent attributes

logical design phase

implements database requirements in a specific database system. For relational database systems, logical design converts entities, relationship, and attributes into tables, keys, and columns. a key is a column used to identify individual rows of a table. Tables, keys, and columns are specified in SQL with CREATE TABLE statements

table diagram

the logical design is depicted in a table diagram. These are similar to ER diagrams but more detailed.

• Rectangles represent tables. Table names appear at the top of rectangles.

• Text within rectangles and below table names represents columns.

• Solid bullets (●) indicate key columns.

• Empty bullets (○) and arrows indicate columns that refer to keys.

  the logical design, as specified in SQL and depicted in a table diagram, is called a database schema

physical design phase

adds indexes and specifies how tables are organized on storage media

data independence

the principle that physical design never affects query results

application programming interface (API)

a library of procedures or classes that links a host programming language to a database. The host lnaguage calls library procedures, which handle details such as connecting to the databse, exewcuting queries, and returning results