166 lines
4.5 KiB
Markdown
166 lines
4.5 KiB
Markdown
## 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";
|
|
```
|