Database Fundamentals Introduction

1. Database FundamentalsIntroduction • Introduction to database systems • Database Management Systems (DBMS) • Type of Database • Database Design • Database Design Considerations • Component of Database Systems

2. Introduction to Database Systems • A database is a software program that stores information which relates to a particular activity or purpose • Examples: • A bank needs to store the information relating to customer accounts • A hospital needs to keep data about patients and the medication dispensed, • A university needs to maintain record of its students, • A internet sales business needs to ..

3. Introduction to Database Systems • A database includes tools to help organise and maintain the data. • A database design engineer must know and be able to use these tools in order to make the database efficient and accurate. • In a properly designed database the information is updated once and all other occurrences of this within the database will be updated automatically. • A principal requirement of any database system is the ability to generate, store and retrieve data efficiently.

4. Basic components of a database system In order to convert data into useful information a set of software tools are need, SQL, Form, etc..

5. Database FundamentalsIntroduction • Introduction to database systems • Database Management Systems (DBMS) • Type of Database • Database Design • Database Design Considerations • Component of Database Systems

6. Database Management Systems (DBMS) • DBMS is a collection of programs provided by the vendor, which enable the data to be accessed, filtered and generally processed efficiently, in order to yield useful information for the user

7. Database Management Systems (DBMS) • In principle, user will generate a question (query) and DBMS will search the dB to find the answer, which will be returned to user • A DBMS will include additional tools to provide graphical user interfaces and report generation programs to facilitate the delivery of information to the user. • It will also provide tools to create, edit, process, and modify data and also to program the database. SQL is a standard language accessing relational databases.

8. What is a Database Management System? • A Database Management System (DBMS) is a program that manages a database: • Supports a high-level access language (e.g. SQL). • Application describes database accesses using that language. • DBMS interprets statements of language to perform requested database access.

9. SQL examples: • Example 1: SELECT Course_Number FROM StudentRecords WHERE student_name = ‘ ‘; • Example 2: SELECT student_number FROM StudentRecords WHERE BEngCourse = ‘2388’ AND average_mark >70; • These examples show that SQL is a very useful tool for interrogating a database

10. Database FundamentalsIntroduction • Introduction to database systems • Database Management Systems (DBMS) • Type of Database • Database Design • Database Design Considerations • Component of Database Systems

11. Types of Database • Classification by user • Single user databases: Access was initially designed as a single user database • If Single user database system used for multiple user, there are two problems need to be concern: Data corruption and Performance & Speed.

12. Types of Database • Classification by user • Single user databases: If there are less than 50 users than this type of database is referred to as a workgroup database, if the users number is bigger than 50, it is referred to as an Enterprise database

13. Microsoft’s solutions for the multi-user market • File-server: Access database is configured as a network share so that multiple users can use it simultaneously • Client/Server: Access work as a client, there is MS SQL server in the server side • Database replication: MS networking supports replication through its directory services. • Web-based database solutions. ASP/Access or ASP/SQL server

14. Classification by location • Centralised Database: In a centralised database system there is only one database which is shared between users. • Q: What is the main disadvantage of centralised databases? How do we solve it? • A: Perhaps the main disadvantage of this type of database is that a failure of the server running the database will prevent all users from using the database until the server is back on-line. To overcome this problem larger database applications tend to be of a distributed type • Q: What is the main disadvantage of centralised databases? How do we solve it? • A: Perhaps the main disadvantage of this type of database is that a failure of the server running the database will prevent all users from using the database until the server is back on-line. To overcome this problem larger database applications tend to be of a distributed type

15. Classification by location -Distributed Database

16. Classification by location -Distributed Database • A distributed database is commonly found in networks. Here the current version of the database is kept on different servers. Any changes are replicated across the network so that all copies of the database are current. • Q: What are the main disadvantages and disadvantages of distributed databases? • The main advantage of this arrangement is perhaps in fault tolerance. If one of the servers is down, others can take over the workload. These type of databases are common in enterprise databases. • The main disadvantage is cost, both in terms of software licensing and the cost of the hardware in a network. In heavy utilisation environment network traffic may also be a problem.

17. Classification by type • Classification by type distinguishes between a transactional database and a data warehouse. • The distinction is fairly intuitive with the transactional database being used to process day-to-day transaction information and maintain a record of decisions made relating to this data. Due to the fact that many transactions can occur in this database system, and that these transactions will impact on decisions made on that day, these types of databases are treated as time critical. • On the other hand, the data warehouse is used to generate strategic decisions and the data stored tends to be longer term. Consequently this data is considered to be NON-time-critical. • In terms of the number of applications the transactional database (also referred to as production database) is the more common of the two database types.

18. Classification by type

19. What is a Transaction? • When an event in the real world changes the state of the enterprise, a transaction is executed to cause the corresponding change in the database state • With an on-line database, the event causes the transaction to be executed in real time • A transaction is an application program with special properties - discussed later - to guarantee it maintains database correctness

