Question 1
Question 2
- Functional dependencies include:
- CinemaID, CinemaName, Email
- BranchID, BranchAddress, BranchPhoneNumber
- Screen, ScreenName, No.of.Seats, SessionID, Date
- MovieStartTime, MovieID, Name, Year, Language, LengthInMinutes
- The candidate keys include:
- CinemaID,
- BranchID
- SessionID
- MovieID
- The data provided is in Fist normal form (1NF).
- Conversion from 1NF to BCNF
- Tables based on normalized form using SQL:
CinemaID Int NOT NULL,
CinemaName VarChar (30),
CREATE TABLE Branch (
BranchID Int NOT NULL,
BranchAddress VarChar (30),
BranchPhoneNumber Varchar (30) );
CREATE TABLE Screen (
ScreenID Int NOT NULL,
ScreenName VarChar (30),
NumberofSeats Int NOT NULL );
CREATE TABLE Session (
SessionID Int (3),
MovieStartTime Varchar (30) );
CREATE TABLE Movie (
MovieID Int NOT NULL,
Language Varchar (10),
LengthInMinutes Int (3) );
Question 3
- SQL query that displays the CinemaID, CinamaName and email of Cinema that have more than 2 branches or a branch in the Suburb of ‘Greyjoy’
WHERE (Branch>2) OR (Branch LIKE “%GreyJoy%”);
- SQL query that displays BranchAddress, CinemaName and PhoneNumber of the branch that has postcode that starts with 2 or 6
WHERE (Branch LIKE ‘2%) OR (Branch LIKE ‘6%);
- SQL query that displays CinamaName, BranchAddress, Session Date(dd-month-yyyy format), Movie StartTime and date where any French movie is being screened. Sort the result by CinemaName descending
WHERE Language = “French”
ORDERBY CinemaName DESC;
- SQL query that displays CinamaName, BranchAddress, ScreenName, MovieName, YearReleased and Language that screens either a Spanish Movie or a German movie in afternoon or evening session
WHERE (Language = ‘Spanish’) OR (Language = ‘German’) AND (Session = ‘Afternoon’) OR (Session= ‘Evening’)
- SQL query that displays CinamaName, BranchAddress and the number of screens that are scheduled to screen ‘The Hundred-Foot Journey’
WHERE CinemaName = “The Hundred Foot Journey”
