Using SQL Server Management Studio, create all four tables of the sample database The sample database...

  

Transcribed Image Text:

Using SQL Server Management Studio, create all four tables of the sample database The sample database used in this book represents a company with departments and employees. Each employee in the example belongs to exactly one department, and each department has one or more employees. Jobs of employees center on projects: each employee works at the same time on one or more projects, and each project engages one or more employees. The data of the sample database can be represented using four tables: department employee project works on Tables 1-1 through 1-4 show all the tables of the sample database. dept_no d1 d2 d3 dept_name location Research Dallas Accounting Seattle Marketing Dallas Table 1-1 The department Table emp_no emp_fname emp_Iname dept no 25348 Matthew Smith d3 10102 Ann Jones d3 18316 John Barrimore dl 29346 James James d2 2581 Elke Hansel d2 9031 Elsa Bertoni d2 28559 Sybill Moser d1 Table 1-2 The employee Table project no project_name budget pl Apollo 120000 p2 Gemini 95000 p3 Mercury 186500 Table 1-3 The project Table emp_no project_no job enter_date 10102 P1 Analyst 2016.10.1 10102 P3 Manager 2018.1.1 25348 P2 Clerk 2017.2.15 18316 P2 NULL 2017.6.1 29346 P2 NULL 2016.12.15 2581 P3 Analyst 2017.10.15 9031 PI Manager 2017.4.15 28559 PL NULL 2017.8.1 28559 P2 Clerk 2018.2.1 9031 P3 Clerk 2016.11.15 29346 P1 Clerk 2017.1.4 The department table represents all departments of the company. Each department has the following attributes: department (dept_no, dept_name, location) dept no represents the unique number of each department, dept_name is the name of cach department, and location is the location of the corresponding department. The employee table represents all employees working for the company. Each employee has the following attributes: employee (emp_no, emp_fname, emp_Iname, dept_no) emp_no represents the unique number of each employee. emp_fname and emp_Iname are the first name and last name of each employee, respectively. Finally, dept no is the number of the department to which the employee belongs. Each project of the company is represented in the project table. This table has the following columns: project (project no, project_name, budget) project no represents the unique number of each project. project_name and budget specify the name and the budget of each project, respectively. The works on table specifies the relationship between employees and projects. It has the following columns: works on (emp_no, project no, job, enter_date) emp_no specifies the employee number and project no specifies the number of the project on which the employee works. The combination of data values belonging to these two columns is always unique. job and enter_date specify the task and the starting date of an employee in the corresponding project, respectively. Rubrics: 1. Creating tables: department - 20 points employee - 20 points project - 20 points works on 20 points 2. Add screenshots in SSMS to a word document along with appropriate description. (20 points) Using SQL Server Management Studio, create all four tables of the sample database The sample database used in this book represents a company with departments and employees. Each employee in the example belongs to exactly one department, and each department has one or more employees. Jobs of employees center on projects: each employee works at the same time on one or more projects, and each project engages one or more employees. The data of the sample database can be represented using four tables: department employee project works on Tables 1-1 through 1-4 show all the tables of the sample database. dept_no d1 d2 d3 dept_name location Research Dallas Accounting Seattle Marketing Dallas Table 1-1 The department Table emp_no emp_fname emp_Iname dept no 25348 Matthew Smith d3 10102 Ann Jones d3 18316 John Barrimore dl 29346 James James d2 2581 Elke Hansel d2 9031 Elsa Bertoni d2 28559 Sybill Moser d1 Table 1-2 The employee Table project no project_name budget pl Apollo 120000 p2 Gemini 95000 p3 Mercury 186500 Table 1-3 The project Table emp_no project_no job enter_date 10102 P1 Analyst 2016.10.1 10102 P3 Manager 2018.1.1 25348 P2 Clerk 2017.2.15 18316 P2 NULL 2017.6.1 29346 P2 NULL 2016.12.15 2581 P3 Analyst 2017.10.15 9031 PI Manager 2017.4.15 28559 PL NULL 2017.8.1 28559 P2 Clerk 2018.2.1 9031 P3 Clerk 2016.11.15 29346 P1 Clerk 2017.1.4 The department table represents all departments of the company. Each department has the following attributes: department (dept_no, dept_name, location) dept no represents the unique number of each department, dept_name is the name of cach department, and location is the location of the corresponding department. The employee table represents all employees working for the company. Each employee has the following attributes: employee (emp_no, emp_fname, emp_Iname, dept_no) emp_no represents the unique number of each employee. emp_fname and emp_Iname are the first name and last name of each employee, respectively. Finally, dept no is the number of the department to which the employee belongs. Each project of the company is represented in the project table. This table has the following columns: project (project no, project_name, budget) project no represents the unique number of each project. project_name and budget specify the name and the budget of each project, respectively. The works on table specifies the relationship between employees and projects. It has the following columns: works on (emp_no, project no, job, enter_date) emp_no specifies the employee number and project no specifies the number of the project on which the employee works. The combination of data values belonging to these two columns is always unique. job and enter_date specify the task and the starting date of an employee in the corresponding project, respectively. Rubrics: 1. Creating tables: department - 20 points employee - 20 points project - 20 points works on 20 points 2. Add screenshots in SSMS to a word document along with appropriate description. (20 points)