Assignment Task

1. Complete the information-level design for a database that satisfies the following constraints and user view requirements. In order to complete this information–level design you are required to answer the questions given below 1(a) – (e).

1. Identify and list entities described in all user requirements given above.
2. Add attributes to these entities and represent them as a collection of tables and attributes (Database schema): e.g. Customer (Customer_Num, Cust_First_Name,. . . . .)
   
   Note: Select and underline suitable primary key for each table. 
   
   
3. Outline 3 business rules that describe the relationships between entities.
   
   Note: You can make assumptions to create certain relationships, if it is not specifically mentioned in the given business rules. e.g. A Coach can work on only one program.
   
   
4. Determine the functional dependences.
   
   e.g. Customer_Num → Cust_First_Name
   
   
5. Then normalise these tables. Make the normalization to 3NF. State for every step in the normalization, which functional dependency causes it. 

2. Represent the structure of your database visually by using an entity-relationship (E-R) diagram. If you make any assumptions about data that are not explicitly given in the problem, these must be described.

Note: You need to use Visio, draw.io [1] or Lucid chart [2] or any other software tool, to create the ER diagram. 

3. Build this model using XAMPP by creating these tables and Relationships. Populate these tables with appropriate data; include at least 2 records in each table. 

4. Report Generation 

1. Write an SQL query to generate one example of useful information that can be obtained from this database.
2. For additional you will execute more queries (at least 2) on the table join from the database you created in Question 3 and include the screen shots of the outputs and all SQL statements.