Question

My SQL Data Model

Delta Airlines wants to track certain information about its fleet of planes. Delta’s planes go in for servicing and checks every 6 months. These checks are conducted by trained maintenance engineers. During this process there can be one or several issues related to the plane that are identified and noted. Delta wants to track information about the planes, the issues identified with the plane and the maintenance engineer who has identified these issues.

Engineers may identify more than one issue in a plane inspection. Every issue has an issue code and description. As issues are identified by engineers, they are logged. A place can have multiple issues associated with it and multiple planes can have similar issues. When an engineer identifies an issue with the place, the or she identifies the issue, the sub-system of the plane it is associated with and the date and time is was identified with the plane. Multiple engineers may work on a plane inspection but an engineer is assigned to a single plane inspection at a time.

Delta wants to track the following information about the plane, the make, the model, the acquired date, the supplier organization, its last service date and the last maintenance engineer. Delta also wants to track information about the issue including the date the issue was identified, a description of the issue and other details that are deemed pertinent. Information about the maintenance engineer are also recorded including their name, the date/time they started work, the date/time they stopped work, their contact information and other details about the employee.

Answer 1

If you are using a Linux/Mac machine then you can use the terminal to log in or else you can use the MYSQL workbench in windows

Enter "mysql -u {username} -p" and then in the password prompt enter the password

inside the mysql prompt you can follow the steps below to create a database create multiple tables to store all the data and we can connect the tables to one another for better accessibility.

1.create database DELTA_PLANES;

2.use DELTA_PLANES; //This is to tell mysql engine that we are using this database

3.create table Employee(Emp_no int primary key,Emp_name varchar(30),Salary int,Mob_no int(10),Address varchar(40));

This creates table Employee to store their detail.

Here primary key is a constraint which allows only unique values in that particular column and cannot be null.int refers to the datatype which is integer and varchar refers to character but it is variable i.e if we utilise less space than it is indicated when the table is create then the memory not utilised is deallocated.

4.create table Planes(Plane_no int primary key,Plane_name varchar(30),Model varchar(20),Supplier varchar(30),last_service date,Emp_assigned varchar(30));

This creates a table Planes to store all the details of the plane such as name,plane_no,model,etc.

5.create table Issue(Plane_no int primary key,Issue varchar(30),Description varchar(150),Emp_Assigned varchar(30),started_work date,stopped_work date,Emp_No int,foreign key(Plane_no) references Planes(Plane_no),foreign key(Emp_No) references Employee(Emp_no));

This create a table Issue to keep track of the Issue,its description and the employee assigned,etc.

One of the thing to notice here is the foreign key constraint which connects column Emp_no and Plane_no in Issue table to the Emp_no and Plane_no column in the Employee table and Planes table respectively.

We can then insert data into the table using "insert into {tablename} values(args)" where the number of arguments should be equal to the no of columns

We can also access data from the database using select command ex: "select * from {tablename}" fetches all the data from the table.