《《Database Principles数据库原理》PPT.pptx》由会员分享，可在线阅读，更多相关《《Database Principles数据库原理》PPT.pptx（153页珍藏版）》请在三一文库上搜索。
1、,Database Principles数据库原理,Textbooks,Required: A First Course in Database Systems, by Jeffrey D.Ullman Jennifer Widom. Recommended: 萨师煊, 王珊. 数据库系统概论(第三版). 高等教育出版社, 2000 年 Books on SQL Server 2000 You may also want: Books on design and programming : Powerbuilder, PowerDesigner, UML, VB, Delphi, VC, JA
2、VA, C#, ASP, PHP, JSP etc.,2,Why Using English Textbook ?,More computer English words More advanced and newest information Better information retrieval capability More international communication chance,3,What to learn in this unit ?,Basic theories (basic) System and database design (designer) Appli
3、cation Programming (programmer) Database management and maintenance (database manager),4,Syllabus,Background and history of database management systems Fundamentals of using a database management systems: SQL 2000.(*extracurricular) Database modeling Relational model: theoretical background and oper
4、ations Database Language SQL Fundamentals of database management systems: transactions and security etc. Database system design and practice,5,Chapter 1The Worlds of Database Systems,Evolution of Database Systems Architecture of DBMS and DBS Future of Database Systems,1.1 Evolution of Database Syste
5、ms,1.1.1 Some Basic notions Data Database (DB) Database Management System (DBMS) Database System (DBS). Database Administrator (DBA),7,Basic Notions,Data Distinct pieces of information represented in a form suitable for processing by computer. Data can exist in a variety of forms - as digital number
6、s, text, image, sound, video and etc. Database (DB) In essence, its nothing more than a collection of information that exists over a long period of time. In common parlance, it refers to a collection of data managed by a database management system (DBMS) or just database system (DBS).,8,Example of D
7、ata and Database,9,DBMS,Database Management System (DBMS) A collection of programs that enables you to store, modify, and extract information from a database. There are many different types of DBMSs, ranging from small systems that run on personal computers to huge systems that run on mainframes.,10
8、,Basic functions of DBMS,Data definition Allow creating new databases and specify their schema (logical structure of the data), via data definition language (DDL). Data manipulation Allow efficient access to the data, such as querying and modifying the data, via query language or data manipulation l
9、anguage (DML). Operation management of DB Support the storage of very large amounts of data over a long period of time, assuring data integrity, keeping data secure from accident or unauthorized use. Support concurrent, atomic access to data. Creation and maintenance of DB Such as inputting and tran
10、sferring of Data, DB backup and recovery, performance monitoring, audit and analysis etc.,11,Database System (DBS),Database System (DBS). DB, DBMS, Development Tools, DB applications, DB Administrator (DBA) and users,12,DBMS in Computer System,Database Administrator,DBA Person who is responsible for
11、 management and maintenance of DB. Tasks Database contents and structures Storage structure and access methods Database security and integration Monitor and improve database performance,13,1.1.2 History of Data management,File systems Problems: limited support to definition of data schema, no direct
12、ly support to DML (Data Manipulate Language), no support to efficient concurrent, secure access etc. Early DBMS The first commercial DBMS appeared in the late 1960s, evolved from file systems. The early DBMSs used several data models for describing structure of DB: hierarchical model and network mod
13、el. Problems: no support to high-level query languages.,14,History of Data management (cont.）,Relational DBMS A famous paper by Ted Codd in 1970, “A relational model for large shared data banks” in Comm. ACM. Data organized as tables called relations. Unlike the user of early DBS, user of a relation
14、al system wont be concerned with the storage structure, and queries are expressed in a very high-level language (SQL). Today used in most DBMSs.,15,Relational Model,Based on tables, such as:,16,Attributes (column headers),Query Languages,SELECT ManagerFROM Employee, DepartmentWHERE Employee.name = C
15、lark Kent”AND Employee.Dept = Department.Dept ;,17,History of Data Model,Data model: abstraction of data features in real world into computer world.,18,Relational model or not?,Relational model is good for: Large amounts of data simple operations Navigate among small number of relations Difficult Ap
16、plications for relational model: VLSI Design (CAD in general) CASE: Computer-Aided Software Engineering Graphical Data Where number of relations is large, relationships are complex Object Data Model Logic Data Model,19,Other Data Models,Object data model Usually find objects via explicit navigation,
17、 also query language in some systems Complex Objects Nested Structure (pointers or references) Encapsulation, set of Methods/Access functions Object Identity Inheritance Defining new classes like old classes Logic data model Prolog, Datalog More power than relational,20,The DBMS Marketplace,Relation
18、al DBMS companies Oracle, Sybase are among the largest software companies in the world. IBM offers its relational DB2 system. With IMS, a nonrelational system, IBM is by some accounts the largest DBMS vendor in the world. Microsoft offers SQL-Server, plus Microsoft Access for the cheap DBMS on the d
19、esktop, answered by “lite” systems from other competitors. Relational companies also challenged by “object-oriented DB” companies. But countered with “object-relational” systems, which retain the relational core while allowing type extension as in OO systems.,21,1.2 Architecture of DBMS,Major compon
20、ents of a DBMS,22,Schema (logical structure of the data); Metadata(data of data),Architecture of DBS,Three Schema Architecture of DBS External Schema: user schema or subschema Defines one view of the data as seen by a specific set of application or end users. There may be many external schemas in a
21、DB. Schema: conceptual schema, logical schema Defines data from perspective systems designer; Independent of end users every Ex-Schema has an Ex-Schema/Schema Reflection. When Schema changes, DBA changes the Ex-Schema/Schema reflections, so application programs neednt been changed. Physical Independ
22、ence Via Reflection of Schema/Internal Schema Schema/Internal Schema Reflection is unique. When Internal Schema changes, DBA changes the Schema/ Internal Schema Reflection, so application programs neednt been changed.,25,Three Aspects to Studying DBMSs,Modeling and design of databases Allows explora
23、tion of issues before committing to an implementation. Application programming SQL: queries and DB operations like update. Host language and Embedded SQL DBMS implementation Storage, query processing, transaction, ,26,Reading Guide,A First Course in Database Systems: Required: 1.1 Recommended: 1.2,
24、1.5 数据库系统概论 推荐：第一章,27,Exercises,(Required, in English) Explain the following notions and give an example to show them clearly. DB, DBMS, DBS 以下选做： (推荐作业，中文完成) 逻辑独立性和物理独立性是什么含义？在数据库系统架构中是如何保证这两个独立性的？ (建议作业，中文完成) 对数据库技术发展趋势、主流数据库产品、流行数据库开发和设计技术进行调研，撰写小论文，和同学分组讨论。,28,Chapter 2Database Modeling,Data Mod
25、els Concept Model a movie can have more than one star.,48,Many-One Relationships,Some binary relationships are many -one from one entity set to another. Each entity of the first set is connected to at most one entity of the second set. But an entity of the second set can be connected to zero, one, o
26、r many entities of the first set.,49,Example,Owns, from Movies to Studios is many-one. A movie is owned by at most one studio. But a studio owns any number of movies.,50,One-One Relationships,In a one-one relationship, each entity of either entity set is related to at most one entity of the other se
27、t. Example: Relationship Runs between entity sets Studios and Presidents. A president can run only one studio and a studio has only one president.,51,Representing “Multiplicity”,Show a many-one relationship by an arrow entering the “one” side. Show a one-one relationship by arrows entering both enti
28、ty sets.,52,实体联系图示,一对一、一对多、多对一、多对多,53,Attributes on Relationships,Sometimes it is useful to attach an attribute to a relationship. Think of this attribute as a property of tuples in the relationship set.,54,Example,55,Movies,stars,Contracts,salary,It is appropriate to associate a salary with the (st
29、ar, movie, studio) triple in the relationship set for the Contracts relationship.,studios,Keys,A key is a set of attributes for one entity set such that no two entities in this set agree on all the attributes of the key. It is allowed for two entities to agree on some, but not all, of the key attrib
30、utes. We must designate a key for every entity set. Keys in E/R Diagrams: Underline the key attribute(s),56,Example: a Multi-attribute Key,57,Courses,dept,number,hours,room,Note that hours and room could also serve as a key, but we must select only one key.,Design Techniques,Avoid redundancy. Dont u
31、se an entity set when an attribute will do.,58,Avoiding Redundancy,Redundancy occurs when we say the same thing in two or more different ways. Redundancy wastes space and (more importantly) encourages inconsistency. The two instances of the same fact may become inconsistent if we change one and forg
32、et to change the other.,59,Example: Good,60,This design gives the name of each studio exactly once.,Example: Bad,61,Movies,Studios,Owns,title,This design states the name of each studio twice: as an attribute and as a related entity.,name,addr,year,StudioName,Example: Bad,62,This design repeats the s
33、tudios address once for each movie and loses the address if there are temporarily no movie for a studio.,Entity Sets Versus Attributes,An entity set should satisfy at least one of the following conditions: It is more than the name of something; it has at least one nonkey attribute. or It is the “man
34、y” in a many-one or many-many relationship.,63,Example: Good,64,Movies,Studio,Owns,title,Studio deserves to be an entity set because of the nonkey attribute addr. Movies deserves to be an entity set because it is the “many” of the many-one relationship Owns.,name,addr,Example: Good,65,Movies,title,T
35、here is no need to make the studio an entity set, because we record nothing about studios besides their name.,StudioName,Example: Bad,66,Movies,Studios,Owns,title,Since the Studios is nothing but a name, and is not at the “many” end of any relationship, it should not be an entity set.,name,两个实体型间的联系
36、,67,多个实体型间的联系,多个实体型间的一对多联系 若实体集E1，E2，.，En存在联系，对于实体集Ej（j=1，2，.，i-1，i+1，.，n）中的给定实体，最多只和Ei中的一个实体相联系，则Ei与E1，E2，.，Ei-1，Ei+1，.，En之间是一对多联系 实例：课程、教师与参考书三个实体型 多个实体型间的一对一联系 多个实体型间的多对多联系,68,课堂思考,三个实体间的联系 判断联系种类,69,供应商,项 目,零 件,供 应,同一实体集内各实体间的联系,一对多联系 实例 职工实体集内部具有领导与被领导的联系 某一职工（干部）“领导”若干名职工，一个职工仅被另外一个职工直接领导 这是一对
37、多的联系 一对一联系 多对多联系,70,Other Example,71,矩形：表示实体集 菱形：表示联系集 线：连接实体集与联系集或属性与实体集 椭圆：表示属性 下划线：主码属性,学生实体例,学生实体及属性,72,联系及其属性例,73,实例练习,实体 系、班级、学生、教研室、教师、课程 给出概念模型：ER图,74,Reading Guide,Required: 2.2,2.5 Recommended: 2.3,2.8 ,数据库系统概论第一章,75,Exercises,To manage the materials of a factory, please give the E-R diagr
38、ams. Such entity sets are involved as below: Warehouse: with attributes as Number, area and telephone Accessory : with attributes as number, Name, Specification, price and description Supplier: with attributes as Number, name, addr, telephone and account Project : with attributes as number and budge
39、t Employee : with attributes as number, name and age The relationships among these entity sets are listed as below: A kind of accessory can be deposited in more than one warehouse. A warehouse can accommodate more than one kind of accessory. There can be more than one employee in a warehouse, and a
40、employee can work in only one warehouse. Some employee can leader others. A supplier can supply various accessories to various projects. A project can use accessories supplied by various suppliers. A kind of accessory can be supplied by various supplier.,76,Chapter 3The Relational Data Model,Relatio
41、nal Model Functional Dependencies,77,Contents,3.1 Basics of the Relational Model 3.3 From E/R diagrams to Relational Designs 3.5 Functional Dependencies 3.7 Design of Relational Database Schemas Reading Guide Exercise,78,The things you should know,The basic principle of relational database is propos
42、ed by E.F.Codd in 1970. The first RDBMS production is System R The most popular RDBMS DB2, Oracle, Ingres, Sybase, Informix,79,3.1 Basic of the Relational Model,The relational model gives us a single way to represent data: as a two- dimensional table called a relation.,80,Attributes (属性) Attributes
43、of a relation serve us as names for the columns of the relation. Usually, the attributes describes the meaning of entries in the column below.,81,Schemas（模式） The name of a relation and the set of attributes for a relation is called the schema for that relation. We show the schema for the relation wi
44、th the relation name followed by a parenthesized list of its attributes.,82,Tuples（元组） The rows of a relation, other than the header row containing the attributes, are called tuple. For example: Relations, however, are sets of tuples, and it is impossible for a tuple to appear more than once in a gi
45、ven relation.,83,Domains（域） The relational model requires that each component of each tuple be atomic; that is, it must be of some elementary type such as integers or string. Each attributes of a relation has particular elementary type, thus domain is decided.,84,Equivalent Representations of a Rela
46、tion The attributes of the relation can be reordered without change the relation.,85,Relation Instance（关系实例） A relation about movies is not static; rather, relations change over time. It is not common for the schema of a relation to change. We shall call a set of tuples for a given relation an insta
47、nce of that relation.,86,An Example of Relation Instance,Relation: Person (Name, Address, Telephone) Relation Instance: NameAddressTelephone Bob123 Main St555-1234 Bob128 Main St555-1235 Pat123 Main St555-1235 Harry456 Main St555-2221 Sally456 Main St555-2221 Sally456 Main St555-2223 Pat12 State St5
48、55-1235,87,More ,Relation (Instance) = a set of tuples Database = collection of relations Relation schema = relation name + attributes Example: Movies (title, year, length, fileType) Database schema = a set of all relation schemas Movies(Title, Year, Length, FileType) Star(Name, Age) Studio(StudioNa
49、me, Addr),88,Name Addr Tel N1 A1 T1 N2 A2 T2 N3 A3 T3 N4 T4 N5 T5 T6 T7,89,Name Addr Tel N1 A1 T1 N1 A1 T2 N1 A1 T3 . . . N1 A1 T7 N1 A2 T1 N1 A3 T1 N2 A1 T1,Tuple,Domain,Component,Attribute,Integrity Constrain of Relations,Entity Constrain The attributes belong to key can not be set as NULL. Reference Constrain Foreign Key: an non-key attribute A in R is a key in S, then the A is called a foreign key of R. The value of foreign key can only be NULL or same as what is in S. User-define Constrai