70420403- Database Systems 数据库管理系统 Li Jianguojli@math.tsinghua.edu.cn
References • Raghu Ramakrishnan/Johannes Gehrke, Database Management Systems, 2nd Ed. , 清华大学出版社/McGraw-Hill, 2000 • C. J.Date, 数据库系统导论（第7版）, 机械工业出版社
What is DBMS? • Need for information management • A very large, integrated collection of data. • Models real-world enterprise. • Entities (e.g., students, courses) • Relationships (e.g., Liu Wei is taking 70420403) • A Database Management System (DBMS)is a software package designed to store and manage databases.
Why Use a DBMS? • Data independence and efficient access. • Data integrity and security. • Uniform data administration. • Concurrent access, recovery from crashes. • Replication control • Reduced application development time.
File Systems program 1 File 1 data description 1 program 2 File 2 data description 2 program 3 File 3 data description 3
Application program 1 (with data semantics) DBMS description Application program 2 (with data semantics) manipulation database control Application program 3 (with data semantics) Database Management
? Why Study Databases?? • Shift from computation to information • at the “low end”: access to physical world • at the “high end”: scientific applications • Datasets increasing in diversity and volume. • Digital libraries, interactive video, Human Genome project, e-commerce, sensor networks • ... need for DBMS/data services exploding • DBMS encompasses several areas of CS • OS, languages, theory, AI, multimedia, logic
number, Designation • Hours worked, Pay rate • Insurance, Pension Key Terms cont. • Database System • A database, a database management system and appropriate hardware and personnel.
Database System: Hardware • Set of physical devices on which a database resides. It consists of one or more computers, disk drives, CRT terminals, printers, tape drives, connecting cables and other auxiliary and connecting hardware.
TAPE Printer Client Server Client Database System: Hardware cont.
Database System: Software • A database software includes two types of software • General-purpose database management software, usually called the database management system (DBMS) • Application software that uses DBMS facilities to manipulate the database to achieve a specific business function, such as providing reports or documents, which can be used by users.
Database System: Software cont. • Application software is generally written standard programming language such as C, or it may be written in a language (commonly called a fourth-generation language) supplied with the DBMS. • These programs utilise the command language of the DBMS and make use of the information contained in the data dictionary.
Database System: Software cont. • User Interface • Language, menus and other facilities by which users interact with various system components, such as application programs, the DBMS • Computer-aided software engineering (CASE) tools • Automated tools used to design databases and application programs.
Data Dictionary/Directory (Repository) • A subsystem that keeps track of the • definitions of all data items in the database. • relationships that exists between various data structures. • indexes that are used to access data quickly. • screen and report format definitions that may be used by various application programs.
Data Dictionary • Definitions of data items in the database includes: • Elementary-level data items (fields), • group and record-level data structures, and • files or relational tables.
Metadata • Data that describe the properties or characteristics of other data. • Some of these properties include data definitions, data structures and rules or constraints. Item name, the data type, length, minimum and maximum allowable values (where appropriate) and a brief description of each data item. • Metadata allow database designers and users to understand what data exist, what the data mean. • Data without clear meaning can be confusing, misinterpreted or erroneous.
Metadata E.g. Data Item Value Name Type Length Min Max Description Name Character 30 Employee Name ID Number 9 Employee No. Dept Character 10 Dept. No. Age Integer 2 18 60 Employee Age Name Character 15 Dept. Name Manager Number 9 Mgr. Emp. No. Employee No. (ID) unique Manager is an employee of the organisation
Database System: People • Two different types of people (users and practitioners) are concerned with the database. Users • who need information from the database to carry out their primary business responsibility e.g. Executives, managers, staff, clerical personnel
Database System: People cont. Practitioners • people responsible for the database system and its associated application software. e.g. Database administrators, analysts, programmers, database and system designers, information systems managers.
Components of a Database System Four components: People, H/W, S/W, Data • Practitioners (analysts and database designers) in consultation with users identify data needs and design database structures to accommodate these needs. • The database structures are specified to the DBMS through the data dictionary.
Components of a DBS cont. • Users enter data into the system by following specified procedures. • The entered data are maintained on hardware media such as disks and tapes. • Application programmes that access the database are written by practitioners and users to be run on computers.
Application Program DATA DICTIONARY/ DIRECTORY Analyst Application Program Application Program DBMS DATABASE Terminals Computer User PRINTER Components of a DBS cont.
Key Terms cont. • Database Management System • Systems software that facilitates the management of a database. E.g. Oracle, Access, SQL Server
Database Management System (DBMS) • DBMS • An application software that organises data into records in one or more databases and allows organising, accessing and sorting of the data in a variety of formats.
DBMS cont. • Relational DBMS • Most common type of DBMS. Data elements are stored in different tables made up of rows and columns. Relates data in different tables through the use of common data element(s).
DATA DICTIONARY/ DIRECTORY DBMS Data Dictionary/ Directory Subsystem User Query and Reporting Facilities Security and Integrity Subsystem Application Program Development Facilities DATABASE Database Access Subsystem PROGRAMMER PROGRAMMER USER USER Components of a DBMS
What is not a DDBS? • A timesharing computer system • A loosely or tightly coupled multiprocessor system • A database system which resides at one of the nodes of a network of computers - this is a centralized database on a network node
Data Security • The database is a valuable resource needing protection. • The DBMS provides database security by limiting access to the database to authorised personnel. • Authorised users will generally be restricted as to the particular data they can access and whether they can update it.
3 1 2 4 Data items Data Security • Access is often controlled by passwords and by data views, which are definitions of restricted portions of the database.
Data Integrity • The integrity and consistency of the database are protected via constraints on values that data items can have and by backup and recovery capabilities provided within the DBMS. • Data constraint definitions are maintained in the data dictionary.
Data Integrity • Backup and recovery are supported by software that automatically logs changes to the database and provides for a means of recovering the current state of the database in case of system failure.
Concurrent Data Access • One of the chief functions of the DBMS is to support the access, retrieval and update of data in the database. • The DBMS provides the physical mechanisms allowing multiple users to access a variety of related data quickly and efficiently.
User-Oriented Data Manipulation • DBMS provides user-oriented data manipulation tools. • Easy-to-use query languages allow users to formulate queries and request one-time reports directly from the database. • Often query languages will contain facilities to format the results of queries as reports.
Data Manipulation • Report generators have more powerful reporting facilities than those in the query language.
Application Development • The DBMS commonly provides significant assistance to the application programmer. • Tools for screen, menu and report generation • application generators • compilers • data and view definition facilities
Application Development • Modern database systems provide language components that are much more powerful than those of traditional languages, making the programming process itself considerably more efficient. • Developer 2000/PowerBuilder for Oracle • Visual Basic for Microsoft SQL server
Database Applications Databases range from those for a single user with a desktop computer to those on mainframe computers with thousands of users. • Personal databases • Workgroup databases • Departmental databases • Enterprise databases
Personal Computer Databases Designed to support one user with a standard alone PC. E.g. a sales person keeping track of this customer information with contact details.
Workgroup Databases A relatively small team of people (less than 25) who collaborate on the same project or application. E.g. a software development team maintaining a list of software objects.
Department Databases A department is a functional unit of an organisation. It is larger than a workgroup. Department databases are designed to support the various functions and activities of a department. E.g. a personnel database that is designed to track data concerning employees, jobs, skills and job assignments.
Enterprise Databases An enterprise is one whose scope is the entire organisation or enterprise. Such databases are intended to support organisation-wide operations and decision making. E.g. a large health care organisation that operates a group of medical centre's including hospitals, clinics and nursing homes.
Enterprise Databases cont. An enterprise database does support information needs from many departments. The most important type of enterprise database today is called a data warehouse. • Data warehouse • An integrated decision support database whose content is derived from the various operational databases.
Marketing Accounting Sales Advertising Accounts Receivable Accounts Payable Accounting Corporate Database Purchasing Marketing Database Environment
3 Level ANSI/SPARC Architecture • Made databases more independent of application • Became a standard for the organisation of DBMS
3 Levels • External • User’s and Application’s view of data • Conceptual • Logical Data Model • Physical • Physical data Model
External (Sub) Schema • defines the external view of data as seen by a user or program • Conceptual Schema • defines the logical view of data as seen by all users and programs • Physical (Internal) Schema • defines the physical view of data as seen by a DBMS
Databases 3 Level Architecture user a user i/program j program x sub-schema a sub-schema i sub-schema z conceptual schema physical schema
database Physical View • The DBMS must know • exact physical location • precise physical structure Employee record A.B.C. De Silva |222, Galle Road, Colombo | 650370690V|Senior Lecturer Name (20 characters) Address (40 characters) NID (10 char) Designation (15 char)
Physical View • Physical view provides the disk drives, physical addresses, indexes and pointers. • Physical Database Design is the responsibility of the Database Administrator (DBA). No user is concerned with this view. • Physical devices to contain the data • Access methods to retrieve and update data • maintain and improve database performance