Data Base Management Systems

1. Data Base Management Systems Mrs. Anita H.B.

2. What is Data ? • Data is a collection of known facts, figures or statistics that can be recorded and that is meaningful. • Data can be text , numbers, audio, video or any combination of these. • Data which is in raw form may not be useful for decision making • What is Database ? • It is a collection of related Data, having valid and implicit meaning and designed to meet the information needs of an organization. • Eg: 1. Personnel Telephone directory (Name, Address…..) • 2. Library Database • (Acc.No., Author1, Author2, pub , No-Pages, Year, Article No….)

3. What is a DBMS? • A Database Management System (DBMS)is a software package designed to store and manage databases. • A DBMS is a collection of programs that enables users to create and maintain a database. • It provides an environment that is both convenient and efficient to use in storing and retrieving database information. • Database System: The database and the DBMS software together is a Database System. Sometimes, the applications are also included.

4. DBMS Catalog: Catalog contains information such as structure of each file, the type and storage format of each data item and various constraints on the data. Meta Data: Data stored on the DBMS catalog is called meta-data. It describes the structure of the primary database. Database system environment: 1. Application programs or queries written by users or programmers. 2. Software to process these queries or application programs 3. Software to access the stored data 4. DBMS catalog contains stored database definition or metadata 5. Physical stored database

6. Functions of DBMS • Defining a database • Constructing the database • Manipulating the database • Sharing the database among various users • Protecting the database • Maintenance of a database

7. Traditional Database Applications • Banking: all transactions • Airlines: reservations, schedules • Universities: registration, grades • Sales: customers, products, purchases • Online retailers: order tracking, customized recommendations • Manufacturing: production, inventory, orders, supply chain • Human resources: employee records, salaries, tax deductions • Databases touch all aspects of our lives

8. Advance Database Applications • Multimedia Database - Stores Pictures, Sound and Video clips • Geographic Information Database - Stores and analyze maps, Weather data, Satellite images. • Data Warehouse & online Analytical Processing(OLAP) - Companies to extract and analyze useful information from very large database for decision making.

9. Real Time and Active database Technology • Used to control industrial and manufacturing Processes. • Mobile Database • Available on users portable computers. • Web Database • Databases are integrated with web to support business • operations like e-commerce, supply chain management • Spatial Databases • Keeps track of objects in a multidimensional space • eg: Data in CAD/CAM

10. Database example (college Database) Stu Marks tb Regno Sem Sub1 Sub2 Subn Faculty tb Name Address Phno Email Dept Qualification Student tb Name Address Phno Email Basic deg Course joined Fee paid Hostel no Alumni tb Regno Name Address Phno Email Qualification Present Comp Hostel tb Hostel No Hostel Name Year Fees Course tb Course no Course Name years Fees stru

11. Traditional Oriented approach: • The system has number of application programs that can manipulate with files. • Ex: One user, the grade report, may keep a file on students and grades. A second user, accounting office, may keep track of students fees and their payments. Both are interested in data of students but each user maintains separate files. • Application programs are written by programmers as per the need of organization. • As need of organization arises, new permanent files created. • Disadvantages: • Data redundancy and inconsistency • Difficulty in data accessing • Integrity problems • Concurrent access anomalies • Security problems

12. Main Characteristics of the Database • Self-describing nature of a database system: A DBMS catalog stores the description of the database. The description is called meta-data. This allows the DBMS software to work with different databases. • Insulation between programs and data: The data & program is not embedded. Therefore any changes in the structure of file is not required to change the programs but we need to change the catalog. This is called program-data independence. • Allows changing data storage structures and operations without having to change the DBMS access programs.

13. In DBMS environment, if a new field is to be added to the student file, only the student file definition in the catalog has to be changed and no change is needed in the programs that access the database. This is known as Program Data Independence. Internal storage format for a typical student record • In recent developments even the operations that are to be carried out on the data can be stored along with the definition itself. • An operation contains 2 parts . • An interface which includes name of the operation • The data types of its arguments. • User programs can use these operations by invoking the operations by invoking the operations from definition and providing the operands.

14. Main Characteristics of the Database • Data Abstraction: A data model is used to hide storage details and present the users with a conceptual view of the database. • Support of multiple views of the data: Each user may see a different view of the database, which describes only the data of interest to that user. Different user required different part of DB. • Sharing of data and multiuser transaction processing : Allowing a set of concurrent users to retrieve and to update the database. Concurrency control within the DBMS guarantees that each transaction is correctly executed or completely aborted.

15. Example for multiple views Sql> create table doctortab( Dno integer not null, Dname char(20), Dadd char(30), Specialization char(20)); Sql> create table patient( Pno integer not null, Pname char(20), Padd char(30), City char(10), Dno integer, disease char(20), Primary key (Pno), Foreign key References doctortab(Dno)); Sql> create viewBangpatient As select * from patient where city = ‘Bangalore’;

