Lab Manual CSC371 DB-I V2 PDF

Preview

Summary

Lab Manual ...

Description

LAB MANUAL Course: CSC271: Database Systems

Department of Computer Science

Database Learning Procedure

J (Journey inside-out the concept) 2) Stage a1 (Apply the learned) 3) Stage v (Verify the accuracy) 4) Stage a2 (Assess your work) 1) Stage

COMSATS University Islamabad (CUI)

Table of Contents Lab # Lab # 01 Lab # 02 Lab # 03 Lab # 04 Lab # 05 Lab # 06 Lab # 07 Lab # 08 Lab # 09 Lab # 10 Lab # 11 Lab # 12 Lab # 13 Lab # 14 Lab # 15 Lab # 16

Topics Covered

Page #

MySQL and Workbench Environment Installation, Using and understanding tool Relational Algebra – Generic Operations Generic Operations Relational Algebra – Cross Product Cross Product Introduction to SQL Select Statement usage SELECT Query Modifiers Query Modifiers (ORDER BY and LIMIT) Lab Sessional 1 SQL Join Operations Join Operations SQL Outer/Inner Join Operations Outer/Inner Join Aggregation and Groups Aggregation Operations Sub Queries Sub Queries SQL as Data Manipulation Language DML Operations Lab Sessional 2

SQL as Data Definition Language DDL Operations Relational Database Design (Normalization) Normalization Entity-Relational Model and Diagram ERD Reinforcement of Joins and Aggregation Joins, Aggregations and groups Terminal Examination

2

LAB # 01 Statement Purpose: Data is a collection of raw facts and figures, processed to obtain useful information to assist organization in making decisions. Database is an organized collection of related data. In order to manage the databases, Database Management Systems (DBMS) or Database Systems offer sets of program and tools.

Activity Outcomes: After performing this lab students should be able to:    

Load or unload a database in MySQL through MySQL Workbench Explore structure of tables in a database Retrieve data from the tables Change existing data in the database tables

Instructor Note: As pre-lab activity, attend theory class.

Tools/Software Requirement   

MySQL Community Server 5.6 MySQL Workbench 6.1 Sakila Database

1)

Stage J (Journey)

Introduction Data is a collection of raw facts and figures, processed to obtain useful information to assist organization in making decisions. Database is an organized collection of related data. In order to manage the databases, Database Management Systems (DBMS) or Database Systems offer sets of program and tools.

Stage a1 (apply) Lab Activities: Activity 1: 1. MySQL Workbench is a unified visual tool for database architects, developers, and DBAs alike. Both MySQL Community Server and Workbench are already installed on your workstation. If they are not, download and installed the latest version from the MySQL 3

Website. MySQL Server usually runs as a service on port 3306 and it starts automatically. If it is not running, you should manually start it first. Afterwards open the Workbench. Its landing window appears as follows. 2. When Workbench first starts, it presents the Home window, which lists saved connections to MySQL Server. See the highlighted area in the snapshot. Connections help to manage and connect with MySQL Server that actually runs the database. At any time you may add a new connection by selecting the (+) icon. 3. Select an appropriate connection from the list. This opens a database management window as shown below.

Solution:

Activity 2: 1. Expand the Sakila database from the left side of the Workbench window. Among the listed categories, expand Tables. You will see list of tables in Sakila database. 2. Right click on any table of your choice (e.g. Actors) and select “Table Inspector”. It opens a new tab showing the schema, attribute names and other metadata. 3. Afterwards right click on the same table and now select “Select Rows”. It will open another tab for navigating through the contents of the database table. 4. The data view supports many different options such as sorting on a column by selecting its header, searching contents, deleting a row, adding another, or exporting the contents to an external file. You can play with these options. 5. Don’t forget to check the log pan below the query tab. It mentions all errors, warnings, and messages.

4

Solution:

2) Stage v (verify) Home Activities: 

3)

Answer questions from the quiz. These questions assume Sakila database hasn’t changed.

Stage a2 (assess)

Lab Assignment and Viva voce Deliverable There is no deliverable for this lab. You only have to attempt a quiz designed around the lab task. 5

