Requirements: In order to complete this, you will need to perform the following: Create tables Create key constraints Create check constraints Import data to the table (provided) Ensure bad data cannot be inserted Answer lab questions #4 Steps: Step 1: Create a new database in pgAdmin named 'lab04' - this can be done either via SQL or from the pgAdmin GUI Step 2: In step 3, you will be writing three SQL queries - all of which create a table. Your entire code will be based off the rules listed below. It will combine everything we learned during the week regarding keys and constraints. It is important you read all of the following requirements before starting your code. 1. Create three tables based upon the following E/R diagram: player_id number first_name last_name position age salary team_id players smallint PK smallint varchar(25) varchar(50) char(2) smallint int int team_id city name conference division teams serial PK varchar(50) varchar(50) char(3) char(5) coaches coach_id serial PK first_name varchar(25) Klast_name varchar(50) title varchar(25) salary int team_id int FK 2. Ensure that the SQL to create the tables includes all key constraints required based upon the primary and foreign keys shown in the E/R diagram. 3. Ensure that if a row is deleted from the teams table, it is automatically deleted from both the players and teams tables, i.e. there is a cascade delete for both 4. In the NFL, player numbers can only be the numbers 1 through 99. Create a CHECK constraint that ensures no number lower than 1 or higher than 99 can be entered into the 'number' column in the players' table. 5. There are two conferences in the NFL; 'NFC' and 'AFC' create a CHECK constraint that ensures any data being entered into the conference' column in the 'teams' table has to be one of those two values. 6. Create a UNIQUE constraint that ensures the 'name' attribute in the teams' table cannot have duplicate values. 7. Ensure that each row entered into both the 'players' and 'coaches' table must have first names and last names, i.e. first name and last name cannot be null for either table Steps 3: Write three SQL queries based upon the requirements in step 2. You should have one query for the creation of each table; players, teams, and coaches. All constraints should be included within the table creation queries and no table alters should be used. Again, the three table creation queries should meet all of the requirements in step 2 Step 3: Entering data into your tables to test is optional, but highly encouraged in order to test your code. I will be using the SQL you create, entering 'good' data into the tables, then attempting to enter bad' data that should create errors based upon the constraints you have added in your code. Requirements: In order to complete this, you will need to perform the following: Create tables Create key constraints Create check constraints Import data to the table (provided) Ensure bad data cannot be inserted Answer lab questions #4 Steps: Step 1: Create a new database in pgAdmin named 'lab04' - this can be done either via SQL or from the pgAdmin GUI Step 2: In step 3, you will be writing three SQL queries - all of which create a table. Your entire code will be based off the rules listed below. It will combine everything we learned during the week regarding keys and constraints. It is important you read all of the following requirements before starting your code. 1. Create three tables based upon the following E/R diagram: player_id number first_name last_name position age salary team_id players smallint PK smallint varchar(25) varchar(50) char(2) smallint int int team_id city name conference division teams serial PK varchar(50) varchar(50) char(3) char(5) coaches coach_id serial PK first_name varchar(25) Klast_name varchar(50) title varchar(25) salary int team_id int FK 2. Ensure that the SQL to create the tables includes all key constraints required based upon the primary and foreign keys shown in the E/R diagram. 3. Ensure that if a row is deleted from the teams table, it is automatically deleted from both the players and teams tables, i.e. there is a cascade delete for both 4. In the NFL, player numbers can only be the numbers 1 through 99. Create a CHECK constraint that ensures no number lower than 1 or higher than 99 can be entered into the 'number' column in the players' table. 5. There are two conferences in the NFL; 'NFC' and 'AFC' create a CHECK constraint that ensures any data being entered into the conference' column in the 'teams' table has to be one of those two values. 6. Create a UNIQUE constraint that ensures the 'name' attribute in the teams' table cannot have duplicate values. 7. Ensure that each row entered into both the 'players' and 'coaches' table must have first names and last names, i.e. first name and last name cannot be null for either table Steps 3: Write three SQL queries based upon the requirements in step 2. You should have one query for the creation of each table; players, teams, and coaches. All constraints should be included within the table creation queries and no table alters should be used. Again, the three table creation queries should meet all of the requirements in step 2 Step 3: Entering data into your tables to test is optional, but highly encouraged in order to test your code. I will be using the SQL you create, entering 'good' data into the tables, then attempting to enter bad' data that should create errors based upon the constraints you have added in your code
