View the step-by-step solution to:

This must be done in SQL Server 2005. In the first exercise, the Class field in the Part table should be a string of size 5, and not an int.

This must be done in SQL Server 2005.
In the first exercise, the Class field in the Part table should be a string of size 5, and not an int.
1- Write a statement that creates a table named Part, with an Id field as an int idendity primary key(PK), a SupplierId int field, a Description string field of size 25, a Count int field, a Class string field of size 5, and an inspection small date field. All fields cannot have a value of null, and the SupplierId field should be an FK that references the PK of a Supplier parent table.
2- Write a satement to add a check constraint to the above defined table, which guarantees that values entered in the inspection small date field are not older than today, meaning from now on. According to new requirements from your business analyst, an inspection can only be scheduled in the future.
3- Write a statement to add a check constraint to the above defined table, which guarantees that values entered in the Count field are never less than zero.
4- Write a statement to add a check constraint to the above defined table, which guarantees that values entered in the Class field can only be limited to the following strings: Open, Spec, and Priv.
5- Write a statement to create a view that selects all fields from the above table, except for both Id fields, in addition to the supplier name field of the Supplier table referenced in question 1. Note that the user is only interested in Parts that are not of class Priv. In other words, parts of class Priv should not be returned. Hint: A JOIN must be used to only return common rows.

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question