A

Practical Report
On
“Workshop Practice Lab”

(ASDP- ……)

Submitted in fulfillment of the requirements for the award of degree of

Bachelor of Technology
In
Mechanical Engineering

By
Name: ________________________________

Class: ________________________________

Submitted to: __________________ HOD ( ME)________________

(Lab Incharge)
Department of Mechanical Engineering
MAYA DEVI UNIVERSITY
MONTH_________YEAR___________
 CERTIFICATE

We hereby certify that the work which is being presented in PRACTICAL

REPORT entitled
“Workshop Practice Lab”

in partial fulfillment of the requirement for the award of the Bachelor of

Technology in Mechanical Engineering and submitted the Department of
Mechanical Engineering of MAYA DEVI UNIVERSITY, Dehradun (U.K) is an
authentic record of our own work carried out during a period from ________ to
________ under the guidance of, practical incharge and
___________________ of Department of Mechanical Engineering, MAYA
DEVI UNIVERSITY, Dehradun.

The matter presented in this seminar has not been submitted by us for the award
of any other degree elsewhere.

Student Name & Roll no.___________________________________________

This is to certify that the above statement is correct to the best of our knowledge.
Date: …………………

Signature: …………………..
(LAB INCHARGE)
 DECLARATION

We hereby declare that the seminar entitled “Workshop Practice Lab”

submitted for the fulfillment of the degree of Bachelor of Technology in
Mechanical Engineering from Maya Devi University, Dehradun embodies
results of original work and studies carried out by us and the contents of the
seminar do no form the basis for the award of any other degree to us or to
anybody else from this or any other university/institute.

Signature:______________
Student Name:___________________
 ACKNOWLEDGEMENT

We would like to take this opportunity to express our deep sense of gratitude to
all who helped us directly or indirectly during this seminar work.

First of all, we would like to express our deepest gratitude to our seminar
supervisor________________________ (practical incharge) for his enormous
help and advice and for providing inspiration which cannot be expressed with
words. We would have not accomplished this seminar without his patient care,
understanding and encouragement. His advice, encouragement and critics are
source of innovative ideas, inspiration and causes behind the successful
completion of this seminar work. The confidence shown on us by him was the
biggest source of inspiration for us.

We are deeply thankful to MDU Management for providing facilities for

accomplishment of this PRACTICAL.

Date: …………………

Signature:______________
Student Name:___________________
 INDEX
S.No. Experiments Remark
To manufacture the fitting job as per given
1.
drawing.
2. To make the double V- butt joint with arc welding.
To manufacture the job on centre lathe machine as
3.
per given drawing .
To manufacture the half lap carpentry job as per
4. given drawing.
To make the box by sheet metal operation.
5.
To study basics of manufacturing science related to tool
6. and operations of foundary shop.

7. Study of tools and operations of Smithy shop

Minor Project:
8. To make a minor project by the students in batches
comprising the operations performed in different shops.
9.

10.

11.

12.

13.

14.

15.

5|Page
 MAYA DEVI UNIVERSITY, DEHRADUN
LAB MANUAL
Course Name : EXPERIMENT NO. 1

Branch: ME Semester : I
Faculty : Mr.

AIM- To manufacture the fitting job as per given drawing.

RAW MATERIAL- MS Flat- 50mm × 50mm × 5mm
.
TOOLS- Work bench, Bench vice , Hand hacksaw, Surface plate, Scriber, try
square, Flat basted file (rough & smooth), Chalk, Center punch, Hand hammer.
PROCEDURE-
1. Remove all burrs and scales from work pieces with rough file.
2. Hold the work piece firmly on Vice for filling one reference surface.
3. File the surface clean and flat with blasted file and check the flatness with
try square.
4. Coat the cleaned surface with wet chalk and allow it to dry.
5. Layout the desired dimensions using surface plat, scale and gauge.
6. Mark the line drawn with scorner, centre punch and hand hammer.
7. Hold the job again on the vice and cut away unwanted material with a hand
hacksaw as per marking.
8. Deburr the job completely.
9. File the cut sides with smooth file until the job is finished to required
dimensions and smoothness.
10. Put your roll no. with punch numbers and submit.

PRECAUTIONS-
Ensure that job is securely held in the Vice, otherwise it may spill away and injure
you.
Hand hacksaw should be straight during cutting of V shape.
RESULT-
The fitting job has successfully manufactured.
6|Page
 Tools used in Fitting Workshop :
1. Holding Tools
2. Marking Tools
3. Measuring Tools
4. Cutting Tools
5. Finishing Tools
6. Striking Tool

Holding Tools used in Engineering Workshop:

Holding tools are also known as Work Holding devices in the Fitting workshop.
They are used to hold all types of components between jaws(Fixed Jaw and
Movable Jaw).
The Various Work holding devices are
 Workbench
 Bench Vice
 Pipe Vice
 Hand Vice
 Toolmakers Vice

They are 3 types of Marking tools used in the fitting workshop and are as
follows.
 Scriber
 Dot Punch
 Center Punch

Some of the Measuring Tools used in Fitting Workshop are as follows.

 Steel rule & Measuring Tape
 Surface Plate
 Divider
 Try square
 Inside Calliper
 Odd leg calliper
 Outside Calliper
 Vernier Height Guage
 Micrometre
 Vernier Calliper
7|Page
 Cutting Tools used in Fitting Workshop:
Cutting Tools are used to cut the given specimen(plate) w.r.t.the given
dimensions. The material of the given specimen in the Fitting workshop is Mild
Steel.
The Cutting Tools used in Engineering Workshop are
 Hacksaw (Cutting Tool)
 Files (Finishing Tool): Though it is a finishing tool, it comes under,
 cutting tools only.

Finishing Tools used in Fitting Workshop:

 Flat file
 Square file
 Triangular file
 Round file
 Half-round file
 Swiss or Needle file

Operations used in Fitting Workshop:

A sequence of operations used in the Fitting Workshop is as follows.
 Filing (Rust from the workpiece)
 Measuring
 Marking
 Punching
 Sawing
 Filing (removing the unwanted portion)

Safe Work Practices in Fitting Workshop:

 Wear leather shoes and not sandals.
 Do not touch any electrical appliances.
 Don’t wear loose clothes in the workshop.
 Clean the unwanted material from the workbench before starting the
 operation.

Power Tools used in Workshop:

The tools which are driven by means of Power (Electric current) are called
Power tools. In this article, I will be listing out 4 Power tools used in the Fitting
8|Page
 workshop.
 Power Hacksaw
 Circular Saw
 Drilling Machine
 Grinding Machine

The detailed explanation of the above power tools is as follows.

Power Hacksaw:
 A power hacksaw is a type of hacksaw that is powered by its own electric
motor (also known as an electric hacksaw).
 Most power hacksaws are stationary machines but some portable models
also exist.
 A Power Hacksaw is used to cut the metal at required shape and size by
means of a hacksaw which is driven by means of Electric motor.
 The construction and working of Power Hacksaw were presented below.
 The forward stroke is the cutting stroke and the backward stroke is the
idle stroke. In the backward stroke, the chips are removed from the
workpiece.
 The workpiece is fixed between the two ends of the machine vice.

Components of Power Hacksaw:

The Power Hacksaw consists of the following parts.
1. A Vertical column with Gears and Power buttons.
2. Hacksaw
3. Coolant
4. Base
5. Machine Vice
6. Foot/Emergency Switch

9|Page
 MAYA DEVI UNIVERSITY, DEHRADUN
LAB MANUAL
Course Name : EXPERIMENT NO. 2
Workshop Practice Lab

Branch: ME Semester : I
Faculty : Mr.

AIM- To make the double V- butt joint with arc welding.

RAW MATERIAL- Mild steel (MS) flat.

TOOLS AND EQUIPMENT- Step down transformer, Electrode, Electrode

Holder,Cables, Electrode, Table, Eye shield, Gloves, Chipping Hammer, Tong and Wire
brush,File, Bench vice, Scribes, Punch , Hammer.

THEORY- Welding Process, Different Joints, Arc Welding principle, and Techniques in
Wilding.

PROCEDURE-
1. Cut the required dimension work piece from lot and remove the all burrs.
2. Hold the work piece in Vice and make its surface smooth.
3. Coat the surface of job with chalk and allow it to dry.
4. layout the desired dimension, mark line & spot with scriber and punch.
5. Hold the job again on vice and make double V Butt joint.
6. Place the work piece at welding table in desired Butt joint position.
7. Set the voltage and current for work pieces and weld them.
8. Switch off the welding machine and allow to cool them.
9. Hold the welded pieces with tong and chip off the slag from weld portion, using
chipping hammer.
10. Cool down the job with cold water, put the Roll No. with punch Numbers.

10 | P a g e
PRECAUTIONS-
1. Ensure that the pieces are placed in desired position.
2. Use proper eye shield to protect eyes
3. Use gloves to protect hands.
4. Maintain proper distance from work pieces.

RESULT-
The job has successfully made.

11 | P a g e
 MAYA DEVI UNIVERSITY, DEHRADUN
LAB MANUAL
Course Name : EXPERIMENT NO. 3
Manufacturing Practices / Workshop Lab

Faculty : Mr. Branch: ME Semester : I

AIM : - To manufacture the job on centre lathe machine as per given drawing .

MATERIAL : - Mild steel rod 35 mm .

TOOLS : - Lathe machine and tools for different operation .

PRECAUTIONS : -
1. Ensure that job is firmly held in the jaws , otherwise it may slip away and injured you .
2. Tool must be held in tool holder tightly .
3. Tool must be sharp .
4. Run the machine with proper speed .
5. Maintain properly work piece .

PROCEDURE :-
1.Cut the required dimentions from the raw material .
2.Load the job on machine from one end provide filling operation on it .
3.Provide turning operation on dia 30x25 and knurling by knurling tool .
4.Provide proper taper , turning diameter 30 to dia 20 x15 mm .
5.Unload the job and reload on machine from the machine end . 6.Facing the end to
maintain length .
7.Turn dia 30x25 mm jobs .
8. Turn dia 20 x35 mm .
9 , Champher the job 2 mm x 45 mm from end .
10. Unload job from machine , mark roll no . and submit it .

