Database

Graffiti Busters Lab 2A DDL Objectives Given an ERD, define tables and constraints on the data, using the Create Table statement in SQL Server Alter the definition of an existing table using the Alter Table statement Create indexes on non-primary key fields using the Create Index statement Run the Lab2ATestData sql file to test your table creates. If the script fails, you have errors in your tables. 1. 2. 3. 4. Requirements Given the ERD shown in the appendix, create the required tables for the Graffiti Busters database In addition, create appropriate constraints (primary key, foreign key, check and default) All the attributes that are on the ERD are required attributes. 1. The following constraints must be included in your table definitions. Column Wage Credits Table Check Default -0 20 Training Job Job Client Job 2 alpha characters T8A 1H3 Format PC SubTotalTotal Total>Subtotal The following columns must use the identity property starting at 1 and incrementing by 1: Table Column Job JobNumber Client 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. 2. 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) 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. c. A default of AB needs to be added to the job tables province attribute. 3. Create indexes for all foreign keys Submission Requirements Lab Submission is to include the following . Create a single script file called "Lab2ASolution.sql" that contains a clearly commented batch of statements for each requirement. Each batch will contain the SQL statement(s) required torcomplete the question. DO NOT include anything else (test data, random comments, etc.) . A professional-looking printed report, including a title page, discussion and a printout of your script file in a duotang. Your title page should include the lab number, student name, instructor name, and section number . . A short discussion about the lab including o o o o About what you liked/disliked about the lab How long it took you to complete the lab How prepared you felt you were for the lab Recommendations for future labs (if any) If there are any known errors in your solution you must identify them in your discussion. Errors you have identified and simply could not find a solution for are more acceptable than undocumented errors@. Failure to document known errors wil result in mark deductions. Graffiti Busters Lab 2A DDL Objectives Given an ERD, define tables and constraints on the data, using the Create Table statement in SQL Server Alter the definition of an existing table using the Alter Table statement Create indexes on non-primary key fields using the Create Index statement Run the Lab2ATestData sql file to test your table creates. If the script fails, you have errors in your tables. 1. 2. 3. 4. Requirements Given the ERD shown in the appendix, create the required tables for the Graffiti Busters database In addition, create appropriate constraints (primary key, foreign key, check and default) All the attributes that are on the ERD are required attributes. 1. The following constraints must be included in your table definitions. Column Wage Credits Table Check Default -0 20 Training Job Job Client Job 2 alpha characters T8A 1H3 Format PC SubTotalTotal Total>Subtotal The following columns must use the identity property starting at 1 and incrementing by 1: Table Column Job JobNumber Client 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. 2. 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) 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. c. A default of AB needs to be added to the job tables province attribute. 3. Create indexes for all foreign keys Submission Requirements Lab Submission is to include the following . Create a single script file called "Lab2ASolution.sql" that contains a clearly commented batch of statements for each requirement. Each batch will contain the SQL statement(s) required torcomplete the question. DO NOT include anything else (test data, random comments, etc.) . A professional-looking printed report, including a title page, discussion and a printout of your script file in a duotang. Your title page should include the lab number, student name, instructor name, and section number . . A short discussion about the lab including o o o o About what you liked/disliked about the lab How long it took you to complete the lab How prepared you felt you were for the lab Recommendations for future labs (if any) If there are any known errors in your solution you must identify them in your discussion. Errors you have identified and simply could not find a solution for are more acceptable than undocumented errors@. Failure to document known errors wil result in mark deductions