Question

The Wombat Film Company has cinemas in Wagga, Orange, Mildura, Bathurst, Albury and Temora. It requires a database to record its screenings and revenue.For each cinema we need to store the owner name and phone.Each cinema consists on several theatres, ranging from 6 in Bathurst and Orange, to 2 in Temora. We need to store seating capacity for each theatre. Each theatre has several screenings per day, where we record the theatre number, preview code and name (always one preview shown before the main film), the preview rating (G, PG, MA or R) and distributors name and phone, the film code and name, its rating, distributor’s name and phone, film length, number of adult and concession tickets purchased and the start time and end time of the screening.

For all screenings with a start time before 6pm (Mon to Fri excluding public holidays) the cost is adult $6 and concession $4 (equal price code B). At other times the cost is adult $10 and concession $6 (equals price code A). However, on some special occasions, such as movie releases, all tickets are $12 (equals price code C.)Some of the recorded attributes include town, theatre-no, film-name, preview-name and screening-no.  

The design of your database should be able to handle queries which could work out (not that you are required to do so):the total revenue for each film in each town;the number of screenings of each film for each town;the total number of adult and concession tickets sold each day for each town.

Answer 1

Here you need to create 4 below table with is in relationship with each other using primary key and foreign key. You can directly create these table using given code of sql file.

Cinema Table:

f Name Type Collation Attributes Null Default Comments Extra Action □ Cinema-id i) Change Drop ▼ More 1 int(11) No None □ No None Change Drop More 2 Cinema-name varchar(255) utf8 general-ci No None ChangeDrop More 3 Town varchar(255) utf8_general_ci □ 4 Owner-name No None Change G) Drop ▼ More varchar(255) utf8 general d Change Drop ▼ More No None 5 Owner Phone varchar(20 utf8 generaloi 6 No of theatres int(11) No None Change Drop More

Theatres Table

f Name Type Collation Attributes Null Default Comments Extra Action [] 1 Theatres-id> int(11) Change Drop More No None [] 2 Cinema-id ) int(11) No None Change Drop ▼ More 3 Theatre_name varchar(255) utf8_general_ci No None Change Drop ▼ More 4 capacity No None Change Drop ▼ More utf8_general_ci text

Screening Table

# Name Type Collation Attributes Null Default Comments Extra Action 1 Screening_id Change Drop More int (11) No None 2 Theatres_id Change Drop ▼ More int (11) 0 one hangeDrop More 3 Screening_ date No None Change Drop More 4 preview code varchar(255) utf8_general_ci 0 one hangeDrop More varchar(255) utf8_general_ci No None 5 preview name Change Drop More 6 preview_rating varchar(255) utf8_general_ci 0 one hange DropMore 7 distributors name varchar(255) utf8 general ci No None 8 distributors phonevarchar(20) utf8 generalci ChangeDrop More 0 one Change Drop More 9 film code varchar(255) utf8_general_ci No None Change Drop ▼ More 10 film name varchar(255) utf8_general_ci 0 one 11 film_rating hangeDrop More varchar(10) utf8_general_ci No None 12 film_length Change Drop More varchar(255) utf8_general_ci 0 one hangeDrop More 13 number_of adult text utf8-general-ci No None 14 number_of_concession text Change Drop More utf8_general_ci 0 one □ 15 start-time hange DropMore time No None ChangeDrop More 16 end time time 0 one

Revenue Table

Type Collation Attributes Null Default Comments Extra Action Change f Name Drop ▼ More No None int(11) 1 Revenue- ro op ▼More Change No None int(11) 2 Screening_l Change Drop ▼ More No None int(11) 3 Theatresld Change Drop More No None date 4 date Change Drop ▼ More No None time 5 time Drop ▼ More Change No None int(11) 6 Adult-ticket-price □ Change Drop ▼ More No None int(11) 7 Concession-ticket-price

SQL file for phpmyadmin:

Copy below text and save as film_company.sql and import it in phpmyadmin of xamp

-- phpMyAdmin SQL Dump
-- version 4.8.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: May 21, 2019 at 12:49 PM
-- Server version: 10.1.34-MariaDB
-- PHP Version: 7.2.7

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `film_company`
--

-- --------------------------------------------------------

--
-- Table structure for table `cinema`
--

CREATE TABLE `cinema` (
`Cinema_id` int(11) NOT NULL,
`Cinema_name` varchar(255) NOT NULL,
`Town` varchar(255) NOT NULL,
`Owner_name` varchar(255) NOT NULL,
`Owner_Phone` varchar(20) NOT NULL,
`No_of_theatres` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `revenue`
--

CREATE TABLE `revenue` (
`Revenue_id` int(11) NOT NULL,
`Screening_Id` int(11) NOT NULL,
`Theatres_Id` int(11) NOT NULL,
`date` date NOT NULL,
`time` time NOT NULL,
`Adult_ticket_price` int(11) NOT NULL,
`Concession_ticket_price` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `screening`
--

CREATE TABLE `screening` (
`Screening_id` int(11) NOT NULL,
`Theatres_id` int(11) NOT NULL,
`Screening_date` date NOT NULL,
`preview_code` varchar(255) NOT NULL,
`preview_name` varchar(255) NOT NULL,
`preview_rating` varchar(255) NOT NULL,
`distributors_name` varchar(255) NOT NULL,
`distributors_phone` varchar(20) NOT NULL,
`film_code` varchar(255) NOT NULL,
`film_name` varchar(255) NOT NULL,
`film_rating` varchar(10) NOT NULL,
`film_length` varchar(255) NOT NULL,
`number_of_adult` text NOT NULL,
`number_of_concession` text NOT NULL,
`start_time` time NOT NULL,
`end_time` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `theatres`
--

CREATE TABLE `theatres` (
`Theatres_id` int(11) NOT NULL,
`Cinema_id` int(11) NOT NULL,
`Theatre_name` varchar(255) NOT NULL,
`capacity` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `cinema`
--
ALTER TABLE `cinema`
ADD PRIMARY KEY (`Cinema_id`);

--
-- Indexes for table `revenue`
--
ALTER TABLE `revenue`
ADD PRIMARY KEY (`Revenue_id`),
ADD UNIQUE KEY `Screening_Id` (`Screening_Id`),
ADD KEY `Theatres_id` (`Theatres_Id`);

--
-- Indexes for table `screening`
--
ALTER TABLE `screening`
ADD PRIMARY KEY (`Screening_id`),
ADD UNIQUE KEY `Thetres_id` (`Theatres_id`);

--
-- Indexes for table `theatres`
--
ALTER TABLE `theatres`
ADD PRIMARY KEY (`Theatres_id`),
ADD KEY `Cinema_id` (`Cinema_id`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;