ACKNOWLEDGEMENT We would like to recognize the people who helped me to reach this far our lecture Bajuna Salehe, who provided us with lectures, Mr Abdallah Siphy , Mr Christopher Rabi our tutors chance for their guidance on tutorials. Lastly we highly appreciate the participation and the cooperation of our team, we are proud of ourselves.
i
ABSTRACT Since we have being given a chance to assume that we are hired at airport as Database Administrator thus we had to design and develop airport’s database as a system that will manage and control all the database activities conducted at the airport. The following steps were performed to reach this far, first task was the collection of all necessary information concerning the air, then showing the entity relationship diagram from the information collected on the airport, then translating ER model into the logical design showing the relational schemas and integrities then developing the database using MySql together with visual basic 6.0 then developing the front end application to perform the required operations.
ii
CHAPTER ONE 1 INTRODUCTION 1.1 INTIAL TASKS This an Airport Flights Database System project report gives a brief description on the tasks worked out, development of ERD from the given scenario whereby we had seven entities Employee, Traffic controller, Technician, Test, Airport, Model, Airplane with their attributes. Afterward we were able to build the database using MySQL whereby we had twelve tables that we developed from the ERD which had seven entities thus by breaking many to many relationships we were able to come up with twelve tables. Then on developing the front end application we used Visual basic 6.0. Thus the other task was to write and apply the SQL queries that could answer the provided questions. 1.2 Purpose The purpose for this project is to design a database system for Airport Flights and to produce the front-end applications together with the required queries. 1.3 Objectives and Outcomes To come with the database-driven front end applications, queries or requests on the front end application.
1
CHAPTER TWO 2.0 INTRODUCTION This chapter gives a dramatically presentation of the scenario provided through the use of the Entity relationship diagram, together with the relationship diagram showing the relationships involved. 2.1 ENTITY RELATIONSHIP DIAGRAM CONCEPTUAL DESIGN
2
2.2 RELATIONSHIP DIAGRAM
3
2.3 REALATIONAL SCHEMA Accommodation (model_no,air_port_name) Air_plane( RegNo, model_no) Air_port( air_port_name) Employee( SSN, salary, air_port_name,union_member_no)
country,
city,
street,
house_no,e_name,
Expert ( TSSN, model_no) Model ( model_no,model_name,capacity, weight) Phone( Tphone_no,TRphone_no,TSSN, TRSSN) Technician (TSSN,Tname,country,city,house_no,street,salary,union_member_no) Test (TAA_test_no,test_name, max_test_score) Testing (testing_date, testing_hours,testing_score,TAA_test_no,RegNo,TSSn) Traffic_controller( TRSSN, Trfname, country, city,house_no, street, salary, union_member_no, medical_examination_date) Administrator( username, password)
Accommodation ATTRIBUTE model_no air_port_name
DATA TYPE Varchar Varchar
LENGTH 30 30
KEY Primary key Primary key
DATA TYPE Varchar Varchar
LENGTH 30 30
KEY Primary key
Administrator ATTRIBUTE username Password
4
Air_plane
ATTRIBUTE RegNo model_no
DATA TYPE Varchar Varchar
LENGTH 15 30
KEY Primary key
DATA TYPE Varchar
LENGTH 30
KEY Primary key
DATA TYPE Varchar Varchar Varchar Varchar Varchar Varchar Varchar Varchar Varchar
LENGTH 15 13 10 30 30 30 30 30 30
KEY Primary key
DATA TYPE Varchar Varchar
LENGTH 15 15
KEY
DATA TYPE Varchar Varchar Varchar Varchar
LENGTH 30 30 15 15
KEY Primary key
Air_port ATTTRIBUTE air_port_name Employee ATTRIBUTE SSN salary union_member_no country city street house_no e_name air_port_name Expert ATTRIBUTE TSSN model_no Model ATTRIBUTE model_no model_name capacity Weight
5
Phone ATTRIBUTE Tphone_no TRphone_no TSSN TRSSN
DATA TYPE Varchar Varchar Varchar Varchar
LENGTH 15 15 15 15
KEY Primary key
DATA TYPE Varchar Varchar Varchar
LENGTH 15 30 4
KEY Primary key
DATA TYPE Varchar Varchar Varchar Varchar Varchar Varchar Varchar Varchar
LENGTH 15 30 30 30 15 30 13 10
KEY Primary key
DATA TYPE Date time varchar varchar varchar varchar
LENGTH
KEY
Test ATTRIBUTE TAA_test_no Test_name max_test_score Technician ATTRIBUTE TSSN Tname country city house_no street salary union_member_no Testing ATTRIBUTE testing_date testing_hours testing_score TAA RegNo TSSN
3 15 15 15
6
Traffic_controller ATTRIBUTE TRSSN Trfname country city house_no street salary union_member_no Medical_examination_dat e
DATA TYPE Varchar Varchar Varchar Varchar Varchar Varchar Varchar Varchar date
LENGTH 15 30 30 30 15 30 30 10
KEY
7
CHAPTER THREE 3 IMPLEMENTATION 3.0 Database Implementation In Database implementation we includes coding and testing database processing programs, completing database documentation, and installing databases and converting data, as necessary, from prior systems. Here, at last, is the point in the systems development life cycle for which our thought have been preparing. Our prior activities, conceptual data modeling and logical and physical database design are necessary previous stages. 3.1 Coding and Testing The coding we are using are truly compiled well and tested well. Our system runs well and performs queries well and with meaningful output. We are truly sure that this database system will give the desired needed and wanted by the system administrator wants. 3.2 Database Processing Programs We have use MySQL database as our database engine and storing our data. Also we have use Visual Basic 6.0 for designing our front-end application interface. We also assure that by deploying these programs we will come with a well represented Flight Database System. An administrator wills do all queries through the front-end application. 3.3 Database Installation We install our database in the laptop which runs Windows 7 Operating Systems. At the end of implementation, we expect a functioning database system that meets administrators’ information requirements. After that, the system will be put into production use, and database maintenance will be necessary for the life of the system.
8
3.4 Major SQL Queries
FORM 1: select * from model where model_no= " & "'" & LTrim(keyword)
9
FORM 2: select m.model_name,m.model_no,a.RegNo,m.capacity,m.weight from model
m,air_plane a where m.model_no=a.model_no
10
FORM 3:select * from test
FORM 4:select count(RegNo) from air_plane
11
FORM 5: select RegNo,model_no from air_plane
FORM 6: select * from technician
12
FORM 7: select * from traffic_controler
13
FORM 8: select distinct t.testing_date,t.testing_hours,t.testing_score,s.TAA_test_no,s.test_name
from testing t, test s where testing_date between ('2010/01/01') and ('2010/12/31')
14
FORM 9: select * from test
FORM 10: select m.model_name,m.model_no,m.capacity,m.weight from model m where
m.weight>5000 15
FORM 11: SHOWS THE SUBSCRIBING LINK
16
FORM 12: FROM BOEING LINK
FORM 12: Airbus flight available for subscribing.
17 3.5 Front End Application The front end application we developed for our system comprises of eighteen forms. Form one (as home page) and form two is for login by the administrator.
FORM 13: front end application
FORM 14: ADMINISTRATOR LOGIN FORM
18 Form three or Operation form is where SQL command buttons are. These commands are for searching airplane model, subscribing new flight model, adding new technicians and air traffic controllers, display the available flights, display tests that are performed periodically, to calculate number of airplanes that are served by the airport and also the flight that has decided not to land in addition to that, to calculate number of tests performed to particular flight per month. An administrator what he or she does is just press the button to perform operation he or she wants.
FORM 15: OPERATION FORM
Consider when an administrator press Add new command, the two forms will pop up, first for adding employee particulars and the next form for adding airplane particulars such as of Model, Test, Expert, Testing, Airplane, Airport. Employee particulars are such as Technician, Traffic Controller and Phone.
19
FORM 16: Employee particular form
FORM 17: Aiplane particulars from.
20
CHAPTER FOUR CONCLUSION Thus due to the design and development of airport’s database as a database administrator we were able to organize all the information necessary about the airplanes stationed and maintained at the airport. The complete and proper implementation of all the required detail will lead to the effective management and control of the most airport facing problems on airplane management.
21
Contents ABSTRACT………………………………………………………………………………………………… ……………………………………………….iACKNOWLEDGMENT…………………………………… …………………………………………………………………………………………..i CHAPTER ONE ……………………………………………………………………………………………………………… ……………………....1 1 INTRODUCTION........................................................................................................ 1 1.1 INTIAL TASKS...................................................................................................... 1 1.2 Purpose.............................................................................................................. 1 1.3 Objectives and Outcomes.................................................................................. 1 CHAPTER TWO............................................................................................................. 2 2.0 INTRODUCTION.................................................................................................. 2 2.1 ENTITY RELATIONSHIP DIAGRAM CONCEPTUAL DESIGN........................................2 2.2 RELATIONSHIP DIAGRAM....................................................................................... 3 2.3 REALATIONAL SCHEMA.......................................................................................... 4 3 IMPLEMENTATION..................................................................................................... 8 3.0 Database Implementation.................................................................................. 8 3.1 Coding and Testing............................................................................................. 8 3.2 Database Processing Programs..........................................................................8 3.3 Database Installation......................................................................................... 8 3.4 Major SQL Queries.............................................................................................. 8 FORM 1................................................................................................................. 8 FORM 2............................................................................................................... 10 FORM 3............................................................................................................... 11 FORM 4............................................................................................................... 11 FORM 5............................................................................................................... 12
FORM 6............................................................................................................... 12 FORM 7............................................................................................................... 13 FORM 8............................................................................................................... 14 FORM 9............................................................................................................... 15 FORM 10............................................................................................................. 15 FORM 11............................................................................................................. 16 FORM 12............................................................................................................. 17 FORM 13............................................................................................................. 18 3.5 Front End Application....................................................................................... 18 FORM 14............................................................................................................. 18 FORM 15............................................................................................................. 19 FORM 16............................................................................................................. 20 FORM 17............................................................................................................. 20 CHAPTER FOUR......................................................................................................... 21 CONCLUSION.......................................................................................................... 21
