Computers SQL

 

Professional Litigation User Services (PLUS) is a company that creates all types of visual aids for judicial proceedings. Clients are usually private law firms, although the District Attorney’s office has occasionally contracted for its services.

PLUS creates graphs, maps, timetables, and charts, both for computerized presentations and in large sizes for presentations to juries. And anything else of a visual nature that can be used in a judicial proceeding to make, clarify, or support a point.

Raj Jawahir is responsible for tracking the daily payments received from PLUS clients. You’ll help him maintain the PLUS database by creating the following ERD and tables:

1. Use SQL Server to create a new database with 2 tables based on the ERD/TICs above.
2. Create a new database and name it PLUS.
3. Inside the PLUS database create a new table named Firm using the table design shown in Figure 1 ERD and Figure 2 table instance chart above. Create this table with the required columns, key types (Primary Key), constraints, data type and length as detailed in the Figure 2 Firm TIC above. Save the Firm Table.
4. Inside the PLUS database create a new table named Payment using the table design shown in Figure 1 ERD and Figure 3 table instance chart above. Create this table with the required columns, key types (Primary Key), constraints, data type and length as detailed in the Figure 2 Payment table instance chart above. Be sure to add the Foreign Key constraint in the Payment table! Save the Payment table.
5. Use Figure 2 Firm table instance chart Sample Data to insert 2 rows of data into the Firm table.
6. Use Figure 3 Payment table instance chart Sample Data to insert 2 rows of data into the Payment table.

Screenshot Guidelines:

1. Complete all of the steps outlined above in the Assignment Description.
2. When you are finished, take the following screenshots and paste them into ONE Word document:
• One screenshot in the Object Browser of the Database name PLUS and both table names.
• One screenshot of each table (Firm and Payment) expanded in the Object Browser. All column names, constraints (Primary, Foreign, Not Null), data types and length must be visible. For the Payment table make sure the Foreign Key constraint is visible in the screenshot.
• One screenshot of each table (Firm and Payment) in the “Edit Top 200 Rows” view. Must be able to see data in all columns – you might have to take more than one screen shot for the Firm table if you are unable to view all columns.
• After you have inserted data into both tables, write the following Select statements to test the database. Take one screen shot of each Select statement and the results.
3. Write one select statement that will retrieve all the rows from the Firm table.
4. Write one select statement that will retrieve the paymentid, firmid and amount paid from the Payment table. Sort the results by amount paid in ascending order.

Paste your screenshots into ONE Word document.