Page 1 :
What is the Database?, , , , o, , , , A database is an organized collection of interrelated, data stored together to serve applications. It work, like a container which may contains the various, database objects., , Most of the databases stores data in the form of, Relations (also called Tables). Such Database are, known as Relational Database., , A Software used to manage Relational database is, called RDBMS (Relational Database Management, System)., , , , , , Why Database System is used? (Advantages), , , , o, , Databases reduces Redundancy, , It removes duplication of data because data are kept at one, place and all the application refers to the centrally maintained, database., , Database controls Inconsistency, , When two copies of the same data do not agree to each other,, then it is called Inconsistency. By controlling redundancy, the, inconsistency is also controlled., , Database facilitate Sharing of Data, , Data stored in the database can be shared among several, users., , Database ensures Security, , Data are protected against accidental or intentional disclosure, to unauthorized person or unauthorized modification., Database maintains Integrity, , \t enforces certain integrity rules to insure the validity or, correctness of data. For ex. A date can't be like 25/25/2000., , , , , , Data model describes ‘How data is organized or stored’ in the, , database. It may be, Relational Data Model, , In this model data is organized into Relations or Tables (i.e., Rows and Columns). A row in a table represents a relationship of, data to each other and also called a Tuple or Record. A column is, called Attribute or Field., , Network Data Model, , In this model, data is represented by collection of records and, relationship among data is shown by Links., , Hierarchical Data Model, , In this model, Records are organized as Trees. Records at top, level is called Root record and this may contains multiple directly, linked children records., , Object Oriented Data Model, , In this model, records are represented as a objects. The collection, of similar types of object is called class., , , , Scanned with CamScanner

