- Describe your database scenario (Don’t select any database which we studied during lab session or lecture) with at least 5 Entities. (1 Mark)
- Design an Entity Relationship (ER) diagram to capture the scenario requirements. Make sure cardinalities, relationships, and primary keys are clear. State any assumptions that may affect the ER diagram. Your ER should include at least 4 entities and 1 “Many-to-many” relationship.
(2 Marks)
- Convert the ER diagram to Class diagram. (1 Mark)
- Part 2: Create and Populate Relations
- Map the ER diagram to a relational model and create the relations in MySQL. (1 Mark)
- Enter at least 20 tuples (records) in each relation/table. Your solution should include the screenshots of your relations with data. (1 Mark)
- Part 3: Indexes and Queries
- Write any retrieval query that includes a selection condition and show how MySQL internally performed this query. (1 Mark)
- Create an index for the same column used in the previous selection condition. (1 Mark)
- Repeat the same retrieval query in step (1) and show how MySQL internally performed the query this time. Discuss how the created index improves the performance of your database. (1 Mark)
- Write any retrieval query that includes at least one join condition and one selection condition and show the result of this query. (1 Mark)
- Show the Query Statistics and Execution Plan for the above query. (1 Mark)
- Part 4: Roles and Privileges
- Create two roles and assign the following privileges to the roles (1 Mark):
- Give all privileges to the first role.
- Give only insert privileges to the second role who can further assign the same privileges to others.
- Create two accounts and assign the above roles to the created accounts (each account with a different role). (1 Mark)
- Verify the privileges assigned to each account. (1 Mark)