Introduction to Data Base & SQL

An Introduction to Data Base Assistant Prof. M. Shahid Department of Commerce, AMU, Aligarh Data Hierarchy Data Hierarchy  Data Base management System User DATA BASE DBMS...

An Introduction to Data Base Assistant Prof. M. Shahid Department of Commerce, AMU, Aligarh Data Hierarchy Data Hierarchy  Data Base management System User DATA BASE DBMS A database is an organized Tool: MySQL, MicrosoftSQL,Oracle, collection of related files DBMS is a computer software that provide the facility to user, to capture , create, update, delete, manage, use, and analyze data in databases. DBMS advantages Less redundancy Consistency Sharing Multiple views Security User friendly Data independence  Advantages of Database Approach  Self-describing nature of a database system: A DBMS catalog stores the description of a particular database (e.g. data structures, types, and constraints) The description is called meta-data. This allows the DBMS software to work with different database applications.  Insulation between programs and data: Called program-data independence. Allows changing data structures and storage organization without having to change the DBMS access programs. Slide 1- 6  Advantages of Database Approach Support of multiple views of the data: Each user may see a different view of the database, which describes only the data of interest to that user. Controlling redundancy in data storage and in development and maintenance efforts. Sharing of data among multiple users. Slide 1- 7  Advantages of Database Approach  Sharing of data and multi-user transaction processing: Allowing a set of concurrent users to retrieve from and to update the database. Concurrency control within the DBMS guarantees that each transaction is correctly executed or aborted Recovery subsystem ensures each completed transaction has its effect permanently recorded in the database OLTP (Online Transaction Processing) is a major part of database applications. This allows hundreds of concurrent transactions to execute per second.  Advantages of Database Approach Restricting unauthorized access to data. Providing Storage Structures (e.g. indexes) for efficient Query Processing Providing backup and recovery services. Providing multiple interfaces to different classes of users. Representing complex relationships among data. Enforcing integrity constraints on the database. Slide 1- 9  When not to use a DBMS  Main inhibitors (costs) of using a DBMS: High initial investment and possible need for additional hardware. Overhead for providing generality, security, concurrency control, recovery, and integrity functions.  When a DBMS may be unnecessary: If the database and applications are simple, well defined, and not expected to change. If there are stringent real-time requirements that may not be met because of DBMS overhead. If access to data by multiple users is not required. Slide 1- 10  When not to use a DBMS When no DBMS may suffice: If the database system is not able to handle the complexity of data because of modeling limitations If the database users need special operations not supported by the DBMS. Slide 1- 11 Functions of DBMS  Create databases  Create tables  Create supporting structures  Read database data  Modify database data (insert, update, delete)  Maintain database structures  Enforce rules  Control concurrency  Provide security  Perform backup and recovery Applications Areas Banking (Acc., Loan and Banking Transactions) Airlines (Reservation Schedules) Universities (student details, Attendance, Admissions) Credit card transactions (Credit card purchase and Monthly Statements) Telecommunications (Bill, Call and Users details) Finance (Sales, Purchase, Holdings of Bonds, Stocks) Sales (Product and Purchase details) Human Resource (Salaries, payroll taxes, benefits) Production (Supply chain, inventory status, orders) Database Users Users may be divided into Those who actually use and control the database content, and those who design, develop and maintain database applications (called “Actors on the Scene”), and Those who design and develop the DBMS software and related tools, and the computer systems operators (called “Workers Behind the Scene”). Slide 1- 14 Database Users Actors on the scene Database administrators: Responsible for authorizing access to the database, for coordinating and monitoring its use, acquiring software and hardware resources, controlling its use and monitoring efficiency of operations. Database Designers: Responsible to define the content, the structure, the constraints, and functions or transactions Slide 1- 15 against the database. They must communicate with the end-users and understand their needs. Relational Model  The model was first proposed by Dr. E.F. Codd of IBM Research in 1970  Informally, a relation looks like a table of values.  A relation typically contains a set of rows.  The data elements in each row represent certain facts that correspond to a real-world entity or relationship In the formal model, rows are called tuples  Each column has a column header that gives an indication of the meaning of the data items in that column In the formal model, the column header is called an attribute name (or just attribute) Example of a Relation Slide 5- 17 Formal Definitions - Schema  The Schema (or description) of a Relation: Denoted by R(A1, A2,.....An) R is the name of the relation The attributes of the relation are A1, A2,..., An  Example: CUSTOMER (Cust-id, Cust-name, Address, Phone#) CUSTOMER is the relation name Defined over the four attributes: Cust-id, Cust-name, Address, Phone#  Each attribute has a domain or a set of valid values. For example, the domain of Cust-id is 6 digit numbers. Slide 5- 18 Formal Definitions - Tuple  A tuple is an ordered set of values (enclosed in angled brackets ‘< … >’)  Each value is derived from an appropriate domain.  A row in the CUSTOMER relation is a 4-tuple and would consist of four values, for example:  This is called a 4-tuple as it has 4 values A tuple (row) in the CUSTOMER relation.  A relation is a set of such tuples (rows) Slide 5- 19 Formal Definitions - Summary  Formally, Given R(A1, A2,.........., An)  r(R)  dom (A1) X dom (A2) X....X dom(An)  R(A1, A2, …, An) is the schema of the relation  R is the name of the relation  A1, A2, …, An are the attributes of the relation  r(R): a specific state (or "value" or “population”) of relation R – this is a set of tuples (rows) r(R) = {t1, t2, …, tn} where each ti is an n-tuple ti = where each vj element-of dom(Aj) Slide 5- 20 Definition Summary Informal Terms Formal Terms Table Relation Column Header Attribute All possible Column Domain Values Row Tuple Table Definition Schema of a Relation Populated Table State of the Relation Slide 5- 21 Example – A relation STUDENT Slide 5- 22 Figure (but with different order of tuples) Slide 5- 23 What is RDBMS? RDBMS stands for Relational Database Management System. RDBMS is a program used to maintain a relational database. RDBMS is the basis for all modern database systems such as MySQL, Microsoft SQL Server, Oracle, and Microsoft Access. RDBMS uses SQL queries to access the data in the database.  KEYs  A superkey is a key defined either for an entity set or relationship set that uniquely identifies an entity, e.g., social-security number, phone number, combination of name and social-security number.  A candidate key is a minimal superkey that uniquely identifies either an entity or a relationship, e.g., social- security number, phone number.  A primary key is a candidate key that is chosen by the database designer to identify the entities of an entity set.  If any attribute/set of attribute in a table is primary key of another table, this is known as foreign key. E.g. A attribute Department No. in employee table act as foreign key as it is the primary key in Department table.  SQL: Structured Query Language Database language for storing, MySQL, manipulating and retrieving data stored in a MS Access relational database. Oracle Sybase 1970 – Dr. Edgar F. Codd of IBM is known SQL server as the father of relational databases. Query Dispatcher 1974- SQL appeared 1978 – IBM, System/R SQL Query Optimization 1986-IBM, ORACLE (First relational Engine, Engines database) Classic Query Engine Why SQL? SQL allows users to access data in the relational database describe the data. define the data in a database manipulate that data. embed within other languages create and drop databases and tables. view, stored procedure, functions set permissions on tables, procedures MYSQL  MySQL is a relational database management system  MySQL is open-source  MySQL is free  MySQL is ideal for both small and large applications  MySQL is very fast, reliable, scalable, and easy to use  MySQL is cross-platform  MySQL is compliant with the ANSI SQL standard  MySQL was first released in 1995  MySQL is developed, distributed, and supported by Oracle Corporation SQL Commands Data definition language (DDL) Create Alter Drop Data manipulation language (DML) Select Insert Update Delete Data Control language (DCL) Grant Revoke Thanking YOU!!