DBMS LAB SWCET
WEEK 1 : CONCEPT DESIGN WITH E-R MODEL
Analyze the problem carefully and come up with the entities in it. Identify what data has to be
persisted in the database. This contains the entities, attributes etc.Identify the primary keys for all
the entities. Identify the other keys like candidate keys, partial keys if any.
The entities for the above company named “Roadway Travels” are as follows
1. Bus
2. Ticket
3. Passenger
4. Reservation
5. Cancellation
Now we will identify the attributes for each entity and construct the E-R Model.
1. BUS Entity:
The attributes are:
i. Bus_No
ii. Source
iii. Destination
iv. Dep_Time
The E-R Model is as follows:
In the above figure Bus_No is the Primary Key
2. TICKET Entity:
The attributes are:
i. Ticket_No
ii. Journey_Date
iii. Age
iv. Sex
v. Source
vi. Destination
vii. Dep_Time
The E-R Model is as follows:
1
DBMS LAB SWCET
In the above figure Ticket_No is the Primary Key
3. PASSENGER Entity:
The attributes are:
i. PNR_No
ii. Ticket_No
iii. Name
iv. Age
v. Sex
vi. PPNo
The E-R Model is as follows:
In the above figure PNR_No is the Primary Key and Ticket_No is Foreign Key
4. RESERVATION Entity:
The attributes are:
i. PNR_NO
ii. Journey_Date
iii. No_Of_Seats
iv. Address
v. Contact_No
vi. Status
The E-R Model is as follows:
2
DBMS LAB SWCET
In the above figure PNR_No is the Foreign Key
5. CANCELLATION Entity:
The attributes are:
i. PNR_No
ii. Journey_Date
iii. No_Of_Seats
iv. Address
v. Contact_No
vi. Status
The E-R Model is as follows:
In the above figure PNR_No is the Foreign Key
3
DBMS LAB SWCET
Relate the entities appropriately. Apply cardinalities for each relationship. Identify the strong
entities and weak entities (if any). Indicate the type of relationships (total/partial). Try to
incorporate generalization, aggregation, specialization etc wherever required.
Relationship between BUS Entity and PASSENGER Entity:
The following diagram depicts the relationship exists between BUS Entity and PASSENGER
The relationship between BUS Entity and PASSENGER Entity is strong relationship. Because the
two entities consists of primary keys. Here the two entities are strong entities. So the relationship
is strong relationship.
Relationship between PASSENGER Entity and RESERVATION Entity:
The following diagram depicts the relationship exists between PASSENGER Entity and
RESERVATION Entity.
4
DBMS LAB SWCET
The relationship between PASSENGER Entity and RESERVATION Entity is weak relationship.
Because the one entity consists of primary key and another entity consists of foreign key. Here
the one entity is strong entity (single border rectangle) and another entity is weak entity (double
border rectangle).
The relationship between strong entity and weak entity is weak relationship.
Relationship between PASSENGER Entity and CANCELLATION Entity:
The following diagram depicts the relationship exists between PASSENGER Entity and
CANCELLATION Entity.
The relationship between PASSENGER Entity and CANCELLATION Entity is weak
relationship. Because the one entity consists of primary key and another entity consists of foreign
key. Here the one entity is strong entity (single border rectangle) and another entity is weak entity
(double border rectangle). The relationship between strong entity and weak entity is weak
relationship.
5
DBMS LAB SWCET
6
DBMS LAB SWCET
WEEK 2 : RELATIONAL MODEL
Represent all the entities (Strong, Weak) in tabular fashion. Represent relationships in a tabular
fashion. There are different ways of representing relationships as tables based on the cardinality.
Represent attributes as columns in tables or as tables based on the requirement. Different types of
attributes (Composite, Multivalued and Derived) have different way of representation.
The following are the tabular representations of the entities used in this company.
1. BUS Entity Table:
2. TICKET Entity Table:
7
DBMS LAB SWCET
3. PASSENGER Entity Table:
4. RESERVATION Entity Table:
5. CANCELLATION Entity Table:
8
DBMS LAB SWCET
WEEK 3 : NORMALIZATION
Database Normalization is a technique for designing relational database tables to minimize
duplication of information and, in doing, to safeguard the database against certain types of
logical or structural problems, namely data anomalies. For example, when multiple instances of a
given piece of information occur in a table, the possibility exists that these instances will not be
kept consistent when the data within the table is updated, leading to a loss of data integrity. A
table that is sufficiently normalized is less vulnerable to problems of this kind. Because its
structure reflects the basic assumptions for when multiple instances of the same information
should be represented by a single instance only.
Normalization: Database designed based on the E-R model may have some amount of
inconsistency, ambiguity and redundancy. To resolve these issues some amount of
refinement is required. This refinement process is called normalization.
Let us now consider the “transportation table”
9
DBMS LAB SWCET
The above table shows the data file of the transportation in a table format. To reduce the anomalies
normalization is applied.
1ST NORMAL FORM:
A relation R is said to be in the first normal form (1NF) if and only if all the attributes of the
relation R are atomic in nature.
Consider the Transportation table to reproduce the table. The following table shows the table in
1NF.
10
DBMS LAB SWCET
In the new form, all the attributes are atomic, meaning that they are not further
decomposable.
11
DBMS LAB SWCET
2ND NORMAL FORM:
The relation is said to be in second normal form if and only if:
1. It is in the first normal form
2. No partial dependency exists between non-key attributes and key attributes.
Key Attributes:
In a given relationship if the attribute X uniquely defines all other attributes, then the
attribute X is a key attribute.
In the above tables Bus_No, Ticket_No, PNR_No are key attributes.
Non-Key Attributes:
In a given relationship R, all the attributes which are not key attributes are called non-key
attributes. The remaining attributes are non-key attributes.
Let us visit again 1NF table.
Bus_No is the key attribute for Bus
Ticket_No is the key attribute for Ticket
PNR_No is the key attribute for passenger
Other attributes like name, age, sex, Journey_Date etc are non-key attributes
To make the table 2NF we have to remove all the partial dependencies
Source, Destination and Dep_Time depends only on Bus_No
Journey_Date depends only on Ticket_No
Ticket_No, Name, Age, Sex, PPNo depends on PNR_No
BUS Table:
Bus_No Source Destination Dep_Time
1234 Anantapur Kurnool 10:30:00
4567 Kadapa Hyderabad 22:30:00
1245 Hyderabad Chennai 19:00:00
3467 Bangalore Hyderabad 12:30:00
TICKET Table:
Ticket_No Journey_Date
2345 2010-08-20
5678 2010-08-25
2457 2010-08-30
4679 2010-09-05
12
DBMS LAB SWCET
PASSENGER Table:
PNR_No Name Age Sex PPNo
1 Varuni 25 F 9003345678
2 Rahul 20 M 9247212345
3 Ajay 32 M 9989892731
4 Suni 28 F 9885252069
RESERVATION Entity Table:
PNR_No Journey_Date No_Of_Seats Address Contact_No Status
1 2010-08-20 2 Anantapur 9003345678 Yes
2 2010-08-30 1 Bangalore 9989892731 No
3 2010-08-25 2 Hyderabad 9885252069 Yes
4 2010-09-05 2 Kadapa 9247212345 No
CANCELLATION Entity Table:
PNR_No Journey_Date No_Of_Seats Address Contact_No Status
3 2010-08-30 1 Bangalore 9989892731 No
3RD NORMAL FORM:
A relation R is said to be in third normal form if and only if
1. It is in 2NF
2. No transitive dependency exists between non-key attributes and key attributes.
BUS Table:
Bus_No Source Destination Dep_Time
1234 Anantapur Kurnool 10:30:00
4567 Kadapa Hyderabad 22:30:00
1245 Hyderabad Chennai 19:00:00
3467 Bangalore Hyderabad 12:30:00
13
DBMS LAB SWCET
TICKET Table:
Ticket_No Journey_Date
2345 2010-08-20
5678 2010-08-25
2457 2010-08-30
4679 2010-09-05
PASSENGER Table:
PNR_No Name Age Sex PPNo
1 Varuni 25 F 9003345678
2 Rahul 20 M 9247212345
3 Ajay 32 M 9989892731
4 Suni 28 F 9885252069
RESERVATION TABLE:
PNR_No Journey_Date No_Of_Seats Address Contact_No Status
1 2010-08-20 2 Anantapur 9003345678 Yes
2 2010-08-30 1 Bangalore 9989892731 No
3 2010-08-25 2 Hyderabad 9885252069 Yes
4 2010-09-05 2 Kadapa 9247212345 No
Here the reservation table is split into two tables in order to remove transitive dependency.
The following tables shows the 3rd normal form.
1. Reservation status table
PNR_No Journey_Date Status
1 2010-08-20 Yes
3 2010-08-30 No
2 2010-08-25 Yes
4 2010-09-05 No
14
DBMS LAB SWCET
2. Reservation details table
PNR_No No_Of_Seats Address Contact_No
1 2 Anantapur 9003345678
3 1 Bangalore 9989892731
2 2 Hyderabad 9885252069
4 2 Kadapa 9247212345
CANCELLATION TABLE
PNR_No Journey_Date No_Of_Seats Address Contact_No Status
3 2010-08-30 1 Bangalore 9989892731 No
Here the cancellation table is split into two tables in order to remove transitive dependency.
The following tables shows the 3rd normal form.
1. Cancellation status table
PNR_No Journey_Date Status
3 2010-08-30 No
2. Cancellation details table
PNR_No No_Of_Seats Address Contact_No
3 1 Bangalore 9989892731
15
DBMS LAB SWCET
WEEK 4 : PRACTICING DDL COMMANDS
Data Definition Language (DDL) statements are used to define the database structure or
schema. Used to create tables, altering the database, dropping tables and databases if not
required. You will also try truncate, rename commands etc.
1. Creating the database table using CREATE TABLE Command:
The “Create Table” Command is used to create objects in the database. The syntax of this
command is as follows
SQL> CREATE TABLE <table name> (Attribute1 Data type,Attribute2 Data
type,……,Attributen Data type, <Integrity-constraint t1>,<Integrity-constraint
t2>,…………………………………..,<Integrity-constraint tn>);
Example:
1. Create table for entity Bus:
2. Create table for entity Ticket:
3. Create table for Passenger:
4. Create table for Reservation:
16
DBMS LAB SWCET
5. Create table for Cancellation:
2. Altering the database tables using ALTER TABLE Command:
The “ALTER TABLE” command is used to alter the structure of the database. The syntax of the
command is as follows:
SQL>ALTER TABLE tablename ADD (column_name column_definition(datatype));
Example:
SQL> ALTER TABLE ticket ADD (Ticket_Cost number);
3. Dropping the database tables using DROP TABLE Command:
The “DROP TABLE” command is used to delete objects from the database. The syntax of the
command is as follows.
SQL> DROP TABLE tablename;
Example:
SQL> DROP TABLE bus;
4. TRUNCATE TABLE Command:
TRUNCATE TABLE Command is used to remove all records from a table, including all
spaces allocated for the records are removed. The syntax is as follows.
SQL> TRUNCATE TABLE tablename;
17
DBMS LAB SWCET
Example:
SQL> TRUNCATE TABLE passenger;
5. RENAME TABLE Command:
RENAME TABLE Command is used to rename the old table name to new table name. The
syntax is as follows.
SQL> RENAME TABLE tablename TO newtablename
Example:
SQL> RENAME TABLE reservation TO ticketreservation;
18
DBMS LAB SWCET
WEEK 5 : PRACTICING DML COMMANDS
DML Commands are used to for managing data within schema objects. Some examples:
SELECT – Retrieve data from the database
INSERT – Insert data into the table
UPDATE – Updates existing data within the table
DELETE – Deletes all records from a table, the space for the records remain
1. Select Command:
The SELECT Command is used to retrieve data from the database. The syntax of the
command is as follows.
SELECT A1,A2,……,An FROM Tablename WHERE P;
Here A1,A2,……An are the attributes and P is the condition
Example:
SQL> SELECT Bus_No , Dep_Time FROM bus WHERE Source=’Kadapa’;
2. Insert Command:
The INSERT Command is used to insert data into a relation, we either specify a tuple to be
inserted or write a query whose result is a set of tuples to be inserted. The syntax is as follows.
To insert a single value into the table the following syntax is used.
SQL> INSERT INTO tablename VALUES(value list);
To insert the multiple values into the table the following syntax is used.
SQL> INSERT INTO tablename VALUES(&value1,&value2,………,valueN);
Example:
SQL> INSERT INTO passenger VALUES (0012345678,1234,’Rahul’,25,
‘M’,9902345178);
19
DBMS LAB SWCET
SQL> INSERT INTO passenger
VALUES(&PNR_No,&Ticket_No,’&Name’,&Age,’&Sex’,&PPNo);
SQL> INSERT INTO passenger
VALUES(&PNR_No,&Ticket_No,’&Name’,&Age,’&Sex’,&PPNo);
Enter value of PNR_No: 0014568972
Enter value of Ticket_No: 3456
Enter value of Name: Varuni Enter
value of Age: 26
Enter value of Sex: F
Enter value of PPNo: 9830527846 1
row created
SQL> /
And press enter. Then, again it inserts one more tuple.
3. Update Command:
UPDATE Command is used to update the existing data within the table. In certain situations we
may wish to change a value in a tuple (row) without changing all values in the table. For this
purpose, the update statement can be used. The syntax of the command is as follows.
SQL> UPDATE tablename SET value WHERE condition;
Example:
SQL> UPDATE Reservation SET PNR_No=5 WHERE PNR_No=4;
20
DBMS LAB SWCET
4. Delete Command:
DELETE Command is used to delete all records from a table, the space for the records
remain. The syntax of the command is as follows.
SQL> DELETE FROM tablename WHERE condition;
Example:
SQL> DELETE FROM cancellation WHERE PNR_No=0012345678;
21
DBMS LAB SWCET
WEEK 6 : QUERYING
In this week you are going to practice queries (along with sub queries) using ANY, ALL, IN,
EXISTS, NOT EXISTS, UNION, INTERSECT, Constraints etc…
Practice the following Queries:
1. Display unique PNR_No of all passengers.
SQL>Select DISTINCT PNR_No from Passenger;
Output:
2. Display all the names of male passengers.
SQL>Select Name from Passenger where Sex=’M’;
Output:
3. Display the ticket numbers and names of all the passengers.
SQL>Select Ticket_No, Name from Passenger;
Output:
22
DBMS LAB SWCET
4. Display the sorted list of passenger names.
SQL> Select * from Passenger ORDER BY Name;
Output:
5. Display details of passengers sorted in ascending order by their age.
SQL>Select * from Passenger ORDER BY Age ASC;
Output:
6. Display details of passengers sorted in descending order by their age.
SQL>Select * from Passenger ORDER BY Age DESC;
Output:
23
DBMS LAB SWCET
7. Find the ticket numbers of the passengers whose names start with ‘A’ and end with ‘Y’.
SQL>Select Ticket_No from Passenger where Name like ‘a%y’;
Output:
8. Find the names of passengers whose age is between 30 and 45.
SQL>Select name from Passenger where Age >=30 and Age <=40;
Output:
(OR)
SQL>Select Name from Passenger where Age between 30 and 40;
Output:
9. Display all the passenger names beginning with ‘V’.
SQL>Select Name from Passenger where Name like ‘v%’;
Output:
24
DBMS LAB SWCET
10. Create and display a table for Cancellation where PNR_No should be unique, seatNo should
not be null and should be equal to 3 in case of mising value.
SQL>Create table Cancellation(PNR_No int UNIQUE, Contact_No varchar(10), SeatNo int NOT NULL
DEFAULT 3);
SQL>Insert into Cancellation values(8, ‘5847357884’, default);
SQL>Select * from Cancellation;
Output:
11. Display the information present in Passenger and Cancellation.
SQL> Select PNR_No, PPNo from Passenger UNION Select PNR_No, Contact_No from Reservation;
Output:
12. Display the contact numbers of passengers who are males or have reservations.
SQL>Select PPNo from Passenger where Sex=’M’ UNION Select Contact_No from Reservation
where Status=’Yes’;
Output:
13. Display the name, age and sex of passengers who have reservations.
25
DBMS LAB SWCET
SQL> Select Name, Age, Sex from Passenger where EXISTS (Select * from Reservation where
Reservation.PNR_No=Passenger.PNR_No);
Output:
(OR)
SQL>Select Name, Age, Sex from Passenger where PNR_No IN (Select PNR_No from Reservation);
Output:
14. Display the name , contact numbers of passengers who don’t have reservations.
SQL>Select Name, PPNo from Passenger where NOT EXISTS (Select * from Reservation where
Reservation.PNR_No=Passenger.PNR_No);
Output:
(OR)
SQL>Select Name, PPNo from Passenger where PNR_No NOT IN(Select PNR_No from
Reservation);
Output:
15. Display details of passengers whose PNR_No is greater than PNR_No of any passenger with
age greater than 20.
SQL>Select * from Passenger where PNR_No > ANY (Select PNR_No from Passenger where
Age>20);
Output:
26
DBMS LAB SWCET
16. Display details of passengers whose PNR_No is greater than PNR_No of all the passengers
with age greater than 20.
SQL>Select * from Passenger where PNR_No > ALL (Select PNR_No from Passenger where
Age>20);
Output:
27
DBMS LAB SWCET
WEEK-7 : QUERIES
Practice the queries using aggregate functions COUNT, AVG, MAX and MIN.
Queries
1) Display the number of passengers whose Age is greater than 25.
SQL>Select COUNT (Age) from Passenger where Age>25;
Output:
2) Display the number of reservations.
SQL>Select COUNT(*) from Reservation;
Output:
3) Display the average age of passengers.
SQL>Select AVG(Age) from Passenger;
Output:
4) Display the age of the oldest passenger.
SQL>Select MAX(Age) from Passenger;
Output:
28
DBMS LAB SWCET
5) Display the age of the youngest passenger.
SQL>Select MIN(Age) from Passenger;
Output:
6) Display the total number of seats in cancellation.
SQL>Select SUM(No_Of_Seat) from Cancellation where Status=’Yes’;
Output:
7) Display the name and age of the youngest passenger.
SQL>Select Name, Age from Passenger where Age=(Select MIN(Age) from Passenger);
Output:
8) Display the name and age of the eldest passenger.
SQL>Select Name, Age from Passenger where Age=(Select MAX(Age) from Passenger);
29
DBMS LAB SWCET
Output:
9) Display status and number of seats in reservation.
SQL>Select Status, SUM(No_Of_Seats) from Reservation GROUP BY Status;
Output:
10) Display status and number of seats which are reserved.
SQL>Select Status, SUM(No_Of_Seats) from Reservation GROUP BY Status HAVING Status=’Yes’;
Output:
11) Create and display a view on passenger table containing PNR_No, Name, Age and having age
greater than 25.
SQL>Create VIEW passd as Select PNR_No, Name, Age from Passenger where Age>25;
SQL>Select * from passd;
Output:
30
DBMS LAB SWCET
12) Delete the view passd.
SQL>Drop VIEW passd;
Output:
31
DBMS LAB SWCET
WEEK 8 : TRIGGERS
Create triggers on a table which acts on events INSERT, UPDATE and DELETE.
TRIGGER ON INSERT:
32
DBMS LAB SWCET
TRIGGER ON UPDATE:
TRIGGER ON DELETE:
33
DBMS LAB SWCET
WEEK 9: PROCEDURES
Creation of stored procedure, execution of procedure and modification of procedures.
Procedure for factorial of a number:
Procedure for reversing a string:
34
DBMS LAB SWCET
Procedure to find if a string is a palindrome or not:
Procedure to find fibonacci series:
35
DBMS LAB SWCET
WEEK 10 : CURSORS
Declare a cursor that defines a result set , open the cursor to establish the result set, fetch the data
into variables as needed from the cursor, one row at a time. Close the cursor when done.
SQL> ed cursor1.sql
DECLARE
total_rows number(2);
BEGIN
UPDATE reservtrig
SET noofseats = noofseats + 5;
IF sql%notfound THEN dbms_output.put_line('no seats
reserved');
ELSIF sql%found THEN total_rows :=
sql%rowcount*5;
dbms_output.put_line( total_rows || 'seats are added'); END
IF;
END;
SQL> @ cursor1.sql;
OUTPUT:
Old noofseats: 28
New noofseats: 33 number of
seats added: 5 Old noofseats:
45
New noofseats: 50 number of
seats added: 5 10seats are
added
PL/SQL procedure successfully completed.
36
