INDEX

S.No. LAB PRACTICLES DATE REMARKS

S.No. LAB PRACTICLES DATE REMARKS
 DEENBANDHU CHHOTU RAM
UNIVERSITY OF SCIENCE AND
TECHNOLOGY MURTHAL (SONEPAT)

DATABASE MANAGEMENT SYSTEM LAB

(CSE383C)

SUBMITTED TO: SUBMITTED BY:

MR. PRADEEP MALIK PRANJAL DALAL
ASSISTANT PROF. 23001001084
CSE - DEPARTMENT CSE - ‘B’ 3rd YEAR
 Program no. – 1
Aim: Study of MySQL interface, with different types of installations, Accounts and
privileges in MySQL.
What is MySQL?
MySQL is a popular open-source relational database management system (RDBMS) developed by Oracle. It
is widely used for storing, managing, and retrieving data in structured form.

Key Points about MySQL:

• Relational Database: Organizes data into tables (rows and columns) with relationships between them.

• Open-Source: Free to use under the GNU GPL license, but also has enterprise editions.

• SQL Language: Uses Structured Query Language (SQL) to manage data (e.g., SELECT, INSERT,
UPDATE, DELETE).
• Cross-Platform: Runs on Windows, Linux, macOS, and can also be deployed on cloud platforms.

• Client-Server Model:

o Server: The MySQL server stores and processes data.

o Client: Applications or users connect to the server to run queries.

Features:

• High performance and scalability

• Data security with user accounts and privileges

• Supports large databases (up to terabytes of data)

• Compatible with many programming languages (PHP, Python, Java, C, etc.)

• Commonly used with web applications (e.g., WordPress, Facebook, YouTube originally used
MySQL).

How to Download MySQL?

Step 1: Go to the official website of MySQL and download the community server edition software. Here,
you will see the option to choose the Operating System, such as Windows.

Step 2: Next, there are two options available to download the setup. Choose the version number for the
MySQL community server, which you want. If you have good internet connectivity, then choose the
MySQL-installer-web-community. Otherwise, Choose the other one.
Step 3: Once the installer is downloaded, run it to start the MySQL installation.

Step 4: Now, we can see the installer community window, asking to choose a Setup type for our MySQL
products. Choose Custom and click Next to decide what products we want to actually install.

Step 5: In the next step, select MySQL Server, MySQL Workbench, MySQL Shell (all latest versions) to be
installed. We can also choose more products available as per necessity. Click Next.
Step 6: The installation process will now begin. However, path conflicts might arise if there exists a path
directory with the same name. After the installation is done, click Next.

Step 7: In this step, we will be asked to set Type and Networking of MySQL. Unless there is any particular
change we want to make, it is recommended to keep the settings as they are, and click Next.

Step 8: Then, we need to set the Authentication method to access MySQL root user. So, choose the strong
password encryption method (as it is recommended) and click Next.
Step 9: Set a password for the root account. This password must always be used to log into the root account
in every session. After setting password, click Next.

Step 10: In this step, MySQL Server Instance will be configured as a Windows Service. The default name
will be set as "MySQL80", which can be changed if needed. Click Next.

Step 11: As shown in the image below, the specified configuration steps will be applied on clicking Execute.
Once it is completed, click Finish. The next window will display the products on which the configuration is
applied. Click Next to finish the installation.

3. Accounts in MySQL

• MySQL uses user accounts to control who can connect and what they can do.

• Each account has:

o Username

o Host (from where they can connect, e.g., localhost, 192.168.1.%)

o Password

Default Account:

• root → Superuser with all privileges.

• Additional accounts can be created as needed.

Creating a new user:

4. Privileges in MySQL

Privileges define what actions a user can perform.

Common Privileges:

• ALL PRIVILEGES – Full access.

• SELECT – Read data.

• INSERT – Add data.

• UPDATE – Modify data.

 • DELETE – Remove data.

• CREATE – Create databases/tables.

• DROP – Delete databases/tables.

• GRANT OPTION – Allow giving privileges to others.

Granting Privileges:

Revoking Privileges:
 Program no. 2
Aim: Study of various Data Types and Data Objects in SQL.
1. Data Types in SQL
Data types define the kind of values a column in a table can hold. Different RDBMS (like MySQL, Oracle,
SQL Server) may have small differences, but the main categories are similar.

A. Numeric Data Types

Used to store numbers.

• INT / INTEGER – Whole numbers (e.g., 10, -25).

• SMALLINT / BIGINT – Smaller or larger ranges of integers.

• DECIMAL(p,s) / NUMERIC(p,s) – Fixed-point numbers with precision (p = total digits, s = digits

after decimal).
Example: DECIMAL (5,2) → 123.45.

• FLOAT / REAL / DOUBLE – Approximate floating-point numbers.

B. Character/String Data Types

Used to store text.

• CHAR(n) – Fixed-length string (always stores exactly n characters).

• VARCHAR(n) – Variable-length string (up to n characters).

• TEXT / CLOB (Character Large Object) – Large text data, e.g., articles, descriptions.

C. Date and Time Data Types

Used for temporal data.

• DATE – Stores only date (YYYY-MM-DD).

• TIME – Stores only time (HH:MM:SS).

• DATETIME / TIMESTAMP – Stores both date and time.

• YEAR – Stores year values.

D. Boolean Data Type

• BOOLEAN / BIT – Stores TRUE or FALSE values (some RDBMS use 1/0).

E. Binary Data Types

Used to store binary (non-text) data such as images or files.
 • BINARY / VARBINARY(n) – Fixed or variable-length binary data.

• BLOB (Binary Large Object) – Very large binary objects (images, multimedia).

2. Data Objects in SQL

Data objects are the main database structures where data is stored and managed. They are created and
managed using DDL (Data Definition Language) commands.

A. Tables
• The primary object to store data.

• Organized into rows and columns.

• Example:

B. Views
• A virtual table based on the result of a query.

• Does not store data physically, just shows data from underlying tables.

• Example:

C. Indexes
• Improve the speed of data retrieval.

• Similar to an index in a book.

• Example:
D. Schema
• Logical container that holds tables, views, and other objects.

• Helps in organizing database objects.

E. Sequences
• Generate numeric values in order (often used for primary keys).

• Example (Oracle):

F. Synonyms (in Oracle/SQL Server)

• Alternate names for tables or views.

• Useful for simplifying access to objects.