Learning outcomes

LO1. Understand the database concepts and components
LO2 Develop a logical database design
LO3 Implement a physical database for a given requirement
LO4 Normalization and document databases

Read the following Scenario and assume that you have assigned to design and implement a robust Database System forThe Leisure Travel Boat Company.

Scenario and the Tasks

The Leisure Travel boat company owns a number of boats more than 100 of staff members are working there. Each boat is allocated to a particular voyage, although some voyages may have several boats. Each voyage passes through a number of ports.

One or more Captains are allocated to each voyage.Each Captains is only responsible one voyage.

Some of the ports have a dockyard where boats are kept and each of the boats has one home dockyard. All the boats have unique registration no and number of passengers that boat can carry on it. Number of passengers is varied from boat to boat. Each voyage is identified by a voyage number. All the staff members having their employee number, name, (first name, last name)address (Apartment number, street number and city), contactnumber (mobile and home phone number), age, salary. Other than this information medical history of all the captains are stored in the system. Fixed no of hours should be covered by each captain within the month other than this, every additional working hour he will be paid 5% of basic salary.

Each dockyard has a name, address and telephone number. Although each boat has home dockyard boats can be serviced at any dockyard throughout the journey. Maintenance engineers, mechanics works at a particular dockyard, Maintenance engineers have their qualification and mechanics have their experience stored in the system.

Voyage booking and cancellation
Online enquiry facilities and voyage schedules are available for passengers.
10 days prior to the journey date Passengers can start booking voyage by paying full amount of charges. The group Passengers has facility to do wholeboat booking. All the Charges are depending on the voyage, food and entertainment facility can be provided on request. If any passenger wants to cancel the booking it should be done 2 days prior to the journey date, then company will refund the 75% of the Charges. otherwise the company has the right to charge 50% and refund only the half of the amount of the amount.

Requirements of the assignment
Management of this Company needs to restrict medical history details of captains from the payroll department.
All the possible reports should be generated according to the management request.
Data base documentation is required for the system

Note: Mention your valid assumptions clearly.

Students can add any functionality which will enhance the system and make the proposed solution more comprehensive.

Use Harvard referencing to properly acknowledge all the external sources you use.

Tasks:

Design the database with the constraint that the available technology is relational.

1. Explain what is data model, Critically compare different data models and explain why older data models are being replaced by new data models.

2. Critically explain the benefits and limitations of different database technologies.

3. Analyze and briefly explain the different approaches to database design.

4. Draw an entity relationship diagram for given scenario with proper standards. Identify important keys and represent different types of attributes and relationships.

5. Draw Relational Schemas. Effectively map conceptual data models with relational database schema according to the mapping algorithm. All the steps should be clearly mentioned.

6. Create the database using SQL server. Practical submission and Demonstration
NOTE: Make sure to enter at least 10 records for each table.

7. Write sql queries for below requirements. Practical submission and Demonstration

Note: You can write any three queries using at least two tables.

8. Explain the purpose and benefits of having queries in the database for decision making process of the furniture shop with the examples.

9. Provide the Test plan, test strategy and proper test cases and provide all the test documents.

10. Explain how verification and validation has addressed in your data base and the access rights to various types of users.

11. Provide a well-structured documentation including proper academic style and Referencing and in-text citation using Harvard Referencing System.

Attachment:- Database Design Development.rar