Database Management Systems ISYS 464

1. Database Management Systems ISYS 464 Fall 2004 David Chao

2. Introduction to Databases • The most important component in an information system • A group of related files • Created to support business operations: • Day-to-day operations: TPS • Decision-makings: MIS, DSS • Strategic plans: ESS • Integrated to reduce data duplication and maintain data consistency

3. Traditional File-Based Systems • A collection of application programs that perform services for the end-users. Each program defines and manages its own data. • Example:

4. Comma-Delimited File • It stores each data item with a comma separating each item and places double quotes around string fields. • Student file with fields: SID, Sname, and GPA • “S5”, ”Peter”, 3.0 • “S1”, “Paul”, 2.5

5. Sequentially Accessing the Student File to Compute Average GPA Dim fileNumber, stCounter As Integer Dim SID, SNAME As String Dim gpa, sumGpa As Double fileNumber = FreeFile() FileOpen(fileNumber, "c:\stdata.txt", OpenMode.Input) Do While Not EOF(fileNumber) Input(fileNumber, SID) Input(fileNumber, SNAME) Input(fileNumber, gpa) sumGpa += gpa stCounter += 1 Loop MessageBox.Show(sumGpa / stCounter.ToString)

6. Limitations of the File-Based Approach • Duplication of data • Data inconsistency • Program-data dependence • When file structure changed, all programs that access the file must be modified to conform to the new file structure. • The definition of the data is embedded in the program. • Fixed queries • No facilities for asking unplanned, ad hoc queries

7. Database Approach • The database holds not only the data but also a description of the data. • System catalog (or data dictionary, or meta data) • Data about data • Program-data independence

8. Database management System (DBMS) • A software that enables users to define, create, maintain, and control access to the database. • Data Definition Language (DDL) • Data Manipulation Language (DML) • Control access: • Security, integrity, concurrent access, recovery, support for data communication, etc. • Utility services • File import/export, monitoring facilities, etc.

9. ANSI-SPARC Architecture • A three-level architecture to provide data independence • External level: The user’s view of the database. This level describes the part of the database that is relevant to each user. • Conceptual level: This level describes what data is stored in the database and the relationships among the data. • Internal level: The physical representation of the database on the computer. This level describes how the data is stored in the database. • File organizations, indexes

10. Data Independence • Data independence means that upper levels are unaffected by changes to lower levels. • Logical data independence • Changes to the conceptual level, such as the addition of new entities, attributes, or relationships, should be possible without having to change the existing external level design. • Physical data independence • Changes to the physical level, such as using a different file organization, indexes, should be possible without having to change the conceptual level design.

11. Three-Level Example • Employee Entity: • Conceptual design: • EmpID – 4 characters • EmpName – 30 characters • DateOfBirth – Date field 8 bytes • Salary – Number(7,2) • Sex – 1 character • Physical level: • Record size = 4 + 30 + 8 + 7 +1 = 50 bytes • Sequential file with index on EmpID field • External level: • EmpAgeView: • EmpID, EmpName, Age=Year(Today()) – Year(DateOfBirth) • EmpSalaryView: EmpID, EmpName, Salary

12. Benefits of Using Views • Views provide a level of security. • Views provide a mechanism to customize the appearance of the database. • Views provide a consistent, unchanging picture of the database, even if the underlying database is changed.

13. Advantages of DBMS • Control of data redundancy • Data consistency • Support Ad Hoc queries • Improved data integrity, security, backup and recovery, concurrency

14. Multi-User DBMS Architectures • Teleprocessing: • One computer with a single CPU and a number of dumb terminals. • Add burden on the central computer, which not only had to run the application programs and DBMS, but also had to carry out a significant amount of work on behalf of the terminals (such as formatting data for display) • File-Server: • Applications run on workstations that contain a full copy of the DBMS. File-server acts as a shared hard disk drive. The DBMS on each workstation sends requests to the file-server for data, but none of the processing is done by the server. • Generate a significant amount of network traffic. • Client-Server: • Computer network development • Balanced distributed processing

15. SQL queries Client Database Server Results Database Server: A high processing power computer with advanced DBMS. Client: A PC that runs database applications. SQL interface.

16. Client Functions • Manages the user interface. • Accepts and checks syntax of user input. • Implements business rules. • Generates database requests and transmits to server. • Passes response back to user.

17. Database Server Functions • Checks authorization. • Accepts and processes database requests from clients. • Ensures integrity constraints not violated. • Performs query/update processing and transmits response to client. • Provides concurrent database access, transaction management, and recovery control.

18. Advantages of Client-Server Architecture • Enables wider access to databases. • Increased performance: Different CPUs can be processing applications in parallel. • Hardware costs may be reduced: Only server requires higher storage and processing power to manage the database. • Network traffic is reduced: Only database requests and results are sent. • Increased database integrity.

19. Database Application • It is a program that interacts with the database at some point in its execution by issuing an appropriate request (typically an SQL statement) to the DBMS. • Database programming

20. The three components in a database application 1. Presentation – user interface • Menus, forms, reports, etc 2. Processing logic • Business rules 3. Database

21. Categories of Database Applications • One-Tier • Legacy online transaction processing • PC database application • Two-Tier client/server • Client-based presentation. • Processing logic is buried either inside the user interface on the client or within the database on the server, or both. • Three-Tier, N- tier • Processing logic is separated from the interface and database.

22. Two-tier • Simplicity • Provides a basic separation of tasks. The client is primarily responsible for the presentation of data to user, and the server is primarily responsible for supplying data services to the client. • Fat client: • More functions are placed on the client • Fat server: • More functions are placed on the server.

23. Three-Tier • 1. User interface, 2. Business logic and data processing layer, 3. Database server. • Advantage: • Implementing business rules as components. • Objects that provide services to other client applications. • Application maintenance is centralized. • Separation of the business logic from the database functions. • Fit naturally to the Internet environment.

24. The Web as a Database Application Platform • Three-tier architecture • Browser, web server, database server, processing logic • Advantages: • Cross-platform support • Graphical user interface

25. The Web as a Database Application Platform • Disadvantages • Reliability: Internet is unreliable and slow. • Security • Costs: hardware and software 20%, marketing 24%, content development 56%. • Potentially enormous peak load.

26. Major Databse Management Activities • Creating database • Updating database • Querying database

27. Creating Database • Analysis • System analysis • Data Flow Diagram, UML • Data modeling • ERD • Design • Maps the data model on to a target database model. • Implementation: Efficiently store and retrieve data • File organization and index

28. Updating Database • Insertions, deletions, modifications • Insertion only, no deletion • Concurrent processing • Read/Write • Transaction management

29. Querying Database • Relational algebra • SQL • QBE

30. New Developments in Database • Object-Oriented database • Object-Relational database • Decision support with data warehouse • Web based database applications • XML database

31. Course Overview • An introduction to the three-level database • Conceptual level: • Data modeling, ERD, Normalization • Physical level: • File organizations and index • External level • Relational algebra, SQL, QBE • Database management techniques