Docstoc

into_db

Document Sample
into_db Powered By Docstoc
					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       File handling routines
                and reports          File definition
 Sales             Sales application programs




               Data entry       File handling routines
               and reports          File definition
Contracts        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


                     Property_for    Has            Branch
Viewing      Takes
                        _Rent


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
               Data entry
               and reports
Sales       Sales application
                programs
                                     DBMS
                                Property, Owner,
                                Renter and Lease   Database
                                 details + File
               Data entry         Definitions
               and reports
Contracts   Contracts application
                 programs
     Components of DBMS environment


                      Data
Hardware   Software            Procedures   People

    Machine           Bridge         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 used-
      Tree 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

				
DOCUMENT INFO