Reference no: EM133907240

Database Concepts

Overview
The objective of this assignment is to measure your understanding of the basic concepts in the relational database model and using entity-relationship model for database design. The assessment is in two parts, split into four tasks which cover Basic ER Modelling and Basic Relational Modelling. The tasks are as follows.

Part A: Entity-Relationship Modelling
Design and plan for the implementation of a database system, diagramming the design to a high standard using UML notation through the diagramming tool Lucidchart.

Model the activities of an organisation and present the model as an Entity-Relationship (ER) diagram. Analyse this ER diagram, and possibly modify it, based on additional client requirements.

Map an ER diagram into a relational database schema, showing every step of the mapping.

Part B: Relational Database Model
Answer a series of short questions about a Relational Database model.
To complete this assessment, you must be familiar with Lucidchart, which is covered during the Week 1- 4 activities.

Assessment criteria
This assessment will measure your ability to:
Describe various data modelling and database system technologies.
Explain the main concepts for data modelling and characteristics of database systems.Develop a sound database design using conceptual modelling mechanisms such as entity-relationship diagrams.
Develop a database based on a sound database design.

Part A: Entity-Relationship Modelling

Task 1: Designing an Entity-Relationship Model

Airline Operation Database
The following information is required for an airline operations branch

The airline flies to many airports. Airports are distinguished by a code and information about their time zone and local currency are recorded.

Each route flown by the airline has an ID, a distance and a duration. Each route also has a destination airport and a departure airport.

Information about staff is also required. Their name, address, unique badge number and gender are needed. Each staff member has a company-issued mobile and its number must also be recorded.

A flight has its own number and departure time and flies a particular route. To cover demand, there are usually several flights allocated to each route.

Each aeroplane in the airline fleet has a call sign unique to it. The type of plane and number of seats should also be recorded.

The basic requirement for the airline to maintain operation is that a plane and staff are available to be allocated to a flight. All three factors must be present for a take-off to occur.

Customers of the airline are identified by their credit card number and their name and contact number are required. Customers can book flight tickets from the airline, and date of the flights should be specified.

Based on the given description, model the given business rules, and present your model as an Entity-Relationship (ER) diagram. Carefully state any assumptions you make. In your ER diagram, you must properly denote all applicable concepts, including weak or strong entities, keys, composite or multi-valued attributes, relationships and their cardinality and participation constraints.

If you cannot represent any of this information in the ER model, clearly explain what limitations in the ER model restrict you from representing your model.

Task 2: Optimize an Entity-Relationship Model

Advanced Airline Operation Database
The following information is required for an airline operations branch

The airline flies to many airports. Airports are distinguished by a code and information about their time zone and local currency are recorded.

Each route flown by the airline has an ID, a distance and a duration. Each route also has a destination airport and a departure airport.

Information about staff is also required. Their name, address, unique badge number and gender are needed. Each staff member has a company-issued mobile and its number must also be recorded.

A staff member may have a line manager to supervise his/her work. A manager may supervise a number of staff members. The start time of such supervision should also be recorded. Please note only current line managers of staff members are kept in database.

A flight has its own number and departure time (say 8:30am) and flies a particular route. To cover demand, there are usually several flights allocated to each route.

Each aeroplane in the airline fleet has a call sign unique to it. The type of plane and number of seats should also be recorded.

The basic requirement for the airline to maintain operation is that a plane and staff are available to be allocated to a flight. All three factors must be present for a take-off to occur (also known as a flight instance). A certain staff and a certain plane can be allocated to the same flight multiple times on different dates, i.e. different flight instances of the same flight.

All the seats for each flight are numbered sequentially from 1 upwards. Each seat also has a seat class (e.g., Economy, Business, First).

Customers of the airline are identified by their credit card number and their name and contact number are required. Customers can purchase any number of seats from the airline. In addition, customers can purchase seats from the same flight on different dates, i.e. different flight instances of the same flight. Boost your grades with expert-level assignment help today.

Please modify / optimize your initial ER diagram from Task 1 to incorporate the additional requirements in bold text. A complete ER diagram (initial ER with amendments) should be presented. Carefully state any assumptions that you make. In your ER diagram, you must properly denote all applicable concepts, including:
Weak or strong entities
Keys
Composite or multi-valued attributes
Relationships and their cardinality and participation constraints.

Task 3: Mapping an ER Model to a Relational Database Schema
Consider the following ER diagram, which illustrates aspects of a chain hotel system.

You are requested to map the above ER diagram into a relational database schema using the course introduced 7 steps mapping rules. Show every step of the mapping. No marks are awarded to the final schema if you do not show the partially built schema at the end of each step. Clearly indicate the primary key (underlined) and foreign keys (with an asterisk) in each relation.

Part B: Relational Database Model

Task: Relational Database Model
This section contains a database instance for the Company database that stores company related data for an organisation. A company database contains six relations: Employee, Department, Project, Dependent, Department Locations (Dept_Locations) and Works_on. A sample database instance is shown in Figure 2, where primary keys of relations are underlined, and arrowed lines indicate foreign keys and the corresponding attributes in parent relation. The meanings of most attributes are self-explanatory. Some additional notes for attribute meanings are as follows:

The following questions must be answered based on the given database schema and instance. Where explanation is required, each answer should be a SHORT passage of at most several lines.

Question 1: Can an employee manage one department and be a regular employee in another? Explain your answer using the data integrity constraints on Employee and Department relations.

Question 2: Can an employee work on projects which are managed by departments other than his/her own department? If yes, explain how to find out such facts in steps, no need to write SQL statements. If no, suggest all possible schema modifications that would make this query feasible.

Question 3: A new project has been established. A new record has been entered into the Project relation, as follows:

The new project has the same project name as that of the existing project 1 in the relation, is it allowed? The managing department info of the new project is missing, is it allowed? Please list all the integrity constraints violated by the above operation. If the operation does not violate any constraints, indicate as ‘no violations' and justify your decision from primary key and foreign key perspectives.

Question 4: Does the given Department maintain a history of all department managers? Please explain your answer.

Question 5: Consider the request "Find employees who do not have a spouse. List their SSNs and names". Is this request answerable using the given database schema? If yes, explain how using steps, no need to write SQL statements. If no, suggest all possible schema modifications that would make this query feasible.

Question 6: A new department location is introduced. Can the below SQL statement be successfully executed? If yes, explain your answer using integrity constraints; if not, explain your answer and provide SQL statements to complete the request. Your SQL statements must run in SQLite.

Question 7: Write an SQL statement to create the Dependent table including all the constraints, assuming all the tables that Dependent table depends on already exist in the database. Your SQL statement must run in SQLite.

Question 8: A data entry error resulted in the Dnumber values of the "Research" and "Administration" departments being swapped. These two departments also manage multiple projects and employees. The operator attempted the following SQL statements to fix the error, but these attempts were not successful.

Explain why the above approach failed in SQLite? What constraints were violated? Please also write the correct SQL UPDATE statements to carry out your proposed solution. Your SQL statement must run in SQLite.