1. Produce an Entity-Relationship Model for the scenario described above
Develop a top-down design of the data in the form of an entity-relationship diagram. You should note all assumptions you make about the data and the reasoning behind your design choices. Also include any appropriate constraints and a list of entity types showing their attributes and identifiers.
2. Design a set of relations conforming to Boyce-Codd Normal Form (BCNF).
Once you are satisfied that the ER diagram is a good representation of the data, produce a logical design by mapping the E-R diagram to a set of (normalized) relations. Clearly show all intermediate steps.
3. Implement your final database design.
Take each of the relations from your relational model and implement them as SQL tables. You must include all primary and foreign keys as well as any other table or column constraints you feel are appropriate. Then, using appropriate sample data and your own imagination,populate your finished tables.
4. Query your database.
Using SQL, write a set of realistic sample queries based on the above scenario (use your imagination for details of each query) but they should include the following SQL query techniques:
• Joins (using two, three or more tables)
• Set operations (UNION, INTERSECT and MINUS)
• Ordering
• Grouping
• Aggregate functions (MIN, MAX, AVG, COUNT, SUM)
• Table aliases
• Renaming columns
• Sub-queries (nested queries)
You should aim to write at least tensample queries - ranging from basic SELECT...FROM...WHERE queries to more advanced ones using the above techniques.
5. Optimize your Database
• You should now optimize your database:
• Apply suitable database optimization techniques to your final set of tables.
• Aim to implement a range of indexes.
• Run a suite of queries that will invoke those indexes.
• Aim to demonstrate some index suppression techniques.
6. Secure your Database
Your optimized database should now be secured. To demonstrate this:
• Create three new users on your database
• Allocate them different security privileges and roles.
• Create a suite of views over your existing base tables.
• Issue appropriate privileges so that at least one user can use those views.
• Clearly demonstrate what actions each user can/cannot perform.
7. Building a Web Interface
You now have a fully working, secure and optimized database. Your final task is to design and build a web-based interface to that database such that it can support the following operations:
• Insert new rows of data
• Update existing rows of data
• Delete existing rows of data
• Query existing rows of data