15CSL58: DATABASE MANAGEMENT LABORATORY INTRODUCTION Database: A Database is a collection of interrelated data and a Database Management System is a a software system that enables users to define, create and maintain the database and which provides which provides controlled access to the database SQL: It is structured query language, basically used to pass the query to retrieve and manipulate the information from database. Depending upon the nature of query, SQL is divided into different components: • DDL (Data Definition Language) • DML (Data Manipulation Language) • DCL (Data Control Language) DDL:
The Data Definition Language (DDL) is used to create the database (i.e. tables, ta bles, keys, relationships etc), maintain the structure of the database and destroy databases and database objects. Eg. Create, Drop, Alter, Describe, Truncate 1. CREATE statements: It is used to create the table. Syntax: CREATE TABLE table_name(columnName1 datatype(size), columnName2 datatype(size), .........); 2. DROP statements: To destroy an existing database, table, index, or view. If a table is dropped all records held within it are lost and cannot be recovered. Syntax: DROP TABLE table_name; 3. ALTER statements: To modify an existing database object. • Adding new columns: Syntax: Alter table table_name Add (New_columnName1 datatype(size), New_columnName2 datatype(size), .........) • Dropping a column from a table: Syntax: Alter table table_name DROP column columnName: • Modifying Existing columns: Syntax: Alter table table_name Modify (columnName1 Newdatatype(Newsize)); 4. Describe statements: To describe the structure (column and data t ypes) of an existing database, table, index, or view. Syntax: DESC table_name; 5. Truncate statements: To destroy the data in an existing database, table, index, or view. If a table is truncated all records held within it a re lost and cannot be recovered recov ered but the table structure is maintained. Syntax: TRUNCATE TABLE table_name; Data Manipulation Language (DML): • A Data Manipulation Language enables programmers and users of the database to retrieve insert, delete and update data in a database. e.g. INSERT, UPDATE, DELETE, SELECT.
DBMS LAB MANUEL
INSERT: INSERT statement adds one or more records to an y single table in a relational database. Syntax:
mysql>INSERT INTO tablename VALUES (expr1,expr2........); (or) mysql>INSERT INTO tablename(Attribute1,Attribute2,…..) -->VALUES(expr01,expr02........), --> (expr11,expr22........), --> (expr21,expr22........),…….; UPDATE: UPDATE statement that changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition. Syntax:
mysql>UPDATE table_name SET column_name = value,[ column_name = value ...] -->[WHERE condition]; DELETE: DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed. Syntax:
mysql>DELETE FROM tablename WHERE condition; SELECT: SELECT statement returns a result set of records from one or more tables. The select statement has optional clauses: • WHERE specifies which rows to retrieve • GROUP BY groups rows sharing a property so that an aggregate function can be applied to each group having group. • HAVING selects among the groups defined by the GROUP BY clause. • ORDER BY specifies an order in which to return the rows. Syntax:
mysql>SELECT FROM
WHERE; Where • Attribute list is a list of attribute name whose values to be retrieved by the query. • Table list is a list of table name required to process query. • Condition is a Boolean expression that identifies the tuples t o be retrieved by query. quer y. Data Constraints are the business Rules which are enforced on the data being stored in a table are called Constraints. Types of Data Constraints 1. I/O Constraint This type of constraint determines the speed at which data can be inserted or extracted from an Oracle table. I/O Constraints is divided into two different types The Primary Key Constraint The Foreign Key Constraint 2. Business rule Constraint This type of constraint is applied to data prior the data being inserted into table columns. Column level Table level • •
• •
DBMS LAB MANUEL
The PRIMARY KEY Syntax:
mysql>CREATE TABLE tablename (Columnname1 DATATYPE PRIMARY KEY NOT NULL, -->columnname2 DATATYPE,columnname3 DATATYPE,…….); The FOREIGN KEY Syntax
mysql>CREATE TABLE tablename (Columnname1 DATATYPE, columnname2 DATATYPE , -->columnname3 DATATYPE,……,FOREIGN KEY(Columnname)REFERENCES Tablename(Columnname) [ON DELETE CASCADE]); The table in which FOREIGN KEY is defined is called FOREIGN TABLE or DETAIL TABLE. The table in which PRIMARY KEY is defined and referenced by FOREIGN KEY is called PRIMARY TABLE or MASTER TABLE. ON DELETE CASCADE is set then DELETE operation in master table will trigger the DELETE operation for corresponding records in the detail table. ER- Diagram: It is an Entity – Relationship diagram which is used to represent the relationship between different entities. An entity is an object in the real world which is distinguishable from other objects. The overall logical structure of a database can be expressed graphically by an ER diagram, which is built up from following components. Rectangles: represent entity sets. Ellipses: represent attributes. Diamonds: represent relationships among entity sets. Lines: link attribute to entity sets and entity sets to relationships. Mapping Cardinalities: It expresses the number of entities to which another entity can be associated via a relationship set. For a binar y relationship set R between entity sets A and B. The Mapping Cardinalities must be one of the following. • One to one • One to many • Many to one • Many to many PART-A: SQL Programming (Max. Exam Mks. 50) Design, develop, and implement the specified queries for the following problems using Oracle, MySQL, MS SQL Server, or any other DBMS under LINUX/Windows environment. Create Schema and insert at least 5 records for each table. Add appropriate database constraints. Database programs (MySQL)
SQL commands to create and manipulate databases using MySQL. 1.Open the terminal by using “Alt+Ctrl+T” on keyboard. 2.Type the following command to get MySQL prompt: mysql -u root -p or OPEN MYSQL Application. 3.Enter the password root123 or Ask The instructor. 4.You will get the MySQL prompt.
DBMS LAB MANUEL
5.To create any table, you need to choose any one databases, so to know the databases that are created, use the command show, this command list-out the created databases. 6.New database can be created by using: CREATE SCHEMA BANK; USE BANK; CREATE TABLE BRANCH (BNAME VARCHAR (10) PRIMARY KEY, CITY VARCHAR (10),ASSETS REAL);. 7.USE command is used to select the database for creating tables under that database. 8.To get the tables information use DESC command. 9.To insert values into the table: INSERT INTO BRANCH VALUES('MAIN','BLR',2500000); 10.Contents of the table can be viewed by using the command SELECT * FROM TABLENAME; 11.In between if user wants to clear the screen of the terminal then press: ctrl+l. 12.To get any information about the commands used in the mysql use “help” command. Eg: help insert; help create; 13.Type exit to exit from the MySQL prompt.
DBMS LAB MANUEL
LAB PROGRAM-01 1. Consider the following schema for a Library Database: BOOK(Book_id, Title, Publisher_Name, Pub_Year) BOOK_AUTHORS(Book_id, Author_Name) PUBLISHER(Name, Address, Phone) BOOK_COPIES(Book_id, Branch_id, No-of_Copies) BOOK_LENDING(Book_id, Branch_id, Card_No, Date_Out, Due_Date) LIBRARY_BRANCH(Branch_id, Branch_Name, Address) Write SQL queries to 1. Retrieve details of all books in the library – id, title, name of publisher, authors, number of copies in each branch, etc. 2. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017 to Jun 2017. 3. Delete a book in BOOK table. Update the contents of other tables to reflect this data manipulation operation. 4. Partition the BOOK table based on year of publication. Demonstrate its working with a simple query. 5. Create a view of all books and its number of copies that are currently available in the Library.
SOL: ER DIAGRAM:
DBMS LAB MANUEL
Schema Diagram: Book Book_id
Title
Pub_Year
Publisher _Name
Book_Author Book_id
Author_name
Publisher Name
Phone_n o
Address
Book_Copies Book_id
Branch_i d
No_of_Copies
Book_Lending Book_id
Branch_id
Card_no
Date_out
Due_date
Library_Branch Branch_id
Address
Branch_name
BORROWERS Card_no
Name
Address
•
SHOW ALL DATABASES
•
CREATE A DATABASE “LIBRARY_DATABASE”
DBMS LAB MANUEL
•
SHOW THE CURRENT DATABASE SELECTED
•
USE THE DATABASE “LIBRARY_DATABASE”
•
SHOW THE CURRENT DATABASE SELECTED
•
SHOW TABLES PRESENT IN THE DATABASE
•
CREATE TABLE “PUBLISHER”
•
CREATE TABLE “BOOK”
•
CREATE TABLE “BOOK_AUTHOR”
•
CREATE TABLE “BORROWERS”
•
CREATE TABLE “LIB”
•
CREATE TABLE “BOOK_COPIES”
DBMS LAB MANUEL
•
CREATE TABLE “BOOK_LENDING”
•
INSERT VALUES INTO TABLE “PUBLISHER”
•
INSERT VALUES INTO TABLE “BOOK”
•
INSERT VALUES INTO TABLE “BOOK_AUTHOR”
•
INSERT VALUES INTO TABLE “BORROWERS”
•
INSERT VALUES INTO TABLE “LIB”
DBMS LAB MANUEL
•
INSERT VALUES INTO TABLE “BOOK_COPIES”
•
INSERT VALUES INTO TABLE “BOOK_LENDING”
•
DISPLAY ALL TABLES : 1.BOOK
2.Publisher
3.Book_author
DBMS LAB MANUEL
4.Book_copies
5.Book_lending
6.Lib
7.Borrowers
DBMS LAB MANUEL
QUERIES: 1. Retrieve details of all books in the library – id, title, name of publisher, authors, number of copies in each branch, etc.
2. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017 to Jun 2017.
3. Delete a book in BOOK table. Update the contents of other tables to reflect this data manipulation operation
DBMS LAB MANUEL
4. Create a view of all books and its number of copies that are currently available in the Library.
5. Partition the BOOK table based on year of publication. Demonstrate its working with a simple query. NOTE: INORDER TO DO PARTITION OF ANY TABLE THAT TABLE SHOULD BE FREE OF PK AND FK •
DROPING PRIMARY KEY
•
DISPLAY TABLE BEFORE PARTION
DBMS LAB MANUEL
•
PARTITION
•
DISPLAY TABLE AFTER PARTION
DBMS LAB MANUEL
LAB PROGRAM-02 2.Consider the following schema for Order Database: SALESMAN (Salesman_id, Name, City, Commission) CUSTOMER (Customer_id, Cust_Name, City, Grade, Salesman_id) ORDERS (Ord_No, Purchase_Amt, Ord_Date, Customer_id, Salesman_id) Write SQL queries to 1. Count the customers with grades above Bangalore’s average. 2. Find the name and numbers of all salesmen who had more than one customer. 3. List all salesmen and indicate those who have and don’t have customers in their cities (Use UNION operation.) 4. Create a view that finds the salesman who has the customer with the highest order of a day. 5. Demonstrate the DELETE operation by removing salesman with id 1000 . All his orders must also be deleted.
SOL: ER DIAGRAM:
SCHEMA:
DBMS LAB MANUEL
•
DATABASE CREATION:
•
USE DATABASE Lab2:
•
SELECT CURRENT DATABASE:
•
Table Creation: 1. SALESMAN
2. CUSTOMER
DBMS LAB MANUEL
3. ORDERS
•
DESCRIBE TABLE: 1. SALESMAN
2. CUSTOMER
3. ORDERS
DBMS LAB MANUEL
•
INSERT VALUES TO TABLE: 1.SALESMAN
2.CUSTOMER
3.ORDERS
QUERIES:
DBMS LAB MANUEL
1. Count the customers with grades above Bangalore’s average.
2. Find the name and numbers of all salesmen who had more than one customer.
3. List all salesmen and indicate those who have and don’t have customers in their cities (Use UNION operation.)
4. Create a view that finds the salesman who has the customer with the highest order of a day.
DBMS LAB MANUEL
5. Demonstrate the DELETE operation by removing salesman with id 1000. All his orders must also be deleted.
“ODER BY SALESMAN ID=1000 WILL BE ALSO DELETED IN TABLE ORDER”
DBMS LAB MANUEL
LAB PROGRAM-03 3.Consider the schema for Movie Database: ACTOR (Act_id, Act_Name, Act_Gender) DIRECTOR (Dir_id, Dir_Name, Dir_Phone) MOVIES (Mov_id, Mov_Title, Mov_Year, Mov_Lang, Dir_id) MOVIE_CAST (Act_id, Mov_id, Role) RATING (Mov_id, Rev_Stars) Write SQL queries to 1. List the titles of all movies directed by ‘Hitchcock’. 2. Find the movie names where one or more actors acted in two or more movies. 3. 3. List all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN operation). 4. 4. Find the title of movies and number of stars for each movie that has at least one rating and find the highest number of stars that movie received. Sort the result by movie title. 5. 5. Update rating of all movies directed by ‘Steven Spielberg’ to 5. Sol: ER DIAGRAM
DBMS LAB MANUEL
SCHEMA
•
DATABASE CREATION:
•
USE DATABASE Lab2:
•
SELECT CURRENT DATABASE:
•
CREATE TABLES: 1. ACTOR
DBMS LAB MANUEL
2. DIRECTOR
3. MOVIES
4. MOVIE_CAST
5. RATING
•
DESCRIBE TABLES: 1. ACTOR
2. DIRECTOR
DBMS LAB MANUEL
3. MOVIES
4. MOVIE_CAST
5. RATING
•
INSERT INTO TABLE 1. ACTOR
DBMS LAB MANUEL
2. DIRECTOR
3. MOVIES
4. MOVIE_CASE
DBMS LAB MANUEL
5. RATING
•
DISPLAY TABLES: 1. ACTOR
2. DIRECTOR
3. MOVIE
DBMS LAB MANUEL
4. MOVIE_CAST
5. RATING:
Queries: 1. List the titles of all movies directed by ‘Hitchcock’.
2. Find the movie names where one or more actors acted in two or more movies.
3. 3. List all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN operation).
DBMS LAB MANUEL
4. 4. Find the title of movies and number of stars for each movie that has at least one rating and find the highest number of stars that movie received. Sort the result by movie title.
5. 5. Update rating of all movies directed by ‘Steven Spielberg’ to 5.
