Introduction to Database Design Methodology

1. Introduction to Database Design Methodology Chapter 2 Three Levels of Data Abstraction in Database

2. Learning Goals • To understand the idea of data abstraction in database system • To describe the needs of the three levels of data abstraction in database architecture • To recognise the contents of the three levels: view level, conceptual level, physical level

3. Table of Contents • What is data abstraction? • The three levels of data abstraction in database: • View Level • Conceptual Level • Physical Level • Three levels of data abstraction - summary

4. What is Data Abstraction?

5. What is Abstraction? • Abstraction is the process of taking out essential characteristics from something while ignoring the details or unnecessary parts. • Depending on our different points of views, we may focus on different characteristics of that thing. • Reference:http://people.cs.vt.edu/~kafura/cs2704/abstraction.html

6. What is Abstraction? Question for discussion • From the point of view of a computer user, what are the important characteristics of a hard disk? (Image used under the GNU Free Documentation License)

7. What is Data Abstraction? • Data abstraction is the process of taking out essential characteristics from a database system while ignoring or hiding the details or unnecessary parts. • For example, the users of a database system only use the portions of data they need, they do not need to know the complex data structure and the way that data are stored. Therefore, the unnecessary details can be hidden from the users. This is the idea of data abstraction. • Depending on which “level” of the database system we are working on, we will focus on different characteristics of the database. • There are different levels of data abstraction. We will study such levels in the next part.

8. Three Levels of Data Abstraction

9. Three Levels of Data Abstraction • There are three levels of data abstraction in a database system, namely conceptual level, physical level and view level. • To understand the needs of these three levels of data abstraction, let us consider the case of a school library system.

10. Three Levels of Data Abstraction Case Study – School Library System • A computerized school library system consists of a database storing the data of the books and users, and the borrowing records, etc. • Users can access the library system by using web browser through the school network or the Internet.

11. Three Levels of Data Abstraction Case Study – School Library System (con’t) • There are different people working on different parts of the school library system. • the general users • Librarians • database designers • database administrators • … • These people will perceive different “levels” of the database.

12. View Level

13. Three Levels of Data Abstraction Case Study – School Library System (con’t) • Basic users of the system: • General users • They only need to know their own borrowing records and the information of the books, etc. • They do not need to know the borrowing records and late-return records of other users.

14. Three Levels of Data Abstraction Case Study – School Library System (con’t) • Basic users of the system: • Librarians • They can access the borrowing records of individual users by supplying the corresponding users’ IDs. • They also need to view and print the late-return records on a particular date. • Both the library users and librarians do not need to know the database structure and the way that data are stored.

15. Three Levels of Data Abstraction Chan May May’s borrowing records School Library System Borrowing records of different users Chan May May Structure hidden from users Chan Tai Keung’s borrowing records Librarian Late-return records of all users on 1-1-2008 Chan Tai Keung

16. Three Levels of Data Abstraction Case Study – School Library System (con’t) • A typical user interface viewed by a general user

17. Case Study – School Library System (con’t) A sample late-return report obtained by a librarian Three Levels of Data Abstraction

18. Three Levels of Data Abstraction Case Study – School Library System (con’t) View Level of the Database System • The level of data abstraction seen by the library users (general users and librarians) is known as view level. • Different users may have different user views on the database in this view level. • A user view is a representation of the portion of data that a user needs. Some common forms of user views are: data entry forms, web pages and reports, etc.

19. Three Levels of Data Abstraction • Examples of different forms of user views Report Data Entry Form Web Page

20. Three Levels of Data Abstraction The needs of View Level • It allows different users to view the portions of data in the forms they needed. This can simplify users' interaction with the database system. • It prevents users from accessing certain parts of the database. This can enhance system security. • It hides the complex database structure from users as they do not need to view such structure.

21. Activity 1

22. Activity 1 • Refer to Worksheet 1 of Chapter 2 • The aim of this activity is to let you understand the basic idea of data abstraction in the view level. Users only view the portions of data they need.

23. Activity 2

24. Activity 2 • Refer to Worksheet 2 of Chapter 2 • The aim of this activity is to let you have practice on designing the user view of a database system.

25. Conceptual Level

26. Three Levels of Data Abstraction Case Study – School Library System (con’t) • The other people working on the database system are the database designer and the database administrator. • A database designer is a person who designs and creates a database system. • A database administrator (DBA) is a person who is responsible for maintaining and monitoring a database system. • Sometimes the database designer and the DBA are the same person.

27. Three Levels of Data Abstraction Case Study – School Library System (con’t) • Both the database designer and DBA of the library system need to view the structure of the database. • They need to know what data are stored and how the data are related in the database. • That is, in a relational database model, they need to know the structures of tables and the relationships among the tables. • However, they do not need to known how these tables are physically represented and stored in the storage medium.

28. Three Levels of Data Abstraction School Library System Librarian Hidden from database designer / DBA Database Designer / DBA

29. Three Levels of Data Abstraction Case Study – School Library System (con’t) Conceptual Level of the Database System • The level of data abstraction perceived by the database designer and DBA is known as conceptual level. • This level consists of the entire logical structure of the database. • For a relational database, the logical structure is a collection of tables and the relationships among tables. • People working on this level do not need to known how this logical data structure is physically represented in the storage medium. • Remark: A common tool for designing the logical structure in conceptual level is Entity-Relationship (ER) diagram, which will be introduced in Chapter 4.

