Module: Database Design and Development
Learning outcomes covered
LO1 Develop a logical database design.
LO2 Implement a physical database for a given requirement
LO3 Test and document databases
Scenario and the Task
“Quick Builders” is a leading private construction firm at Colombo. Quick builders handle several projects simultaneously and one Engineer assigned to an exact one project and project can have one or more engineers. An engineer supervises one or more supervisors and a supervisor may have many engineers.
Under a supervisor there can be one or more labors with different talents and a certain labor is supervised by zero or many supervisors.
The construction firm is willing to enhance their information system and to have a properly designed Database Management System is a one of its primary task.
Tasks:
Design the database with the constraint that the available technology is relational.
1. Draw an entity relationship diagram for given scenario with proper standards. Identify important keys and represent different types of attributes and relationships.
2. Draw Relational Schemas. Effectively map conceptual data models with relational database schema according to the mapping algorithm. All the steps should be clearly mentioned.
3. Create the database using SQL server.
4. Write SQL queries for below requirements.
List of names of all Engineers who attached to a particular project
List of names of labors who works under a particular Engineer
List of names of supervisors who works under a particular Engineer
5. Generate the suitable management reports for database by using graphical format.
6. Explain the purpose and benefits of having queries in the database for construction firms’ decision making process with the examples.
7. Provide the Test plan, test strategy and proper test cases and provide all the test documents.
8. Create the user manual for the database.
9. Explain how verification and validation has addressed in your database.
10. Explain the access rights to various types of users.