G4G0-1/Semester 1/DatabaseBackup/Week 4/Week 4 Database Systems - Data Description Language.md

Lecture 1 (13:00)

Intro

• Create relations CREATE TABLE
• Modify relations ALTER TABLE
• Destroy relations DROP TABLE

Common Datatypes

• CHARACTER(L) / CHAR(L)
  • Fixed length containing L letters
  • Fewer characters, padded with (usually) spaces
• CHARACTER VARYING(L) / VARCHAR(L)
  • Variable length string that may hold up to L characters
  • No padding
• INTEGER / INT
  • Signed whole number
  • Range of possible values is dependant on the DBMS
• NUMERIC(P,S)
  • Signed, fixed point number
  • P = Precision (# Digits)
  • S = Scale (# Digits to the right of decimal place)
  • NUMERIC(5,2) = -999.99 to 999.99
• BOOLEAN
  • 3 values - TRUE, FALSE, UNKNOWN
• DATE
  • YYYY-MM-DD
• TIME
  • HH:MM:SS
• TIMESTAMP
  • YYYY-MM-DD HH:MM:SS
• INTERVAL
  • Refers to period of time. ex. Time Span.
• BINARY LARGE OBJECT(L) / BLOB(L)
  • Large, variable length binary string up to L bytes of length
• NULL
  • Indicates value is unknown
  • Valid for any data type

Creating Tables

CREATE TABLE <table name> (
<column name> <data type> [<default value>] [<column constraint>],
<column name> <data type> [<default value>] [<column constraint>],
...
...
[<table constraint1>],
[<table constraint2>],
...
);

Examples

CREATE TABLE students (
	studentID INTEGER,
	title VARCHAR(4),
	name VARCHAR(20),
	city VARCHAR(20)
);

CREATE TABLE courses (
	courseID CHAR(4),
	courseName VARCHAR(20),
	startDate DATE,
	endDate DATE
);

Default Values

• When using INSERT, any columns without a specified value are assigned to default
• Unless specified, default value is NULL
• By specifying DEFAULT <value expression> in CREATE TABLE, we can assign default values

CREATE TABLE courses (
	courseID CHAR(4) DEFAULT ("XXXX")
	...
);

Constraints

• NOT NULL
  • Prohibits NULL from being the value in a column

CREATE TABLE students (
	...
	name varchar(20) NOT NULL,
	...
);

This example shows that any values in the column name must not be NULL

• UNIQUE
  • Forces distinct column values.
  • Allows NULL values

CREATE TABLE employee (
	...
	niNumber CHAR(11) UNIQUE,
	...
);

This example shows that every value in the column niNumber must be unique.

• PRIMARY KEY
  • Uniquely identifies a tuple (row) in the table.
  • May not be NULL.

CREATE TABLE employee (
	employeeID INTEGER PRIMARY KEY,
	...
);

This example shows that the column employeeID is the primary key of the table.

• FOREIGN KEY
  • References another table with a unique link

CREATE TABLE students (
	studentID INTEGER PRIMARY KEY,
	...
	course CHAR(4) REFERENCES courses(courseID)
);

CREATE TABLE courses (
	courseID CHAR(4) PRIMARY KEY,
	...
)

Naming Constraints

• CONSTRAINT
  • If a constraint violation occurs, the DBMS will notify which constraint was violated.
  • Constraints can be deleted by name.

CONSTRAINT <constraint name> <constraint>

CREATE TABLE students (
	studID INTEGER CONSTRAINT student_pk PRIMARY KEY,
	title VARCHAR(4),
	name VARCHAR(20) CONSTRAINT name_not_null NOT NULL,
	city VARCHAR(20) CONSTRAINT city_default DEFAULT "Salford",
	course CHAR(4) CONSTRAINT student_fk REFERENCES courses(coursesID)
);

CREATE TABLE courses (
	coursesID CHAR(4) CONSTRAINT course_pk PRIMARY KEY,
	courseName VARCHAR(20)
	...
);

Table Constraints

• Example of composite primary key: supply(partID, supplierID)
• Multiple attributes cannot be assigned the constraint PRIMARY KEY, they must be assigned it in a list.

CREATE TABLE supply (
 partID INTEGER,
 supplierID INTEGER,
 PRIMARY KEY (partID, supplierID)
);

Exercise

CREATE TABLE team (
	teamID CHAR(4) CONSTRAINT team_pk PRIMARY KEY,
	name VARCHAR(20) CONSTRAINT name_not_null NOT NULL
);

CREATE TABLE member (
	memberID CHAR(4) CONSTRAINT member_pk PRIMARY KEY,
	niNumber CHAR(11) CONSTRAINT ni_unique UNIQUE,
	address VARCHAR(40) CONSTRAINT address_default DEFAULT "Salford",
	teamID CHAR(4) CONSTRAINT team_fk REFERENCES team(teamID)
);

Lecture 2 (15:00)

Modifying Tables

ALTER TABLE <table name>
ADD <column name> <data type>;

Destroying Tables

• Can be used with * :)

DROP TABLE <table name>;

3 Areas of SQL

• Data Manipulation Language
  • Retrieve and modify data.
• Data Description Language
  • Define structure of the data.
• Data Control Language
  • Used to restrict access by certain users.

Tutorial

CREATE TABLE doctors (
	doctor-id CHAR(4) PRIMARY KEY,
	doctor-name VARCHAR(20) NOT NULL,
	telephone-number INTEGER(12) UNIQUE
);

CREATE TABLE treats (
	treat-id CHAR(4) PRIMARY KEY,
	doctor-id CHAR(4) UNIQUE,
	patient-id CHAR(4) UNIQUE,
	drug VARCHAR(30),
	treatment-date DATE,
	FOREIGN KEY (doctor-id, patient-id)
);

CREATE TABLE patients (
	patient-id CHAR(4) PRIMARY KEY,
	title CHAR(3),
	first-name VARCHAR(20) NOT NULL,
	surname VARCHAR(20) NOT NULL,
	address VARCHAR(30) DEFAULT ("Salford")
);