LAB # 02 Statement Purpose: Relational Algebra is a meta-language and forms underlying basis of SQL query language. It has six basic operators including: select, project, union, set difference, rename, and cross product. The operators take one or two relations as inputs and produce a new relation as a result.

Activity Outcomes: After performing this lab students should be able to: 1. Implement relational algebra operations 2. Design query expressions by composing relational algebra operations 3. Retrieve data from a database using RA expressions

Instructor Note: As pre-lab activity, attend theory class.

Tools/Software Requirement    

MySQL Community Server 5.6 MySQL Workbench 6.1 Sakila Database Relational Algebra Interpreter (Duke University) o http://www.cs.duke.edu/~junyang/ra/ o https://github.com/junyang/RA

4)

Stage J (Journey)

Introduction Relational Algebra is a meta-language and forms underlying basis of SQL query language. It has six basic operators including: select, project, union, set difference, rename, and cross product. The operators take one or two relations as inputs and produce a new relation as a result.

Stage a1 (apply) Lab Activities: Activity 1: 1. Load RA Interpreter and unzip it to any appropriate folder. We will use the interpreter to write relational algebra queries and submit these to MySQL for retrieving required data. 2. Open console and change current directory to where the interpreter was placed. 3. Run following command from the console. 6

 java -jar ra.jar mysql.properties 4. A new ra prompt will be activated in the console. Here you can type and run relational algebra operations. 5. As specified in the mysql.properties file, the interpreter would be connected to MySQL and Sakila database. You can try to execute different expressions on the Sakila database. 6. Make sure MySQL Workbench is also running where you can inspect relation schema to cross check that the queries are referring to accurate relation and attribute names. Note that clicking on any relation on the left pane of Workbench opens relation information in a bottom left pane. 7. Preferably write relational algebra expression within the Workbench and ignore if any error is reported as the Workbench is primarily meant to work with SQL queries instead of relational algebra. Once written, the expression can be pasted on the ra console to execute the query. 8. The interpreter supports handful of relational algebra operations as listed in the following table. The interpreter uses a special syntax and the expression should end with semi-colon symbol. Examples are presented in the next table. Focus on first two operations. 9. You can start your experiment with the given relational algebra expressions. 10. Continue playing with the interpreter till you are comfortable with the querying syntax and mechanism.

Solution: Operation

Syntax and Description

SELECT

\select_{CONDITION} EXPRESSION Selection over an expression

PROJECT

\project_{ATTRIBUTE_LIST} EXPRESSION Projection on selected attributes

RENAME

\rename_{NEW_ATTRIBUTE_NAME_LIST} EXPRESSION Rename all attributes of an expression

UNION

EXPRESSION_1 \union EXPRESSION_2 Union between two expressions

DIFFERENCE

EXPRESSION_1 \diff EXPRESSION_2 Difference between two expressions

INTERSECT

EXPRESSION_1 \intersect EXPRESSION_2 Intersection between two expressions

CROSS PRODUCT JOIN

EXPRESSION_1 \cross EXPRESSION_2 Cross-product between two expressions EXPRESSION_1 \join EXPRESSION_2

7

Natural join between two expressions; The interpreter literally follows the rules for cross product that column names must be unique from both sides. Don't forget to apply rename operation if after cross product two columns with same name may appear in the schema.

1. You can start your experiment with the given relational algebra expressions.

Information Need

Expression in Interpreter Syntax

Copies of ALASKA PHANTOM in the inventory

\select_{film_id=flm_id and title='ALASKA PHANTOM'} ( film \cross ( \rename_{inventory_id, flm_id, store_id, last_updat} inventory ) );

Category of the film ICE CROSSING

\project_{name} ( \select_{category_id=cat_id} ( \select_{film_id=flm_id and title='ICE CROSSING'} ( film \cross ( \rename_{flm_id, cat_id, last_updat} film_category ) ) \cross (\rename_{category_id,name,last_updt} category) ) );

2. Continue playing with the interpreter till you are comfortable with the querying syntax and mechanism.

Activity 2: 1. Write relational algebra expression for the following information needs. Write their corresponding expressions in the interpreter syntax as well. 2. Store all expressions in a plain text file. 3. Information needs: a. List of movies with rental rate higher than $3. [2] b. List of addresses from the “Alberta” district. [2] 8

