Introduction to Databases

1
Introduction to Databases
2
Case Example File based Processing

• Real Estate Agents office
• Property for sale or rent
• Potential Buyer/renter
• Staff/employees

3
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?

4
Reports

• Expected monthly turnover of renters?
• This months turnover compare to the last month?

5
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)

6
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

7
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)

8
File Based Processing
File handling routines
Data entry and reports
File definition
Sales
Sales application programs
File handling routines
Data entry and reports
File definition
Contracts
Contracts application programs
9
Limitations of File-Based Processing

• Separation and Isolation of data
• Duplication of data
• Data dependence
• Incompatibility of files
• Fixed queries/ proliferation of application
  programs

10
Database

• A shared collection of logically related data
  (and a description of this data) designed to meet
  the information needs of an organization.

11
Entity Relationship Diagram
Staff
IsAllocated
Oversees
Property_for_Rent
Branch
Has
Viewing
Takes
Requests
Owns
Owner
Renter
12
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

13
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

14

• 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

15
Database Processing
Data entry and reports
Sales application programs
Sales
DBMS
Property, Owner, Renter and Lease details File
Definitions
Database
Data entry and reports
Contracts application programs
Contracts
16
Components of DBMS environment
Data
Hardware
Software
Procedures
People
Bridge
Machine
Human
17
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

18
Dream Home hardware configuration
Northern Office
Western Office
Database server
Eastern Office
Southern Office
Database
19
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.

20
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
  used- Tree structures.

21

• 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

22
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.

23
Database designers

• Logical database designers
• Entities, attributes, relationships, constraints
  Business rules
• Physical database designers
• Decides how it is to be physically realized.

24
Application Programmers

• Retrieving, inserting, updating and deleting
• Programs
• Third Generation Language (3GL)
• Fourth Generation Language (4GL)
• End Users
• Inexperienced Users
• Sophisticated users

25
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 --gt errors
• Well designed database --gt correct information
• A complete methodology for database design.
• Entity- Relationship diagram
• Guidelines to help identify the entities,
  attributes and relationships

26
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

27
Disadvantages of Databases

• Complexity
• Size
• Cost of DBMS
• Additional hardware costs
• Cost of conversion
• Performance
• High impact of a failure