Suppose you are a database developer at a XYZ data development company, you got a new project to develop a database system. The system is a ticket booking system for attending Jerash Festival. This system includes a set of tickets that contains different set of prices. Each ticket is related to one singer and one theatre. A theatre is related to one singer only, and the theatre belongs to one singer. 

As well, each ticket belongs to a specific type. The tickets are described by the type, price, duration, and singer who will recall the concert and a unique ID. There is many theaters and each theater has an ID, size and name. 

The singers are characterized by name (first name, last name), nationality, Singer ID and band. In addition, this system has users who can register and create an account to choose their favorite singers to attend their concert. Each user is identified by a unique e-mail and has password, name, country, birthdate and gender. A user can book one or many tickets at the festival. 

The clients (users) need some security applied to the database such as password. In addition, they requested to perform the following operations using the database system: 

1. Registration and deletion of users accounts.

 System displaying the following: 

1. • All tickets sorted by type (such as golden ticket, silver ticket and black ticket).
   • All singers in which theaters these singers will have the concert in.
   • The tickets in ascending order according to its prices.
   • The number of tickets recorded for each client.
   • All singers, theaters, and tickets all ordered in descending order by singer, ticket type.
   • The clients who have bought the golden ticket more than twice, and if so, how many clients were recorded?
2. Your manager asked you to suggest additional queries that may ease the process of management and produce appropriate management information in order to help the festival manager to manage the festival and help the signers to be at their correct theaters as well help the clients to attend the signers concerts.

In addition, the clients specify 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: 

1. Do not include any ticket without specifying its type and price.
2. If the client does not fill ticket type, then fill it with "Black", by default.
3. Each theatre must at least contain one singer.
4. Do not register user with the same email.

The main tasks assigned to you are listed below, be sure to document your work in an organized documentation with a detailed description of the features and operations of your system.

Assignment activity and guidance

The following are the main tasks you are assigned to you:

Designing the Database System

1. Investigate the requirements of the system.

2. Apply the conceptual design (ERM) concepts to:
   • Write the entity and identify the attributes required for each entity.
   • Define the cardinality ratio of each relationship and their types.
   • Check whether the provided design is normalized. If not, normalize the database by removing the anomalies. In addition, what is the type of normalization achieved in your design?

3. Design the relational database which includes:
   • List of tables mapped from the ERD.
   • Database schema which includes the fields, datatypes and constraints.
   • Specify identifiers (primary Key) and foreign keys datatypes for each table.

4. Design set of simple input and output interfaces for the project using Wireframe or any interface-designing tool. In addition, explain the validation mechanisms to validate data in the tables with examples. 

5. Check whether your design is representing the identified user and system requirements to determine the effectiveness of the design, after producing the design.