c. d. e. f. g.

List of actors by full name that have TOM as their first name. [3] Title and replace cost of movies that are PG rated. [3] Rental and return date of all rentals of customer 148. [3] List payment id, amount and payment date of all such payments made by HELEN HARRIS where the amount was more than $5. Hint: First find and note down customer id for the given name and then lookup payment information from the relevant relation. [3] h. Rental and return date for the movie HUNGER ROOF. [4]

Solution: Submit a PDF document including the relational algebra expressions in the interpreter syntax to answer above-mentioned information needs as well as snapshot of their outcome when executed on the interpreter.

5) Stage v (verify) Home Activities: 

6)

Answer questions from the task. These questions assume Sakila database hasn’t changed.

Stage a2 (assess)

Lab Assignment and Viva voce Deliverable Submit a PDF document including the relational algebra expressions in the interpreter syntax to answer above-mentioned information needs as well as snapshot of their outcome when executed on the interpreter.

9

LAB # 03 Statement Purpose: Relational Algebra is a meta-language and forms underlying basis of SQL query language. It has six basic operators including: select, project, union, set difference, rename, and cross product. The operators take one or two relations as inputs and produce a new relation as a result.

Activity Outcomes: After performing this lab students should be able to:   

Implement relational algebra operations Design query expressions by composing relational algebra operations Retrieve data from a database using RA expressions

Instructor Note: As pre-lab activity, attend theory class.

Tools/Software Requirement    

MySQL Community Server 5.6 MySQL Workbench 6.1 Sakila Database Relational Algebra Interpreter (Duke University) o http://www.cs.duke.edu/~junyang/ra/ o https://github.com/junyang/RA

7)

Stage J (Journey)

Introduction Relational Algebra is a meta-language and forms underlying basis of SQL query language. It has six basic operators including: select, project, union, set difference, rename, and cross product. The operators take one or two relations as inputs and produce a new relation as a result.

Stage a1 (apply) Lab Activities: Activity 1: 11. Load RA Interpreter and unzip it to any appropriate folder. We will use the interpreter to write relational algebra queries and submit these to MySQL for retrieving required data. 12. Open console and change current directory to where the interpreter was placed. 13. Run following command from the console. 10

 java -jar ra.jar mysql.properties 14. A new ra prompt will be activated in the console. Here you can type and run relational algebra operations. 15. As specified in the mysql.properties file, the interpreter would be connected to MySQL and Sakila database. You can try to execute different expressions on the Sakila database. 16. Make sure MySQL Workbench is also running where you can inspect relation schema to cross check that the queries are referring to accurate relation and attribute names. Note that clicking on any relation on the left pane of Workbench opens relation information in a bottom left pane. 17. Preferably write relational algebra expression within the Workbench and ignore if any error is reported as the Workbench is primarily meant to work with SQL queries instead of relational algebra. Once written, the expression can be pasted on the ra console to execute the query. 18. The interpreter supports handful of relational algebra operations as listed in the following table. The interpreter uses a special syntax and the expression should end with semi-colon symbol. Examples are presented in the next table. Focus on first two operations. 19. You can start your experiment with the given relational algebra expressions. 20. Continue playing with the interpreter till you are comfortable with the querying syntax and mechanism.

Solution: Operation

Syntax and Description

SELECT

\select_{CONDITION} EXPRESSION Selection over an expression

PROJECT

\project_{ATTRIBUTE_LIST} EXPRESSION Projection on selected attributes

RENAME

\rename_{NEW_ATTRIBUTE_NAME_LIST} EXPRESSION Rename all attributes of an expression

UNION

EXPRESSION_1 \union EXPRESSION_2 Union between two expressions

DIFFERENCE

EXPRESSION_1 \diff EXPRESSION_2 Difference between two expressions

INTERSECT

EXPRESSION_1 \intersect EXPRESSION_2 Intersection between two expressions

CROSS PRODUCT JOIN

EXPRESSION_1 \cross EXPRESSION_2 Cross-product between two expressions EXPRESSION_1 \join EXPRESSION_2

11

