## Lecture 1 (13:00) Organisation

### 32741 ( Module Code )

### Module Information

- 67 hours of private study across all modules in semester 1
- Exercise booklet on blackboard
- Semester 1 Coursework 50%
- Semester 2 Coursework 50%

### Reading

- Preface, C.1 & C.2 of exercise booklet.

## Lecture 2 (15:00) Introduction

### Applications of Databases

- Supermarket
	- Barcode DB
	- Price Retrieval
	- Stock Control
- Credit Card
	- Credit Limit Check
	- Security Check
- Other
	- Library
	- Websites

### File-Based Systems

- Attempt to digitise information
- Collection of application programs
- Each performs a front end interface
- Each manages and defines its own data
- Limitations lay the foundations to what we have now

#### Limitations

- **Separation** and **isolation** of data
- **Incompatible** file formats
- **Fixed queries** (written by the developer, not the end user) / proliferation of application programs
- Data **duplication**
	- Costs money and time to enter data multiple times
	- **individual**, **independent** instances of data
	- Additional storage space taken
	- **Loss of data integrity** and parity (consistency lost), since they are individual sources of data.
	- Ex. Payroll and Personnel data duplicated across DB
- Data **dependence**
	- **Physical structure** / storage of data / records is defined in **application code**
	- Difficult to change structure
	- Programs have to be modified if structure needs changing

### Databases

- Shared collection of **logically related** data (+ **description**), designed to fit needs.
- **Self-describing** collection of integrated records

#### Database System

1. Collection of data stored on files
2. Database Management System ( Interface )
3. Variety of users

### Database Management System

- Software that enables the **creation**, **definition**, and **maintenance** of databases. and provides **controlled access**.
- User Interaction is provided through User Programs (Queries), examples of include:
	- Add Data
	- Delete Data
	- Update Data
	- Retrieve Data

#### Functions of DBMS

- Data Integration
	- Stored **efficiently**
	- Minimises **duplication** and **redundancy**
- Data Integrity
	- No **corruption** or **inconsistency**
- Data Security
	- Data is not lost or made inconsistent through system failures, or through deliberate corruption.
- Data Independence
	- **Isolates users** from physical data
	- User has logical model interface of database

#### Users of a DB Environment

- Data Admin
	- Consults and advises senior management
	- Ensures database serves its purpose
- Database Admin
	- Technically oriented
	- Ultimate control over data structure and access
- Database Designers
	- Designs structure of database to cater to needs
- Application Programmers
	- Designers of end user applications
- End Users
	- Clients who require access to the database
	- No control over function of the applications
	- May have little to no knowledge on back-end

#### Advantages over File-Based

- **De-Duplication**
- Lowers risk of inconsistency
- More users share more data
- Improved **Security**
- Improved **accessibility** and **responsiveness**
	- Department boundaries crossed, data is **no longer independent**
	- Provides **query** language
	- **On-demand** access
- Improved **productivity**
	- Provides many standard functions programmers would have to hard code
- Improved **maintenance**
	- Separates data descriptions from applications
- Improved **backup** and **recovery**

#### Disadvantages

- **Complicated**
- **Large** / Heavy
- **Expensive** setup
- May incur **additional storage** space
- **Cost** of conversion
- **Performance** may not be as good as file-based
- Higher **impact of failure**

### Relational DBMS

- Dominant DBMS in use
- Estimated sales of $15-20m/year
- Established in 1970
- Simple logical structure

#### Relational DB

- Stored in files called relations (tables)
	- Consists of rows and columns
	- Carries data on one kind of entity ex. Cars
- Commonly addressed as entity-name(attributes). Ex. Cars(Model, Age, Colour)
- Primary Key - The identifying attribute of a table. Ex. Registration Plate (Unique)
- Goal is to remove as much redundant data as possible.
- Foreign Key - another unique key to provide an index to another table.
	- Attribute that is linked to the primary key of another table

### Reading

- C.1 Connolly & Begg (2014 & 2004)

## Workshop 1 (9:00)

```
SELECT *
FROM [Customers];
WHERE [Country] = "Germany";
```

```
SELECT *
FROM [Customers];
WHERE [Country] = "Germany" AND [ContactTitle] = "Sales Representative";
```