12 | P a g e
RESULT : The turning job as per given drawing is successfully manufactured .

OBSERVATIONS :

Job No. Length Width Thickness Remarks

1
2
3
4

13 | P a g e
 MAYA DEVI UNIVERSITY, DEHRADUN
LAB MANUAL
Course Name : EXPERIMENT NO. 4
Workshop Practice Lab

Faculty : Mr. Branch: ME Semester : I

AIM: - To manufacture the half lap carpentry job as per given drawing.

RAW MATERIAL: - Timber block of deodar wood measuring 250mm × 45mm

×20mm.

TOOLS AND EQUIPMENT: - Work bench carpentry Vice Jack planer, Cross cut saw,
Tennon Saw, Firmers Chisel, Mallet, Try square, Marking Gauge, Pencil. Scale, Wood
Working File, Sand paper etc.

THEORY:- Defects in wood, carpentry operations.

PROCEDURE:-
1. Cut the 255mm × 50mm × 25mm.Piece from lot.
2. Remove all burrs and make it smooth through planer.
3. Now mark over piece through, Gauge & Pencil and convert it through planer
250mm × 45mm × 20 mm. pieces.
4. Through saw cut the piece in two half’s.
5. Place one half into vice and mark over 45mm length that booth one side.
6. Now cut the piece from 45mm length upto 10mm depth.
7. Place the same piece in vertical position and Remove the 45mm × 45mm × 10mm
piece.
8. Take second piece horizontally in vice.

14 | P a g e
Carpentry is a skilled trade and a craft in which the primary work
performed is the cutting, shaping and installation of building materials
during the construction of buildings, ships, timber bridges, concrete
formwork, etc. Carpenters traditionally worked with natural wood and
did the rougher work such as framing, but today many other materials
are also used and sometimes the finer trades of cabinetmaking and
furniture building are considered carpentry.
Joint, in carpentry, junction of two or more members of a framed
structure. Joinery, or the making of wooden joints, is one of the
principal functions of the carpenter and cabinetmaker. Wood, being a
natural material, is not uniform in quality, and moisture, present in the
tree during growth, is uneven in cut wood.

timber joints

15 | P a g e
 Basic timber joints used in carpentry.

The object of the joint is to fix two members together so that the joint has the
greatest possible mechanical strength and is as unobtrusive as possible. Though
there are many joints in use, they fall into a few basic groups, many being
variations and elaborations on fundamentally simple ideas. Practically all are based
on handwork, and with few exceptions most machine-made joints follow the
traditional patterns; most joints rely to a considerable extent on a combination of
mechanical fit and glue for their strength. Common types of joints include the
dovetail, used for joining two flat members together at right angles, as in the sides
of a drawer; the dowelled joint, in which dowelling is employed to impart
mechanical strength; and the mortise and tenon, used to join a horizontal member
with the vertical member of a frame.

Carpentry tool list-

Essential Carpentry Tools List

What follows is a list of what we consider to be the essential tools for carpentry,
handily divided into sections based on hand tools, power tools, accessories,
storage, and safety equipment. While not every one of these tools will be required
for every job, this list will provide a comprehensive guide to tools that have no
replacement in their functionality.

Because carpentry is such a broad field of study, with many possible projects and
outcomes depending on your skill level, choosing only the tools that you’re most in
need of for a project you have in mind is the best way to go. That way, you can
continue to collect tools that are appropriate both to your desires, and your carpentry
skill level. Before you know it, you’ll have a fine set of
tools and the know-how to put them to good use!

Hand Tools

1.Claw Hammer

One of the most basic tools of the carpentry trade, a claw hammer will allow you
to drive nails with one end and remove them with the other. Look for one that has
a comfortable but secure grip and is not too heavy to hold and swing.

16 | P a g e
\

2. Tape Measure
Plastic measuring tapes are cheap and simple but tend to stretch and lose
accuracy over time. Go for a metal measuring tape, preferably a retractable
model that’s good for measuring larger pieces of uncut lumber.

3. Measuring Squares
Any tool also known as a “carpenter’s square” must be essential, right? That’s
absolutely true for measuring squares, as they’ll allow you to identify, measure, and
mark right angles during your projects.

4. Chisels
Great for hand-carving or lathe turning, chisels were once the primary tool for
carving out joints in Japanese carpentry. Often sold in sets, you’ll need a variety of
chisels to perform any meaningful or detailed work.

17 | P a g e
5. Mallets
A wooden mallet is complementary to a chisel set, allowing you to precisely
drive the sharp heads into your workpiece. Rubber mallets are useful in putting
together joints and will not damage wood when tapped with light to medium
force.

6. Handsaw
The workhorse of hand tool carpentry, handsaws are used to rip and crosscut
boards to size. Look for blades that are taper ground, allowing better clearance
around the teeth and a smoother, more efficient cutting motion.

7. Backsaw
Designed with thin blades and finely set teeth, backsaws are the detail-oriented
companion to the handsaw. Supported by a heavy strip of brass or steel over the
top of the blade, they excel at detail work and can be used to cut mortise and tenon

18 | P a g e
or dovetail joints.

8. Curve Cutting Saw

Bow Saws, scroll saws, coping saws, and compass saws all fall into this category,
often reserved for decorative work. Until you have a project that calls for curved
cuts, you won’t need any of these saws. When you’re ready to tackle more
intricate designs, however, there is no substitute for these tools.

9. Planes
Used to smooth wooden surfaces while gradually reducing their thickness, planes
are a complex and multi-part hand tool with something of a cult following in the
classical carpentry community. A good bench plane will get you through 90% of
projects, while rabbet and block planes are reserved for more specialized angled
work.

10. Sharpening Stone

Keeping your chisels, gouges, and planes sharp is essential to their safe and
appropriate functioning. Oil Stones are the most popular choice in the United
States, while a two-part water stone is more common for Japanese hand tools.

19 | P a g e
11. Grinder
While not technically a hand tool, a motorized bench grinder is used almost
solely in the refinishing of hand tools like chisels gouges. Chipped or worn
blades can often be refurbished through the combination of a few times through
a grinder and finishing with a sharpening stone.

12. Hand Drill

Though power drills have become a much more popular option, hand drills offer
quiet and convenience instead of speed and intensity. With the proper set of drill
bits, a hand drill can be every bit as effective as a power drill in preparing high-
quality workpieces.

13. Screwdrivers
First and foremost, when using a screwdriver, the tip must fit the size of the screw
slot in question. Therefore, you’ll need screwdrivers in a range of sizes to
accommodate whatever size of screw you come up against in your projects.

20 | P a g e
14. Clamps
You can rarely have too many clamps in a woodworking shop, and a wide
selection of bar clamps, miter clamps, and C-clamps will always hold you in good
stead when you need an “extra hand”. Good clamps can be expensive, so
consider renting extra sets for especially large projects.

Power Tools

15. Power Drill

The most widely sold and often used power tool on the market, a power drill is
useful not only for carpentry projects but also for general household repairs and
maintenance. Cordless power drills offer excellent versatility and ease of use as
long as you can remember to put their batteries back on the charger when you’re
done.

16. Jigsaw

Also known as a saber saw, these versatile and inexpensive power tools do a fine
job of cutting boards to a rough size but can’t be relied on for straight cuts. Instead,
use them for curved cuts and quick projects where precision is not key.

21 | P a g e
17. Circular Saw
A rough and ready workhorse for carpentry in a woodshop or on a job site, the
circular saw has become a staple of construction contractors everywhere.
Thanks to advances in materials technology, you can now find lightweight and
exceptionally powerful circular saws available for entirely reasonable prices.
18. Router

Used for cutting grooves, curved figures, and joints, routers can easily replace
a whole family of hand planes. Fixed routers are suspended above the
workpiece and lowered into the cut as one unit, while plunge routers deploy
only the cutting edge via spring-loaded columns.
19. Sander

Great for taking much of the time and effort out of finishing a piece of wood,
even the finest of powered sanders will only get you partway there. Once
you’ve progressed to a “finishing sander”, there will still be a short period of
hand-sanding necessary to achieve a truly fine finish.
20. Biscuit Joiner
A particularly specialized saw and joiner combo, the biscuit joiner cuts a small
section of wood out of the edge of a board to which glue and a “biscuit” of
compressed wood are added. As the glue and wood expand to fill the small
hole, a strong and durable joint is created.

21. Table Saw

The most central aspect of any woodworking shop, a table saw will be your
most substantial investment for home woodworking. Traditional cabinet saws
are large, heavy, and extremely accurate and efficient; more modern
portable models allow you to take much of this utility with you to a job site.

22. Radial Arm Saw

Sometimes known as a chop saw, the radial arm saw is a quick and ready tool
for making rips, crosscuts, bevels, or miter cuts on a job site or in your home
workshop. While lacking some of the precision of a table saw, they are an
indispensable tool for increasing efficiency in trim and molding work.
23. Band Saw

A continuous loop of metal driven between two wheels, the band saw
always cuts downward – thereby eliminating the possibility of kickback on a
workpiece. It performs many of the same functions of a radial arm saw (if
somewhat less accurately) and can be used for curved cuts as well.
24. Planer
After purchasing a table or band saw for your home workshop,the planer might
be the next most essential power tool. The best planers can be precisely
adjusted to evenly smooth surfaces on all four sides of a workpiece, saving
much time and effort when compared to using a hand plane.
25. Lathe

An adjunct art form to carpentry’s many straight lines and right angles,
incorporating a lathe into your workshop allows you to enter the world of
turned pieces such as bowls, cups, and decorative table legs. You’ll need
specialized turning tools to take full advantage of a lathe in your home
workshop.
26. Universal Machine
When space is limited and you may not be able to fit many power tools into
one woodshop, the universal machine is your answer. Combining the functions
of a table saw, jointer, thickness planer, spindle shaper, and mortiser, it’s an
all-in-one solution ideally suited to cramped workspaces.

