Laboratory Manual Database Management System Laboratory

Vidya Pratishthan’sCollege Of Engineering, BaramatiLaboratory ManualDatabase Management System LaboratoryThird Year - Information Technology (2012)Examination SchemePractical: 50 marksOral: 50 MarksTeaching SchemePractical: 04 Hrs/Week/BatchPrepared ByProf. Patil Pradeep M.Prof. Shah Sahil K.Department of Information TechnologyVidya Pratishthan's College of EngineeringBaramati - 413133Dist- Pune (M.S.)INDIAJUNE 2015

Vidya Pratishthan’sCollege of Engineering, BaramatiDepartment of Information Technology EngineeringApproval SheetLaboratory ManualDatabase Management System LaboratoryThird Year - Information Technology (2012)Prof. Patil P.M.Prof. Shah Sahil KSubject InchargeProf.S.A.TakaleHead IT DeptDr. S.B.DeosarkarPrincipal

List of AssignmentsSr.NoTitlePart – I: Database Programming Languages – SQL,PL/SQL, NoSQL12345678Study of Open source Database MySQLDesign and Development of SQL/NoSQL DDL statements todemonstrate the use of SQL object as Table , View, index andsequenceStudy of SQL queriesStudy of SQL Join typesWrite a PL/SQL code block to calculate the grade of minimum10 studentsWrite a PL/SQL block to implement all types of cursorWrite a PL/SQL stored procedure and functionWrite a Database trigger (Row level and Statement trigger)Part – II: Large Scale Databases123456712Implement aggregation and indexing with suitable exampleusing MongoDB.Implement Map reduces operation with suitable example usingMongoDB.Design and Implement any 5 query using MongoDBImplement any one Concurrency Control Protocol usingMongoDB and .net/JavaCreate simple objects and array objects using JSONEncode and Decode JSON Objects usingJava/Perl/PHP/Python/RubyImplement any machine learning algorithm for BIG dataPart – III : Mini Project : Database Project Life CycleDesign and Implement any Database Application using PHP/python/Java and MogoDB as aback end. Implement Database navigation operations (add, delete,edit etc. ) usingODBC/JDBC.Write a program in PHP/ python/Java/.net to access storedprocedure and functions usingODBC/JDBC

Syllabus:314447 : DATABASE MANAGEMENT SYSTEMS LABORATORYTeaching Scheme:Practical: 4 Hours/WeekExamination Scheme:Practical : 50 Marks Oral : 50 MarksPrerequisites :Discrete StructureCourse Objectives :1. To learn and understand Database Programming Paradigms2. To learn and understand SQL, PL/SQL ,NoSQL3. To learn Relational Database (Open source) such as MongoDB/ Oracle/MySQL/SQLServer4. To learn and understand Database Project Life Cycle.Course Outcomes :1. Understanding of Database Programming Languages2. Master the basics of database languages and construct queries using SQL, PLSQL, NoSQL3. Master the basic concepts of Database Project Life Cycle and appreciate the applications ofdatabase systems4. Understand how analytics and big data affect various functions now and in the future5. Appreciate the impact of analytics and big data on the information industry and theexternalecosystem for analytical and data servicesPart – I: Database Programming Languages – SQL, PL/SQL, NoSQL1. Study of Open Source Databases : MySQL/ MongoDB/CouchDB etc2. Design and Develop SQL/NoSQL DDL statements which demonstrate the use of SQLobjectssuch as Table, View, Index, Sequence, Synonym.3. Design at least 10 SQL/NoSQL queries for suitable database application usingSQL/NoSQL DML statements: Insert, Select, Update, Delete with operators, functions, andsetoperator.4. Design at least 10 SQL/NoSQL queries for suitable database application using SQL DMLstatements: all types of Join, Sub-Query and View.5. Write a PL/SQL block to calculate the grade of minimum 10 students.6. Write a PL/SQL block to implement all types of cursors.7. Write a PL/SQL stored procedure and function.8. Write a database Trigger (Row level and Statement level).Part – II: Large Scale Databases1. Implement aggregation and indexing with suitable example using MongoDB.2. Implement Map reduces operation with suitable example using MongoDB.3. Design and Implement any 5 query using MongoDB

