1 / 33

Introduction to Database Management System

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

jzielinski
Download Presentation

Introduction to Database Management System

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction toDatabase Management System 電機三 趙上鋒

  2. Outline • What is DBMS? • What can DBMS do? • How to design a database? • How to use DBMS? • Conclusion • Reference

  3. What is DBMS?

  4. Data v.s. Information • Data • Anything stored without summarized or analyzed. • Information • Data that has been proceeded to be meaningful for users.

  5. Example Information Data

  6. Database • Stores organized data so that you can query from it or update it. • Like an electrical shelf to put data with organization.

  7. x x Application Application From user to data Database Teacher (user) data Student (user)

  8. 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.

  9. x x Application Application Relation between App & DBMS DBMS Conceptual schema Physical schema Teacher (user) View1 data Student (user) View2

  10. What can DBMS do?

  11. 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.

  12. 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

  13. 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

  14. 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!

  15. How to design a database

  16. 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?

  17. num id password order user Some example for ER-modal attribute key name ISBN author book Entity set relation price

  18. Relational Model • Most widely used today • Microsoft SQL Server • Oracle • MySQL • SyBase • IBM DB2 • Microsoft Office Access

  19. 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)

  20. 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.

  21. 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

  22. Convert from ER-Model to Relational Model num id password name ISBN author order user book price user book order

  23. How to use DBMS?

  24. SQL • Structured Query Language • Developed by IBM (system R) in the 1970s • Current standard: SQL-99 • DDL: Data Definition Language • DML: Data Manipulation Language

  25. 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

  26. 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

  27. Example of alter table alter table book add m_price integer alter table book drop m_price

  28. Example of insert insert into book( ISBN, name, author, price ) values ( 1234567890, “羊肉爐不是故意的”, “LogyDog”, 200 )

  29. Example of delete delete from book where author = ‘白先勇’

  30. 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 羊肉爐不是故意的 180 342 243

  31. Example of select select book as b where b.price<300 and author=‘白先勇’

  32. Conclusion

  33. Reference • Database Management System, 3rd Ed., by R&G • http://mll.csie.ntu.edu.tw/course/database/index.html - by Hao-hua Chu • Computer Science -- An Overview, 7th Ed, by J. Glenn Brookshear

More Related