Natural join between two expressions; The interpreter literally follows the rules for cross product that column names must be unique from both sides. Don't forget to apply rename operation if after cross product two columns with same name may appear in the schema.

1. You can start your experiment with the given relational algebra expressions.

Information Need

Expression in Interpreter Syntax

Copies of ALASKA PHANTOM in the inventory

\select_{film_id=flm_id and title='ALASKA PHANTOM'} ( film \cross ( \rename_{inventory_id, flm_id, store_id, last_updat} inventory ) );

Category of the film ICE CROSSING

\project_{name} ( \select_{category_id=cat_id} ( \select_{film_id=flm_id and title='ICE CROSSING'} ( film \cross ( \rename_{flm_id, cat_id, last_updat} film_category ) ) \cross (\rename_{category_id,name,last_updt} category) ) );

2. Continue playing with the interpreter till you are comfortable with the querying syntax and mechanism.

Activity 2: 1. Write relational algebra expression for the following information needs. Write their corresponding expressions in the interpreter syntax as well. 2. Store all expressions in a plain text file. 3. Information needs: a. First and last name of actors who played in BOONDOCK BALLROOM. [3] 12

b. c. d. e.

Rental and return date for the movie ALASKA PHANTOM. [3] List of movie titles that were never rented. [4] Email addresses of customers who haven’t paid even a single penny yet. [5] Email addresses of customers who although rented a movie but didn’t pay anything. [5]

Solution: Submit a PDF document including the relational algebra expressions in the interpreter syntax to answer above-mentioned information needs as well as snapshot of their outcome when executed on the interpreter.

8) Stage v (verify) Home Activities: 

9)

Answer questions from the task. These questions assume Sakila database hasn’t changed.

Stage a2 (assess)

Lab Assignment and Viva voce Deliverable Submit a PDF document including the relational algebra expressions in the interpreter syntax to answer above-mentioned information needs as well as snapshot of their outcome when executed on the interpreter.

13

LAB # 04 Statement Purpose: Structured Query Language (SQL) was developed at IBM San Jose Research Laboratory as a part of System R project. It is a declarative query language for querying a relational database. It also includes features for defining the structure of the data, for inserting and modifying data in the database, and for specifying security constraints. It is relational complete (it supports all six core relational algebra operations). SQL commands can be classified into three groups out of which we are going to practice commands that query a database, called Data Manipulation Language (DML).

Activity Outcomes: After performing this lab students should be able to: 1. Design SQL queries to retrieve data using SELECT clause and various associated operators. 2. Translate relational algebra expressions to SQL queries. 3. Execute SQL queries over MySQL using MySQL Workbench.

Instructor Note: As pre-lab activity, attend theory class.

Tools/Software Requirement   

MySQL Community Server 5.6 MySQL Workbench 6.1 Sakila Database

10)

Stage J (Journey)

Introduction Structured Query Language (SQL) was developed at IBM San Jose Research Laboratory as a part of System R project. It is a declarative query language for querying a relational database. It also includes features for defining the structure of the data, for inserting and modifying data in the database, and for specifying security constraints. It is relational complete (it supports all six core relational algebra operations). SQL commands can be classified into three groups out of which we are going to practice commands that query a database, called Data Manipulation Language (DML).

14

Stage a1 (apply) Lab Activities: Activity 1: 1. This lab assumes that MySQL Community Server is running and Sakila database has been loaded using MySQL Workbench. 2. Open MySQL Workbench and open the default connection instance. 3. A new query window would open from where you can write and execute queries.

4. 5. 6. 7. 8.

You can save the query file and can also add comments using # symbol. On executing queries, results are displayed in the lower part of the screen. Error or success messages are displayed in action output pane at the bottom. Try running few SQL queries modeled during the lectures to get it going. Continue playing with the Workbench and SQL queries till you are comfortable with the querying mechanism and have learnt the shortcuts to execute queries.

Solution:

Activity 2: 1. Write SQL queries for the following information needs. You should execute your attempt and make necessary corrections if needed. 2. Information needs: a. Retrieve first names of all actors. [2] b. Retrieve all payments over and above $10 made during 14-22 August 2005. [3] c. Find all films of m...