DBMS(DATABASE MANAGEMENT SYSTEM) PRACTICALS !!!

Practical No: 1

Title: Implementation ER Diagrams

2. Attributes

In DBMS, we have entities, and each entity contains some property about their behavior which is also called the attribute.

In relational databases, we have tables, and each column contains some entity that has some attributes, so all the entries for that column should strictly follow the attribute of the entity.

Entities define the characteristic property of the attributes.  Some of the attributes are as follows:  Simple Attributes.

Composite Attributes.
Single Valued Attributes.
Multi-valued Attributes.
Derived Attributes.
Key Attribute

3. Relationship:

A relational database collects different types of data sets that use tables, records, and columns.

It is used to crea

Different types of relational database tables:

One to One relationship,
One to many or many to one relationship,
Many to many relationships

Draw an ERD for the following Health-Care

Practical No: 2 Draw an ERD for the following Student Enrollment System

Practical No: 3 Draw an ERD for the following Customer Order System

Practical No: 4 Title: Implementation of DDL commands of SQL with suitable examples

DDL: DDL stand for Data Definition Language. It is used to define database structure or pattern. It is used to create Schema, Tables, Indexes etc in database. Using DDL statement, you can create sketch of database. Data Definition Language is used to store information of metadata like number of tables and schema, their names, indexes and columns of table created.

Create table ·
Alter table ·
Drop Table

1.Create a table EMPLOYEE with following schema: (Emp_no, E_name, E_address, E_mob_no, Dept_no, Job_id, Salary) Dept (Dept_no, Dept_name, Loc)

2. Add a new column; HIREDATE to the existing relation.

3. Change the datatype of JOB_ID from char to varchar2.

4. Change the name of column/field Emp_no to E_no.

5. Modify the column width of the job field of emp table 8

6. Remove the HIREDATE column from the table ALTERED TABLE

Practical No: 5 Title: Implementation of DML commands of SQL with suitable examples

Insert table ·
Update table ·
Delete Table DML:

DML stands for Data Manipulation Language. It is used for accessing and manipulating data in a database. It handles users request.

Insert 10 valid values in the table with the required constraints

a. Emp_no is PK
b. E_name, Dept_no, Salary is mandatory
c. E_mob_no should not have more than or less than 10 values in the table
d. Salary cannot be less than 1000.

2. Give an increment of Rs. 1000 to all the employees working in ‘IT’ department.

3. Remove the employee whose Emp_no is 8.

Practical No: 6 Title: Implementation of DQL Commands on the Database Tables DQL:

DQL stands for Data Query Language. It’s purpose is to retrive the data.

DQL STATEMENTS:

SELECT CLAUSE STATEMENT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

1. Display all the details of the employees.

SELECT *

FROM EMP;

2.List records in the emp table order by sal in ascending order.

3. Display only those employees whose deptno is D301.

4. Display deptno from the table employee avoiding the duplicate value

5. Display the employee name of the employees whose salary is more than 13000 and less than 16000. (Write the queries using AND clause and using BETWEEN Claus