5003 Storing and Retrieving Data

First class

生僻单词

retrieve
Concurrency
extraneous
diamond
tuple

What is database

A database system consists of two components
Database (DB) and Database Management System (DBMS)
The DB contains the data
The DBMS is software that stores, manages and
retrieves the information in the DB

Database Overview

Data Models

database

A database models a real-world enterprise, e.g. CityU

data model

A data model is a formal language for describing data

schema

A schema is a description of a particular collection of data using a particular data model

The most widely used data model is the relational data model

relational data model

The main concept relation, essentially a table with rows and columns
Each relation has a schema, e.g. Enrolled(sid: string, cid:
string, gpa: real)

Data Abstraction

Physical schema

Relations stored as unordered files.
Index on first column of Students.

Conceptual (or logical) schema

Courses(cid: string, cname:string, credits:integer)
Students(sid: string, name: string, login: string, age: integer, gpa:real)
Enrolled(sid:string, cid:string, grade:string)

External (or view) schema

Course_info(cid:string,enrollment:integer)

Data Independence

Physical data independence

Allows the physical schema to be modified without rewriting application programs
e.g. adding or removing an index or moving
a file to a different disk

Logical data independence

Allows the logical schema to be modified without rewriting application programs
e.g. adding an attribute to a relation

Efficient Access

Concurrency Control

Data Integrity

Transactions

A transaction is a sequence of reads and writes to the DB caused by one execution of a user program

Transactions must have the ACID properties:
Atomic: all or nothing
Consistent: the DB must be in a consistent state after
the transaction
Isolated: transactions are performed serially
Durable: the effects of a transaction are permanent

Database Languages

Database Languages

A database language is divided into two parts
Data definition language (DDL)
Data manipulation language (DML)
Structured query language (SQL) is both a DDL and a DML

Data definition language (DDL)

The DDL allows entire databases to be created, and allows integrity constraints to be specified
Domain constraints
Referential integrity
Assertions
Authorization

The DDL is also used to modify existing DB schema
Addition of new tables
Deletion of tables
Addition of attributes

Data manipulation language (DML)

The DML allows users to access or change data in a database

Retrieve information stored in the database
Insert new information into database
Delete information from the database
Modify information stored in the database

Database Users

SDSC5003

SDSC5003 Topics

DB specification and implementation
Database design – the relational model and the ER model
Creating and accessing a database
Relational algebra
Creating and querying a DB using SQL
Query optimization, transaction processing, …
Database application development
Spark/Hadoop (more of data processing systems)
Basic programming model

Exercise

Which of the following plays an important role in representing information about the real world in a database?

1. The data definition language.
2. The data manipulation lan