Database and Project Management Module 2: Building Tables and Relationships

Momentum Legal

Assignment #1

For this assignment, you are going to create a new database for a law firm. Using the information provided, create the two tables.

For each table, create the required fields, choose the appropriate data type, ensuring that the field shows the full text. Save the completed tables as PDF documents in Landscape orientation.

Instructions for Saving your Database

For each assignment you will need to save and upload your database along with any other created or requested files. As the module progresses you will be adding to and modifying your database. Save your database as: Your Name_Database. e.g. Joan Smith_Database

Instructions for Saving Files for Submission

First create a file folder for your assignment. Save the file folder as Your Name_Assignment #.e.g. Joan Smith_Assignment 1

To save a table, query, or report for submission do the following:

First make sure you are in the table, query or report you want to save

Click on "File" on the top left-hand corner of the ACCESS ribbon

Click on "Print"

Click on "Print Preview"

Select "Landscape" in the "Page Layout" section

Click on "PDF or XPS" in the "Data Gallery"

Insert Filename and ensure PDF is showing as "File Type"

Filename protocol is (exercise name_your first initial and last name)

e.g.

Category Table_dsmith

Zipping files

Once you have saved all your files in a folder for each assignment you will need to compress or zip the folder before uploading it. A zipped folder takes up less space and makes sending files easier and quicker. There are several options available for zipping files and Windows has its own zip program installed already. Include your re-saved Database in each zipped assignment folder.

Assignment 1 - PART 1

Law firm name: Momentum Legal

Table #1: Clients

Fields to be created:

Fields	Data Type	Tips
Client ID	AutoNumber	Assign as Primary Key	AC 2 Pages 8-9
First Name	Short Text
Last Name	Short Text
Street Address	Short Text
City	Short Text	Assign a default of Ottawa	Change default as required
Province	Short Text	Assign a default of ON	Change default as required
Postal Code	Short Text
Telephone	Short Text	Use Input Mask	AC 2 Pages 8-9

Client Data

1. Susan Reed

127 Dover Crescent, Ottawa, ON, K2B 7Z1

613 555-2112

1. Lionel Campeau

53 Bank Avenue, Ottawa, ON, K5P 3V4

613 551-4325

1. Yolande Serrault

8885 Roberts Avenue, Ottawa, ON, K4K 4L4

513 945-2313

1. David McNarra

54 Avenue Sainte, Gatineau, QC, J8L 4X8

819 412-3333

1. Philip Stevenson

56 Picard Place, Ottawa, ON, K4C 5T3

513 920-4431

1. Peter Prana

444 Boulevard Galland, Gatineau, QC, J8L 7G3

819 444-3416

1. Kenneth Preston

83 Birchview Place, Ottawa, ON, K2G 9V4

513 444-3412

1. Constance Peters

78-324 Fernbank Drive, Ottawa, ON, K4B 8D3

613 555-0000

1. Al Posiedon

5 Kennedy Road, Ottawa, ON, K1E 4T1

613 888-4323

1. Abigail Auberge

989 Chemin Herron, Gatineau, QC, J8L 5R1

819 444-5555

Table #2: Staff

Fields to be created:

Fields	Data Type	Tips
Staff ID	AutoNumber	Assign as Primary Key	AC 2 Pages 12-13
First Name	Short Text
Last Name	Short Text
Telephone	Short Text	Use Input Mask	AC 2 Pages 8-9

Staff Data

1. Adele Murphy

613 526-1346

1. Peter Mitchell

613 526-1348

1. Anthony Marcus

613 526-1349

1. Diane Fitzgibbons

613 526-1347

1. Mark O'Brian

613 526-1350

1. Joanne Simmons

613 526-1343

1. Marie Gaston

613 526-1342

Save your database and files with the naming convention outlined by Algonquin College at the beginning of this assignment.

Filename protocol is (exercise name_your first initial and last name)

e.g.

Category Table_dsmith

Assignment 1 - PART 2

Using the database you created in Part 1, add/modify or edit the following information:

Note: Adjust field size to ensure that the full field information is visible.

1. Client Table
   1. Add cell phone numbers for the following clients:
      1. Susan Reed 513 434-5566
      2. Lionel Campeau 613 741-8756
      3. David McNarra 819 544-5400
      4. Philip Stevenson 513 847-5436
      5. Peter Prana 819 565-6655
      6. Constance Peters 613 749-0000
      7. Al Posiedon 613 513-8765
   2. Add email addresses for the following clients:
      1. Susan Reed s..d@gmail.ca
      2. Lionel Campeau l..u@rogers.ca
      3. Yolande Serrault y..2@gmail.ca
      4. David McNarra d..a@bellnet.com
      5. Philip Stevenson p..n@magma.com
      6. Peter Prana p..a@gmail.ca
      7. Constance Peters c..s@rogers.ca
      8. Abigail Auberge a..e@cogeco.ca
   3. Change the address and Telephone for Susan Reed to

54 Baker Street, Ottawa, ON, K5R 7D1

613 542-9988

1. Staff Table
   1. Add cell numbers for:
      1. Adele Murphy 613 432-9876
      2. Peter Mitchell 513 666-3241
      3. Anthony Marcus 513 444-1231
      4. Diane Fitzgibbons 613 879-1100
      5. Joanne Simmons 513 554-8799
      6. Marie Gaston 513 332-1123
   2. Add email addresses for:
      1. Adele Murphy a..y@momentumlegal.com
      2. Peter Mitchell p..l@momentumlegal.com
      3. Anthony Marcus a..s@momentumlegal.com
      4. Diane Fitzgibbons d..s@momentumlegal.com
      5. Joanne Simmons j..s@momentumlegal.com
      6. Marie Gaston m..n@momentumlegal.com
   3. Delete Mark O'Brian and his information. (TIP: you must delete his entire record StaffID# 5 will be completely removed)
   4. Add a new staff member:
      1. Daniel Stuart, 613 526-1351

Cell # 513 828-4545

Email: d..t@momentumlegal.com

1. Add a column to Client table labelled StaffID, (Data Type - Number).
2. Assign lawyers to the clients as follows: (TIP: use their StaffID # not their name)

Client	Lawyer
Susan Reed	Adele Murphy
Lionel Campeau	Marie Gaston
Yolande Serrault	Adele Murphy
David McNarra	Diane Fitzgibons
Philip Stevenson	Daniel Stuart
Peter Prana	Anthony Marcus
Kenneth Preston	Daniel Stuart
Constance Peters	Adele Murphy
Al Posiedon	Peter Mitchell
Abigail Auberge	Joanne Simmons

1. Create a one-to-many relationship between the Staff and Client tables, with the Staff being the "One" and the client being the "Many". Enforce Referential Integrity, Cascade Update Related Fields and Cascade Delete Related Records.

1. In a zipped folder include your modified database and save as PDF the following tables & report:

Submit the following:

1. Database
2. Staff Table PDF
3. Client Table PDF

Relationship Report PDF