1 / 25

資料庫系統簡介 臺北大學統計系 莊東穎

資料庫系統簡介 臺北大學統計系 莊東穎. Data Hierarchy. Hierarchy of Data bits bytes (characters) fields - characters joined together records - a collection of related fields files - a collection of records databases - a collection of linked files. Data Hierarchy. Bits Bytes Fields Records Files

guillaume-jair
Download Presentation

資料庫系統簡介 臺北大學統計系 莊東穎

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. 資料庫系統簡介臺北大學統計系莊東穎 T.Y. Juang

  2. Data Hierarchy • Hierarchy of Data • bits • bytes (characters) • fields - characters joined together • records - a collection of related fields • files - a collection of records • databases - a collection of linked files T.Y. Juang

  3. Data Hierarchy Bits Bytes Fields Records Files Databases T.Y. Juang

  4. Definition • Database • a collection of data organized in a manner that allows users to retrieve and use those data • an integrated collection of logically related records and files • a collection of information stored in an organized form in a computer T.Y. Juang

  5. Why Database? • Compactness, Speed, Less Drudgery & Currency • A database consists of some collection of persistent data that is used by the application systems of some given enterprise • Data Administration • done by Data Administrator (DA) • a senior management level • decide what data should be stored in the database; establish the policy of maintaining and dealing with the data (e.g., data security policy) • Database Administration • done by Database Administrator (DBA) • technical level • create database and control the performance of the system T.Y. Juang

  6. Definition • DataBase Management System (DBMS) • a software package used to interact with a database • a software tool for organizing storage and retrieval of that information • a software involves the control of how database are created, interrogated, and maintained to provide information needed by end users and the organization T.Y. Juang

  7. Major components of a database system • Data: integrated and shared. • Hardware: disk, CPU, Main Memory ... • Software: DBMS • Users : 1. Application programmers 2. End users 3. Database administrator (DBA) • Defining conceptual schema • Defining internal schema • Liaising with users • Defining security and integrity checks • Defining backup and recovery procedures • Monitoring performance and changing requirements T.Y. Juang

  8. Files and File Management Payroll files Invoicing files Inventory files Payroll program Invoicing program Inventory control program T.Y. Juang

  9. Databases Payroll programs Invoicing programs Inventory control programs Database DBMS Database DBMS T.Y. Juang

  10. Database System • Database System Considerations • what information the database will contain? • how much access should be given to various users? • how should the database be physically organized? • how can the database structure be made to seem logical to the user? T.Y. Juang

  11. Design Goals • Goals • Overcome data redundancy • Overcome program dependency on data • Allow a large number of users to draw information easily • Link several files for data processing T.Y. Juang

  12. User A1 User A2 User B1 User B2 User B3 Host Language + DSL Host Language + DSL Host Language + DSL Host Language + DSL Host Language + DSL External View B External View A External schema A External schema B External/conceptual mapping B External/conceptual mapping A Database management system (DBMS) Conceptual View Conceptual schema < DBA Conceptual/internal mapping (Build and maintain schemas and mappings) Storage structure definition (Internal schema) Stored database (Internal View) Database System Architecture T.Y. Juang

  13. Database Models • Models (Structures) • a data model describes components of the database and how they work together • a data model describes linking of records that share fields • Common data models • Hierarchical • Network • Relational • Object Oriented T.Y. Juang

  14. Models Hierarchical database Network database Relational database T.Y. Juang

  15. Hierarchical Data Models • Hierarchical • The first conceptual model • One-to-many relationship • All relationships are established when the database is created • All access is through pre-established links • A child can have only one parent • IMS, DL/1 (IBM), System 2000(MRI), METABAS(PMI), MARK IV(INFORMATICS)... T.Y. Juang

  16. Network Data Models • Network • Like a hierarchical database • Can represent more complex logical relationships • A child can have more than one parent • Parent is called an owner, child is called a member • CODASYL(Conference On Data Systems Languages ) • Permits all relationships: one-to-one, one-to-many, many-to-many • DMS-11(Burroughs), DMS-170(CDC), TOTAL(Cincom), IDMS(Cullinane), DMS1100(Univac) T.Y. Juang

  17. Network Data Models • Network • Advantages • More flexible than hierarchical database • Successfully implemented and marketed • Disadvantages • Schema must be defined at time database is created • Retrieval of data is based solely on the schema T.Y. Juang

  18. Relational Data Models • Relational • E. F. Codd (1969) • Data organized into tables or files called “relations” • Tables have - rows, tuples or record - columns, fields or attributes • Range of values for a field is the domain • Relationships are determined at time of use • Relationships are created by operations on tables • Operations are performed at the time of a request • No predetermined schema is required T.Y. Juang

  19. Relational Data Models • Relational • Users can ask virtually any questions • Virtually all modern DBMS support these • Most popular • DB2 (IBM), Oracle (Oracle), SQL Server (Sybase), INGRES (Computer Associate), INFORMIX OnLine (INFORMIX), MS SQL Server (MS), Access (MS), …. T.Y. Juang

  20. 關聯性資料庫管理系統 (續) Stores Table stor_id 7066 7067 7131 8042 6380 7896 stor_name Bamum’s News & Brews Doc-U-Mat:Quality Laundry & Books Bookbeat Eric the Read Books Fricative Bookshop city Tustin Los Gatos Remulade Portland Seattle Fremont state CA CA WA OR WA CA zip 92789 96745 98014 89076 98056 90019 T.Y. Juang

  21. Stores Table stor_id 7066 7067 7131 8042 6380 7896 stor_name Bamum’s News & Brews Doc-U-Mat:Quality Laundry & Books Bookbeat Eric the Read Books Fricative Bookshop city Tustin Los Gatos Remulade Portland Seattle Fremont state CA CA WA OR WA CA zip 92789 96745 98014 89076 98056 90019 Sales Table stor_id 7066 7067 7131 7131 8042 8042 6380 6380 8042 7896 7896 7896 8042 7066 7131 7131 7131 7131 7067 7067 7067 stor_name QA7442.3 D4482 N914008 N914014 423LL922 423LL930 722a 6871 P723 X999 QQ2299 TQ456 QA879.1 A2976 P3087a P3087a P3087a P3087a P2121 P2121 P2121 data Sep Sep Sep Sep Sep Sep Sep Sep Mar Feb Oct Dec May May May May May May Jun Jun Jun 13 14 14 14 14 14 13 14 11 21 28 12 22 24 29 29 29 29 15 15 15 1985 1985 1985 1985 1985 1985 1985 1985 1988 1988 1987 1987 1987 1987 1987 1987 1987 1987 1987 1987 1987 12:AM 12:AM 12:AM 12:AM 12:AM 12:AM 12:AM 12:AM 12:AM 12:AM 12:AM 12:AM 12:AM 12:AM 12:AM 12:AM 12:AM 12:AM 12:AM 12:AM 12:AM qty 75 10 20 25 15 10 3 5 25 35 15 10 30 50 20 25 15 25 40 20 20 payterms On invoice Net 60 Net 30 Net 30 On invoice On invoice Net 60 Net 60 Net 30 On invoice Net 60 Net 60 Net 30 Net 30 Net 60 Net 60 Net 60 Net 60 Net 30 Net 30 Net 30 title_id PS2091 PS2091 PS2091 MC3021 MC3021 BU1032 PS2091 BU1032 BU1111 BU2075 BU7832 MC2222 PC1035 PC8888 PS1372 PS2106 PS3333 PS7777 TC3218 TC4203 TC7777 Discounts Table stor_id 7131 8042 discount 6.7 5.0 T.Y. Juang

  22. 關聯性資料庫管理系統 (續) • 資料具整合性(Data Integrity) • 使用結構化查詢語言(Structured Query Language)維護及查詢資料 • 方便性 • 高效率的資料儲存方式 • 安全性 T.Y. Juang

  23. RDBMS基本功能 • Data consistency • Recovery • Backup and Restore • Security • Stored Procedure • Triggers • Data Definition • Data Manipulation • Data Control Statement • Creating Databases • Creating tables • Data Retrieval • Data Modification • view • grant T.Y. Juang

  24. Object Oriented Data Model • Object Oriented Database • Better handle more complex types of data (graphics, pictures, voice, text) than other database structures • Support encapsulation, inheritance • Jasmine (CA) -物件資料庫系統 T.Y. Juang

  25. Normalization • Normalization • a logical database design involves using formal methods to separate the data into multiple, related tables • A greater number of narrow tables (with fewer columns) is characteristic of a normalized database • To improve performance • First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF) and Forth Normal Form (4NF) T.Y. Juang

More Related