Assignment 2 Developing database using Microsoft SQL Server
Total points: 10
The objective of this assignment is to learn how to build and access a database using Microsoft SQL server.
Mark distribution:
Table creation
20
Relational diagram and integrity control
20
Sample data entry
10
Information search query
40
Report writing
10
Note: You can download and print out Tutorial for Microsoft SQL from avenue course web site. Following the guidelines you can do the similar tasks in this assignment much easier. You may also reference the textbook with the pages indicated in each task. The help function from Microsoft SQL Server is also very useful.
A consulting company stores data to track all charges to project. The charges are based on the hours each employee works on each projects. In the JOB table there are different jobs identified by job code. The hour charge JOBCHGHOUR is determined by the job code. In EMPLOYEE table each employee has only one job classification. The PROJECT table represents the project number, the project name and budget for each project. Each project has a manager with EMPNUM as the foreign key in the Project table. The assignment is represented in the ASSGINMENT table. Each employee may be assigned to many projects and each project may have many people assigned to work for certain hours in different days. Please notice that the ASSIGNMENT table is not in the third normal form. Transitive dependences exist: EMPNUM ASSIGNJOB, ASSIGNJOB ASSIGNCHGHR, and the derived value ASSIGNCHARGE ASSIGNHOURS ASSIGNCHGHR. The reason of keeping these redundant data is that they change over time.
The structure and contents are shown below:
Write and run SQL statement to create above tables using Microsoft SQL server.
Use the above table names and field names exactly in order to make assignment easy to implement. Please do not use space in table names and field names. Based on the data showing above, specify the data type for each column. Specify primer key and foreign keys and set up integrity control in your table definition statement. Run the table creation statement and make sure all tables are created successfully. Save the queries and report it in your assignment report. Notice the create table can only run once. If you want change your design, better to drop the table and create a new one.
Using database diagram to create and show the relationships between tables. Specify relations and table constrains for integrity control. Print out the relation diagram. Report what foreign key constrains you have established on the links between Employee and Assignment and why you implement them.
Enter sample data for the tables you created. You can enter data by using a SQL Insert statement, or the command of editing top 200 rows. Be careful not to violate your integrity control when you enter data. For instance, you need to enter JOB data before enter Employee data. You need to enter Project data before enter the assignment for that project. For date please use four digit for the year. For example, enter 20Nov2017 rather than 20Nov17.
To make sure your work is different from other students, please enter your name as an employee with a job as database designer JOBCODE is 502 in the EMPLOYEE table and enter your work assignment for one project such as Evergreen PROJNUM is 18 in ASSIGNMENT table.
Design queries for information search
Create and run a query to list employees who were hired before the year 1995.
Create and run a query to list job code, job description, and employee last name, first name sorted by job description and employee last name.
Create and run a query to list the names of employees who were assigned to the Evergreen project.
Create and run a query to list the project name, the total hours and total charges assigned to each project sorted by project name.
You need to save each query. You may use a Snipping toll to copy and paste your screenshot to show the query and the query result.
Write a report for the above tasks with sample run results. Report how many hours you have spent and what you have learned from this assignment.
Submission
Combine all the results in a single report. Report what database you use in SQL server. Report how many hours you have spent in this assignment and what you have learned.
Note:
1. The assignment should be done individually. You are allowed to discuss with other students on how to do the assignment but you must implement it in your own ProjDB database and cannot copy other students assignment report.
2. The report should be saved in Microsoft word file. The file name should be ASS2section number your mac id. For instance, if you are in section C1 and your mac id is stevej, then your file name will be ASS2C1stevej
3. The assignment should be submitted before class meeting of the due week as indicated in the class schedule.
4. Please submit your assignment online to avenue course web site http:avenue.mcmaster.ca