MSSQL project
Budget: $25 – $100 USD
ind a local business, school, social club, sports club, church, non-profit organisation or any other small to medium sized organisation of your choice in your area having issues or problems which could be addressed by either designing a new database or improving the existing one. Do some background research to gain a clear understanding of the operations of that organisation. You might need to consult staff in the organisation, other resourceful individuals, or related documentation to get a better understanding of operations of the organisation. Ensure that you obtain permission from the organisation if you are going to collect private or sensitive information beyond what is available in the public domain.
Once the organisation and the issue to address have been identified the next step is to collect information you will need to model a database that would address the issues. Since you are developing a relational database, the most obvious modelling technique is the Entity Relationship Diagram (ERD). Look out for any possible anomalies and resolve them through a normalization process. Once you create the normalised ERD, you can start the physically implementing your database within the Microsoft SQL ServerTM environment. That involves writing Transact SQL (T-SQL) statements for creating the database structures, including the database itself, tables, indexes, constraints, and other objects. Write some T-SQL commands to populate all tables in your database with some sample data. Next, think of some questions or queries that could be answered through the proposed database. The aim of this step is to ensure that the database you designed will provide a solution to the issues and problems identified. Write the queries in natural language followed by the equivalent T-SQL views. Also include some stored procedures, triggers, and other programming objects that you feel are needed to make the database easier to use. Enable mixed mode authentication and create at least one user account for your database. Package all the code in one script and group the commands into appropriate batches such that the logical flow of execution makes sense. Ideally, it should be possible to create the entire database and its objects with a single execution of the script. Lastly, create a full backup of your database.
The above mentioned is just a guide to get you started and will not be sufficient to earn a high mark. Additional marks are allocated for suitable coding standards, comments, naming conventions, exception handling, readability, and code efficiency.
Submit the following three files in one compressed or zip folder.
1. A document that includes the following information:
a. Introduction and background of chosen organisation (not more than 1⁄2 a page). b. An ERD that accurately represents the proposed database. c. The normalization steps you took when you created the ERD. Optionally, justify how the
database meets the requirements for at least Third Normal Form (3NF). d. A list of all tables, stored procedures, views, triggers, and other objects with a description for each e. List of questions or queries, in plain natural language, that could be answered through the
proposed database.
2. The T-SQL script file for creating the entire database and its objects, including sample data.
3. A full SQL Server database backup containing all the objects as identified in the database.
Once the organisation and the issue to address have been identified the next step is to collect information you will need to model a database that would address the issues. Since you are developing a relational database, the most obvious modelling technique is the Entity Relationship Diagram (ERD). Look out for any possible anomalies and resolve them through a normalization process. Once you create the normalised ERD, you can start the physically implementing your database within the Microsoft SQL ServerTM environment. That involves writing Transact SQL (T-SQL) statements for creating the database structures, including the database itself, tables, indexes, constraints, and other objects. Write some T-SQL commands to populate all tables in your database with some sample data. Next, think of some questions or queries that could be answered through the proposed database. The aim of this step is to ensure that the database you designed will provide a solution to the issues and problems identified. Write the queries in natural language followed by the equivalent T-SQL views. Also include some stored procedures, triggers, and other programming objects that you feel are needed to make the database easier to use. Enable mixed mode authentication and create at least one user account for your database. Package all the code in one script and group the commands into appropriate batches such that the logical flow of execution makes sense. Ideally, it should be possible to create the entire database and its objects with a single execution of the script. Lastly, create a full backup of your database.
The above mentioned is just a guide to get you started and will not be sufficient to earn a high mark. Additional marks are allocated for suitable coding standards, comments, naming conventions, exception handling, readability, and code efficiency.
Submit the following three files in one compressed or zip folder.
1. A document that includes the following information:
a. Introduction and background of chosen organisation (not more than 1⁄2 a page). b. An ERD that accurately represents the proposed database. c. The normalization steps you took when you created the ERD. Optionally, justify how the
database meets the requirements for at least Third Normal Form (3NF). d. A list of all tables, stored procedures, views, triggers, and other objects with a description for each e. List of questions or queries, in plain natural language, that could be answered through the
proposed database.
2. The T-SQL script file for creating the entire database and its objects, including sample data.
3. A full SQL Server database backup containing all the objects as identified in the database.