Transcript 投影片 1
Introduction to Database Management System 電機三 趙上鋒 Outline What is DBMS? What can DBMS do? How to design a database? How to use DBMS? Conclusion Reference What is DBMS? Data v.s. Information Data Anything stored without summarized or analyzed. Information Data that has been proceeded to be meaningful for users. Example Information Data Database Stores organized data so that you can query from it or update it. Like an electrical shelf to put data with organization. From user to data x Teacher (user) Application Database x Student (user) Application data What is DBMS? Database Management System DBMS is software to store and manage data, so applications don’t have to worry about them. Like a person who search the dictionary for you. Relation between App & DBMS DBMS x Teacher (user) Application View1 Conceptual schema x Student (user) Application View2 Physical schema data What can DBMS do? DBMS can Do These… Store huge amount of data ( eg. 100+GB ) Store data for long period of time Manage data on permanent storage. Efficient database operation B+ Tree indexing Hash-based indexing Allow people to query & update data Support query language. More Requirement Protect from unauthorized access Security! Protect from system crash Crash recovery Support many users to access the database at the same time Concurrency control Allow administrator to easily change data schema Protect from incorrect input If we don’t use DBMS Applications have to stores data as files 32-bit addressing (5GB) is insufficient to address 100GB+ data file Write special code to: support different queries protect data from multiple users and concurrent access protect against data loss / corruption as result of system crashes If we don’t use DBMS (cont’d) Other issues: Rewrite applications when data schema changes Password-based authorization is insufficient Optimize applications for efficient access and query Easier to use a DBMS to handle these issuse! How to design a database Data Model A data model is a collection of concepts for describing data. Entity-relation (ER) model Proposed by Peter Chen (BS NTU EE ‘68) in 1976 popular for conceptual design Relational model Object-oriented model A schema is a description of a particular collection of data in a given data model. Eg. How to describe a book? Some example for ER-modal num attribute key id name password ISBN author user order relation book Entity set price Relational Model Most widely used today Microsoft SQL Server Oracle MySQL SyBase IBM DB2 Microsoft Office Access Why People Like It Simple Each relation is represented as a table of rows and columns Easy to understand Ease of expressing complex query (using SQL) on the data Efficient query evaluation (using query optimization) Example of Relation A relation has two parts: Relational Schema defines column heads of the table. Relational Instance contains the data rows (called tuples or records) of the table. ISBN author price 1234567890 羊肉爐不是故意的 LogyDog 200 0780785678 奼紫嫣紅牡丹亭：四百年青春之夢 白先勇 380 白先勇 270 0025201179 name 孽子 Example of Relation Field Also called an attribute or a column Key a set of minimal fields that can uniquely identify a tuple in a relation ISBN author price 1234567890 羊肉爐不是故意的 LogyDog 200 0780785678 奼紫嫣紅牡丹亭：四百年青春之夢 白先勇 380 白先勇 270 0025201179 name 孽子 Convert from ER-Model to Relational Model num id password user name ISBN order author book price user id book password ISBN name ISBN num order id author price How to use DBMS? SQL Structured Query Language Developed by IBM (system R) in the 1970s Current standard: SQL-99 DDL: Data Definition Language DML: Data Manipulation Language SQL Basic Commands DDL create table: create a table drop table: delete a table alter table: alter a field in a table DML insert: add a record delete: delete a record update: change field values in a record select: query data satisfying some condition Example of create table & drop table create table book( ISBN integer, name char(255), author char(127), price integer, primary key(ISBN) ) drop table book ISBN name author price Example of alter table alter table book add m_price integer alter table book drop m_price ISBN name author price m_ price Example of insert insert into book( ISBN, name, author, price ) values ( 1234567890, “羊肉爐不是故意的”, “LogyDog”, 200 ) ISBN name 1234567890 羊肉爐不是故意的 author price LogyDog 200 Example of delete delete from book where author = ‘白先勇’ ISBN author price 1234567890 羊肉爐不是故意的 LogyDog 200 0780785678 奼紫嫣紅牡丹亭：四百年青春之夢 白先勇 380 白先勇 270 0025201179 name 孽子 Example of update update book as b set b.name = ‘羊肉爐不是故意的’ where b.author = ‘LogyDog’ update book as b set b.price = b.price * 0.9 ISBN author price 1234567890 羊肉爐是故意的 羊肉爐不是故意的 LogyDog 200 180 0780785678 奼紫嫣紅牡丹亭：四百年青春之夢 白先勇 380 342 白先勇 270 243 0025201179 name 孽子 Example of select select book as b where b.price<300 and author=‘白先勇’ ISBN author price 1234567890 羊肉爐不是故意的 LogyDog 200 0780785678 奼紫嫣紅牡丹亭：四百年青春之夢 白先勇 380 白先勇 270 0025201179 name 孽子 Conclusion Reference Database Management System, 3rd Ed., by R&G http://mll.csie.ntu.edu.tw/course/database/in dex.html - by Hao-hua Chu Computer Science -- An Overview, 7th Ed, by J. Glenn Brookshear