BIT231 Database Systems Assignment: Normalization and Relational Model

This document presents a complete solution to a database systems assignment, addressing key concepts such as business rules, entity-relationship diagrams (ERD), data dictionaries, and the relational data model. The assignment explores the design and implementation of a database, beginning with the definition of business rules for a hypothetical airline and country database. It proceeds to create ER diagrams, define entities, attributes, and relationships, and construct a data dictionary. The solution also demonstrates the application of normalization techniques, specifically the conversion of a relation to the first, second, and third normal forms (1NF, 2NF, and 3NF). The assignment includes detailed tables and examples to illustrate each step of the database design process. References to relevant database concepts are provided.

BIT231 Databases Systems
Student ID: 1452842
Student Name: Surpreet Singh

Table of Contents
Question 1...................................................................................................................................................3
1. Business Rules.................................................................................................................................3
2. Entities, Attributes and Relationships..............................................................................................4
3. Entity Relationship Diagram............................................................................................................5
4. Data Dictionary................................................................................................................................6
5. Relational Data Model.....................................................................................................................7
Question 2...................................................................................................................................................8
1. Countries Relation...........................................................................................................................8
2. First Normal Form..........................................................................................................................10
3. Second Normal Form.....................................................................................................................11
4. Third Normal Form........................................................................................................................12
References.................................................................................................................................................15

Question 1
1. Business Rules
1. MPoly Airline flight is uniquely identified by the flight number with date.
2. Each flight also records its actual arrival and departure time.
3. Passenger who flow on the flight is uniquely identified by passenger number.
4. Passenger’s details are also recorded like his or her name, address and telephone number.
5. Company records the details like fare and date of reservation of the passenger.
6. Each passenger may have taken 1 or more flights.
7. Every flight can carry many passengers.
8. Each pilot is uniquely identified by Pilot/Employee number.
9. Each pilot’s details are recorded like first name, last name, date of birth and hire date.
10. Pilot may or may not have flown the flights.
11. Airplane is uniquely identified by serial number.
12. Airplane also records manufacturer name, passenger capacity and built year.
13. Each flight uses one airplane on a particular whereas each airplane may or may not be flown
on more than one flight and date.
14. Airplane’s maintenance procedure is uniquely identified by procedure number.
15. Maintenance procedure also records procedure name, frequency of procedure.
16. Maintenance of each airplane records date of procedure, maintenance location and
duration.

2. Entities, Attributes and Relationships

3. Entity Relationship Diagram

4. Data Dictionary

5. Relational Data Model

Question 2
1. Countries Relation
Citizen
CitizenID CitizenName DOB CountryBorn
1 Eric Ambler 3/25/1976 Canada
2 Raymond Chandler 2/12/1973 USA
3 Jung Chang 1/1/1960 New Zealand
4 James Clavell 9/4/1987 Singapore
5 Charles Dickens 7/13/1965 Australia
6 John Ellroy 8/28/1973 India
7 Janet Evanovich 11/11/1973 UK
8 Ian Fleming 9/25/1977 Switzerland
9 Frederick Forsyth 12/25/1986 Japan
10 Graham Greene 1/1/1960 South Korea
11 Dashiell Hammett 6/29/1972 Singapore
12 Donna Leon 5/20/1988 New Zealand
13 Elmore Leonard 4/24/1956 Australia
14 Christopher Lloyd 6/6/1967 USA
15 Paul Lund 12/3/1987 UK
16 Alan Moorehead 4/19/2000 Singapore
17 Haruki Murakami 7/6/1976 USA
18 David Rooney 3/5/1967 Australia
19 Damon Runyon 8/23/1987 UK
20 Hunter Thompson 10/29/1987 New Zealand
Dictator
DictatorID DictatorName
1 Smith
2 Peter
3
4 Donald
5 Fred

Countries
CountryID CountryName DictatorID
1 New Zealand 1
2 Australia 8
3 UK 3
4 USA 7
5 Singapore 5
6 Canada 2
7 Indonesia 4
8 South Korea 1
9 Thailand 8
10 Malaysia 3
DateCountryCitizenship
Date CountryID CitizenID
1/1/1989 1 1
10/30/2005 2 1
6/19/1976 1 1
4/28/1965 3 3
11/11/1973 3 3
6/7/2001 2 3
8/30/1987 2 4
9/25/1977 1 4
7/7/2005 3 4
2/27/2000 1 5

