Database Management System (DBMS) PDF

Database Management System (DBMS) TSLee 1  Objectives Differentiate among a character, field, record, and data file Differentiate between file processing systems and the database approach Compare the advantages and disadvantages of both...

Database Management System (DBMS) TSLee 1  Objectives Differentiate among a character, field, record, and data file Differentiate between file processing systems and the database approach Compare the advantages and disadvantages of both file and database system approach in data storage Discuss functions common to most database management systems TSLee 2 TSLee 3  Type of File Processing & DBMS tools File Processing tools DBMS tools Ms. Word Ms. Access Ms. Excel Oracle DBMS Notepad DB2 (IBM) Ms. Power Point phpMyAdmin (open source) TSLee 4 File Based System TSLee 5 Database System TSLee 6  File Processing Systems and Databases File approach Database approach Each department has Programs and users its own set of files share data Redundant data Reduced data Isolated data redundancy Improved data integrity Easier access Reduced development time TSLee 7  Advantages of File & Database File System DBMS Cheap Data consistency Easy to operate More information generated from same data Less expert/training needed Improve security Less system update required Easy to recover TSLee 8  Disadvantages of File & Database File System DBMS Slow information retrieval Expensive More storage space Expert needed Less secure Complex Redundant data Frequent update TSLee 9  Application Commands to: DB store Software DB DB DB retrieve update System TSLee 10  Databases, Data, and Information A database management system (DBMS), allows users to: Create a computerized Add, modify, and database delete data Create forms and Sort and retrieve data reports from the data TSLee 11  Types of DBMS Relational Database Management Systems. Hierarchical Database Management Systems. Network Database Management Systems. Object-oriented Database Management Systems. TSLee 12  Types of DBMS in the market Source from: Liu (Dec 22, 2020). Ranking of the most popular database management systems worldwide, as of December 2020. statista.com. https://www.statista.com/statistics/809750/worldwide-popularity-ranking-database- management-systems/ TSLee 13  Database System Structure Data is organized by levels TSLee 14  Database design How databases are formed? – Entity Relationship Diagram – Normalization TSLee 15  DB Modelling with ERD Entity Relationship Diagram (ERD) to demonstrate the overall structure of a database. to shows the relationships of entity sets stored in a database. Entity in this context is an object, a component of data. Entity has attributes that define its properties. (e.g. LMS - students – id, name, dob etc) an ER diagram illustrates the logical structure of databases by defining the entities, their attributes, and showing the relationships between them, TSLee 16 ERD TSLee 17  DB table design with Normalization Normalization is a database design technique which organizes tables in a manner that reduces redundancy and dependency of data. It divides larger tables into smaller tables and uses a special formatted link structure to link them. TSLee 18 Normalization Before normalization After normalization 19 TSLee What a Relational Database Management System is? Relational DBMS = RDBMS A “big program” written by someone else that accesses and updates those files for you The data set of the database is represented by tables or relations TSLee 20  Tables in RDBMS Primary Key Primary Key Primary Key Foreign Key Primary Key Foreign Key Foreign Key TSLee 21  Where RDBMS is use? Backend for applications Backend for large Websites Backend for Web services 22 TSLee  DBMS Language Application Software SQL language DB DB DB DB System Query language: DDL – Data Definition Language DQL – Data Query Language DML – Data Manipulation Language DCL – Data Control Language TSLee 23  What Programmers do with DBMS DDL query to create tables: CREATE TABLE Students ( Name CHAR(30) SSN CHAR(9) PRIMARY KEY NOT NULL, Category CHAR(20) )... DML query to populate tables: INSERT INTO Students VALUES(‘Charles’, ‘123456789’, ‘undergraduate’).... TSLee 24  DQL Queries Find all courses that “Mary” takes SELECT C.name FROM Students S, Takes T, Courses C WHERE S.name=“Mary” and S.ssn = T.ssn and T.cid = C.cid What happens behind the scene ? – Query processor figures out how to answer the query efficiently. – Data are retrieved with mathematic operations (union, difference, projection, join…) TSLee 25  Summary Identify the character, field, record, and data file Compared the structure of the file processing systems and the database approach Discussed functions common to most database management systems Compared the advantages and disadvantages of both file and database system approach in data storage TSLee 26