Transcript Intoduction to Databases

Introduction to Databases
Case Example: File based Processing
•
•
•
•
Real Estate Agent’s office
Property for sale or rent
Potential Buyer/renter
Staff/employees
Queries
•
•
•
•
•
3-bed room apts for sales?
Flats within 3 miles of the city?
Average price of house?
Average rent for 2 BR flat?
Total annual salary for staff?
Reports
• Expected monthly turnover of renters?
• This month’s turnover compare to the last
month?
Files in Sales Dept
Selling and renting of properties
• Property_for_rent (Pno, Street, Area, City,
Pcode, Type, Rooms, Rent, Ono)
• Owner (Ono, Fname, Lname, Address,
Tel_no, Pref_Type, Max_rent)
Potential renters
Renter (Rno, Fname, Lname, Address,
Tel_No, Pref_Type, Max_rent)
Files in Contract Dept
• Lease(Lno, Pno, Rent, Payment, Deposit,
paid, Start, Finish, Duration)
• Property_for_rent (Pno, Street, Area, City,
Pcode, Rent)
• Renter (Rno, Fname, Lname, Address,
Tel_No)
• Data entry
• File maintenance
• Reports generation
Another example
Payroll Dept
Staff_salary(Staff No, First Name, Last Name,
Address, Date_of_Birth, Salary, National
Insurance Number, Branch Number)
Personnel Dept:
Staff(Staff No, First Name, Last Name,
Address, Telephone Number, Position,
Date_of_Birth, Salary, National Insurance
Number, Branch Number)
File Based Processing
Data entry
and reports
Sales
File definition
Sales application programs
Data entry
and reports
Contracts
File handling routines
File handling routines
File definition
Contracts application programs
Limitations of File-Based Processing
•
•
•
•
•
Separation and Isolation of data
Duplication of data
Data dependence
Incompatibility of files
Fixed queries/ proliferation of application
programs
Database
• “A shared collection of logically related
data (and a description of this data)
designed to meet the information needs of
an organization.”
Entity Relationship Diagram
Staff
IsAllocated
Oversees
Viewing
Takes
Property_for
_Rent
Has
Branch
Requests
Owns
Renter
Owner
Parts of ER diagram
• Six entities (the rectangles): Branch, Staff,
Property_for_Rent, Owner, Renter and
Viewing.
• Six relationships (the diamonds):
IsAllocated, Has, Overseas, Owns,
Requests, and Takes
DBMS
• “ A software system that enables users to
define, create and maintain the database and
provides controlled access to this database.”
• Data definition Language (DDL): data
types, structures and constraints on the data.
• Data Manipulation Language (DML):
insert, update, delete, and retrieve data
• Query Language: general enquiry facility
• Two types of DML:
– Procedural: manipulates the database record by
record.
– Non-Procedural: operates on a set of records.
• Most common is Structured Query Language (SQL)
• Controlled Access to the database:
–
–
–
–
–
security system
integrity system
Concurrency controlled, shared access
recovery control system
user accessible catalog
Database Processing
Sales
Contracts
Data entry
and reports
Sales application
programs
DBMS
Property, Owner,
Renter and Lease
details + File
Definitions
Data entry
and reports
Contracts application
programs
Database
Components of DBMS environment
Data
Hardware
Software
Machine
Procedures
Bridge
People
Human
Hardware
• Some DBMS run only on particular
hardware and operating systems
• Minimum amount of main memory and disk
space to run
• Client -Server architecture:
– Central computer runs the backend of the
DBMS
– Other computers run the frontend
Dream Home hardware configuration
Northern Office
Western Office
Database server
Eastern Office
Southern Office
Database
Software
• The DBMS software
• Application programs: C, COBOL, Fortran, Ada
etc.
• Using a fourth level language such as SQL
• Fourth generation tools:
–
–
–
–
–
–
rapid development of applications
non-procedural query languages
report generators
form generators
graphic generators
application generators
• Fourth generation tools can improve productivity.
Data
• The structure of the database is called the schema.
• Tables: Property_for_Rent, Owner, Renter and
Lease
• Attributes
• System Catalog contains:
–
–
–
–
Names, types, and sizes of data items
Integrity constraints on data
Names of authorized users who have access to the data
What indexes and storage structures are being usedTree structures.
Procedures:
•
•
•
•
•
•
Log on to the DBMS
Use particular DBMS facility or application program
Start and stop the DBMS
Make backup copies of the database
Handle hardware and software failures
Change the structure of the table, reorganize the data across multiple disks,
improve performance, or archive data to secondary storage.
PEOPLE:
• DB engineers, DB designers, DB developers/
programmers
Data and database administrators
• Data Administrator (DA)
– management of data (database planning, development,
maintenance of standards, policies, procedures and
conceptual and logical database design).
• Database Administrators:
– Physical database design and implementation, security
and integrity control, maintanence of the operational
system and ensuring satisfactory performance for the
applications and users.
Database designers
• Logical database designers:
– Entities, attributes, relationships, constraints
Business rules
• Physical database designers:
– Decides how it is to be physically realized.
Application Programmers
• Retrieving, inserting, updating and deleting
• Programs
– Third Generation Language (3GL)
– Fourth Generation Language (4GL)
• End Users:
– Inexperienced Users
– Sophisticated users
The Paradigm Shift
• The structure of the database is determined using
the database design.
• Paradigm shift: think of the data first and the
application later.
• Poorly designed database --> errors
• Well designed database --> correct information
• A complete methodology for database design.
– Entity- Relationship diagram
– Guidelines to help identify the entities, attributes and
relationships
Advantages of Databases
•
•
•
•
•
•
•
•
•
•
•
Control of data redundancy
Data consistency
More information from the same amount of data
Sharing the data
Improve data integrity and improve security
Enforce standards
Economies of scale
Balance the conflicting requirements
Improve data accessibility and responsiveness
Increase productivity
Improve maintenance through data interdependencies (pgm-data
independence)
• Increase concurrency
• Improve backend recovery services
Disadvantages of Databases
•
•
•
•
•
•
•
Complexity
Size
Cost of DBMS
Additional hardware costs
Cost of conversion
Performance
High impact of a failure