2. First Normal Form
R1 = (CountrylD#, CountryName, DictatorlD, DictatorName, (CitizenlD#, CitizenName, DOB,
CountryBorn, DateCountryCitizenship))
R11 = (CountrylD#, CountryName, DictatorlD, DictatorName)
R12 = (CitizenlD#, CountrylD, CitizenName, DOB, CountryBorn, DateCountryCitizenship)
CountryID CountryName DictatorID DictatorName
1 New Zealand 1 Smith
2 Australia 2 Peter
3 UK 3
4 USA 4 Donald
5 Singapore 5 Fred
6 Canada 3
7 Indonesia 2 Peter
8 South Korea 4 Donald
9 Thailand 1 Smith
10 Malaysia 5 Fred
CitizenID CitizenName DOB CountryBorn Date
7 Janet Evanovich 11/11/1973 UK 4/28/1965
5 Charles Dickens 7/13/1965 Australia 11/11/1973
3 Jung Chang 1/1/1960 New Zealand 6/19/1976
1 Eric Ambler 3/25/1976 Canada 9/25/1977
4 James Clavell 9/4/1987 Singapore 8/30/1987
1 Eric Ambler 3/25/1976 Canada 1/1/1989
3 Jung Chang 1/1/1960 New Zealand 2/27/2000
2 Raymond Chandler 2/12/1973 USA 6/7/2001
8 Ian Fleming 9/25/1977 Switzerland 7/7/2005
8 Ian Fleming 9/25/1977 Switzerland 10/30/2005

3. Second Normal Form
R11 = (CountrylD#, CountryName, DictatorlD, DictatorName)
R12 = (CitizenlD#, CountrylD, CitizenName, DOB, CountryBorn, DateCountryCitizenship)
R121 = (CitizenlD, CountrylD, DateCountryCitizenship)
R122 = (CitizenlD#, CitizenName, DOB, CountryBorn)
CountryID CountryName DictatorID DictatorName
1 New Zealand 1 Smith
2 Australia 2 Peter
3 UK 3
4 USA 4 Donald
5 Singapore 5 Fred
6 Canada 3
7 Indonesia 2 Peter
8 South Korea 4 Donald
9 Thailand 1 Smith
10 Malaysia 5 Fred
Date CitizenID CountryID
1/1/1989 1 1
9/25/1977 1 1
6/7/2001 2 2
2/27/2000 3 1
6/19/1976 3 1
8/30/1987 4 2
11/11/1973 5 3
4/28/1965 7 3
10/30/2005 8 2
7/7/2005 8 3

CitizenID CitizenName DOB CountryBorn
1 Eric Ambler 3/25/1976 Canada
2 Raymond Chandler 2/12/1973 USA
3 Jung Chang 1/1/1960 New Zealand
4 James Clavell 9/4/1987 Singapore
5 Charles Dickens 7/13/1965 Australia
6 John Ellroy 8/28/1973 India
7 Janet Evanovich 11/11/1973 UK
8 Ian Fleming 9/25/1977 Switzerland
9 Frederick Forsyth 12/25/1986 Japan
10 Graham Greene 1/1/1960 South Korea
11 Dashiell Hammett 6/29/1972 Singapore
12 Donna Leon 5/20/1988 New Zealand
13 Elmore Leonard 4/24/1956 Australia
14 Christopher Lloyd 6/6/1967 USA
15 Paul Lund 12/3/1987 UK
16 Alan Moorehead 4/19/2000 Singapore
17 Haruki Murakami 7/6/1976 USA
18 David Rooney 3/5/1967 Australia
19 Damon Runyon 8/23/1987 UK
20 Hunter Thompson 10/29/1987 New Zealand
4. Third Normal Form
R11 = (CountrylD#, CountryName, DictatorlD, DictatorName)
R111 = (CountrylD#, CountryName, DictatorlD)
R112 = (DictatorlD#, DictatorName)
R121 = (CitizenlD, CountrylD, DateCountryCitizenship)
R122 = (CitizenlD#, CitizenName, DOB, CountryBorn)