Accessories
27. Carpenter’s Pencil
Flat instead of round, the carpenter’s pencil is more versatile and accurate than
a standard pencil in measuring precise markings for woodworking cuts. Next
Level Carpentry covers how to sharpen a carpenter’s pencil here:

28. Extension Cords

If you own any power tools that are not cordless, chances are you will one
day need an extension cord to get the most use out of them. Even extra-long
extension cords are now available at a reasonable price, making them an
easy addition to your carpentry toolkit.
29. Miter Box

A simple wooden jig used for cutting miter joints and square ends, you can also
find them available in plastic or metal. Two raised sides and a groove in the
middle allow you to slot your saw into predetermined angles to guide your cuts.
30. Bench Hook
Used for crosscutting short lengths of wood with a backsaw, the bench hook is
primarily helpful as a safety measure. Readymade bench hooks are available,
or

you can make your own by attaching two blocks to a flat board.

31. Woodworking Vise

Attached to one end of a solidly built woodworking bench, a vise is incredibly

helpful as an “extra hand” on any workpiece. European-style vises have
wooden jaws, allowing a workpiece to be gripped without leaving any marks.
32. Dust Collection System

Whether you choose a shop vac for its portability and versatility or a
dedicated dust collection system attached to your table saw, you’ll need
something to make sure your work surfaces are free of dust. Aside from
increasing accuracy and precision in your work, this will also keep your floors
safer for walking on.
 MAYA DEVI UNIVERSITY, DEHRADUN
LAB MANUAL
Course Name : EXPERIMENT NO. 5
Workshop Practice Lab

Branch: ME Semester : I
Faculty : Mr.

AIM:- To make the box by sheet metal operation.

RAW MATERIAL:- 200mm × 120mm, Aluminum sheet (27 Gauge).

TOOLS:- Cutter / Snip, Scriber, Mallet, Stake / Anvil.

THEORY:- Different kind of sheet metal operation.

PROCEDURE:-
1. Take the 27 gauge Aluminum sheet and mark 200mm × 120mm 2 rectangle with
scriber and scale.
2. Cut the 200mm × 120mm 2 rectangle piece with cutter from sheet.
3. Take 20mm dimension with scale and mark it from all side with scriber.
4. Place the sheet on anvil in such way so that 20mm should be outside from
platform.
5. Now with mallet, strike over outer part of sheet and fold it.
6. Fold the 20mm from all side of piece and box will made.

RESULT:- The box has successful made.

 What is Manufacturing Process?
Manufacturing process is basically a complex activity, concerned with people
who've a broad number of disciplines and expertise and a wide range of machinery,
tools, and equipment with numerous levels of automation, such as computers,
robots, and other equipment. Manufacturing pursuits must be receptive to several
needs and developments.
Beside above, all the future technicians must understand the basic needs of
workshop routines in terms of man, equipment, material, methods, revenue and other
infrastructure conveniences needed to be placed properly for maximum shop or plant
layouts and other support solutions effectively regulated or positioned in the field or
industry within a properly planned manufacturing firm.

Meaning
The complete knowledge of fundamental workshop technology and manufacturing
processes is highly troublesome for anybody to claim competence over it. It deals
with numerous aspects of workshops procedures also for providing the basic
working awareness of the various engineering materials, tools, accessories,
manufacturing processes, basic concepts of machine instruments, production
criteria’s, traits and uses of numerous testing instruments and calibrating or
inspecting units for checking materials or products designed in various production
shops in a commercial environment. It also explains and illustrates the use of several
hand tools (calibrating, marking, forming and supporting gear etc.), tools, machinery
and diverse methods of production that facilitate forming or shaping the existing raw
materials into appropriate usable forms. Below are some of the manufacturing
processes that are worth reading.

Types of Manufacturing Processes

Following are the 4 types of manufacturing processes

Machining
Tools used for machining are immobile power-driven units used to form or shape
solid materials, specifically metals. The forming is done by removing extra materials
from a work-piece. Machine tools make up the foundation of advanced industry and
are utilized either indirectly or directly in the manufacturing of tool parts.

They are categorized under three main categories:

1. Traditional Chip-making tools.

2. Presses.
3. Modern machine tools.

Traditional chip-making tools form the work-piece by trimming away the unwanted
part accessible as chips. Presses implement a several shaping processes, which
includes shearing, pressing, or elongating. Non-traditional machine tools implement
light, electric powered, chemical, and sonic power; superheated gas; and high-
energy compound beams to form the exotic supplies and materials that have been
created to meet the requirements of modern technology.

Joining
Every joining approach has particular design needs, while certain joint needs may
propose a particular joining approach. Design for assembly, and fastener selection
apply their own specifications.
Bolting is a standard fastening method, for instance, but welding may cut down the
weight of assemblies. Naturally, joints intended for the two approaches would differ
tremendously.
However, all joint patterns must consider features such as load factors, assembly
effectiveness, operating surroundings, overhaul and upkeep, and the materials
chosen.
Welding is generally a cost-effective approach to fabricate. It doesn't require
overlapping materials, and so it removes excess weight brought on by other
fastening methods. Fasteners don't have to be purchased and stored in stock.
Welding also can minimize costs related to extra parts, for example angles mounted
between parts.

Forming
Metal forming is the approach of creating the metallic components by deforming the
metal but not by removing, cutting, shredding or breaking any part. Bending,
spinning, drawing, and stretching are a few important metal forming process in
manufacturing. The metal press such as die and punching tools are implemented for
this manufacturing process.
Advantages: – Same equipment can be utilized for manufacturing various components
by simply changing the dies.
Disadvantages: – High apparatus and tooling expenses. – Heat treatment must be
applied afterwards.

Casting
Casting is a manufacturing process in which a solid is dissolved into a liquid, heated
to appropriate temperature (sometimes processed to change its chemical formula),
and is then added into a mold or cavity. Thus, in just one step, complex or simple
shapes can be crafted from any kind of metal that has the capability to be melted.
The end product can have practically any arrangement the designer wants.
Furthermore, the reluctance to working challenges can be improved, directional
attributes can be managed, and a pleasing look can be developed.
CNC machine

CNC machining is a term commonly used in manufacturing and

industrial applications.

The term CNC stands for 'computer numerical control', and the CNC
machining

Definition - it is a subtractive manufacturing process which typically

employs computerized controls and machine tools to remove layers of
material from a stock piece—known as the blank or workpiece—and
produces a custom- designed part.

This process is suitable for a wide range of materials, including

metals, plastics, wood, glass, foam, and composites, and finds

application in a variety of industries, such as large CNC machining and

CNC machining aerospace parts.

CNC machine - It represents the actual programmable machine that is

capable of autonomously performing the operations of CNC machining.

Subtractive manufacturing processes, such as CNC machining, are often

presented in contrast to additive manufacturing processes, such as 3D

printing, or formative manufacturing processes, such as liquid injection

molding. The automated nature of CNC machining enables the
production of high precision and high accuracy, simple parts and the
cost-effectiveness when fulfilling one-off and medium-volume
production runs.
 CNC Machining Process
CNC machining is a manufacturing process which utilizes
computerized controls to operate and manipulate machine and cutting
tools to shape stock material—e.g., metal, plastic, wood, foam,
composite, etc.—into custom parts and designs. While the CNC
machining process offers various capabilities and operations, the
fundamental principles of the process remain largely the same
throughout all of them. The basic CNC machining process includes the
following stages:

 Designing the CAD model

 Converting the CAD file to a CNC program
 Preparing the CNC machine
 Executing the machining operation

CAD Model Design

The CNC machining process begins with the creation of a 2D vector or

3D solid part CAD design either in-house or by a CAD/CAM design
service
company. Computer-aided design (CAD) software allows designers and
manufacturers to produce a model or rendering of their parts and products
along with the necessary technical specifications, such as dimensions and
geometries, for producing the part or product.

Once the CAD design is completed, the designer exports it to a CNC-

compatible file format.

CAD File Conversion

The formatted CAD design file runs through a program, typically

computer- aided manufacturing (CAM) software, to extract the part
geometry and generates the digital programming code which will
control the CNC machine and manipulate the tooling to produce the
custom-designed part.

CNC machines used several programming languages, including G-code

and M- code. The most well-known of the CNC programming languages,
general or geometric code, referred to as G-code.

Once the CNC program is generated, the operator loads it to the CNC
machine. Types of CNC Machining Operations
CNC machining is a manufacturing process suitable for a wide variety of
industries, including automotive, aerospace, construction, and agriculture,
and able to produce a range of products, such as automobile frames, surgical
equipment, airplane engines, and hand and garden tools. The process
encompasses several different computer-controlled machining operations—
including mechanical, chemical, electrical, and thermal processes—
which remove the necessary material from the workpiece to produce a
custom- designed part or product. While chemical, electrical, and thermal
machining processes are covered in a later section, this section explores
some of the most common mechanical CNC machining operations
including:

 Drilling
 Milling
 Turning

Other CNC Machining Operations

Other mechanical CNC machining operations include:
 Broaching
 Sawing
 Grinding
 Honing
 Lapping

Objective type questions related to workshop manufacturing

Industrial safety
1. What are the important provisions of Indian factories act with regard to safety ?
2. What are the general safety features or precautions in the workshop ?

Metals and their properties .

1. Name some metals used in general work of manufacturing . 2. What are the
principle mechanical properties of material ?
3. Differentiate the ferrous and non ferrous metals . 4. What is gray cast iron , and its
importance in the industries ? 5. What is plain carbon steel and its utilization ?

6. Why alloy steel is used in industries ? 7. What is the composition of High speed
steel ?
8. Give some uses of High carbon steel and its percentage of carbon . 9. Name some
elements which are used to make alloy steel .

