How would the SQL output and statements look like for these questions? (ignore #3)

1. The following table structure contains staff data that need to be normalized and stored into a relational database.

empID empName empPhone empPositionID empPositionDescipt empNextRaiseDate empSalary

1. Create a table that can store the staff data with empID as the primary key.

2. Implement the following database structure by typing it and based on your experience, which referential integrity linking the primary key with the foreign key below appears less complex to you?

CREATE TABLE staff_tbl (empID Char(9) NOT NULL PRIMARY KEY, empLastName VarChar (15), empFirstName VarChar (15),

emMiddleName Char (15), empPhone Char(10));

CREATE TABLE Salary_tbl

(empEmpID Char(9) NOT NULL,

salID Char(10) NOT NULL PRIMARY KEY, salPositionID Char(10), salPositionDescription VarChar(25), salNextRaiseDate Date, salSalary decimal(8,2),

CONSTRAINT salID_FK Foreign Key (empEmpID) REFERENCES staff_tbl (empID));

CREATE TABLE Salary1_ tbl (salID Char(10) NOT NULL PRIMARY KEY, salPositionID Char(10),

salPositionDescription VarChar(25),

salNextRaiseDate Date,

salSalary decimal(8,2),

empEmpID Char(9) NOT NULL References staff_tbl (EmpID) );

3. Modify the doctor table from previous week to ensure that Initials field can only take two characters. If your middle initials field does not or no longer exist, you can first alter the table to add it. After adding the middle first column, then you can now define a column level constraint to automatically reject any entry of total length not equal to two.

4. Create a student table with the following attributes (SSN, Student Id, First Name, Last Name, Birth day, Gender, and Grade) and assign appropriate data types for each.

5. Add appropriate constraints for Primary key and Unique Key on the student table created in previous question. Use your database design skills to decide which columns need the Primary and Unique Key constraints.

6. Ensure that the Gender column can only take letter M for male or F for female, and use the Birthday field to ensure that currently enrolled high school students who are 17 years or younger are not included in the report. Hint: Use the PSQL current date function to validate the date of birth for 17 years or younger students.

7. Create a database Index on Last name field for the Student table then display both the index and the student table to show all indexes and constraints created.

8. In previous week take-home quiz and a similar question in lab#5, you used the following statement to calculate how many authors were born between 1900 and 1930. select count(*)from author where year_born >= 1900 and year_born <= 1930;

select count(*)from author where year_born >= 1900 and year_born <= 1930;

Now assume you want to limit the values that can be entered in the year_born column to the range of values between 1900 and 1930. How can you implement this solution using the CHECK constraint technique?