1. Compare database systems to file based systems. Your discussion should include the advantages, disadvantages and limitations of each.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Scenario:

 Jet Airline:

 Jet Airline is an international airline company who is looking to develop an effective RDBMS to cater their needs for their growing business. As a junior developer, you have been asked to develop a database for New Jet Airline which will satisfy all their requirements.

The database should contain information about passengers, flights, departure, employees of the airline and aircrafts.

For passengers’- name, address, phone number and related information to store.

For employees- the company wants to record the name, address, salary, identification number and flight information.

Not all employees can fly aircrafts but just the pilots.

For all these employees, it is required to record the qualification i.e. what kind of plane they can fly.

For planes, it is required to record the model and manufacturers of the plane. The airline has many aircrafts of certain type.

For flights the airline needs to keep the information, like flight number, origin, destination, departure time and arrival time. For the same source-destination, there can be many flights per week.

Relevant assumptions, if required can be made.

Database should be able to get the following information from the developed database:

1. Statistics of the payments, passengers, flights and employees

2. Number of bookings and cancellations for a specific day for specific journey

3. Number of flights for a specific journey in a

4. The average number of passengers for specific source – destination

Tasks / Deliverables of Assignment 1:

 Marks are awarded for producing a properly documented system that meets the requirements as specified below:

Task 1:  Understanding of database  [ 20%]

 By relating your discussion to the above scenario where applicable,

1. Compare database systems to file based systems. Your discussion should include the advantages, disadvantages and limitations of

2. Discuss the characteristics of Hierarchical, network and relational database models and present practical applications where each one might be more appropriate to use.

3. Top-down and bottom-up are the two common approaches to a relational database design. Discuss the strengths and weaknesses of Top-down and bottom-up approaches and where each one might be appropriate to use. Comment on the approach you will use for this task

Task 2: Conceptual Modelling   [20%]

 

1. Identity all the possible entities that you can find in the problem scenario and list them in alphabetical

2. Using Crow’s foot notation, design a conceptual data model for the New Airline database in a modelling tool of your choice. All entity types and the relationships between them must be clearly shown. Your design should show all cardinality and participation constraints accurately. Any enhanced features used (e.g. generalization/specialization) must be

 

Task 3: Logical Modelling   [25%]

 

1. Discuss the principles of normalization and demonstrate by examples the steps you followed to achieve normal forms in relation to the given scenario. Your demonstration must show the full process with explanations, starting from UNF, 1NF, 2NF to 3NF. (There is no requirement here to go through the full list of attributes in the data model for the New Airline).

2. Produce an ERD with all the normalized entities containing the relevant attributes. State any de-normalization, if any, in all your relations and clearly state the reasons for de-normalizing relations in your implemented

3. Map an ER model devised above into a set of logical relations in the relational data

 

Task 4: Physical Modelling     [30%]

1. Produce a script using appropriate SQL commands (DDL) to create a set of database tables using MySQL relational database. Your tables should also show all constraints applied at either the column or the table level. You are expected to implement the database so that it meets the requirements described in the scenario.

2. By using appropriate SQL statements, populate all the tables in the database you created with some data. The data should be meaningful but does not need to be extensive.

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of