Introduction to Database System

1. Introduction to Database System Adisak Intana Lecturer Chapter 2: Database Architecture

2. Table Contents Database system environment Database system architecture Data independence Database users Data dictionary Advantages and disavantages of database system 2

3. Table Contents Database system environment Database system architecture Data independence Database users Data dictionary Advantages and disavantages of database system 3

4. File System • A computerized data processing environment which is composed of four major parts : • Hardware The computer hardware : CPU, mainmemory, and I/O devices • Software • Application programs • Data is in a collection of independent files • Users • Naive users • Application programers 4

5. Application programs Operating system Files File Processing System Environment 5

6. Database system • A computerized data processing environment which is composed of four major parts : • Hardware The computer and the secondary storage devices • Software • Data is in a collection of related files in the form of database • Users 6

7. Database processing system environment Application program DBMS Operating system Database 7

8. Staff recruitment system Department1 Customers’ orders system Department2 Sales accounting system Department3 Employee data Customer data Product data Database processing using DBMS DBMS Database 8

9. Database • A shared collection of interrelated data. • A collection of data stored in a standardized format and designed to be shared by multiple users. • A collection of persistent related data of interest of a specific enterprize. • Database contains information about a particular enterprise. 9

10. Student ID Name Sex Age 4702777 Amporn F 18 4702888 Sasin M 19 Units of data File Record Field Database Student file : 10

11. Units of data • Physical level Unit of data used by the hardware of computers. • Bit • Byte • Word 11

12. Units of data • Logical level Unit of data used or viewed by the users. • Field • Record • File • Database 12

13. database Student and teacher files file Listing of all students in student file record 4720777, Amporn, F, 18 field Amporn 01001011 (letter K in ASCII) byte bit 0 or 1 Units of data 13

14. Database management system (DBMS) • A collection of programs that enables users to modify and extract information from a database. • A suite of programs which typically manage large structured sets of persistent data and offering ad hoc query facilities to many users. 14

15. Database users • Users in database environment are people whose jobs involve the day-to-day use of large database. • Users are differentiated by the way they expect to interact with the database management system. • Naive users Invoke and use the application programs that have been written for specific tasks. 15

16. Table Contents Database system environment Database system architecture Data independence Database users Data dictionary Advantages and disavantages of database system 16

17. User1 User3 User4 User2 User work area User work area User work area User work area Externalal level Logical database Logical level Physical database Physical level Simplified view of database system 17

18. External level เป็นสถาปัตยกรรมข้อมูลระดับนอกสุด ใกล้ชิดกับผู้ใช้ฐานข้อมูลมากที่สุด เพราะเป็นระดับที่กล่าวถึงมุมมองข้อมูลของผู้ใช้แต่ละคน (user view) ผู้ที่เกี่ยวข้องคือ user ที่ใช้ฐานข้อมูล 18

19. Logical level เป็นสถาปัตยกรรมข้อมูลระดับที่อยู่ระหว่าง Internal กับ External ทำหน้าที่ถ่ายทอดรูปแบบของ Physical Level ให้อยู่ในรูปแบบที่สามารถเข้าใจได้ง่าย (Logical Structure) ผู้ที่เกี่ยวข้องกับข้อมูลในระดับนี้คือ DBA 19

20. Physical level เป็นสถาปัตยกรรมฐานข้อมูลระดับในสุด ซึ่งอธิบายโครงสร้างของการจัดเก็บข้อมูลในเชิงกายภาพ (Physical Structure) ผู้ที่เกี่ยวข้องกับระดับนี้คือ DBA และ System Administrator 20

21. Example • สถาปัตยกรรมระบบฐานข้อมูลทั้ง 3 ระดับ สามารถอธิบายได้โดยใช้ ฐานข้อมูลระบบงานสินค้าคงคลัง (northwind) ดังนี้ • Physical Level • ฐานข้อมูล Northwind เก็บอยู่ในไฟล์ที่ชื่อว่า northwind.mdb ซึ่งเป็นไฟล์ของโปรแกรม MS Access • ทำงานอยู่บนระบบปฏิบัติการ MS Windows 95/98 • เก็บอยู่ใน directory c:\program files\microsoft office\samples บน Hard Disk ณ ตำแหน่ง Sector 84A6, Track 15, Cylinder 8 เป็นต้น 21

22. Example • External Level ฐานข้อมูล Northwind แบ่งให้พนักงานแผนกต่าง ๆ มองเห็นได้ต่างกัน ดังนี้คือ • พนักงานฝ่ายขาย มองเห็นข้อมูลในCaregories, Orders, Order Details, Products, Categories • พนักงานฝ่ายบุคคล มองเห็นข้อมูลใน Employees, Orders • ผู้จัดการทั่วไป (GM) มองเห็นข้อมูลทั้งหมด 22

23. Example • Logical Level ฐานข้อมูล Northwind ประกอบด้วย • Tables คือ Categories, Customers, Employees, Order Details, Orders, Products, Shippers และ Suppliers • Queries คือ Category Sales for 1995, Current Product List, Employee Sales by Country, 23

24. Simplified view of database system DBA(s)/Programmers/Naive users Programs/Query Language Programs/Query Processor Programs to access stored data Users’ database Data dictionary 24

25. Table Contents Database system environment Database system architecture Database users Data independence Data dictionary Advantages and disavantages of database system 25

26. Database users • Application programmers Write application programs and interact DBMS through database language. • Database administrator 26

27. Database administrator • Responsible for all the activities of the database system environment. • Must have a good understanding of the enterprise’s information resources and needs. 27

28. Database administrator • Database administrator's duties include : • Define the schema • Define storage structure and access method • Modify schema and physical organization • Granting user authority to access the database • Specifying integrity constraints • Acting as liaison with users • Monitoring performance and responding to changes in requirements 28

29. Table Contents Database system environment Database system architecture Database users Data independence Data dictionary Advantages and disavantages of database system 29

30. Database independence • The ability to change the description of a database structure at one level of the database system architecture without having to change the description at the next higher level database structure. 30

31. Table Contents Database system environment Database system architecture Database users Data independence Data dictionary Advantages and disavantages of database system 31

32. Data dictionary • The data dictionary or system catalog contains metadata • Metadata means “data about the data” • This metadata is the definitions of the structure, data type, integrity constraints, etc. of the database and other objects in the DBMS rather than the users’ raw data. 32

33. Data dictionary • The data dictionary can be regarded as a database in its own right, but it is the system database rather than user database. • The data in the data dictionary should be integrated into a database with the same structure as the user database. • It should certainly be possible to query the data dictionary just like the users’ database. 33

34. Example Data dictionary 34

35. Example Data in Database 35

36. Table Contents Database system environment Database system architecture Database users Data independence Data dictionary Advantages and disavantages of database system 36

37. Advantages of database approach • Providing a solution to basic data storage and retrieval. • Providing flexibility and availability of up-to- minute information. • Reducing data redundancy. • Obtaining data consistency. • Sharing of data. 37

38. Advantages of database approach (continue) • Standard can be enforced. • Ad hoc queries are possible. • Ease of application programs development. • Reduce application program development time. • Uniform security, privacy, and integrity can be applied. • Obtaining data independence. 38

39. Disadvantages of database approach • High cost • Conversion from file processing to DBMS • More sophisticated hardware and software • Higher operating costs (personnel) • Complexity of DBMS administration • Vulnerability to failure • Difficulty of recovering & securing data assets 39