Assignments for Introduction to Database Management Systems
\begin
Discover why 18 million people worldwide trust Overleaf with their work.
\documentclass[a4paper]{article}
\usepackage[english]{babel}
\usepackage[utf8x]{inputenc}
\usepackage{amsmath}
\usepackage{graphicx}
\usepackage[colorinlistoftodos]{todonotes}
\title{Assignment no. 1}
\author{Emine Cambel}
\begin{document}
\maketitle
\section{Problem Statement}
Airport(\underline{Code}, City, Country, Latitude, Longitude)\\
\\
Flight(\textbf{\underline{Airline}}, \underline{FlightNumber}, \textbf{Origin}, \textbf{Destination})\\
\\
Schedule(\textbf{\underline{Airline}}, \textbf{\underline{FlightNumber}}, \underline{Date}, DepartureTime, ArrivalTime)\\
\\
Airline(Name, \underline{Abbreviation}, HomeCountry, WebSite)\\
\\
Ticket(\underline{Number}, \textbf{Airline}, \textbf{FlightNumber}, \textbf{Date}, Cost)\\
\\
In the above schema, note the following conventions:
\begin{itemize}
\item The primary key of each relation is \underline{underlined}.
\item Foreign keys are shown in \textbf{boldface}. Specifically:
\begin{itemize}
\item The attribute \textbf{Airline} in the Flight relation is a foreign key which references the Airline
relation.
\item The attributes \textbf{Origin} and \textbf{Destination} in the \textbf{Flight} relation are each foreign keys which reference the Airport relation.
\item The attribute pair \textbf{(Airline, FlightNumber)} in the Schedule relation is a foreign key which references the \textbf{Flight} relation.
\item The attribute triple \textbf{(Airline, FlightNumber, Date)} in the Ticket relation is a foreign key which references the \textbf{Schedule} relation.
\end{itemize}
\end{itemize}
\section{Dissolved Queries}
\begin{enumerate}
\item
Find the code, latitude, and longitude of each airport which located in a city named Paris or Berlin.\\
\\
The \textbf{SELECT} statement is used to select data (here: Code, Latitude, Longitude) \textbf{FROM} database (Airport) with the Condition (each airport which is located in a city named Paris or Berlin).\\
The \textbf{WHERE} clause is used to extract only those records that fulfill a specified criterion (here: City='Paris' OR City='Berlin').
\textbf{A.X} is a function that describes a choosen column X in table A (here: Type Aiport)
\begin{quote}
\textbf{SELECT} A.Code, A.Latitude, A.Longitude\\
\textbf{FROM} Airport A\\
\textbf{WHERE} A.City='Paris' OR A.City='Berlin';
\end{quote}
\item
Find the names of those airlines which have either a flight with origin the airport with code TXL or a flight with origin the airport with code SXF.\\
\\
The \textbf{DISTINCT} keyword is used to return only distinct (different) values.
We have a look at the Flight table where we can get the origin airport of each flight. This origin flight we compare with the code 'TXL' or 'SXF'. \\
We use the \textbf{JOIN} clause to combine the rows from two or more tables. This is based on a common field between them.\\
We keep in mind, that the attribute Airline in the Flight relation is a foreign key which references the Airline relation. The primary key in Airline table is Abbreviation. So because of that we notice that the Airline column in the Flight table refers to the Abbreviation in the Airline table. In order to this we can match (\textbf{JOIN.. ON..}) the Abbreviation column in Airline with the Airline column in Flight. We join on this in the Airline table so that we can \textbf{SELECT} the names of the Airline table that fulfill the conditions.
\begin{quote}
\textbf{SELECT DISTINCT} A.Name \\
\textbf{FROM} Flight F \\
\textbf{JOIN} Airline A \\
\textbf{ON} F.Airline = A.Abbreviation \\
\textbf{WHERE} F.Origin = 'TXL' \textbf{OR} F.Origin = 'SXF';
\end{quote}
\item
Find the names of those airlines which have both a flight with origin the airport with code TXL and a flight with origin the airport with code SXF. (Hint: This may be done without using embedded subqueries by using the INTERSECT directive. Since MySQL does not support this directive, it is sufficient to give a solution which works only with PostgreSQL.)\\
\\
SELECT Name\\
FROM\\
WHERE Code='TXL' AND Code='SXF'\\
\\
\item
Find the names of all airlines which have a scheduled flight on the date 2016-11-12.\\
\\
This is the same kind of querie like in $2$, just with different information.
\begin{quote}
\textbf{SELECT DISTINCT} A.Name\\
\textbf{FROM} Schedule S\\
\textbf{JOIN} Airline A\\
\textbf{ON} S.Airline=A.Abbreviation\\
\textbf{WHERE} S.Date='2016-11-12';\\
\end{quote}
\item
Find the airport code, city, and country for all airports which have a departure for which some ticket with ticket number less than 200 costs more than 4000.\\
%if(s.DepartureTime != NUll && t.nummer<200 &&t.cost>4000){return a.code +a.country +a.city }}
\\
SELECT DISTINCT Code, Country, City
FROM Schedule S, Ticket T
JOIN Airport A
ON
WHERE DepartureTime IS NOT NUll AND T.Number<200 AND T.Cost>4000
\item
Find the names of those airlines which have a flight whose destination is an airport which is located in Germany or France\\
\\
SELECT A.Name\\
FROM Airline A\\
WHERE\\
\\
\item
Find the names of those airlines with either the string “Air” or else the string “Luft” (both case insensitive) in their names.\\
\\
The \textbf{LIKE} operator is used to search for a specified pattern in a column.
\begin{quote}
\textbf{SELECT} A.Name \\
\textbf{FROM} Airline A \\
\textbf{WHERE} A.NAME LIKE '\%Luft\%' OR A.NAME LIKE '\%Air\%';
\end{quote}
\item
Reduce the price of all tickets issued by Scandinavian airlines for the interval 2016-11-01
to 2016-11-20 inclusive by 20$\%$. (Note: You may use the code “SK” in your query. It is not necessary to pattern match on “Scandinavian”)
SELECT\\
FROM\\
WHERE\\
\\
\item
Find the codes of those airports located in Berlin, Germany which do not have any scheduled departures.\\
\\
SELECT Code\\
FROM Schedule S\\
JOIN Airport A
ON
WHERE S.DepartureTime is Null\\
\\
\item
Find the flights for the airline with code SK which are scheduled on 2016-11-12 with a departure time before noon (12:00). In addition to the flight number, give the airport codes for both the origin and the destination.\\
\\
SELECT\\
FROM\\
WHERE\\
\\
\end{enumerate}
\end{document}