Page 2 :
Data Models, , 22 Katra, , , , , , , , [Representation of Records and Relationship in various Data Model |, , , , , , Qo, , o, , oo 0 8, , RDBMS Terminology, , Relation (Table), , A Relation or Table is Two-Dimensional (Matrix) like structure, , arranged in Rows and Columns. It has the following properties, * Column homogeneous - All items in a column are of same data, type., , % Each column assigned a unique name and must have atomic, (indivisible) value., , All rows of a relation are distinct i.e. no two identical rows, (records) can exist in the Relation., , % Ordering or Rows (Records) or Columns (fields) are immaterial., , Domain, , It is collection (set) of possible values from which the value for a, column is derived., , Tuple/ Entity/ Record :, , A Row of a table is called Tuple or Record., Attribute/ Field:, , Column of a table is called Attribute or Field., Degree : Number of columns (attributes) in a table., Cardinality : Number of Records in a table., , , , , , Concept of Keys, , , , In a Relation, each record must be unique i.e. no two identical, records are allowed in the Database. A column or combination of, column which identifies a record called Key of the Table. A key, attribute must have unique (non-repeatable ) value., , O Primary Key, , A set of one or more column that can uniquely identify a record in, the relation is called Primary Key., , O Candidate Key, , A Column or group of columns which can be used as primary key, are called Candidate keys, as they are candidate to become as, Primary key., , O Alternate Key, , A Candidate Key that is not a Primary key is called Alternate key., , O Foreign Key, , A non-key column whose values are derived from the primary key, of some other table is called Foreign key., , , , Scanned with CamScanner

Page 3 :
Introduction to MySQL mse, , MySQL is an Open Source, Fast and Reliable Relational Database, Management System (RDBMS) . It is alternative to many of the, commercial RDBMS. The main features of MySQL are© Open Source & Free of Cost:, It is Open Source and available free of cost. it is part of LAMP, (Linux, Apache, MySQL, PHP/ Perl/ Python) Open Source group., © Portability:, It can be installed and run on any types of Hardware and OS like, Linux, MS Windows or Mac etc,, O Security:, It offers privilege and password system for authorization,, O Connectivity, \t may connect various types of client using different protocols and, Programming Languages ., O Query Language, It uses SQL (Structured Query Language) as query language, which, is standardized by ANSI., , , , , , Types of SQL Commands, , MySQL follows SQL specifications for its commands . These, SQL commands can be categorized as , © Data Definition Language (DDL), , These SQL commands are used to create, alter and delete, database objects like table, views, index etc., , Example : CREATE , ALTER , DROP etc., © Data Manipulation Language (DML), , These commands are used to insert, delete, update and retrieve, the stored records from the table., , Ex. SELECT..., INSERT... DELETE.., UPDATE... etc., © Transaction Control Language (TCL), These commands are used to control the transaction., Ex. COMMIT, ROLLBACK, SAVEPOINT etc., © Data Control Language (DCL), , These commands are used to manipulate permissions or access, rights to the tables etc., Ex. GRANT , REVOKE etc., , , , , , Database Handling commands in MySQL, ————————————, , O Creating a Database., , The following command will create School database in MySQL., mysql> CREATE DATABASE School;, , O Opening a database, To open an existing database, following command is used., mysql> USE school ;, , O Getting listings of database and tables, mysql> SHOW DATABASES;, mysql> SHOW TABLES;, , O Deleting a Database and Table, mysql> DROP DATABASE School;, mysql> DROP TABLE Student;, , O Viewing Table Structure, mysql> DESCRIBE Student;, , , , , , Scanned with CamScanner

Page 4 :
Data type in MySQL, , O Numeric Data Types:, @ INTEGER or INT - up to 11 digit number without decimal., ™ SMALLINT ~ up to 5 digit number without decimal,, ™ FLOAT (M,D) or DECIMAL(M,D) or NUMERIC(M,D), , Stores Real numbers upto M digit length (including .) with D, decimal places., , e.g. Float (10,2) can store 1234567.89, O Date & Time Data Types:, ™ DATE - Stores date in YYYY-MM-DD format., ™ TIME - Stores time in HH:MM:SS format., O String or Text Data Type:, = CHAR(Size), A fixed length string up to 255 characters. (default is 1), ™ VARCHAR(Size), A variable length string up to 255 characters., , , , Char, Varchar, Date and Time values should be enclosed with single (‘*) or, double ( “*) quotes in MySQL., , , , , , , , Creating Tables, , , , O Creating Simple Tables:, CREATE TABLE < Table Name>, (<Col name1><data type>[(size)][Constraints],...);, Data types- INTEGER, NUMERIC(P,D), CHAR(n), VARCHAR(n), DATE etc., , , , , , O Creating Table from Existing Table:, CREATE TABLE <Table name> [AS] (<Select Query>);, , , , , , , , , , , , Making Simple Queries Using SELECT, , , , The SELECT command of SQL, empower you to make a, request (queries) to retrieve stored records from the, database., , The syntax of SQL is given below, SELECT < [Distinct | ALL] *| column name(s) >, FROM <table(s)>, , WHERE <condition>, , ORDER BY <column name> [ASC | DESC] ;, Consider the table Student having some records as —, , , , , , , , , , , , Si Amitabh Harivansh Rai 1948-11-10 | Allahabad | 12, $2 Sharukh Firoz 1970-05-10 | Delhi WV, $3 Irphan Akbar 1970-10-05 | Jaipur W, S4 Salman Salim Javed 1972-04-10 | Mumbai 10, ss Abhishek Amitabh 1975-03-12 | Mumbai 10, , , , , , , , , , , , , , , , , , , , Scanned with CamScanner

Page 5 :
Making Simple Queries —- Cont.., , , , OQ Selecting all columns, , If you want to view all columns of the student table, then you, should give the following commandmysql> SELECT * FROM Student ;, , MySQL will display the all records with all columns in the Student table., * Is used to represent all columns., , , , , , , , , , , , st Amitabh 1948-11-10 | Al 12, $2 Sharukh Firoz 1970-05-10 | Delhi aa, s3 Irphan Akbar 1970-10-05 | Jaipur n, s4 Salman Salim Javed 1972-04-10 | Mumbai 10, ss Abhishek Amitabh 1975-03-12 | Mumbai 10, , , , , , , , , , , , , , , , , , , , Making Simple Queries - Cont, O Selecting columns, , If you want to view only Name and City columns of the student table, , mysql> SELECT Name, City FROM Student ;, , , , Amitabh Allahabad, Sharukh Delhi, , Irphan Jaipur, Saiman Mumbai, Abhishek Mumbai, , mysql> SELECT City, Name FROM Student ;, , , , Allahabad Amitabh, Delhi Sharukh, Jaipur Irphan, Mumbai Salman, Mumbai Abhishek, , , , , , Making Simple Queries — Cont.., , , , O Eliminating Duplicate values in a column DISTINCT, mysql> SELECT City FROM Student ;, , Allahabad, Delhi, , Jaipur, Mumbai, Mumbai, , mysql> SELECT DISTINCT City FROM Student ;, , , , Allahabad, Delhi, Jaipur, Mumbai, , , , , , , , , , , , , , , , Scanned with CamScanner