Database Systems, Lecture notes of Technology

Download Database Systems and more Lecture notes Technology in PDF only on Docsity!

Database Systems

Nguyễn Văn Diêu

HO CHI MINH CITY UNIVERSITY OF TRANSPORT

2020

Kiến thức - Kỹ năng - Sáng tạo - Hội nhập Sứ mệnh - Tầm nhìn Triết lý Giáo dục - Giá trị cốt lõi

Table of contents

1 Overview

2 Relational Data Model

3 Relational Algebra Language

4 Structured Query Language

5 Integrity Constraints

6 Views and Index

Nguyễn Văn Diêu Table of Contents 2/

1.2. Databases Are Everywhere

• (^) Database = a large (?) collection of related data.
• (^) Classically, a DB models a real-world organization (e.g., enterprise, university).
  • (^) Entities (e.g., students, courses)
  • (^) Relationships (e.g., “Martin is teaching DBs in 2018/09”)
• (^) Changes in the organization = changes in the database.
• (^) Examples:
  • (^) Personnel records
  • (^) Students management
  • (^) Banking
  • (^) Airline reservations

Nguyễn Văn Diêu 1. Overview 4/

1.3. Scientific Databases (Examples)

• (^) Biology: e.g., DNA sequences of genes, amino-acid sequences of proteins, genes expressed in tissues (up to several Gigabytes)
• (^) Astronomy: e.g., location and spectra of astronomic objects (up to several Terabytes)
• (^) Physics: e.g., sensor measurements in particle physics experiments (up to several Petabytes)

Nguyễn Văn Diêu 1. Overview 5/

1.5. Database Management System (DBMS)

• (^) DBMS is a software package designed to store and manage databases.
• (^) Several brands, e.g.
  • (^) Oracle (Oracle)
  • (^) DB2 (IBM)
  • (^) SQL Server, Access (Microsoft),
  • (^) MySQL, PostgreSQL, HSQLDB, SQLite (open source)

Nguyễn Văn Diêu 1. Overview 7/

Use a DBMS?

• (^) Separation of the Data definition and the Program.
• (^) Abstraction into the simple Model.
• (^) Data independence and efficient access.
• (^) Reduced application development time.
• (^) Data integrity and Security.
• (^) Uniform data administration.
• (^) Concurrent access, recovery from crashes.
• (^) Support for multiple different views.

Nguyễn Văn Diêu 1. Overview 8/

2.1. Data Model

Data model is a notation for describing data or information. The description generally consists of three parts:

• (^) Structure of the data. Familiar with programming languages such as C: arrays, structures, objects. In the database world, data models referred to as a conceptual model to emphasize the difference in level.
• (^) Operations on the data.
  • (^) Operations that retrieve information: Set of queries.
  • (^) Operations that change the database: Set of modifications.
• (^) Constraints on the data. The way to describe limitations on what the data can be.

Nguyễn Văn Diêu 2. Relational Data Model 10/

Important Data Models

Today, there are three data models of importance for database systems:

• (^) Relational data model. Including object-relational extensions
• (^) Semi-structured data model. Including XML and related standards.
• (^) NoSQL data model. Four types of NoSQL databases are Document-oriented, Key-Value Pairs, Column oriented and Graph.

Nguyễn Văn Diêu 2. Relational Data Model 11/

2.3. Attributes

• (^) The columns of a relation are named by attributes.
• (^) An attribute describes the meaning of entries in the column below.
• (^) Each attribute belong to one data type , so that have Domain.
• (^) Domain : Set of possible atomic values, including Null value.
• (^) in Domain not permitted record structure, set, list, array, or any other type that can broken into smaller components.

Nguyễn Văn Diêu 2. Relational Data Model 13/

2.4. Schemes

• (^) Name of a relation and the set of attributes for a relation is called the relation scheme for that relation.
• (^) Show scheme for the relation with the relation name followed by a parenthesized list of its attributes.
• (^) e.g. STUDENT(ST-ID, ST-NAME, CLASS-ID)
• (^) The attributes in a relation scheme are a set, not a list.
• (^) A database consists of one or more relation schemes. That is called a relational database scheme or just database scheme.
• (^) In general: R ( A 1 , A 2 , ..., An )

Nguyễn Văn Diêu 2. Relational Data Model 14/

2.6. Relation Instances

• (^) Tuples in relation change over time.
• (^) A set of tuples for a given relation in time is an instance of that relation.
• (^) Denote: r(R)
• (^) e.g. STUDENT(ST-ID, ST-NAME, CLASS-ID) in time we see we have one relation instance ST-ID ST-NAME CLASS-ID S01 Nguyen Van Nam L S02 Nguyen Van Nam L S03 Tran Quoc Tuan L S04 Le Van Cuong L S05 Nguyen Van Cuong L

Nguyễn Văn Diêu 2. Relational Data Model 16/

2.7. Keys

• (^) A key of a relation R is a subset K of R such that for any distinct tuples t 1 and t 2 in r ( R ) then t 1 ( K ) ̸= t 2 ( K ) and no proper subset K ′ of K shares this property.
• (^) X is a superkey of R if X contains a key of R.
• (^) In R maybe more than one key.
• (^) Each key denote one underline.
• (^) e.g. STUDENT have one key ST-ID.

Nguyễn Văn Diêu 2. Relational Data Model 17/

3.2. Conditions

Let R, S with conditions:

1. Identical sets of attributes, domains for each attribute must be the same.
2. Attributes must be ordered to the same for both relations.

Nguyễn Văn Diêu 3. Relational Algebra Language 19/

3.3. Set Operators

Relation R and S : set of tuples.

1. R ∪ S: Union of R and S. R ∪ S = { t | t ∈ r (R) ∨ t ∈ s (S)}
2. R ∩ S: Intersection of R and S. R ∩ S = { t | t ∈ r (R) ∧ t ∈ s (S)}
3. R − S: Difference of R and S. R − S = { t | t ∈ r (R) ∧ t ∈/ s (S)}

Nguyễn Văn Diêu 3. Relational Algebra Language 20/