BI系列（一）：Intro to database and DB

Lesson1: Data characteristics

Define basic terminology and database characteristics

Database: a collection of persistent data that can be shared and interrelated.

• Persistency means that data reside on stable storage such as a magnetic disk.
• Inter-related means that data stored as separate units can be connected to provide a whole picture. That is, databases contain both entities and relationships among entities.
• Shared means that a database can have multiple uses and users and can be used simultaneously.

Provide an example

When taking a university course, we interact with the university database. There are entities, like students, courses and there are relationships between these entities (Entities - Relationships Diagram, ERD).

Lesson2: Organizational roles

Discuss organizational roles in functional areas and information technology departments

• Functional
  • Indirect
  • Paremetric
  • Power
• Information Technology
  • Data Specialists
  • Developer
  • Management

Lesson3: DBMS overview and database definition feature

Database Management System(DBMS)：A collection of components that supports the creation, use and maintenance of databases (support data acquisition, dissemination, maintenance, retrieval, and formatting) .

Common Features of DBMSs

• Database definition: Language and graphical tools to define entity types, relationships, integrity constraints, and authorization rights.
  • The Structured Query Language (SQL) is an industry standard language supported by most DBMSs. SQL can be used to define tables, relationships among tables, integrity constraints (rules that define allowable data), and authorization rights (rules that restrict access to data)
• Non-procedural access: Language and graphical tools to access data without complicated coding.
• Application development: Graphical tools to develop menus, data entry forms, and reports; data requirements for forms and reports are specified using non-procedural access.
• Procedural Language Interface: Language that combines non-procedural access with full capabilities of a programming language.
• Transaction Processing: Control mechanisms to prevent interference from simultaneous users and recover lost data after a failure.
• Database tuning: Tools to monitor and improve database performance.

Discuss the essential difference between DBMS and desktop software

DBMSs should be planned at first, such as the relationships among entities, but desktop software, like excel, is not planned at first.

Lesson4: Non-Procedural Access

Non-procedural Database Language: a language such as SQL that allows you to specify the parts of a database to access rather than to code a complex procedure. The users specifies the parts of a database to retrieve, not implementation details of how retrievals occurs. No loop statement is allowed.

Query: A database query can be either a simple data retrieval query or an action query that performs additional operations on the data, such as insertion, updating or deletion.

Application Development tools

Graphical tools are provided for building complete applications using forms and reports. Data entry forms provide a convenient tool to enter and edit data, while reports enhance the appearance of data that is displayed or printed (indentation style is much clearer for visualizing than tabular style). Non-procedural access makes form and report creation possible without extensive coding.

Procedural Language Interface

A method to combine a non-procedural language such as SQL with a programming language such as Java or Visual Basic in order to be a supplement of developed applications.

Lesson5: Transaction Processing Overview

A transaction is a unit of work that should be processed reliably without interference from other users and without loss of data due to failures

Transaction Processing: reliable and efficient processing of large volumes of repetitive work. DBMSs ensure that simultaneous users do not interface with each other and that failures do not cause lost work.

Lesson6: Data warehouse processing overview

• Transaction processing
  • Primary data in operational databases
  • Large volumes of transactions with relatively small amounts of data per transaction
  • Some reporting requirement for operations
• Business intelligence processing
  • Secondary data from operational databases
  • Substantial processing for transformations and integration
  • Large volumes of data for reporting

Lesson7: DBMS technology evolution

Brief evolution of database technology.png

Architectures of Database Management Systems

Data independence means that a database should have an identity separate from the applications (computer program, forms and reports) that use it. The separate identity allows the database definition to be changed without affecting related applications.

Three Schema Architecture

• The external level: the user level
• The conceptual level:defines the entity types and relationships
• The internal level: represents the storage view of the database whereas the conceptual schema represents the logical meaning of the database.

The internal schema defines files, collections of data on a storage device such as a hard disk. A file can store one or more entity types
described in the conceptual schema.

Schema mappings describe how a schema at a higher level is derived from a schema at a lower level. The mapping provides the knowledge to convert a request using an external view into a request using the tables in the conceptual schema. The mapping between conceptual and internal levels shows how entities are stored in files.DBMSs using schemas and mappings ensure data independence.

Parallel DBMS

Parallel DBMS is a DBMS capable of utilizing tightly-coupled computing resources (processors, disks and memories). Tight coupling is achieved by networks with data exchange time comparable to the time of the data exchange with a disk. Parallel database technology promises performance improvements (speedup and scaleup) and high availability.

Distributed Database

Distributed Database is a database in which parts are located at different network sites. Distributed Database technology supports local control of data, data sharing for requests involving data from more than one site, and reduced communication overhead.