As a database developer at the creative Developers Company (CDC), you got a new project to develop a database system for a Cinema named the Movie Zone. CDC Company conducted a meeting with the Movie Zone General Manager and they required a System for their staff to ease and organize the process of movies reservation. The Movie Zone General Manager requires a system that allows the customer to search for a movie, know the genre for the selected movie, reserve a movie and other additional services.

To reserve a movie at Movie Zone Company, the customer must have an account that has a unique National ID, name (first name, mid name, last name), phone number (one or more), age, gender, address, nationality, and date of birth. Each customer can reserve more than one movie. In addition, many customers can reserve many movies. Each movie is described by movie title, movie id, duration, revenue and year.

In Movie Zone Cinema, a movie has one or many genre, each of which is identified by genre id, and genre name. One Genre can be related for more than one movie. Also, Movie Zone deals with a range of production companies and are identified by id, name, address and phone number. The production company produces one or more movies, and the film is related to only one production company.

There is a list of rules and restrictions that must be applied to the developed database to ensure the accuracy of the data entered, which are as follows: 

The revenue of each film must not be less than $100

If the customer did not fill in the nationality fill it with "Jordanian" by default.

Do not reverse any movie without specifying its duration.

Do not register two customers with the same email.

Task1: Designing Database System 

As a first stage, the first task assigned to you is to design the database system, you are required to perform the following: 

Investigate and document at least 10 system and user requirements based on the given scenario

Outline the database system by designing an appropriate Entity Relationship Diagram (ERD)

Create the relational database which includes:

List of tables mapped from the ERD

Database schema which includes the fields, datatypes and constraints (data validation), identifiers (primary keys and foreign keys).

Check whether the provided design is normalized. If not, normalize the database by removing the anomalies.

Design set of simple input and output (reports) interfaces for the project using Wireframe or any interface-designing tool.

Assess and validate your design with respect to given requirements, check whether all requirements are satisfied, show details on how you did your assessment.

Task 2: Implementing the database system

Choose an appropriate database management system (DBMS) to design the required database system based on the relational database you designed in the first task. Also, implement the validation methods mentioned in the scenario. Also, consider the validations methods inside the interfaces such as using the drop-down list.

2. The system should provide the user with helpful information according to their needs, perform SQL queries over your implemented database that retrieves the following information (this question will be graded by observation). 

Female customers information, including national ID, name, phone number.

Details of production companies ordered by name, in ascending order.

Details of each movie, including its producing company name and mobile number.

Details of the movie with the highest revenue.

Consider the user requirements of a cinema manager, analyze and show how your implemented system helps the manager extracting the information they need. Therefore, you need to create queries that provides a proof that your system allows the managers to extract such information. In addition, explain the results of your queries and show how your queries satisfied the managers request.

 4.Create simple interfaces to insert, update and delete data in the database, f for each entity

You need to apply security to the implemented database by adding a strong password.

Task 3: Evaluating the database system

Based on the user and system requirements, measure the implemented database solution by listing each requirement along with where it was met and how it was achieved. In addition, recommend improvements based on the assessment

2. After implementing the database system, you need to test it by performing the following: 

Report the test plan that you want to follow by identifying elements of the system that need to be tested and considering data that should be used to fully test the system.

List the procedures to be used in testing (white box testing , black box testing)

Based on the test plan you have given in the previous point, check the system against user and system requirements. In addition, provide relevant test cases (at least 5 test cases) for the database you have implemented.

Evaluate the effectiveness of your testing, by showing how were your test cases helpful to find out if the system is correct and complete or not.

3. Assess the implemented database by discussing improvements that could be made over the system, keeping its quality attributes like: scalability and maintainability.