Graffiti Busters Lab 2A DDL Objectives: 1. Given an ERD, define tables and constraints on the data, using the Create Table statement in SQL Server. 2. Alter the definition of an existing table using the Alter Table statement. 3. Create indexes on non-primary key fields using the Create Index statement. 4. Insert data using the Lab2ATestData.sql file to insert data into your tables and debug any errors. Requirements: 1. Given the ERD shown in the appendix, create the required tables for the Graffiti Busters database including primary key and foreign key constraints. All the attributes that are on the ERD are required attributes. (8 marks) 2. Create the following constraints (check and default). (4 marks) The following constraints must be included in your table definitions. Table Column Check Default Staff Type Wage >=0 20 Training Credits Subtotal w The following columns must use the identity property starting at 1 and incrementing by 1: Table Column Job JobNumber StaffType StaffTypeCode DMIT1508 - Database Fundamentals Marks: 4% of final grade (marked out of 24) Lab 2 Due: 11:59 pm, Feb 12th 2021 No Late Labs will be accepted Client ClientID 3. The database has been in use for a couple months now and Graffiti Busters is very happy with the design (which was created by another development team) but realized they forgot to give them some information. Use alter table to accomplish this. a. They would like to store clients email addresses (if they have one). It must allow up to 100 characters and have proper email format (characters '@' characters Characters).(2 marks) b. They would like to be able to know if a staff member is currently available (an unavailable staff member would be one who is on holidays and cannot be assigned to a job.). An 'Available' field must be added and be able to store 'Y' or 'N' only. It is a required attribute and have the attribute default to 'Y' for all previously entered staff records and new records. (3 marks) C. A default of 'AB' needs to be added to the job tables province attribute. (2 marks) 4. Create indexes for all foreign keys. (2 marks) Payment PX Clut Service De destine F YOSO VOMIS Sevede Vue Dale ditem Notes Cateur Bolt wil A V. atat CH tuote wanne stoffen Stato Et PPKO Fostane VO Postcode Fran Vacher Supply Lastlane HOY 2005 Como to Descrico YO Phone vecento TO Honey Super Rencode FK Stalin Nemo Darty Training RS Store Supply stafood Suporte parcode Durite V WE Stor Room Submission Requirements Lab Submission is to include the following: Create a single script file called Lab2Solution.sql" that contains a clearly commented batch of statements for each requirement. Each batch will contain the SQL statement(s) required to complete the question. DO NOT include anything else (test data, random comments, etc.). The entire script should be able to run at once. If there are any known errors in your solution you must identify them at the top of your script in comments. Errors you have identified and simply could not find a solution for are more acceptable than undocumented errors. Failure to document known errors will result in mark 12 mark deduction for each undocumented error up to 3 marks. Your script file will be submitted to moodle (or other location if specified by your instructor). . Any additional requirements as specified by your instructor. Other Considerations Do not make assumptions. If you have questions about the company, ask your instructor (client). This is not a group project. Working with another student on lab material may result in a grade of O for this lab. Up to 3 Marks may be deducted for incomplete lab submission requirements or poor client communication (check the provided documentation for the answer before asking the client). Graffiti Busters Lab 2A DDL Objectives: 1. Given an ERD, define tables and constraints on the data, using the Create Table statement in SQL Server. 2. Alter the definition of an existing table using the Alter Table statement. 3. Create indexes on non-primary key fields using the Create Index statement. 4. Insert data using the Lab2ATestData.sql file to insert data into your tables and debug any errors. Requirements: 1. Given the ERD shown in the appendix, create the required tables for the Graffiti Busters database including primary key and foreign key constraints. All the attributes that are on the ERD are required attributes. (8 marks) 2. Create the following constraints (check and default). (4 marks) The following constraints must be included in your table definitions. Table Column Check Default Staff Type Wage >=0 20 Training Credits Subtotal w The following columns must use the identity property starting at 1 and incrementing by 1: Table Column Job JobNumber StaffType StaffTypeCode DMIT1508 - Database Fundamentals Marks: 4% of final grade (marked out of 24) Lab 2 Due: 11:59 pm, Feb 12th 2021 No Late Labs will be accepted Client ClientID 3. The database has been in use for a couple months now and Graffiti Busters is very happy with the design (which was created by another development team) but realized they forgot to give them some information. Use alter table to accomplish this. a. They would like to store clients email addresses (if they have one). It must allow up to 100 characters and have proper email format (characters '@' characters Characters).(2 marks) b. They would like to be able to know if a staff member is currently available (an unavailable staff member would be one who is on holidays and cannot be assigned to a job.). An 'Available' field must be added and be able to store 'Y' or 'N' only. It is a required attribute and have the attribute default to 'Y' for all previously entered staff records and new records. (3 marks) C. A default of 'AB' needs to be added to the job tables province attribute. (2 marks) 4. Create indexes for all foreign keys. (2 marks) Payment PX Clut Service De destine F YOSO VOMIS Sevede Vue Dale ditem Notes Cateur Bolt wil A V. atat CH tuote wanne stoffen Stato Et PPKO Fostane VO Postcode Fran Vacher Supply Lastlane HOY 2005 Como to Descrico YO Phone vecento TO Honey Super Rencode FK Stalin Nemo Darty Training RS Store Supply stafood Suporte parcode Durite V WE Stor Room Submission Requirements Lab Submission is to include the following: Create a single script file called Lab2Solution.sql" that contains a clearly commented batch of statements for each requirement. Each batch will contain the SQL statement(s) required to complete the question. DO NOT include anything else (test data, random comments, etc.). The entire script should be able to run at once. If there are any known errors in your solution you must identify them at the top of your script in comments. Errors you have identified and simply could not find a solution for are more acceptable than undocumented errors. Failure to document known errors will result in mark 12 mark deduction for each undocumented error up to 3 marks. Your script file will be submitted to moodle (or other location if specified by your instructor). . Any additional requirements as specified by your instructor. Other Considerations Do not make assumptions. If you have questions about the company, ask your instructor (client). This is not a group project. Working with another student on lab material may result in a grade of O for this lab. Up to 3 Marks may be deducted for incomplete lab submission requirements or poor client communication (check the provided documentation for the answer before asking the client)