Please use Microsoft SQL Server Management Studio

Assistance Application Database Objects

1. Tables

Create a database and the following tables and implement relationships in your database.

Determine the appropriate fields, data types, and other field properties (such as check constraints) for each table based on the information below. Only create the tables listed (choosing fields for each table is up to you).

Define any primary keys, and include fields where needed to serve as foreign keys based on the following relationships: Client(1) Assist(M); Specialist(1) Assist(M); Assist(1) Task(M); Specialist(1) Outreach(M).

Identify and create indexes as appropriate.

Submit a document with the code for your database, tables, and indexes.

Client Table

First, last, and middle names

Nickname

Birthdate

Social Security Number (9 numbers)

Medicare number (9 numbers followed by a letter)

ACES number (internal state identifying system, 5 digits)

Phone number

Message phone

Street Address

Mailing Address

Emergency contact name, phone number, relationship, and comment area

Physician and physician phone number

Income

Value of Assets

Specialist Table

First and last names

Title

Assist Table

Open and Close Dates

Contact type (phone, office visit, home visit)

Referral source, phone number, whether referral is confidential, if follow-up with the referring source is needed

Type of assist (valid values: referral, assistance with forms, advocacy, supportive counseling, service eligibility for doctor and/or dental, other)

Work Effort (or Task) Table

Date

Activity

Whether follow-up is needed

Status

Outreach Table

Date

Location and/or group

Number of attendees

Notes

2. Table Data

Write SQL statements to insert, update, and delete a record in each of the Clients, Assist, Task, Outreach, and Specialist tables.

Insert records into the tables for the sample data below).

Write Select statements to organize data from different tables.

Select all clients who have had an assist; include client name, assist date and type.

List all assists including the name of the Specialist.

List client names having tasks needing follow up.

List all clients having an assist in 2003; include client name and specialist name in the result.

List all clients having an open assist; include client name, assist date and type, and specialist name in the result.

Copy the SQL statements from #1, #2 and #3 into a Word document and submit to Canvas.

Sample Data

Clients

Thank you!

Sample Data Clients residence adaress Specialists Assists Tasks Sample Data Clients residence adaress Specialists Assists Tasks