20. What is a Transaction Processing System? • Transaction execution is controlled by a TP monitor • Creates the abstraction of a transaction, analogous to the way an operating system creates the abstraction of a process • TP monitor and DBMS together guarantee the special properties of transactions • A Transaction Processing System consists of TP monitor, databases, and transactions

21. Transaction Processing System DBMS database transactions DBMS database TP Monitor

22. System Requirements • High Availability: on-line => must be operational while enterprise is functioning • High Reliability: correctly tracks state, does not lose data, controlled concurrency • High Throughput: many users => many transactions/sec • Low Response Time: on-line => users are waiting

23. System Requirements (con’t) • Long Lifetime: complex systems are not easily replaced • Must be designed so they can be easily extended as the needs of the enterprise change • Security: sensitive information must be carefully protected since system is accessible to many users • Authentication, authorization, encryption

24. OLTP vs. OLAP • On-line Transaction Processing (OLTP) • Day-to-day handling of transactions that result from enterprise operation • Maintains correspondence between database state and enterprise state • On-line Analytic Processing (OLAP) • Analysis of information in a database for the purpose of making management decisions

25. OLAP • Analyzes historical data (terabytes) using complex queries • Due to volume of data and complexity of queries, OLAP often uses a data warehouse • Data Warehouse - (offline) repository of historical data generated from OLTP or other sources • Data Mining - use of warehouse data to discover relationships that might influence enterprise strategy

26. Examples - Supermarket • OLTP • Event is 3 cans of soup and 1 box of crackers bought; update database to reflect that event • OLAP • Last winter in all stores in northeast, how many customers bought soup and crackers together? • Data Mining • Are there any interesting combinations of foods that customers frequently bought together?

27. Database FundamentalsIntroduction • Introduction to database systems • Database Management Systems (DBMS) • Type of Database • Database Design • Database Design Considerations • Component of Database Systems

28. Database Design Very basic level database components

29. Database Design • The design of a database is concerned with the way that these components are organised and this determines how efficient the design solution will be. • To design the data structure we need to ask ourselves a few questions, for example: • 1. Do we know all the fields that we need to include so that our records and files contain all the necessary data? • 2. What fields will each of our records contain. • 3. Do we allow fields to duplicate in records? This will create data redundancy. • 4. What records are needed in files?

30. Database FundamentalsIntroduction • Introduction to database systems • Database Management Systems (DBMS) • Type of Database • Database Design • Database Design Considerations • Component of Database Systems

31. Database design considerations • Data dependence and Structural dependence • Data is organised by the designer into a structure that suits its application. Thus, there is a structural dependence associated with data in a database. • Structural dependence deals with the mechanism for changing database structure. • Good database design should ensure that it is relatively easy to make the necessary modifications to the structure. • For example, the addition of a field to an existing file structure. • data dependence and is concerned with the issues of data itself, rather than the structure. • For example, data dependence will be concerned with how easy it is to change a field from say being an integer to a decimal.

32. Database design considerations • Data Redundancy • Data Redundancy is perhaps the most common problem in dB design. This is to say that the same data is stored in a number of locations. For example, a student’s address is kept in the finance file as well as enrolment and exam results file. • Good database design will ensure that the data is not duplicated in this manner. This will save on storage space (memory) and also improve the speed of data access and processing. • data integrity, refers to the consistency of data which is duplicated. • For example, if our design requires us to maintain a student’s e-mail address in a number of different locations, then failing to update a change in e-mail address in all locations will result in data inconsistency.

33. Database design considerations • Data anomalies • Modification, insertion and deletion anomalies all relate to data in a one-to-many relationship. For example, there is one personal tutor to many students. So this is a one-to-many relationship and the database will keep a record indicating the personal tutor for each student. If the tutor’s telephone number changes then, because the relationship is one-to-many, all student files have to be updated with this new information. Failure to update will result in a data anomaly, which in this case is referred to as a modification anomaly. • Similarly we face problems with for example inserting new tutor, when this is required and deleting an old tutor. All the students files have to be updated accordingly, and failure to do this will result in an insertion anomaly or conversely a deletion anomaly.

34. Database FundamentalsIntroduction • Introduction to database systems • Database Management Systems (DBMS) • Type of Database • Database Design • Database Design Considerations • Component of Database Systems

35. Components of Database Systems

36. Components of Database Systems • A Database system will comprise of hardware and software components. • The hardware part includes the computer hardware but it is worth noting that besides the computer and peripheral hardware, computer networks hardware plays a significant role in modern database systems. • The software components include the operating system (O/S), the database engine (DBMS) and the additional tools including application and utility software. • The operating system refers to the software that makes all the resources available to users. The resources usually include hardware such as printers, hard disks, network interface cards, multimedia equipment, but this can also include software, such as applications that need to be shared between users. Common operating systems include UNIX, Linux, Microsoft DOS and Windows, Mac and MVS from IBM. The database engine sits above the O/S and uses it to communicate with resources. It has its own software tools to help the users in their efforts to communicate with the database and obtain the required information. Common DMS include Microsoft access, Oracle, IBMs DB2.