QAC020C155S Data Modelling and SQL Language

On the successful completion of this module students will be able to;

1.Understand fundamental concepts of database management systems such data independence, data models and database technologies.

2.Be able to design and implement relational database systems.

3.Be able to use manipulation and querying tools, including the use of Structured Query Language (SQL) for managing data held in a relational database management system (RDBMS).

4.Be able to test and document relational database systems

Scenario: Jet Airline

Jet Airline is an international airline company who is looking to develop an effective RDBMS to cater for their growing business needs. 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, 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, such as 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 specific journey in a week.

4.Average number of passengers for specific source – destination

Assessment

Tasks / Deliverables of the Assignment 1:

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

Task 1:  Understanding of database

By relating your discussion to the above scenario where applicable.

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

2.Compare the SQL databases and NoSQL databases. Your discussion should include the advantages, disadvantages, and limitations of each.

Task 2: Conceptual Modelling

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

2.Using Crow’s foot notation, design a conceptual data model for the Jet-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 indicated.

Task 3: Logical Modelling

1.Discuss the principles of normalization and demonstrate using 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 (Note: There is no requirement here to go through the full list of attributes in the data model for the Jet-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 solution.

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

Task 4: Physical Modelling

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 have created with some data. Note: the data should be meaningful but does not need to be extensive.

Although, much of your report will contain existing body of knowledge, you must write your assignment with your own words to demonstrate your understanding of the subject. You are required to follow the Harvard referencing system when citing others' work. An accompanying list of references must also be provided as part of your report. Extensively referenced work reflects the level of research you conducted in the process of producing the document. It is also an acknowledgement of other people’s work. Correct referencing demonstrates your academic and professional skill. It also reflects your academic integrity and thus to some degree protects you from cases of plagiarism.

Marking Criteria

Understanding of database

Have you compared the SQL databases and NoSQL databases, advantages, disadvantages, and limitations of each?

Have you discussed characteristics of Hierarchical, network and relational database models and present practical applications where each one might be more appropriate to use

Conceptual Modelling

Have you identified all the possible entities you can find in the problem scenario and list them in alphabetical order?

Have you used Crow’s foot notation for conceptual data model for the Jet-Airline database in a modelling tool of your choice?

Have you showed cardinality and participation constraints accurately?

Logical Modelling Have you discussed the principles of normalization and demonstrate by examples the steps you followed?             

Have you produced an ERD with all the normalized entities containing the relevant attributes?

Have you stated any de-normalization?

Have you mapped an ER model devised above into a set of logical relations in the relational data model?

Physical Modelling Have you produced a script using appropriate SQL commands (DDL) and all constraints to create a set of database tables using MySQL relational database?

Have you used appropriate SQL statements to populate all the tables in the database you created with some data?

Referencing and Bibliography Have you correctly cited your work using the Harvard references style?