×
BE SO GOOD THEY CAN'T IGNORE YOU
--Your friends at LectureNotes
Close

Database Management System

by Anna SuperkingsAnna Superkings
Type: PracticalInstitute: ANNA UNIVERISTY Specialization: Information Technology EngineeringOffline Downloads: 5Views: 135Uploaded: 3 months ago

Touch here to read
Page-1

Database Management System by Anna Superkings

Experiment:
Anna Superkings
Anna Superkings

/ 243

Share it with your friends

Suggested Materials

Leave your Comments

Contributors

Anna Superkings
Anna Superkings
SYLLABUS (Common to Computer Science Engineering & Information Technology) 1. Creation of a database and writing SQL queries to retrieve information from the database. 2. Performing Insertion, Deletion, Modifying, Altering, Updating and Viewing records based on conditions. 3. Creation of Views, Synonyms, Sequence, Indexes, Save point. 4. Creating an Employee database to set various constraints. 5. Creating relationship between the databases. 6. Study of PL/SQL block. 7. Write a PL/SQL block to satisfy some conditions by accepting input from the user. 8. Write a PL/SQL block that handles all types of exceptions. 9. Creation of Procedures. 10. Creation of database triggers and functions 11. Mini project (Application Development using Oracle/ Mysql ) a) Inventory Control System. b) Material Requirement Processing. c) Hospital Management System. d) Railway Reservation System. e) Personal Information System. f) Web Based User Identification System. g) Timetable Management System. h) Hotel Management System LAB EQUIPMENTS Hardware and Software required for a batch of 30 students: Hardware: 30 Personal Computers Software: Front end : VB/VC ++/JAVA or Equivalent Back end : Oracle / SQL / MySQL/ PostGress / DB2 or Equivalent Platform : Windows 2000 Professional/XP Oracle server could be loaded and can be connected from individual PCs. 2
OBJECTIVES: The student should be made to: Learn to create and use a database Be familiarized with a query language Have hands on experience on DDL Commands Have a good understanding of DML Commands and DCL commands Familiarize advanced SQL queries. Be Exposed to different applications OUTCOMES: At the end of the course, the student should be able to: Design and implement a database schema for a given problemdomain Populate and query a database Create and maintain tables PL/SQL. Prepare reports. using REFERENCE: spoken-tutorial.org GENERAL INSTRUCTIONS FOR LABORATORY CLASSES DO’S Without Prior permission do not enter into the Laboratory. While entering into the LAB students should wear their ID cards. The Students should come with proper dress code. Students should sign in the LOGIN REGISTER before entering into the laboratory. Students should come with observation, record note, class notes, and lab relevant book to the laboratory. Students should maintain silence inside the laboratory. After completing the laboratory exercise, make sure to shutdown the system properly. DONT’S Students bringing the bags inside the laboratory.. Students wearing slippers insides the laboratory. Students using the computers in an improper way. Students scribbling on the desk and mishandling the chairs. Students using mobile phones inside the laboratory. Students making noise inside the laboratory. 3
INTRODUCTION TO SQL SQL: Structured query language pronounced as (SEQUEL). This language is used to communicate to oracle database. Database Management System (DBMS): It is a software it helps to manage the database management system should able to perform the following activities very easily. 1. Inserting the new data. 2. Updating the exiting data. 3. Deleting unnecessary data. 4. Retrieving the require data. A database along with the software which helps to manage. The database is called database management system (DBMS). A DBMS which is based on relational theory is called as relational database management system. Examples of RDBMS: 1. ORACLE 2. SQL SERVER 3. DB2 4. MYSQL 5. SYBASE 6. TERA DATA 7. MS ACCESS SQL Commands The SQL language is subdivided according to their functions as follows DDL - Data Definition Language DML - Data Manipulation Language DRL/DQL - Data Retrieval Language / Data Query Language DCL) - Data Control Language TCL) - Transaction Control 4
Data Definition Language (DDL): Data Definition Language (DDL) or Schema Definition Language, statements are used to define the database structure or schema. CREATE - to create objects in the database ALTER - alters the structure of the database DROP - delete objects from the database TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed COMMENT - add comments to the data dictionary RENAME - rename an object Data Manipulation Language (DML): Data Manipulation Language (DML) statements are used for managing data within schema objects. INSERT - insert data into a table UPDATE - updates existing data within a table DELETE - deletes all records from a table, the space for the records remain MERGE - UPSERT operation (insert or update) CALL - call a PL/SQL or Java subprogram EXPLAIN PLAN - explain access path to data LOCK TABLE - control concurrency Data Retrieval Language / Data Query Language (DRL/DQL): SELECT - retrieve data from the a database Data Control Language (DCL): Data Control Language (DCL) statements. Some examples: GRANT - gives user's access privileges to database REVOKE - withdraw access privileges given with the GRANT command Transaction Control (TCL): Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. COMMIT - save work done SAVEPOINT - identify a point in a transaction to which you can later roll back ROLLBACK - restore database to original since the last COMMIT SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use 5

Lecture Notes