Heat treatment
1. What is meant by heat treatment ?
2. What is isothermal transformation ?
3. What are the various factor to influence the hardening process of a metal ?
4. How do you classify the heat treatment processes ?
5. What is hardening process ?
6. What is casehardening ? And why is it done ?
7. What is annealing process ?
8. What is the difference between gas nitriding and cyaniding ?
9. How can we test the hardness of a metal ?
10. What is tempering of steel ?

Cutting tools
1. Differentiate the single point and multipoint cutting tools .
2. What is the difference between HCS and HSS tools .
3. What is machinability ?
4. Define the role of cutting fluids in manufacturing .
5. What are the characteristics of an ideal cutting tool ?
6. Classify the types of tool material ,
7. Name some element to make hard the cutting tools .
8. What is difference between orthogonal and oblique cutting ?
9. Give two examples of each cutting process .
10. What are the conditions which influence the metal cutting ?

Lathe machine
1. What is the working principle of lathe machine ?
2. What is the difference between turret and capstan lathe ?
3. Define cutting speed , feed and depth of cut .
4. What is the translating gear and its importance ?
5. Name the operation performed on lathe machine . Name the main parts of lathe
machine .
6 . Name the main parts of lathe machine .
7. What are the lathe accessories ?
8. What is lead screw and its importance ?

9. What is eccentric turning ?

10. What are the main tools of lathe machine ?
Shaper machine

1. Draw the neat sketch of shaper machine .

2. How can you classify the shaper machine ?
3. What is the role of shaper machine ?

Milling machine

1. What is milling process ?

2. What are the main attachments of milling machine ?
3. What are the main parts of milling machine ?

Grinding machine

1. What is the object of grinding ?

2. How many types are of grinding machines ?
3. Define the centre less grinding process .
4. What is abrasive material ?
5. What is creep grinding ?
Welding

1. Classify the welding according their state .

2. Which gases are used in gas welding ?
3. Define TIG and MIG welding .
4 .Compare the AC and DC arc welding .
5. What is the difference between soldering and brazing ?
6. What is the application of spot welding ?
7. Draw a neat sketch of working principle of arc welding .
8. Why current is not effective when we touch the iron / mild steel job ?

9. What are the major welding defects ?

10. Name some welding joints .
11. What is the difference in bare electrode and coated electrode and why ?

Fitting
1. What is fastening ?
2. How many types are fastening ?
3. What is the difference between BSW and metric thread ?

4. What is the difference between right hand and left hand thread ?
5. Define lead and pitch .
6. Name the different type of threads ?
7. What is riveting ?
8. Name the riveted joint .
9. What are the main operation during fitting works ?
10. Name the general instruments used in fitting .
11. What is the process of marking ?

Carpentry

1 . What are the major wood working tools .

2 . What is the difference in timber and wood ?
3 . What are the differences between hard and soft wood ?
4 . What is " setting " of saw teeth ?
5 . Name some different type of woods .
6. What is seasoning ?
7. What are the major defects in timber ?
8. What are the auxiliary materials used in carpentry ?
9. How many joints can be produced in wood working ?
10. What is router ? Name operation performed by router .

Sheet metal work

1. What is sheet metal work ?
2 . Write down any four sheet metal characteristics .
3. What is meant by clearance ?
4. What is stretching ?
5. Define the term spring back
6 .How force exerted on the form block is calculated ?
7 .What are the formability test method ?
8 .What is super plasticity of metals ?
9 .What is metal spinning process ?
10. What is sheet metal ?
11. What is the role of shearing machine ?
12. Name few instruments used in sheet metal work .
Measuring instruments

1 . Explain the different parts of a combination set and their uses .

2. What is the difference between vernier caliper and micrometer ?
3. What is the use of try square ?
4. What is the least count of vernier caliper and micrometer ?
5. What is role of bore dial indicator ?
6. How slipgauge is used in the measuring ?

Ref . Books
1. Manufacturing technology by R.K Rajput ( Laxmi publications )
2. Workshop technology by B.S Raghuvanshi ( Dhanpat Rai & co . )
 A
Practical Report
On

“Database Management Systems (DBMS) Practical Lab”

(ASDP- ……)

Submitted in fulfillment of the requirements for the award of degree of

Master
In

Computer Applications

By
Name: ________________________________

Class: ________________________________

Submitted to: __________________ HOD (CS)________________

(Lab Incharge)
Department of School of Computer application
MAYA DEVI UNIVERSITY
MONTH_________YEAR___________
 CERTIFICATE

We hereby certify that the work which is being presented in PRACTICAL

REPORT entitled

“Database Management Systems (DBMS) Practical Lab”

in partial fulfillment of the requirement for the award of the Master in
Computer Application in and submitted to the Department of School of
Computer Application of MAYA DEVI UNIVERSITY, Dehradun (U.K) is an
authentic record of our own work carried out during a period from ________ to
________ under the guidance of, practical incharge and
___________________ of Department of School of Computer Science and
Application , MAYA DEVI UNIVERSITY, Dehradun.

The matter presented in this seminar has not been submitted by us for the award
of any other degree elsewhere.

Student Name & Roll no.___________________________________________

This is to certify that the above statement is correct to the best of our knowledge.
Date: …………………

Signature: …………………..
(LAB INCHARGE)
 DECLARATION

We hereby declare that the seminar entitled “Database Management Systems

(DBMS) Practical Lab”submitted for the fulfillment of the degree of Master

in Computer Application in School of Computer Science & Application from
Maya Devi University, Dehradun embodies results of original work and studies
carried out by us and the contents of the seminar do no form the basis for the
award of any other degree to us or to anybody else from this or any other
university/institute.

Signature:______________
Student Name:___________________
 ACKNOWLEDGEMENT

We would like to take this opportunity to express our deep sense of gratitude to
all who helped us directly or indirectly during this seminar work.

First of all, we would like to express our deepest gratitude to our seminar
supervisor________________________ (practical incharge) for his enormous
help and advice and for providing inspiration which cannot be expressed with
words. We would have not accomplished this seminar without his patient care,
understanding and encouragement. His advice, encouragement and critics are
source of innovative ideas, inspiration and causes behind the successful
completion of this seminar work. The confidence shown on us by him was the
biggest source of inspiration for us.

We are deeply thankful to MDU Management for providing facilities for

accomplishment of this PRACTICAL.

Date: …………………

Signature:______________
Student Name:___________________
 Experiment No.-1
Retrieving All Records from a Table

Objective:
Learn how to retrieve all records from a table using SQL.

Prerequisites:

 Basic understanding of SQL.

 A DBMS installed (e.g., MySQL, PostgreSQL, SQLite).
 A sample database with at least one table.

Sample Table:
Let's assume we have a table named Employees with the following structure:

EmployeeID Name Position Salary

1 John Doe Manager 60000
2 Jane Smith Developer 55000
3 Alice Brown Designer 50000

Step-by-Step Instructions:

1. Open Your DBMS: Launch your preferred DBMS (e.g., MySQL Workbench,
pgAdmin, etc.).
2. Connect to the Database: Connect to the database where your table is located.
3. Select the Database: If necessary, use the command to select the database:

sql
Copy code
USE your_database_name; -- Replace with your actual database name
4. Write the SQL Query: To retrieve all records from the Employees table, use
the following SQL query:

sql
Copy code
SELECT * FROM Employees;
5. Execute the Query: Run the query. You can usually do this by clicking a
"Run" button or executing the command from the command line.
 6. View the Results: Check the output/results pane to see all records from the
Employees table.

Expected Output: After executing the query, you should see a result set that looks like this:

EmployeeID Name Position Salary

1 John Doe Manager 60000
2 Jane Smith Developer 55000
3 Alice Brown Designer 50000

Conclusion: You have successfully retrieved all records from the Employees table
using SQL. This fundamental operation is essential for data retrieval in any database
application.

Exercises:

1. Modify the query to select specific columns (e.g., Name and Salary).
2. Experiment with a different table in your database to retrieve records.

Experiment No.-2
Selecting Specific Columns from a Table

Objective:
Learn how to select specific columns from a table using SQL.

Prerequisites:

 Basic understanding of SQL.

 A DBMS installed (e.g., MySQL, PostgreSQL, SQLite).
 A sample database with at least one table.

Sample Table:
Continuing with the Employees table, which has the following structure:

EmployeeID Name Position Salary

1 John Doe Manager 60000
2 Jane Smith Developer 55000
3 Alice Brown Designer 50000

Step-by-Step Instructions:

1. Open Your DBMS: Launch your preferred DBMS (e.g., MySQL Workbench,
pgAdmin, etc.).
2. Connect to the Database: Connect to the database where your table is located.
3. Select the Database: If necessary, use the command to select the database:

sql
Copy code
USE your_database_name; -- Replace with your actual
database name
4. Write the SQL Query: To select specific columns (e.g., Name and Salary)
from the Employees table, use the following SQL query:

sql
Copy code
SELECT Name, Salary FROM Employees;
5. Execute the Query: Run the query by clicking the "Run" button or executing
the command from the command line.
6. View the Results: Check the output/results pane to see the selected columns.

Expected Output: After executing the query, you should see a result set that looks like this:
 Name Salary
John Doe 60000
Jane Smith 55000
Alice Brown 50000

Conclusion: You have successfully selected specific columns (Name and Salary)
from the Employees table using SQL. This allows you to retrieve only the data you
need, making queries more efficient.

Exercises:

1. Modify the query to select the Position and EmployeeID.

2. Experiment with selecting different combinations of columns from the table.

Experiment No.-3
Using the WHERE Clause to Filter Records

Objective:
Learn how to filter records from a table using the WHERE clause in SQL.
Prerequisites:

 Basic understanding of SQL.

 A DBMS installed (e.g., MySQL, PostgreSQL, SQLite).
 A sample database with at least one table.

Sample Table:
We will continue using the Employees table, which has the following structure:

EmployeeID Name Position Salary

1 John Doe Manager 60000
2 Jane Smith Developer 55000
3 Alice Brown Designer 50000

Step-by-Step Instructions:

1. Open Your DBMS: Launch your preferred DBMS (e.g., MySQL Workbench,
pgAdmin, etc.).
2. Connect to the Database: Connect to the database where your table is located.
3. Select the Database: If necessary, use the command to select the database:

