Database Systems(DBS) Or DATABASE MANAGEMENT SYSTEM

Database Systems

1. Database Systems (DBS) or Database Management Systems (DBMS) CPE 513

2. Course Outline Introduction to DBS or DBMS Entity-relation diagrams (ER) Relational model Relational Algebra and calculus as applied to the design of database Structured Query Language (SQL) Data normalization Storage and indexing

3. Definitions Data: Raw, unprocessed facts. EX: 30, John, Lagos Information: Processed data. Ex: The age of John is 30. Database: Collection of related data. Data that is stored in an organized fashion EX: Online banking system, library management system Meta-data: The database definition DBMS: Oracle, MySQL etc. SQL (Structured Query Language): used to manipulate the data in the DB.

4. Database Management System (DBMS) or Database System (DBS) Definition: Collection of programs that enables users to create and maintain the database Functionalities Define: Specifying the data type and constraints for the data to be stored. Construct: Process of storing data on some storage medium. Manipulate: Querying the database to retrieve specific data, updating database and generating reports Share: Allows multiple users and programs to access the database concurrently .

5. Database System Environment

6. Characteristics of DBMS File System Approach DBMS Approach Accounts dept Matric no Name Fees due Fees paid Exam dept Matric no Name Grade Course

7. Characteristics of DBMS Approach Self-describing nature of a database system Support of multiple views of the data Sharing of data and multiuser transaction processing

8. Self-describing nature of the database system Database system: Database +Meta-data (DB definition) Stored in :DBMS catalogue Used by DBMS software and database users

9. A database that stores student and course information STUDENT Name Matric_No Level Department Lanre EES/15/16/0001 400 Computer Science Tope EES/15/16/0002 500 Computer Engineering COURSE CourseName CourseCode Department Database Systems CPE 513 Computer Engineering Discrete Mathematics MTH 403 Maths Computer Communication Networks CPE 505 Computer Engineering GRADE_REPORT Matric_no CourseCode Grade EES/15/16/0001 CPE 513 A EES/15/16/0001 CPE 505 B EES/15/16/0002 CPE 505 B

10. An example of database catalogue Relations or tables Columns Relation_Name No_of_columns STUDENT 4 COURSE 3 GRADE_REPORT 3 Column_Name Data_type Belongs-To-Relation Name Character (30) STUDENT MATRIC_NO String(14) STUDENT , GRADE_REPORT LEVEL Integer (3) STUDENT DEPARTMENT Character (20) STUDENT, COURSE CourseName Character (20) COURSE CourseCode String (6) COURSE, GRADE_REPORT Grade Character (1) GRADE_REPORT

11. Support of Multiple Views of the Data A database has many users, each of whom may require a different view of the database

12. Sharing of Data and Multiuser Transaction Processing A multiuser DBMS allows multiple users to access the database at the same time. DBMS must include concurrency control . OLTP (Online Transaction Processing), a major part of database application DBMS must enforce several transaction properties: i ) Isolation ii) Atomicity

13. Database Users Actors on the Scene: whose job involves using large databases everyday Database administrators Database designers End users System analysts and application programmers (software engineers) Workers Behind the Scene: maintain the database system environment System designers and implementers Operators and maintenance personnel

14. Actors on the Scene Database Administrators: In database environment, primary resource (database), secondary resource (DBMS and related software) Database administrator (DBA) responsibilities : Administering primary/secondary resources Authorizing access to the database Co-ordinating and monitoring the use of database Acquiring hardware and software resources as needed. Troubleshooting if problem arises See to any security concern

15. Actors on the Scene Database Designers Responsible for: Identifying the data to be stored in the database Choosing appropriate structures to represent and store data Communicating with database users – understand their requirements – designs database Develop views for each group of users

16. Actors on the Scene End users: End users: people whose jobs require access to the database – for querying, updating, generating reports.

17. Actors on the Scene System Analysts and Application Programmers (Software Engineers): System Analysts – determine the requirements of the end users. They check whether all the requirements of the end users are satisfied. Application Programmers – also known as back-end developers. They write the codes for the application program. Responsible for developing the application programs.

18. Workers Behind the Scene System designers and implementers: Design and implement DBMS modules and interfaces as a software package Operators and maintenance personnel: Responsible for actual running and maintenance of hardware and software of the DBMS environment.

19. Advantages and Disadvantage of DBMS Controlling redundancy Restricting unauthorized access Providing backup and recovery Providing multiple user interfaces Representing complex relationships among data Enforcing integrity constraints

20. Controlling redundancy File system approach This will lead to wastage of storage space, inconsistency . In database approach, views of different users are integrated All the data are stored in only one repository Accounts dept Matric no Name Fees due Fees paid Exam dept Matric no Name Grade Course

21. Restricting unauthorized access When multiple users share a large database, the type of access operation must be controlled. DBMS must provide security and authorization subsystem. DBA – creates accounts and specifies account restrictions.

22. Providing Backup and Recovery The backup and recovery subsystem of DBMS – responsible for recovery – in case of hardware or software failures. Ex: If the computer crashes during a complex transaction, the recovery subsystem – responsible for ensuring that the transaction resumes from where it was interrupted or at least restore to the state it was before transaction started executing

23. Providing Multiple User Interfaces Multiple users – different levels of technical knowledge – so DBMS should provide a variety of user interfaces. Ex : Query languages, programming language interfaces – application programmers , forms, menu-driven interfaces. Form-style interfaces and menu-driven interfaces – Graphical user interfaces (GUI)

24. DBMS Interfaces Menu-based Interfaces: These interfaces present the users with a list of options (menus) Most popular – Pull-down menus

25. DBMS Interfaces Forms-Based Interfaces: Displays a form to each user.

26. Representing Complex Relationships among Data A database may have variety of data – interrelated in many ways. DBMS must be capable of: Representing complex relationships among data Retrieve and update related data easily and efficiently.

27. Enforcing Integrity Constraints Simplest type of integrity constraint – specifying data type for each data item Another type of constraints – null values . Responsibility of database designers – identifying integrity constraints during database design.

28. Disadvantage of DBMS Overhead costs of using DBMS: High initial investment Assignment 1 List and explain other disadvantages of DBMS .