《数据库第一章讲义》由会员分享,可在线阅读,更多相关《数据库第一章讲义(69页珍藏版)》请在金锄头文库上搜索。
1、Database System principles,Yuan Xiaojie,Shen wei Dept. of Computer Science and technology, Nankai University,Slides adapted from material by Profs. Jeff Ullman (Stanford) and Art Keller (UCSC),Course Goals & Content(内容),cover mostly relational databases how to design and create such databases how to
2、 use them (via SQL query language) how to implement them (only briefly) will touch on some advanced issues data warehouse(数据仓库), data mining,Outline,Introduction to DBMSs (Yuan) The Entity Relationship model (Shen) The Relational Model (Shen) SQL: Introduction (Shen) SQL: Advanced (Shen) DB Schema D
3、esign (Yuan) Relation Algebra & Query Processing (Yuan) Query Processing (Yuan) Concurrency Control (Yuan),Textbook,“A First Course in Database System”, Jeffrey D.Ullman, Jennifer Widom 翻译版,数据库系统基础教程原书第三版, 机械工业出版社(本科教材) “Database System Implementation”,Hector Garcia-Molina, Jeffrey D.Ullman, 翻译版,数据库
4、系统实现第二版, 机械工业出版社(本科参考、研究生教材),Textbook,Database Systems: The Complete Book, Hector Garcia-Molina, Jeffrey D. Ullman, and Jennifer Widom, Prentice Hall, 2008.,这本书的网址是http:/infolab.stanford.edu/ullman/dscb.html,原版很贵,已不出版,Stanford Database Courses,CS 145,CS 245,CS 346,CS 345,CS 347,CS 395,CS 545,Fall, S
5、pring,Winter,DB Systems Implementation,Advanced Topics,TP + DDBs,Independent DB Project,DB Seminar,Fall,Spring,Spring,All,Fall, Spring,A First Course in Database System,Database System Principles,https:/www.coursera.org/course/db,View Video Lectures,Professor Jennifer Widom is the Fletcher Jones Pro
6、fessor and Chair of the Computer Science Department at Stanford University,Course Format,For all students two 70-min lectures / week (If you have conflicts, do let us know in advance, 5% ) some homeworks(Will be collected at the beginning of class on the due date, No late homework will be accepted,
7、10%) projects (15%) Midterm Examination( 10%, SQL, Computer) Final Examination Closed Book (60%),Todays Lecture,Introduction Motivating example How do DBMSs work? DDL, DML, views. Fundamental concepts DBMS users Overall system architecture Steps in Building a DB Application Conclusions,Definitions,D
8、BMS = Data Base Management System: is a piece of software designed to store and Manage databases the (commercial) system, like: DB2, Oracle, MS SQL-server, . Database system: DBMS + data + application programs,A Motivating Example,Suppose we are building a system to store the information about: stud
9、ents courses professors who takes what, who teaches what,为什么需要DBMS?,采用文件系统实现存储功能是否可行?,文件系统是否可以完成数据库的存储功能?,#include struct Dateint iMonth,iDay,iYear; void main() Date dt=6,10,92; ofstream datafile(“ata.dat“,ios:binary); datafile.write(char *) ,利用C+编写数据存储程序样例,ata.dat: 06 00 00 00 0A 00 00 00 5C 00 00
10、00,利用文件系统存储数据,m_sno m_name m_age,CStudent派生于 CRecordSet,STUDENT数据源,CStudentDlg派生于CDialog,m_sno m_name m_age,对话框控件,UpdateData(),=,Requery() Update(),Application Requirements,store the data for a long period of time large amounts (100s of GB) protect against crashes protect against unauthorized use al
11、low users to query/update: who teaches “CS 173” enroll “Mary” in “CS 311” allow several (100s, 1000s) users to access the data simultaneously allow administrators to change the schema add information about TAs,Trying Without a DBMS,Why Direct Implementation Wont Work: Storing data: file system is li
12、mited size less than 4GB (on 32 bits machines) when system crashes we may loose data password-based authorization insufficient Query/update: (基于口令的授权不足) need to write a new C+/Java program for every new query need to worry about performance,Concurrency: limited protection need to worry about interfe
13、ring with other users need to offer different views to different users (e.g. registrar, students, professors) Schema change: entails changing file formats need to rewrite virtually all applications Better let a database system handle it,建立自己数据库所面临的问题,元数据(metadata),关于数据的数据 创建自己的C+数据库时,需要在自己的源代码中定义元数据
14、,使得任何想使用这些数据的人不得不访问你的源代码 用于处理文件封锁和开锁的源代码会相当复杂 采用数据库管理系统(DBMS)是最佳选择,Detailed outline,Introduction Motivating example How do DBMSs work? DDL, DML, views. Fundamental concepts DBMS users Overall system architecture Steps in Building a DB Application Conclusions,How do DBs work?,DBMS,data,meta-data = ca
15、talog = data dictionary,谁选修了数据库课程?,我的卡还有多少钱?,我要买上海的飞机票?,A well-designed DBMS makes many people happy!,DBMS应该怎样构造?,银行系统 数据项有:姓名、地址、帐号、存款、结余等 基本操作:查询、更新、转帐等 不能出现任何错误:并发操作、支取现金等 订票系统 数据项有:航班信息、机票信息、预定信息等 基本操作:查询时间、票价、座位等,更新信息等 并发访问处理,系统崩溃处理,防止信息丢失等,数据项存储-数据模型,数据库中的数据是按照一定的逻辑结构存放的,这种结构是用数据模型来表示的,An Exam
16、ple of a Relation,tuples,Attribute names,Table name,student,course,takes,How do RDBMS work? - SQL,create table student (学号 char(8) primary key, 姓名 char(8), 出生日期 date, 所属学院 varchar(30),insert into student values(20161001,张三,1994.08.05,计控) insert into student values(20162023,李四,1995.06.07,数学) insert into student values(20164512,王五,1994.11.15,金融),SQL(Structured Query