sql
Copy code
USE your_database_name; -- Replace with your actual
database name
4. Write the SQL Query: To filter records where the Position is
Developer, use the following SQL query:

sql
Copy code
SELECT * FROM Employees WHERE Position = 'Developer';
5. Execute the Query: Run the query by clicking the "Run" button or executing
the command from the command line.
6. View the Results: Check the output/results pane to see the filtered records.

Expected Output: After executing the query, you should see a result set that looks like this:

EmployeeID Name Position Salary

2 Jane Smith Developer 55000

Conclusion: You have successfully filtered records from the Employees table
using the WHERE clause. This allows you to retrieve specific data based on certain
conditions.

Exercises:

1. Modify the query to filter records where Salary is greater than 55000.

sql
Copy code
SELECT * FROM Employees WHERE Salary > 55000;
2. Experiment with filtering based on different criteria, such as Name or
Position.

Experiment No.-4
Sorting Records Using ORDER BY

Objective:
Learn how to sort records from a table using the ORDER BY clause in SQL.

Prerequisites:

 Basic understanding of SQL.

 A DBMS installed (e.g., MySQL, PostgreSQL, SQLite).
  A sample database with at least one table.

Sample Table:
We will continue using the Employees table, which has the following structure:

EmployeeID Name Position Salary

1 John Doe Manager 60000
2 Jane Smith Developer 55000
3 Alice Brown Designer 50000

Step-by-Step Instructions:

1. Open Your DBMS: Launch your preferred DBMS (e.g., MySQL Workbench,
pgAdmin, etc.).
2. Connect to the Database: Connect to the database where your table is located.
3. Select the Database: If necessary, use the command to select the database:

sql
Copy code
USE your_database_name; -- Replace with your actual
database name
4. Write the SQL Query: To sort the records by Salary in ascending order,
use the following SQL query:

sql
Copy code
SELECT * FROM Employees ORDER BY Salary ASC;
5. Execute the Query: Run the query by clicking the "Run" button or executing
the command from the command line.
6. View the Results: Check the output/results pane to see the sorted records.

Expected Output: After executing the query, you should see a result set sorted by salary:

EmployeeID Name Position Salary

3 Alice Brown Designer 50000
2 Jane Smith Developer 55000
1 John Doe Manager 60000

Conclusion: You have successfully sorted records from the Employees table using
the ORDER BY clause. This allows you to organize your data in a meaningful way.
Exercises:

1. Modify the query to sort by Name in descending order:

sql
Copy code
SELECT * FROM Employees ORDER BY Name DESC;
2. Experiment with sorting by multiple columns (e.g., first by Position, then
by Salary).

Experiment No.-5
Using Aggregate Functions in SQL

Objective:
Learn how to use aggregate functions to perform calculations on your data in SQL.

Prerequisites:

 Basic understanding of SQL.

 A DBMS installed (e.g., MySQL, PostgreSQL, SQLite).
 A sample database with at least one table.

Sample Table:
We will continue using the Employees table, which has the following structure:
EmployeeID Name Position Salary
1 John Doe Manager 60000
2 Jane Smith Developer 55000
3 Alice Brown Designer 50000

Aggregate Functions Overview

1. COUNT: Counts the number of rows that match a specified condition.

2. SUM: Calculates the total sum of a numeric column.
3. AVG: Computes the average value of a numeric column.
4. MIN: Finds the minimum value in a column.
5. MAX: Finds the maximum value in a column.

Step-by-Step Instructions:

1. Open Your DBMS: Launch your preferred DBMS (e.g., MySQL Workbench,
pgAdmin, etc.).
2. Connect to the Database: Connect to the database where your table is located.
3. Select the Database: If necessary, use the command to select the database:

sql
Copy code
USE your_database_name; -- Replace with your actual
database name
4. Use Aggregate Functions:
o COUNT: Count the number of employees.

sql
Copy code
SELECT COUNT(*) AS TotalEmployees FROM
Employees;
o SUM: Calculate the total salary of all employees.

sql
Copy code
SELECT SUM(Salary) AS TotalSalary FROM
Employees;
o AVG: Calculate the average salary.

sql
Copy code
SELECT AVG(Salary) AS AverageSalary FROM
 Employees;
o MIN: Find the minimum salary.

sql
Copy code
SELECT MIN(Salary) AS MinimumSalary FROM
Employees;
o MAX: Find the maximum salary.

sql
Copy code
SELECT MAX(Salary) AS MaximumSalary FROM
Employees;
5. Execute Each Query: Run each query one by one by clicking the "Run"
button or executing the commands from the command line.
6. View the Results: Check the output/results pane to see the results for each
aggregate function.

Expected Outputs:

 COUNT: Total number of employees.

Copy code
TotalEmployees
3
 SUM: Total salary of all employees.

Copy code
TotalSalary
165000
 AVG: Average salary of employees.

Copy code
AverageSalary
55000
 MIN: Minimum salary among employees.

Copy code
MinimumSalary
50000
 MAX: Maximum salary among employees.
 Copy code
MaximumSalary
60000

Conclusion:

You have successfully used aggregate functions to perform calculations on the

Employees table. These functions are essential for analyzing data effectively.

Exercises:

1. Use COUNT with a WHERE clause to count employees with a salary greater
than 55000.

sql
Copy code
SELECT COUNT(*) AS CountAbove55000 FROM Employees
WHERE Salary > 55000;
2. Find the total salary of employees in a specific position (e.g., Manager).

sql
Copy code
SELECT SUM(Salary) AS TotalManagerSalary FROM
Employees WHERE Position = 'Manager';
 Experiment No.-6
Complex Queries Using JOIN

Objective:
Learn how to write complex SQL queries that involve multiple tables using different
types of JOIN operations: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL
JOIN.

Prerequisites:

 Basic understanding of SQL and JOIN operations.

 A DBMS installed (e.g., MySQL, PostgreSQL, SQLite).
 Sample databases with at least two related tables.

Sample Tables:

1. Employees Table:

EmployeeID Name Position Salary

1 John Doe Manager 60000
2 Jane Smith Developer 55000
3 Alice Brown Designer 50000
2. Departments Table:
DepartmentID DepartmentName EmployeeID
1 IT 2
2 HR 1
3 Marketing NULL
Types of JOINs
1. INNER JOIN: Returns records that have matching values in both tables.
2. LEFT JOIN: Returns all records from the left table and the matched records
from the right table. If no match, NULL is returned for columns from the right
table.
3. RIGHT JOIN: Returns all records from the right table and the matched
records from the left table. If no match, NULL is returned for columns from
the left table.
4. FULL JOIN: Returns all records when there is a match in either left or right
table records. If no match, NULL is returned for non-matching records.

Step-by-Step Instructions:

1. Open Your DBMS: Launch your preferred DBMS (e.g., MySQL Workbench,
pgAdmin, etc.).
2. Connect to the Database: Connect to the database where your tables are
located.

3. Select the Database: If necessary, use the command to select the database:

sql
Copy code
USE your_database_name; -- Replace with your actual
database name
Example Queries:
1. INNER JOIN

Retrieve employees and their corresponding department names where there is a

match.

sql
Copy code
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.EmployeeID =
Departments.EmployeeID;
2. LEFT JOIN

Retrieve all employees and their department names, including those without a
department.

sql
Copy code
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.EmployeeID =
Departments.EmployeeID;
3. RIGHT JOIN

Retrieve all departments and the employees in them, including departments without
employees.

sql
Copy code
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.EmployeeID =
Departments.EmployeeID;
4. FULL JOIN

Retrieve all employees and all departments, matching where possible.

sql
Copy code
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
FULL JOIN Departments ON Employees.EmployeeID =
Departments.EmployeeID;
Execute Each Query:

Run each query one by one by clicking the "Run" button or executing the commands
from the command line.

Expected Outputs:
 INNER JOIN Output:
 Name DepartmentName
Jane Smith IT
John Doe HR
 LEFT JOIN Output:

Name DepartmentName
John Doe HR
Jane Smith IT
Alice Brown NULL
 RIGHT JOIN Output:

Name DepartmentName
John Doe HR
Jane Smith IT
NULL Marketing
 FULL JOIN Output:

Name DepartmentName
John Doe HR
Jane Smith IT
Alice Brown NULL
NULL Marketing
Conclusion:

You have successfully written complex SQL queries using various types of JOIN
operations to retrieve related data from multiple tables.

Exercises:

1. Create a query that lists all employees with their department names, but show
employees from the HR department only.

sql
Copy code
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.EmployeeID =
Departments.EmployeeID
WHERE Departments.DepartmentName = 'HR';
2. Write a query to count how many employees belong to each department,
including departments with no employees.

sql
Copy code
SELECT Departments.DepartmentName,
COUNT(Employees.EmployeeID) AS EmployeeCount
FROM Departments
LEFT JOIN Employees ON Employees.EmployeeID =
Departments.EmployeeID
GROUP BY Departments.DepartmentName;
 Experiment No.-7
Subqueries, Nested Queries, and Set Operations

Objective:
Learn how to use subqueries and nested queries in SQL, as well as implement set
operations.

Prerequisites:

 Basic understanding of SQL.

 A DBMS installed (e.g., MySQL, PostgreSQL, SQLite).
 Sample databases with at least two related tables.

Sample Tables:

1. Employees Table:

EmployeeID Name Position Salary

1 John Doe Manager 60000
2 Jane Smith Developer 55000
3 Alice Brown Designer 50000
4 Bob White Developer 57000
2. Departments Table:

DepartmentID DepartmentName EmployeeID

1 IT 2
2 HR 1
3 Marketing NULL
Step-by-Step Instructions:
 1. Open Your DBMS: Launch your preferred DBMS (e.g., MySQL Workbench,
pgAdmin, etc.).
2. Connect to the Database: Connect to the database where your tables are
located.

3. Select the Database: If necessary, use the command to select the database:

