Intro to Database PDF

Intro to Database Topic Objectives: 1.Know the basic concepts of the database 2.Understand the reason for using the database 3.Understand the characteristics of a database 4.Understand the basic structure of the database What is Database? It is a structured collection of records or data that is...

Intro to Database Topic Objectives: 1.Know the basic concepts of the database 2.Understand the reason for using the database 3.Understand the characteristics of a database 4.Understand the basic structure of the database What is Database? It is a structured collection of records or data that is stored in a computer system. Use as a method of storing, managing, and retrieving information Purpose of database The purpose of the database are: Stores data Provide an organizational structure for data Provide a mechanism for querying, creating, modifying, and deleting of data (CRUD) Characteristics of Database Stores large amount of records Retrieve or access data or information easily and quickly. It can provide data security or protection. Basic structure of a database DATABASE is basically made up of TABLES. Tables can also be called relation. Table on the other hand is made up of ROWS (also called records or tuples) and COLUMNS (also called fields or attribute) Spreadsheet vs. Database A spreadsheet stores data values in cells, with multiple cells represented in a system of rows and columns. A database typically stores data values in tables. Each table has a name and one or more columns and rows.  DATABASE MANAGEMENT SYSTEMS WHAT IS DBMS? The Database Management System, is a software application, used to create, manage, and administer the databases. Create their own Databases Some of the most commonly used Database Management Systems include, MySQL, ORACLE, Microsoft SQL Server, and there are many such popular DBMS in the market. Database System Environment COMPONENTS OF DBMS Hardware ○ Actual computer system used for keeping and accessing the database. Software ○ It is the DBMS itself Data ○ Main component of DBMS. Users ○ It is the one who can access, manipulate and retrieve data from the database. Database Users End users Database Administrators (DBA) Application Programmers (Software Engineers) System Analyst End Users These users access the database from the front end with the help of a pre-developed application. They have little knowledge about the design and working of databases. Types of End users: ○ Naïve Users - Use predefined queries, often through a user-friendly interface.  - They depend on pre-developed applications like Bank Management Systems, Library Management Systems, Hospital Management Systems, and Railway Ticket Booking Systems(IRCTC) and get the desired result. - ○ Sophisticated Users - Write their own queries for more complex operations. Database Administrators (DBA) Responsible for managing the database system, ensuring it runs efficiently, securely, and is available to authorized users. Backup and recovery, user management, security, performance tuning, and database maintenance. Application Programmers Developers who write applications that interact with the database. Writing queries, stored procedures, and integrating databases with applications. System Analyst Responsible for the design, structure, and properties of databases. The analyst will gather information from the shareholders as well as end users to understand their requirements and translate it into functional specifications for the new system. ***** RELATIONAL MODEL A data model in which the data is organized in relations (tables).This is the model implemented in most of the modern DBMS. RELATIONAL DATABASE MANAGEMENT SYSTEM It is a program use to create, update, and administer a relational database. Invented by Edgar ‘Ted’ F. Codd – 1970 Data are stored in tables and that relationships exist between the tables. Data are manipulated using SQL WHAT IS SQL? SQL stands for Structured Query Language It is used to communicate with a database. According to ANSI, it is the standard language for RDBMS SQL lets you access and manipulate databases WHAT CAN SQL DO? SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database  SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views DIFFERENCE BETWEEN DBMS AND RDBMS DBMS stores data in the form of files, whereas RDBMS stores data in the form of tables. DBMS supports single users, while RDBMS supports multiple users. DBMS can only manage small amount of data, while RDBMS can handle large amount of data. DBMS has low software and hardware requirements whereas RDBMS has higher hardware and software requirements. In DBMS, data redundancy is common while in RDBMS, keys and indexes do not allow data redundancy. PRIMARY KEY To qualify as a primary key, an attribute must have the following properties: It must have a non-null (not empty) value for each instance of the entity The value must be unique for each instance of an entity The value must not change or become null during the life of each entity instance FOREIGN KEY It completes a relationship by identifying the parent entity. It provides a method for maintaining integrity in the data (called referential integrity) Every relationship in the model must be supported by a foreign key. IDENTIFYING KEYS TABLE RELATIONSHIPS To prevent the duplication of information in a database by repeating fields in more than one table Table relationships can be established to link fields of tables together.  One-to-One Relationship One-to-Many Relationship/Many-to-one Relationship Many-to-Many Relationship ***** Database and Database Objects Database Creation A maximum 32,767 databases can be specified on an SQL Server There are three types of files used to store database: Primary File - it is used store data. - Every database has one primary file. Secondary File - holds all of the data that does not fit in the primary data file. Transaction Log File - holds the log information use to recover the database. Every Database has at least two files, a primary file and a transaction log file. Syntax for Creating Database: Rules in naming a database: 1. Database name must be unique 2. Must conform to the rules of identifiers 3. Can be a maximum of 128 characters Example: Database Creation CREATE DATABASE Student_Database_Information CREATE DATABASE IF NOT EXISTS Student_Database_Information Modifying, Viewing, Renaming, Deleting Viewing: SHOW CREATE DATABASE [database name] Renaming: Click the "Operations" tab. Type a new database name in the field “Rename database to:” and click Go. Deleting: DROP DATABASE Modifying database: ALTER DATABASE ADD FILE (Name = , Filename = , size = , Maxsize = , filegrowth = ) [REMOVE FILE Exercise on Altering, Viewing, Renaming, and Dropping of database 1. Modify Employeenfo Database by adding one secondary file with logical name as EmpInfo2_data. Use External FileName as EmpInfo2.ndf with the same sizes as that of the primary file. Refer to your exercise on Database Creation. 2. View Database EmployeeInfo 3. Change the name of Database from EmployeeInfo to EmployeeNewInfo 4. Remove the Database Database Object A database object is any defined logical unit within the database that is used to store information or reference data. This is commonly referred as the back-end database. Database Object: Schemas Tables Synonyms Views Indexes Stored Procedures Functions Constraints Triggers What is Schemas? In a simplest form, Schema is known as the owner of an object or group of objects; It contains database objects such as tables, view, or stored procedures; It cannot be removed if it is being referenced or used by another object; ‘dbo’ is the default schema owner Syntax: Create Schema [schema_name] What is a Table? A table is a database object used to store the physical data. It is organized in rows and columns A NULL value is an unknown value and it is different from empty or zero value Guidelines for Creating Table Identify Table Name Identify attributes or field names of the table Identify whether those attributes are null or not null Identify the datatypes to be used for each attribute Identify the length of attributes Syntax for Creating Table: CREATE TABLE table_name ( column1 datatype (datatype size) [null | not null], column2 datatype (datatype size) [null | not null, column3 datatype (datatype size) [null | not null,.... ); Syntax for Creating Table (Using Another Table): CREATE TABLE new_table_name AS SELECT column1, column2,...  FROM existing_table_name WHERE....; Common Datatypes Altering, Adding, Removing Column in a Table Adding Column: ○ Alter table Add column_name datatype, Column_name2… Modifying Column: ○ Alter table MODIFY Column column_name new_datatype Removing Column: ○ alter table Drop COLUMN column_name, column_name2… ○ Viewing, Renaming, Removing Table Viewing Table Structure: ○ DESC or DESCRIBE Renaming Table: ○ ALTER TABLE RENAME TO Dropping Table: ○ DROP TABLE ***** DDL & DML Structured Query Language SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database. SQL was the first commercial language introduced for E.F Codd's Relational model Today almost all RDBMS ( MySql, Oracle, Infomix, Sybase, MS Access) uses SQL as the standard database language. SQL SQL functions fit into two broad categories : - Data definition language (DDL) - Data manipulation language (DML) Based on relational algebra, but not entirely identical.  - Relations ->Tables - Tuples->Rows - Attributes-> Columns Data Manipulation Language (DML) Data Manipulation Language (DML) statements are used for managing data within database. DML commands are not auto-committed. It means changes are not permanent to database, they can be rolled back. DML Commands INSERT Command Insert command is used to insert data into a table. Syntax: INSERT INTO table-name value (data1, data2.) For e.g. - Consider a table Student with following fields. It is possible to write the INSERT INTO statement in two ways: 1. Specify both the column names and the values to be inserted: INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...); 2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows: INSERT INTO table_name VALUES (value1, value2, value3,...); INSERT into Student values(101,'Adam',15); -The above command will insert a record into Student table. UPDATE Command  Update command is used to update a row of a table. Following is its general syntax, UPDATE table-name set column-name = value where condition; UPDATE Student set s_name='Abhi', s_age=17 where s_id=101; -The above command will update two columns of a record. DELETE Command Delete command is used to delete data from a table. Delete command can also be used with condition to delete a particular row. Syntax: ○ DELETE from table-name ; Example to Delete all Records from a Table: ○ DELETE from Student; The above command will delete all the records from Student table. DELETE from Student where s_id = 103 -The above command will delete the record where s_id is 103 from Student table Data Definition Language  Data Definition Language (DDL) statements are used to define the database structure or schema. All DDL commands are auto-committed. That means it saves all the changes permanently in the database. DDL Commands CREATE Command Create is a DDL command used to create a table or a database. Following is the Syntax, ○ CREATE DATABASE ; ○ CREATE TABLE ( COLUMN_NAME datatype(size) [null | not null], COLUMN_NAME datatype(size) [null | not null]...) Example for Creating Database, ○ create database Test; ALTER Command Alter command is used for alteration of table structures. There are various uses of alter command, such as: ○ to add a column to existing table to rename any existing column ○ to change datatype of any column or to modify its size. ○ alter is also used to drop a column TRUNCATE Command Truncate command removes all records from a table. But this command will not destroy the table's structure. When we apply truncate command on a table its Primary key is initialized. Syntax: ○ TRUNCATE TABLE ; DROP Command Drop query completely removes a table from database. This command will also destroy the table structure. Syntax: ○ DROP TABLE ; ○ DROP DATABASE For Example ○ DROP TABLE Student RENAME Command  Rename command is used to rename a table. Following is its Syntax, rename table old-table-name to new-table-name; For Example ○ rename table Student to Student-record; The above query will rename Student table to Student-record.