Page 3 :
DBMS, VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &, SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR, , A DBMS refers to Database Management System, It is a software that is responsible for storing,, manipulating, maintaining and utilizing, database., A database along with the a DBMS is referred to, as a database system., There are various DBMS software available in, the market like : Oracle, MS SQL Server, MySQL, Sybase,, PostgreSQL, SQLite,
Page 5 :
RELATIONAL DATABASE MODEL, VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &, SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR, , In relational database model data is organized, into table (i.e. rows and columns)., These tables are also known as relations., A row in a table represent relationship among a, set of values., A column represent the field/attributes related to, relation under which information will be stored., For example if we want to store details of, students then : Roll, Name, Class, Section, etc., will be the column/attributes and the collection of, all the column information will become a, Row/Record,
Page 7 :
COMPONENT OF A TABLE, Byte, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &, SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR, , : group of 8 bits and is used to store a, character., Data Item : smallest unit of named data. It, represent one type of information, and often referred to as a field or, column information, Record, : collection of data items which, represent a complete unit of, information, Table, : collection of all Rows and Columns.,
Page 10 :
COMMON RDBMS, MySQL, , , , , SQLite, , , , , Runs on virtually all platforms including Linux, Unix and, Windows. Popular for web based application and online, publishing. It is a part of LAMP (Linux, Apache, MySQL,, PHP) stack, Relational DBMS but it is not client-server database, engine rather, it is embedded into end program. Arguably, the most widely deployed database engine as it is used by, several browsers, OS and embedded systems(Mobiles)., , PostgreSQL, , , General purpose object-relational DBMS. It is the most, advanced open source database system. It is free and open, source i.e. source code is available under PostgreSQL, license, a liberal open source license., , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &, SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR, ,
Page 14 :
CONCEPT OF KEYS, ENAME, ANKITA, SUJEET, VIJAYA, NITIN, VIKRAM, , GENDER, F, M, F, M, M, , DEPTNO SALARY, 10, 20000, 20, 24000, 10, 28000, 30, 18000, 30, 22000, , COMM, 1200, , 2000, 3000, 1700, , In relation each record must be unique i.e. no two, identical records are allowed in the Database. A, key attribute identifies the record and must have, unique values. There are various types of Keys:, Primary Key, Candidate Key, Alternate Key, and Foreign Key., , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &, SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR, , EMPNO, 1, 2, 3, 4, 5
Page 15 :
KEYS, Primary Key, A set of one or more attribute that can identify a record, uniquely in the relation is called Primary Key., There can be only 1 primary key in a table, Allows only distinct (no duplicate) values and also forces, mandatory entry (NOT NULL) i.e. we cannot left it blank., , , , , Candidate Key, In a table there can be more than one attribute which, contains unique values. These columns are known as, candidate key as they are the candidate for primary key., Among these database analyst select one as a primary key, based on requirement like must contain unique value,, compulsory entry and where maximum searching is done, etc., , , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &, SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR, ,
Page 16 :
KEYS, , , Alternate Key, In case of multiple candidate keys, one of them will, be selected as Primary Key and rest of the column, will serve as Alternate Key, A Candidate Key which is not a primary key is an, Alternate Key., , , Foreign key, Used to create relationship between two tables., It is a non-key attribute whose value is derived from, the Primary key of another table., Foreign key column will for the value in Primary key, of another table, if present then entry will be allowed, otherwise data will be rejected., Primary Key column table from where values will be, derived is known as Primary Table or Master Table, or Parent Table and Foreign key column table will be, Foreign Table or Detail Table or Child table, , , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &, SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR, ,
Page 18 :
REFERENTIAL INTEGRITY, , The master table’s column is a Primary Key or has a, unique index, The related fields have the same data type, Both tables must belong to same database., , , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &, SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR, , Used to ensure relationship between records in, related tables are valid and user don’t, accidentally delete or change the related data., Referential integrity can be applied when:,
Page 19 :
REFERENTIAL INTEGRITY, When referential integrity is enforced using, Foreign Key you must observe the following, rules:, You cannot enter a value in Child Table which is not, available in Master Table’s Primary key column., However you can enter NULL values in foreign key, You cannot delete a record from Master Table if, matching record exists in related table, You cannot modify or change the Primary Key value, in Master table if its matching record is present in, related table., , , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &, SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR, ,
Page 20 :
BRIEF HISTORY OF MYSQL, , , , , , , , , , , , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &, SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR, , , , MySQL is freely available open source RDBMS, Can be downloaded from www.mysql.org, In MySQL information is stored in Tables., Provides features that support secure environment, for storing, maintaining and accessing data., It is fast, reliable, scalable alternative to many of the, commercial RDBMS today., Create and supported by MySQL AB, a company, based in Sweden. This company is now subsidiary of, Sun Microsystems. On April 2009 Oracle Corp., acquires Sun Microsystems., The chief inventor of MySQL was Michael, Widenius(a.k.a Monty). MySQL has been named after, Monty’s daughter My. The logo of MySQL is dolphin, and name of that dolphin is ‘Sakila’
Page 21 :
MYSQL DATABASE SYSTEM, VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &, SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR, , MySQL database system refers to the, combination of a MySQL server instance and, MySQL database., It operates using Client/Server architecture in, which the server runs on the machine containing, the database and client connects to server over a, network, MySQL is a multiuser database system, meaning, several users can access the database, simultaneously,
Page 22 :
MYSQL DATABASE SYSTEM, The Server, , , , , Listens for client requests coming in over the, network and access the database as per the, requirements and provide the requested information, to the Client, , The Client, , , Are the programs that connect to MySQL server and, sends requests to the server and receives the, response of Server. Client may be the MySQL prompt, or it may be Front-end programming which connect, to server programmatically like connecting to MySQL, using Python Language or Java or any other, language, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &, SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR, ,
Page 23 :
FEATURES OF MYSQL, Speed, , , , Ease of Use, , , , , Supports SQL, , Portability, , , , , Is available free of cost. It is Open Source, , Query language Support, , , , , Can be managed from command line or GUI, , Cost, , , , , MySQL runs very fast., , Can be run on any platform and supported by various, compilers, , Data Types, , , Supports various data types like Numbers, Char etc., , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &, SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR, ,
Page 24 :
FEATURES OF MYSQL, Security, , , , , Scalability and Limits, , , , , Clients can connect to MySQL using drivers, , Localization, , , , , Can handle large databases. Some of real life MySQL, databases contains millions of records., , Connectivity, , , , , Offers privileges and password systems that is very flexible, and secure., , The server can provide error message to client in many, language, , Client and Tools, , , Provides several client and utility programs. Like, mysqldump and mysqladmin. GUI tools like MySQL, Administration and Query Browser, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &, SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR, ,
Page 27 :
SQL AND MYSQL, VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &, SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR, , SQL stands for Structured Query Language., Is a language that enables you to create and, operate on relational databases., MySQL uses SQL in order to access databases., It is the standard language used by almost all the, database s/w vendors., Pronounced as SEQUEL, Original, version was developed by IBM’s, Almanden Research Center, Latest ISO standard of SQL was released in 2008, and named as SQL:2008,
Page 29 :
DATA DEFINITION LANGUAGE, , , , , , , , , , It allows to create database objects like creating, a table, view or any other database objects., The information about created objects are stored, in special file called DATA DICTIONARY, DATA DICTIONARY contains metadata i.e. data, about data., While creating a table DDL allows to specify –, name of table, attributes, data types of each, attribute, may define range of values that, attributes can store, etc, Major commands of DDL are – CREATE,, ALTER, DROP, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &, SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR, ,
Page 30 :
DATA MANIPULATION LANGUAGE, It allows to perform following operation on, table, Retrieval of information stored in table, Insertion of new data in table, Modification of existing data in table, Deletion of existing data from table, , , , , DML is of 2 type, Procedural DML (in this we specify what data is, needed and how to get it), Non-Procedural DML (in this we specify what, data is needed without specifying how to get it), , , , , Main DML commands are – SELECT,, INSERT, UPDATE AND DELETE, , VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &, SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR, ,
Page 31 :
JUST A MINUTE…, VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &, SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR, , What is Database? What are the advantages of, Database System?, What is DDL and DML? Give examples of, command belonging to each category, What is the difference between Primary key and, Candidate key, What is Primary Key? What are the restriction, imposed by Primary Key? How many primary, key can be applied on a Table?, What is Degree and Cardinality of table?,