sql
Copy code
USE your_database_name; -- Replace with your actual
database name
Subqueries and Nested Queries
1. Subquery Example

Retrieve employees whose salary is above the average salary.

sql
Copy code
SELECT Name
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
2. Nested Query Example

List employees who work in the department with the highest salary.

sql
Copy code
SELECT Name
FROM Employees
WHERE EmployeeID IN (
SELECT EmployeeID
FROM Departments
WHERE EmployeeID IS NOT NULL
AND DepartmentID = (SELECT DepartmentID FROM
Departments ORDER BY EmployeeID DESC LIMIT 1)
);
Set Operations
1. UNION

Retrieve all unique positions from the Employees table and a hypothetical
Contractors table.

sql
Copy code
SELECT Position FROM Employees
UNION
SELECT Position FROM Contractors; -- Assuming
Contractors table exists
2. INTERSECT

Retrieve the names of employees who are also in a Contractors table.

sql
Copy code
SELECT Name FROM Employees
INTERSECT
SELECT Name FROM Contractors; -- Assuming Contractors
table exists
3. EXCEPT

Retrieve the names of employees who are not contractors.

sql
Copy code
SELECT Name FROM Employees
EXCEPT
SELECT Name FROM Contractors; -- Assuming Contractors
table exists
Execute Each Query:

Run each query one by one by clicking the "Run" button or executing the commands
from the command line.

Expected Outputs:

 Subquery Output: Names of employees with a salary above the average.

  Nested Query Output: Names of employees who work in the department with
the highest employee ID (as a proxy for the highest salary).

 UNION Output: A combined list of unique positions from both tables.

 INTERSECT Output: Names common to both employees and contractors.

 EXCEPT Output: Names of employees who are not contractors.

Conclusion:

You have successfully implemented subqueries, nested queries, and set operations in
SQL to manipulate and retrieve data from multiple tables.

Exercises:

1. Create a query to find employees whose salary is less than the salary of Jane
Smith.

sql
Copy code
SELECT Name
FROM Employees
WHERE Salary < (SELECT Salary FROM Employees WHERE
Name = 'Jane Smith');

2. If you have a Projects table, find all project names that are not assigned to
any employees.

sql
Copy code
SELECT ProjectName FROM Projects
EXCEPT
SELECT ProjectName FROM EmployeeProjects; --
Assuming EmployeeProjects table exists

o design an Entity-Relationship (ER) diagram, let’s go through a hypothetical

scenario and outline the key entities, attributes, and relationships.

Scenario: University Database

Description: A university wants to keep track of its students, courses, and the
professors who teach those courses. The database should also manage the enrollment
of students in courses.

Entities and Attributes:

1. Student
o Attributes:
 StudentID (Primary Key)
 FirstName
 LastName
 DateOfBirth
 Email
2. Course
o Attributes:
 CourseID (Primary Key)
 CourseName
 Credits
3. Professor
o Attributes:
 ProfessorID (Primary Key)
 FirstName
 LastName
 Email
 Department
4. Enrollment
o Attributes:
 EnrollmentID (Primary Key)
 Semester
 Year
 Grade

Relationships:

1. Enrolls
o Between: Student and Course
o Cardinality: Many-to-Many (A student can enroll in many courses, and
a course can have many students)
o Associative Entity: Enrollment (to capture additional attributes like
Semester, Year, and Grade)
2. Teaches
o Between: Professor and Course
 o Cardinality: One-to-Many (A professor can teach multiple courses, but
each course is taught by one professor)

ER Diagram Representation:

Here’s a simple text representation of the ER diagram:

css
Copy code
[Student] ------------------------< [Enrollment]
>------------------------ [Course]
|
|
|
|
[Professor]
---------------------------------------------------------
------|

Diagram Components:

1. Entities:
o Rectangles represent entities: Student, Course, Professor, Enrollment.
2. Attributes:
o Ovals connected to their respective entities for attributes.
o Underlined attributes indicate primary keys (e.g., StudentID, CourseID,
ProfessorID).
3. Relationships:
o Diamonds represent relationships (Enrolls, Teaches).
o Lines indicate the cardinality of the relationships (1 to Many, Many to
Many).

Example of ER Diagram:

If you were to create a visual ER diagram using diagramming software, it might look
something like this:

sql
Copy code
+-----------------+ +-----------------+
| Student | | Professor |
|-----------------| |-----------------|
| StudentID (PK) | | ProfessorID (PK)|
| FirstName | | FirstName |
| LastName | | LastName |
| DateOfBirth | | Email |
| Email | | Department |
+-----------------+ +-----------------+
| |
| |
| |
| |
| |
| |
+-----------------------+
| Teaches |
+-----------------------+
|
|
|
+-----------------+
| Course |
|-----------------|
| CourseID (PK) |
| CourseName |
| Credits |
+-----------------+
|
|
|
+-----------------+
| Enrollment |
|-----------------|
| EnrollmentID (PK)|
| Semester |
| Year |
| Grade |
+-----------------+

Conclusion:

This ER diagram provides a clear representation of the entities involved in the

university database scenario, their attributes, and how they relate to each other. You
can use diagramming tools like Lucidchart, Draw.io, or any other ER diagram
software to create a visual representation based on this design.

4o mini

Experiment No.-8
To convert an ER diagram into relational schemas, we’ll take the entities, attributes,
and relationships identified in the ER diagram and define them in terms of tables.
Each table will correspond to an entity or a relationship, with attributes as columns.

Scenario Recap

From the university database example, we had the following entities and their
attributes:

1. Student
o StudentID (PK)
o FirstName
o LastName
o DateOfBirth
o Email
2. Course
o CourseID (PK)
o CourseName
o Credits
3. Professor
 o ProfessorID (PK)
o FirstName
o LastName
o Email
o Department
4. Enrollment (associative entity for the many-to-many relationship between
Student and Course)
o EnrollmentID (PK)
o StudentID (FK)
o CourseID (FK)
o Semester
o Year
o Grade

Relational Schemas

Based on the ER diagram, the relational schemas would be defined as follows:

1. Student Table

sql
Copy code
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE,
Email VARCHAR(100) UNIQUE
);
2. Course Table

sql
Copy code
CREATE TABLE Course (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100),
Credits INT
);
3. Professor Table

sql
Copy code
 CREATE TABLE Professor (
ProfessorID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100) UNIQUE,
Department VARCHAR(50)
);
4. Enrollment Table

sql
Copy code
CREATE TABLE Enrollment (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
Semester VARCHAR(20),
Year INT,
Grade CHAR(2),
FOREIGN KEY (StudentID) REFERENCES
Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES
Course(CourseID)
);

Explanation of the Relational Schemas:

 Primary Keys (PK): Each table has a primary key that uniquely identifies
each record.
 Foreign Keys (FK): In the Enrollment table, StudentID and
CourseID act as foreign keys referencing the Student and Course tables,
respectively. This establishes the relationship between these tables.
 Data Types: Appropriate data types are assigned based on the nature of the
data (e.g., VARCHAR, INT, DATE, CHAR).

Conclusion

 The conversion of ER diagrams into relational schemas defines the structure of

your database in terms of tables and relationships. This is a crucial step in
database design, ensuring that your relational database can effectively
represent the data modeled in the ER diagram. You can use these schemas to
create the actual database tables in a SQL database management system.
 4o mini
 Experiment No.-9
Normalization is a process used in database design to minimize redundancy and
dependency by organizing fields and table relationships. Here’s how to apply
normalization techniques (1NF, 2NF, and 3NF) to ensure database efficiency for our
university database example.

Starting Point: Relational Schema

Initial Tables:

1. Student
o StudentID (PK)
o FirstName
o LastName
o DateOfBirth
o Email
2. Course

o CourseID (PK)
o CourseName
o Credits
3. Professor
 oProfessorID (PK)
o FirstName
o LastName
o Email
o Department
4. Enrollment

o EnrollmentID (PK)
o StudentID (FK)
o CourseID (FK)
o Semester
o Year
o Grade

Normalization Process
1. First Normal Form (1NF)

Definition: A table is in 1NF if:

 All attributes contain only atomic values (no repeating groups or arrays).
 Each column contains values of a single type.

Analysis:

 The initial tables already meet 1NF requirements, as all attributes are atomic.

Example of 1NF Validation:

 Student's FirstName, LastName, etc., are atomic. No columns contain

multiple values.

2. Second Normal Form (2NF)

Definition: A table is in 2NF if:

 It is in 1NF.
 All non-key attributes are fully functionally dependent on the primary key.

Analysis:
  The Enrollment table is the only candidate for potential violation. If the
same student enrolls in multiple courses, then Semester, Year, and Grade
depend only on EnrollmentID, not on the composite of StudentID and
CourseID.

No Changes Needed:

 Since all non-key attributes in other tables depend on their respective primary
keys, the schema is already in 2NF.

3. Third Normal Form (3NF)

Definition: A table is in 3NF if:

 It is in 2NF.
 There are no transitive dependencies (non-key attributes do not depend on
other non-key attributes).

Analysis:

 The Professor table might have a transitive dependency if, for example,
Department could be derived from another attribute or could link to another
entity.

Adjustments:

 If departments have their own attributes, create a separate Department table.

Normalized Schema:

1. Student Table (Remains unchanged)

sql
Copy code
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE,
Email VARCHAR(100) UNIQUE
);
2. Course Table (Remains unchanged)

sql
Copy code
CREATE TABLE Course (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100),
Credits INT
);

3. Professor Table (Modification suggested)

sql
Copy code
CREATE TABLE Professor (
ProfessorID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100) UNIQUE
);

4. Department Table (New table to eliminate transitive dependencies)

sql
Copy code
CREATE TABLE Department (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);

5. Enrollment Table (Remains unchanged)

sql
Copy code
CREATE TABLE Enrollment (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
Semester VARCHAR(20),
Year INT,
Grade CHAR(2),
 FOREIGN KEY (StudentID) REFERENCES
Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES
Course(CourseID)
);

6. Professor_Department Table (New table to link professors to departments)

