Introduction to database & sql

WELCOME TO OUR PRESENTATION INTRODUCTION TO DATABASE & SQL 1

What is a Database? 2  Database is a collection of related data, that contains information relevant to an enterprise.  For example: 1. University database 2. Employee database 3. Student database 4. Airlines database etc…..

PROPERTIES OF A DATABASE3  A database represents some aspect of the real world, sometimes called the miniworld or the universe of discourse (UoD).  A database is a logically coherent collection of data with some inherent meaning.  A database is designed, built and populated with data for a specific purpose.

What is Database Management System (DBMS)?4  A database management system (DBMS) is a collection of programs that enables users to create & maintain a database. It facilitates the definition, creation and manipulation of the database.  Definition – it holds only structure of database, not the data. It involves specifying the data types, structures & constraints for the data to be stored in the database.  Creation –it is the inputting of actual data in the database. It involves storing the data itself on some storage medium that is controlled by the DBMS.  Manipulation-it includes functions such as updation, insertion, deletion, retrieval of specific data and generating reports from the data.

A SIMPLIFIED DATABASE SYSTEM ENVIRONMENT55

Typical DBMS Functionality 6  Define a database : in terms of data types, structures and constraints  Construct or Load the Database on a secondary storage medium  Manipulating the database : querying, generating reports, insertions, deletions and modifications to its content  Concurrent Processing and Sharing by a set of users and programs – yet, keeping all data valid and consistent

Typical DBMS Functionality 7 Other features:  Protection or Security measures to prevent unauthorized access  “Active” processing to take internal actions on data  Presentation and Visualization of data

Database System 8  The database and the DBMS together is called the database system.  Database systems are designed to manage large bodies of information.  It involves both defining structures for storage of information & providing mechanisms for the manipulation of information.  Database system must ensure the safety of the information stored.

Database System Applications 9  Banking- for customer information, accounts & loans, and banking transactions.  Airlines-for reservations & schedule information.  Universities-for student information, course registration and grades.  Credit card transactions-for purchases on credit cards & generation of monthly statements.  Telecommunication-for keeping records of calls made, generating monthly bills, maintaining balances, information about communication networks.  Finance-for storing information about holdings, sales & purchases of financial instruments such as stocks & bonds.  Sales-for customer, product and purchase information.  Manufacturing-for management of supply chain & for tracking production of items in factories.  Human resources-for information about employees, salaries, payroll taxes and benefits

Functions of Database administrators (DBA)10  Coordinating & monitoring the database  Authorizing access to the database  For acquiring hardware & software resources as needed by the user  Concurrency control checking  Security of the database  Making backups & recovery  Modification of the database structure & its relation to the physical database

Advantages of DBMS 11  Controlling Redundancy  Restricting Unauthorized Access  Providing Storage Structures for Efficient Query Processing  Providing Backup and Recovery  Providing Multiple User Interfaces  Representing Complex Relationship among Data  Enforcing Integrity Constraints  Permitting Inferencing and Actions using Rules

Disadvantages of DBMS 12  Cost of Hardware & Software  Cost of Data Conversion  Cost of Staff Training  Appointing Technical Staff  Database Damage

Different parts of a database  Fields  Records  Queries  Reports

Fields  Database storage units  Generic elements of content

Records A simple table showing fields (columns) and records(rows): And as part of an MS Access database table:

Queries  Queries are the information retrieval requests you make to the database  Your queries are all about the information you are trying to gather

Reports  If the query is a question... ...then the report is its answer  Reports can be tailored to the needs of the data-user, making the information they extract much more useful

SQL is used for:  Data Manipulation  Data Definition  Data Administration  All are expressed as an SQL statement or command. 19

Using SQL20 To begin, you must first CREATE a database using the following SQL statement: CREATE DATABASE database_name Depending on the version of SQL being used the following statement is needed to begin using the database: USE database_name

Using SQL  To create a table in the current database, use the CREATE TABLE keyword 21 CREATE TABLE authors (auth_id int(9) not null, auth_name char(40) not null) auth_id auth_name (9 digit int) (40 char string)

Table Design22 Rows describe the Occurrence of an EntityName Address Jane Doe 123 Main Street John Smith 456 Second Street Mary Poe 789 Third Ave Columns describe one characteristic of the entity

Using SQL  To insert data in the current table, use the keyword INSERT INTO 23 auth_id auth_name Then issue the statement SELECT * FROM authors INSERT INTO authors values(‘000000001’, ‘John Smith’) 000000001 John Smith

Data Retrieval (Queries)  Queries search the database, fetch info, and display it. This is done using the keyword 24 SELECT * FROM publishers pub_id pub_name address state 0736 New Age Books 1 1st Street MA 0987 Binnet & Hardley 2 2nd Street DC 1120 Algodata Infosys 3 3rd Street CA The *Operator asks for every column in the table.

Data Input  Putting data into a table is accomplished using the keyword 25 pub_id pub_name address state 0736 New Age Books 1 1st Street MA 0987 Binnet & Hardley 2 2nd Street DC 1120 Algodata Infosys 3 3rd Street CA Table is updated with new information INSERT INTO publishers VALUES (‘0010’, ‘pragmatics’, ‘4 4th Ln’, ‘chicago’, ‘il’) pub_id pub_name address state 0010 Pragmatics 4 4th Ln IL 0736 New Age Books 1 1st Street MA 0987 Binnet & Hardley 2 2nd Street DC 1120 Algodata Infosys 3 3rd Street CA

Data Retrieval (Queries)  Queries can be more specific with a few more lines 26 pub_id pub_name address state 0736 New Age Books 1 1st Street MA 0987 Binnet & Hardley 2 2nd Street DC 1120 Algodata Infosys 3 3rd Street CA Only publishers in CA are displayed SELECT * from publishers where state = ‘CA’

Using SQL27 SELECT auth_name, auth_city FROM publishers auth_id auth_name auth_city auth_state 123456789 Jane Doe Dearborn MI 000000001 John Smith Taylor MI auth_name auth_city Jane Doe Dearborn John Smith Taylor If you only want to display the author’s name and city from the following table:

Using SQL28 DELETE from authors WHERE auth_name=‘John Smith’ auth_id auth_name auth_city auth_state 123456789 Jane Doe Dearborn MI 000000001 John Smith Taylor MI To delete data from a table, use the DELETE statement:

Using SQL29 UPDATE authors SET auth_name=‘hello’ auth_id auth_name auth_city auth_state 123456789 Jane Doe Dearborn MI 000000001 John Smith Taylor MI To Update information in a database use the UPDATE keyword Hello Hello Sets all auth_name fields to hello

Using SQL30 ALTER TABLE authors ADD birth_date datetime null auth_id auth_name auth_city auth_state 123456789 Jane Doe Dearborn MI 000000001 John Smith Taylor MI To change a table in a database use ALTER TABLE. ADD adds a characteristic. ADD puts a new column in the table called birth_date birth_date . . Type Initializer

Using SQL31 ALTER TABLE authors DROP birth_date auth_id auth_name auth_city auth_state 123456789 Jane Doe Dearborn MI 000000001 John Smith Taylor MI To delete a column or row, use the keyword DROP DROP removed the birth_date characteristic from the table auth_state . .

Using SQL32 DROP DATABASE authors auth_id auth_name auth_city auth_state 123456789 Jane Doe Dearborn MI 000000001 John Smith Taylor MI The DROP statement is also used to delete an entire database. DROP removed the database and returned the memory to system

Introduction to database & sql

In this document

More Related Content

What's hot

Viewers also liked

Similar to Introduction to database & sql

More from zahid6

Recently uploaded

Introduction to database & sql