Lab and Homework Assignment #1Microsoft Access and SQL Server Management Studio Exercise 1: Access Table Design, Creation, and Modification Objective:

Lab and Homework Assignment #1Microsoft Access and SQL Server Management Studio

Exercise 1: Access Table Design, Creation, and Modification

Objective: In this exercise, you will use MS Access to create and modify an Access database table.

General Steps:

1. Create a Table in Datasheet View
2. Start Access and click Blank Database. Type Branch_LastFirstName.accdb in the File Name box. Click Browse icon to find the folder location where you will store the database 🡪 OK. Click Create to create the new database.
3. Click to Add and select Short Text as the Data type. Type LocationID and press Tab. Select Short Text, type Manager, and then press Tab. Select Short Text and type Region.
4. Click in the first column (the ID field) next to the New Record asterisk, press Tab, and 

then type the data for the new table below, letting Access assign the ID field value for each new record (using the AutoNumber data type). Replace LastName with your own last name.

LocationID	Manager	Region
B10	Hayes	North
B20	Ruiz	East
B30	Downing	West
B40	Fadden	South
B50	LastName	Downtown

4. Click Save on the Quick Access Toolbar. Type Locations as the table name. 
5. Click View to switch to Design view. Ensure that the ID field is selected and click Delete Rows in the Tools group on the Design tab. Click Yes to both warning messages.

2. Set a Table’s Primary Key

1. Ensure that the LocationID field is selected and click Primary Key in the Tools group on the Design tab. Save the table.

3. Work with Field Properties

1. Click in the LocationID field name and modify the LocationID field properties:

• Click in the Field Size box and change 255 to 5.
• Click in the Caption box and type Location ID. 
• Check the Indexed property; confirm it is Yes (No Duplicates).
• Check the Required property; confirm it is Yes.

2. Click the Manager field name and modify the Manager field properties:

• Click in the Field Size box in the Field Properties pane and change 255 to 30.
• Click in the Caption box and type Manager’s Name.

3. Click the Region field name and modify the following Region field properties:

• Click in the Field Size box and change 255 to 30.
• Click in the Caption box and type Region Name.

4. Create a New Field in Design View

1. Click in the first blank field row below the Region field name and type StartDate. Press Tab, click the Data Type arrow, and then select Date/Time. Press Tab and type This is the date the manager started working at this location.
2. Click in the Format box in the Field properties pane, click the arrow, and then select Short Date. Click in the Caption box and type Manager’s Start Date. 
3. Save the table and click Yes in the warning box.

5. Modify the Table in Datasheet View

1. Click Datasheet View. 
2. Position the pointer over the border between Location ID and Manager’s Name so that it becomes a double-headed arrow and double-click the border. Repeat the process for the borders after each of the other fields.
3. Click inside the Manager’s Start Date in the first record and click the Date Picker. Find and select July 3, 2024, from the calendar. Type the following dates directly in each field for the rest of the managers: 2/15/2024, 3/15/2024, 6/20/2024, 7/21/2024.
4. Click Close at the top-right corner of the datasheet and click Yes to save the changes.
5. Double-click the Location table to open the table. Click the File tab, click Print, and click Print Preview. Click Close Print Preview and close the Locations table.
6. Close the database and exit Access.

Exercise 2: Multiple-Table Databases in Access

Objective: In this exercise, you learn how to import new Excel and Access Data into the database created in Exercise 1.

1. Import Excel Dataa. Open Branch_LastFirstName created in Exercise 1. Enable the content if necessary.
2. Click the External Data tab, click New Data Source, point to From File, and then select Excel. Ensure that Import the source data into a new table in the current database is selected.
3. Click Browse, and then navigate to and select the a02h2Customers.xlsx you downloaded from Blackboard. Click Open and click OK. Ensure that the First Row Contains Column Headings check box is checked and click Next. 
4. Ensure that CID is displayed in the Field Name box in Field Options. Click the Indexed arrow and select Yes (No Duplicates). Click Next. 
5. Click the Choose my own primary key option, make sure the CID field is selected, and click Next. 
6. Click Finish to accept Customers as the table name. Click Close.
7. Open the imported Customers table in Datasheet view and double-click the border between each of the field names to adjust the columns to Best Fit. 
8. Save and close the table.

2. Import Access Data