30. dob checkout_date due_ date sex book_id title user_id Borrow M M USER BOOK name author publisher borrow_code Three Levels of Data Abstraction Case Study – School Library System (con’t) Entity-Relationship Diagram(details will be introduced later) Logical Design(details will be introduced later) USER(user_id, name, sex, dob) BOOK(book_id, title, publisher, author) BORROW(borrow_code, user_id, book_id, checkout_date, due_date)

31. Three Levels of Data Abstraction Case Study – School Library System (con’t) Table Structure & Relationships A view of the table structure and relationships in Microsoft Access A set of database tables

32. Three Levels of Data Abstraction The needs of Conceptual Level • It provides a way for the database designer and DBA to design and view the logical structure of the database. • It hides the implementation details of how the data and database structure (e.g. tables) are represented and stored in the storage medium.

33. Physical Level

34. Three Levels of Data Abstraction Case Study – School Library System (con’t) • How is the relational databaseof the library system actually represented and stored in the storage medium? • It depends on what database management system (DBMS) is used. • Remark: A Database Management System (DBMS) is a software that enables people to create, manage and access a database.

35. Three Levels of Data Abstraction Case Study – School Library System (con’t) • For example • If the database is built on Microsoft Access (version 2003 or before), the database is a .mdb file (e.g. LIBRARY.mdb) which stores all the relevant tables (e.g. USER, BOOK, BORROW). • If the database is built on MySQL, the databaseis a folder (e.g. with the name LIBRARY) which stores the relevant files (.frm, .myd, .myi) that represent the tables and other information of the database. For a table BOOK in the database, there should be a set of files called BOOK.frm, BOOK.myd and BOOK.myi. Ref: http://forge.mysql.com/wiki/MySQL_Internals_File_Formats

36. MS Access LIBRARY.mdb MySQL LIBRARY BOOK.frm BOOK.myd BOOK.myi Three Levels of Data Abstraction School Library System OR

37. Three Levels of Data Abstraction Case Study – School Library System (con’t) Physical Level of the Database System • This level of data abstraction is concerned with how the data and database structure are actually stored in the storage medium (e.g. hard disk). That is, it is about the physical implementation of the database. • For a relational database, it is about how the tables and relationships between tables are represented and stored in the storage medium. • The implementation of physical level depends on the database management system (DBMS) that are being used.

38. Three Levels of Data Abstraction The needs of Physical Level • Physical level is needed for the implementation of the database system. • It is about how the logical structure of the database is physically implemented in the storage medium.

39. Three Levels of Data Abstraction • Let us study the three levels of data abstraction in the library database system again. (Refer to the next slide) • Pay attention to the different characteristics of the database that are perceived in each level.

40. Three Levels of Data Abstraction School Library System View Level General User Librarian Conceptual Level Database Designer / DBA Physical Level

41. Activity 3

42. Activity 3 • Refer to Worksheet 3 of Chapter 2 • In this activity, you will have hands-on practice in using a DBMS (e.g. Microsoft Access). You will observe the view level and conceptual level of a database in the DBMS.

43. Three Levels of Data Abstraction - Summary

44. Three Levels of Data Abstraction • There are three levels of data abstraction in database architecture: • View Level • Conceptual Level • Physical Level

45. Three Levels of Data Abstraction • View Level (External Level) • This level describes the way that users see the data. The description also known as external schema. • Remark: A schema is a precise description of the structure of something. • There are different external schemas that give each class of users a view of the database that is tailored for their needs. • A user view is a representation of the portion of data/information that a user needs. Some common forms of user views are: data entry forms, web pages and reports, etc.

46. Three Levels of Data Abstraction • Conceptual Level (Logical Level) • This level describes what data are actually stored in the database and the relationships among the data. The description is known as conceptual schema. • It contains the entire logical structure of the database as seen by the database designer / database administrator (DBA). • For a relational database, the logical structure is a collection of tables. • The users of this level, usually the DBAs, do not need to known how this logical structure is physically represented. • Common tool for designing the logical structure in this level is Entity-Relationship (ER) diagram.

47. Three Levels of Data Abstraction • Physical Level (Internal Level) • This lowest level describes how the data are actually stored in the storage medium (e.g. hard disk). The description is called internal schema. • It is the way that the Database Management System (DBMS) and Operating System (OS) “perceive” the data. • It is concerned with such things as: • storage space allocation; • file organization of database; • reading and writing of data on storage medium; • data compression and data encryption techniques, etc.

48. Disk Three Levels of Data Abstraction DBMS View Level User view 3 User view 1 User view 2 External Schema 1 External Schema 2 External Schema 3 Conceptual Schema Conceptual Level Physical Level Internal Schema

49. Needs of the Levels of Data Abstraction • The needs of the three levels of data abstraction: • It allows independent customized user views. Different types of users perceive different portions of data in different forms. • It hides the complex logical structure from general users and the physical storage details from DBA. • It allows DBA to change the logical structure of the database without affecting the user views. This is known as logical data independence. • It allows changes to be made on the physical storage without affecting the logical structure of the database. This is known as physical data independence. • Items 3 and 4 above imply that DBA can change the physical aspects of storage without affecting the users.

50. Three Levels of Data Abstraction DBMS Users External Schema 1 External Schema 2 External Schema 3 Logical data independence Conceptual Schema DBA Physical data independence Internal Schema OS/Storage