数据库原理笔记

数据库原理笔记
b站链接：数据库原理及应用–中南大学–徐立臻

Theory of Database

introduction

definition

What is database?
A very large ,integrated collection of data.
Models real-world enterprise.
Entities
Relationships
What is DBMS?
A Database Management System(DBMS) is a software package designed to store and manage databases.
Files vs. Databases
Application must stage large datasets between main memory and secondary storage
Special code for different queries
Must protect data from inconsistency due to multiple concurrent users
Crash recovery
Security and access control
Why Use a DBMS?
Data independence and efficient access
Reduced application development time
Data integrity and security
Uniform data administration
Concurrent access ,recovery form crashes
Why study databases?
Shift from computation to information
at the “low end”:scramble to webspace (a mess!)
at the “height end”:scientific applications
Datasets increasing in diversity and volume.
Digital libraries,interactive video,Human Genome project,EOS project
…need for DBMS exploding
DBMS encompasses most of CS
OS,languages,theory,AI,multimedia,logic
Data ,Data Model and Data Schema
Data are symbols for describing the thing of real world. They are existing form of information.
A data model is a collection of concepts and definitions for describing data.
A schema is a description of a particular collection of data , using a given data model.
The relational model of data is the most widely used model today.
Main concept: relation ,basically a table with rows and columns.
Every relation has a schema, which describes the columns,or fields.
Levels of Model
Views describe how users see the data.
Conceptual schema defines logical structure
Physical schema describes the files and indexes used.
Example: University Databases
Conceptual schema:
Students(sid: string, name: string, login: string,age: integer, gpa:real)
Courses(cid: string, cname:string, credits:integer)
Enrolled(sid:string, cid:string, grade:integer)
Physical schema:
Relations stored as unordered files.
Index on first column of Students.
External Schema (View):
Course_info(cid:string,enrollment:integer)
Data Independence
Applications insulated from how data is structured and stored.
Logical data independence: Protection from changes in logical structure of data.
Physical data independence: Protection from changes in physical structure of data.
The History of Database Technology and its Classification
(1) According to the development of data model
◼ No management(before 1960’): Scientific computing
◼ File system: Simple data management
◼ Demand of data management growing continuously,DBMS emerged.
➢ 1964, the first DBMS (American): IDS, network
➢ 1969, the first commercial DBMS of IBM, hierarchical
➢ 1970, E.F.Codd(IBM) bring forward relational data model
➢ Other data model: Object Oriented, deductive, ER, …
(2) According to the development of DBMS architectures
Centralized database systems
Parallel database systems
Distributed database systems (and Federated database systems)
Mobile database systems
(3) According to the development of architectures of application systems based on databases(基于应用系统的体系发展)
Centralized structure : Host＋Terminal
◼ Distributed structure
◼ Client/Server structure
◼ Three tier/multi-tier structure
◼ Mobile computing
◼ Grid computing / Cloud computing
Centralized structure : Host＋Terminal
◼ Distributed structure
◼ Client/Server structure
◼ Three tier/multi-tier structure
◼ Mobile computing
◼ Grid computing / Cloud computing
Database System
◼ Applications + DBMS + Database + DBA
◼ DBMS is the core of database system
➢ High level user interfaces
➢ Query processing and optimization
➢ Catalog management
➢ Concurrency control and Recovery
➢ Integrity constraints checking
➢ Access control
Life cycle of database systems
Database system planning
◼ Database designing
◼ Database establishing and loading
◼ Database running, managing and maintaining
◼ Database extending and restructuring

Data Model*
2.1 Hierarchical(层次) Data Model
◼ Record and field
◼ Parent-Child relationship (PCR): the most basic data relationship in hierarchical model. It expresses a 1:N relationship between two record types.
Hierarchical Data Schema
◼ A hierarchical data schema consists of PCRs.
◼ Every PCR expresses one 1:N relationship
◼ Every record type can only have one parent
Virtual Record
◼ In real world, many data are not hierarchical.It is hard to express them directly with PCR.
➢M:N(多对多) relationship between different record types
➢A record type is the child of more than two PCRs.
➢ N-ary relationship.(多元关系)
◼ To avoid redundant, virtual record is introduced to express above relationships. It is a pointer in fact.
2.2 Network(网状) Data Model
main
◼ It breakthrough the limit of hierarchical structure, so can express non-hierarchical data more easy.
◼ Record and data items: data items are similar as field in hierarchical model, but it can be vector.
◼ Set : express the 1:N relationship between two record types.
◼ LINK record type: used to express self relationship, M:N relationship and N-ary relationship.
Example of Network Data Schema
2.3 Relational Data Model
Attributes(属性) and Domain(域)
◼ The features of an entity in real world are expressed as attributes in relational model E.g. a student can be described with the attributes such as name, sid, gender, age, birthday, nationality, etc.
◼ The value scope of an attribute is called its domain.
➢Atomic(原子的) data — 1NF(一范式)
➢ Null
Relation(关系) and Tuple(元组)
◼ An entity of real world can be expressed as one or more than one relations(表).
◼ A relation is a N-ary relationship defined on all of its attribute domain.
◼ This is called the schema of R, and n is the number of attributes, called the degree of R. Ai (1≤i ≤n) is attribute name.
◼ An instance (value) of R can be expressed as r or r®,
◼ Relation is also called table. Attribute is also called column, and tuple is also called row.
Primary Key（主键）
◼ A set of attributes is a candidate key（候选键） for a relation if :
1. No two distinct tuples can have same values in this set of attributes, and
2. This is not true for any subset of this set of attributes.
➢ Part 2 false? A superkey.
➢ If there’s >1 key for a relation, one of the keys is chosen (by DBA) to be the primary key, and the others are called alternate key.
➢ If the primary key consists of all attributes of a relation, it is called all key.
◼ That means, the key can decide a tuple uniquely.
◼ E.g., sid is a key for Students. (What about name?) The set {sid, gpa} is a superkey.
Foreign Keys（外键）, Referential Integrity
◼ Foreign key :
◼ E.g. sid is a foreign key referring to Students:
➢ Enrolled(sid: string, cid: string, grade: string)
➢ If all foreign key constraints are enforced,referential integrity is achieved, i.e., no dangling references.
➢ Have you forgotten soft link?

User Interfaces and SQL Language*

Database Management Systems*

The Security and Integrity Constrain

Database Design*

Distributed Database Systems

New Research and Application Fields