database management systems isys 464 fall 2004 david chao
Post on 20-Dec-2015
217 views
TRANSCRIPT
![Page 1: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/1.jpg)
Database Management Systems ISYS 464
Fall 2004
David Chao
![Page 2: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/2.jpg)
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
![Page 3: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/3.jpg)
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:
![Page 4: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/4.jpg)
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
![Page 5: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/5.jpg)
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)
![Page 6: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/6.jpg)
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
![Page 7: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/7.jpg)
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
![Page 8: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/8.jpg)
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.
![Page 9: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/9.jpg)
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
![Page 10: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/10.jpg)
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.
![Page 11: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/11.jpg)
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
![Page 12: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/12.jpg)
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.
![Page 13: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/13.jpg)
Advantages of DBMS
• Control of data redundancy
• Data consistency
• Support Ad Hoc queries
• Improved data integrity, security, backup and recovery, concurrency
![Page 14: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/14.jpg)
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
![Page 15: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/15.jpg)
Client Database Server
SQL queries
Results
Database Server: A high processing power computer with advanced DBMS.
Client: A PC that runs database applications.
SQL interface.
![Page 16: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/16.jpg)
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.
![Page 17: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/17.jpg)
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.
![Page 18: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/18.jpg)
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.
![Page 19: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/19.jpg)
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
![Page 20: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/20.jpg)
The three components in a database application
1. Presentation – user interface• Menus, forms, reports, etc
2. Processing logic • Business rules
3. Database
![Page 21: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/21.jpg)
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.
![Page 22: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/22.jpg)
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.
![Page 23: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/23.jpg)
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.
![Page 24: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/24.jpg)
The Web as a Database Application Platform
• Three-tier architecture– Browser, web server, database server,
processing logic
• Advantages:– Cross-platform support– Graphical user interface
![Page 25: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/25.jpg)
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.
![Page 26: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/26.jpg)
Major Databse Management Activities
• Creating database
• Updating database
• Querying database
![Page 27: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/27.jpg)
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
![Page 28: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/28.jpg)
Updating Database
• Insertions, deletions, modifications– Insertion only, no deletion
• Concurrent processing– Read/Write
• Transaction management
![Page 29: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/29.jpg)
Querying Database
• Relational algebra
• SQL
• QBE
![Page 30: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/30.jpg)
New Developments in Database
• Object-Oriented database
• Object-Relational database
• Decision support with data warehouse
• Web based database applications
• XML database
![Page 31: Database Management Systems ISYS 464 Fall 2004 David Chao](https://reader035.vdocuments.us/reader035/viewer/2022062320/56649d485503460f94a24334/html5/thumbnails/31.jpg)
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