boris/G4G0-1
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
Files
main
G4G0-1/Semester 1/DatabaseBackup/Week 1/Week 1 Database Systems.md
Boris 8c81ee28b7 first commit
2024-01-15 20:14:10 +00:00

4.5 KiB
Raw Permalink Blame History

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";