Database 1. Basic Database Concepts

What is a database?

• A database is a way of storing information in a structured, logical way.

• Database Models: 'Relational Model'
  -- Databases consist of tables, which are linked
  -- Each table has a name
  -- column, row

Relational Model: attribute, tuple

image.png

• Attribute, attribute types
  -- 'null' value
  --constraints, for example, student number is not null, every student must have one

• Tables
  -- an individual cell in a table --> one piece of data
  -- each row has the same number and type of columns
  -- each row must be unique(no duplicate rows)

Primary Keys

• A primary key is an attribute in a table which can be used to uniquely identify each row
  -- you have only one primary key in a table
  -- each table will normally have several potential primary keys which is called candidate keys

Database Schema

• a description of the columns in a table can be called the table schema
  *example: Table(col,col,col...) Table(col,col,...)
  E.g. Student(studentNumber, name, address, DOB)
• Tables can be empty(no rows of data), but must have a schema

SQL = Structured Query Language

• Relational databases use SQL(structured query language) to get this information
• A standard language for accessing and manipulating information in relational databases
• The language consists of SQL commands
• SQL is an ANSI(American National Standards Institute) Standard, but there are slightly different versions(flavours)

SQL Flavours

• most implementations do not implement the entire standard
• but they all implement the basics
• Common Flavours:
  Oracle, MySQL, SQLite, PostgreSQL, MSQL(Microsoft), Microsoft Access