1. Click the External Data tab, click New Data Source, point to From Database, and then select Access. Ensure that the Import tables, queries, forms, reports, macros, and modules into the current database is selected.
2. Click Browse, and then navigate to and select the a02h2Accounts.accdb database. Click Open, and click OK to open the Import Objects dialog box.
3. Click the Accounts table and click OK. Click Close.
4. Open the imported Accounts table in Datasheet view, and then close the table.

3. Modify an Imported Table’s Design

1. Right-click the Accounts table in the Navigation Pane and select Design view. 
2. Click the AID field name and change it to AccountID. Change the Field Size property to Long Integer and type Account ID in the Caption box. 
3. Click the CID field name change it to CustomerID. Change the Field Size property to Long Integer and type Customer ID in the Caption box. 
4. Click the BID field name and change it to LocationID. Type 5 in the Field Size property box and type Location ID in the Caption box.
5. Ensure the Data Type of the Balance field is Currency.
6. Change the Data Type of the OpenDate field to Date/Time and set Short Date in the Format field property. Type Open Date in the Caption box. Click View to switch to Datasheet view and click Yes to each message. 
7. Right-click the Customers table in the Navigation Pane and select Design View.
8. Click the CID field. Change the field name to CustomerID, change the Field Size property to Long Integer, and type Customer ID in the Caption box. 
9. Change the Field Size property to 20 for the FirstName, LastName, Street, and City fields. Change the Field Size property to 2 for State.
10. Change the data type for Zip and Phone to Short Text and the Field Size property to 15 for both fields. 
11. Click the Phone field name, click Input Mask in Field Properties, and click the ellipsis on the right side to launch the Input Mask Wizard. Select “With the symbols in the mask …” Click Next. Click Yes to save the table, click Yes to the warning, and click Finish. 
12. Save the changes to the Customers table.

4. Add Data to an Imported Table

1. Click View to display the Customers table in Datasheet view. 
2. Click next to the * in the Customer ID field in the new record row below, and type 30011. Fill in the rest of the data using your personal information as the client. 
3. Close the Customers table. 

5. Establish table relationships in Access

1. Click the Database Tools tab and click Relationships. Double-click each of the three tables displayed in the Show Table dialog box. Click Close.
2. Click and drag the border of the Customers table field list so that all the fields are visible. 
3. Drag the LocationID field (the primary key) in the Location table onto the LocationID field (the foreign key) in the Accounts table. Click Create. Drag the CustomerID field (the primary key) in the Customers table onto the CustomerID field (the foreign key) in the Accounts table. Click Create. 
4. Save the changes to the relationships.

6. Enforce and Test Referential Integrity

1. Double-click the join line between the Location and Accounts tables. Click the Enforce Referential Integrity and Cascade Update Related Fields check boxes. Click OK.
2. Double-click the join line between the Customers and Accounts tables. Click the Enforce Referential Integrity and Cascade Update Related Fields check boxes. Click OK.

 

Exercise 3: Connecting to SQL Server Management Studio

Objective: In this exercise, you will connect to a remote SQL Server Management Studio through anywhereapps.niu.edu.

General Steps:

1.  Use Citrix (anywhereapps.niu.edu) to start SOL Server Management Studio (SSMS).
2. In the Connect To dialog box, verify that the server type is set to Database Engine andthe server’s name is set to omissql.niunt.niu.edu.
3. Use Windows Authentication, and Click the Connect button

Exercise 4: Create a new database in SQL Server Management Studio

Objective: In this exercise, you will use SQL Server Management Studio to create a new database

General Steps:

1. From Management Studio, expand the server in the Object Explorer. Highlight the Databases folder, and then right click on the Database folder. Choose New database from the context menu.
2. Specify a database name (your last_last name please)
3. Accept the defaults for this screen. (To change the properties of the database files, simply click in the appropriate box and make your modifications)
4. Click OK

Exercise 5: Create tables in SQL Server Management Studio

Objective: In this exercise, you will learn how to add a new table, assign a primary key, and add records in SQL Server Management Studio.

General Steps:

1. Expand the database you want to work with (Work with your own database)
2. Highlight the Tables folder. Right click the Tables folder and then choose New Table from the context menu
3. Now you can add information to Column Name, Data Type, and Allow Nulls columns.  Add the following fields:

