Database Management System (DBMS)

Database Management System (DBMS)

• 1. Database Management System Database Management System
• 2. Slide19 DATABASE: A collection of data is referred to as database. e.g 1.record of students stored in files 2.information stored over internet
• 3. Slide2 DATABASE MANAGEMENT SYSTEM: It is basically a computer based record keeping system (program). e.g. MySQL Microsoft Access FileMaker FoxPro
• 4. Slide3 Data Redundancy Data Inconsistency Purpose of the database: It reduces DATA REDUNDANCY to a large extent. It controls DATA INCONSISTENCY. It facilitates the SHARING of data. It ensures DATA SECURITY.
• 5. Slide4 Various levels of database implementation (concept of abstraction) 1.INTERNAL LEVEL (PHYSICAL LEVEL) It describes how data are actually stored on the storage medium. It is closest to database programmer. Lowest level of database abstraction. 2. CONCEPTUAL LEVEL It describes what data are actually stored in database. It is closest to the database manager. It is an intermediate level of database abstraction. •EXTERNAL LEVEL (VIEW LEVEL) It describes the way in which the data are viewed by individual user. It is the level closest to the users.
• 6. Three levels of data abstraction Three levels of data abstraction View 1 View 2 View 3 Conceptual level Physical level (DBMS Programmer) Database handler User External level
• 7. Three levels of data abstraction Three levels of data abstraction internal Stored- acc. Account # Name Type Amount length=60 type=bytes(15) offset=0 type=bytes(20) offset=15 type=bytes(10) offset=35 type=bytes(15) offset=45 Conceptual AC_No AC_Name Type Amount numeric(15) character(20) character(10) numeric(15) View 1 AC_Name Amount View 2 AC_No AC_Nam e Type Amount Account holder Manager
• 8. DATA MODELS 1.Relational Data Model Table : Items Table : Suppliers Table : Shipments DATA MODELS
• 9. DATA MODELS 1. RELATIONAL DATA MODEL: In relational data model, the data is stored in the form of tables (i.e. rows ad columns). These tables are called relations. The user of the relational database system may insert new tuples, delete tuples, and modify tuples. DATA MODELS
• 10. 2. Network Data Model S1 Britannia 20 25 10 I2 Bread 9.00 I3 Biscui 6.00 ts I4 Snacks 16.00 2. Network Data Model
• 11. 2. Network Data Model 2. Network Data Model 2. NETWORK DATA MODEL: The network data model differs from the relational model. In this model data is represented by connection of records and relationships among data are represented by links. Records are organized as graphs.
• 12. 3. Hierarchical Data Model S1 Britannia 20 25 10 I3 I2 Bread 9.00 Biscuits 6.00 I4 Snacks 16.00 3. Hierarchical Data Model
• 13. 3. Hierarchical Data Model 3. Hierarchical Data Model 3. HIERARCHICAL DATA MODEL: Data is represented by collection of records and relationships among data by links, similar to the network model. Records are organized as trees rather than graphs. Represents relationship among its records through parent child relationships.
• 14. Relational Data Model The relational model was put forward by E.F. Codd of the IBM. It is considered as a very important concept in DBMS. Some basic terminologies Relation: A relation is a table i.e. data is arranged in rows and columns. Tuple : The rows of tables are generally referred to as tuples. Attributes: The columns of tables are generally referred to as attributes. Degree: The number of attributes in a relation determine the degree of the relation. Cardinality: The number of tuples in a relation is called the cardinality of the relation. Relational Data Model
• 15. Slide10 Database Keys are used to establish and identify relations between tables. Primary key: It is a set of one or more attributes that can uniquely identify the tuples within the relation. Candidate key : All attribute combinations inside a relation that can serve as a primary key are candidate keys. Alternate key: A candidate key that is not the primary key is called the alternate key. Foreign key : A foreign key is a non key attribute whose value is derived from the primary key of another table.
• 16. Views Views A view is a (virtual) table that does not really exist in its own right but is instead derived from one or more underlying base tables. It is an excellent way to give people access to some but not all information (data abstraction).
• 17. Slide13 Table : Employee Foreign key in this table
• 18. Slide12 Primary Key in this table