Database Overview

DBMS（Database-Management System）: a collection of interrelated data and set of programs to access those data.

 

Part 1: Database-System Applications

1. Enterprise Information

2. Banking and Finance

3. Universities

4. Airlines

5. Telecommunication

 

Part 2: Purpose of Database Systems

Keeping organizational information in a file-processing system has a number of major disadvantages:

1. Data redundancy and inconsistency

2. Difficulty in accessing data.

3. Data isolation

4. Integrity Problems

5. Atomicity Problems

 

Part 3: View of Data

A database system is a collection of interrelated data and a set of programs that allow users to access and modify these data. A major purpose of a database system is to provide users with an abstract view of the data. That is, the system hides certain details of how the data are stored and maintained.

 

1. Data Abstration

Physical level. The lowest level of abstraction describes how the data are actually stored. The physical level describes complex low-level data structures in detail.

Logical level. The next-higher level of abstraction describes what data are stored in the database, and what relationships exist among those data. The logical level thus describes the entire database in terms of a small number of relatively simple structures. Although implementation of the simple structures at the logical level may involve complex physical-level structures, the user of the logical level does not need to be aware of this complexity. This is referred to as physical data independence. Database administrators, who must decide what information to keep in the database, use the logical level of abstraction.

View level. The highest level of abstraction describes only part of the entire database. Even though the logical level uses simpler structures, complexity remains because of the variety of information stored in a large database. Many users of the database system do not need all this information; instead, they need to access only a part of the database. The view level of abstraction exists to simplify their interaction with the system. The system may provide many views for the same database.

 

 

2. Instances and Schemas

Databases change over time as information is inserted and deleted.

• The collection of information stored in the database at a particular moment is called an instance of the database.
• The overall design of the database is called the database schema.

 

Database systems have several schemas, partitioned according to the levels of abstraction.

The physical schema describes the database design at the physical level, while the logical schema describes the database design at the logical level. A database may also have several schemas at the view level, sometimes called subschemas, that describe different views of the database.

 

3. Data Mdels

Underlying the structure of a database is the data model: a collection of conceptual tools for describing data, data relationships, data semantics, and consistency
constraints. A data model provides a way to describe the design of a database at the physical, logical, and view levels.

 

The data models can be classified into four different categories:

1. Relational Model. uses a collection of tables(relations) to represent both data and the relationships among those data.   record-based model.

2. Entity-Relationship Model(E-R). The E-R data model uses a collection of basic objects, called entities, and relationships among these objects. An entity is a “thing” or “object” in the real world that is distinguishable from other objects.

3. Object-Based Data Model. extending the E-R model with notions of encapsulation, methods(functions), and object identity. The object-relational data model = object-oriented + Relational

4. Semistructured Data Model. Permits the specification of data where individual data items of the same type may have different sets of attributes. This is in contrast to the data models mentioned earlier, where every data item of a particular type must have the same set of attributes. The Extensible Markup Language (XML) is widely used to represent semistructured data.

 

Part 4: Database Languages

A database system provides a data-definition language to specify the database schema and a data-manipulation language to express database queries and updates.

 

1. Data-Manipulaiton Language(DML)

DML is a language that enables users to access or manipulate data as organized by the appropriate data model. The type of acess are:

• Retrieval of information stored in the database (Select)
• Insertion of new information into the database(Insert)
• Deletion of information from the database(Delete)
• Modification of information stored in the database(Update)

 

There are basically two types:

• Procedural DMLs require a user to specify what data are needed and how to get those data.
• Declarative DMLs (also referred to as nonprocedural DMLs) require a user to specify what data are needed without specifying how to get those data.

 

2. Data-Definition Language(DDL)

 

 

Part 5: Relational Databases

Relational Database based on : Relational Model && a collection of tables. It also includes DML && DDL.

 

1. Tables

Each table = multiple columns(Each column has a unique name)

 

2. DML(Data-Manipulation Language)

The SQL query language is nonprocedural. A query takes as input several tables (possibly only one) and always returns a single table.

select instructor.name
from instructor
where instructor.dept_name = 'History';

 

 

3. DDL(Data-Definition Language)

create table department
(dept name char (20),
building char (15),
budget numeric (12,2));

 

 

4. Database Access from Application Programs

Application programs are programs that are used to interact with the database in this fashion.

 

To access the database, DML statements need to be executed from the host language. There are two ways to do this:

• By providing an application program interface (set of procedures) that can be used to send DML and DDL statements to the database and retrieve the results.The Open Database Connectivity (ODBC) standard for use with the C language is a commonly used application program interface standard. The Java Database Connectivity (JDBC) standard provides corresponding features to the Java language.
• By extending the host language syntax to embed DML calls within the host language program. Usually, a special character prefaces DML calls, and a preprocessor, called the DML precompiler, converts the DML statements to normal procedure calls in the host language.

 

Part 6: Database Design