sql
Copy code
CREATE TABLE Professor_Department (
ProfessorID INT,
DepartmentID INT,
PRIMARY KEY (ProfessorID, DepartmentID),
FOREIGN KEY (ProfessorID) REFERENCES
Professor(ProfessorID),
FOREIGN KEY (DepartmentID) REFERENCES
Department(DepartmentID)
);
Summary of Normalization
 1NF: Ensured all attributes are atomic and the tables are properly structured.
 2NF: Confirmed that non-key attributes are fully functionally dependent on the
primary keys.
 3NF: Eliminated transitive dependencies by introducing a new Department
table, improving the structure and efficiency of the database.

This normalization process will help in reducing redundancy and ensuring data
integrity in the university database.

4o mini
 Experiment No.-10
Here's how to create the tables for the university database and define the primary and
foreign keys based on the normalized schema discussed previously.

SQL Table Creation Statements

1. Student Table

sql
Copy code
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE,
Email VARCHAR(100) UNIQUE
);
2. Course Table

sql
 Copy code
CREATE TABLE Course (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100),
Credits INT
);
3. Professor Table

sql
Copy code
CREATE TABLE Professor (
ProfessorID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100) UNIQUE
);
4. Department Table

sql
Copy code
CREATE TABLE Department (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);
5. Professor_Department Table (to establish the many-to-many relationship
between Professors and Departments)

sql
Copy code
CREATE TABLE Professor_Department (
ProfessorID INT,
DepartmentID INT,
PRIMARY KEY (ProfessorID, DepartmentID),
FOREIGN KEY (ProfessorID) REFERENCES
Professor(ProfessorID),
FOREIGN KEY (DepartmentID) REFERENCES
Department(DepartmentID)
);
6. Enrollment Table

sql
Copy code
 CREATE TABLE Enrollment (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
Semester VARCHAR(20),
Year INT,
Grade CHAR(2),
FOREIGN KEY (StudentID) REFERENCES
Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES
Course(CourseID)
);

Explanation of the Table Creation Statements:

 Primary Keys (PK): Each table defines a primary key that uniquely identifies
each record (e.g., StudentID, CourseID, ProfessorID,
DepartmentID, and EnrollmentID).
 Foreign Keys (FK):
o In the Enrollment table, StudentID and CourseID are foreign
keys referencing the Student and Course tables, respectively.
o In the Professor_Department table, ProfessorID references
the Professor table, and DepartmentID references the
Department table.

Conclusion

These SQL statements define the structure of the university database, ensuring that
data integrity is maintained through the use of primary and foreign keys. You can
execute these statements in your SQL database management system to create the
respective tables.
Experiment No.-11

To create a new database and define its schema, you can use SQL commands. Below
is a step-by-step guide to creating a university database and defining its schema based
on the previously discussed tables.

Step 1: Create a New Database

First, you need to create the database. You can do this with the following SQL
command:

sql
Copy code
CREATE DATABASE UniversityDB;

Step 2: Use the Database

Once the database is created, you need to select it for use:

sql
Copy code
USE UniversityDB;

Step 3: Define the Schema by Creating Tables

Now, you can define the schema by creating the necessary tables along with their
primary and foreign keys. Here’s the SQL code for creating each table:

1. Student Table

sql
Copy code
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
DateOfBirth DATE,
Email VARCHAR(100) UNIQUE NOT NULL
);
2. Course Table
 sql
Copy code
CREATE TABLE Course (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100) NOT NULL,
Credits INT CHECK (Credits > 0)
);
3. Professor Table

sql
Copy code
CREATE TABLE Professor (
ProfessorID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL
);
4. Department Table

sql
Copy code
CREATE TABLE Department (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100) NOT NULL
);
5. Professor_Department Table (to establish the many-to-many relationship)

sql
Copy code
CREATE TABLE Professor_Department (
ProfessorID INT,
DepartmentID INT,
PRIMARY KEY (ProfessorID, DepartmentID),
FOREIGN KEY (ProfessorID) REFERENCES
Professor(ProfessorID),
FOREIGN KEY (DepartmentID) REFERENCES
Department(DepartmentID)
);
6. Enrollment Table

sql
Copy code
 CREATE TABLE Enrollment (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
Semester VARCHAR(20) NOT NULL,
Year INT CHECK (Year >= 2000), -- assuming years
should be realistic
Grade CHAR(2),
FOREIGN KEY (StudentID) REFERENCES
Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES
Course(CourseID)
);

Summary of Database Schema

This schema consists of the following tables:

 Student: Stores student details.

 Course: Stores course details.
 Professor: Stores professor details.
 Department: Stores department details.
 Professor_Department: Links professors to departments.
 Enrollment: Tracks which students are enrolled in which courses.

Execution

You can execute the above SQL commands in your SQL database management
system (e.g., MySQL Workbench, pgAdmin) to create the UniversityDB database
and define its schema.

Conclusion

By following these steps, you have successfully created a new database and defined
its schema, establishing the structure needed to manage the university's data
effectively.
 Experiment No.-12
Creating indexes on tables is an effective way to improve query performance in a
database. Below, I'll provide SQL commands to create indexes on the tables in the
UniversityDB database, focusing on columns that are commonly used in search
conditions, joins, and foreign key relationships.

SQL Commands to Create Indexes

1. Index on the Student Table

o Index on LastName to speed up searches by last name.

sql
Copy code
CREATE INDEX idx_student_lastname ON
Student(LastName);
2. Index on the Course Table
o Index on CourseName to speed up searches by course name.

sql
Copy code
CREATE INDEX idx_course_name ON Course(CourseName);
3. Index on the Professor Table
o Index on LastName to speed up searches by professor last name.

sql
Copy code
CREATE INDEX idx_professor_lastname ON
Professor(LastName);
4. Index on the Department Table
o Index on DepartmentName to speed up searches by department name.
 sql
Copy code
CREATE INDEX idx_department_name ON
Department(DepartmentName);
5. Index on the Enrollment Table
o Composite index on StudentID and CourseID to speed up joins and
queries related to enrollments.

sql
Copy code
CREATE INDEX idx_enrollment_student_course ON
Enrollment(StudentID, CourseID);
6. Index on the Professor_Department Table
o Composite index on ProfessorID and DepartmentID for efficient
joins.

sql
Copy code
CREATE INDEX idx_professor_department ON
Professor_Department(ProfessorID, DepartmentID);

Execution

You can execute the above SQL commands in your SQL database management
system (e.g., MySQL Workbench, pgAdmin) after creating the tables in the
UniversityDB database.

Considerations

 Index Types: The default index type is a B-tree index, which is suitable for
most queries. You may consider other types (like full-text indexes) based on
your specific use cases.
 Performance Trade-offs: While indexes speed up read operations, they can
slow down write operations (INSERT, UPDATE, DELETE) because the index
must also be updated. It's important to balance the need for fast queries with
the overhead of maintaining indexes.
 Monitoring: Regularly monitor query performance and adjust indexes as
necessary based on query patterns and application needs.

Conclusion
By creating these indexes, you enhance the query performance for frequently
accessed columns, especially those involved in searches and joins. This will lead to
faster data retrieval and an overall improved user experience in applications relying
on the UniversityDB database

Experiment No.-13
Optimizing SQL queries is crucial for improving performance, especially in
databases with large datasets. Below are strategies for optimizing queries, including
rewriting them for better efficiency and leveraging indexes.

1. Using Indexes

Example Query:

sql
Copy code
SELECT FirstName, LastName
FROM Student
WHERE LastName = 'Smith';

Optimization: Ensure that there is an index on LastName to speed up this query.

The index will help the database quickly locate rows with the last name "Smith".

2. Avoiding SELECT *

Example Query:

sql
Copy code
SELECT *
FROM Course
WHERE Credits > 3;

Optimization: Specify only the necessary columns instead of using SELECT *.

This reduces data transfer and processing time.

Rewritten Query:

sql
Copy code
SELECT CourseID, CourseName
FROM Course
WHERE Credits > 3;
3. Filtering Early

Example Query:

sql
Copy code
SELECT p.FirstName, p.LastName, c.CourseName
FROM Professor p
JOIN Course c ON p.ProfessorID = c.ProfessorID
WHERE c.Credits > 3;

Optimization: If you can filter before joining, do so. This reduces the amount of data
being processed in the join.

Rewritten Query:

sql
Copy code
SELECT p.FirstName, p.LastName, c.CourseName
FROM Professor p
JOIN (
SELECT *
FROM Course
WHERE Credits > 3
) c ON p.ProfessorID = c.ProfessorID;
4. Using EXISTS Instead of IN

Example Query:

sql
Copy code
SELECT FirstName, LastName
FROM Student
WHERE StudentID IN (SELECT StudentID FROM Enrollment
WHERE Grade = 'A');

Optimization: Using EXISTS can sometimes be more efficient than using IN,
particularly when dealing with subqueries.

Rewritten Query:
sql
Copy code
SELECT FirstName, LastName
FROM Student s
WHERE EXISTS (
SELECT 1
FROM Enrollment e
WHERE e.StudentID = s.StudentID AND e.Grade = 'A'
);
5. Limiting Results

Example Query:

sql
Copy code
SELECT FirstName, LastName
FROM Student
ORDER BY LastName;

Optimization: If you only need a subset of the results, use LIMIT to reduce the
number of rows returned.

Rewritten Query:

sql
Copy code
SELECT FirstName, LastName
FROM Student
ORDER BY LastName
LIMIT 10;
6. Using Joins Effectively

Example Query:

sql
Copy code
SELECT s.FirstName, s.LastName, e.Grade
FROM Student s, Enrollment e
WHERE s.StudentID = e.StudentID;

Optimization: Use explicit JOIN syntax for clarity and potential performance
benefits.
Rewritten Query:

sql
Copy code
SELECT s.FirstName, s.LastName, e.Grade
FROM Student s
JOIN Enrollment e ON s.StudentID = e.StudentID;
7. Utilizing Aggregate Functions Wisely

