Database Management Systems Mysql

Introduction to DBMS Preview 50:22

Data:

• Data is a raw fact (collection of characters, numeric values, special characters etc)

• Whatever we input from the keyboard is known as data.

• Data will not provide any meaningful statements to the user.

• Ex:-  ec@mw2lo1e3

Information:

• Processing the data is called as information.

• Information always provides meaningful statements to the user.

• Ex:- welcome@123

Database:

• Database is a collection of information that can be written in predetermined manner and saved at a particular location is called database.

Database Management System (DBMS):

• DBMS is a tool which can be used to maintain & manage the data with in the database.

• DBMS is used for storing the information, accessing the information, sharing the information and providing security to the information.

Models of DBMS:

DBMS contains 6 models:

1. File management system (FMS)

2. Hierarchy management system (HMS)

3. Network database management system (NDBMS)

4. Relational database management system (RDBMS)

5. Object relational database management system (ORDBMS)

6. Object oriented relational database management system (OORDBMS)

File Management System:

• FMS is a first model of DBMS which was designed & developed in 1950's.

• In this model, the data will be stored into sequential manner or continuous stream of a character manner.

Drawbacks:

• Costly in maintenance

• Required more man power

• Accessing the data in time taken process or time consume method

• It is difficult to maintain large amount of data

• There is no security

• It is not possible to share the information to the multiple programmers

• Programmer will get delay response.

Hierarchy Management System:

• HMS is a second model of DBMS which was designed & developed by IBM company when they are developing a project called as IMS (Information Management System) in 1960's.

• In this model the data will be stored in the form of tree structure or level manner.

• In tree structure the user has to maintain the following levels those are

1. Root level will represent Database Name,

2. Parent level will represent Table's Name,

3. Child level will represent Column names of a table,

4. Leaf level will represent Additional columns.

• The main advantage of HMS model is to access the data from the location without taking much time.

Drawbacks:

• In this model only one programmer can interact with the data simultaneously.

• There is no security for database information

• It is not possible to share the database to multiple programmers or location.

Network Database Management System:

• NDBMS is the third model of DBMS which was designed & developed by IBM company when they are enhancing the features in IBM project in 1969

• In this model the data will be stored in the form of tree structure and located with in networks environment.

• The main advantage of NDBMS is to shared the required database to the multiple programmers at a time and communicate with same database.

Drawbacks:

• There is no proper security for centralized database system

• Database redundency will be increased (duplicate values)

• It occupies more memory

• Application performance will be reduced

• User will get delay responses

NOTE: The above 3 models are outdated.

Relational Database Management System:

• RDBMS is a 4th model of DBMS which was designed & developed by German scientist E.F Codd in 1970.

• E.F Codd defined 12 Codd Rules

1. Rule 0: Foundation rule

2. Rule 1: Information rule

3. Rule 2: Guaranteed access rule

4. Rule 3: Systematic treatment of null values

5. Rule 4: Active online catalog

6. Rule 5: Comprehensive data sub-language rule

7. Rule 6: View updating rule

8. Rule 7: High-level insert, update and delete

9. Rule 8: Physical data independence

10. Rule 9: Logical data independence

11. Rule 10: Integrity independence

12. Rule 11: Distribution independence

13. Rule 12: Non-subversion rule

• If any database satisfy at least 6 codd rules then the DBMS is called RDBMS product.

• Here relation can be defined as a commonness between the objects

• Relations again classified into 3 types

1. one to one relation

2. one to many relation

3. many to many relation

• A object can have a relationship with an other object is known as one to one relation.

  Ex:- student <-> sid

• A object can have a relationship with an other many object is known as one to many relation

  Ex:- student <-> C, C++, Java

• Many objects can have a relationship with an other many objects is known as many to many relation.

  Ex:- vendor 1, vendor 2, vendor 3 <-> product 1, product 2, product 3

• E.F Codd was designed the above relations based on mathematical concept is called as "Relational Algebra".

• The above 3 relations are called as Degree of Relationship.

Features of RDBMS:

• In RDBMS model the data should be stored in the format of table.

• Table is a collection of rows and columns.

• The vertical lines is called as column or field or attribute and the horizontal lines is called as row or record or tuple.

• The intersection between row & column is called CELL / ATOMIC

• RDBMS will provide pure security to the database information

• Accessing the data is very easy and user friendly

• RDBMS will provide sharing the database from one location to another location without loosing the data facility.

• It is easy to perform manipulations on tables data.

• RDBMS will improved application performance and avoid database redundancy problems.

Structured Query Language, MySQL Installation & Normalization Preview 41:36

E-R Diagrams:

• Before going to develop original database, first the DBA developer will analyze manual database information and later he converted into "pictorial" representation is known as Entity-Relationship diagrams.

• When we design E-R diagram, use the following symbols

1. rectangle:- will represent table name or entity name

2. oval:- column names of a table

3. diamond:- the relationship between tables

4. ------->:- connecting to the tables

Introduction to SQL:

• Structured Query Language is used to communicate with any database such as SQL Server, MySQL, Oracle, DB2 etc.

• SQL was designed & developed by the German scientist E.F.Codd in 1970 & released in 1972 with ANSI approved.

• SQL will pronounce as "SEQUAL" (Structure English Query Language)

• SQL will provide common user interface (CUI) facility i.e, this is the only one language which can communicate with any type of database's in realtime.

SQL contains the following sub languages:

1. Data Definition Language (DDL)

2. Data Manipulation Language (DML)

3. Data Query/Retrival Language (DQL/DRL)

4. Transaction Control Language (TCL)

5. Data Control Language (DCL)

Commands of sub languages are:

1. DDL:- create, alter, rename, drop, trancate

2. DML:- insert, update, delete

3. DQL:- select

4. TCL:- commit, rollback, savepoint

5. DCL:- grant, revoke

MySQL:

• MySQL is an open-source relational database management system.

• Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language.

• License: GPL (version 2)

• Developed by: Oracle Corporation

• Stable release: 8.0.12 / 27 July 2018; 2 months ago

• Original author: MySQL AB

• Initial release date: 23 May 1995

• Written in: C, C++

• MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses.

• MySQL works on many operating systems and with many languages including C, C++, JAVA, R, Python etc.

• MySQL works very quickly and works well even with large data sets.

• MySQL is very friendly to R and Python.

MySQL Workbench:

• MySQL Workbench is a visual database design tool that integrates SQL development, administration, database design, creation and maintenance into a single integrated development environment for the MySQL database system.

• License: Community ed.: GPL;

•         Standard ed.: Commercial software

• Developed by: Oracle Corporation

• Stable release: 8.0.12 / July 27, 2018

• Operating system: Windows, OS X and Linux

• Written in: C++, C#, Objective-C

MySQL Installation:

Follow the steps to install MySQL in Windows (refer PDF resource file)

Normalization:

What is normalization & normal forms (refer PDF resource file)

Data Definition Language Commands Preview 01:02:24

Data Manipulation Language Commands Preview 47:29

Sub Queries & Constraints Preview 16:07

Aggregate Functions, Clauses & Views Preview 07:21