1 / 91

Database Management Systems (DBMS)

Database Management Systems (DBMS). C ertificate C ourse in I nformation T echnology. Anil Luvishewa anil@cc.ruh.ac.lk. Learning Outcomes. Understand the need for database management as opposed to the traditional stand - alone applications approach.

muireann-chesney
Download Presentation

Database Management Systems (DBMS)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Management Systems(DBMS) Certificate Course in Information Technology Anil Luvishewa anil@cc.ruh.ac.lk

  2. Learning Outcomes • Understand the need for database management as opposed to the traditional stand - alone applications approach. • Learn the major factors involved in proper database design including the use of entity-relationship diagrams and normalization. • Learn to use SQL for data definition and data manipulation.

  3. Topics • Introduction to DBMS • The Database Architecture • Database Design Process • Conceptual Data modeling – (Entity-Relationship approach) • The logical Database Model – (The Relational Model) • Logical Database Design – (Normalization) • Data Manipulation using SQL

  4. Introduction to Database Concepts • Why use a Database? • Why Database Technology? ?

  5. ATM users Bank Banking System Banking System ATM users Branch Branch Mobile

  6. Data and Information • Data • Raw facts • Less meaningful • Not processed Eg: Date of birth • Information • Processed data • Meaningful • Organized • Eg: • Processed DOB: Age

  7. File Processing System • Individual applications maintain own private data files. • Leads to data duplicate (redundancy). • Inconsistency • Management is very difficult • Data is in different formats. • Inflexible

  8. Database Student database File 1 File 2 File 3 What is a database? A database is an organized collection of data that are related in a meaningful way and stored in a common pool which can be accessed by one or many persons for many purposes. A database is a group of related files • Eg: Course file • Student name file • Marks file

  9. What is a Database? Data collection may be . . . Electronically Collected Manually Collected

  10. Key Features of a database The data is • input (stored) only once • Organized • accessible effectively and efficiently

  11. Database Building Blocks • File/Table A collection of related records. • Record A record is a group of related fields. • Field Field represents an attribute, or a characteristic or a piece of information

  12. Table a Record a Field Database Building Blocks..

  13. Database Management System (DBMS) • The DBMS is a software package which allows a user to define, create, and maintain the database and provides controlled access to this database. • The interface between users and data is provided by a database management system. Eg: Oracle, MS SQL-Server, MS Access MySQL, PostgreSQL

  14. Admin DBMS Data-bases Results Modify/ Retrieval (Command) Users Overview of a DBMS

  15. Characteristics of Modern DBMS • Query processing • Transaction management • Concurrency control • Database recovery • Database security and authorization • Distributed databases

  16. Advantages of using DBMS • Data duplication and storage space wasting can be avoided • Inconsistency can be avoided • Data can be shared • Unauthorized access can be restricted • Recovery from failures

  17. Disadvantages of using DBMS • cost of extra hardware • cost of entering data • cost of training people to use DBMS • cost of maintaining DBMS • complex

  18. Components of a Database System • Data The data in the database integrated and shared. • Hardware Physically stores data, it can be secondary storage on which the database physically resides together with associated I/O devices. • Software Between the physical database & the users is a layer of software which is known as the DBMS. • Procedures Refer to the instructions & rules that governs the design & use of the database. • Users Application Programmers. End-users

  19. Controlling & Maintaining a Database Database Administrator (DBA) Database

  20. Duties of the Database Administrator • Installing and upgrades of database software • Performance tuning • Backup and recovery strategies • Start the database & shutdown the database • Monitoring the database • Give and drop database access to the users • Consultation with developers

  21. Database Architecture Data-bases

  22. The Need of a Database Architecture • Users need to access same data but in different customized views. • User views can be changed without affecting data or other user views. • Users are not deal directly with physical data storage • Database administrator should be able to change the database storage without affecting the user’s views.

  23. Database Architecture An architecture for a database system is useful for explaining the structure of a specific database system The database architecture is a three level architecture which reflects the underlying database system

  24. Schemas and Instances Database schema • The description of a database called database schema • The logical structure of the database • Schema is defined during the database designing and is not frequently changed Instance • Data (the actual content) in the database at a particular moment in time is called database state. • It is also called the instances or current set of occurrences

  25. External View 1 External View n Conceptual Schema Internal Schema DB DB DB Three Schema Architecture End users External Level External / Conceptual mapping Conceptual Level Conceptual / Internal mapping Internal Level

  26. Three Schema Architecture.. Internal (Physical) Schema • Defines the physical view of data (as seen by a DBMS) • Describe the physical storage of the database • Describe how the data is stored in the database • Concern with storage space allocation, record description, data compression and encryption techniques.

  27. Internal (Physical) Level • Struct Employee { Char Emp_no[5], Char Name[25], Date Date_joined, Boolean Pay_Tax, Int OT_Hours, Char Des_Code[3] } ;

  28. Three Schema Architecture.. Conceptual Schema • Defines the logical view of data (as seen by programs) • Describe the structure of the whole database for a community of users. • Hides the details of physical storage structure. • Describe entities, data types, relationships, user operations and constraints.

  29. Conceptual Level Employee Table Base Table

  30. Three Schema Architecture.. External Schema • Defines the external view of data (as seen by a user) • The users view of the database • Describe the part of the database that a particular user group is interested. • Hides the other details of the database.

  31. External Level Base Table (Conceptual View) The data what user wants (Only selected parts of selected records from base table) View Table (External View)

  32. Mapping External/Conceptual mapping • Define the correspondence between a particular external view and conceptual view. • This specified how a particular external schema is derived from conceptual schema Conceptual/Internal mapping • Define the correspondence between the conceptual view and stored database. • This specifies how the conceptual schema is represented at the internal level. • If the structure of the stored database is changed, the conceptual internal mapping must also be changed accordingly.

  33. Sno FName LName Staff_No LName Bno Staff_No FName LName DOB Batch_no Mapping.. External view 1 External view 2 External Level Conceptual Level Struct STAFF { int Staff_No; int Branch_No; char FName[15]; char LName[15]; date date_of_birth}; Internal (Physical) Level

  34. Data Independence The major objective for the three-level architecture is to provide data independence Logical Data Independence Capacity to change the conceptual schema without having to change external schemas or application program. Physical Data Independence The ability to modify the physical schema without changing the conceptual (or external ) schema

  35. Database Design

  36. Database Design The database design process can be broken down into four phases. • Requirements collection and analysis phase • Conceptual Design • Logical Design • Physical Design

  37. Requirements collection and analysis phase Prospective database users are interviewed to understand and document their data requirements

  38. Conceptual Design • This is a high level description of the structure of a database Eg: E-R Diagram • Concise description of the data requirements of the users and includes detailed description of the data, relationship and constraints.

  39. Logical Design This is the process of mapping the database structure developed in the previous phase to a particular database model. Eg: Map E-R model to relational

  40. Physical Design This is the process of defining structure that enables the database to be queried in an efficient manner Eg: index and hash file design

  41. Phases of Database Design Problem Requirements collection & analysis Database Requirements Conceptual Design Conceptual Data Model Logical Design Logical Data Model Physical Design Physical Data Model Database Implementation

  42. Entity – Relationship Modeling ER model is high level conceptual data model

  43. The Concepts of the ER Model • Entity • An entity is a thing in the real world with an independent existence . • As entity may be an object with a physical existence . e.g. : a person , a student , a house, etc. • An entity is any object that is relevant to the organization. • Tangible object • Intangible objects • Events

  44. Conceptual Design Entities Department Employee Project Dependent

  45. The Concepts of the ER Model • Attribute • Each entity has particular properties or features (characteristics ) called attributes . • e.g. student entity(student no, name, address, dob)

  46. Type of attributes • Simple (atomic) attribute Attributes that are not divisible are called simple or atomic attribute. Eg: Emp_No • Composite attribute Can be divided in a smaller part which represent more basic attributes with independent meaning of their own. Eg: Name (First Name, Mid Name, Last Name)

  47. Type of attributes • Multi-valued attribute In some case an attribute can have a set of values for the same entity such attributes are called multi-valued attributes. Eg. Degree of a person , Qualification • Derived attribute • An attribute whose value can be calculated from related attribute values Eg: Age (Using Date of Birth) . Years Employed (Using Employed Date) .

  48. Relationship • Relationship A meaningful association between entities. Eg: Student follows courses Employee works for department

  49. Manages follows Student Courses Relationship • Degree of a Relationship The degree of the relationship type is no. of participating entity types . • Unary Relationship • Relationship between the instances of a single entity type. E.g.: person is married to a person (1:1) Employee manages Employees (1:M) • Binary Relationship • Relationship between the instances of two entity types. Degree = 2; Employee

  50. supplies Supplier Project Part Relationship • Degree of a Relationship • Ternary Relationship A simultaneous relationship among the instances of three entity type. Degree = 3 ,Ternary Relationship

More Related