Experiment No.

02
PART A
(PART A: TO BE REFFERED BY STUDENTS)

A.1 Aim: To study about MySQL database and implement basic and DDL commands in SQL

A.2 Prerequisite: -

A.3 Outcome:
After successful completion of this experiment students will be able to
1. Learn how to start and use MySQL
2. Obtain help in MySQL
3. Load a database in MySQL
4. Familiarity with MySQL command line and workbench

A.4 Theory:

MySQL is one of the most popular Open Source SQL database management systems. The MySQL Web
site (http://www.mysql.com/) provides the latest information about MySQL database management
system. It is important to note that MySQL is an open source database and is continually under
development. Each version and sub-version may implement SQL syntax differently and changes are
being made constantly. There are also problems with upward compatibility between different versions.
For example some SQL operations that work in versions 3.0 and 4.0 do not work in version 5.0.
Furthermore, different variants of a version are released in response to bugs that have been found by
database developers who are using the latest versions in their work. If an SQL command does not work
as expected, please consult the MySQL web site for more information.

You must obtain a user ID and a password created by your database administrator in order to log on
to the MySQL RDBMS. To start MySQL, run the MySQL Command Line Client and enter the
password.

● Data Definition in SQL

Before creating any tables, MySQL requires you to create a database by executing the
CREATE DATABASE command.
∙ Create a database
CREATE DATABASE <database name>
∙ Delete a database
DROP DATABASE <database name>
∙ Select the database
USE <database name>
∙ List all databases
SHOW databases;
∙ Rename a database
ALTER DATABASE <database name> RENAME <new database name>
 CREATE, ALTER and DROP
table…………………………………….……relation
row……………………………………..…….tuple
column………………………………….……attribute

Here are the most common Data types:

char(size) Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
varchar(size) Variable-length character string. Max size is specified in parenthesis.
number(size)
Number value with a max number of column digits specified in parenthesis.
/int
date Date value
number(size, Number value with a maximum number of digits of "size" total, with a
d) maximum number of "d" digits to the right of the decimal.

Create Table:

CREATE TABLE table_name (

column_1 data_type PRIMARY KEY,

column_2 data_type NOT NULL,

column_3 data_type DEFAULT 0,

table_constraints

● Specify the name of the table that you want to create after the CREATE TABLE keywords.
● Use IF NOT EXISTS option to create a new table if it does not exist. Attempting to create a table that
already exists without using the IF NOT EXISTS option will result in an error.
● Specify the column list of the table. Each column has a name, data type, and the column constraint. SQLite
supports PRIMARY KEY, UNIQUE, NOT NULL, and CHECK column constraints.
● Specify the table constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK constraints.
● The primary key of a table is a column or a group of columns that uniquely identify each row in the tab

Constraints:
∙ Primary key🡪 A PRIMARY KEY constraint for a table enforces the table to accept unique data for a
specific column and this constraint create a unique index for accessing the table faster
∙ UNIQUE🡪 The UNIQUE constraint in Mysql does not allow to insert a duplicate value in a column.
∙ NOT NULL🡪 In Mysql NOT NULL constraint allows to specify that a column can not contain any
NULL value.
∙ FOREIGN KEY🡪 A FOREIGN KEY in mysql creates a link between two tables by one specific
column of both table. The specified column in one table must be a PRIMARY KEY and referred by the
column of another table known as FOREIGN KEY.
∙ CHECK🡪 The CHECK constraint determines whether the value is valid or not from a logical
 expression.
∙ DEFAULT🡪 While inserting data into a table, if no value is supplied to a column, then the column gets
the value set as DEFAULT

A.5 Tasks

Task1: Simple SQL command

1. SELECT VERSION(), CURRENT_DATE;

2. Select user();

Task 2: Create database: Programme name_Rollno Eg: BTech_I001 and use it

Creating the Table Structures:

Use the following SQL commands to create the table structures for the Theme Park database. Enter each one separately to
ensure that you have no errors. Successful table creation will prompt MySQL to say “Query OK”. It is useful to store each
correct table
structure in a script file, in case the entire database needs to be recreated again at a later
date. You can use a simple text editor such as notepad in order to do this. Save the file as
themepark.sql.
 Task 4: Solve below queries after creating tables:

At the MySQL prompt, create a database called Theme Park, and then select the database for use.
mysql> CREATE DATABASE THEMEPARK;
mysql> USE THEMEPARK;
Creating Tables:
ThemePark Table:
mysql> CREATE TABLE THEMEPARK (
PARK_CODE VARCHAR(10) PRIMARY KEY,
PARK_NAME VARCHAR(35) NOT NULL,
PARK_CITY VARCHAR(50) NOT NULL,
PARK_COUNTRY CHAR(2) NOT NULL);
As you create this structure, also notice that the NOT NULL constraint is used to ensure that the columns PARK_NAME,
PARK_CITY and PARK_COUNTRY does not accept nulls.
Employee Table:
CREATE TABLE EMPLOYEE (
EMP_NUM NUMERIC(4) PRIMARY KEY,
EMP_TITLE VARCHAR(4),
EMP_LNAME VARCHAR(15) NOT NULL,
EMP_FNAME VARCHAR(15) NOT NULL,
EMP_DOB DATE NOT NULL,
EMP_HIRE_DATE DATE,
EMP_AREA_CODE VARCHAR(4) NOT NULL,
EMP_PHONE VARCHAR(12) NOT NULL,
PARK_CODE VARCHAR(10),
INDEX (PARK_CODE),
CONSTRAINT FK_EMP_PARK FOREIGN KEY(PARK_CODE) REFERENCES
THEMEPARK(PARK_CODE));
Note that referential integrity has been enforced by specifying a constraint called FKP_EMP_PARK. In order to use foreign
key constraints in MySQL, notice that the PARK_CODE column is first indexed. This foreign key constraint definition ensures
that you cannot delete a Theme Park from the THEMEPARK table if at least one employee row references that Theme Park
and that you cannot have an invalid entry in the foreign key column.
Ticket Table:
CREATE TABLE TICKET (
TICKET_NO NUMERIC(10) PRIMARY KEY,
TICKET_PRICE NUMERIC(4,2) DEFAULT 00.00 NOT NULL,
TICKET_TYPE VARCHAR(10),
PARK_CODE VARCHAR(10),
INDEX (PARK_CODE),
CONSTRAINT FK_TICKET_PARK FOREIGN KEY(PARK_CODE)
REFERENCES THEMEPARK(PARK_CODE));
Notice that both PRIMARY and FOREIGN KEY constraints have been applied.
Attraction Table:
CREATE TABLE ATTRACTION (
ATTRACT_NO NUMERIC(10) PRIMARY KEY,
ATTRACT_NAME VARCHAR(35),
ATTRACT_AGE NUMERIC(3) DEFAULT 0 NOT NULL,
ATTRACT_CAPACITY NUMERIC(3) NOT NULL,
PARK_CODE VARCHAR(10),
INDEX (PARK_CODE),
CONSTRAINT FK_ATTRACT_PARK FOREIGN KEY(PARK_CODE)
REFERENCES THEMEPARK(PARK_CODE));
Hours Table:
CREATE TABLE HOURS (
EMP_NUM NUMERIC(4),
ATTRACT_NO NUMERIC(10),
HOURS_PER_ATTRACT NUMERIC(2) NOT NULL,
HOUR_RATE NUMERIC(4,2) NOT NULL,
DATE_WORKED DATE NOT NULL,
INDEX (EMP_NUM),
INDEX (ATTRACT_NO),
CONSTRAINT PK_HOURS PRIMARY KEY(EMP_NUM, ATTRACT_NO,
DATE_WORKED),
CONSTRAINT FK_HOURS_EMP FOREIGN KEY (EMP_NUM)
REFERENCES EMPLOYEE(EMP_NUM),
CONSTRAINT FK_HOURS_ATTRACT FOREIGN KEY (ATTRACT_NO)
REFERENCES ATTRACTION(ATTRACT_NO));
Notice that the HOURS table contains FOREIGN KEYS to both the ATTRACTION and the EMPLOYEE table.
Sales Table:
CREATE TABLE SALES (
TRANSACTION_NO NUMERIC PRIMARY KEY,
PARK_CODE VARCHAR(10),
SALE_DATE DATE NOT NULL,
INDEX (PARK_CODE),
CONSTRAINT FK_SALES_PARK FOREIGN KEY(PARK_CODE)
REFERENCES THEMEPARK(PARK_CODE));
SalesLine Table:
CREATE TABLE SALES_LINE (
TRANSACTION_NO NUMERIC,
LINE_NO NUMERIC(2,0) NOT NULL,
TICKET_NO NUMERIC(10) NOT NULL,
LINE_QTY NUMERIC(4) DEFAULT 0 NOT NULL,
LINE_PRICE NUMERIC(9,2) DEFAULT 0.00 NOT NULL,
INDEX (TRANSACTION_NO),
INDEX (TICKET_NO),
CONSTRAINT PK_SALES_LINE PRIMARY KEY
(TRANSACTION_NO,LINE_NO),
CONSTRAINT FK_SALES_LINE_SALES FOREIGN KEY
(TRANSACTION_NO) REFERENCES SALES(TRANSACTION_NO) ON DELETE
CASCADE,
CONSTRAINT FK_SALES_LINE_TICKET FOREIGN KEY (TICKET_NO)
REFERENCES TICKET(TICKET_NO));
Examine the constraint called FK_SALES_LINE_SALES. What is the purpose of ON DELETE CASCADE?
Now, execute the following parts:
1. Use the themepark database.
2. Show the tables that you created.
3. Describe all the tables that you created.

The DROP TABLE command permanently deletes a table (and thus its data) from the
database schema. When you write a script file to create a database schema, it is useful to
add DROP TABLE commands at the start of the file. If you need to amend the table
structures in any way, just one script can then be run to re-create all the database
structures. Primary and foreign key constraints control the order in which you drop the
tables – generally, you drop in the reverse order of creation. The DROP commands for the
Theme Park database are:
DROP TABLE SALES_LINE;
DROP TABLE SALES;
DROP TABLE HOURS;
DROP TABLE ATTRACTION;
DROP TABLE TICKET;
DROP TABLE EMPLOYEE;
DROP TABLE THEMEPARK;
 PART B
(PART B: TO BE COMPLETED BY STUDENTS)

(Students must submit the soft copy as per following segments within
two hours of the practical. The soft copy must be uploaded on the portal
provided by the instructor)

Roll No .E066 Name: Vaibhav Zambre

Division: E
Batch:2 Date of Experiment:20-12-2022
Date of Submission: Grade :

B.1 Tasks given in PART A to be completed here

(Students must write the answers of the task(s) given in the PART A )

TASK 1:
TASK 2:
B.2 Observations and Learning:
(Students must write the observations and learning based on their understanding built
about the subject matter and inferences drawn)
Thus we learned how to create table and insert value in table .
B.3 Conclusion:
Thus we can conclude that we learned about MySQL database and implement basic and DDL
commands in SQL.

(Students must write the conclusive statements as per the attainment of individual
outcomes listed above and learning/observation noted in section B.2)

B.4 Question of curiosity:

 What is the purpose of on delete cascade in SalesLine table?
  Use the ON DELETE CASCADE option to specify whether you want rows deleted in a
child table when corresponding rows are deleted in the parent table. If you do not specify
cascading deletes, the default behavior of the database server prevents you from deleting
data in a table if other tables reference it

***************