SQL Database Management Exercises

This document contains a set of solved SQL assignments covering various concepts such as table creation, data insertion, querying specific information, and understanding relationships between tables. The exercises demonstrate practical applications of SQL commands and provide examples of how to manage and interact with relational databases.

Database System & Design

TABLE OF CONTENTS
STAGE 1.........................................................................................................................................1
1.1 Selection of a suitable case for database design and implementation ..................................1
1.2 Enhanced Entity Relation Diagram.......................................................................................1
STAGE 2..........................................................................................................................................4
2.1 Logical database diagram......................................................................................................4
2.2 Tables using Oracle DBMS..................................................................................................5
2.3 Indexes on tables...................................................................................................................9
2.4 Data population...................................................................................................................10
For customer..............................................................................................................................10
For employee.............................................................................................................................10
2.5 SQL Query writing..............................................................................................................11

STAGE 1
1.1 Selection of a suitable case for database design and implementation
Redberry is a hotel who wants to involve a new database system that can help them in
managing the customer's data more efficiently and appropriately. The purpose of Redberry is to
provide a wide range of products and services to the customers, satisfying their basic needs and
requirements. Its target is to expand the business in different city's, making their own landmark
and hence increasing the productivity (Coronel and Morris, 2016). Although the company stores
the information and data of all the employees but does not have any system for storing the
information of the customers. As it has been made mandatory, so the hotel has decided to involve
a new database system that can help them in storing the information of all the customers. The
database will help them in giving a notification if the customer of the same name and details
have been lived there previously. Also, it is essential for maintaining safety procedures and it
comes under the rules of the government. So, the company has decided to implement a new
database design so that it can help them in an increased rate of productivity.
1.2 Enhanced Entity Relation Diagram
An enhanced entity relationship diagram is considered as a conceptual data model that
helps in adding various extensions to the entity relationship diagram that has been already made
and is original (Brodie and Mylopoulos, 2012). Enterprise rules are classified as rules that helps
in determining the relationship between various different entities of the organisation. It helps in
determining the degree of relationship, domains that have been attributed and the most
important, the membership class. As the hotel is implementing a new database that can help them
in storing the information about their customer's as well is represented as following :
1

2

3

STAGE 2
2.1 Logical database diagram
Normalisation in database is considered as a process in which different tables are being
made of some specific attributes and tables. The main purpose of involving normalisation is to
reduce the data redundancy and hence, it improves the data integrity. For a better handling of the
database, normalisations holds a very essential role. It also ensures no loss of data.
An example of normalisation can be seen as below :
C-id CName CAddress CNoOfDays
101 Alex London 4
102 Shawn England 2
103 Spiry Webber 1
There are certain anomalies in normalisation which are discussed as under :
Updation anomaly
If some customer want to make changes in the number of days of stay, that means if their
plan or trip has been extended and they want to extend their stay in the hotel, the hotel authorities
4

will make update Caddress column in every row (Domínguez and Jaime, 2010). It is important as
well because if the changes are done in actual but not updated in the database, there will be data
inconsistency.
Insert anomaly
If a customer has booked for 4 days and they have decided to move out on the third day
itself, in that case, the management of hotel is allowed for insertion anomaly. That day, they can
allow any other party to stay there because it has become vacant before time.
Deletion anomaly
If some customer has registered their booking, but has not arrived, so after a few hours,
the management will delete that row, thus it will delete the entire booking record of the specific
customer.
Logical database diagram is represented as under :
5

2.2 Tables using Oracle DBMS
The tables for the customer as well as employee's data are represented as under :
create table Employee (
ENAME CHAR (20) NOT NULL,
EAGE FLOAT NOT NULL,
EGENDER CHAR (30) NOT NULL,
EADDRESS VARCHAR (20) NOT NULL
);
INSERT INTO Employee (ENAME, EAGE, EGENDER, EADDRESS)
VALUES ('SHAWN', 32.5, 'MALE', 'OPSTREET');
select * from Employee
Create table Ccustm (
CNAME CHAR (20) NOT NULL,
6

CAGE FLOAT NOT NULL,
CGENDER CHAR (30) NOT NULL,
CADDRESS VARCHAR (20) NOT NULL
);
INSERT INTO Ccustm ( CNAME, CAGE, CGENDER, CADDRESS)
VALUES ('SHRIKANT', 22.5, 'MALE', 'FGSTREET');
select * from Ccustm
2.3 Indexes on tables
After defining a primary key constraint, a unique index is automatically created. A
primary key is considered as a special column in a table of the database that stores a unique and
different value for each row. It cannot consist of any null values. So, new value should be there
for each row.
Taking Employee's table
7

