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

```sql
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

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

```sql
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

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

#### Constraints

- **NOT NULL**
	- Prohibits NULL from being the value in a column

```sql
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

```sql
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.

```sql
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

```sql
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.

```sql
CONSTRAINT <constraint name> <constraint>
```

```sql
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.

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

### Exercise

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

```sql
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

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

#### Destroying Tables

- Can be used with **\*** :)

```sql
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

```sql
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")
);
```