DatabaseSystems数据库管理系统

1 70420403- Database Systems 数 据 库 管 理 系 统Li J 2 References Raghu Ramakrishnan/Johannes Gehrke, Database Management Systems, 2nd Ed. , 清华大学出版社/McGraw-Hill, 2000 C. J.Date, 数据库系统导论（第7版）, 机械工业出版社 3 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. 4 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. 5 File Systemsprogram 1data description 1program 2data description 2program 3data description 3 File 1File 2File 3 6 Database Management databaseDBMSApplicationprogram 1(with datasemantics)Applicationprogram 2(with datasemantics)Applicationprogram 3(with data semantics) descriptionmanipulationcontrol 7 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 ? 8 Database Systems 9 Key Terms cont. Database System A database, a database management system and appropriate hardware and personnel. number, DesignationHours worked, Pay rateInsurance, Pension 10 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. 11 Database System: Hardware cont.ClientClient ServerTAPEPrinter 12 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. 13 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. 14 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. 15 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. 16 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. 17 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 18 E.g. Data Item ValueName Type Length Min Max DescriptionName Character 30 Employee NameID Number 9 Employee No.Dept Character 10 Dept. No.Age Integer 2 18 60 Employee AgeName Character 15 Dept. NameManagerNumber 9 Mgr. Emp. No.Employee No. (ID) uniqueManager is an employee of the organisationMetadata 19 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 responsibilitye.g. Executives, managers, staff, clerical personnel 20 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. 21 Components of a Database SystemFour 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. 22 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. 23 Components of a DBS cont.DATABASEDATA DICTIONARY/ DIRECTORYApplication ProgramApplication ProgramApplication ProgramDBMSComputerTerminals PRINTERAnalystUser 24 Key Terms cont. Database Management System Systems software that facilitates the management of a database.E.g. Oracle, Access, SQL Server 25 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. 26 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). 27 Components of a DBMS DATABASEDATA DICTIONARY/ DIRECTORYUSER PROGRAMMER Data Dictionary/ Directory SubsystemSecurity and Integrity SubsystemDatabase Access SubsystemUser Query and Reporting FacilitiesApplication Program Development Facilities DBMSUSER PROGRAMMER 28 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 nodeWhat is not a DDBS? 29 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. 30 Data Security Access is often controlled by passwords and by data views, which are definitions of restricted portions of the database.1Data items2 34 31 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. 32 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. 33 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. 34 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. 35 Data Manipulation Report generators have more powerful reporting facilities than those in the query language. 36 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 37 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 38 Database ApplicationsDatabases 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 39 Personal Computer DatabasesDesigned to support one user with a standard alone PC.E.g. a sales person keeping track of this customer information with contact details. 40 Workgroup DatabasesA 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. 41 Department DatabasesA 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. 42 Enterprise DatabasesAn 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 centres including hospitals, clinics and nursing homes. 43 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. 44 Database EnvironmentMarketing PurchasingAccountingMarketingSales Advertising AccountingAccounts Receivable Accounts PayableCorporate Database 45 3 Level ANSI/SPARC Architecture Made databases more independent of application Became a standard for the organisation of DBMS 46 3 Levels External Users and Applications view of data Conceptual Logical Data Model Physical Physical data Model 47 External (Sub) Schema defines the external view of dataas seen by a user or program Conceptual Schema defines the logical view of dataas seen by all users and programs Physical (Internal) Schema defines the physical view of dataas seen by a DBMS 48 user a user i/program j program xsub-schema a sub-schema i sub-schema zconceptual schemaphysical schema3 Level ArchitectureDataba ses 49Name (20 characters) Address (40 characters) NID (10 char) Designation (15 char) Physical View databaseA.B.C. De Silva |222, Galle Road, Colombo |650370690V|Senior Lecturer The DBMS must know exact physical location precise physical structureEmployee record 50 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 51 databaseLogical ViewAddress 222, Galle Road, Colombo The user/application must know existence logical reference EmployeeName A.B.C. De Silva Designation Senior LecturerNID 650370690V 52 Logical View This is a single logical description of all data elements and their relationships. It is the result of the conceptual design which involves analysis of all users information needs and data definition of data items needs to meet them. SQL CREATE TABLE statement is used to define the data elements. 53 databaseExternal ViewDepartment Dept. of Computer Science The user/application see authorised data own format LecturerName A.B.C. De Silva Designation Senior Lecturer Age 35 54 External View This consists of user views of the database. Each definable user group will have its own view of the database. Each of these views gives a user-oriented description of the data elements and relationships of which the view is composed. 55 A logical description of some portion of the database that is required by a user to perform some task.E.g. Benefit application user will view part of the employee data, excluding data such as date of birth and salary.User View 56 External View It can be derived directly from the conceptual schema. The collection of all user views is the external level. SQL CREATE VIEW statement is used to create these views and SQL G RANT statement is used to restrict its use to a user group. 57 External Views Allows to hide unauthorised datae.g. salary, dob provide user view e.g. view employee name, designation, department data taken from employee and department files derive new attributese.g. age derived from dob or nidExternal View cont. 58 External Views Allows to change unit of measuremente.g. show age in years or months define security levelse.g. update access to employee file read-only to department fileExternal View cont. 59 Mapping between Levels DBMS map or translate from one level to another.External ConceptualConceptual Physical Database exists in reality only at the internal level. 60 user a user i/program j program xsub-schema a sub-schema i sub-schema zconceptual schemaphysical schemaDataba ses DDLSDDL DML/SQLPractitioners:DBAUsers 61 Data Definition Language (DDL)- is the language component of a DBMS that defines each data element as it appears in the database. Sub-Schema Data Definition Language (SDDL)- is the language component of a DBMS that defines data elements as it should appear to the end users and programmers. Practitioners:DBA 62 Data Manipulation Language (DML)- is a language associated with a DBMS that is employed by end users and programmers to manipulate data in the database. Structured Query Language (SQL) - pronounced as sequel, is the standard data manipulation for relational DBMSs.User 63 Characteristics of Early DBMS Centralised Storage Management Transaction Management Recovery Facilities System Maintained Access Paths 64 Characteristics of Modern DBMS Query processing and optimisation Transaction management Concurrency control Database recovery Database security and authorisation Distributed databases Data warehousing and data mining 65 Function of a DBMSFunctions of a DBMS are concerned with providing efficient flexible data processing capabilities without compromising data validity. Main Functions are: Shared data Control redundancy Data integrity Data security 66 Capabilities of a DBMSCapabilities vary considerable, but basic operations are: Data definition Data entry Data manipulation Data display 67 Advantages Program-data independence Minimal data redundancy Improved data consistency Improved data sharing Increased productivity of application development Enforcement of standards Improved data quality Improved data accessibility and responsiveness Reduced program maintenanceDatabase Approach 68 The separation of data descriptions (metadata) from the application programs that use the data.In the database approach data descriptions are stored in a central location called the data dictionary. This property allows an organisations data to change and evolve (within limits) without changing the application program that process the data.Program-Data/Data Independence 69 Data files are integrated into a single, logical structure. Each primary fact is recorded (ideally) in only one place in the database.E.g. Employee data not with the payroll and benefit files.Note: Data redundancy is not eliminated entirely. Some data items will appear in more than one place (e.g. employee no.) to represent the relationship with others.Minimal Data Redundancy 70 By eliminating (or controlling) data redundancy, we greatly reduce the opportunities for inconsistency.E.g. employee address is stored only once and hence we cannot have disagreement on the stored values. Also, updating data values is greatly simplified and have avoid the wasted storage space.Improved Data Consistency 71 A database is designed as a shared corporate resource. Authorised users are granted permission to use the database, and each user (or group of users) is provided one or more user views to facilitate this use.E.g. employee data common to payroll, benefit applications will be shared among different users.Improved Data Sharing 72 A major advantage of the database approach is that it greatly reduces the cost and time for developing new business applications. Programmer could concentrate on the specific functions required for the new application, without having to worry about design or low-level implementation details; as related data have already been designed and implemented.Increased Productivity of Application Development 73 DBMS provides a number of high-level productivity tools such as forms and report generations and high-level languages that automate some of the activities of database design and implementation.Increased Productivity of Application Development cont. 74 When the database approach is implemented with full management support, the database administration function should be granted single-point authority and responsibility for establishing and enforcing data standards. Standards include naming conventions, data quality standards and uniform procedures for accessing, updating and protecting data. Powerful set of tools for developing and enforcing these standards are available for some DBMS.Enforcement of Standards 75 A number of tools and processes are available to improve data quality. Database designers can specify integrity constraints that are enforced by the DBMS. One of the objectives of a data warehouse environment is to clean up operational data before they are placed in the data warehouse.ConstraintA rule that cannot be violated by database users.Improved Data Quality 76 With relational database, end users without programming experience can often retrieve and display data, even when it crosses traditional departmental boundaries. English-like query language SQL and query tools such as Query-By-Example provide such facilities.Improved Data Accessibility and Responsiveness 77 Stored data are changed frequently for variety of reasons such as new data items types are added, and data formats change (e.g. date format from two-digit to four digit).Data independence allows to reduce the program maintenance time.Reduced Program Maintenance 78 Disadvantages DBMS are more vulnerable than file-based system because of the centralised nature of a large integrated database. If a failure occurs the recovery process is more complex and some times may results in lost transactions. H ardware, software and personnel cost are higher for DBMS.Database Approach 79 Data Models A data model is a collection of concepts for describing data. A schema is a description of a particular collection of data, using the 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. 80 Levels of Abstraction Many views, single conceptual (logical) schema and physical schema. Views describe how users see the data. Conceptual schema defines logical structure Physical schema describes the files and indexes used. * Schemas are defined using DDL; data is modified/queried using DML.Physical SchemaConceptual SchemaView 1 View 2 View 3 81 Example: University Database Conceptual schema: Students(sid: string, name: string, login: string, age: integer, gpa:real) Courses(cid: string, cname:string, credits:in