COMP 3321 1 Database Systems
Description
Unformatted Attachment Preview
COMP 3321
Database Systems
Final Exam
12/01/2022
Name:
Make sure that you have four questions:
Question
1
2
3
4
Est. Time
20 min.
10 min.
50 min.
20min.
Weight
20 points
6 points
16 points
8 points
Grade
Notes
Total Points: 50 points
Total Estimated Time: 100 minutes
Total Time Given: 120 minutes
This test is worth 25% of your final course grade.
This test is an open book.
1. Write a CREATE TABLE statement for all the tables in the above schema.
a. Use suitable data types and NULL / NOT NULL values for all fields.
b. For the STUDENT table: The primary key is an auto-increment. Use suitable
data types and Null/Not Null values for all fields.
c. For the COLLEGE table: The Building field should be in dams ìark and
íith2. Write an insert statement to:
a. Insert into the PROFESSOR table the professoràfirst name and last name
James McCall, who has office number 306 in Adams building. The professorÊphone number is 713-300-2010.
b. Imagine that there is an archive table called COLLEGE_ARC. Insert all data of
the COLLEGE_ARC into the COLLEGE table. Both tables have the same fields.
3. Write data-retrieving statements for each of the following:
a. Write an ALTER statement to add the column Rank to the PROFESSOR table.
Assume that Rank is not required.
b. Write an ALTER statement to make Phone an alternate key in COLLEGE.
c. Write an UPDATE statement to change the city of a student with student number
101102 to /ustond. Write an UPDATE statement to change the phone number of a student with ID
107111 to ñ3-543-0011!nd the state to e. Write a statement to delete all data for professor ¡mes McCall.¦. Delete all data in the COLLEGE table but keep the table structure.
g. Write a view to select the deanàfirst and last name whose appointment title is
dvisor´. Advanced DML.
a. Create a user-defined function named FUNC_APPOINTMENT that combines the
Title and Term of the APPOINTMENT table into a concatenated name field.
b. Create a stored procedure that selects the studentàfirst and last names with a
certain (variable) city and state.
Purchase answer to see full
attachment
Database Systems
Final Exam
12/01/2022
Name:
Make sure that you have four questions:
Question
1
2
3
4
Est. Time
20 min.
10 min.
50 min.
20min.
Weight
20 points
6 points
16 points
8 points
Grade
Notes
Total Points: 50 points
Total Estimated Time: 100 minutes
Total Time Given: 120 minutes
This test is worth 25% of your final course grade.
This test is an open book.
1. Write a CREATE TABLE statement for all the tables in the above schema.
a. Use suitable data types and NULL / NOT NULL values for all fields.
b. For the STUDENT table: The primary key is an auto-increment. Use suitable
data types and Null/Not Null values for all fields.
c. For the COLLEGE table: The Building field should be in dams ìark and
íith2. Write an insert statement to:
a. Insert into the PROFESSOR table the professoràfirst name and last name
James McCall, who has office number 306 in Adams building. The professorÊphone number is 713-300-2010.
b. Imagine that there is an archive table called COLLEGE_ARC. Insert all data of
the COLLEGE_ARC into the COLLEGE table. Both tables have the same fields.
3. Write data-retrieving statements for each of the following:
a. Write an ALTER statement to add the column Rank to the PROFESSOR table.
Assume that Rank is not required.
b. Write an ALTER statement to make Phone an alternate key in COLLEGE.
c. Write an UPDATE statement to change the city of a student with student number
101102 to /ustond. Write an UPDATE statement to change the phone number of a student with ID
107111 to ñ3-543-0011!nd the state to e. Write a statement to delete all data for professor ¡mes McCall.¦. Delete all data in the COLLEGE table but keep the table structure.
g. Write a view to select the deanàfirst and last name whose appointment title is
dvisor´. Advanced DML.
a. Create a user-defined function named FUNC_APPOINTMENT that combines the
Title and Term of the APPOINTMENT table into a concatenated name field.
b. Create a stored procedure that selects the studentàfirst and last names with a
certain (variable) city and state.
Purchase answer to see full
attachment
Explanation & Answer:
4 Questions
User generated content is uploaded by users for the purposes of learning and should be used following our honor code & terms of service.
Have a similar assignment? "Place an order for your assignment and have exceptional work written by our team of experts, guaranteeing you A results."
Complete the 
Complete the order payment after filling in the order form. We’ll receive your order and assign it to a writer.
The assigned writer will complete your paper and forward it to our Editing Team for review and approval.
Once approved by the editor, the completed paper will be uploaded to your account for you to download, review and approve.