Column Name	Data Type	Allow Nulls
BugID	Int	No (unchecked)
ReportedDate	Datetime	No
ReportedBy	Int	No
Severity	Tinyint	No
Problem	Varchar(1000)	yes
AssignedTo	Int	yes
ResolvedDate	Datetime	yes
Resolution	Varchar(1000)	yes

4. For the BugId column properties, expand the Identity Specification section and change “(Is Identity)” to Yes. The increment and seed will default to 1, which is fine.
5. Using the gray columns before the column names, click the area to the left of BugId. Click the Primary Key icon on the toolbar.
6. Click Save icon and change the table name to Bugs. Click OK. Close the window.
7. Highlight the dbo.Bugs table, right click, and select Script Table as 🡪 Create To 🡪 File. Save the file as H:CreateBugs.sql in your directory
8. The ReportedBy and AssignedTo columns will be used to store employee IDs. So we need to create an Employee table. Right-click the Tables folder and select New Table. Fill in the columns as follows:

Column Name	Data Type	Allow Nulls
EmployeeID	Int	No (unchecked)
LastName	Varchar(25)	No
FirstName	Varchar(25)	No
Phone	Varchar(25)	yes
Email	Varchar(50)	yes
HireDate	datetime	yes
BirthDate	Datetime	yes
Comment	Text	yes

9. Make EmployeeID an identity with a seed and increment of 1. Make EmployeeID the primary key.
10. Save the table as Employee and close it.
11. Right-click the Employee table and select Script Table as 🡪 Create To 🡪 File. Save the file as H:CreateEmployee.sql in your directory.
12. Close the window.

Exercise 6: Modify Table Structure and Add Records

Objective: In this exercise, you will learn how to modify a tables’ definition and add records

General Steps:

1. Right-click Employee and select Design
2. Add a new column called Salary with a data type of Money and allows nulls. Click the Save Employee button, then close the Employee table.
3. Right-click Employee table and select Edit All Rows. Enter the following data, leaving the EmployeeID and Comment columns blanks:

LastName	FirstName	Phone	Email	HireDate	BirthDate	Salary
Skywalker	Anakin	x9876	[email protected]	2002.3.29	1964.5.27	1000
Kenobe	Ben	X3426	[email protected]	1998.1.9	1945.7.14	2000

4. Right-click Bugs table and select Edit All Rows.
5. Enter today’s date for ReportedDate, 1 for PeportedBy, and 1 for Severity. Enter “Monitor will not turn on” for Problem column. Save the record.

6. Close the window.

Exercise 7: Table Relationships in SQL Server Management Studio

Objective: In this exercise, you will create a diagram for your database and create relationships between the Bugs and Employee tables.

General Steps:

1. Under your database, highlight the Diagrams item, right-click, and select New Database Diagram.
2. Click Yes to the prompt about adding the supporting objects for diagrams.
3. Double-click the Bugs table and then the Employee table to add them to the diagram. Click Close. You will establish referential integrity between the Employee table and the Bugs table by adding a foreign key constraint.
4. Drag the Employee’s EmployeeID column to the Bugs’ ReportedBy column.
5. In the “Table and Columns” dialog, check that the primary key table is Employee and that the foreign key table is Bugs. Also, check that the two columns referenced are the EmployeeID and the ReportedBy Columns. If the columns are wrong, you can correct the issue by choosing the proper column from the drop-down box. If the tables are wrong, click Cancel and try again.
6. After clicking OK in the “Table and Columns” dialog box, expand the Insert and Update Specification. Click in the Delete Rule value to change it to Cascade in the extended properties. Now click OK to dismiss the dialog.
7. Click the Save button, naming the diagram Relationships. Click OK to confirm the tables to be saved, and then click Yes to save the tables. 
8. Close the Diagram. 
9. Open the Bugs table. Add a new row, using:
   1. ReportedBy: 999
   2. ReportedDate: (today’s date)
   3. Severity: 1
10. Press the <Enter> key or down arrow. What happened ________________________?
11. Confirm that the constraint fails the addition and click OK.
12. Change ReportedBy to 2.  What happened ________________________?
13. Close the Bugs table.

Homework Assignment #1 (20 Points)

Due: 4:00 PM, Monday, September 22, 2025

1. Submit your screenshot of the Relationship Report based on the completion of Exercises 1 and 2 above.
2. Submit your screenshot of the table relationships based on the completion of Exercises 5, 6, and 7 above.