DBMS Case Study B3.pptx

Case study

1. CASE STUDY Pavan kumar Dola Sai Priya Putta Sruthi Khyathi Eshman

2. Objective: This case study is aimed to provide the customers facility to book tickets for cinema halls online,through which they can book tickets Anytime,anywhere. E-ticket system is basically made for providing the customer an anytime and anywhere service for booking the seat in the cinema hall and to gather information about the movies online. The user can easily be able to know about the movies released and then make the choice. And we will also provide some process description and data dictionary.Admin can use the system to insert and delete data (e.g. film description, time table) which will update the webpage (webpage are dynamic page, changing according to the data in database). Also, admin can check the statistic information from the system.

3. The main purpose of our online ticket booking system is to provide another way for the customer to buy cinema ticket. It is an automatic System. After inserting the data to database, staff need not to due with the order receive through the system. In fact, there is similar system on the internet, but there is no refund method found in the existing System. This system is basically aimed to provide the customer the complete information of the movie, according to which the customer can book the tickets and the refund facility provides more flexibility to the system. PURPOSE:

4. Data Modeling Design

5. A)DATA REQUIREMENTS: ->Attributes ->Entities ->Relationships ->Primary attributes

6. ER DIAGRAM:

7. DESIGN OF RELATIONAL DATABASE SCHEMA DEVELOPMENT:

8. Design of Database Table: The data to be used in the system are stored in various tables. The number of tables used & their structure are decided upon keeping in mind the logical relation in the data available. The database design specifies: 1. The various tables to be used 2. Data to store in each table 3. Format of the fields & their types

9. User Information: Booking: DATA STRUCTURE (Tables in the database):

10. SQL CODE: create table theatre(movie_name varchar(20), screen varchar(20), seats number(20)) create table customer(screen varchar(20), showtimings varchar(20), tid number(20)) insert into theatre values('Ghost', 'screen 1', 99) insert into theatre values('Avengers', 'screen 2', 120) insert into theatre values('Gravity', 'screen 3', 120) insert into theatre values('Conjuring', 'screen 4', 120) insert into theatre values('Major', 'screen 5', 120) insert into theatre values('IT', 'screen 6', 120)

11. insert into customer values('screen 1', '7:00 pm', 123) insert into customer values('screen 1', '7:00 pm', 789) insert into customer values('screen 1', '9:00 pm', 997) insert into customer values('screen 2', '9:00 am', 546) insert into customer values('screen 2', '9:00 am', 342) insert into customer values('screen 3', '10:00 pm', 142) insert into customer values('screen 3', '9:00 am', 768) insert into customer values('screen 3', '9:00 am', 765) insert into customer values('screen 4', '10:00 pm', 321) insert into customer values('screen 4', '10:00 pm', 874) insert into customer values('screen 5', '7:00 pm', 796, 122) -- printing the tables before performing the operations on the table select * from theatre select * from customer -- altering the name of the table alter table theatre rename to movies select * from movies -

12. -- altering the table by adding a column into the table alter table movies add theatre_name varchar(20) select * from movies -- altering the table by adding a column into the table customer alter table customer add price varchar(20) select * from customer -- altering the table by dropping the price column as the datatype is wrong alter table customer drop column price select * from customer

13. -- altering the table by adding a column for the price into the table customer alter table customer add price number(20) select * from customer -- update the value of the price for the tickets update customer set price = 112 select * from customer -- updating the values of the added column of the table update movies set theatre_name = 'INOX' select * from movies

14. -- queries -- displaying the details of movies in screen 6 select * from movies where screen = 'screen 6' -- displaying the sum of the amount collected by selling the tickets using the agregate function SUM select sum(price) from customer -- displaying the details of movies from the movies database where the show timing is 7:00 pm select * from movies where screen in (select screen from customer where showtimings = '7:00 pm') -- displaying the ticket ids where the screen chosen is screen 3 select tid from customer where screen = 'screen 3'

15. CONCLUSION: Nowadays, traditional reservation ways of cinema ticketing is dying. It’s new age where technology dominates human life. With the software and technological devices, exceptions are reduced and even terminated. Also, people prefer easy, quick and safe way for every part of his life. This project is designed to meet the requirements of a cinema ticket booking system. It has been developed in PHP and the database has been built in My SQL server keeping in mind the specifications of the system. In our project: with this cinema ticketing system; cinema companies can satisfy comfortable facilities to their customers. The relationship between cinema manager, employee, and customer satisfy a good communication to complete ticketing process. With this platform we developed, we are hoping to reduce time wasting, avoid misunderstandings, provide easy data flow, customer pleasure, and less hard work. We believe that we have accomplished our goals and satisfied with the code we developed.