S.no (Primary
key)
Ename Eage Egender Eaddress
1 Shawn 32.5 Male OP Street
2 Pretty 30.5 Female LK Street
Indexes are being used for maximizing the performance of the queries. A basic example of
indexing a specific table is as under :
create table Employee (
ENAME CHAR (20) NOT NULL,
EAGE FLOAT NOT NULL,
EGENDER CHAR (30) NOT NULL,
EADDRESS VARCHAR (20) NOT NULL
);
INSERT INTO Employee (ENAME, EAGE, EGENDER, EADDRESS)
VALUES ('SHAWN', 32.5, 'MALE', 'OPSTREET');
select * from Employee
CREATE CLUSTERED INDEX i1 ON t1 (col1);
There are two types of indexing that can be done and these are the clustered and the non
clustered.
Clustered indexes
These indexes help in sorting and then storing the rows containing data in the table.
Non clustered indexes
These are different from the clustered ones as they have their own specific structure,
rather than rows and columns.
2.4 Data population
For customer
Create table Ccus (
CNAME CHAR (20) NOT NULL,
CAGE FLOAT NOT NULL,
CGENDER CHAR (30) NOT NULL,
CADDRESS VARCHAR (20) NOT NULL
8

);
INSERT INTO Ccus ( CNAME, CAGE, CGENDER, CADDRESS)
VALUES ('SHRIKANT', 22.5, 'MALE', 'FGSTREET');
INSERT INTO Ccus (CNAME, CAGE, CGENDER, CADDRESS)
VALUES ('JULIE', 23.5, 'FEMALE', 'GVSTREET');
INSERT INTO Ccus ( CNAME, CAGE, CGENDER, CADDRESS)
VALUES ('AMILY', 33.5, 'FEMALE', 'PVSTREET');
INSERT INTO Ccus ( CNAME, CAGE, CGENDER, CADDRESS)
VALUES ('SHERRY', 28.5, 'MALE', 'GSSTREET');
INSERT INTO Ccus ( CNAME, CAGE, CGENDER, CADDRESS)
VALUES ('JIPSY', 39.5, 'FEMALE', 'VTSTREET');
select * from Ccus
The result for this specific code is as under :
For employee
create table Emp (
ENAME CHAR (20) NOT NULL,
EAGE FLOAT NOT NULL,
EGENDER CHAR (30) NOT NULL,
EADDRESS VARCHAR (20) NOT NULL
);
INSERT INTO Emp (ENAME, EAGE, EGENDER, EADDRESS)
VALUES ('HAWNY', 23.5, 'MALE', 'PSTREET');
INSERT INTO Emp (ENAME, EAGE, EGENDER, EADDRESS)
VALUES ('MEENU', 37.5, 'FEMALE', 'IOSTREET');
9

INSERT INTO Emp (ENAME, EAGE, EGENDER, EADDRESS)
VALUES ('HONEY', 32.5, 'MALE', 'TYSTREET');
INSERT INTO Emp (ENAME, EAGE, EGENDER, EADDRESS)
VALUES ('JULIE', 36.5, 'FEMALE', 'CVSTREET');
INSERT INTO Emp (ENAME, EAGE, EGENDER, EADDRESS)
VALUES ('RICHA', 24.5, 'FEMALE', 'OPSTREET');
select * from Emp
2.5 SQL Query writing
create table hotel (
CUSTOMERS CHAR (20) NOT NULL,
EMPLOYEES CHAR (30) NOT NULL,
STAFF CHAR (20) NOT NULL
);
INSERT INTO hotel (CUSTOMERS, EMPLOYEES, STAFF)
VALUES ('RAJ', 'SIMRAN', 20);
select CUSTOMERS from hotel
In the code above, some properties that are added are NOT NULL, selection of particular table
column and a sub query. The result is depicted as under :
10

11

REFERENCES
Books and Journals
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Brodie, M.L. and Mylopoulos, J. eds., 2012. On knowledge base management systems:
integrating artificial intelligence and database technologies. Springer Science &
Business Media.
Vo, H.T., Wang, S., Agrawal, D., Chen, G. and Ooi, B.C., 2012. Logbase: A scalable log-
structured database system in the cloud. Proceedings of the VLDB Endowment. 5(10).
pp.1004-1015.
Elmasri, R. and Navathe, S.B., 2015. Fundamentals of database systems. Pearson.
Domínguez, C. and Jaime, A., 2010. Database design learning: A project-based approach
organized through a course management system. Computers & Education. 55(3).
pp.1312-1320.
12