Example Query:

sql
Copy code
SELECT COUNT(*)
FROM Enrollment
WHERE Semester = 'Fall' AND Year = 2023;

Optimization: Ensure there are indexes on Semester and Year to speed up the
aggregation.

8. Analyze and Optimize Execution Plans

Use your database's query analyzer or execution plan feature to see how queries are
executed. This can help you identify bottlenecks and inefficient operations.

Conclusion

By following these optimization techniques, you can improve the performance of

your SQL queries. Focus on leveraging indexes, rewriting queries for efficiency, and
analyzing execution plans to ensure that your database operations are as fast and
efficient as possible. Regularly revisiting and optimizing queries based on usage
patterns can lead to significant performance improvements.
 Experiment No.-14
Triggers are a powerful feature in SQL databases that automatically execute specified
actions in response to certain events, such as INSERT, UPDATE, or DELETE. Below,
I’ll provide examples of how to create triggers for common scenarios in a university
database.

Scenario: Triggers for the University Database

Let’s create some triggers for the following use cases:

1. Log changes in the Enrollment table whenever a new enrollment is

added.
2. Automatically update a student's status based on their grades.
3. Prevent deletion of a course if it is being referenced in any enrollment
records.

1. Trigger to Log Enrollment Changes

This trigger will log details of new enrollments into a separate Enrollment_Log
table.

Step 1: Create the Log Table

sql
Copy code
CREATE TABLE Enrollment_Log (
LogID INT PRIMARY KEY AUTO_INCREMENT,
StudentID INT,
CourseID INT,
Semester VARCHAR(20),
Year INT,
Grade CHAR(2),
Action VARCHAR(20),
ActionTime DATETIME DEFAULT CURRENT_TIMESTAMP
);
Step 2: Create the Trigger
sql
Copy code
CREATE TRIGGER after_enrollment_insert
AFTER INSERT ON Enrollment
FOR EACH ROW
BEGIN
INSERT INTO Enrollment_Log (StudentID, CourseID,
Semester, Year, Grade, Action)
VALUES (NEW.StudentID, NEW.CourseID, NEW.Semester,
NEW.Year, NEW.Grade, 'INSERT');
END;
2. Trigger to Update Student Status Based on Grades

This trigger will update a hypothetical Status column in the Student table based
on the average grade. Assume grades are represented numerically.

Step 1: Alter the Student Table (Add Status Column)

sql
Copy code
ALTER TABLE Student ADD COLUMN Status VARCHAR(20);
Step 2: Create the Trigger
sql
Copy code
CREATE TRIGGER after_enrollment_update
AFTER UPDATE ON Enrollment
FOR EACH ROW
BEGIN
DECLARE avg_grade CHAR(2);
SELECT AVG(Grade) INTO avg_grade
FROM Enrollment
WHERE StudentID = NEW.StudentID;
IF avg_grade >= 'A' THEN
UPDATE Student SET Status = 'Excellent' WHERE
StudentID = NEW.StudentID;
ELSEIF avg_grade >= 'B' THEN
UPDATE Student SET Status = 'Good' WHERE
StudentID = NEW.StudentID;
ELSE
UPDATE Student SET Status = 'Needs Improvement'
WHERE StudentID = NEW.StudentID;
 END IF;
END;
3. Prevent Deletion of a Course if Enrolled

This trigger will prevent the deletion of a course if there are any records in the
Enrollment table for that course.

Step 1: Create the Trigger

sql
Copy code
CREATE TRIGGER before_course_delete
BEFORE DELETE ON Course
FOR EACH ROW
BEGIN
DECLARE course_count INT;
SELECT COUNT(*) INTO course_count
FROM Enrollment
WHERE CourseID = OLD.CourseID;
IF course_count > 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Cannot delete course;
students are enrolled in it.';
END IF;
END;
Summary of Trigger Use Cases
1. Logging Enrollments: Automatically logs details of every new enrollment
into a log table.
2. Updating Student Status: Automatically updates the student's status based on
their average grade after any enrollment record update.
3. Preventing Deletion: Ensures that courses cannot be deleted if there are active
enrollments.

Conclusion

By using triggers effectively, you can automate various tasks in your database,
enhance data integrity, and maintain consistency without manual intervention.
Triggers can help enforce business rules and keep your database clean and accurate.
Make sure to test triggers thoroughly to avoid unintended consequences, especially
when they involve updates and deletes.
 A
Practical Report
On
“Workshop Practice Lab”

(ASDP- ……)

Submitted in fulfillment of the requirements for the award of degree of

Bachelor of Technology
In
Mechanical Engineering

By
Name: ________________________________

Class: ________________________________

Submitted to: __________________ HOD ( ME)________________

(Lab Incharge)
Department of Mechanical Engineering
MAYA DEVI UNIVERSITY
MONTH_________YEAR___________
 CERTIFICATE

We hereby certify that the work which is being presented in PRACTICAL

REPORT entitled
“Workshop Practice Lab”

in partial fulfillment of the requirement for the award of the Bachelor of

Technology in Mechanical Engineering and submitted the Department of
Mechanical Engineering of MAYA DEVI UNIVERSITY, Dehradun (U.K) is an
authentic record of our own work carried out during a period from ________ to
________ under the guidance of, practical incharge and
___________________ of Department of Mechanical Engineering, MAYA
DEVI UNIVERSITY, Dehradun.

The matter presented in this seminar has not been submitted by us for the award
of any other degree elsewhere.

Student Name & Roll no.___________________________________________

This is to certify that the above statement is correct to the best of our knowledge.
Date: …………………

Signature: …………………..
(LAB INCHARGE)
 DECLARATION

We hereby declare that the seminar entitled “Workshop Practice Lab”

submitted for the fulfillment of the degree of Bachelor of Technology in
Mechanical Engineering from Maya Devi University, Dehradun embodies
results of original work and studies carried out by us and the contents of the
seminar do no form the basis for the award of any other degree to us or to
anybody else from this or any other university/institute.

Signature:______________
Student Name:___________________
 ACKNOWLEDGEMENT

We would like to take this opportunity to express our deep sense of gratitude to
all who helped us directly or indirectly during this seminar work.

First of all, we would like to express our deepest gratitude to our seminar
supervisor________________________ (practical incharge) for his enormous
help and advice and for providing inspiration which cannot be expressed with
words. We would have not accomplished this seminar without his patient care,
understanding and encouragement. His advice, encouragement and critics are
source of innovative ideas, inspiration and causes behind the successful
completion of this seminar work. The confidence shown on us by him was the
biggest source of inspiration for us.

We are deeply thankful to MDU Management for providing facilities for

accomplishment of this PRACTICAL.

Date: …………………

Signature:______________
Student Name:___________________
 INDEX

S.No. Experiments Remark

Learn how to retrieve all records from a table using SQL.
1.
Learn how to select specific columns from a table using
2. SQL.
Learn how to filter records from a table using the WHERE
3. clause in SQL.
Learn how to sort records from a table using the ORDER
4. BY clause in SQL

Learn how to use aggregate functions to perform

5. calculations on your data in SQL.

Learn how to write complex SQL queries that involve

6. multiple tables using different types of JOIN
operations: INNER JOIN, LEFT JOIN, RIGHT
JOIN, and FULL JOIN.

7. Learn how to use subqueries and nested queries in SQL,

as well as implement set operations.
To convert an ER diagram into relational schemas, we’ll
take the entities, attributes, and relationships
8. identified in the ER diagram and define them in terms
of tables. Each table will correspond to an entity or a
relationship, with attributes as columns.

To apply normalization techniques (1NF, 2NF, and 3NF) to

9. ensure database efficiency for our university database
example.
To create the tables for the university database and define the
10. primary and foreign keys based on the normalized
schema discussed previously.
To create a new database and define its schema, using
11. SQL commands.
SQL Commands to Create Indexes
12.

13. Applying indexes in database with example

14. Triggers for the University Database

75 | P a g e
 15.

76 | P a g e
 List of Experiments
Learn how to retrieve all records from a table using SQL.
Learn how to select specific columns from a table using SQL.
Learn how to filter records from a table using the WHERE clause in SQL.
Learn how to sort records from a table using the ORDER BY clause in SQL
Learn how to use aggregate functions to perform calculations on your data in SQL.
Learn how to write complex SQL queries that involve multiple tables using different
types of JOIN operations: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
Learn how to use subqueries and nested queries in SQL, as well as implement set
operations.
To convert an ER diagram into relational schemas, we’ll take the entities, attributes,
and relationships identified in the ER diagram and define them in terms of tables.
Each table will correspond to an entity or a relationship, with attributes as columns.
To apply normalization techniques (1NF, 2NF, and 3NF) to ensure database
efficiency for our university database example.
To create the tables for the university database and define the primary and foreign
keys based on the normalized schema discussed previously.
To create a new database and define its schema, using SQL commands.
SQL Commands to Create Indexes
Applying indexes in database with example
Triggers for the University Database

| P a g e No.
 List of Experiments
To manufacture the fitting job as per given drawing.
To make the double V- butt joint with arc welding.
To manufacture the job on centre lathe machine as per given drawing .
To manufacture the half lap carpentry job as per given drawing.
To make the box by sheet metal operation.

To study basics of manufacturing science related to tool and operations of foundary shop.
Study of tools and operations of Smithy shop
Minor Project:
To make a minor project by the students in batches comprising the operations performed
in different shops.

| P a g e No.
 List of Experiments
To determine the viscosity of a given fluid using the capillary tube method.

To determine the coefficient of discharge for an orifice meter.

To visualize the flow pattern of a fluid using dye injection.

To measure the pressure distribution along a horizontal pipe and verify Bernoulli’s
equation.
To determine the Reynolds number for flow in a pipe and analyze the flow type
(laminar, transitional, or turbulent).
To measure the head loss caused by different pipe fittings, such as bends, valves,
and elbows.
To determine the flow rate of a fluid using a Venturi meter.
To determine the surface tension of a liquid using the drop weight method.

| P a g e No.