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?
- The data definition language.
- The data manipulation lan