16. Sql> create table Student( RegNo integer not null, sname char(20), sadd char(30), City char(10), Country char(10), Course char(10), Qualification char(10), Feespaid boolean, Scholarship boolean,; Create view to get list according to course , country, qualification

17. Advantages of Using the Database Approach • Controlling redundancy in data storage and in development and maintenance efforts. • Sharing of data among multiple users. • Restricting unauthorized access to data. • Providing Storage Structures for efficient Query Processing • Backup & Recovery • Multiple user interfaces

18. Student table

19. Production Master Quality Master Item Master Customer Master

20. Disadvantages • High investment • Training required • Multiple user access is not required • Overhead for proving security, concurrency control, recovery and integrity functions.

21. Database Users Many persons are involved in the design, use and maintenance of a large database. Actors on the scene: The Persons who actually use and control the content called “Actors on the Scene” means involved in design, use, maintenance of large database and day-to-day use of large database. • Data Base Administrator • Data Base Designer • End user • Software Engineer (System Analyst / Application Programmers) • Workers behind scene: Persons who maintain the database environment means enable the database to be developed and the DBMS software to be designed and implemented called “Workers Behind the Scene”. • DBMS system designers and Implementers • Tool Developers • Operators

22. Database administrators responsibility: • Authorizing access to the database • Coordinating and monitoring DB use. • He is accountable for problems like security, poor system response time. • Controlling and coordinating the DBMS system • Administers 3 levels of database • (Internal, Conceptual and External levels) • Specifies external view for various users Applications • Protection for unauthorized access. • Granting permission to the users (different Levels) • Responsible for recovery from failures

23. Database Designers: • Identifying the data to be stored in DB • Choosing appropriate structures, the constraints, and functions or transactions against the database. • Interact with the end-users and understand their requirements and then design DB. • Final database design should meet requirement of all user groups. • End-users: People who access DB for retrieving data, Updating of data, Generating reports. • Different categories • Casual end users • Naïve or Parametric end users • Sophisticated end user • Stand alone end users

24. Categories of End-users • Casual end users : • access database occasionally when needed. • Each time require different information • They may use query language • Naïve or Parametric : • Constantly make use of DB • Large section of the end-user population. • They use previously well-defined functions in the form of “canned transactions” against the database. • Ex: bank-tellers or reservation cheques.

25. Categories of End-users Sophisticated End User: • Includes business analysts, scientists, engineers, others thoroughly familiar with the system capabilities. • Implement their applications to meet their complex requirements. • Many use tools in the form of software packages that work closely with the stored database. Stand-alone End User • maintains personal databases using ready-to-use packaged applications. Ex: Tax package used to create that creates his or her own internal database for tax purpose.

26. System Analyst and Application Programmers: • System Analyst determine the requirement of the end users. • Application Programmers implement these specifications as programs, test them and debug, document and maintain them. • Both Analyst and Programmers are aware of all the facilities of DBMS. Workers behind Screen • DBMS system designers and Implementers • Design and implement the DBMS software package/modules and interfaces as software package. • It includes module for catalog, query language, recovery, security etc.

27. Tool Developers: • These persons implement the software packages that facilitate the database design and help to improve the performance. { like Spell check/word art in word} • Tools are optional packages that are often purchased separately. • This includes tools for database design, performance monitoring, test data generation etc. • Operators/Maintenance Personnel: • These are the system administration personnel who are responsible for the actual running and maintenance of the hardware and software environment for the database system.

28. Extending Database Capabilities • Scientific Applications • Image Storage and Management • Audio and Video data management • Data Mining • Time Series and Historical Data Management

29. When not to use a DBMS • Main inhibitors (costs) of using a DBMS: • High initial investment and possible need for additional hardware. • Overhead for providing generality, security, concurrency control, recovery, and integrity functions. • When a DBMS may be unnecessary: • If the database and applications are simple, well defined, and not expected to change. • If there are stringent real-time requirements that may not be met because of DBMS overhead. • If access to data by multiple users is not required.

30. When not to use a DBMS • When no DBMS may suffice: • If the database system is not able to handle the complexity of data because of modeling limitations • If the database users need special operations not supported by the DBMS.

31. Conclusion • What is data, Database, DBMS ? • What are advantages and disadvantages of dbms? • Define metadata. • List out the functions of dbms. • Describe main characteristics of dbms. • Mention few advanced database applications Define the terms: - metadata - Database system - Program data independence - Program operation independence • Discuss about various people involved with DBMS.

33. DBMS tasks: • Managing large quantity of structured data • Efficient retrieval and modification: query processing and optimization • Sharing data: multiple users use and manipulate data • Controlling the access to data: maintaining the data integrity • An example of a database (relational): • Relations (tables) • Attributes (columns) • Tuples (rows).

34. Additional Implications of Using the Database Approach • Potential for enforcing standards: this is very crucial for the success of database applications in large organizations Standards refer to data item names, display formats, screens, report structures, meta-data (description of data) etc. • Reduced application development time: incremental time to add each new application is reduced.

35. Additional Implications of Using the Database Approach • Flexibility to change data structures: database structure may evolve as new requirements are defined. • Availability of up-to-date information – very important for on-line transaction systems such as airline, hotel, car reservations. • Economies of scale: by consolidating data and applications across departments wasteful overlap of resources and personnel can be avoided.