4. Implement any one Concurrency Control Protocol using MongoDB and .net/Java5. Create simple objects and array objects using JSON6. Encode and Decode JSON Objects using Java/Perl/PHP/Python/Ruby7. Implement any machine learning algorithm for BIG dataPart – III : Mini Project : Database Project Life Cycle1. Design and Implement any Database Application using PHP/ python/Java and MogoDB asaback end. Implement Database navigation operations (add, delete, edit etc. ) usingODBC/JDBC.2. Write a program in PHP/ python/Java/.net to access stored procedure and functions usingODBC/JDBCGroup of students should submit the Project Report which will be consist of Title of theProject, Abstract, Introduction, scope, Requirements, Data Modeling features, DataDictionary, Relational Database Design, Database Normalization, Graphical User Interface,Source Code, Testing document, Conclusion.Instructor should maintain progress report of mini project throughout the semester fromproject group and assign marks as a part of the term workInstructions:1. Instructor should frame minimum six Practical Assignments on Part I2. Instructor should frame minimum seven Practical Assignments on Part II3. Instructor should frame minimum two Practical Assignments and Develop mini project onPart III1. Submission of each Practical Assignment should be in the form of handwritten write-ups/printout of source code and output2. Instructor should assign a mini project to a group of 3 - 4 students based Part III3. Practical Examination will be based on the all topics covered from Part I, Part II &Part III and questions will be asked to judge understanding of practical performed atthe time of practical examinationAll the assignments should be conducted on Latest version of Open Source OperatingSystems, tools and Multi-core CPU supporting Virtualization and Multi-Threading.Reference Books1. “SQL and PL/SQL for Oracle 10g Black Book”, Dr. P. S. Deshpande, DreamTech2. “SQL, PL/SQL: The Programming Language of Oracle”, Ivan Bayross, BPB Publication3. “Oracle 9i/10g The Complete Reference”, Kevin Loney, George Koch, Tata McGraw Hill4. “Managing and Using MySQL”, Reese G., Yarger R., King T., Williums H, 2nd Edition,Shroff Publishers and Distributors Pvt. Ltd., ISBN 81 - 7366 - 465 – X5. “ SQL Server – Black Book”, Dalton Patrik, DreamTech Press6. MongoDB ,O’Reilly Publications7. Hadoop, O’Reilly Publications8. Data Mining: Concepts and Techniques by Jiawei Han, Micheline Kamber, Jian Pei,Elsevier9. http://www.tutorialspoint.com/json/

Assignment No: Basic 1&2Aim: Study of Open Source Databases: MySQLTheory:MySQL is the most popular Open Source Relational SQL database management system.MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses.MySQL is popular because:1) It is released under open source2) It handles a large subset of the functionality of the most expensive and powerful databasepackages, it uses standard form of the well-known SQL data language,3) works on many operating systems and with many languages including PHP, PERL, C,C , JAVA, etc.4) MySQL works very quickly and works well even with large data sets and with PHP.MySQL supports large databases, up to 50 million rows or more in a table.5) The default file size limit for a table is 4GB, but we can increase this (if your operatingsystem can handle it) to a theoretical limit of 8 million terabytes (TB).Starting MySQL :To start MySQL obtain a user ID and a password created by your database administrator inorder to log on to the MySQL .To start MySQL you would:1. Select the Start button2. Select All Programs and then MySQL3. Select MySQL Server 5.04. Click on the MySQL Command line clientThe MySQL command line client window will be displayed5. Enter your password to log on to MySQL

MySQL has its own client interface, allowing you to move data around and change databaseconfiguration. Note that you should use a password to log in. The default username is root ifyou’ve installed MySQL on your own computer. Each MySQL server can host manydatabases. A web application may use its own proprietary database or a standard databaselike MySQL.Once you log in the following command line interface is displayed:To exit the MySQL Shell, just type QUIT or EXIT:mysql QUITmysql exitBasic Queries : Once logged in, you can try some simple queries. For example:

mysql SELECT VERSION();Note that :1. Most MySQL commands end with a semicolon (;)2. MySQL returns the total number of rows found, and the total time to execute the query.3. Keywords may be entered in any letter case i.e uppercase or lowercase. The followingcommands are equivalent.mysql select current date();mysql select CURRENT DATE();

4. use mysql as a simple calculator as:

5. You can also enter multiple statements on a single line. Just end each one with asemicolon:6. Multi-Line Commands :mysql determines where your statement ends by looking for the terminatingsemicolon, not by looking for the end of the input line.Here's a simple multiple-line statement:6. Canceling a Command : If you decide you don't want to executea command that you are in the process of entering, cancel it by typing \c. ( Notice the prompt. It switches back to mysql after you type \c, providingfeedback to indicate that mysql is ready for a new command.)

Prompts:At the MySQL prompt, you can enter database commands followed by Enter. Thereis also a set of commands that MySQL itself interprets. For a list of these commands,type help or \h at the mysql prompt. Following Table shows some of the promptsyou will see.Promptmysql - ' " MeaningWaiting for a command i.e ready for new commandWaiting for the next line of a multiple-line commandWaiting for the next line , waiting for completion of a string thatbegins with a single quote( “ ‘ “ )Waiting for the next line of a string that starts with a double quoteMySQL client Table nameSourceFilenameDatabase nametables or databasesMeaningExit the command-line utilityUse a specific databaseShow lists such as tables ordatabases availableDescribe a table’s columnsDisplay database version andstatusExecute commands from afile as a scriptThese commands allow you to perform tasks such as executing SQL commands that arestored in a script file using the source.

Using a Database :To get started on your own database, first check which databases currently exist.Use the SHOW statement to find out which databases currently exist on the server:mysql SHOW DATABASES;To create a new database, issue the “create database” command:((replacing myldatabseXYZ with your username)To the select a database, issue the “ use” command:Mysql select mydatabaseXYZ;

Creating a Table : Once you have selected a database, you can view all database tables:(An empty set indicates that I have not created any tables yet )