DBS0902-Database_Systems_Review

– The Data-Manipulation sublanguage for querying databases and for modifying the database
• But SQL also includes a data-definition component for describing database schemas.
• Domain is a particular elementary type associated with each attribute of a relation, which can be included for each attribute in a relation schema. Such as Movies(title:string, year:integer, length:integer, genre:string)
2009-4-28
4
Schemas(模式)
• Relation schema(关系模式) = relation name and attribute list (a set of attributes).
– Optionally: types of attributes. – Example: Movies(title, year, length, genre)
• • Queries, retrieve information Modifications, change the database
3. Constraints on the data(数据约束), limitations on what the data can be, such as data types, value ranges, etc.
Contents
• • • • • • The Relational Model of Data Design Theory for Relational Databases High-Level Database Models Algebraic and Logical Query Languages The Database Language SQL Constraints and Triggers
– – relational model(关系模型)： tables(表); semistructured model(半结构模型) ： trees/graphs(树/ 图).
2009-4-28
2
Data Model(数据模型)-2
2. Operations on the data(数据操作), the actions which can be performed on the data. Such as
2009-4-28 9
Keys of Relations(关系键)
• A set of attributes forms a key (关键码, 键) for a relation if there is no two tuples in a relation instance to have the same values in all the attributes of the key. • For Movies, “title” and “year” form a key, which can be represented in its schemas: Movies(title, year, length, genre) • Artificial Keys can be defined, such as employee ID’s. There can be several choices of key, such as employee ID’s and employee Social-Security No.
– The Data-Definition sublanguage for declaring database schemas
2009-4-28 11
Relations in SQL
• Stored relations, which are called tables(表). A relation that exists in the database. • Views(视图), which are relations defined by a computation. These relations are not stored, but are constructed. • Temporary tables(临时表), which are constructed by the SQL language processor.
title Gone with the Wind Wayne’s World Star Wars
2009-4-28
year 1939 1992 1977
genre drama comedy sciFi
length 231 95 124
8
Relation Instance(关系实例)
• A set of tuples for a given relation is called an instance of the relation. (Gone with the Wind, 1939, 231, drama) (Star Wars, 1977, 124, sciFi) (Wayne’s World, 1992, 95, comedy) is an instance of the Movies. • The set of tuples that are in the relation “now” is called the current instance.
1
2009-4-28
Data Model(数据模型)-1
A Data Model is a notation for describing data or information, which consists of data structure, operation and constraints. 1. Structure of the data: Mathematical representation of data (数据的数学表示、概念模型).
2009-4-28 7
Equivalent Representations of a Relations(关系的等价表示)
• Relations are sets of tuples, not lists of tuples. • A relation consists of a set of attributes, not a list of attributes. Another representation of Movies is as follows.
2009-4-28 14
What is Relational Algebra?
• An algebra whose operands are relations or variables that represent relations. • Operators are designed to do the most common things that we need to do with relations in a database.
2009-4-28 12
An Algebraic Query Language
• • • • • • • • Overview of Relational Algebra(关系代数) Set Operations on Relations(关系的集合运算) Projection(投影) Selection(选择) Cartesian Product(笛卡尔积) Natural Joins(自然连接) Theta-Joins(θ连接) Naming and Renaming(命名和重命名)
– The result is an algebra that can be used as a query language for relations.
2009-4-28 15
Core Relational Algebra
• Union(并), intersection(交), and difference(差).
2009-4-28
13
What is an “Algebra”
• A algebra, in general, consists of operators and atomic operands. • Operands (操作数) --- variables or values from which new values can be constructed. • Operators (操作符) --- symbols denoting procedures that construct new values from given values.
• Database = collection of relations. • Database schema = set of all relation schemas in the database.
2009-4-28 5
Tuples(元组)
• A relation is a two-dimensional table. • The rows of relation, other than the header row containing the attributes names, are called tuples(元组, 一般称为“记录”). • Example: (Gone with the Wind, 1939, 231, drama) is a tuple of Movies. • The order of attributes’ values in tuples should be same as the order of attributes defined in the relation schemas.