COMP4039 - 1.Databases

1. Wkiipedia Definition

A database is an organized collection of data, generally stored and accessed electronically from a computer system.

2. Programming data structures and databases

• You have to find a way to map data structures in your programming language to data structures held by the (relational) database.
• OO data structure <——> DBs

3. The world’s simplest databases

3.1. File-based databases

• Linux configuration files: /etc/xxx
• DNS/Network configuration files …

3.2. Problems

• No standards
• Data duplication
• Data dependence
• No way to generate complex queries (e.g., JOIN)
• No provision for concurrency, security, recovery, etc.

4. DBMS - Database Management Systems

4.1. Definition

A database is a system to allow multiple users to share the same information
A Database Management System (DBMS) is the software that implements a database

• a collaborative system
• uniqueness
• data integrity/consistency

4.2. What a DBMS does?

• store data
• manage change (updates)
• organise data
• retrieve data
• retain privacy (security)
• ensure reliability & consistency (no redundancy, transactional model, etc.)

4.3. What a DBMS provides?

SQL=(DDL, DML, DCL)

• Data Definition Language (DDL)
  • e.g., ‘CREATE’ table
• Data Manipulation Language (DML)
  • e.g., ‘INSERT’ row
• Data Control Language (DCL)
  • e.g., ‘GRANT’ privilege on table to user

4.4. DBMS components

• Data dictionary: stores information about database objects
• Data dictionary compiler: extracts database information and stores it in the data dictionary
• Query processor: parses / interprets and optimises user queries (caching?)
• Transaction manager: communicates with recovery manager and scheduler to ensure data consistency in multi-user environments
• Storage manager: deals with concrete file I/O etc.

4.5. ANSI / SPARC architecture

Three tier architecture:

• internal level/schema: For systems designers (OS, file system level , provides platform independent service for conceptual level)
• Conceptual level/schema: For database designers (structure for all users, abstracted from internal level)
• External level/schema: For database users (user view, subject to access permissions, multiple possible views)
  Not fully implemented in any DBMS: Eg. external level blended with conceptual level (MySQL)

4.6. Database systems programmers

• Database systems programmers: writes the DBMS software
• Database administrators (DBA): designs and manages the database system
• Applications developers: write the client software that allows end users to interface with the DBMS
• End users: use the information provided by the database to achieve a personal or organisational goal

5. Lab Exercise

5.1. Exercise Guide

• Download lab-1-countries-data.csv

5.2. My Answer

-- Create Database
CREATE DATABASE COMP4039;

-- Use Database
USE COMP4039;

-- Create Table
CREATE TABLE countries (
    countries_name VARCHAR(50),
    countries_area INT,
    countries_population INT,
    countries_gdp BIGINT(20),
    countries_currency VARCHAR(20),
    countries_capital VARCHAR(50),
    ountries_tld VARCHAR(10)
);

-- 1. Print the GDP of all countries in the database.
SELECT countries_name, countries_gdp
FROM COMP4039.countries;

-- 2. Print the GDP of all countries,in descending order.
SELECT countries_name, countries_gdp
FROM COMP4039.countries
ORDER BY countries_gdp DESC;

-- 3. Print the per-capita GDP of all countries in the database, in descending order.
SELECT countries_name, countries_gdp/countries_population per_capitaGDP
FROM COMP4039.countries
ORDER BY per_capitaGDP DESC;

-- 4. Display 2 places of decimals
SELECT countries_name, ROUND(countries_gdp/countries_population, 2) per_capitaGDP
FROM COMP4039.countries
ORDER BY per_capitaGDP DESC;

-- 5. Ignores very small countries with a land mass of less than 20,000.
SELECT countries_name, ROUND(countries_gdp/countries_population, 2) per_capitaGDP
FROM COMP4039.countries
WHERE countries_area >= 20000
ORDER BY per_capitaGDP DESC;

-- 6. Shows the names of countries that have a land mass in between 100,000 km2 and 200,000 km2.
SELECT countries_name
FROM COMP4039.countries
WHERE countries_area BETWEEN 100000 AND 200000;

-- 7. Shows the names of countries that start with the letter‘M’.
SELECT countries_name
FROM COMP4039.countries
WHERE countries_name LIKE 'M%';

-- 8. Lists the names of countries that end with‘...land’.
SELECT countries_name
FROM COMP4039.countries
WHERE countries_name LIKE '%land';

-- 9. The name of the country is the same as the name of the capital.
SELECT countries_name
FROM COMP4039.countries
WHERE countries_name=countries_capital;

-- 10. Total population of all of the countries.
SELECT SUM(countries_population)
FROM COMP4039.countries;

-- 11. Find the number of countries that have a total land mass of at least 500,000.
SELECT COUNT(*) num
FROM COMP4039.countries
WHERE countries_area >= 500000;

-- 12. Show the countries which have a per capita GDP more than that of Poland, in descending order of wealth.
SELECT countries_name, countries_gdp/countries_population
FROM COMP4039.countries
WHERE countries_gdp/countries_population > (SELECT countries_gdp/countries_population
                                            FROM COMP4039.countries
                                            WHERE countries_name='Poland')
ORDER BY countries_gdp/countries_population DESC;